ADO and MySQL

Mike Manning

Delphi Developer

ADO and MySQL

2005-10-02 09:24:03 PM
delphi78
Hi all
I'm writing a Delphi 7 app with the ADO components to transfer data on a
regular basis from SQL Server 2000 to MySQL v4.0.24 (both remote). To
connect to the MySQL database I am using ADO with ODBC 3.51 from the MySQL
site.
All works fine except for the situation where I am using the ADOTable
component to append records which have varchar fields. It appears part
only of the varchar field values are loaded into the MySQL table.
eg when I appended 120 records which included one field which is
varchar(128) in both the MySQL table and the SQLServer table, only the first
10 chars of each field value were put into the MySQL table. The first
record it dealt with - the length of the data was in fact 10 chars - but
most of the other records had 40 to 80 chars - all truncated to 10 chars
when they got into MySQL.
(I checked that the full strings were being picked up from SQL Server).
I tried calling adotable.refresh before the append - but that didn't seem to
help at all.
I don't have any prior experience with this, but had heard that ADO / ODBC
3.51 / MySQL was reasonably stable and bug free.
Can anyone advise what the cause would be ?
thanks
Mike

Guillem

Delphi Developer

2005-10-03 04:26:23 PM

Re:ADO and MySQL

Hi,
Mike Manning writes:

Quote

Hi all
I'm writing a Delphi 7 app with the ADO components to transfer data
on a regular basis from SQL Server 2000 to MySQL v4.0.24 (both
remote). To connect to the MySQL database I am using ADO with ODBC
3.51 from the MySQL site.
All works fine except for the situation where I am using the ADOTable
component to append records which have varchar fields. It appears
part only of the varchar field values are loaded into the MySQL table.
eg when I appended 120 records which included one field which is
varchar(128) in both the MySQL table and the SQLServer table, only
the first 10 chars of each field value were put into the MySQL table.
The first record it dealt with - the length of the data was in fact
10 chars - but most of the other records had 40 to 80 chars - all
truncated to 10 chars when they got into MySQL.
(I checked that the full strings were being picked up from SQL
Server). I tried calling adotable.refresh before the append - but
that didn't seem to help at all.
I don't have any prior experience with this, but had heard that ADO /
ODBC 3.51 / MySQL was reasonably stable and bug free.

Can anyone advise what the cause would be ?
i've worked with ADO + MySQL 4.01 + MyODBC 3.51 without having such a

problem. Could we see your updating code?
Btw, better forget about ADOTable and use instead ADODataset or
ADOQuery. ADOTable is ok for working with desktop db but not with RDBMS
because it always tries to load the whole table
--
Best regards :)
Guillem Vicens
Dep. Informática Green Service S.A.
www.clubgreenoasis.com
in order to contact me remove the -nospam

Mike Manning

Delphi Developer

2005-10-03 08:12:49 PM

Re:ADO and MySQL

Thanks Guillem
I have switched to using the adodataset component (instead of adotable) and
the append method now seems to be working properly today.
regards
Mike
"Guillem" <XXXX@XXXXX.COM>writes

Quote

Hi,

Mike Manning writes:

>Hi all
>I'm writing a Delphi 7 app with the ADO components to transfer data
>on a regular basis from SQL Server 2000 to MySQL v4.0.24 (both
>remote). To connect to the MySQL database I am using ADO with ODBC
>3.51 from the MySQL site.
>All works fine except for the situation where I am using the ADOTable
>component to append records which have varchar fields. It appears
>part only of the varchar field values are loaded into the MySQL table.
>eg when I appended 120 records which included one field which is
>varchar(128) in both the MySQL table and the SQLServer table, only
>the first 10 chars of each field value were put into the MySQL table.
>The first record it dealt with - the length of the data was in fact
>10 chars - but most of the other records had 40 to 80 chars - all
>truncated to 10 chars when they got into MySQL.
>(I checked that the full strings were being picked up from SQL
>Server). I tried calling adotable.refresh before the append - but
>that didn't seem to help at all.
>I don't have any prior experience with this, but had heard that ADO /
>ODBC 3.51 / MySQL was reasonably stable and bug free.
>
>Can anyone advise what the cause would be ?

i've worked with ADO + MySQL 4.01 + MyODBC 3.51 without having such a
problem. Could we see your updating code?

Btw, better forget about ADOTable and use instead ADODataset or
ADOQuery. ADOTable is ok for working with desktop db but not with RDBMS
because it always tries to load the whole table
--
Best regards :)

Guillem Vicens
Dep. Informática Green Service S.A.
www.clubgreenoasis.com
--
in order to contact me remove the -nospam