Skip to main content

How to do a prepared SQL select

  • February 17, 2017
  • 9 replies
  • 0 views
A
  • Anonymous

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


#COBOL
#PostGRESQL
#SQL

    9 replies

    D

    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


    #COBOL
    #PostGRESQL
    #SQL

    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

    A
    • Anonymous
    • February 17, 2017

    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


    #COBOL
    #PostGRESQL
    #SQL
    This works.
    Thanks a lot
    D

    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


    #COBOL
    #PostGRESQL
    #SQL

    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.

    Chris Glazier
    Forum|alt.badge.img+2

    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


    #COBOL
    #PostGRESQL
    #SQL

    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

    D

    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


    #COBOL
    #PostGRESQL
    #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.

    D

    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


    #COBOL
    #PostGRESQL
    #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.

    Chris Glazier
    Forum|alt.badge.img+2

    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


    #COBOL
    #PostGRESQL
    #SQL

    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:

    D

    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


    #COBOL
    #PostGRESQL
    #SQL
    Thanks Chris, that's what I figured. This was someone else's post but I was interested in furthering my understanding.
    Cheers, Linden.
    A
    • Anonymous
    • February 17, 2017

    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


    #COBOL
    #PostGRESQL
    #SQL
    Thanks Chris, for the precision.
    Regards,
    Alain

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement