menriquez
User
Junior Boarder
| Posts: 12 |   |
Karma: 0
|
|
include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/22 13:08 |
|
|
|
|
yes I am having a problem insert a record into mysql that contains a single-quote char. the mysql docs say it supports the escape "" char, but when I insert this into my delphi string before a single quote, anyDAC throws an exception.
any ideas appreciated...
- mark
|
|
Diman
Admin
Admin
| Posts: 1495 | |
Karma: 19
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/22 13:14 |
|
|
|
|
Hello
What kind of exception ?
Regards,
Dmitry
|
|
menriquez
User
Junior Boarder
| Posts: 12 | |
Karma: 0
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/22 14:16 |
|
|
|
|
hello Dmitry thxs for the quick reply...
I have been thinking about the problem...is it possible that I need to use the chr(27) call instead of using the forward-slash char ( ) when I find a single-quote?
As it was, I parsed every character in the text field, and when I found a single-quote, i inserted a forward slash, like thus..
function TForm1.scrubText(sIn: string): string;
var
i,idx: integer;
sScrub: string;
begin
SetLength(sScrub,1024);
idx := 0;
// sScrub := '';
if Length(sIn) > 0 then begin
for i := 0 to Length(sIn) do begin
// this is how delphi finds a single-quote
if (sIn\[i\] = '''') then begin
sScrub\[idx\] := '\';
inc(idx);
end
sScrub\[idx\] := sIn\[i\];
inc(idx);
end;
SetLength(sScrub,idx-1);
end
else
sScrub := sIn;
scrubText := sScrub;
end;
|
the problem is that anyDAC seems to disregard this escape char and interperts the single-quote as an end-of-field char still...i do not have the exception handy but I can recreate it if necessary...
For a temp fix, I just replaced the single-quote with a blank, but I would like to know how to insert single-quotes into mysql.
thanks.
- mark
|
|
Diman
Admin
Admin
| Posts: 1495 | |
Karma: 19
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/23 05:17 |
|
|
|
|
1) Once again, what is the exception ?
2) Please, provide your SQL.
3) As workaround, you can use parameters.
Regards,
Dmitry
|
|
menriquez
User
Junior Boarder
| Posts: 12 | |
Karma: 0
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/24 09:41 |
|
|
|
|
1. Here is the exception...
11:31:42::anyDAC SQL Exception-Exception class name = EADException
11:31:42::anyDAC SQL Exception-Exception message = [AnyDAC][Phys]-335. Parameter [30] data type is unknown
2. Here is the SQL...notice the "square" char in the "O'Hara" field. That is me trying to insert an Chr(27) (delphi escape char) before the single-quote
INSERT INTO CLMCTL Values('AIC','AFL','4001692','02','4001692-01','0611','9082','C',0,0,0,0,0,0,0,'N','24','2006-11-14'
,'2006-11-11','FL','2007-05-11','2007-05-11','10121',NULL,'O'HARA','BILLY','T'
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'C','NSA','Y','I','2006-07-11','2007-07-11','003'
,0,0,'001','0611-9082','2006-11-14','13:30:26','10172');
I get the same error if I insert a backslash char (what mySql expects) in the place of chr(27)
thanks.
- mark
Post edited by: menriquez, at: 2007/08/24 09:42
|
|
menriquez
User
Junior Boarder
| Posts: 12 | |
Karma: 0
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/24 11:34 |
|
|
|
|
here is the exception when I try using the backslash char....
INSERT INTO CLMCTL Values('AIC','AFL','4001692','02','4001692-01','0611','9082','C',0,0,0,0,0,0,0,'N','24','2006-11-14'
,'2006-11-11','FL','2007-05-11','2007-05-11','10121',NULL,'O'HARA','BILLY','T'
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'C','NSA','Y','I','2006-07-11','2007-07-11','003'
,0,0,'001','0611-9082','2006-11-14','13:30:26','10172');
13:19:31::anyDAC SQL Exception-Exception class name = EADException
13:19:31::anyDAC SQL Exception-Exception message = [AnyDAC][Phys]-335. Parameter [30] data type is unknown
NOTE: it would seem that your forum system is removing the back-slash character from my post...trust me when I say that a back-slash is present in the O'Hara field
it would seem this is a bug for sure...and btw, I can't use params because I generate this statement on-the-fly from type-data from a DBF table. Also, if I cut-n-paste the query into mysql query browser, it executes perfectly.
- mark
Post edited by: menriquez, at: 2007/08/24 11:38
|
|
knulo
User
Senior Boarder
| Posts: 38 | |
Karma: 1
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/24 16:18 |
|
|
|
|
Hi Mark,
you speak about forward slash but discuss a backslash problem... 
Nevertheless, it's not a AnyDAC problem, I think. The backslash is escape character in C and the combination backslash + single quote produces a single quote. What you need maybe backslash and single quote in your result string so you have to escape the backslash and the single quote like
- I would try two single quotes in a single quoted string instead of escaping with backslash.
- I would try double quotes as string delimiter...
- Why do you cannot use parameters? You can put : P1...: Pn (blanks after colons to avoid viewing smilies here) into your statement in case of occurrence of single quote in the data from DBF and fill a string list with the values. After that, you can fill the parameters list with values of string list.
## OFFTOPIC ## "Eaten" backslash in forum? Here comes a backslash within a quoted string (put code in 'code' marks): 
But you're right by that: preview and final appearance in forum differs badly (so I have to practice hard)!
Regards Knut
Post edited by: knulo, at: 2007/08/24 16:45
|
|
Diman
Admin
Admin
| Posts: 1495 | |
Karma: 19
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/24 23:49 |
|
|
|
|
knulo wrote:
- I would try two single quotes in a single quoted string instead of escaping with backslash.
- I would try double quotes as string delimiter...
- Why do you cannot use parameters? You can put : P1...: Pn (blanks after colons to avoid viewing smilies here) into your statement in case of occurrence of single quote in the data from DBF and fill a string list with the values. After that, you can fill the parameters list with values of string list.
First will not work in 1.12, but will in 2.0. Two other suggestions will work.
Also, you can turn AnyDAC preprocessing of SQL, by setting:
ResourceOptions.ParamCreate, MacroCreate, ParamExpand, MacroExpand and EscapeExpand to False.
|
|
menriquez
User
Junior Boarder
| Posts: 12 | |
Karma: 0
|
|
Re:include single-quote in delphi/mysql text field
|
|
Posted: 2007/08/27 07:20 |
|
|
|
|
you speak about forward slash but discuss a backslash problem...
yes...very sorry about that. i realized after the first posts that i was mixing the two up. thanks for working through my confusion and helping me with this issue. I believe I will try the preprocessing idea first, then fall back to double quotes if that does not work.
again, many thanks to both of you for your help with this matter.
- mark
|
|
|
