ODBC driver problems

[Nico den Boer]

Hi all,

I'm working with:

MySql:
Database server: mysql-4.1.7-essential-win
ODBC driver: MyODBC-3.51.10-x86-win-32bit
Administrator: mysql-administrator-1.0.14-win
Query browser: mysql-query-browser-1.1.2-win

Visual FoxPro 8.0 SP1

In FoxPro I can connect to the database via ODBC. The used connect
string is:
lcConnectString =;
"DRIVER={MySQL ODBC 3.51 Driver};" +;
"SERVER=" + THIS.cServer + ";" +;
"UID=" + THIS.cUsrName + ";" +;
"PASSWORD=" + THIS.cPassword + ";" +;
"DB=" + THIS.cDatabase + ";" +;
"WSID=" + THIS.cWorkstationName + ";" +;
"PORT=3306;" +;
"SOCKET=;" +;
"OPTION=" + TRANSFORM(1+4+8+64+512+1024+2048+16384+524288) + ";" +;
"STMT;"

Retrieving/updatings table structures and indexes works fine.
When I retrieve a record, I see some strange things;
- text comes in as binary (general in VFP)
- varchar comes in as text (memo in VFP)
- decimal comes in as currency

Huh ?

The strange fields as they come in in VFP are:
Structure tb1
Number of data records: 1
Memo file block size: 64
Code Page: 1252
Field Field Name Type Width Dec
7 FLD14 Currency 8 4
8 FLD15 Currency 8 4
21 FLD27 Memo 4
26 FLD31 Currency 8 4
27 FLD32 General 4
30 FLD35 Memo 4
31 FLD36 Memo 4
43 FLD6 Currency 8 4

The SQL create for this table is:

CREATE DATABASE test CHARACTER SET latin1;

USE test;

CREATE TABLE tb1 (tb1rid int(8) NOT NULL AUTO_INCREMENT,
fld1 tinyint(1) NULL,
fld10 int(1) NULL,
fld11 tinyint(1) NULL,
fld12 int(2) NULL,
fld13 int(2) NULL,
fld14 decimal(5,2) NULL,
fld15 decimal(5,2) NULL,
fld16 tinyint(1) NULL,
fld17 tinyint(1) NULL,
fld18 tinyint(1) NULL,
fld19 int(3) NULL,
fld2 int(2) NULL,
fld20 date NULL,
fld21 tinyint(1) NULL,
fld22 tinyint(1) NULL,
fld23 int(10) NULL,
fld24 tinyint(1) NULL,
fld25 int(3) NULL,
fld26 date NULL,
fld27 varchar(100) NULL,
fld28 tinyint(1) NULL,
fld29 int(2) NULL,
fld3 date NULL,
fld30 int(2) NULL,
fld31 decimal(5,2) NULL,
fld32 text NULL COLLATE utf8_bin,
fld33 tinyint(1) NULL,
fld34 tinyint(1) NULL,
fld35 varchar(100) NULL,
fld36 varchar(100) NULL,
fld37 tinyint(1) NULL,
fld38 tinyint(1) NULL,
fld39 date NULL,
fld4 int(2) NULL,
fld40 tinyint(1) NULL,
fld41 tinyint(2) NULL,
fld42 int(3) NULL,
fld43 tinyint(3) NULL,
fld44 tinyint(3) NULL,
fld45 tinyint(3) NULL,
fld5 int(2) NULL,
fld6 decimal(4,2) NULL,
fld7 int(3) NULL,
fld8 int(4) NULL,
fld9 tinyint(1) NULL,
PRIMARY KEY(tb1rid)) ENGINE=MyISAM DEFAULT CHARSET=utf8
COLLATE=utf8_bin COMMENT='Configuratie';

Does anyone have an idea how this is caused and how it can be solved ?

Any help would be highly appreciated...

Kind regards, Nico