Re: Dynamic SWQ

Marty Frongillo wrote:
>
> I'm trying to use dynamic sql for the first time. I want to have a
> general
> purpose routine, where I pass in the table-name and field-name and it
> will query the database and return the number of rows selected (and
> eventually an array of field-names). I can't seem to get this to work.
>
> The table is simply,
>
> SQL> describe language
> Name Null? Type
> ------------------------------- -------- ----
> LANGID NOT NULL NUMBER(38)
> NAME NOT NULL CHAR(32)
>
> The fetch returns ora-1403 (no data) and I don't know what's wrong.
> I've
> tried various combinations, but to no-avail. Hopefully, someone can
> point out
> my mistake.
>
> Is there better documentation than in the Pro*C manual (chapter 11)?
>
> int select_table_dyn (char *tablename, char *fieldname, char
> *data_ptr[])
> {
> int i, j;
> int l, n;
> int langid;
> int num_rows = 0;
> int rows_to_fetch=20;
> VARCHAR select_stmt[80];
>
> static char fields [10] [32];
>
> /* begin */
>
> oraca.orastxtf = ORASTFERR;
>
> strcpy(select_stmt.arr, "SELECT ");
> strcat (select_stmt.arr, fieldname);
> strcat (select_stmt.arr, " FROM ");
> strcat (select_stmt.arr, "user.");
> strcat (select_stmt.arr, tablename);
> select_stmt.len = strlen(select_stmt.arr);
>
> /* Register sql_error() as the error handler */
>
> EXEC SQL WHENEVER SQLERROR DO
> dyn_error ("sqlerror\n");
>
> EXEC SQL WHENEVER NOT FOUND do break;
>
> EXEC SQL PREPARE S FROM :select_stmt;
>
> EXEC SQL DECLARE C CURSOR FOR S;
>
> EXEC SQL OPEN C;
>
> for(;;)
> {
> EXEC SQL FETCH C INTO :fields;
>
> num_rows = sqlca.sqlerrd[2];
> }
>
> /* always return the number of rows found */
>
> return (num_rows);
>
> notfound:
> return (-UPA_DB_NO_REGS_FOUND);
> }

It is probably working as it should. Probably the desired rows are being fetched from the table. And then when no more rows are available, a 1403 no data found error occurs. Received on Wed Jan 08 1997 - 00:00:00 CST