Problems with dynamic SQL in PRO*C
Date: Wed, 9 Mar 1994 03:21:47 GMT
Message-ID: <MIKEW.94Mar9162147_at_pooh.dosli.govt.nz>
I have a PRO*C program which needs to retrieve all columns of specified tables, ie. I don't know in advance what the column names are. I'm attempting to use PRO*C's descriptor (SQLDA) variables to retrieve rows from a query, but keep getting the error:
ORA-03115: unsupported network datatype or representation
on the first fetch. I've appended a sample program which provokes this behaviour ... it executes its first argument as an SQL query, then attempts to fetch all selected rows. Note that it has a few limitations:
- the database name & password are hard-coded
- only character columns can be selected
- memory allocation is not checked but it demonstrates the problem. An example execution of this program returns the following:
+------------------------------------------------------------------------
| mikew_at_pooh$ ./desc_test "select ADDRESS from PARCEL"
| Allocating 12 bytes for ADDRESS
| ORA-03115: unsupported network datatype or representation
| mikew_at_pooh$
+------------------------------------------------------------------------
I assume that what knowledge I've been able to scavenge from the PRO*C manual is somewhat lacking (but hey, there's not even a code example!) Can anyone tell me what I'm doing wrong???
Mike Williams, Dept of Survey & Land Information, NZ. Email: <mikew_at_gopher.dosli.govt.nz> Phone: +64 4 471 0380 --- desc_test.pc ----------------------------------------------------------#include <stdlib.h>
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
extern SQLDA *sqlald(); /* Allocate descriptor */ extern void sqlprc(); /* Extract precision and scale */ extern void sqlnul(); /* Test/set NOT NULL attribute */ EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid[20]; VARCHAR passwd[20];
EXEC SQL END DECLARE SECTION; main (argc, argv)
int argc;
char *argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR select [4096];
int feat_num[200];
EXEC SQL END DECLARE SECTION;
SQLDA *select_da;
int col;
strcpy (uid.arr, "CHCH"); uid.len = strlen (uid.arr); strcpy (passwd.arr, "TEST"); passwd.len = strlen (passwd.arr); EXEC SQL CONNECT :uid IDENTIFIED BY :passwd;
/* Create query string */
strcpy (select.arr, argv[1]);
select.len = strlen (select.arr);
/* Prepare & execute query */
EXEC SQL WHENEVER SQLERROR GOTO error;
EXEC SQL PREPARE S FROM :select;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C;
/* Load select descriptor */
select_da = sqlald (40, 30, 30);
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_da;
/* Prepare return areas */
for (col = 0; col < select_da->F; col++) {
select_da->S[col][select_da->C[col]] = '\0';
fprintf (stderr, "Allocating %d bytes for %s\n",
select_da->L[col], select_da->S[col]);
/* Allocate space for the data values and indicator variables */
select_da->V[col] = malloc (select_da->L[col]);
select_da->I[col] = (short *) malloc (sizeof (short));
}
/* Fetch rows */
EXEC SQL WHENEVER NOT FOUND GOTO end_rows;
for (;;) {
EXEC SQL FETCH C USING DESCRIPTOR select_da;
printf ("\n");
for (col = 0; col < select_da->F; col++) {
printf ("%s = '%s'\n", select_da->S[col], select_da->V[col]);
}
}
end_rows:
/* Deallocate space for data values and indicator variables */ for (col = 0; col < select_da->F; col++) {
fprintf (stderr, "type %s = %d\n",
select_da->S[col], select_da->T[col]);
free (select_da->V[col]);
free (select_da->I[col]);
}
/* Deallocate descriptor */
sqlclu (select_da);
EXEC SQL CLOSE C;
return 1;
error: /* Error */
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
fprintf (stderr, "%s", sqlca.sqlerrm.sqlerrmc);
exit (1);
}
Received on Wed Mar 09 1994 - 04:21:47 CET