1. Pietro Guglielmo
  2. PowerBuilder
  3. Thursday, 21 October 2021 09:11 AM UTC
Hi all, 
we are running migration and compatibility tests from power builder version 9 to 2019 version of a 2-tier application developed 15 years ago. The application was developed in PB 9.0.1 Build 6533.
We are now testing the same application under PB 2019 R3 build 2728.

Here is the problem, a database query is performed from a datawindow. The query works fine in PB9 but
the same query raises the following error in PB2019R3 "Select Error: SQLSTATE = S1010 [Microsoft] [ODBC Driver Manager] Error in function sequence. "
The application retrieves only a part of the records, on PB9 it retrieves 165 records without errors while on PB2019 it retrieves 140 records raising the error. Soon after, whatever action is performed on the application causes it to crash.
We are using an ODBC 32-bit connection (in a 64 bit environment) to IBM DB2 database DB2 v11.1.0.1527.
The odbc driver number is : "DB2 v11.1.0.1527 - 11.01.00.1527 - DB2CLI.DLL 10/06/2016".
We do not know if the problem occurs on other datawindows, because we should check them all to know.
Everything works fine on others DW but on this one it doesn't.

Any idea on the this problem?
Thanks,
P.

 


Matt Balent Accepted Answer Pending Moderation
  1. Thursday, 21 October 2021 14:09 PM UTC
  2. PowerBuilder
  3. # 1

Basic questions:  Have you run a DB trace?  Can you run the SQL from somewhere else  (outside of PB) against the database?  Have you tried a different driver?

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 21 October 2021 14:48 PM UTC
  2. PowerBuilder
  3. # 2

Additional basic questions: When editing this DataWindow in the DataWindow Painter, can you preview (retrieve) successfully? Do other DataWindows retrieve properly, either when running the app or from the DataWindow Painter? Have you tried taking the SQL SELECT statement used as the data source for the failing DataWindow and executing it from the ISQL pane in the PowerBuiulder Database Painter?

 

Comment
There are no comments made yet.
Pietro Guglielmo Accepted Answer Pending Moderation
  1. Friday, 22 October 2021 13:42 PM UTC
  2. PowerBuilder
  3. # 3

Hi John, thanks for your helpfulness.
We have found the problem, I will describe the issue so that it can be useful to others as well.
To answer your questions anyway:
"other DataWindows retrieve properly..." yes, both in the app and in the datawindows painter.
"executing it from the ISQL pane " yes, the select works properly if launched from ISQL pane.

 

Comment
There are no comments made yet.
Pietro Guglielmo Accepted Answer Pending Moderation
  1. Friday, 22 October 2021 13:47 PM UTC
  2. PowerBuilder
  3. # 4

Hi Matt, thanks for your helpfulness.
We have found the problem, I will describe the issue so that it can be useful to others as well.
Tracing the DB from the ODBC driver manager we found: "oobjm 1ae8-7ec EXIT SQLNumResultCols with return code -1 (SQL_ERROR)
HSTMT 0x0096DB60
SWORD * 0x00598EA0
DIAG [S1010] [Microsoft][ODBC Driver Manager] Error in the function sequence. (0)"
We then assumed that the problem was related to some column in the select.
In fact, the problem was related to a particular calculated column.

 

Comment
There are no comments made yet.
Pietro Guglielmo Accepted Answer Pending Moderation
  1. Friday, 22 October 2021 13:57 PM UTC
  2. PowerBuilder
  3. # 5

We found the problem,

a particular column of the select was constructed with a series of concatenations between string fields.
"SELECT ....,
coalesce(column1, ' ') ||'/'|| coalesce(column2,' ')||'/'|| coalesce(column3,' ')||'/'||
coalesce(column4, ' ')||'/'||coalesce(column5, ' ')||'/'|| .... etc AS COMPUTED_STRING"
We used DB2 operator "|| ( double pipe ) " which corresponds to the CONCAT function.
By removing this calculated column everything went well.
We haven't tried replacing the || with the CONCAT function yet, but I think the problem is related to this particular operator. It may not be handled correctly by the ODBC driver.
I suppose we should modify the select with a series of :
SELECT CONCAT(CONCAT('colmun1',' '),'column2') AS COMPUTED_STRING
FROM SYSIBM.SYSDUMMY1;
Alternatively we can concatenate the columns via PB script

 

Comment
  1. Pietro Guglielmo
  2. Friday, 22 October 2021 14:07 PM UTC
However, I don't explain why in PB 9 everything worked while in PB2019 it didn't.

(the ODBC driver is the same)

Also, why does the select work from ISQL while it raises the error from the data window?

(I believe ISQL connects via ODBC)
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.