I have a SQL select statement that may or may not return a value. I need to know how to determine if the query returned nothing, because if that is the case the program needs to jump into an IF statement containing an INSERT SQL block
I have a SQL select statement that may or may not return a value. I need to know how to determine if the query returned nothing, because if that is the case the program needs to jump into an IF statement containing an INSERT SQL block
Check out this example.
I saw that, I guess what I need is this:
EXEC SQL SELECT xyz INTO :x FROM table
if(NO RECORDS ARE RETURNED)
{
INSERT xyz into table
}
The part that I need is the "if(NO RECORDS ARE FOUND)". Do I check for a null value in the ":x"? Or is a flag of some sort automatically set when no values result from a select statement.
Code:EXEC SQL WHENEVER NOT FOUND GOTO notfound; EXEC SQL SELECT xyz INTO :x FROM table; // return to avoid executing the insert statement notfound: EXEC SQL INSERT xyz INTO table;
Thank you very much for the suggestion, and do you know of another method?
Nope, sorry, I don't know if there's an if-like way to do it.
Don't think there's another way to do it Pro*C but you could could use sqlplus with a command like
This would allow you to use PL/SQL with IF statements in the script and also to use the EXCEPTION functionality to customise the output of any errors within the script. Apart from being able to determine if an error occured, you could find out what error occured as well and whether or not any records were found/added/updated using select statements and the count(*) function (possibly using a cursor with a SELECT INTO instead of using count(*))Code:System(sqlplus -s user_name/password @script.sql > output.log 2>&1)
Parsing the output file (which because you can control all output between sqlplus commands and PL/SQL commands should be a simple task) then gives you far more meaningful output from your SQL.
This may be too much for what you want to do, but if you're using Pro*C at all, then this method could be a useful possibility for you to use in future programs if not in this case. Also, despite the fact that I have tried to describe several things you OCULD do, you could keep it as simple as only covering the EXCEPTION OTHER to determine if the script failed and a SELECT count(*) to determine what work (if any) was done.
Hope this is of some use to you.
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
(How much wood would a wood chuck cut if a wood chuck could chuck wood?)