Re: SQL with parameter marker -- RPG400-L

Hi Peter,

just a minor correction:

Plese remove the quotes around the parameter markers. Even if the field ischaracter or date quotes around the parameter marker are not allowed or willreturn unexpected results.


SQLStatement = 'SELECT * FROM MYFILE WHERE MYFIELD = ? +
          and MYFIELD2 = ?';

Just an other warning:

Please do not use host variable names that begin with SQL, SQ, RDI or DSN.These variable names names are reserved for the database manager.Even if you have no problems today, in an future release IBM may use a fieldin its precomiler with the name of SQLStatement, and you program either maynot compile any more or you'll get inexpedted results after recompiling.


Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (LesBrown)

"If you think education is expensive, try ignorance." (Derek Bok)

----- Original Message -----From: "Peter Levy" <plevy@xxxxxxxxxxxx>

To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, September 27, 2006 20:48
Subject: Re: SQL with parameter marker

    D SQLStatement    s            100a
    D Field1          s              5p 0
    D Field2          s             10a
      /free
       SQLStatement = 'SELECT * FROM MYFILE WHERE MYFIELD = ? +
          and MYFIELD2 = ''?''';
       Field1 = 10;
       Field2 = 'TEST';
       exec sql declare MYFILE_CURSOR cursor for MYSELECT;
       exec sql prepare MYSELECT from :SQLStatement;
       if SQLSTATE <> '00000';
          // Error recovery.
       endif;
       exec sql open MYFILE_CURSOR using :Field1, :Field2;
       if SQLSTATE <> '00000';
          // Error recovery.
       endif;
       // etc...
      /end-free

Hope this helps.

Jeff Young wrote:

Does anyone have an example of using parameter markers in a SQL RPGLEprogram?
Thanks,


Jeff Young
jyoung@xxxxxxxxxxxx
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
IBM e(logo)server Certified Systems Expert - iSeries Technical SolutionsV5R2IBM Certified Specialist- eServer i5Series Technical Solutions DesignerV5R3IBM Certified Specialist- eServer i5Series Technical SolutionsImplementer V5R3IBM Certified Systems Expert -- eServer i5 iSeries Technical SolutionsV5R3
IBM e(logo) Certified Specialist - p5 and pSeries Technical Sales Support


--
Peter Levy
Alliance Shippers, IT Dept
Englewood Cliffs, NJ
Voice: 201-227-0400
Fax: 201-227-0925
Email: plevy@xxxxxxxxxxxx
AIM: pklevyalliance2

---------------
There are 10 kinds of people in the world;
Those who understand binary and those who don't.



--------------------------------------------------------------------------------

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.