Embedding a carriage return and line feed in SQL
Philip Cain
assign to the field is built out of one or more lines of text
terminated by a carriage return and line feed (CRLF). The value of the
field is displayed in a multi-line edit box in a form.
When I enter the data through the form (by typing into the edit box,
the data is store correctly and the database doesn't complain about
the entry, even when it has one or more CRLFs in it.
But when I do the same thing in code, the database complains.
I set up my query this way:
MyQuery.SQL.Add('Update MyTable set Comments = '
+ dbQuote + C + dbQuote
+ ' where id = ' + IntToStr(SomeID))
dbQuote is a contstant with the value of a single qoute mark. I've
been using this convention for a long time.
C is a string that is built this way:
C := String1 + cCRLF + String2;
cCRLF is a constant declared this way:
cCRLF : string = Chr(13) + Chr(10);
That gives me a C value of
'String1'#D#A'String2'
Note that the CRLF is not in quotes.
The resulting SQL value is two strings separated by the CRLF and that
causes the TQuery to treat the second string differently and complain
sometimes. For example, if there is a colon in the second string, the
query parses it as a parameter and complians that it the parameter is
undefined.
How do I pass all this stuff to SQL as a single string, all out of
bounds for the query parser?
BTW, #13#10 doesn't work either. Same problem.
TIA
Phil Cain
--
Ufuk KESKINOZ
if your field is string or memo, you should use double quote!
try using dbquote variable as double quotation mark:
const dbquote=chr(34); // (") double quote
sincerely,
Ufuk Keskinoz,
System Engineer, Istanbul
Philip Cain wrote:
> MyQuery.SQL.Add('Update MyTable set Comments = '
> + dbQuote + C + dbQuote
> + ' where id = ' + IntToStr(SomeID))
>
> dbQuote is a contstant with the value of a single qoute mark. I've
> Note that the CRLF is not in quotes.
>
Philip Cain
>if your field is string or memo, you should use double quote!
>try using dbquote variable as double quotation mark:
>
> const dbquote=chr(34); // (") double quote
>
Ufuk,
This is interesting. The SQL/92 standard is a single quote, not a
double quote. I have been using a single quote in Interbase for 3
years and have never had a problem.
But I see in the InterBase manual that the example for a <val> is
given as "string" -- with double quotes. There is no explanation for
this and no comment on why the example varies from the standard.
I'll try it out.
Thanks.
Phil Cain
--
Philip Cain
It didn't work. I have the same problem.
The problem is in the TQuery and not in InterBase. I can put carriage
returns into the database when I use the DBedit box, but not when I do
it in the code. It doesn't matter, apparently whether I use single or
double quotes for the SQL.
Phil Cain
--
Alejandro Pablo Reinhold
. Comments DataType ?
. Server error message ?
Regards
Alejandro Reinhold
Philip Cain
>Two Questions:
>
> . Comments DataType ?
In the dataset, it is ftMemo. It was created as a long varchar.
>
> . Server error message ?
I get messages from the VCL and from the server, all because the VCL
is parsing the string incorrectly. For example, if part of the string
includes the characters:
'I called the manager at 5:30'
Then the VCL complains that it doesn't know the data type for Field
:30. But that string should have been part of the stored value, not
part of the SQL.
This happens only if the '5:30' characters follow a carriage return
and line feed. If the characters appear before the first carriage
return, then the VCL does not complain and the value is stored in the
database properly.
The problem is that the VCL does not recognize that the carriage
return and linefeed characters are part of the value to be stored in
the comments field. See the original post for the syntax I used.
Phil Cain
--
Bill Karwin
interchangeably, and allowed you to use them as string literal
delimeters.
I've recently given some hints on the newsgroups about a new feature in
InterBase 6.0: double quotes will be interpreted as per the ANSI SQL
"delimited identifiers" feature, so you can have keywords, spaces,
international characters, and lowercase characters in metadata object
names. There will also be a backward-compatibility mode to preserve
pre-6.0 behavior, in case you use double-quotes for string literals and
don't want to change your code yet.
But I advise everyone to start using only SINGLE QUOTES for string
literals, to help prepare your apps so that it's easy for you to move to
the new feature in InterBase 6.0.
In the InterBase manuals in versions 5.0, 5.1.1, and 5.5, we have been
gradually rewriting examples to use only single quotes. I think by 5.5
we changed all of these instances in the manuals.
Philip Cain wrote:
>
> This is interesting. The SQL/92 standard is a single quote, not a
> double quote. I have been using a single quote in Interbase for 3
> years and have never had a problem.
>
> But I see in the InterBase manual that the example for a <val> is
> given as "string" -- with double quotes. There is no explanation for
> this and no comment on why the example varies from the standard.
--
Bill Karwin
InterBase Product Manager
Borland division, Inprise Corp.
Ufuk Keskinoz
> In the dataset, it is ftMemo. It was created as a long varchar.
is there a 'long varchar' declaration in InterBase ?
create a table:
CREATE TABLE MyTable( comments varchar(32765))
try same things with this field...
i ve checked it out, but no problem occurred (tried also 2nd string which
contains colon character)
if again same problem occurs, then
send that bugged source code & Interbase data prototype,
i would try to debug it for you.
Ufuk Keskinoz,
Aage Johansen
MySQLString := dbQuote + C + dbQuote;
MyQuery.SQL.Add('Update MyTable set Comments = :XXX '
+ ' where id = ' + IntToStr(SomeID))
MyQuery.ParameterByName('XXX') := MySQLString;
MyQuery.ExecSQL;
Philip Cain wrote:
> I use a TQuery to update a large field in my database. The value I
> assign to the field is built out of one or more lines of text
> terminated by a carriage return and line feed (CRLF). The value of the
> field is displayed in a multi-line edit box in a form.
>
> When I enter the data through the form (by typing into the edit box,
> the data is store correctly and the database doesn't complain about
> the entry, even when it has one or more CRLFs in it.
>
> But when I do the same thing in code, the database complains.
>
> I set up my query this way:
>
> MyQuery.SQL.Add('Update MyTable set Comments = '
> + dbQuote + C + dbQuote
> + ' where id = ' + IntToStr(SomeID))
>
> dbQuote is a contstant with the value of a single qoute mark. I've
> been using this convention for a long time.
>
> C is a string that is built this way:
>
> C := String1 + cCRLF + String2;
>
> cCRLF is a constant declared this way:
> cCRLF : string = Chr(13) + Chr(10);
>
> That gives me a C value of
> 'String1'#D#A'String2'
>
> Note that the CRLF is not in quotes.
>
> The resulting SQL value is two strings separated by the CRLF and that
> causes the TQuery to treat the second string differently and complain
> sometimes. For example, if there is a colon in the second string, the
> query parses it as a parameter and complians that it the parameter is
> undefined.
>
> How do I pass all this stuff to SQL as a single string, all out of
> bounds for the query parser?
>
Philip Cain
>But I advise everyone to start using only SINGLE QUOTES for string
>literals, to help prepare your apps so that it's easy for you to move to
>the new feature in InterBase 6.0.
Good news. Bill. Thanks.
My problem turns out to be TQuery, which doesn't parse SQL very
wittily.
Now my problem is getting a long string into a varchar(10240). Not
even IB_objects will allow me to put more than 512 bytes there. Is
there some setting I'm missing?
Phil Cain
--
Philip Cain
>> DataType
>> In the dataset, it is ftMemo. It was created as a long varchar.
>
>is there a 'long varchar' declaration in InterBase ?
>
>create a table:
> CREATE TABLE MyTable( comments varchar(32765))
>
Ufuk,
It's a varchar(10240).
I discovered the problem with quotes and colons. It's not in
InterBase. It's in the TQuery, which does not parse very well and I
think I can get around the problem with a query parameter.
But I have another problem. Even though I've defined the field as a
long varchar, I can't put more than 512 bytes into it. I get an
undefined SQL error. This happens even when I bypass the BDE and do it
with IB_Objects.
How do you get more than 512 characters into a varchar field?
Phil Cain
--
Philip Cain
>Could you use a Query with parameter, like the following:
>
> MySQLString := dbQuote + C + dbQuote;
>
> MyQuery.SQL.Add('Update MyTable set Comments = :XXX '
> + ' where id = ' + IntToStr(SomeID))
> MyQuery.ParameterByName('XXX') := MySQLString;
> MyQuery.ExecSQL;
>
Aage,
Thanks. I think that will do it.
Phil Cain
--
Ufuk Keskinoz
apply the instructions carefully,
> It's a varchar(10240).
> I discovered the problem with quotes and colons. It's not in
> InterBase. It's in the TQuery, which does not parse very well and I
> think I can get around the problem with a query parameter.
i applied your query as is, but no crlf problems occured,
i think you sould reinstall your program..........
evertyhing is ok!
> But I have another problem. Even though I've defined the field as a
> long varchar, I can't put more than 512 bytes into it. I get an
> undefined SQL error. This happens even when I bypass the BDE and do it
> with IB_Objects.
first of all you should use TMemo control instead of TEdit control
create table MyTable (comments varchar(10240));
your string1,string2 variables must be assigned like that:
string1:=memo1.lines.text;
string2:=memo2.lines.text;
-------------pacal declaration------------------------------
procedure TForm1.Button1Click(Sender: TObject);
const ccrlf=chr(13)+chr(10);
dbquote=chr(34);
var c,string1,string2:string;
begin
string1:=memo1.lines.text;
string2:=memo2.lines.text;
c:=string1+ccrlf+string2;
query1.close;
query1.sql.clear;
query1.sql.add('insert into Mytable values('
+dbquote+c+dbquote+')');
query1.execsql;
end;
--------------------------------------------------------
> How do you get more than 512 characters into a varchar field?
use TDbMemo control,
wishing the evertying be ok!
Good Luck,
Ufuk Keskinoz
Alejandro Pablo Reinhold
1) If the Sql.text property includes ':' characters and the text
don't includes parameters, then you must set the TQuery.ParamCheck to
FALSE. The ":" characters will be treated as part of the text.
This eliminates the message "Unknown data type" from Delphi.
2) I don't find any problem with your original "quote" problem. The next
code works ok:
const
dbQuote = '''';
cCRLF : string = Chr(13) + Chr(10);
var
c : string;
begin
c := 'ABC'+cCrLf+'5:00';
MyQuery.ParamCheck := false;
MyQuery.Sql.Text :=
'Update MyTable set Comments = ' + dbQuote + C + dbQuote +
' where id = 1';
MyQuery.ExecSql;
3) I don't find any problem about the 512 charactes limit. In the next
example the database stores all 1000 characters.
const
dbQuote = '''';
cCRLF : string = Chr(13) + Chr(10);
var
c : string;
i : integer;
begin
c := '';
for i := 1 to 1000 do
c := c + 'x';
MyQuery.ParamCheck := false;
MyQuery.Sql.Text :=
'Update MyTable set Comments = ' + dbQuote + C + dbQuote +
' where id = 2';
MyQuery.ExecSql;
Regards
Alejandro Reinhold
Philip Cain wrote:
> Alejandro Pablo Reinhold <sist...@gecom.com.ar> wrote:
>
> >Two Questions:
> >
> > . Comments DataType ?
> In the dataset, it is ftMemo. It was created as a long varchar.
>
> >
Philip Cain
>...
>i applied your query as is, but no crlf problems occured,
>i think you sould reinstall your program..........
>
>evertyhing is ok!
>...
>c:=string1+ccrlf+string2;
>query1.close;
>query1.sql.clear;
>query1.sql.add('insert into Mytable values('
>+dbquote+c+dbquote+')');
>query1.execsql;
Everyting is not ok. It depends on the string you use.
Here's a string that won't work:
It's a rule: "gravity pulls you down."
Put that string in string1 and string2 exactly as I have it. You will
not be able to make the resulting string, c, show up exactly like that
in the database by using the method you show. Try it out. You'll be
surprised.
>
>> But I have another problem. Even though I've defined the field as a
>> long varchar, I can't put more than 512 bytes into it. I get an
>> undefined SQL error. This happens even when I bypass the BDE and do it
>> with IB_Objects.
>
>
> first of all you should use TMemo control instead of TEdit control
>
I dont' know if TMemo does it any differently than TEdit but I'll
check it out.
Anyway, I can't use any component for this. The data is not entered in
the UI. I'm converting data from one database to another and I must do
it all in code. This is not part of the parsing problem I talk about
above. This is different.
InterBase will not allow me to put more than 512 characters in that
varchar(10240) field. No matter how I format the characters.
BTW, I can get more than 512 characters in a TEdit control. The
problem is not a limitation in the control. It's a limitation in
InterBase.
Phil Cain
--
Philip Cain
>1) If the Sql.text property includes ':' characters and the text
> don't includes parameters, then you must set the TQuery.ParamCheck to
> FALSE. The ":" characters will be treated as part of the text.
> This eliminates the message "Unknown data type" from Delphi.
>
Alejandro,
I never thought of that. Thanks.
Now the problem is that InterBase won't let me load more than 512
characters into a varchar(10240) field. Do you know how to do that?
I've tried by using a TEdit component in my app, by doing it entirely
in code, and by using IB_objects (directly into the InterBase API) and
the result is always the same. I get an unknown SQL error and it
always occurs if I try to enter a field value that is 513 characters
long or longer.
This is obviously some kind of InterBase limitation that I don't
understand. Can you help me?
TIA
Phil Cain
--
Ufuk Keskinoz
I have solved the problem!
regards,
Ufuk Keskinoz, Istanbul
System Engineer
try this:
(cut&paste)
------------source code----------------------------
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure EliminateQuoteProblem(var string1:string);
var Findex,fposition:integer;FDummyStr:String;
begin
FDummyStr:='';//initials
Fposition:=1;
Findex:= pos('"',string1);
if FIndex=0 then exit;
repeat
string1[findex]:=' ';//assume problem ok
FDummyStr:=FDummyStr+copy(string1,Fposition,FIndex-Fposition)+chr(34)+chr(34);
FPosition:=Findex+1;//move next
Findex:= pos('"',string1);//is it ok
until FIndex=0;
string1:=fdummystr;//correct string
end;
procedure TForm1.Button1Click(Sender: TObject);
const ccrlf=chr(13)+chr(10);
dbquote=chr(34);
var c,string1,string2:string;
begin
string1:=memo1.lines.text;
string2:=memo2.lines.text;
EliminateQuoteProblem(string1); //call my function
EliminateQuoteProblem(string2); //call my function
c:=string1+ccrlf+string2;
query1.close;
query1.sql.clear;
query1.sql.add('insert into mytable values('
+dbquote+c+dbquote+')');
query1.execsql;
end;
---------------------------------source
code----------------------------------
Ufuk Keskinoz
i would like to add something also:
we declared :
var c,string1,string2: string;
pay attention to STRING type,
you should check your compiler to act STRING type as long string not
short string
because short string have max 255 chars as you know.
this may limit your entries...
in your problem it was not the colon which made problem,
it was the quote itself...
also it was nice to exchange ideas,
good luck!
Ufuk Keskinoz,Istanbul
System Engineer
Philip Cain wrote:
> Ufuk Keskinoz <ukes...@yahoo.com> wrote:
>
> >Hi again!
> >I have solved the problem!
>
> Ufuk,
>
> You work too hard!
>
> And you are quite right. This does stop the error messages from the
> TQuery and allow the string to go to the database.
>
> Thank you.
>
> Phil Cain
>
> --