Prepare method raises an execption if SQL contains lines

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
mia@hcv.dk
Posts: 1
Joined: Tue 26 May 2009 21:19

Prepare method raises an execption if SQL contains lines

Post by mia@hcv.dk » Tue 13 Oct 2009 14:45

Hi.

Using SDAC 4.80.0.51 and Delphi 7.

I've used some time now trying to solve a problem where the .Prepare method of a TMSQuery raised the exception of "Implicit conversion fra data type sql_variant to varbinary(max) is not allowed. Use the CONVERT bla. bla. bla."

The problem was due to the fact that I'm acustomed to use the .SQL property and .Prepare fails if the SQL statement spans more than 1 line. See examples below.

Did I miss something in the documentation or is this a bug?

Regards

Mikkel Andersen


Examples:
--- This fails ---
msquery1.SQL.Clear;
msquery1.Params.Clear;
msquery1.Params.CreateParam(ftBlob, 'fido', ptInput);
msquery1.sql.add('insert into BlobTable');
msquery1.sql.add(format('values (%d, %d, %d, :fido)', [10, 20, 30]));
msquery1.Prepare;
msquery1.Params.ParamByName('fido').Value:='HELLO';
msquery1.Execute;

--- This works ---
msquery1.SQL.Clear;
msquery1.Params.Clear;
msquery1.Params.CreateParam(ftBlob, 'fido', ptInput);
msquery1.sql.add(format('insert into BlobTable values (%d, %d, %d, :fido)', [10, 20, 30]));
msquery1.Prepare;
msquery1.Params.ParamByName('fido').Value:='HELLO';
msquery1.Execute;

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Fri 16 Oct 2009 10:32

You should set the ParamCheck property to false if you add parameters manually. In the first case the parameters list is cleared when you add the first line.

Post Reply