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
WHERE tabname not matches "sys*"
SELECT count(*), ns_tabela
INTO cont, Nstabela
WHERE nome = varTabName
GROUP BY 2;
IF cont = 0 or cont is null THEN
INSERT INTO tabela_sys (ns_tabela, nome)
VALUES (0, varTabName);
FOREACH cur2 WITH HOLD FOR
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 ;)