QUERY TO MYSQL SHOWN ERROR 42S22 UNKNOW COLUMN

[ckwong]

Connect Mysql server =5.018

UniQuery1.SQL.Clear;
UniQuery1.SQL.Add('select name,center from user where name="user" and password="123"');
UniQuery1.Open;

It shown 42S22 unknown column 'user' in 'where clause'

What is the problem ?

[Dimon]

To solve the problem use single quotes instead of double, like this:

Code: Select all

UniQuery1.SQL.Add('select name,center from user where name=''user'' and password=''123'''); 

[ckwong]

Thanks

[alehandro]

Hi, Dimon
I was thinking to open new thread but found this, and this one is very similar to mine, but not very

So, preconditions:
MySQL Server 5.1 (latest GA)
UniDAC v3, C++Builder6,
using MySQL provider,
the TUniScript with configured UniConnection to my MySql DB;

Actions:
loaded my script into script compo using Load button, OK, executing....tons of errors

I"m showing you the minimal piece of sql that lead to the issue:

Code: Select all

#
### TABLE STRUCTURE FOR `t_cleanout`###
#

DROP TABLE IF EXISTS `t_cleanout`;
CREATE TABLE `t_cleanout` (
  `id` int(10) NOT NULL,
  `date` datetime NOT NULL,
  `address` text NOT NULL,
  `proptype` varchar(15) NOT NULL,
  `evicted` varchar(15) NOT NULL,
  `rooms` int(5) NOT NULL,
  `groundfloor` varchar(5) NOT NULL,
  `multi_lev` varchar(5) NOT NULL,
  `pets` varchar(5) NOT NULL,
  `garage` varchar(5) NOT NULL,
  `condo` varchar(5) NOT NULL,
  `comments` text NOT NULL,
  `authorized` varchar(64) NOT NULL,
  `agreed` varchar(5) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
INSERT INTO `t_cleanout`  VALUES ( "300" , "2010-07-11 21:44:23" , "" , "" , "" , "0" , "" , "" , "" , "" , "" , "" , "" , "" );

gives following error:

---------------------------
Error
---------------------------

#42S22Unknown column '300' in 'field list'
---------------------------
Abort Ignore
---------------------------

Of course I understand that manual correction of " char into '
for all the fields that are quoted will fix the issue, but
unfortunately this is not suitable with me at all

Why?
1) I have very huge scripts - 1+ MB of SQL (!)
2) that file is generated by another system and I'm just receiving it and trying to execute
3) this is a not one - time action, but rather periodic one
4) I've checked with MySQL Workbench ( a tool available form MySql site) and you know what?
it eat my script without ANY error reported with 100% successful result.

Conclusion:
UniDAC/MySQL has some issue when executing SQL statements.
SQL parsing engine is not compatible with MySQL and that is really upsetting.

Questions:
could you advice me regards
- is it possible to somehow configure UniDAC component (UniScript) to accept my script data
- if its not configurable - could you please consider this issue as a major one and schedule some fix in upcoming release?

Thanks for reading this all
Looking forward for your answer

[Dimon]

To solve the problem you should set the EnableUniSQL global variable from the Uni unit to False.

[alehandro]

To solve the problem you should set the EnableUniSQL global variable from the Uni unit to False.

Dimon, you shoot in 10th

Thank you very much
Maybe it worth to add this golden bit of info into Help file (UniDAC.chm) ?
as for me it is really important!

2) Please add a sample of MySQL Embedded server usage into Demos\UniDacDemo + detailed instruction how to configure UniConnection and what file (MySQL db dir, share dir from embedded server, etc) and where to put - I've collected these bits from forum,but again, would be better to put it just in one place

Good luck!

[Dimon]

To connect to MySQL Embedded server with TUniConnection, we added the EmbeddedParams connection option for specifiyng parameters of the Embedded connection. Also you should set the following properties:

Code: Select all

  UniConnection.ProviderName := 'MySQL';
  UniConnection.Database := DatabaseName;
  UniConnection.SpecificOptions.Values['Embedded'] := 'True';
  UniConnection.SpecificOptions.Values['EmbeddedPrams'] := '--basedir=.'#13#10'--datadir=data';

The --basedir parameter sets the base path for MySQL Embedded server. All paths are usually resolved relative to this.
The --datadir parameter sets the path to the data directory.
See MySQL Reference Manual for detailed description.

[stevel]

To connect to MySQL Embedded server with TUniConnection, we added the EmbeddedParams connection option for specifiyng parameters of the Embedded connection. Also you should set the following properties:

Code: Select all

  UniConnection.ProviderName := 'MySQL';
  UniConnection.Database := DatabaseName;
  UniConnection.SpecificOptions.Values['Embedded'] := 'True';
  UniConnection.SpecificOptions.Values['EmbeddedPrams'] := '--basedir=.'#13#10'--datadir=data';

The --basedir parameter sets the base path for MySQL Embedded server. All paths are usually resolved relative to this.
The --datadir parameter sets the path to the data directory.
See MySQL Reference Manual for detailed description.

Instead of entering this in code, I would like to enter the EmbeddedParams value in the property editor. But no matter what I try it does not recognize the entered string. Is it possible to do this?

[stevel]

To connect to MySQL Embedded server with TUniConnection, we added the EmbeddedParams connection option for specifiyng parameters of the Embedded connection. Also you should set the following properties:

Code: Select all

  UniConnection.ProviderName := 'MySQL';
  UniConnection.Database := DatabaseName;
  UniConnection.SpecificOptions.Values['Embedded'] := 'True';
  UniConnection.SpecificOptions.Values['EmbeddedPrams'] := '--basedir=.'#13#10'--datadir=data';

The --basedir parameter sets the base path for MySQL Embedded server. All paths are usually resolved relative to this.
The --datadir parameter sets the path to the data directory.
See MySQL Reference Manual for detailed description.

Instead of entering this in code, I would like to enter the EmbeddedParams value in the property editor. But no matter what I try it does not recognize the entered string. Is it possible to do this?

I found a way to do it:

In the connection properties editor for EmbeddedParams property value,
type --basedir=.
then press Alt and punch 010 and then press Alt again and punch 013
then type --datadir=.\data

This will work! I have EN-US Windows (English USA) I am not sure if it will work for people with other languages.

Thanks for such a great component.

Regards,
Steve Faleiro