(RPG) Offline : SQL Select statement

[rdevost]

Hi,

I have an offline program and a local database that I need to read from. The local database is a multilingual file where I get the equivalent multilingual text depending on the chosen language code. I used the SQL Select statement but it doesn't seem to be working as I keep getting null values.

Here's my SQL statement:

LRNG_SetProperty('/Form.Vars.PGMNAM' : 'MX3LOGON');
LRNG_SetProperty('/Form.&.TBL' : 'MultiLingual');

LRNG_SetProperty( '/Form.Fields.BTN_LOGIN.Text.&.SQL' :
'Select MLTXTVAL from MultiLingual where ' +
'MLLANGID = (&(VAR:PRMLNG)) and ' +
'MLPGMNAM = (&(VAR:PGMNAM)) and ' +
'MLFLDNAM = ''BTN_LOGIN''');

where &(VAR:PRMLNG) = 'ENG' and &(VAR:PGMNAM) = 'MX3LOGON'.


I also tried another version of Select statement, this time hard-coding the Language ID (MLLANGID) and Program Name (MLPGMNAM):

LRNG_SetProperty( '/Form.Fields.BTN_LOGIN.Text.&.SQL' :
'Select MLTXTVAL from MultiLingual where ' +
'MLLANGID = ''ENG'' and ' +
'MLPGMNAM = ''MX3LOGON'' and ' +
'MLFLDNAM = ''BTN_LOGIN''');


In both cases, my Select statements are not returning anything. The Multilingual file contains the records I should be getting. But in the client log, the Select statement appeared like this:

Line 2328: 2015-04-13 06:19:54.658 LongRange[763:2154413] _StatementRowIterator::R lansa::sqlite::Statement::execute():422: DB:SQL: SELECT "rowid" FROM "MultiLingual" WHERE "mlfldnam"=? AND "mllangid"=? AND "mlpgmnam"=?

The MLFLDNAM, MLLANGID and MLPGMNAM all have null values.


The MultiLingual file has these records, to list a few:
MLLANGID -- MLPGMNAM -- MLFLDNAM -- MLTXTVAL
ENG MX3LOGON BTN_LOGIN Login
ENG MX3LOGON BTN_HOME Home
ENG MX3LOGON BTN_SUBMIT Submit


I don't know what I'm doing wrong, but I am not getting anything in my SQL statement. Any help would be greatly appreciated.

Thanks in advance.

Rocel

[MarkDuignan]

If you turn on the developer console (Settings -> Development -> Development Console) do you see any new information about what is happening?

[rdevost]

Hi Mark,

I see a lot of things being logged in the log file but this is the only statement that I found fishy. I have already cleared the log file as it is getting bigger but I'll post it in here when I get to re-create it.

Also, I have some SQL statements below that make use of Vars. It reads from a local file 'SystemValues' and I tried to get the value of the column SYSCHARVAL when SYSVARNAME condition is met and I tried to store the result to Vars PRMLNG and SMTP. I also noticed that using Vars with SQL does not work. Or does it? Does this SQL only works when it is bound to a Field name and not a Variable name?

LRNG_SetProperty( '/Form.&.TBL':'SystemValues');
LRNG_SetProperty( '/Form.&.SQL':
'Select SYSCHARVAL from SystemValues ' +
'where SYSVARNAME = ''PRIMARYLANGUAGE''');
LRNG_SetProperty( '/Form.Vars.PRMLNG.Value.&.FLD': 'SYSCHARVAL');
wrkPrmLng = %trim(LRNG_GetPropAsStr('/Form.Vars.PRMLNG'));
SavedState.MX3LANGID = %trim(wrkPrmLng);

LRNG_SetProperty( '/Form.Vars.SMTP.Value.&.SQL':
'Select SYSCHARVAL from SystemValues ' +
'where SYSVARNAME = ''SMTPRELAYSERVER''');
wrkEmailServer = %trim(LRNG_GetPropAsStr('/Form.Vars.SMTP'));
SavedState.MX3SMTP = %trim(wrkEmailServer);

Also tried this version and it doesn't work either.

LRNG_SetProperty( '/Form.Vars.SMTP.&.SQL':
'Select SYSCHARVAL from SystemValues ' +
'where SYSVARNAME = ''SMTPRELAYSERVER''');
wrkEmailServer = %trim(LRNG_GetPropAsStr('/Form.Vars.SMTP'));
SavedState.MX3SMTP = %trim(wrkEmailServer);

Any input would be appreciated. Thanks again in advance!

Rocel