MySQL Bugs: #7649: Integer retrieval argument fails to retrieve

Bug #7649	Integer retrieval argument fails to retrieve
Submitted:	3 Jan 2005 23:37	Modified:	10 Aug 2006 8:32
Reporter:	Steve DeWitt	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.10	OS:	Windows (windows)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
I am using Powerbuilder 8.0.3 and obdc connector 3.51.10. When I create a datawindow that has a retrieval argument that is a number it works fine against a Linux MySQL server version 5.0.1 alpha but fails to retrieve anything when run against a windows 2000 mySQL server version 5.0.1 or 5.0.2. If I make my retrieval argument a string, even though the field in the database is numeric, it works fine. The pbtrace:

/*---------------------------------------------------*/
/*                 01/03/2005  15:11                 */
/*---------------------------------------------------*/
(980c6a8): DIALOG CONNECT TO TRACE ODBC:
(980c6a8): DBPARM=ConnectString='DSN=unified_archive_eck',PBCatalogOwner='root' (7594 MilliSeconds)
(980c6a8): PREPARE WITH BIND VARIABLES:
(980c6a8):   SELECT mast.staff_group,   
         mast.principal,   
         mast.source_suffix,   
         mast.client_code  
    FROM mast   
where archive_period = ?

(980c6a8): FLOAT Length=0 ID=:r_period *200411.0000000000000* (0 MilliSeconds)
(980c6a8): BIND SELECT OUTPUT BUFFER (DataWindow): (313 MilliSeconds)
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(980c6a8): EXECUTE: (281 MilliSeconds)
(980c6a8): FETCH NEXT: (0 MilliSeconds)
(980c6a8): 
 Error 1 (rc 100)
(980c6a8): ROLLBACK: (0 MilliSeconds)
(980c6a8): ROLLBACK: (0 MilliSeconds)
(980c6a8): PREPARE:
(980c6a8):   SELECT mast.staff_group,   
         mast.principal,   
         mast.source_suffix,   
         mast.client_code  
    FROM mast   
 (0 MilliSeconds)
(980c6a8): DESCRIBE: (35922 MilliSeconds)
(980c6a8): name=staff_group,len=5,type=VCHAR,pbt=2,dbt=12,ct=0,prec=4,scale=0
(980c6a8): name=principal,len=3,type=CHAR,pbt=1,dbt=1,ct=0,prec=2,scale=0
(980c6a8): name=source_suffix,len=5,type=VCHAR,pbt=2,dbt=12,ct=0,prec=4,scale=0
(980c6a8): name=client_code,len=10,type=VCHAR,pbt=2,dbt=12,ct=0,prec=9,scale=0
(980c6a8): COLUMNS INFORMATION: TABLE=mast OWNER=root (125 MilliSeconds)
(980c6a8): PRIMARY KEY RETRIEVE: (125 MilliSeconds)
(980c6a8): FOREIGN KEY RETRIEVE: (141 MilliSeconds)
(980c6a8): 
 Error 1146 (rc -1) : SQLSTATE = S0002
[MySQL][ODBC 3.51 Driver][mysqld-5.0.2-alpha-nt]Table 'root.pbcattbl' doesn't exist
(980c6a8): 
 Error 1 (rc 100)
(980c6a8): PREPARE:
(980c6a8):   SELECT mast.staff_group,   
         mast.principal,   
         mast.source_suffix,   
         mast.client_code  
    FROM mast   
where archive_period = 0
 (0 MilliSeconds)
(980c6a8): CANCEL: (0 MilliSeconds)
(980c6a8): PREPARE:
(980c6a8): SELECT mast.staff_group, mast.principal, mast.source_suffix, mast.client_code FROM mast  (0 MilliSeconds)
(980c6a8): DESCRIBE: (21156 MilliSeconds)
(980c6a8): name=staff_group,len=5,type=VCHAR,pbt=2,dbt=12,ct=0,prec=4,scale=0
(980c6a8): name=principal,len=3,type=CHAR,pbt=1,dbt=1,ct=0,prec=2,scale=0
(980c6a8): name=source_suffix,len=5,type=VCHAR,pbt=2,dbt=12,ct=0,prec=4,scale=0
(980c6a8): name=client_code,len=10,type=VCHAR,pbt=2,dbt=12,ct=0,prec=9,scale=0
(980c6a8): COLUMNS INFORMATION: TABLE=mast OWNER=root (110 MilliSeconds)
(980c6a8): PRIMARY KEY RETRIEVE: (109 MilliSeconds)
(980c6a8): FOREIGN KEY RETRIEVE: (109 MilliSeconds)
(980c6a8): 
 Error 1146 (rc -1) : SQLSTATE = S0002
