How to test /FREE SQL Select Into

[Tom Wheeler]

How do I test for a successful Select other that test the into fields for a value? This should be easy but my searching came up with nothing.

/FREE
EXEC SQL
SELECT CDFIVE, CDUCD1
INTO :OJTSPR, :OJSKIL
FROM PRPCD
WHERE CDER = :OJER AND TRIM(CDUCD2)= TRIM(:COCRID)
AND CDTYPE ='TSK';
/END-FREE

[TedHolt]

Look at the value of SQLSTATE or SQLCODE. I prefer and recommend SQLSTATE.

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzala/rzalaco.htm

[Tom Wheeler]

I was looking more so for a example like does the "IF SQLSTATE = '02000' " go inside the free format? doesn't seem to work outside the Free.

[JonBoy]

It would help us to understand what it is that you are having trouble with if you could be a bit more precise than saying "doesn't seem to work ".

It didn't compile? Produces the wrong results at run time?

There is almost zero difference between free and fixed IFs.

Free: If SQLSTATE = '02000';

Fixed: If SQLSTATE = '02000'

That's it!

[Tom Wheeler]

Your right, I ran this code but the IF on the SQLSTATE didn't occur. I believe the syntax is correct so maybe it the data coming in. I am going to put it into debug and see what it returns.
Thanks

/FREE
EXEC SQL
SELECT CDFIVE, CDUCD1
INTO :OJTSPR, :OJSKIL
FROM PRPCD
WHERE CDER = :OJER AND TRIM(CDUCD2)= TRIM(:COCRID)
AND CDTYPE ='TSK';

INV_CD = 'N';
IF SQLSTATE = '02000';
INV_CD = 'Y';
ENDIF;

/END-FREE

[B.Hauser]

You only checked for SQLSTATE '02000'. What SQLSTATE (or SQLCODE) is exactly returned?
So we do not know what really happens.
In either way, if not found or an error is returned the INTO :HostVariables are NOT updated, so if these hostvariables are not cleared before the SELECT ... INTO statement, they content of these variables is still the same as before executing the SELECT ... INTO statement.

Birgitta

[Vectorspace]

I think you may have misunderstood (apologies if I am wrong)

SQL State 02000 means no records found/end of file.

So what you are doing is:

INV_CD = 'N';
If no records were found
INV_CD = 'Y';
endif

Is that what you expected?

[Tom Wheeler]

I am clearing the host Variables each time before the statement, good point to remember. I miss understood the meaning the meaning of the SQLSTATE "02000". The program is returning SQLSTATE = '01004' so there is a problem with my substring.. I just need to know when the select doesn't find a row to insert into the host variables maybe I need to trap on SQLCODE not SQLSTATE.

Thank you for your help.

[Tom Wheeler]

Vectorspace, thanks for your help. That is exactly what I am trying to do. So SQLSTATE "02000" is Record not found.

[jayvaughn]

another way...

rowFound = 0;

/FREE
EXEC SQL
SELECT 1, CDFIVE, CDUCD1
INTO :rowFound, :OJTSPR, :OJSKIL
FROM PRPCD
WHERE CDER = :OJER AND TRIM(CDUCD2)= TRIM(:COCRID)
AND CDTYPE ='TSK';

INV_CD = 'N';
IF rowFound;
INV_CD = 'Y';
ENDIF;

Though sqlstat is recommended, for most cases you only care if the record is found or not, therefore if SQLCODE <> 0, then "something" did not jive and your data was not returned from the sqlstmt.
Which is all you usually care about. If you do care exactly what happened and need to report it back SQLSTAT is needed.