Powerbuilder - embedded SQL - SELECT INTO

0
votes

While working on a production issue, I stumbled on something, that I thought was, strange in a SELECT statement in PB.

Essentially, I have a simple SQL that selects into a Variable. I guess, the original developer (I am only maintaining this code) expected only one row in this case.

....
//
Date ld_CutOff

....
SELECT TO_DATE(PARM_VALUE, 'DD-MON')
INTO :ld_CutOff
FROM SYSPARM
WHERE PARM_NAME = 'CUTOFF_DATE' ;
....

Now the table has more than 1 that match the criteria. Thus, the SQL errored (Select Returned more than 1 row) as expected. (Based on prior experience in PB and PL/SQL,) I expected the host variable to be null (01/01/1900 in this case). To my surprise, the SQL actually set the variable to 1st Date value (or is the last value?) returned by the SQL.

(FYI, I turned on the SQL Trace: it seems it keeps fetching all the rows, so I guess it will be last row value that's put in the local variable?)

I am fixing the SQL, but I am just curious to know more about the way PB handles this. Is this a normal behavior for PB/SQL or am I expecting PL/SQL behavior in PB?? The versions I am using: PB 10.2.1 (Build 9671) and Oracle 10g. Thanks a lot for any comments or suggestions.

Sam

2
powerbuilder
Perhaps this behavior has been changed in a more recent version of PowerBuilder (12.5) and works the way you would expect it to. Of course, fixing the SQL query itself to ensure it only returns a single row would be wise. Also, I suggest you use the SetNull() function on your local variable before executing the SQL query. - Bernard
Thanks, Bernard. I may be wrong, but I thought it was so in a earlier versions of PB too. I always do the error checks (SQLCODE check etc) and I initialize the variables as you suggested. Whoever coded the function that had this SQL didn't do any of these and the program "worked" as long as there was only one row. With additional rows added, we started seeing the error message, but apparently it was setting the date, so it looked like it "worked". FYI, The Sysparm table actually has fields Effective_Dt and End_Dt, so we can use parameter valid for a date range. - svaratech
Good luck maintaining the code! - Bernard
There may be something in a settings file that changes this behaviour. - Slapout

2 Answers

0
votes

This is normal behavior. When using embedded SQL (select into) you can only retrieve one row and there is no way around that. But you have plenty of other options:

  1. Further restrict the Where clause to get one result.
  2. Use group functions (MAX, MIN) on select columns to make sure you get one result.
  3. Use a datawindow or dataobject.
  4. Declare a cursor and perform a fetch into loop.

When you get the error that you did; I wouldn't count on any particular behavior to happen because I don't think selecting multiple rows into host variables is supported so you don't know how it will work when you upgrade to a later version of PB.

Hope this helps.

0
votes

You can use a dynamic cursor and fetch only the first row.

  DECLARE dbinfo DYNAMIC CURSOR FOR SQLSA;
  ls_sql = "SELECT {fn curdate() }"
  PREPARE SQLSA FROM :ls_sql USING SQLCA;
  OPEN DYNAMIC dbinfo;
  IF SQLCA.SQLCode > 0 THEN
       // erro handling
  END IF
  FETCH  dbinfo INTO :ls_date;
  CLOSE dbinfo;