Re: Pro*C help needed

oshaughj_at_p4.cs.man.ac.uk (Jamie O'Shaughnessy) writes:

>I'm currently having a few problems writing an ORACLE application
>using Pro*C and wondered if any of you could help.
 

>I've got a table called BRONZES with a key attribute called ACC_NO.
 

>A user inputs an ACC_NO and I want to check if a tuple exists in
>the DB with that ACC_NO already.
 

>Easy : just do
 

>EXEC SQL SELECT ACC_NO FROM BRONZES WHERE ACC_NO = uservar_ACC_NO;
 

>if it returns something -> one already exists. The problem lies in
>that I have more than one table (BRONZES, AEGEANBA, TERRACOTTAS)
>and the user wants to input data into one of these. They choose
>the table, so I know what table they want to input data into and
>I know the ACC_NO they want to use. How do I check if something
>exists with that ACC_NO?????
 

>I guess I use a PREPARE, OPEN AND FETCH dynamic query and build
>a string up containing :
 

>"SELECT ACC_NO FROM tablename WHERE ACC_NO = :user_acc_no"
 

>and then
 

>EXEC SQL PREPARE S1 FROM :above_string;
>EXEC SQL DECLARE C1 CURSOR FOR S1;
>EXEC SQL OPEN C1;
>EXEC SQL FETCH C1 INTO :variable_of_acc_no_type;
 

>Is this the right idea???? Come on someone, give us a clue!!!!
 

>- Jamie O'Shaughnessy

Here is a possible solution:

EXEC SQL BEGIN DECLARE SECTION;
  VARCHAR cmd[80];
EXEC SQL END DECLARE SECTION;
...
cmd.len = sprintf(cmd.arr,"SELECT ACC_NO FROM %s WHERE ACC_NO = %d",tname,key); EXEC SQL EXECUTE IMMEDIATE :cmd;
if(sqlca.sqlcode == 1427) /* TOO MANY ROWS */ ...

Be sure you don't have an EXEC SQL WHENEVER SQLERROR ... active otherwise the if statement will never be reached.

--
Stephane Hamel - SHamel_at_Mais.Hydro.Qc.Ca	| AutoControle Inc.
Analyst/Programmer      			| Montreal, Quebec, Canada
"Traped in the inifinite loop of learning..."	| Tel.: 1 514 858 7882