[MySQL][ODBC 3.51 Driver][mysqld-5.0.2-alpha-nt]Table 'root.pbcattbl' doesn't exist
(980c6a8): 
 Error 1 (rc 100)
(980c6a8): PREPARE:
(980c6a8): SELECT mast.staff_group, mast.principal, mast.source_suffix, mast.client_code FROM mast where archive_period = 0  (0 MilliSeconds)
(980c6a8): CANCEL: (0 MilliSeconds)
(980c6a8): PREPARE:
(980c6a8): SELECT mast.staff_group, mast.principal, mast.source_suffix, mast.client_code FROM mast WHERE archive_period = '0'  (0 MilliSeconds)
(980c6a8): CANCEL: (0 MilliSeconds)
(980c6a8): PREPARE WITH BIND VARIABLES:
(980c6a8):   SELECT  mast.staff_group ,           mast.principal ,           mast.source_suffix ,           mast.client_code     FROM mast      WHERE ( archive_period = ? )   
(980c6a8): VCHAR Length=6 ID=:r_period *200411* (0 MilliSeconds)
(980c6a8): BIND SELECT OUTPUT BUFFER (DataWindow): (27906 MilliSeconds)
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(980c6a8): ,len=2,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0

How to repeat:
change the retireval argument from a number in the powerbuilder datawindow to a string and back again. The filed in the database must be of type numeric

Could you please can provide the dump file of the table. Please add it as attached
file using the Files tab.

Thanks in advance.

dump of problem tables. Mast is the table that I am most concerned with

Attachment: dump.sql (www/unknown, text), 53.41 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

What else was requested besides the dump file of the tables affected?

Bug #20537 was marked as a duplicate of this one.

Hi Steve and sorry for the delay.
Since I'm new to PB can you post workspace file which demonstrates this error?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

What's you need ??

My english is not very good, but if i can help you ??

Etienne

Hello. What I need is rather simple, a working PB project with all necessary files demonstrating this behavior.

Hello,

I've for you :
 a pb project 
 a sql dump of database
 a log with the bug

When you start the pb application, a window with X lines appears.

Click on a line and click the button modify, it's ok with the key = 12 or 256, but with 123456, no informations was return by the SQL ( see pbtrace.log ).

I've another Pb, when i want to add files to this bug with the link, i've this link in browser :

http://bugs.mysql.com/bug.php?id=7649&error=fp

how can i send you the zip files.

Thanks.

Etienne Duvette

See the bug 20537, the file PB project is attach.

Etienne thanks. Trying to reproduce.

Unfortunatelly, PowerBuilder 10.5 build 4500 does not recognize pbmysql.pbl file... Can anyone provide me with input on this?
Etienne, can you split OPTION=4195 in individual options (a+b+c...) and post it here?

yes, it's for PB7.
I've export all objects ans send package in other bug 20537
Perhaps you can import in PB10.

i've try with another option, it's the same result.

Description of OPTION = 4195

