Thread: Pro *C

  1. #1
    Registered User
    Join Date
    Jul 2004
    Posts
    9

    Pro *C

    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

  2. #2
    Registered User glUser3f's Avatar
    Join Date
    Aug 2003
    Posts
    345
    Check out this example.

  3. #3
    Registered User
    Join Date
    Jul 2004
    Posts
    9
    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.

  4. #4
    Registered User glUser3f's Avatar
    Join Date
    Aug 2003
    Posts
    345
    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;

  5. #5
    Registered User
    Join Date
    Jul 2004
    Posts
    9
    Thank you very much for the suggestion, and do you know of another method?

  6. #6
    Registered User glUser3f's Avatar
    Join Date
    Aug 2003
    Posts
    345
    Nope, sorry, I don't know if there's an if-like way to do it.

  7. #7
    Goscinny or Uderzo?
    Join Date
    Jun 2004
    Posts
    33

    Smile

    Don't think there's another way to do it Pro*C but you could could use sqlplus with a command like
    Code:
    System(sqlplus -s user_name/password @script.sql > output.log 2>&1)
    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(*))

    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?)

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Differing versions of XP Pro?
    By Davros in forum A Brief History of Cprogramming.com
    Replies: 2
    Last Post: 10-02-2002, 11:19 AM
  2. Home vs. Pro
    By DavidP in forum A Brief History of Cprogramming.com
    Replies: 5
    Last Post: 01-08-2002, 06:37 AM
  3. Visual c++ / Visual Fox Pro
    By unanimous in forum C++ Programming
    Replies: 0
    Last Post: 12-25-2001, 01:32 PM