SQL and RPG 400 (SQLRPG) Variable lengths help required.
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!
Software/Hardware used:
Software/Hardware used:
ASKED:
March 20, 2010 8:00 PM
UPDATED:
July 8, 2010 10:54 AM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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.
Thanks… I have now sorted the problem with your help.
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
How to print a PDF image which is stored in BLOB using SQL RPGLE?
Hi AS400rpgle
Please start a new question for this unrelated task.
Phil
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