SQL SELECT INTO Issue V7R3

[mbeaulac]

When I run this in debugger (RDI) all the number fields have 4040404 value in them and the character field are blanks.



I also went to the green screen: STRSQL

and hard coded this:

SELECT * FROM @AAM WHERE AAMSEQ = 0000167

and that work and brought back the record.



I get no error messages.



I am calling this from a web page using Lotus Notes.



I am not sure what to do at this point.



**FREE
CTL-OPT NOMAIN OPTION (*SRCSTMT : *NODEBUGIO);

// To create the service program:
// CRTSRVPGM SRVPGM(BPCSO/PUR019WS)
// MODULE(BPCSO/PUR019W)
// SRCFILE(BPCSS/PURBNDF) SRCMBR(PUR019WB)


DCL-PR PUR019 EXTPROC(*DCLCASE);
*N PACKED(7:0);
*N CHAR(2);
END-PR;

DCL-PROC PUR019 EXPORT;
DCL-PI PUR019;
SEQ PACKED(7:0);
RSL CHAR(2);
END-PI;

DCL-DS purData;
AAMSEQ PACKED(7:0);
AAMPRC CHAR(10);
AAMGLN CHAR(20);
AAMAP1 CHAR(10);
AAMAP2 CHAR(10);
AAMSTS CHAR(1);
AAMCRB CHAR(10);
AAMDCR PACKED(8:0);
AAMTCR PACKED(6:0);
AAMMDB CHAR(10);
AAMDMD PACKED(8:0);
AAMTMD PACKED(6:0);
END-DS;

EXEC SQL SELECT AAMSEQ, AAMPRC, AAMGLN, AAMAP1, AAMAP2, AAMSTS, AAMCRB, AAMDCR, AAMTCR, AAMMDB,
AAMDMD, AAMTMD INTO urData
FROM @AAM
WHERE AAMSEQ = :SEQ;

RETURN;

END-PROC PUR019;

[JonBoy]

You say you ran the code in debug - but how? You did a CALL from the command line? If not, that is the first thing to try since you need to determone the point of failure.

The fields in question would be expected to contain x'4040...' if no data was ever loaded into them since they are in a DS and you have not coded an INZ on the DS itself or any of the subfields. A DS is a character field by definition and therefore will always contain blanks (i.e. x'40') unless the INZ keyword is used.

But how do you expect the result to get back to the caller even when it is correct? There is no parameter equivalent to purData and purData is not specified on the Return op-code or on the proceure interface. (The prototype by the way is not needed in V7+ just move the DCLCASE to the PI.

Also I see no sign of a check for SQLSTATE after the operation which is very bad practice. Unlike native I/O operations such as READ and CHAIN SQL will not issue errors if it doesn't work - it will just set appropriate state values. Change the logic to check the SQLSTATE and in the event of an error set an appropriate value in some field in the DS.

[JonBoy]

I'm not going to respond to your private message as that loses the value of the discussion for those who come after.

There is as good an example as I've seen of a simple approach to checking SQLSTATE in this thread https://stackoverflow.com/questions/...ion-in-ile-rpg

Simple answer is to check if the first two characters of SQLSTATE are '00' if so all is OK.

You need to create a simple RPG program that you can use to test your procedure. That way you can eliminate the Lotus Notes web page part of the potential problem chain.

All you need is.

CTL-OPT DTFACTGRP(*NO);
DCL-PR PUR019 EXTPROC(*DCLCASE);
*N PACKED(7:0);
*N CHAR(2);
END-PR;

dcl-s seq packed(7) Inz(0000167); // Your test value
dcl-s rsl char(2);

PUR019( SEQ: RSL );

*InLr = *On;

Compile this and bind it to your service program. Then call this from the command line and use step into to enter the called routine.


BUT - you have to know how the heck the value is going to get returned to the web app. I suspect and additional parameter for the return value as it is unlikely that the interface can handle real return values. But I have no idea how Notes does it. Do you not have examples you can look at?

[B.Hauser]

You should always check the SQLCODE or the SQLSTATE after a SELECT statement.
You either can check the first digits of the SQLSTATE (00 = Okay, 01 = Warning, 02 = Not found everything else is an error) or the SQLCODE ( < *Zeros = Error, 100 = Not Found, > 0 except 100 = Warning).
I personally prefer the SQLCODE, because it is easier to check and it is easy to find the appropriate error message. All SQL error messages are stored in the QSQLMSG message file. The message is, 'SQL' or 'SQ' + the absolute value of the SQLCODE, i.e. -811 --> Message-Id SQL0811.

If an error occurs it is (since Release V5R3M0) also possible to trap the SQL Error Message in your program.

Exec SQL GET DIAGNOSTICS CONDITION 1 :ErrorText = MESSAGE_TEXT;

.

Also always put an INZ to your data structure and/or CLEAR the data structure immediately before you read data with SQL in the data structure.

Do I understand correctly, numeric fields include Blanks (i.e. x'4040')?
SQL cannot handle these fields. Because it is not possible to insert blanks into numeric fields in SQL described tables, SQL does not expect blanks in numeric fields and crashes.
You need to revise your numeric data correctly, i.e. replace the blanks in your numeric fields with *Zeros.

Birgitta