1. Michael Ray
  2. PowerBuilder
  3. Thursday, 29 April 2021 21:51 PM UTC

[A DB2 database contains the tables and is running on an AS400 minicomputer. The application ODBC connects to a local network Adaptive Server Anywhere database. The ASA database ODBC connects to the DB2 over the company network. The ASA configuration uses 3 sql statements:
1. CREATE SERVER remotedb2 CLASS 'db2odbc' USING 'remote_db2';  (remote_db2 = ODBC DSN)
2. CREATE EXTERNLOGIN appuser TO remotedb2 REMOTE LOGIN user... IDENTIFIED BY pw...;
3. CREATE EXISTING TABLE p_itmst AT 'remotedb2..APLUSHBFPL.itmst';  (for each table accessed, "p_" prefix = proxy table)   Edit 5/3/2021]

DW painter: retrieval hangs when using 6 retrieval arguments (sql syntax not graphic).
DB painter: retrieves ok (argument values hardcoded).

If the dw retrieval arguments are unused (values hardcoded into syntax), retrieves ok. If 1 retrieval argument is used the dw painter still hangs (5 hardcoded). See the sql select below (nothing special).
Small selects with few retrieval arguments retreive ok (syntax or graphic).

If DB profile ODBC Disable Bind is checked, 1 retrieval argument, retrieves ok. If all 6 retrieval arguments used, only some rows are retrieved.

In PB 10.5 this dw retrieves ok. When PB 19 hangs above, have to close/crash PB to start over. No changes have been made after migration. What to do?

Development details...
PB: 2019 R3 build 2703
PC: win10 Pro, ver 20H2
DB: Adaptive Server Anywhere 9.0.2.3951 64 bit, network server
DB profile: default settings (ODBC DSN, user, password).
Tables in query: proxy tables on server   [Actual tables in DB2 database. Edit 5/3/2021]

The sql (nothing special):

//retrieval arguments (Edit 4/30/2021: removed abbreviations for argument type).
string         as_part_num
string         as_business_unit
string         as_warehouse
string array as_orderstatus
number      al_begindate
number      al_enddate

//DW painter: data source (with retrieval arguments)  [Added comments. Edit 5/3/2021]
 SELECT "p_itmst"."imitno",  p_ordet.odorno,  p_ordet.odwhid,  sum(p_ordet.odqtor) AS c_sumqtyordered  
    FROM "p_itmst",   //part
         "p_ordet",      //order detail
         "p_orhed"      //order header
   WHERE ( "p_itmst"."imitno" = "p_ordet"."oditno" ) and  //joins
         ( "p_ordet"."odcono" = "p_orhed"."ohcono" ) and  
         ( "p_ordet"."odorno" = "p_orhed"."ohorno" ) and  
         ( "p_ordet"."odorgn" = "p_orhed"."ohorgn" ) and  

         ( ( rtrim("p_itmst"."imitno") like :as_part_num ) AND  //part criteria
         ( "p_itmst"."imsusp" <> 'S' ) AND  
         ( "p_itmst"."imvnno" = :as_business_unit ) AND  

         p_ordet.odcsno <> 6844 AND  //order detail criteria
         ( "p_ordet"."odwhid" like :as_warehouse ) AND
         "p_ordet"."odwhid" <> 'AP' AND  
         ( "p_ordet"."odorst" not in (:as_orderstatus) ) AND  

        substr("p_orhed"."ohorno", 2, 1) in ('0','1','2','3','4','5','6','7','8','9') and //>= '0'  //order header criteria
         ( "p_orhed"."ohortp" <> 'Q' ) AND  
        ((p_orhed.ohcsno > 7000 and p_orhed.ohcsno < 8000) or p_ordet.odohld <> 'CR') AND
         ( "p_orhed"."ohrsdt" between :al_begindate and :al_enddate ) )   
 GROUP BY "p_itmst"."imitno", p_ordet.odorno,  p_ordet.odwhid  


//DB painter (without retrieval arguments)
  SELECT p_itmst.imitno, p_ordet.odorno, p_ordet.odwhid, sum(p_ordet.odqtor) AS c_sumqtyordered  
    FROM p_itmst,      //part
         p_ordet,         //order detail
         p_orhed         //order header
   WHERE ( p_itmst.imitno = p_ordet.oditno ) and    //joins
         ( p_ordet.odcono = p_orhed.ohcono ) and  
         ( p_ordet.odorno = p_orhed.ohorno ) and  
         ( p_ordet.odorgn = p_orhed.ohorgn ) and  

         ( ( rtrim(p_itmst.imitno) like '%' ) AND    //part criteria
         ( p_itmst.imsusp <> 'S' ) AND  
         ( p_itmst.imvnno = 'MXBU' ) AND  

         p_ordet.odcsno <> 6844 AND  //order detail criteria
         ( p_ordet.odwhid like '%' ) AND  
         p_ordet.odwhid <> 'AP' AND  
         ( p_ordet.odorst not in ('3','4') ) AND   //[quote each list item. Edit 5/6/2021]

         substr(p_orhed.ohorno, 2, 1) in ('0','1','2','3','4','5','6','7','8','9') and  //order header criteria
         ( p_orhed.ohortp <> 'Q' ) AND  
        ((p_orhed.ohcsno > 7000 and p_orhed.ohcsno < 8000) or p_ordet.odohld <> 'CR') AND
         ( p_orhed.ohrsdt between 210420 and 210425 ) )   
 GROUP BY p_itmst.imitno, p_ordet.odorno, p_ordet.odwhid
;

Michael Ray Accepted Answer Pending Moderation
  1. Thursday, 1 July 2021 14:53 PM UTC
  2. PowerBuilder
  3. # 1

The issue is resolved using DisableBind=1 as part of the ODBC connection. In contrast to what was stated in the original post, this did actually work. Thank you all.

Comment
  1. Armeen Mazda @Appeon
  2. Thursday, 1 July 2021 17:41 PM UTC
Thanks for sharing the solution!
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Friday, 30 April 2021 03:21 AM UTC
  2. PowerBuilder
  3. # 2

Hi Michael,

We haven’t encountered any issue of this kind. Do you get the same issue if you set Disable Bind to 0?

To better analyze and keep track of this issue, I suggest that you submit a Bug to Appeon Support System (https://www.appeon.com/standardsupport/newbug), and at the same time, upload a simple case that can reproduce the issue, including pbl(dataobject) and the DB syntax of these three tables: p_itmst, p_ordet, and p_orhed.

 

Regards,

Ken

Comment
  1. Michael Ray
  2. Friday, 30 April 2021 17:23 PM UTC
Initially, no Disable Bind setting was used. I only set Disable Bind to troubleshoot. No difference here.

Need to 1. submit bug with link provided. 2. upload sample (pbl with datawindow, DB syntax of 3 tables). Thanks.
  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Thursday, 29 April 2021 22:07 PM UTC
  2. PowerBuilder
  3. # 3

Maybe I'm reading this incorrectly, but I thought that you could NOT pass a structure as a parameter, nor use it as a parameter in your exe.

 

Comment
  1. Michael Ray
  2. Friday, 30 April 2021 17:11 PM UTC
HI Olan, I abbreviated String to str in the post. Also strarr means string array.
  1. Helpful
  1. Olan Knight
  2. Thursday, 6 May 2021 14:19 PM UTC
Ah, thanks!

  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.