Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Problem with datawindow retrieval argument data types, number vs decimal

  1. Advice
0
Votes
Undo
  1. Sylvain .
    Sylvain .
  2. PowerBuilder
  3. Wednesday, 5 May 2021 15:34 PM UTC

PowerBuilder CloudPro 2019R3 Build 2670
MySQL Server  8.0.23
MySQL ODBC   8.0 ANSI Driver

I have a parent table with 2 child tables.

The parent table key is a column define as BIGINT with AUTO_INCREMENT and the 2 child tables key are both a column define as BIGINT.

I have 3 datawindows, one for each tables with a simple SELECT KEY_ID, COL1, COL2,… WHERE KEY_ID = :al_key_id  in every datawindow.

the :al_key_id retrieval argument in all 3 datawindows is defined as a NUMBER.

The child datawindows always retrieve properly but the parent datawindow did not retrieve.

I changed the retrieval argument for the parent datawindow from NUMBER to DECIMAL and now it retrieve properly.

My understanding of NUMBER vs DECIMAL is as follow:

  NUMBER    15 digits signed

  DECIMAL   28 digits signed with a decimal point anywhere in there

 

My question is why the retrieve of the parent table do not work with NUMBER but work with DECIMAL, especially when the value I’m using for the key is 1.

Does anyone has an idea about this, and should I always use DECIMAL over NUMBER.

 

I have other tables across our systems with the same parent child configuration and they ALL use NUMBER without problem, only that specific parent table give me that problem.

 

Thank you for your time and effort, it is always appreciated.

Sylvain.

PowerClient deployment
How to display list of Images in a DLL?
Responses (4)
Sylvain .
Sylvain . Accepted Answer Pending Moderation
  1. Monday, 17 May 2021 13:15 PM UTC
  2. PowerBuilder
  3. # 1

Thank you Julie for your answer.

I already knew about that configuration switch in the ODBC, but that could cause other problem.

Like I said the only time this problem will occurs is on table where the key is a BIGINT and  AUTO_INCREMENT like this example:

CREATE TABLE TABLE_TEST (
   `KEY_ID`   BIGINT            AUTO_INCREMENT,
   `COL_1`    VARCHAR(30)   DEFAULT ' ' NOT NULL ,
   `COL_2`    VARCHAR(30)   DEFAULT ' ' NOT NULL ,
CONSTRAINT TABLETEST_PK PRIMARY KEY ( KEY_ID ) ) ENGINE=MyISAM ;

When the AUTO_INCREMENT is not present the problem do not occur.

For now I will simply use argument of type DECIMAL, it look like it is fixing my problem. 

Thank you again for your help.

Sylvain

Comment
There are no comments made yet.
Julie Jiang @Appeon
Julie Jiang @Appeon Accepted Answer Pending Moderation
  1. Monday, 17 May 2021 10:40 AM UTC
  2. PowerBuilder
  3. # 2

Hi Sylvain,

Our engineering team has the following workaround suggestion. Please try and see whether it can work. 

1> When configuring the ODBC connection, pleas enable "Treat BIGINT columns as INT columns". 

2> In MySQL, the key_id column type shall be bigint, while in PB, the key_id column type is long. 

If possible, we'd like to invite you to join our PowerBuilder/PowerServer 2021 beta testing (https://www.appeon.com/products/powerserver) and see whether the application, when deployed with PowerServer 2021, can work with MySQL correctly. Note there is one troubleshooting tip about MySQL at: https://docs.appeon.com/pb2021/whats_new/ch01s05s04.html#d0e7272. 

And we would appreciate much if you can send your test case for our direct testing and study. If that's possible, please email to product@appeon.com.

Best regards, Julie

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 5 May 2021 17:29 PM UTC
  2. PowerBuilder
  3. # 3

Hi Sylvain;

  Just an FYI that PowerBuilder does not support MySQL...

http://docs.appeon.com/pb2019r3/connection_reference/ch02s01.html

Regards ... Chris

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 5 May 2021 18:05 PM UTC
Yes sorry, MySQL is not supported either. I suspect that it's in the way the PB ODBC "interface" driver deals with the MySQL's ODBC driver in the handshake between the two drivers. I also suspect that it would require an Engineering change to make NUMBER "officially" work. Because MySQL is not supported, I am afraid that you are on your own for this issue. :-(
  1. Helpful
Sylvain .
  1. Sylvain .
  2. Wednesday, 5 May 2021 20:05 PM UTC
I was sure of that 'on my own', but my point is how come for all other DW using NUMBER it is working except for one where it is build the same way and with the same column type as the others. In any case thank you again for your help.
  1. Helpful
Sylvain .
  1. Sylvain .
  2. Wednesday, 5 May 2021 20:13 PM UTC
Just remembered and I'm not too sure if I'm right, when PB sends the SQL to the DB server it will put a ? where the retrieval argument :al_key_id is, is there a configuring in PB or the ODBC that would put the actual value where the ? is at this time. So if the key I'm treading is 13 the ? will be the actual value 13
  1. Helpful
There are no comments made yet.
Matt Balent
Matt Balent Accepted Answer Pending Moderation
  1. Wednesday, 5 May 2021 16:43 PM UTC
  2. PowerBuilder
  3. # 4

Here is the same question from earlier this year.

 

In your case I assume the other datawindows without the issue don't have large enough numbers to cause it (yet).

Comment
Sylvain .
  1. Sylvain .
  2. Wednesday, 5 May 2021 17:01 PM UTC
Thank you for your reply, I know about that post, but like I said the key value I'm playing with is 1.
  1. Helpful
Sylvain .
  1. Sylvain .
  2. Wednesday, 5 May 2021 17:05 PM UTC
Just to make this clear about the key value, I'm reading all 3 tables the parent and the 2 child tables with the exact same key value witch is 1.
  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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.