Like in a Prepared SQL Statement

[devinst]

Is there a way to use a LIKE predicate in a prepared SQL Statement with variable substitution?

SQLStmt = 'select chCustNm, shaOlC, shaSNo, shaSNm, lsCounty, +
lsOperator, lsActCode +
from Cust +
where ((? = '' '' ) or +
(shaSNm like ''%?%'') )';

exec sql
open LeaseList_C using :f1LeaseNme, :f1LeaseNme;

f1LeaseNme contains the information entered by the user. This seems to translate to actually looking for a '?' in the name. I did test it by changing shaSNm to being with a ? in the file and it did show up.

I know that I can just concatenate that section of the where statement but would like to know if anyone has gotten this to work?

Thanks.!!!

[Glenn]

Devin,

I don't have a quick way to test but I can make 2 suggestions.

1 - Try rewriting your like statement to concatenate the '%' symbols to the marker - ex: (shaSNm like ''%" || ? || "%'').
2 - If #1 doesn't work, maybe create a second host variable that has the '%' symbols added to both ends of the value being searched for (f1LeaseNme2 = '%' + %trim(f1LeaseNme) + '%';) and make the 'like' selection look like this: (shaSNm like ?).

For either one of these, keep in mind that with the LIKE predicate the SQL engine will likely have to do a full table scan.

Glenn

[Scott Klement]

Yeah, Glenn has the right idea...

You can't do LIKE '%?%' because this will search for strings containing the ? character. In other words, you can't put the ? inside the quotes -- it'll think it's the character you want to search for (rather than a parameter marker) if it's in the quotes. You need it outside the quotes, such as LIKE '%' concat ? concat '%'

Also... you should NOT begin a variable name with 'SQL'. IBM reserves variables beginning with 'SQL' for the embedded SQL precompiler. When they add new features to the precompiler in future releases or technology refreshes, they might add new variables beginning with 'SQL' -- if some day they add a variable named 'SQLSTMT' it will break your code. So you should not start variables with the letters 'SQL'. Instead, just use a different name like 'STMT' or 'MYSTMT' or even 'mySQLStmt'.... just so that the first 3 letters aren't 'SQL'.

One more thing... you said "I know that I can just concatenate that section of the where statement". I just want to point out that you shouldn't concatenate this section of the where statement -- that's not a good practice because it opens your code up to SQL injection attacks. You should always use parameter markers (or host variables) for variables that have data that could come from a user or anything else that you don't completely control in your program. I know that you were already trying to avoid concatenating -- so you're on the right track, but I wanted to emphasize that it's not okay to concatenate here, it can open up big security holes. You are on the right track with using a parameter marker, so please keep thinking that way...

With all of that in mind, I've written up a quick sample program for you. This uses the PRODP file that's in our PUISAMPLES library rather than the CUST file from your code -- but otherwise, I think it's what you're looking for. Give it a try:

Code: Select all

         ctl-opt dftactgrp(*no) actgrp(*new);

         dcl-s stmt varchar(100);
         dcl-s id packed(7: 0);
         dcl-s name char(30);
         dcl-s str varchar(32);
         dcl-s msg  char(52);

         exec SQL set option naming=*SYS,commit=*NONE;

         stmt = 'select prid, pname +
                   from PRODP +
                  where ?='' '' +
                     or pname like ''%'' concat ? concat ''%''';

         str = 'ABC';

         exec SQL prepare s1 from :stmt;
         exec SQL declare c1 cursor for s1;
         exec SQL open c1 using :str, :str;

         exec SQL fetch from c1 into :id, :name;
         dow %subst(sqlstt:1:2)='00' or %subst(sqlstt:1:2)='01';
            msg = %char(id) + ' ' + name;
            dsply msg;
            exec SQL fetch from c1 into :id, :name;
         enddo;

         exec SQL close c1;
         *inlr = *on; 

You can use || instead of 'concat' if you like (like Glenn suggested) but, I recommend using 'concat' because the || characters sometimes have problems with international character sets... plus I think 'concat' is easier to read.

[devinst]

Scott, I made some changes to my program per your suggestions. And I do greatly appreciate your foresight to not name fields beginning with SQL.
You changes for the like statement work great if I enter the entire name of the location. The SQL statement, when executing, seems to include the trailing blanks at the end of the variable field. str in your example. I tried using %trim on the open statement, but not with much hope, but of course that did not compile. Any suggestions?

[Scott Klement]

The easy/best way is to define your variable as VARCHAR (or VARYING if you use fixed format D-specs) and %TRIM it prior to the OPEN CURSOR. My 'str' variable was defined this way already. Let's say, for example, the user is providing info in a field called 'search', which is fixed-length and coming from a display. You could do this:

Code: Select all

dcl-s varSearch varchar(32);
--  or older way  --
 D varSearch       s             32a   varying
  .
  .
varSearch = %trim(Search);
exec SQL open c1 using :varSearch, :varSearch;

Because the field is VARYING/VARCHAR you can trim it before the open call and it'll work fine.

The alternative... and I hesitate to mention this because I don't like using fixed-length fields for stuff like this, but... if you need compatibility with V4R1 or OPM RPG (shouldn't be an issue these days, really) then the other alternative is to use this old kludge:

Code: Select all

 D SearchLike      s             32a
  .
  .
stmt = 'select prid, pname +
          from PRODP +
         where ?='' '' +
          or pname like ?';

SearchLike = *ALL'%';
SearchLike = %trim(Search) + SearchLike;
exec SQL open c1 using :Search, :SearchLike;

This way is uglier and harder to understand, so I recommend using a VARYING/VARCHAR approach.

[devinst]

I would have laid money down that I tried the varchar. I even have the declaration statement. But this would not be the first time I was mistaken. And not the last for sure.

I did as you instructed Scott and it is working perfectly now. Thanks again for all your and Glenn's help with this.!!!!