TMSQuery.Prepare 3.70.0.24 (D6) versus 4.10.0.10 (D2007)

[PaulT2]

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.

[Antaeus]

Parameter types and parameter data types should be assigned implicitly or explicitly before preparation in both SDAC 3 and SDAC 4.

[PaulT2]

Can you explain that one a little more - as my previous message said, that is not my experience in SDAC 3. All that is needed in 3.70 is to assign the SQL and call prepare e.g. MSQuery1.SQL = 'update ...' ; MSQuery1.prepare; The resulting SQL profiler analysis shows that the statement is correctly prepared and parameter types are assigned automatically. In 4.10 this is not the case.

If you say there is a change in requirements and that parameter types and sizes must now be allocated in SDAC 4 before a prepare, then fair enough, but saying that about SDAC 3 goes against what I have in production.

If the answer is that you must explicitly assign types and sizes, then can you comment on what type chould be allocated for an SQL char(40) field ? The best I can get is to use string, size 40, but that results in a varchar(40) parameterization when looked at in SQL profiler. I'm not too happy to leave it at that...

If you would comment further to help me determine a way forward, I would be grateful.

[Antaeus]

For CHAR(40) you should set the Size property to 40. if you do not set Size, it will be set automatically to 8000.

[PaulT2]

As I have said in both previous posts :-

1). The functionality of the prepare statement has changed between 3.70 and 4.10.

2). Specifying a string size 40 as a manual parameter type results in a VARCHAR(40) and not a CHAR(40).

Both of these points are very simple to replicate as I have already said - a connection, a query with parameterized fields and a button which calls query.prepare.

By not specifying parameters manually, you will proove point 1, namely that it fails.

By specifying parameters manually, you will proove point 2, namely that a string size 40 translates to VARCHAR(40) and not CHAR(40).

I would be grateful if you could address these issues.

Paul.

[PaulT2]

This is still an outstanding issue for me and one that I hope someone is able to shed some light on.

Antaeus - Could give me some definative answers ?

[Antaeus]

1). The functionality of the prepare statement has changed between 3.70 and 4.10.

This change was necessary for performance optimization of prepared queries execution. This optimization eliminates additional round trips to server, and performs preparation and the first call to the prepared routine in a single batch.

2). Specifying a string size 40 as a manual parameter type results in a VARCHAR(40) and not a CHAR(40).

Both of these points are very simple to replicate as I have already said - a connection, a query with parameterized fields and a button which calls query.prepare.

By not specifying parameters manually, you will proove point 1, namely that it fails.

By specifying parameters manually, you will proove point 2, namely that a string size 40 translates to VARCHAR(40) and not CHAR(40).

Are there any known problems when parameters are described as VARCHAR instead of CHAR?

[Antaeus]

In order to revert the old behaviour with preparation and parameters, you should do the following:
1) add the OLEDBAccess unit to the uses clause of a unit in your application;
2) add the following line to the initialization section of the unit:
ParamsInfoOldBehavior := True

[PaulT2]

Thank you Antaeus, just what I needed.

Just a couple of observations from my less than knowledgable point of view....

1). I call prepare explicitly exactly when I know there will be potentially thousands of calls to the same statement. In such cases the overhead of one (or several) extra round trips to the server is of no consequence (?). These are batch programs generally operating on the same physical machine as the SQL server and 99.99% of the calls are therefore the "sp_execute" calls with only a tiny percentage being the setting up of those calls in the first place.

2). The queries are not created through the GUI, but are held as text in a unit for easier referencing and cross referencing (there are hundreds of queries). As such I would have a lot of extra lines of code to write and maintain in order to explicitly set parameter types and sizes.

...and to answer your question regarding VARCHAR(40) vs CHAR(40). No, I don't know of any issues that would cause. It was simply the first difference I noticed when I tried going down the route of populating parameter types myself through the SDAC query parameter GUI. It simply seemed a slightly backward step from what was previously a 100% working solution (at least for my situation).

Anyway, thanks again..

[Antaeus]

Thank you for your observations. We will keep them in mind for the next SDAC builds.