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