4096 
Indique au serveur qu'il peut ignorer l'espace après les noms de fonctions, avant la parenthèse ouvrante ‘(’ (exigé par PowerBuilder). Cela transforme tous les noms de fonctions en mots clés 

64 
Ignore l'utilisation du nom de la base de données dans la syntaxe db_name.tbl_name.col_name. 

32 
Active ou désactive le support des curseurs dynamiques. (Interdit en MyODBC 2.50.) 

2 
Le client ne peut pas gérer la vraie valeur des lignes modifiées. Si cette option est active, MySQL retourne ``found rows'' à la place. Vous devez avoir MySQL 3.21.14 ou plus récent pour faire fonctionner cette option. 

1 
Le client ne peut pas gérer la taille réelle des colonnes retournées par MyODBC.

Thanks, looking.

Etienne, I still can't make PB use your sample. My knowledge of PB is rather limited, I must admit... I've found following in documentation:
The following table shows some recommended option values for various configurations:
  Configuration         Option Value
  Sybase PowerBuilder     135168
Does this help?

I've test with the option = 135168, it's the same problem.
I will demand in forum powerbuilder , who can convert in PB10 my example in PB7.

Etienne, please do so since my license will expire by the end of this week.

i've post the zip files in the forum powerbuilder.

http://forums.sybase.com/cgi-bin/webnews.cgi?cmd=item-27126&group=sybase%2Epublic%2Epowerb...

I'm waiting for response

I've post a new version of PBL ( Bug #20537 )

Help to convert :

1.  Put pbmysql.pbl into a directory - let's call it c:\pbmysql.

2.  Start PB.

3.  Go to the File menu and choose New.

4.  Go to the Target tab.

5.  Doubleclick on Existing Application.

6.  Find C:\pbmysql\pbmysql.pbl in the explorer that is shown.

7.  Click on the plus sign to expand it - it will show your "mysql" 
application object.

8.  Select the "mysql" application object and click Next.

9.  Click Next to accept the default library list.

10.  Click Finish to accept c:\pbmysql\mysql.pbt as the Target name.

11.  Click OK on the next window shown ("Migrate Application") to tell PB to

migrate the application.

Etienne, finally I got it working.
My environment is:
  * MySQL 5.0.23-log (mysqld-max-nt), nothing special in my.ini
  * MyODBC 3.51.12 GA
  * Win XP Pro SP2
  * PB 10.5
  * PB project/SQL script provided by you
As you can see from attached picture, I have no problem modifying any of the rows. I'll try to analyze Steve's example too.

PB sshot

Attachment: 7649.jpg (image/jpeg, text), 23.84 KiB.

I've used Etiennes sample project and passed Int argument / retrieved DB data with no problem. See attached images.

Data retrieval window

Attachment: 7649-IntArgRetr-1.jpg (image/jpeg, text), 16.21 KiB.

DB data retrieved passing integer argument

Attachment: 7649-IntArgRetrieved.jpg (image/jpeg, text), 11.15 KiB.

is it ok with all values ? , for me it's not ok with the key 123456 !!

it' ok with data retrieve but run the application and use 

click on the line with key 123456 and click on the button "modifier"

Is it ok ??

is the datawindow empty ??

see the pbtrace.log and the disk c:\wintNT

Etienne, I tried 12 and 123456. Everything works. You are using different schema for projects inside mysql. First one (Edit/Post) uses 'asso' and report uses 'test' schema (Input of argument). Maybe the problem is there?

Etienne, I see differences between PB logs. 
My log shows:
(28d3028): PREPARE WITH BIND VARIABLES:
(28d3028):   SELECT  educateur.no_educateur ,           educateur.nom_educateur ,           educateur.prenom_educateur ,           educateur.formation ,           educateur.code_etat ,           educateur.place     FROM educateur      WHERE ( educateur.no_educateur = ? )   
(28d3028): FLOAT Length=0 ID=:no_educ *123456*(DBI_PREPARE_WITHPARM) (0.053 MS / 15.190 MS)
(28d3028): BIND SELECT OUTPUT BUFFER (DataWindow):(DBI_SELBIND) (0.764 MS / 15.954 MS)
(28d3028): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(28d3028): ,len=52,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(28d3028): ,len=52,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(28d3028): ,len=52,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(28d3028): ,len=3,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(28d3028): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(28d3028): EXECUTE:(DBI_DW_EXECUTE) (0.759 MS / 16.713 MS)
(28d3028): FETCH NEXT:(DBI_FETCHNEXT) (0.045 MS / 16.758 MS)
(28d3028): 	COLUMN=123456	COLUMN=tert	COLUMN=terte	COLUMN=tet	COLUMN=A	COLUMN=4
(28d3028): FETCH NEXT:(DBI_FETCHNEXT) (0.065 MS / 16.823 MS)
(28d3028): *** DBI_FETCHEND *** (rc 100)

while your log has:
(28d3028): FLOAT Length=0 ID=:no_educ *123456.00000000*(DBI_PREPARE_WITHPARM) (0.053 MS / 15.190 MS)

and Steves log has:
(28d3028): VCHAR ...

I believe this comes from different versions of PB. Mine works as expected.
Make sure you use latest version of MyODBC available and upgrade your PB if necessary.

Ok, perhaps it's pb version...

I search a relaese 10 and return you the result.

Thanks for your help.

I've tested with pb 10.5 eval, 
the parameter is 123456 and not 12456.0000...
But i've nothing in my datawindows. => see the log.

My config

windows 2000
easyPhp 1.8
myodbc driver 3.51

(2aa4838): PREPARE WITH BIND VARIABLES:
(2aa4838):   SELECT  educateur.No_educateur ,           educateur.Nom_educateur ,           educateur.Prenom_educateur ,           educateur.Formation ,           educateur.code_etat ,           educateur.place     FROM educateur      WHERE ( educateur.No_educateur = ? )   
(2aa4838): FLOAT Length=0 ID=:educ *123456* (0.069 MS / 12.188 MS)
(2aa4838): BIND SELECT OUTPUT BUFFER (DataWindow): (0.778 MS / 12.966 MS)
(2aa4838): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(2aa4838): ,len=52,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(2aa4838): ,len=52,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(2aa4838): ,len=52,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(2aa4838): ,len=3,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(2aa4838): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(2aa4838): EXECUTE: (0.466 MS / 13.432 MS)
(2aa4838): FETCH NEXT: (0.077 MS / 13.509 MS)
(2aa4838): *** DBI_FETCHEND *** (rc 100)

please help me !! SOS SOS

Ettiene, I don't know what to do more... I have correct results on my machine. Please check you are not attaching to wrong database or something easily overlooked like that. I don't think there's serious problem involved now that parameter is passed correctly.

I've try to install mySQL 5, but now easyHP don't work.
I'm searching the problem and inform you leater.

Thanks

I've install PB 10.5, MySQL 5; Apache 2.0.58.
It's OK....

You can close the bug. 
I must upgrade my PB version for a version with licence.

Thanks, thanks..

I'm running PB 10.5 and MySQL 5.0.27-community-log. I have this issue in PB 9 as well, I just switched to PB 10.5 and I still have the issue. The trace logs:

/*---------------------------------------------------*/
/*                 11/6/2006  16:35                  */
/*---------------------------------------------------*/
(eb4c10): DIALOG CONNECT TO TRACE ODBC:
(eb4c10): DBPARM=ConnectString='DSN=MySQL;UID=root;PWD=rewt;option=135168;database=harris' (23.637 MS / 23.637 MS)
(eb4c10): PREPARE WITH BIND VARIABLES:
(eb4c10):   SELECT  tone.SwitchKey ,           tone.ToneKey ,           tone.ToneDescription ,           tone.ToneDefault ,           tone.ToneIndex ,           tone.ToneDuration ,           tone.SilenceDuration ,           tone.FrequencyOne ,           tone.AmplitudeOne ,           tone.FrequencyTwo ,           tone.AmplitudeTwo     FROM tone      WHERE ( tone.SwitchKey = ? ) and          ( tone.ToneKey = ? )   
(eb4c10): FLOAT Length=0 ID=:SwitchKey *1*
(eb4c10): FLOAT Length=0 ID=:ToneKey *57* (0.068 MS / 23.705 MS)
(eb4c10): BIND SELECT OUTPUT BUFFER (DataWindow): (0.589 MS / 24.294 MS)
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=42,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=3,type=CHAR,pbt=1,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): ,len=80,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(eb4c10): EXECUTE: (0.336 MS / 24.630 MS)
(eb4c10): FETCH NEXT: (0.143 MS / 24.773 MS)
(eb4c10): *** DBI_FETCHEND *** (rc 100)
(eb4c10): ROLLBACK: (0.003 MS / 24.776 MS)

