Comments in TQuery.SQL
Jim Elden
When I run an SQL statement that contains a comment on the first line it
gives me an error that says 'Field '' is of an unknown type. Here is an
example of an offending statement:
<SNIP>
/****** Object: View dbo.Claim_Payors_View Script Date: 3/13/1998
9:25:56 AM ******/
CREATE VIEW Claim_Payors_View
AS SELECT
C.invoice,
C.claim_payor_sequence,
C.company_code,
C.insurance_id,
P.company_name,
IP.account
FROM Claim_Payors C
LEFT JOIN Payors P
ON P.company_code = C.company_code
LEFT JOIN Insurance_Policies IP
ON IP.company_code = C.company_code
AND IP.insurance_id = C.insurance_id
GO
</SNIP>
If I delete the comment line (starts with /*) the SQL runs.
I am using D4 C/S and MSSQL 7.0, all latest patches. I am loading this
SQL statement into TQuery.SQL at runtime using the TQuery.SQL.Add and
executing it with TQuery.SQL.ExecSQL.
How can I get it to like the comments? Is this a bug?
Thanks!
Jim
Steve Koterski
wrote:
>When I run an SQL statement that contains a comment on the first line it
>gives me an error that says 'Field '' is of an unknown type. Here is an
>example of an offending statement:
*Any* comment as the first line produces this error? Or just when you have
time values (or other instances of colons)? Do you get the error with the
comment below as the initial line in the TQuery.SQL property?
/* This is a comment */
I suspect that the colons within the remark might be fooling either the
Delphi facility for recognizing and filling query parameters or your SQL
parser. What type of exception is occurring? (I suspect an EDatabaseError.)
Try suppressing the use of TQuery parameters by setting the
TQuery.ParamCheck prorety to False.
//////////////////////////////////////////////////////////////////////////
Steve Koterski "My problem lies in reconciling my gross
Technical Publications habits with my net income."
INPRISE Corporation -- Errol Flynn (1909-1959)
http://www.borland.com/delphi
Jocelyn Tremblay
for a label (GOTO Error:) was causing the error. I still haven't found a
workaround though and sure hope somebody can guide me through this. I did
try 'ParamCheck' to False but I still get the same error message, ie, 'Field
'' is of an unknown type'.
Jocelyn Tremblay
jocelyn....@multidata.ca
Steve Koterski wrote in message <36e738d9...@forums.inprise.com>...
Leo Seaman
Delphi uses the colon to designate a parameter. So every : in the
script will cause the problem. To fix the problem, set the ParamCheck
property to False, then delete all the parameter objects delphi has
created for you.
here is some example code:
procedure TForm1.Button1Click(Sender: TObject);
var
i : integer;
begin
// must set the paramcheck to false
Query1.ParamCheck := False;
with query1.SQL do
begin
clear;
add('create trigger t_asdf');
add('on dbo.CONTACTS');
add('for insert, update');
add('as');
add(' if @@RowCount = 0 return');
add(' goto error');
add('return');
add('error:');
add(' return');
end;
// must also free all parameter objects created
// by delphi
for i := Query1.ParamCount -1 downto 0 do
begin
Query1.Params.Items[i].Free;
end;
Query1.ExecSQL;
ShowMessage('groovy');
end;
Hope that helps,
Leo
Jim Elden wrote:
>
> Hello all,
>
> When I run an SQL statement that contains a comment on the first line it
> gives me an error that says 'Field '' is of an unknown type. Here is an
> example of an offending statement:
>
--
Leo Seaman USA: (800) 279-9717
Database Programmers Retreat UK: +44 (0) (1452) 814-303
email:lese...@aug.com
http://www.dp-retreat.com "Programmers Teaching Programmers"
Jim Elden
causing the problem.
My other workaround, since I was loading the SQL at runtime from a script file,
was to not load the comments into the SQL property.
Thanks!
Jim
Steve Koterski wrote:
> On Mon, 08 Mar 1999 15:10:02 -0500, Jim Elden <el...@nospam.accumedic.com>
> wrote:
>
> >When I run an SQL statement that contains a comment on the first line it
> >gives me an error that says 'Field '' is of an unknown type. Here is an
> >example of an offending statement:
>
Steve Koterski
<jocelyn....@multidata.ca> wrote:
>I have the same problem when creating triggers. I found out that the colon
>for a label (GOTO Error:) was causing the error. I still haven't found a
>workaround though and sure hope somebody can guide me through this. I did
>try 'ParamCheck' to False but I still get the same error message, ie, 'Field
>'' is of an unknown type'.
I have not heard of setting ParamCheck to False *not* causing the TQuery to
essentiall ignore the embedded colons. However, another way to do this (the
only way prior to addition of ParamCheck) is to double up the colons. For
instance the text "Error:" would become "Error::".
This is tedious to to manually and a pain to have to remember. But you can
also do this programmatically to take some of the bite out of this
necessary task.
Jocelyn Tremblay
Thanx for the tip. I ran a quick test and using a double colon 'Error::'
instead of 'Error:' seems to work just fine. I thought that creating my
trigger using 'Error::' would be rejected by MS SQL Server 7 but it worked
just fine. Seems like MS SQL Server simply rejected the second colon
(unless it's the TQuery that did something to the second colon).
I am curious. Can you explain why the two colons work ? Seems to be TQuery
specific isn't it?
Thanx again!
Jocelyn
jocelyn....@multidata.ca
Steve Koterski wrote in message <36e8600f...@forums.inprise.com>...
Steve Koterski
<jocelyn....@multidata.ca> wrote:
>Thanx for the tip. I ran a quick test and using a double colon 'Error::'
>instead of 'Error:' seems to work just fine. I thought that creating my
>trigger using 'Error::' would be rejected by MS SQL Server 7 but it worked
>just fine. Seems like MS SQL Server simply rejected the second colon
>(unless it's the TQuery that did something to the second colon).
>
>I am curious. Can you explain why the two colons work ? Seems to be TQuery
>specific isn't it?
I am not positive without delving into the VCL, but I believe that is has
to do with the parser used to populate the parameters with values. This
would put it somewhere on the VCL side, though not necessarily in the
TQuery itself. It would also mean that by the time the SQL statement get
sent to the database back-end (Microsoft SQL Server), the extraneous colon
is already gone.