-
kevin Rowe
- PowerBuilder
- Wednesday, 11 November 2020 03:11 PM UTC
I have code that i need to use dynamic cursors for to scan for key values in referential testing.
database is postgres.
the first query must be in sql as the compiler does not recognize the @ character as SQL. it works fine.
the second query cannot use parameters because the compiler either does not recognize the ? character for place holder or assumes it is a literal and cannot place the variable in using :varname on the open
the code compiles and runs up to the second cursor prepare then crashes.
no matter how i try to code the second cursor, it fails when I open it.
either statement is not prepared, or mismatch between parameters and place holders
currently it just crashes with statement is not prepared. NO sql trace in database, no obvious error bofore in powerbuilder
can you execute two dynamic staging area cursors together at all?
If so, what am i doing wrong?
<code>
// find all linked tables by foreign key with arribute type restrict on delete
// return table name and true for SQL error will occur if delete is processed
// or false for no key matches on restrict table
string lsTableListSQL
string lsKeyListSQL
string lsTable
string lsCol
string lsFtable
string lsKeyName
string lsKeyType
string lsKeyReadSQL
long llCount = 0
long llKeyCount = 0
boolean lbFound = false
// read all tables with a restrict on delete to the input table
lsTableListSQL = &
"SELECT " + &
" (SELECT r.relname FROM pg_class r WHERE r.oid = c.conrelid) AS table, " + &
" (SELECT attname FROM pg_attribute " + &
" WHERE attrelid = c.conrelid AND ARRAY[attnum] <@ c.conkey LIMIT 1) AS col, " + &
" (SELECT r.relname FROM pg_class r WHERE r.oid = c.confrelid) AS ftable " + &
" FROM pg_constraint c " + &
" WHERE c.confrelid = (SELECT oid FROM pg_class WHERE relname = '" + tablename + "' ) " + &
" AND confdeltype = 'r';"
// read the key fields of the input table
dynamicstagingarea sqlsa1
dynamicstagingarea sqlsa2
DECLARE fktablecsr DYNAMIC CURSOR FOR SQLSA;
DECLARE keylistcursor dynamic CURSOR FOR sqlsa1;
DECLARE keyreadcursor DYNAMIC CURSOR FOR SQLSA2;
sqlsa1 = create dynamicstagingarea
sqlsa2 = create dynamicstagingarea
PREPARE SQLSA FROM :lsTableListSQL USING iuo_odbc;
OPEN DYNAMIC fktablecsr;
FETCH fktablecsr &
INTO :lsTable,
:lsCol,
:lsFtable;
do while (iuo_odbc.SQLCODE = SQL_OK)
lsKeyListSQL = &
"SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type " + &
"FROM pg_index i " + &
"JOIN pg_attribute a ON a.attrelid = i.indrelid " + &
" AND a.attnum = ANY(i.indkey) " + &
"WHERE i.indrelid = '" + lsTable + "'::regclass " + &
"AND i.indisprimary;"
PREPARE SQLSA1 FROM :lsKeyListSQL USING iuo_odbc;
OPEN DYNAMIC keyreadcursor; <--- crashes here
FETCH keyreadcursor
INTO :lsKeyName,
:lsKeyType;
lsKeyReadSQL = "SELECT COUNT(*)" + &
" FROM " + lsTable
llKeyCount = 1;
do while (iuo_odbc.SQLCODE = SQL_OK)
if (llKeyCount = 1) then
lsKeyReadSQL = lsKeyReadSQL + " WHERE "
else
lsKeyReadSQL = lsKeyReadSQL + " AND "
end if
// add in the key value pairs for each keyname and passed value
if (upperbound(keyvalues) < llKeyCount) then
// quack quack oops. no value for key
lbFound = false
blockedby = "Error. Invalid key check on " + tablename
return false
end if
if (lsKeyType = 'integer') or (lsKeyType = 'smallint') then
lsKeyReadSQL = lsKeyReadSQL + lsKeyName + " = " + string(keyvalues[llKeyCount])
end if
if (left(lsKeyType,7) = 'numeric') then
lsKeyReadSQL = lsKeyReadSQL + lsKeyName + " = " + string(keyvalues[llKeyCount])
end if
if (left(lsKeyType,7) = 'decimal') then
lsKeyReadSQL = lsKeyReadSQL + lsKeyName + " = " + string(keyvalues[llKeyCount])
end if
if (left(lsKeyType,4) = 'char') then
lsKeyReadSQL = lsKeyReadSQL + lsKeyName + " = '" + string(keyvalues[llKeyCount]) + "'"
end if
if (lsKeyType = 'date') then
lsKeyReadSQL = lsKeyReadSQL + lsKeyName + " = '" + string(keyvalues[llKeyCount], "YYYY-MM-DD") + "'"
end if
llKeyCount++;
FETCH keylistcursor
INTO :lsKeyName,
:lsKeyType;
loop
CLOSE keylistcursor ;
// key read SQL is prepared.
// check the passed value in the table
PREPARE SQLSA2 FROM :lsKeyReadSQL USING iuo_odbc;
OPEN DYNAMIC keyreadcursor using :lsTable ;
llCount = 0;
FETCH keyreadcursor INTO :llCount ;
if (llCount <> 0) then
lbFound = true
blockedby = lsTable
exit
end if
CLOSE keyreadcursor ;
FETCH fktablecsr
INTO :lsTable,
:lsCol,
:lsFtable;
LOOP
CLOSE fktablecsr;
return lbFound
</code>
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.