sql - Stored Procedure with system tables -


i have 2 different system tables in database: systables (which stores tables of database) , syscolumns (which stores columns of tables of database).

  • systables contains: tabname , tabid;
  • syscolumns contains: colname, tabid, colno.

i have write following procedure:

  • i have 2 new tables - tabela_sys (contains ns_tabela , nome), , campo_sys (ns_campo, ns_tabela, nome).
  • i want store data of systables not exist in tabela_sys , want store data of syscolumns not exist in campo_sys.
  • for that, made following procedure , tested 1 case in created new column in existing table.
  • it expected after executing procedure, tabela_sys keep number of rows equal , campo_sys recognize 1 new row.

it not happening , campo_sys has many more rows, after performing procedure.

does know wrong?

create procedure test ()  define cont       integer; define cont2       integer; define tabidnum       integer; define vartabname,aa       varchar (50,0); define nstabela       integer; define strcolname       varchar (50,0); define colidnum       integer;      foreach cur1 hold          select  tabid, tabname            tabidnum,vartabname            systables           tabname not matches "sys*"          --let aa=vartabname;          select  count(*), ns_tabela         cont, nstabela         tabela_sys         nome = vartabname         group 2;          --let nstabela=0;          if cont = 0 or cont null                 insert tabela_sys (ns_tabela, nome)              values (0, vartabname);              select dbinfo('sqlca.sqlerrd1')               nstabela               systables              tabname='systables';         end if;      foreach cur2 hold          select  tabid, colname             colidnum, strcolname            syscolumns            tabid = tabidnum                 --and   colname not in (select nome campo_sys ns_tabela = nstabela)            select  count(*)         cont2         campo_sys         nome = strcolname         ,   ns_tabela = nstabela;          --let nscampo=0;          if cont2 = 0 or cont2 null               insert campo_sys (ns_tabela, nome) --(ns_campo, ns_tabela, nome)              values (colidnum, strcolname); --(0, colidnum, strcolname);          end if;      end foreach      end foreach  end procedure;

of course number of rows different tabela_sys , campo_sys because second foreach run each columns in syscolumns , whenever new column found table campo_sys inserted.

the requirement of yours never give equal number of records unless syscolumns table 1 (means 1 column in table think not ideal situation) when using existing procedure.

ps:i not have enough reputation comment hence adding answer here. may accept answer if answered question ;)


Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more