Error 42000

777Yves · Post by **777Yves** » Tue 25 Jan 2011 20:17

Hi, I have a schema with several tables in it and they all have been created from one script generated by «Dbf To MySql» from IC.
When I try to open this table via a TMyTable, I receive the 42000 message.
All tables are ok except one that I try a DROP and was re-created by the following script generated by MySQL WorkBench. The table is empty:

Code: Select all

delimiter $$

CREATE TABLE `general` (
  `SOURCE` varchar(25) CHARACTER SET utf16 DEFAULT NULL,
  `PERMA` varchar(7) CHARACTER SET utf16 DEFAULT NULL,
  `PERMIS` varchar(6) CHARACTER SET utf16 DEFAULT NULL,
  `DEBUT` date DEFAULT NULL,
  `FIN` date DEFAULT NULL,
  `NIVEAU` varchar(2) CHARACTER SET utf16 DEFAULT NULL,
  `HOP` varchar(2) CHARACTER SET utf16 DEFAULT NULL,
  `PROG` varchar(2) CHARACTER SET utf16 DEFAULT NULL,
  `DATA1` bigint(20) DEFAULT NULL,
  `DATA2` bigint(20) DEFAULT NULL,
  `DATA3` bigint(20) DEFAULT NULL,
  `DATA4` bigint(20) DEFAULT NULL,
  `DATA5` decimal(10,2) DEFAULT NULL,
  `STR0` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR1` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR2` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR3` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR4` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR5` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR6` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR7` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR8` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `STR9` varchar(50) CHARACTER SET utf16 DEFAULT NULL,
  `MALADIE` bigint(20) DEFAULT NULL,
  `MATERNITE` bigint(20) DEFAULT NULL,
  `PARENTAL` bigint(20) DEFAULT NULL,
  `SANSSOLDE` bigint(20) DEFAULT NULL,
  `HOPHORSQC` bigint(20) DEFAULT NULL,
  `HOPHORSQCN` bigint(20) DEFAULT NULL,
  `ABANDON` bigint(20) DEFAULT NULL,
  `RETRAIT` bigint(20) DEFAULT NULL,
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Id`),
  KEY `Permis` (`PERMIS`),
  KEY `Perma` (`PERMA`),
  KEY `PermaNiveauHop` (`PERMA`,`NIVEAU`,`HOP`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2$$

Is there something wrong that you can see here ?

Thanks

AndreyZ · Post by **AndreyZ** » Wed 26 Jan 2011 13:38

Hello,

I cannot reproduce the problem. Please specify the following:
- the exact version of MyDAC. You can see it in the About sheet of TMyConnection Editor;
- the exact version of MySQL server and client. You can see it in the Info sheet of TMyConnection Editor.

777Yves · Post by **777Yves** » Thu 27 Jan 2011 00:03

Hi,

here is the info you request.

MyDAC MySQL Data Access v 6.00.2 for RAD Studio XE
MySQL server v 5.5.6-rc
Windows 7

A precision about that problem is that I could open, query, etc within WorkBench.

Thanks

AndreyZ · Post by **AndreyZ** » Thu 27 Jan 2011 10:27

I cannot reproduce the problem. Please try composing a small sample to demonstrate the problem and send it to andreyz*devart*com.

777Yves · Post by **777Yves** » Sat 29 Jan 2011 14:13

Well thing are weird sometime.
The problem disapear when I insert a record in the table.
And even if I delete that record, the problem is not there anymore.

Then I drop the table and rerun the script I send you.
Back in Delphi XE
If I click on the component that is already there TMyTable.active = true, Ok.
If I drop another TMyTable, table = general, active = true, error 42000.

I don't know how I can send you an example of that problem ?

Yves

AndreyZ · Post by **AndreyZ** » Tue 01 Feb 2011 10:40

Please try dropping the "general" table and creating it using the TMyQuery component in the following way:

Code: Select all

  MyQuery.SQL.Clear;
  MyQuery.SQL.Text := 'CREATE TABLE `general` (' +
  '`SOURCE` varchar(25) CHARACTER SET utf16 DEFAULT NULL,' +
  '`PERMA` varchar(7) CHARACTER SET utf16 DEFAULT NULL,' +
  '`PERMIS` varchar(6) CHARACTER SET utf16 DEFAULT NULL,' +
  '`DEBUT` date DEFAULT NULL,' +
  '`FIN` date DEFAULT NULL,' +
  '`NIVEAU` varchar(2) CHARACTER SET utf16 DEFAULT NULL,' +
  '`HOP` varchar(2) CHARACTER SET utf16 DEFAULT NULL,' +
  '`PROG` varchar(2) CHARACTER SET utf16 DEFAULT NULL,' +
  '`DATA1` bigint(20) DEFAULT NULL,' +
  '`DATA2` bigint(20) DEFAULT NULL,' +
  '`DATA3` bigint(20) DEFAULT NULL,' +
  '`DATA4` bigint(20) DEFAULT NULL,' +
  '`DATA5` decimal(10,2) DEFAULT NULL,' +
  '`STR0` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR1` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR2` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR3` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR4` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR5` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR6` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR7` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR8` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`STR9` varchar(50) CHARACTER SET utf16 DEFAULT NULL,' +
  '`MALADIE` bigint(20) DEFAULT NULL,' +
  '`MATERNITE` bigint(20) DEFAULT NULL,' +
  '`PARENTAL` bigint(20) DEFAULT NULL,' +
  '`SANSSOLDE` bigint(20) DEFAULT NULL,' +
  '`HOPHORSQC` bigint(20) DEFAULT NULL,' +
  '`HOPHORSQCN` bigint(20) DEFAULT NULL,' +
  '`ABANDON` bigint(20) DEFAULT NULL,' +
  '`RETRAIT` bigint(20) DEFAULT NULL,' +
  '`Id` int(11) NOT NULL AUTO_INCREMENT,' +
  'PRIMARY KEY (`Id`),' +
  'KEY `Permis` (`PERMIS`),' +
  'KEY `Perma` (`PERMA`),' +
  'KEY `PermaNiveauHop` (`PERMA`,`NIVEAU`,`HOP`)' +
') ENGINE=InnoDB DEFAULT CHARSET=ucs2';
  MyQuery.Execute;
  MyTable.TableName := 'general';
  MyTable.Open;

Do you have the error 42000 on executing MyTable.Open ?

777Yves · Post by **777Yves** » Tue 01 Feb 2011 15:22

Hi,
I have drop Table General via WorkBench.

In delphi, I have created a new application.
Drop a TMyQuery, a TMyTable, a TMyConnection, a TBotton.
Copy your code in the TBotton onClick.
F9 then click on the botton.
The following error message appear but the table is created in MySql.

Code: Select all

Exception 'first chance' à $75D2B727. Classe d'exception EMySqlException avec un message
'
#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'general' at line 1'.
Processus PetitFmrq.exe (5200)

AndreyZ · Post by **AndreyZ** » Wed 02 Feb 2011 10:28

It seems that it's a MySQL bug. You can find more information about it here: http://bugs.mysql.com/bug.php?id=57899
To solve the problem please try using the following code:

Code: Select all

...
MyQuery.Execute;
MyTable.Options.QuoteNames := True;
MyTable.TableName := 'general';
MyTable.Open;

777Yves · Post by **777Yves** » Thu 03 Feb 2011 14:59

Hi, well if I quote the name, there is no error.

Thanks.

AndreyZ · Post by **AndreyZ** » Thu 03 Feb 2011 16:05

It is good to see that this problem was solved. Feel free to contact us if you have any further questions about MyDAC.