It seems if I use a "Number" retrieval argument > 9 on a primary key field it doesn't work. If I use a "Number" retrieval argument on a none primary key field it seems to work for any number. Any help would be appreciated.

It seems if I use a "Number" retrieval argument > 9 on a primary key field it doesn't work. If I use a "Number" retrieval argument on a none primary key field it seems to work for any number. Any help would be appreciated.

It seems if I use a "Number" retrieval argument > 9 on a primary key field it doesn't work. If I use a "Number" retrieval argument on a none primary key field it seems to work for any number. Any help would be appreciated.

A setting in the PowerBuilder ODBC INI file (pbodbxx.ini) in the Sybase Shared Directory Add:

[MySQL]
PBSupportBindSelect='NO'
PBSupportBindUpdate='NO'
PBSupportDBBind='NO'

Hi John. I don't quite get your meaning. If you add those lines, things work?
Anyway, we will be releasing new version of MySQL connector/ODBC 3.51 shortly so watch for news.

Hello,

I'm testing actually PB 10.5 and mysql 4.1.
I've the same problem.

What is the probleme : Powerbuilder 10.5 or Mysql 4.1.

Etienne

To all. I was unable to repeat the problem on my machine... How can I work on a problem I can't reproduce? As this is 3rd party SW I am unable to give any definite answers too. I am sorry but please do recognize situation I'm in. Maybe this is worth reporting to PB too as they might already have reports like this.