Highlighted
Respected Contributor.
Respected Contributor.
2374 views

How to do a prepared SQL select

Jump to solution

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

Tags (3)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: How to do a prepared SQL select

Jump to solution

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

View solution in original post

0 Likes
8 Replies
Highlighted
Absent Member.
Absent Member.

RE: How to do a prepared SQL select

Jump to solution

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

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

RE: How to do a prepared SQL select

Jump to solution
This works.
Thanks a lot
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: How to do a prepared SQL select

Jump to solution

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.

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: How to do a prepared SQL select

Jump to solution

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

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: How to do a prepared SQL select

Jump to solution

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.

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: How to do a prepared SQL select

Jump to solution

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:

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: How to do a prepared SQL select

Jump to solution
Thanks Chris, that's what I figured. This was someone else's post but I was interested in furthering my understanding.
Cheers, Linden.

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

RE: How to do a prepared SQL select

Jump to solution
Thanks Chris, for the precision.
Regards,
Alain
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.