I am attempting to use a prepared statement to fetch information (a single row) about a location; based on a given ID. The location ID is either passed via parameter, or directly entered and read from the display/user interface.
I have tried stepping through the RDi debugger. I see the program walking through the code, executing the subroutine. The problem revolves around the data structure and the sql "prepare and execute". I was hoping to select the data into a data structure (without listing the individual "INTO" fields); while also avoiding the use of a SQL cursor (since it is a single row). Is this possible?
Problem:
Investigation (scattered thoughts; ideas to try):
Note: Code has been condensed (below); striping out some logic and comments.
JOB LOG
CODE
I have tried stepping through the RDi debugger. I see the program walking through the code, executing the subroutine. The problem revolves around the data structure and the sql "prepare and execute". I was hoping to select the data into a data structure (without listing the individual "INTO" fields); while also avoiding the use of a SQL cursor (since it is a single row). Is this possible?
Problem:
- Data not stored into the data structure.
- The prepared query is failing to execute.
Investigation (scattered thoughts; ideas to try):
- The 'actual' SQL query with a valid location ID, works outside of the program.
- The dsLocationInfo is qualified, appears to be valid, but Job Log says it is not defined or usable.
- Is a cursor required for a single row result?
- Tried using dim(1) in data structure, but still failed.
- Should I use the individual fields instead of the data structure?
- Is a cursor required for a single row result?
- SQLSTATEMENT not found. Tried shortening the object to sqlSTMT, but same results. Could this be a false-positive due to DS/variable fields? Database Resource not created?
Note: Code has been condensed (below); striping out some logic and comments.
JOB LOG
PHP Code:
Variable DSLOCATIONINFO not defined or not usable.
Prepared statement SQLSTATEMENT not found.
DSPLY Street:
CODE
PHP Code:
**FREE
ctl-opt dftactgrp(*no) actgrp(*caller) main(procMainline);
dcl-pr procMainline extpgm('a001');
*n int(20) options(*nopass);
end-pr;
dcl-proc procMainline;
dcl-pi *n;
inLocID int(20) options(*nopass);
end-pi;
dcl-f a001ui workstn alias infds(dsINFDS);
dcl-c F03 x'33'; // F3 - Exit
dcl-s sqlString varchar(1024);
dcl-ds dsUIHead likerec(UIHEAD:*output);
dcl-ds dsUIFoot likerec(UIFOOT:*output);
dcl-ds dsUIBody likerec(UIBODY:*all);
dcl-ds dsINFDS;
actionKey char(1) pos(369);
end-ds;
dcl-ds dsLocationInfo qualified;
adrStreetNumber int(5);
adrStreetPrefix char(2);
adrStreetName char(15);
adrStreetSuffix char(4);
adrAdditionalInfo char(10);
end-ds;
if %parms() = 1;
dsUIBody.uiLocationID = inLocID;
endif;
write UIHead dsUIHead;
write UIFoot dsUIFoot;
write UIBody dsUIBody;
read UIBody dsUIBody;
dou actionKey = F03;
exsr queryForLocationInfo;
select;
when actionKey = F05;
dsUIFoot.msgLine = 'Data refreshed.';
other;
dsUIFoot.msgLine = '';
end-sl;
enddo;
// End program
return;
begsr queryForLocationInfo;
sqlString = 'SELECT STRNUM, STRPRE, STRNAM, STRSUF, STRADI ';
sqlString += 'INTO :dsLocationInfo '; // May need to remove this and use the modified execute, below
sqlString += 'FROM PROD.LOCMSTRP ';
sqlString += 'WHERE LOCID = ? ';
sqlString += 'OPTIMIZE FOR 1 ROW;';
exec sql prepare sqlStatement from :sqlString;
exec sql execute sqlStatement using dsUIBody.uiLocationID;
// exec sql execute sqlStatement into :dsLocationInfo using dsUIBody.uiLocationID;
dsply 'Street: ' dsLocationInfo.adrStreetName;
endsr;
end-proc;
Comment