Board index » delphi » TQery.ExecSQL Problem with : and "

TQery.ExecSQL Problem with : and "

D5, BDE

procedure RunUpdateStatement(s: string);
begin
  with qQuery do
    begin
      Close;
      SQL.Text := s;
      ExecSQL;
    end;
end;

  These two statements work fine:
  RunUpdateStatement('insert into temp1 (d1, d2) values ('':'', '':'')');
  RunUpdateStatement('insert into temp1 (d1, d2) values ('':'', ''"'')');

  This one fails.  I get "Missing right quote."
  RunUpdateStatement('insert into temp1 (d1, d2) values (''"'', '':'')');

Thanks in advance,
George

 

Re:TQery.ExecSQL Problem with : and "


George Wynne was at one time quoted as saying:

Quote
> D5, BDE

> procedure RunUpdateStatement(s: string);
> begin
>   with qQuery do
>     begin
>       Close;
>       SQL.Text := s;
>       ExecSQL;
>     end;
> end;

>   These two statements work fine:
>   RunUpdateStatement('insert into temp1 (d1, d2) values ('':'', '':'')');
>   RunUpdateStatement('insert into temp1 (d1, d2) values ('':'', ''"'')');

>   This one fails.  I get "Missing right quote."
>   RunUpdateStatement('insert into temp1 (d1, d2) values (''"'', '':'')');

Different servers interpret the " and ' differently, but in my
experience, I've had more luck specifying a double quote using '''''' for
a string than using ".

--
Brian

Life is like a hot bath:
It feels good while you're in it, but the longer
you stay the more wrinkled you get.

Re:TQery.ExecSQL Problem with : and "


This is just a demo.  The double quote was input by the user.  I'm
surrounding it with two pairs of single quotes, which should be OK.  My
second and third RunUpdateStatements are identical, except that the
parameters are reversed.  I don't feel that I should have to StringReplace
user-input double quotes into two single quotes everytime I want to use
TQuery.ExecSQL.

Other ideas?

Thanks,
George

Quote
"Brian Gillespie" <it's_dewnap...@hotmail.com> wrote in message

news:MPG.16d1fc13ffbc99b98968b@newsgroups.borland.com...
Quote
> George Wynne was at one time quoted as saying:
> > D5, BDE

> > procedure RunUpdateStatement(s: string);
> > begin
> >   with qQuery do
> >     begin
> >       Close;
> >       SQL.Text := s;
> >       ExecSQL;
> >     end;
> > end;

> >   These two statements work fine:
> >   RunUpdateStatement('insert into temp1 (d1, d2) values ('':'',
'':'')');
> >   RunUpdateStatement('insert into temp1 (d1, d2) values ('':'',
''"'')');

> >   This one fails.  I get "Missing right quote."
> >   RunUpdateStatement('insert into temp1 (d1, d2) values (''"'',
'':'')');

> Different servers interpret the " and ' differently, but in my
> experience, I've had more luck specifying a double quote using '''''' for
> a string than using ".

> --
> Brian

> Life is like a hot bath:
> It feels good while you're in it, but the longer
> you stay the more wrinkled you get.

Re:TQery.ExecSQL Problem with : and "


George,

   Check out the QuotedStr function.

Good luck,
krf

Quote
George Wynne <geo...@nssco.com> wrote in message news:3c6932e8_1@dnews...
> I'm surrounding it with two pairs of single quotes, which should be OK.

Re:TQery.ExecSQL Problem with : and "


Is this what you are suggesting?

procedure RunUpdateStatement(s: string);
begin
  with qQuery do
    begin
      Close;
      SQL.Text := s;
      ExecSQL;
    end;
end;

  RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr(':')
+ ', ' + QuotedStr(':') + ')');
  RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr(':')
+ ', ' + QuotedStr('"') + ')');
  RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr('"')
+ ', ' + QuotedStr(':') + ')');

Same result.  The first two work, the second one fails.

Other ideas?

Thanks,
George

Quote
"Kevin Frevert" <kfrev...@midwayusa.com> wrote in message

news:3c69592c$1_1@dnews...
Quote
> George,

>    Check out the QuotedStr function.

> Good luck,
> krf

> George Wynne <geo...@nssco.com> wrote in message news:3c6932e8_1@dnews...
> > I'm surrounding it with two pairs of single quotes, which should be OK.

Re:TQery.ExecSQL Problem with : and "


George Wynne was at one time quoted as saying:

Quote
> Is this what you are suggesting?

> procedure RunUpdateStatement(s: string);
> begin
>   with qQuery do
>     begin
>       Close;
>       SQL.Text := s;
>       ExecSQL;
>     end;
> end;

>   RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr(':')
> + ', ' + QuotedStr(':') + ')');
>   RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr(':')
> + ', ' + QuotedStr('"') + ')');
>   RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr('"')
> + ', ' + QuotedStr(':') + ')');

> Same result.  The first two work, the second one fails.

> Other ideas?

The only other thing I can think of is to output the result of the string
you are building to an input box, copy the string from the input box into
a query window, and try and run the query from there to see if it runs
that way.

so instead of :

RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr(':')
+ ', ' + QuotedStr(':') + ')');

do:

RunUpdateStatement(InputBox('insert into temp1 (d1, d2) values (' +
QuotedStr(':') + ', ' + QuotedStr(':') + ')','',''));

You'll be able to select the text for the query from the input box and
run it against your database using your database query tool of choice.

--
Brian

Life is like a hot bath:
It feels good while you're in it, but the longer
you stay the more wrinkled you get.

Re:TQery.ExecSQL Problem with : and "


George,

   The QuotedStr function will make sure a mixture of both single/double in
a string are properly interpreted by parsers and you have to do something if
you are hard-coding double quotes, but I don't remember.  I think you have
to use 4 tick marks ('''') to equal one double quote character (or something
like that)

