Variable not found when calling a SQLRPGLE program

690 pts.
Tags:
AS400 - Embedded SQL
CL Program
SQLRPGLE
Variable not found when calling a SQLRPGLE program with parameters in CL program CALL PGM(LIBXYZ/TESTRPG1) PARM(&LIBNAM &FILNAM) here LIBNAM AD FILNAM are passed with VALUES to the SQLRPGLE program and at sql statemet ie. insert INTO LIB/FILl( LIB is having the value and FILL is not getting value, throwing as variable not found.

Software/Hardware used:
as400
ASKED: June 7, 2013  12:52 PM
UPDATED: August 22, 2013  7:49 PM

Answer Wiki

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

Discuss This Question: 17  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
  • ravula
    please help me ,how can i proceed further in this
    690 pointsBadges:
    report
  • Splat
    My first question would be whether the values being loaded by the calling program are in fact making it to the program being called. Do you have any job logs or program dumps available that might shed some light on this?
    6,255 pointsBadges:
    report
  • ToddN2000
    How is the CL program called? How are the parameters defined? Try debugging the CL program and see what the parameter values are just before the call to the RPGLE program. Can you post the code and we can see what you have tried?
    6,360 pointsBadges:
    report
  • ravula
    from cl program calling the SQLRPGLE program with 2 parameters one library and another is file, when tried to debug the program i could see the values are received in the SQLRPGLE program but in the SQL statemets /EXEC SQL + insert INTO LIB001/File01( select * from LIB002/File02) /END-SQL i am getting variable not found here in File01 but i am getting value in lIB001 this is some example but it is not the exact program .
    690 pointsBadges:
    report
  • ravula
    calling from the cl program CALL PGM(LIBXYZ/TESTRPG1) PARM(&LIBNAM &FILNAM) ============================================= *ENTRY PLIST PARM LIB001 10 PARM FILE001 10 /EXEC SQL + insert INTO LIB001/File01( select * from LIB002/File02) /END-SQL ========================= what could be the reason here for file name is not getting in File01,it is throwing variable not found, where as LIb001 value is getting
    690 pointsBadges:
    report
  • ravula
    what is the step i need to take care ,it would help me in going forward to the program, i am stuck here
    690 pointsBadges:
    report
  • philpl1jb
    Wonder what happened to my last response This must be a dynamic SQL You need to create a string .. including the actual file name from the input parameter and then use that.
    48,565 pointsBadges:
    report
  • TomLiotta
    what could be the reason here for file name is not getting in File01,it is throwing variable not found, where as LIb001 value is getting
    .
    I can't see anything in the code that you showed that connected the parameter values to the SQL. The values in the CL variables don't seem to have anything to do with the SQL.
    .
    Are you showing the exact source statements? There are no variables in the SQL statement. If you don't copy and paste the exact source statements, we can't tell what might be wrong. If those are exact copies, then File01 isn't a variable. It's just a literal constant in the source that showed to us.
    .
    Tom
    125,585 pointsBadges:
    report
  • ravula
    DCL VAR(&LIBNAM1) TYPE(*CHAR) LEN(10) VALUE('LUKAM') DCL VAR(&FILNAM1) TYPE(*CHAR) LEN(10) VALUE('pf02') DCL VAR(&LIBNAM2) TYPE(*CHAR) LEN(10) VALUE('LUKAM2') DCL VAR(&FILNAM2) TYPE(*CHAR) LEN(10)VALUE('pf03') CALL PGM(LUKAM/TESTRPG1) PARM(&LIBNAM1 &FILNAM1 &LIBNAM2 &FILNAM2) ===================TestRPG1 sqlrpgle program C *ENTRY PLIST C PARM LIB 10 C PARM FILE1 10 C PARM LIB2 10 C PARM FILE2 10 /EXEC SQL + insert INTO LIB/File1( select * from LIB2/File2) /END-SQL ============== how can we move this to dynamic sql any example for inserting into anothre file ,by selecting the records from the file through dynamic sql
    690 pointsBadges:
    report
  • philpl1jb
    this should give you an idea of one way to do it... Where myChar128 is a alpha field large enough to fit the SQLString myChar128 = 'INSERT INTO ' + %TRIM(lib) + '/' + %trim(file1) + ' (SELECT * from ' + %TRIM(lib2) + '/' + %trim(file2) + ')' ; EXEC SQL EXECUTE IMMEDIATE :myChar128; or in fixed format C Eval myChar128 = 'INSERT INTO ' + %TRIM(lib) + '/' + %trim(file1) + ' (SELECT * from ' + %TRIM(lib2) + '/' + %trim(file2) + ')' C\EXEC SQL C+ EXECUTE IMMEDIATE :myChar128 C\END-SQL
    48,565 pointsBadges:
    report
  • ravula
    Thanks philip its worked fine now DLIB S 10A DFIL S 10A DLIB2 S 10A DFIL2 S 10A Dsqlstring s 1024a C *entry plist C parm LIB C parm FIL C parm LIB2 C parm FIL2 C /free sqlstring = ‘Insert into ‘ + %trim(LIB2)+’/'+ %trim(FIL2)+ ‘(select TNO, TNAME from ‘ + %trim(lib)+’/'+ %trim(fil)+’)’ ; Exec sql Execute Immediate :sqlstring ; /end-free c eval *inlr=*on c return
    690 pointsBadges:
    report
  • ravula
    But now when i adding the condition with,” where Tno like ‘_8%’ ” i am facing a issue, could you please help in this
    690 pointsBadges:
    report
  • philpl1jb
    1. Make sure variable sqlstring is long enough 2. see changes below 3. like '‘ <-- double quotes tells the system this is not a string termination quote, it is to be a quote in the string 4. ''' at end of string will put a quote in the string and then terminate it. sqlstring = ‘Insert into ‘ + %trim(LIB2)+’/’+ %trim(FIL2)+ ‘(select TNO, TNAME from ‘ + %trim(lib)+’/’+ %trim(fil)+’)’ + ' where Tno like '‘_8%’'';
    48,565 pointsBadges:
    report
  • philpl1jb
    3. like ‘‘ <– two single quotes 4. ''' three single quotes at end of string will put a quote in the string and then terminate it. Or create a const q with a quote in it ... ''' then sqlstring = ‘Insert into ‘ + %trim(LIB2)+’/’+ %trim(FIL2)+ ‘(select TNO, TNAME from ‘ + %trim(lib)+’/’+ %trim(fil)+’)’ + ' where Tno like ' + q + '_8%’ + q;
    48,565 pointsBadges:
    report
  • TomLiotta
    ...i am facing a issue, could you please help in this
    .
    What issue? We could help a lot easier if you told us what the "issue" is. Simply saying that you have an issue doesn't us anything useful.
    .
    Tom
    125,585 pointsBadges:
    report
  • ravula
    Thanks Philip for your valuable time , i am able to complete my requirement now Thank you all !!!
    690 pointsBadges:
    report
  • philpl1jb
    Thanks for the feedback
    48,565 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