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>