SQLRPGLE – Prepare – Declare Cursor

895 pts.
Tags:
SQL Cursors
SQLRPGLE
I'm using Prepare, Declare SQL cursor in my SQLRPGLE program

After Declare statement I receive SQL -312 - Which indicates to me that the SQL statement is not declared correctly. Is it something wrong with my query. I ran this query in SQL editor and went fine. I dun understand why not in the program.

Given below is the SQL stmt snapshot in the debug mode.

EVAL SqlString                                                         SQLSTRING =                                                                      ....5...10...15...20...25...30...35...40...45...50...55...60      1   'SELECT RPPAT#, RPORD#, RPORDT, RPORTM, RPNDC, RPDNAM, RPORDR'     61   ', PHNAME FROM EMP1file INNER  join EMP2fil on rpndc = fxndc '    121   'INNER join EMP3fil on rpordr = nwdrnum where RPORDT >= :FMDA'    181   'T AND RPORDT <= :TODAT AND recid <> "D" and NWDRNUM = PHYNO '    241   'and RPNDC = "Executive O" and RPNDC <> "D" order by RPORDR, '    301   'upper(RPDNAM)                                               '    361   '                                                            '    421   '                                                            '    481   '                                                            '

ASKED: August 25, 2009  1:49 AM
UPDATED: August 26, 2009  2:39 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

What version of the OS are you using and what does your declare statement look like?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Nutangujar
    C/Exec SQL C+ Prepare P1 From :SqlString C/End-Exec *------------------------------------- C/EXEC SQL C+ Declare C1 Cursor For P1 C/End-Exec Actually i'm not using open cursor statement ...with the clause 'using'......to resolve this problem i used '?' instead of the :FMDat host variable and then opened this cursor using 'Using :Fmdat' clause and this worked.....then i got varibale not defined exception for constant used in the query i.e. "D" i moved this constant to a variable and used '?' and the same technique...now it is working... however i have lengthy code which opens cursor based on various selection parameters passed for a report. this make my program look ugly....is there any way i can write a smart code....
    895 pointsBadges:
    report
  • Teandy
    I'm not much of an SQL person, but I did find this thread which may help. http://itknowledgeexchange.techtarget.com/itanswers/rpg-with-dynamic-sql-problem-sqlcod-501312/ I think your problem is coming from trying to use a host variable in dynamic sql. Try changing: where RPORDT >= :FMDAT AND RPORDT <= :TODAT TO where RPORDT >= ' + quote + FMDAT + quote + ' AND RPORDT<= ' +quote + TODAT + quote. quote is defined as: D QUOTE C CONST(X'7D')
    5,860 pointsBadges:
    report
  • BigKat
    are you creating the SQLString in your program dynamically, or is it a fixed statement? If it is built, then instead of building it as
    eval  SQLString = "...RPORDT >= :FMDAT and..." 
    build it as
    eval SQLString = "...RPORDT >= " + %char(FMDAT) + " and..."
    this creates an SQL statement fro prepare/declare that has no host variables in it
    7,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following