MySQL Bugs: #44935: String fields do not work with Delphi

Bug #44935	String fields do not work with Delphi
Submitted:	18 May 2009 15:45	Modified:	14 Aug 2009 9:43
Reporter:	Dansoler Soler	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows (fields)
Assigned to:		CPU Architecture:	Any
Tags:	Some fields do not return

Description:
Hi, I`m using MYSQL in my host, and I try to conect with my programs with delphi, for this I'm using the drive ODBC 5.1. I Instaled and configured. Conection OK, but when I'm using select somes fields do not return, only fields integer and date, but char and varchar do not return. My table have varchar, and I change to char, but return the same error, some fields do not show. I'm using - select * , or select (name of fields).

Help me, thanks 

How to repeat:
conect ODBC drive 5.1 with internet

Hi Dansoler and thanks for your report.

Could you be more specific please regarding the info provided. Also, I would like Delphi project attached to this report (including DDL/DML) statements so I can test this.

Hi, I'm using delphi 6.0, I'm using DBE with drive 5 ODBC to conect my MYSQL in my host (My host are linux and has MYSQL, then I have create a lot of tables, with diferents fields, to use in my home page), I try to conect now my programs (mede in delphi) to conect to my MYSQL server, the conection is ok, but and I put the command select in my SQL (DBE) only returns filed integer, date, timestamp ... in my table has varchar and char, but I can not see it in my select return.

Can I help with more information?

Dansoler,
Can you show the exact definition of the fields in your table that unable to be retrieved?

OK, my table has ID = integer, data = date, and teste = varchar, but if you put only varchar, or oder the same error continued. Do you want my project of delphi or send me other project to conect to my databse?

I have a table let's say :
   create table a (somestring varchar(20));
and I insert three data with:
   insert into a values('one');
   insert into a values('two');
   insert into a values('three');
and I use select statement with MySQL GUI Tools with:
   Select 'hello' as string from a;
and the result is :
   string
   --------------------
   one
   two
   three
it works fine, but when I use Delphi 5, using TQuery, write SQL statement in its SQL properties with :
    Select 'halo' as string from a
it has no data, even field "string" is not recognized. 

When I try in MyODBC 2.5 or MyODBC 3.51 it work fine, the result is the same as from the GUI Tools MySQL 5.1.
Plz reply...

Hi Santoso, thanks for your input. Can you please create an ODBC trace an attach it to this bug? The process is described here:
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-trace.html

bug-data-44935.zip

Attachment: ODBC.rar (application/rar, text), 7.68 KiB.

We will check on logs but as I said many many times before, BDE is abandoned for more than a decade now... There is no way we can fix it's behavior nor anyone in the world will. Please use dbExpress drivers or ODBC/dbExpress bridge called OpenODBC. What you need (besides OpenODBC project which should be on SourceForge) is to modify two files to look somewhat like this:

  o File 1: dbxdrivers.ini
[Installed Drivers]
MYSQL=1
OOdbc=1

[MYSQL]
GetDriverFunc=getSQLDriverMYSQL
LibraryName=dbexpmys.dll
VendorLib=LIBMYSQL.dll
BlobSize=-1
Database=DBNAME
ErrorResourceFile=
HostName=ServerName
LocaleCode=0000
Password=***
User_Name=root

[OOdbc]
LibraryName=dbxoodbc.dll
GetDriverFunc=getSQLDriverODBC
VendorLib=odbc32.dll

  o File 2: dbxconnections.ini
[MSConnection]
BlobSize=4194304
Database=mysql
DriverName=MYSQL
ErrorResourceFile=
HostName=localhost
LocaleCode=0000
Password=
User_Name=root

[OOdbc]
DriverName=OOdbc
Database=DRIVER={MySQL ODBC 3.51 Driver};SERVER=some_server;DATABASE=mysql;USER=root;PASSWORD=***;Port=***;OPTION=3
User_Name=root
Password=***

and be rid of BDE once and for all... I'm using similar setup for rather large and complex dynamic reporting/charting tool without problems for almost 5 years now.

I can not do with dbexpress with delphi 6.0.

Yes you can. I'm having troubles with my VM containing exact setup right now, will try to post details later.

Bug#45993 was marked as duplicate of this report.

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

Explanation: Since BDE is *NOT* supported by original authors and being that it's closed source, we will not support it either. More over as original publisher has a replacement technology called dbX. The fact that c/ODBC 2.x "worked" with BDE bears no influence on this decision as c/ODBC 2.x was not ODBC compliant which 3.51 and 5.1 strive to be.

However, I have a soft spot for Delphi so here's what I do.
Externally:
  Be sure to have 
    dbexpmy4.dll   - part of Delphi
    dbexpmysql.dll - part of Delphi
    dbxoodbc.dll   - http://sourceforge.net/projects/open-dbexpress/
    libmySQL.dll   - part of MySQL server setup on Windows
    midas.dll      - part of Delphi
    odbc32.dll     - part of Win OS
    Install either 3.51 or 5.1 c/ODBC

  Edit files as follows:
  o File 1: dbxdrivers.ini
[Installed Drivers]
MYSQL=1
OOdbc=1

[MYSQL]
GetDriverFunc=getSQLDriverMYSQL
LibraryName=dbexpmys.dll
VendorLib=LIBMYSQL.dll
BlobSize=-1
Database=DBNAME
ErrorResourceFile=
HostName=ServerName
LocaleCode=0000
Password=***
User_Name=root

[OOdbc]
LibraryName=dbxoodbc.dll
GetDriverFunc=getSQLDriverODBC
VendorLib=odbc32.dll

  o File 2: dbxconnections.ini
[MSConnection]
BlobSize=4194304
Database=mysql
DriverName=MYSQL
ErrorResourceFile=
HostName=localhost
LocaleCode=0000
Password=
User_Name=root

[OOdbc]
DriverName=OOdbc
Database=DRIVER={MySQL ODBC 3.51
Driver};SERVER=**;DATABASE=mysql;USER=root;PASSWORD=**;Port=**;OPTION=XX
User_Name=root
Password=**

  Internally:
    In Delphi, add following dbExpress (and other) components with properties and relations as described:
TSQLConnection:SQLConnection
	DriverName: 	OOdbc
	GetDriverFunc: 	getSQLDriverODBC
	LibraryName:	dbxoodbc.dll
	Params:
		DriverName: 	OOdbc
		Database:	DRIVER={MySQL ODBC 3.51 Driver};SERVER=**;DATABASE=mysql;USER=**;PASSWORD=**;OPTION=XXX

TDatasetProvider:DatasetProvider
	Dataset:	SQLQuery

TSQLQuery:SQLQuery
	SQLConnection:	SQLConnection

TClientDataset:ClientDataset
	ProviderName:	DatasetProvider

TDataSource:DataSource
	Dataset: 	ClientDataset

Things just work for me...