A.R.R. February 2016

Stored Procedure with system tables

I have two different system tables in my database: Systables (which stores all tables of the database) and Syscolumns (which stores all columns of the tables of the database). Systables contains: tabname and tabid; Syscolumns contains: colname, tabid, colno.

I have to make the following procedure:

  • I have 2 new tables - Tabela_sys (contains ns_tabela and nome), and Campo_sys (ns_campo, ns_tabela, nome).
  • I want to store the data of systables that does not exist in tabela_sys and I want to store the data of syscolumns that does not exist in Campo_Sys.
  • For that, I made the following procedure and I tested one case in which I created a new collumn in an existing table.
  • It is expected that after executing my procedure, Tabela_sys will keep the number of rows equal and Campo_sys will recognize 1 new row.

It is not happening and Campo_sys has many more rows, after performing the procedure.

Does anyone know what could be 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 WITH HOLD FOR

        SELECT  tabid, tabname
        INTO    TabIdNum,varTabName
        from    systables
        WHERE   tabname not matches "sys*"

        --let aa=varTabName;

        SELECT  count(*), ns_tabela
        INTO cont, Nstabela
        FROM tabela_sys
        WHERE nome = varTabName
        GROUP BY 2;

        --let Nstabela=0;

        IF cont = 0 or cont is null THEN    
            INSERT INTO tabela_sys (ns_tabela, nome) 
            VALUES (0, varTabName);

            SELECT dbinfo('sqlca.sqlerrd1')
            INTO   Nstabela
            FROM   systables
            WHERE  tabname='systables';
        END IF;

    FOREACH cur2 WITH HOLD FOR

        SELECT  tabid,        

Answers


BharatG February 2016

Of course the number of rows will be different for tabela_sys and campo_sys because the second foreach is run for each columns in syscolumns and whenever a new column is found for a table the campo_sys is inserted.

The requirement of yours will never give a equal number of records unless the syscolumns for a table is 1 (Means one column in a table which I think is not ideal situation) when using the existing procedure.

PS:I do not have enough reputation to comment hence adding as an answer here. You may accept as an answer if it answered your question ;)


Ricardo Henriques February 2016

There are a lot of misconceptions on the procedure.

First of all, don't use:

WHERE   tabname not matches "sys*"

If someone creates a table, let's say system_dest, you won't catch it.

The safest way to exclude system tables is:

WHERE   tabid > 99;

The tabid of the first user-defined table object in a database is always 100. And there are catalog tables that don't start with sys, like: GL_COLLATE, GL_CTYPE and VERSION.

You're creating a new serial instead of using the system one; do you plan to keep a history? This can be buggy if someone drop a table and recreate a new one using the same name.

You only update if no record is found, this means if the situation above happens you will not update. And if someone drops or add a new column it will not reflect on your tables.

Your second foreach has one logical problem:

    SELECT  tabid, colname 
    INTO    ColIdNum, StrColName
    FROM    syscolumns 
    WHERE   tabid = TabIdNum        

In this snipet you're storing the tabid and the colname.

Then you count the occurrence on the campo_sys by the name and your given ID not the tabid of the system. Hence it will always be 0.

    SELECT  count(*)
    INTO cont2
    FROM campo_sys
    WHERE nome = StrColName
    and   ns_tabela = Nstabela;

At last you insert into campo_sys storing the tabid of the system and the colname.

INSERT INTO campo_sys (ns_tabela, nome) --(ns_campo, ns_tabela, nome) 
VALUES (ColIdNum, StrColName); --(0, ColIdNum, StrColName);

This is why every time you run the procedure y

Post Status

Asked in February 2016
Viewed 2,623 times
Voted 5
Answered 2 times

Search




Leave an answer