[comp.databases] INGRES ESQL question.

gordon@mead.UUCP (Gordon Edwards) (09/05/90)

According to the ESQL documentation the statement_name in PREPARE can be a
string constant or a host string variable.

This works:

	char dsql[100];

	strcpy(dsql, "select a, b from y");

	EXEC SQL PREPARE s FROM :dsql;

This does not:

	char dsql[100];
	char *s;

	strcpy(dsql, "select a, b from y");

	EXEC SQL PREPARE :s FROM :dsql;

I did a similar thing for declaring cursors as host string variables (see
the test program below) which worked.  There are no examples of statement_
name as a host string variable.

Has anyone ever used this construct?  Would an Ingres person like to comment?

Below is the entire test program:

EXEC SQL INCLUDE sqlca;

main()
{
	EXEC SQL BEGIN DECLARE SECTION;
	int a_val, b_val;
	char *c, *s;
	char dsql[100];
	EXEC SQL END DECLARE SECTION;

	EXEC SQL CONNECT 'iilibdb';

	strcpy(dsql, "select a, b from y");

	EXEC SQL PREPARE :s FROM :dsql;
			 ^^ works if just ... PREAPRE s FROM ...

	EXEC SQL DECLARE :c CURSOR FOR :s;
				       ^^ works if just ... FOR s;

	EXEC SQL OPEN :c;

	do {
		EXEC SQL FETCH :c INTO :a_val, :b_val;

		printf("\ta = %d\tb = %d\n", a_val, b_val);
	} while (sqlca.sqlcode == 0);

	EXEC SQL CLOSE :c;
	
	EXEC SQL DISCONNECT;
}

Thanks.

--
Gordon Edwards                                    Mead Data Central, Dayton OH

robf@squid.rtech.com (Robert Fair) (09/08/90)

>gordon@mead.UUCP (Gordon Edwards) has problems using a program
variable as a statement name is dynamic SQL, and wonders if it should
work or not. Although he doesn't give an exact Ingres version or 
error, this construct should work OK at least Ingres 6.2 and 6.3

Looking at the example program Mr Edwards provided, the problem may be 
no initialization of the variable containing the statement name, so Ingres was 
passed a NULL or garbage statement name - hence  causing an error.
To make the program work, simply initialize the statement and cursor 
variables to the names you want to use, e.g.  I added the  following lines 
to the example and it worked fine:

	s="stat1"; 	/* name of dynamic statement to use */
	c="cur1";	/* name of dynamic cursor to use */

[For those who are interested, when you use a program variable for a 
 cursor or statement name, the runtime value is used for the corrosponding
 object name - here we are calling the statement 'stat1' and the cursor 'cur1'.]

Robert L. Fair
Premium Support Group
Ingres Corporation, NJ


>Below is the entire test program:
>
>EXEC SQL INCLUDE sqlca;
>
>main()
>{
>	EXEC SQL BEGIN DECLARE SECTION;
>	int a_val, b_val;
>	char *c, *s;
>	char dsql[100];
>	EXEC SQL END DECLARE SECTION;
>
>	EXEC SQL CONNECT 'iilibdb';
>
>	strcpy(dsql, "select a, b from y");
>
>	EXEC SQL PREPARE :s FROM :dsql;
>			 /* ^^ works if just ... PREAPRE s FROM ...*/
>
>	EXEC SQL DECLARE :c CURSOR FOR :s;
>				      /* ^^ works if just ... FOR s;*/
>
>	EXEC SQL OPEN :c;
>
>	do {
>		EXEC SQL FETCH :c INTO :a_val, :b_val;
>
>		printf("\ta = %d\tb = %d\n", a_val, b_val);
>	} while (sqlca.sqlcode == 0);
>
>	EXEC SQL CLOSE :c;
>	
>	EXEC SQL DISCONNECT;
>}
>
>Thanks.
>
>--
>Gordon Edwards                                    Mead Data Central, Dayton OH