Problem inserting special characters

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
jaballesco

Problem inserting special characters

Post by jaballesco » Tue 10 May 2005 17:21

Hi, everybody!
I have a problem working with MySQL and C++ Builder 6.
I can´t find the solution and I hope that maybe
somebody can help me.
I use the MyDAC components (3.50.0.20) to connect the MySQL
database (version 4.1.11) with C++ Builder. The fact
is that everything goes rigth except when I try to
write into the database. When I write special spanish
characters (accents, ñ,...) from Builder, this characters are
changed in the database, so they´re wrong. I think
that it occurs because the default character set of
C++ Builder is not supported by the MySQL database
(but I´m not sure about it). If anyone can explain me
the reason, I´ll be exceedingly grateful.
Thank you very much for your attention.
Regards,

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Wed 11 May 2005 06:34

At once after establishing a connection execute "SET NAMES ..." query. For details please see MySQL Reference Manual. You can get the same result if to modify server settings (my.ini).

vincenzo

Post by vincenzo » Wed 11 May 2005 14:43

you must escape all special char specialy ' that is a very special char
to delimit string parameter put before char \ like this \

jaballesco

Post by jaballesco » Wed 11 May 2005 17:36

Hi, Ikar and Vincenzo!
I have tried the Ikar solution (set names 'latin1' after connect to the server), but it doesn´t work. In fact, the client character set is latin1 by default, so it doesn´t change anything. I need to find out the way to write spanish characters correctly in the database from builder!!! (from the same client in comand line it works perfectly)
Thanks for your help. I hope new posts!
Regards (un saluto)

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Thu 12 May 2005 08:17

Do you pass values of the rows in the query body or through parameters? If the last, you needn't quote it using \.

vincenzo

Post by vincenzo » Thu 12 May 2005 10:09

I have done this on all sql string and works fine
before this i have the same problem
regards

AnsiString stTemp;

stTemp = StringReplace(stPass, "'", "\", TReplaceFlags() << rfReplaceAll);

vincenzo

Post by vincenzo » Thu 12 May 2005 10:14

Sorry there is a mistake

AnsiString stTemp;

stTemp = StringReplace(stPass, "'", "\", TReplaceFlags() << rfReplaceAll);

jaballesco

Post by jaballesco » Thu 12 May 2005 12:21

Hi again!!

The problem remains!! When you talk about special char ', do you refer to the char used for including strings?

When I use the next sentences:

MyQuery1->SQL->Clear();
MyQuery1->SQL->Add("insert into datos values (2,
'Andrés Perelló Domínguez','11222444-G','1965-2-13',
'C/ España cañí, nº1','922444555','2004-1-1',
'2006-12-31','No')");
MyQuery1->Execute();
MyTable1->Refresh();

I have the mentioned problem. Doing what Vincenzo said (including \ before the ' char (or \)), doesn´t change the result. Part of this result is:

AndrÚs Perell¾ DomÝnguez (second parameter)
C/ Espa±a ca±Ý (fifth parameter)

Guest

Post by Guest » Fri 13 May 2005 02:34

I use CB6 and MyDac and do NOT have this issue.

But i dont use AnsiStrings if i can help it...
Run this as a test...

char Q1[512];
sprintf(Q1,"%s","insert into datos values (2,'Andrés Perelló Domínguez')");
MyQuery1->SQL->Clear();
MyQuery1->SQL->Add(Q1);
MyQuery1->Execute();

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Fri 13 May 2005 07:57

> stTemp = StringReplace(stPass, "'", "\", TReplaceFlags() << rfReplaceAll);

' symbol is not the only one that requires special processing. It would be correct
to use string parameters to pass values.

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Fri 13 May 2005 07:58

Instead of

Code: Select all

MyQuery1->SQL->Clear();
MyQuery1->SQL->Add("insert into datos values (2,
'Andrés Perelló Domínguez','11222444-G','1965-2-13',
'C/ España cañí, nº1','922444555','2004-1-1',
'2006-12-31','No')");
MyQuery1->Execute();
MyTable1->Refresh();
use

Code: Select all

MyQuery1->SQL->Clear();
MyQuery1->SQL->Add("insert into datos values (:p1, :p2, :p3, ...)");
MyQuery1->Params[0]->AsInteger = 2;
MyQuery1->Params[1]->AsString = "Andrés Perelló Domínguez";
...
MyQuery1->Execute();
MyTable1->Refresh();
If MyTable1 refers to datos, it would be correct to perform edition in the following way:

Code: Select all

MyTable1->Edit();
MyTable1->Fields[0].AsInteger := ...
MyTable1->Fields[1].AsString := ...
MyTable1->Post();

Guest

Post by Guest » Fri 13 May 2005 09:07

Hi!!
Anonymous wrote: char Q1[512];
sprintf(Q1,"%s","insert into datos values (2,'Andrés Perelló Domínguez')");
MyQuery1->SQL->Clear();
MyQuery1->SQL->Add(Q1);
MyQuery1->Execute();
I have tried this way, but the problem still appears.

If I use the Ikar solution:
MyQuery1->SQL->Clear();
MyQuery1->SQL->Add("insert into datos values (:p1, :p2, :p3, ...)");
MyQuery1->Params[0]->AsInteger = 2;
MyQuery1->Params[1]->AsString = "Andrés Perelló Domínguez";
...
MyQuery1->Execute();
MyTable1->Refresh();
I have the next error compiling:

[C++ Error] Unit1.cpp(38): E2288 Pointer to structure required on left side of -> or ->*

Ikar, I don´t understand the second part of the code (MyTable1). Could you explain me why do you do it, please?[/code][/quote]

jaballesco

Post by jaballesco » Fri 13 May 2005 10:04

Sorry... The previous post is mine

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Fri 13 May 2005 14:28

> MyQuery1->Params[0]->AsInteger = 2;

This is a misprint. Use instead

MyQuery1->Params->Items[0]->AsInteger = 2

> Ikar, I don´t understand the second part of the code (MyTable1).
> Could you explain me why do you do it, please?

This is a standard method for data editing on CBuilder. Detailed information about used methods you can see in CBuilder help

jaballesco

Post by jaballesco » Fri 13 May 2005 16:40

Using string parameters to pass values gives the same result with wrong characters. :cry:
Thanks anyway, Ikar. I´ll continue trying to resolve it. If someone know another possibility, please, let me know!!

Post Reply