Prepare method raises an execption if SQL contains lines
Posted: 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;
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;