var
  DblQuotes: String;
..
  DblQuotes := ''''; {variable should now contain the double-quote
character}
...

Good luck,
krf

Quote
George Wynne <geo...@nssco.com> wrote in message news:3c6a98da$1_1@dnews...
> Is this what you are suggesting?

> Same result.  The first two work, the second one fails.

> Other ideas?

> Thanks,
> George

Re:TQery.ExecSQL Problem with : and "


I've essentially already tried this.  I printed out my qQuery.SQL.Text which
failed, which was:

insert into temp1 (d1, d2) values ('"', ':')

and that worked fine in SQL Server Query Analyzer.

I plan to submit a bug report to Borland.

Thanks,
George

Quote
"Brian Gillespie" <it's_dewnap...@hotmail.com> wrote in message

news:MPG.16d4421d1e98ae9e98968f@newsgroups.borland.com...
Quote
> George Wynne was at one time quoted as saying:
> > Is this what you are suggesting?

> > procedure RunUpdateStatement(s: string);
> > begin
> >   with qQuery do
> >     begin
> >       Close;
> >       SQL.Text := s;
> >       ExecSQL;
> >     end;
> > end;

> >   RunUpdateStatement('insert into temp1 (d1, d2) values (' +
QuotedStr(':')
> > + ', ' + QuotedStr(':') + ')');
> >   RunUpdateStatement('insert into temp1 (d1, d2) values (' +
QuotedStr(':')
> > + ', ' + QuotedStr('"') + ')');
> >   RunUpdateStatement('insert into temp1 (d1, d2) values (' +
QuotedStr('"')
> > + ', ' + QuotedStr(':') + ')');

> > Same result.  The first two work, the second one fails.

> > Other ideas?

> The only other thing I can think of is to output the result of the string
> you are building to an input box, copy the string from the input box into
> a query window, and try and run the query from there to see if it runs
> that way.

> so instead of :

> RunUpdateStatement('insert into temp1 (d1, d2) values (' + QuotedStr(':')
> + ', ' + QuotedStr(':') + ')');

> do:

> RunUpdateStatement(InputBox('insert into temp1 (d1, d2) values (' +
> QuotedStr(':') + ', ' + QuotedStr(':') + ')','',''));

> You'll be able to select the text for the query from the input box and
> run it against your database using your database query tool of choice.

> --
> Brian

> Life is like a hot bath:
> It feels good while you're in it, but the longer
> you stay the more wrinkled you get.

Re:TQery.ExecSQL Problem with : and "


As I've said before, I should not have to do a StringReplace to substitute
two single quotes for one double quote every time my users input data.

Thanks,
George

Quote
"Kevin Frevert" <kfrev...@midwayusa.com> wrote in message

news:3c6a9fa7_1@dnews...
Quote
> George,

>    The QuotedStr function will make sure a mixture of both single/double
in
> a string are properly interpreted by parsers and you have to do something
if
> you are hard-coding double quotes, but I don't remember.  I think you have
> to use 4 tick marks ('''') to equal one double quote character (or
something
> like that)

