REPLACE in SELECT_SQL Where

[jyoung]

I am trying to use REPLACE in a SELECT_SQL where clause but keep getting the dreaded "Unrecoverable error occurred while executing the Web application routine." error.

I have a feeling it is something to do with the quotes. If I remove the replace it works fine. Any one use REPLACE with select_sql? Any ideas on how to make it work?

Code: Select all

#STD_TEXT := #CLOFN.UpperCase.Concat( "%" )
select_sql fields(#CLOFID #CLOD #CLOCN #CLOFN #CLOIN) from_files((AF1155)(XF1140)) where('(SUBSTRING(CAST(#CWDWP AS VARCHAR(8)), 1, 4) = :wk_YearString) AND (REPLACE(#CLOFN, '' '', '''') LIKE :STD_TEXT) AND (#CWOFID = #CLOFID) AND (#CWOFD = #CLOFD) AND (#CWOCN = #CLOCN) AND (#CWOD = #CLOD) AND (#CWELVL = 0) AND (#CLELVL = 0)')

The X_ERR.log does not help much.

Code: Select all

============================================================================== 
Thu Feb 23 15:29:15 2017 
Release 14.1.0 Build 4138 Windows 10 Enterprise Edition (10.0.0.0 Build 10586)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (11/17/2016 10:34:33 AM)
---EPC (17-11-2016 10:43:44)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (2/3/2017 10:04:13 AM)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (2/6/2017 9:40:28 AM)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (2/6/2017 10:44:09 AM)
---EPC (07-02-2017 12:19:20)
---EPC (07-02-2017 12:21:43)
---EPC (13-02-2017 14:54:55)
Message : X_COMP returned error code -1. Check X_ERR.LOG for more information.
Routine : X_Execute_WAM
Job Number: 007956 OS User : LANSA
DBII=LANSA DBUT=MSSQLS GUSR=QOTHPRDOWN PSTC=Y PROC=*WAM WAML=LANSA:XHTML WVAR=2 W3ST=1 WXSL=YY LANG=ENG PART=SYS USER=LANSA CMTH=T CDLL=LCOMGR32.DLL WSTY=IISB ITHP=N
ROOT=C:\Program Files (x86)\LANSA\X_WIN95

Is there anyway to view the generated SQL? Perhaps that can clue me into why it is failing.

[soa]

Why don't you build the SQL statement in full and execute it with the USING clause rather than relying on the SQL command generated by LANSA. This way you can take the string you've built in debug and run in a standard SQL tool such as STRSQL. This will either execute correctly or give you useful diagnostic messages.

[jyoung]

I've been trying to stay away from the free format select_sql because we move code between different environments and each environment has a different library. When using the free format version, we pretty much hard code the library in the statement, we do have some ways to handle it better but its a big headache. Letting the library list of the job (which we do set dynamically) handle all of this helps out a lot.

It would not be a problem, if it would just tell me what the query is that its trying to generate.

Syntactically this seems correct, the query works when removing the "replace" and using replace fails on the simplest of queries. Using other functions work fine (SUBSTRING and CASE) it seems like only functions that have quote delimiters that are problematic.

[billcross]

Have you tried putting the where into a variable and using the *QUOTE field instead of ' ? The command to build could be

#WHERE := <Start of Where> + *QUOTE + ' ' + *QUOTE + ',' + *QUOTE + *QUOTE + <End of Where>

[soa]

I do not understand why you would hard code the library in the USING statement. SELECT_SQL will use the library list with either format.

[jyoung]

Perhaps I am doing something wrong, but every time I have to use the free format select_sql I've had to specify the library the File is in.

http://docs.lansa.com/14/en/lansa015/in ... free_p.htm mentions that

It is usually necessary to specify the collection that the table is in.

"Ususally" for me has been always.

I think this has to do with using two operating systems. When working locally (on Windows) I have to specify the library name, on the iSeries I don't. I think its an OS problem and not a RDBMS problem as I think the issue is that Windows does not have the concept of a library list (that I know of. I think the PATH environment variable is the closest thing).

This turns into a mess of code like this for every query that uses free form select_sql.

Code: Select all

if (*OSAPI = IBM)
* create query here
else
* create query with library here
endif

Like any good ORM, using its query generation ability gives you the flexibility to move between OS(s) and RDBMS(s) with few issues. I think this is actually a bad thing to do, you should IMHO, develop on what you are deploying to. This of course presents a problem because unlike my RPG coding co workers, who develop on the iSeries, we LANSA devs develop on Windows.

When the query generation tools break down and provides no feedback you are pretty much stuck with having to resort to custom sql and using, in this case, code specific to each platform.

[soa]

I've only used this on the iseries but it seems odd that SELECT_SQL in its old form would not require a library name but freeform would.

If you have to use conditioning in building the statement why not do something like

#stm = 'Select * from &1 where x=1'

if (*OSAPI = IBM)
#stm = #stm.replace('&1', 'MYFILE')
else
#stm = #stm.replace('&1', 'LIBRARY/MYFILE')
endif

[soa]

Another suggestion would be to replicate your problem code into an RDML function then check it in to an iSeries with keep source. Then the RPGSQL source listing will show you what is being generated and maybe even give you syntax error information. I assume there's generated c++ source somewhere but I've never looked for that.

[jyoung]

That is pretty much what I am having to do know except the logic to determine what library is bit more complex. We have 3 different environments, 2 geographical regions and 2 "applications" each with different libraries. I've got it worked out, so that I can do something like

Code: Select all

#wk_Sql := ("select ... from &1.MyTable where ...").Subsitute(#LibraryListManager.GetDataLibrary(#wk_CountryCode))

Where country code denotes what "region" I am in. I also ended up making a system variable *ENVIRONMENT that tells me if I am local, in dev or production.

All that being done, it would still be easier IMHO to do

Code: Select all

select_sql fields(...) from_files((MyTable)) where(...)

[Tim McEntee]

Is it because you are using hashes for your fields in the where clause?
I would have used (REPLACE(CLOFN, '' '', '''') LIKE :STD_TEXT)

[jyoung]

Hi Tim,

Don't know but I did not think about it, but I'll give it a shot. I can use the hash in other SQL functions like CAST but perhaps REPLACE and its quotes require something different.

Will give it a go and see what happens.

Thanks,
Joe