Pro C dynamic SQL query

Question

I have to execute the following query using Pro C to get the output and den display the output to the user.

i tried the following code snippet:

int count=0;
char query1[100]="select count(code) from customer where customer_type='a';";
EXEC SQL ALLOCATE DESCRIPTOR 'out' ;
EXEC SQL PREPARE statement FROM :query1 ;
EXEC SQL DESCRIBE OUTPUT statement USING DESCRIPTOR 'out' ;
EXEC SQL SET DESCRIPTOR 'out' VALUE 1 TYPE = :data_type, 
    LENGTH = :data_len, DATA = :count ;
EXEC SQL DECLARE c CURSOR FOR statement ;
EXEC SQL OPEN c ;
EXEC SQL FETCH c INTO DESCRIPTOR 'out' ;
EXEC SQL GET DESCRIPTOR 'out' VALUE 1 :count = DATA;
EXEC SQL CLOSE c ;
printf("%-8d ",count);

but the output i get is always 0.

How shall i proceed to get the proper output?? can anyone help pls...

Answer 1

It is quite possible you have some errors in there that arn't getting noticed.

Use the EXEC SQL WHENEVER to get some error checking going on.

The one thing that jumps out at me is the semicolon at the end of the query1 value. If I recall correctly, Pro*c will barf on it.

Answer 2

I would strongly recommend against using this method of Pro*C dynamic SQL (Oracle dynamic SQL method 4) unless you can possibly avoid it.

The only case you should need to use this method is when you are using dynamically generated SQL and you don't know how many host variables will be used. E.g. You don't know how many columns will be in the SELECT clause.

A fully fledged example of using Oracle dynamic SQL method 4 can be found at http://docs.oracle.com/cd/B28359_01/appdev.111/b28427/pc_15ody.htm#i7419.