> var
>   DblQuotes: String;
> ..
>   DblQuotes := ''''; {variable should now contain the double-quote
> character}
> ...

> Good luck,
> krf

> George Wynne <geo...@nssco.com> wrote in message

news:3c6a98da$1_1@dnews...

- Show quoted text -

Quote
> > Is this what you are suggesting?

> > Same result.  The first two work, the second one fails.

> > Other ideas?

> > Thanks,
> > George

Re:TQery.ExecSQL Problem with : and "


Just curious, why not?  Simply run the QuotedStr function on whatever the
user enters and your done.  Your not replacing two single quotes with a
double-quote, the QuotedStr simply allows parsers to correctly interpret
strings that contain a mixture of quotation marks (single and double).  Run
MSSQL's Profiler to see what I mean (Query Analyzer does this too, you just
don't see it).

Another option is to use parameters.

In the SQL property (at design time):
Insert into MyTable
(Field1, Field2)
Values
(:Field1, :Field2)
- Set the parameters according (DataType = ftString, ParamType=ptInput)

In code...

procedure TMyDataModule.InsertFields(const Field1, Field2 :String);
begin
  with qInsert do
     begin
        ParamByName('Field1').AsString := Field1;
        ParamByName('Field2').AsString := Field2;
        ExecSQL
     end
end;

{Calling form}
procedure TForm1.Button1Click(Sender :TObject);
begin
   MyDataModule.InsertFields(Edit1.Text, Edit2.Text);
end;

Good luck,
krf

Quote
George Wynne <geo...@nssco.com> wrote in message news:3c6bc8e2$1_1@dnews...
> As I've said before, I should not have to do a StringReplace to substitute
> two single quotes for one double quote every time my users input data.

> Thanks,
> George

Re:TQery.ExecSQL Problem with : and "


I tried QuotedStr several posts ago in this thread.  It did not work.  Look,
the code in my original post should work.  It's a Borland bug, unless
someone can write for me a RunUpdateStatement that inserts a double quote
first and a colon second.  And the problem is not just the double quote; I
think the colon is throwing off the parsing somewhere.

The insert statement that I really want to write is created dynamically, and
has 50 or so parameters, so I think I'd rather just wait to hear from
Borland.

Thanks,
George

Quote
"Kevin Frevert" <kfrev...@midwayusa.com> wrote in message

news:3c6bd3cf$1_2@dnews...
Quote
> Just curious, why not?  Simply run the QuotedStr function on whatever the
> user enters and your done.  Your not replacing two single quotes with a
> double-quote, the QuotedStr simply allows parsers to correctly interpret
> strings that contain a mixture of quotation marks (single and double).
Run
> MSSQL's Profiler to see what I mean (Query Analyzer does this too, you
just
> don't see it).

> Another option is to use parameters.

> In the SQL property (at design time):
> Insert into MyTable
> (Field1, Field2)
> Values
> (:Field1, :Field2)
> - Set the parameters according (DataType = ftString, ParamType=ptInput)

> In code...

> procedure TMyDataModule.InsertFields(const Field1, Field2 :String);
> begin
>   with qInsert do
>      begin
>         ParamByName('Field1').AsString := Field1;
>         ParamByName('Field2').AsString := Field2;
>         ExecSQL
>      end
> end;

> {Calling form}
> procedure TForm1.Button1Click(Sender :TObject);
> begin
>    MyDataModule.InsertFields(Edit1.Text, Edit2.Text);
> end;

> Good luck,
> krf

> George Wynne <geo...@nssco.com> wrote in message

news:3c6bc8e2$1_1@dnews...

- Show quoted text -

Quote
> > As I've said before, I should not have to do a StringReplace to
substitute
> > two single quotes for one double quote every time my users input data.

> > Thanks,
> > George

Re:TQery.ExecSQL Problem with : and "


Come to think of it, Borland regards a colon in a SQL statement as the
beginning of a variable, right?  That is certainly what is confusing their
parser.  Still, it is strange that ther parser is confused by:

  RunUpdateStatement('insert into temp1 (d1, d2) values (''"'', '':'')');

but not
  RunUpdateStatement('insert into temp1 (d1, d2) values ('':'', ''"'')');

George

Quote
>"George Wynne" <geo...@nssco.com> wrote in message news:3c6c3b1e_1@dnews...
> I tried QuotedStr several posts ago in this thread.  It did not work.
Look,
> the code in my original post should work.  It's a Borland bug, unless
> someone can write for me a RunUpdateStatement that inserts a double quote
> first and a colon second.  And the problem is not just the double quote; I
> think the colon is throwing off the parsing somewhere.

> The insert statement that I really want to write is created dynamically,
and
> has 50 or so parameters, so I think I'd rather just wait to hear from
> Borland.

> Thanks,
> George

> "Kevin Frevert" <kfrev...@midwayusa.com> wrote in message
> news:3c6bd3cf$1_2@dnews...
> > Just curious, why not?  Simply run the QuotedStr function on whatever
the
> > user enters and your done.  Your not replacing two single quotes with a
> > double-quote, the QuotedStr simply allows parsers to correctly interpret
> > strings that contain a mixture of quotation marks (single and double).
> Run
> > MSSQL's Profiler to see what I mean (Query Analyzer does this too, you
> just
> > don't see it).

> > Another option is to use parameters.

> > In the SQL property (at design time):
> > Insert into MyTable
> > (Field1, Field2)
> > Values
> > (:Field1, :Field2)
> > - Set the parameters according (DataType = ftString, ParamType=ptInput)

> > In code...

> > procedure TMyDataModule.InsertFields(const Field1, Field2 :String);
> > begin
> >   with qInsert do
> >      begin
> >         ParamByName('Field1').AsString := Field1;
> >         ParamByName('Field2').AsString := Field2;
> >         ExecSQL
> >      end
> > end;

> > {Calling form}
> > procedure TForm1.Button1Click(Sender :TObject);
> > begin
> >    MyDataModule.InsertFields(Edit1.Text, Edit2.Text);
> > end;

> > Good luck,
> > krf

> > George Wynne <geo...@nssco.com> wrote in message
> news:3c6bc8e2$1_1@dnews...
> > > As I've said before, I should not have to do a StringReplace to
> substitute
> > > two single quotes for one double quote every time my users input data.

> > > Thanks,
> > > George

Re:TQery.ExecSQL Problem with : and "


Yes, I didn't think of that.  You can try setting the ParamCheck property to
False (at design time in the Object Inspector) so the BDE doesn't try to
bind any parameters.

krf

Quote
George Wynne <geo...@nssco.com> wrote in message news:3c6c3d9c_1@dnews...
> Come to think of it, Borland regards a colon in a SQL statement as the
> beginning of a variable, right?  That is certainly what is confusing their
> parser.  Still, it is strange that ther parser is confused by:

>   RunUpdateStatement('insert into temp1 (d1, d2) values (''"'', '':'')');

> but not
>   RunUpdateStatement('insert into temp1 (d1, d2) values ('':'', ''"'')');

> George

> >"George Wynne" <geo...@nssco.com> wrote in message

news:3c6c3b1e_1@dnews...

- Show quoted text -

Quote
> > I tried QuotedStr several posts ago in this thread.  It did not work.
> Look,
> > the code in my original post should work.  It's a Borland bug, unless
> > someone can write for me a RunUpdateStatement that inserts a double
quote
> > first and a colon second.  And the problem is not just the double quote;
I
> > think the colon is throwing off the parsing somewhere.

> > The insert statement that I really want to write is created dynamically,
> and
> > has 50 or so parameters, so I think I'd rather just wait to hear from
> > Borland.

> > Thanks,
> > George

> > "Kevin Frevert" <kfrev...@midwayusa.com> wrote in message
> > news:3c6bd3cf$1_2@dnews...
> > > Just curious, why not?  Simply run the QuotedStr function on whatever
> the
> > > user enters and your done.  Your not replacing two single quotes with
a
> > > double-quote, the QuotedStr simply allows parsers to correctly
interpret
> > > strings that contain a mixture of quotation marks (single and double).
> > Run
> > > MSSQL's Profiler to see what I mean (Query Analyzer does this too, you
> > just
> > > don't see it).

> > > Another option is to use parameters.

> > > In the SQL property (at design time):
> > > Insert into MyTable
> > > (Field1, Field2)
> > > Values
> > > (:Field1, :Field2)
> > > - Set the parameters according (DataType = ftString,
ParamType=ptInput)

> > > In code...

> > > procedure TMyDataModule.InsertFields(const Field1, Field2 :String);
> > > begin
> > >   with qInsert do
> > >      begin
> > >         ParamByName('Field1').AsString := Field1;
> > >         ParamByName('Field2').AsString := Field2;
> > >         ExecSQL
> > >      end
> > > end;

> > > {Calling form}
> > > procedure TForm1.Button1Click(Sender :TObject);
> > > begin
> > >    MyDataModule.InsertFields(Edit1.Text, Edit2.Text);
> > > end;

> > > Good luck,
> > > krf

> > > George Wynne <geo...@nssco.com> wrote in message
> > news:3c6bc8e2$1_1@dnews...
> > > > As I've said before, I should not have to do a StringReplace to
> > substitute
> > > > two single quotes for one double quote every time my users input
data.

> > > > Thanks,
> > > > George

Re:TQery.ExecSQL Problem with : and "


Super!  That fixed it.  That's one of those properties that I never notice.
Thanks!

George

Quote
"Kevin Frevert" <kfrev...@midwayusa.com> wrote in message

news:3c6d09d9$1_1@dnews...
Quote
> Yes, I didn't think of that.  You can try setting the ParamCheck property
to
> False (at design time in the Object Inspector) so the BDE doesn't try to
> bind any parameters.

> krf

> George Wynne <geo...@nssco.com> wrote in message news:3c6c3d9c_1@dnews...
> > Come to think of it, Borland regards a colon in a SQL statement as the
> > beginning of a variable, right?  That is certainly what is confusing
their
> > parser.  Still, it is strange that ther parser is confused by:

> >   RunUpdateStatement('insert into temp1 (d1, d2) values (''"'',
'':'')');

> > but not
> >   RunUpdateStatement('insert into temp1 (d1, d2) values ('':'',
''"'')');

> > George

> > >"George Wynne" <geo...@nssco.com> wrote in message
> news:3c6c3b1e_1@dnews...
> > > I tried QuotedStr several posts ago in this thread.  It did not work.
> > Look,
> > > the code in my original post should work.  It's a Borland bug, unless
> > > someone can write for me a RunUpdateStatement that inserts a double
> quote
> > > first and a colon second.  And the problem is not just the double
quote;
> I
> > > think the colon is throwing off the parsing somewhere.

> > > The insert statement that I really want to write is created
dynamically,
> > and
> > > has 50 or so parameters, so I think I'd rather just wait to hear from
> > > Borland.

> > > Thanks,
> > > George

> > > "Kevin Frevert" <kfrev...@midwayusa.com> wrote in message
> > > news:3c6bd3cf$1_2@dnews...
> > > > Just curious, why not?  Simply run the QuotedStr function on
whatever
> > the
> > > > user enters and your done.  Your not replacing two single quotes
with
> a
> > > > double-quote, the QuotedStr simply allows parsers to correctly
> interpret
> > > > strings that contain a mixture of quotation marks (single and
double).
> > > Run
> > > > MSSQL's Profiler to see what I mean (Query Analyzer does this too,
you
> > > just
> > > > don't see it).

> > > > Another option is to use parameters.

> > > > In the SQL property (at design time):
> > > > Insert into MyTable
> > > > (Field1, Field2)
> > > > Values
> > > > (:Field1, :Field2)
> > > > - Set the parameters according (DataType = ftString,
> ParamType=ptInput)

> > > > In code...

> > > > procedure TMyDataModule.InsertFields(const Field1, Field2 :String);
> > > > begin
> > > >   with qInsert do
> > > >      begin
> > > >         ParamByName('Field1').AsString := Field1;
> > > >         ParamByName('Field2').AsString := Field2;
> > > >         ExecSQL
> > > >      end
> > > > end;

> > > > {Calling form}
> > > > procedure TForm1.Button1Click(Sender :TObject);
> > > > begin
> > > >    MyDataModule.InsertFields(Edit1.Text, Edit2.Text);
> > > > end;

> > > > Good luck,
> > > > krf

> > > > George Wynne <geo...@nssco.com> wrote in message
> > > news:3c6bc8e2$1_1@dnews...
> > > > > As I've said before, I should not have to do a StringReplace to
> > > substitute
> > > > > two single quotes for one double quote every time my users input
> data.

> > > > > Thanks,
> > > > > George

Other Threads