SQL Error when running JumpStart generated code for expanding SubFile
Posted: Fri May 06, 2016 10:24 am
by mdruk1986
Hello,
I used JumpStart to generate a page at a time SubFile with a search field. The Subfile loads and displays correctly. However, when i enter a search term it throws a SQL System error which i have attached. However, when i manually code in the search term or use a variable instead of parameter marker it works. Please see rpg code below. Pretty sure the problem is how i am handling the "SearchTerm" variable but do not see how to fix it.
Any Ideas?
I used JumpStart to generate a page at a time SubFile with a search field. The Subfile loads and displays correctly. However, when i enter a search term it throws a SQL System error which i have attached. However, when i manually code in the search term or use a variable instead of parameter marker it works. Please see rpg code below. Pretty sure the problem is how i am handling the "SearchTerm" variable but do not see how to fix it.
Any Ideas?
RPG CODE
****************************************************
** Procedure: Open_SQL **
** Procedure Prepare, Declare and Open SQL cursor **
** Parms: **
** None **
** Return: **
** None **
****************************************************
P Open_SQL B
D Open_SQL PI N
D PrevSortField...
D like(Ctl_Fields.Sortfield)
D Statement S 4096A varying
D SearchTerm S 23A varying
/Free
EXEC SQL
Set Option
Naming = *Sys,
Commit = *None,
UsrPrf = *User,
DynUsrPrf = *User,
Datfmt = *iso,
CloSqlCsr = *EndMod ;
// Original jumpstart generated command immediately below
//SearchTerm = '%' + %Trim(Ctl_Fields.Search) + '%' ;
SearchTerm = '''' + %Trim(Ctl_Fields.Search) + '%''' ;
Statement = 'SELECT * FROM UBAMF ' ;
If CTL_Fields.Search <> *Blanks ;
Statement += 'WHERE UNAME LIKE ? ' ;
// Statement += 'WHERE UNAME LIKE' + SearchTerm + ' ' ; this works
// Statement += 'WHERE UNAME LIKE ''SMITH%'' '; this works
EndIf;
Statement += Order_By(PrevSortField);
Statement += ' FOR FETCH ONLY';
EXEC SQL
PREPARE S1 FROM :Statement ;
If SQLCODE < 0;
Return *Off;
EndIf;
EXEC SQL
DECLARE C1 SCROLL CURSOR FOR S1 ;
If SQLCODE < 0;
Return *Off;
EndIf;
EXEC SQL
// OPEN C1 ;
OPEN C1 USING :SearchTerm ;
// :SearchTerm
// ;
If SQLCODE < 0;
Return *Off;
EndIf;
Db_rrn = 1;
Ctl_Fields.EnableUp=*Off;
Ctl_Fields.EnableDown=*On;
Ctl_Fields.PageNo = 1;
return *on;
/End-Free
P E