TMSQuery.Prepare 3.70.0.24 (D6) versus 4.10.0.10 (D2007)
Posted: Wed 22 Aug 2007 11:27
Is there a known difference in usage for TMSQuery and the prepare method between these two versions ?
Moving our code from the 3.70 to 4.10 release produces an error when preparing seemingly any query with parameters. I have produced this behaviour in a simple project with just a TMSConnection, TMSQuery and a button to call MSQuery1.Prepare, having set the SQL up in the property editor. A typical SQL call under 3.70 when prepared would yield the following on SQL server profiler:-
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 char(40),@P2 int,@P3 char(40)',N'UPDATE Site Set Description=@P1 WHERE Model=@P2 AND Value=@P3',1
select @p1
Using 4.10, the following is produced:-
declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@P1 sql_variant,@P2 sql_variant,@P3 sql_variant',N'update Site set Description=@P1 where Model=@P2 and Value=@P3',1
select @p1
Note that the parameters are all now sql_variants. This triggers an EMSError - "Statement(s) could not be prepared. Implicit conversion from data type sql_variant to char is not allowed. Use CONVERT function to run this query".
If I define the parameters manually in the new (at least to me coming from 3.70) TMSQuery dialog editor (double clicking the query component on the form), then the query can be prepared - although I'm not sure how you would defnie a "char(40)" for example as a parameter with string type and 40 chars gets prepared as varchar(40) ?
Is there anything new I need to do get the parameters "setup" automatically during the "prepare", is this a bug, or do I now need to set up all parameters manually before executing a prepare. This last option would seem like a very backward step, so I hope it isn't the case.
Thanks in advance
Paul.
Moving our code from the 3.70 to 4.10 release produces an error when preparing seemingly any query with parameters. I have produced this behaviour in a simple project with just a TMSConnection, TMSQuery and a button to call MSQuery1.Prepare, having set the SQL up in the property editor. A typical SQL call under 3.70 when prepared would yield the following on SQL server profiler:-
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 char(40),@P2 int,@P3 char(40)',N'UPDATE Site Set Description=@P1 WHERE Model=@P2 AND Value=@P3',1
select @p1
Using 4.10, the following is produced:-
declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@P1 sql_variant,@P2 sql_variant,@P3 sql_variant',N'update Site set Description=@P1 where Model=@P2 and Value=@P3',1
select @p1
Note that the parameters are all now sql_variants. This triggers an EMSError - "Statement(s) could not be prepared. Implicit conversion from data type sql_variant to char is not allowed. Use CONVERT function to run this query".
If I define the parameters manually in the new (at least to me coming from 3.70) TMSQuery dialog editor (double clicking the query component on the form), then the query can be prepared - although I'm not sure how you would defnie a "char(40)" for example as a parameter with string type and 40 chars gets prepared as varchar(40) ?
Is there anything new I need to do get the parameters "setup" automatically during the "prepare", is this a bug, or do I now need to set up all parameters manually before executing a prepare. This last option would seem like a very backward step, so I hope it isn't the case.
Thanks in advance
Paul.