How to do a prepared SQL select

Hello,

I'm trying to do a prepared select but i got a compiler error message 

** External Compiler Module message
** ES0100 Incorrect SQL statement syntax near: into

what i've done is:

move "select pays from medecins where toubib = :fdbiljour-i8-toubib" to Stmtbuf
exec sql prepare stm_pays_medecin from :Stmtbuf end-exec
exec sql execute stm_pays_medecin into :wss-x2-pays using :wss-i8-toubib end-exec

I see in the documentation that there's no "into" in the "execute" syntax

So, how should i write this ?
This request only returns 1 line.

I'm using PostgreSQL

Thanks for asnwering

  • Hi there,

    I think you would usually use prepare followed by, "exec sql open cursor..." and then "exec sql fetch into...".

    As you are expecting one row, simpler to use a single "exec sql select..."

    EXEC SQL

        SELECT pays

        INTO :wss-x2-pays

        FROM medecins

        WHERE toubib = :fdbiljour-i8-toubib

    END-EXEC.

    I am using Visual COBOL in Windows.

    All the best,

    Linden

  • Good. No problem.  I am doing a lot of work with exec sql at the moment but not so much with 'dynamic' SQL. I have just been experimenting with it.

    MicroFocus staff may correct me here but... I don't think you can do a simple "exec sql execute..." for a select statement. To fetch a single row using dynamic sql (i.e. where you create your sql code in a variable at run time), it's quite convoluted versus the simple 'embedded' select above. You have to do something like this...  

    MOVE "select myColumn from myTable where blah blah blah" TO mySQL.

    exec sql prepare myPreparedSQL from :mySQL end-exec.
    exec sql declare myCursor cursor for myPreparedSQL end-exec.
    exec sql open myCursor end-exec.
    exec sql fetch myCursor into :myResultStore end-exec.
    exec sql close myCursor end-exec.

    All the best, Linden.

  • Verified Answer

    Only Oracle Pro*COBOL supports the use of host variables directly in a prepared statement. If you are using the OpenESQL precompiler then you need to use question marks as parameter markers instead of host variables.

    Please see documentation here:

    I did a video on this some time ago that may also be of some help.

    Visual COBOL in a NutShell: Using Dynamic SQL

  • Thanks Chris. Am I interpreting the docs right i.e. that you can't do a dynamic select statement without a cursor?

    I tried this but i couldn't get it to work so I'm assuming select only works when using cursor/fetch in dynamic mode? Is that right?

    move "select col from table limit 1" to mySQL.

    exec sql prepare myPreparedSQL from :mySQL end-exec.

    exec sql execute myPreparedSQL using :myResultVariable end-exec.

    Is 'using' just for passing parameters in (for '?' markers) rather than getting results back?

    Thanks, Linden.

  • Thanks Chris. Am I interpreting the docs right i.e. that you can't do a dynamic select statement without a cursor?

    I tried this but i couldn't get it to work so I'm assuming select only works when using cursor/fetch in dynamic mode? Is that right?

    move "select col from table limit 1" to mySQL.

    exec sql prepare myPreparedSQL from :mySQL end-exec.

    exec sql execute myPreparedSQL using :myResultVariable end-exec.

    Is 'using' just for passing parameters in (for '?' markers) rather than getting results back?

    Thanks, Linden.

  • Yes if you are returning a result then you must use a cursor when using dynamic SQL. The USING on the open is to provide parameters for any parameter markers you have specified with '?'. The result is returned using an INTO phrase on the FETCH.

    Example found here:

  • Thanks Chris, that's what I figured. This was someone else's post but I was interested in furthering my understanding.
    Cheers, Linden.
  • Thanks Chris, for the precision.
    Regards,
    Alain