SQL and RPG 400 (SQLRPG) Variable lengths help required.

15 pts.
Tags:
SQL
SQLRPGLE
Please could someone help me with the following, I am new to SQL and have been tasked with the following:- I am writing an RPG program where the user will be able to do a search by entering up to 15 characters to find part numbers. I know how to do this using SQL but I am struggling with this when I embed the SQL into my RPG program. i.e if the user wants to find all part numbers with 175 in, the SQL would be PNUM like '%175%' I am assuming I need to change the '%175%' to a variable name i.e SQL would be PNUM Like :PNVAR I have defined PNVAR as 15 Characters long because the user could put up to 15 Chars in the search field. When I create my String from the user input %175% and then move it into PNVAR the field contains the following:- '%175% ' which is incorrect. As far as I'm aware you cannot create a variable length field in RPG, but can you create one in SQL as however many characters the user enters this is the number of chars +%% that the PNVAR needs to be. Am I even on the right lines... who knows? Help!

Answer Wiki

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

The answer is to build the complete SQL statment on the fly.

Here is a sample

begsr GetPageCount;

// get the page count for the file
// set the select statment
sqlStmt = ‘select count(*) from CUSTLOG where pagctl = ‘ +
qt + ‘1’ + qt;

/end-free
*
c/exec sql
c+ PREPARE S1 FROM :sqlStmt
c/end-exec

========

Here is another sample
// build a record for the output member
if first = ‘Y';
stmt = ‘Update ‘ + %trim(libraryName) + ‘/’ +
%trim(fileName) + ‘ Set ‘;
else;
stmt = %trim(stmt) + ‘,';
endif;

c/exec sql
c+ execute immediate :STMT
c/end-exec

Discuss This Question: 6  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
  • Cwc
    You
      can
    create a variable length field in RPG. It's done by specifying the VARYING keyword in the definition. Just specify 15 as the length, since that would be its maximum. In your calculations, you could use the %Len( ) built-in-function to get the actual number of characters that were entered in the field.
    4,290 pointsBadges:
    report
  • AS400Kate
    Thanks... I have now sorted the problem with your help.
    15 pointsBadges:
    report
  • TomLiotta
    Am I even on the right lines... who knows? That depends whether the column in the database file is variable length or not. If the file has a fixed-length column, then you're not quite on the right track yet, especially if the user may want to enter a value such as '%175' instead of '%175%'. Tom
    125,585 pointsBadges:
    report
  • AS400rpgle
    How to print a PDF image which is stored in BLOB using SQL RPGLE?
    15 pointsBadges:
    report
  • philpl1jb
    Hi AS400rpgle Please start a new question for this unrelated task. Phil
    51,305 pointsBadges:
    report
  • philpl1jb
    Didn't see this question .. seems like it should have been solved something like this .. wrkVar = '''%' + %trim(PNVAR) + '%''' execsql Select * from myfile where PNUM Like :wrkVAR The two quotes leaves a single quote in actual field the third quote terminates the string so the field wkVar would contain '%123%' followed by a bunch of spaces which would be ignored Phil
    51,305 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