QuotePrefix and QuoteSuffix

QuotePrefix and QuoteSuffix

Postby guiz » Fri 15 Apr 2005 09:01

I'm using MySQLDirect .NET 2.70. I have set QuotePrefix and QuoteSuffix to "`" to fix a problem about table names with spaces and special characters but it doesn't work.

It returns me an exception because it's not using prefixes correctly.

example:

You put this code
-->Insert command on Access (QuotePrefix and Quotesuffix = "[/]"):

And you have this Insert Command (tables could have spaces & special characters)
-->Insert Into [Table A]( [User Id], [User Name])


You put this code
-->Insert command on MySQL (QuotePrefix and Quotesuffix = "`"):

And MySQL seems not to work properly. It doesn't use prefix and suffix to contain table and colums names (I can't use spaces)
-->Insert Into Table A( User Id, User Name)

As you could see, it doesn't use prefixes to delimit table names and it causes an exception.

Maybe I'm not doing correctly. Do you have any idea?

Thanks
guiz
 
Posts: 11
Joined: Wed 09 Mar 2005 08:54
Location: Barcelona (SPAIN)

Postby Serious » Mon 18 Apr 2005 07:29

MySQLDirect .NET automatically quotes entity names in components properties and in CommandBuilder.
Example:
Code: Select all
     // create table: create table `table 1` (`col 1` int)
     MySqlConnection connection = new MySqlConnection("host=localhost;database=test;user id=root;");
     connection.Open();
     MySqlDataAdapter adapter = new MySqlDataAdapter("select * from `table 1`", connection);
     MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
     commandBuilder.Quoted = true;
     commandBuilder.QuotePrefix = commandBuilder.QuoteSuffix = "`";
     MessageBox.Show(commandBuilder.GetInsertCommand().CommandText); // INSERT INTO `table 1`(`col 1`) VALUES (:p1)
Our provider does not change user queries, so you must use valid quoting in your queries.
Example:
Code: Select all
MySqlCommand command;
...
command.CommandText = "insert into `a b c`.`table1` values (...";
Note that in new version of MySQLDirect .NET we added support for quoting and unquoting names that consist of numbers only or contain quotes.
Serious
 

Thanks

Postby guiz » Mon 18 Apr 2005 14:22

Thanks Serious, the problem was I was not using code line:

Code: Select all
commandBuilder.Quoted = true;

Now it's ok.
guiz
 
Posts: 11
Joined: Wed 09 Mar 2005 08:54
Location: Barcelona (SPAIN)


Return to dotConnect for MySQL