'show create table' no longer works after upgrade to MySQL 5

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for MySQL in Delphi and C++Builder
Post Reply
ffcDev
Posts: 8
Joined: Mon 05 May 2008 14:00

'show create table' no longer works after upgrade to MySQL 5

Post by ffcDev » Mon 05 May 2008 14:23

Hi,

I recently upgraded my MySQL server from a version 4 to version 5, and now "show create table " no longer works.

The column "Create Table" in the returned dataset used to be an ftMemo with the table's create statement. Now it returns an ftString with just the name of the table.

Here's my setup info:

Server:
MySQL v5.0.51a
Linux Ubuntu v8.04

Client:
dbexpmda.dll v4.20.0.7
Delphi 7 (using dbexpmda.dll in Direct Mode)
Windows XP sp2

thanks,
Bill.

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 07 May 2008 13:42

On executing the "SHOW CREATE TABLE" query, different versions of MySQL server can return different field types for the "Create Table" field.
Probably your old server returned the ftMemo type for this field, and your new one returns the ftString type. But in any case should contain the table's create statement.

ffcDev
Posts: 8
Joined: Mon 05 May 2008 14:00

Post by ffcDev » Wed 07 May 2008 15:41

Yea, i don't mind that it's an ftString, but the create statement is messed up. I also get different results with different dbexpmda.dll versions.

I originally had dbexpmda.dll v4.0.0.2 and it was returning truncated create statements. (truncating around 900 characters.) So I upgraded to dbexpmda.dll v4.20.0.7 and it got worse. I no longer get a truncated create statement, i simply get the name of the table, without any create command, fields, indexes, etc. However if the table has a small create statement (lets say, under 900 characters) it works fine.

Can you try it on a table with a large create statement?

thanks,
Bill.

=== This Works ==========================
CREATE TABLE `NamePrefix` (
`NamePrefixID` int(11) NOT NULL default '0',
`Prefix` varchar(10) default NULL,
PRIMARY KEY (`NamePrefixID`)
) ENGINE=InnoDB

show create table NamePrefix

=====================================
the above example returns this:

Code: Select all

Table      Create Table
---------- -----------------------------------------------------
NamePrefix CREATE TABLE `NamePrefix` (
             `NamePrefixID` int(11) NOT NULL default '0',
             `Prefix` varchar(10) default NULL,
             PRIMARY KEY  (`NamePrefixID`)
           ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
=== This does not =========================
CREATE TABLE `WebAgent` (
`WebAgentID` int(11) NOT NULL,
`AgentID` int(11) default NULL,
`Prefix` varchar(10) NOT NULL,
`FirstName` varchar(20) NOT NULL,
`MiddleName` varchar(20) NOT NULL,
`LastName` varchar(20) NOT NULL,
`Suffix` varchar(10) NOT NULL,
`NickName` varchar(20) NOT NULL,
`PrimaryPhone` varchar(12) NOT NULL,
`PrimaryExtension` varchar(5) NOT NULL,
`EMail` varchar(50) NOT NULL,
`AcceptsEMail` tinyint(4) NOT NULL default '0',
`ValidEMail` tinyint(4) NOT NULL default '0',
`ValidateCode` int(11) default NULL,
`EnteredDate` date default NULL,
`ChangedDate` date default NULL,
`WebsitePassword` varchar(20) NOT NULL,
`CompanyName` varchar(50) NOT NULL,
`Address` varchar(50) NOT NULL,
`City` varchar(50) NOT NULL,
`State` varchar(4) NOT NULL,
`Zip` varchar(5) NOT NULL,
`Zip4` varchar(4) NOT NULL,
`UserAgent` varchar(255) NOT NULL,
`LoginDate` date default NULL,
`MarketingMethodID` int(11) default NULL,
PRIMARY KEY (`WebAgentID`)
) ENGINE=InnoDB

show create table WebAgent

=======================================

the above example returns this:

Code: Select all

Table    Create Table 
-------- ------------ 
WebAgent WebAgent 

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 08 May 2008 10:36

Thank you for information. This problem arises because MySQL v5.0.51a returns "Create Table" field length incorrectly.
We have fixed the problem. This fix will be included in the next dbxMda build.

Post Reply