TMSQuery.Prepare

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

TMSQuery.Prepare

Post by jan.javurek@floresps.cz » Fri 05 Feb 2010 03:39

Hello, I have a problem with TMSQuery.Prepare in SELECT ... WHERE Col IN (SELECT ...)

Example:

Code: Select all

CREATE TABLE Test (
    ID   int NOT NULL PRIMARY KEY,
    ID2  int,
	Data image not null
)

Code: Select all

Query.SQL.Text := 'SELECT * FROM Test WHERE ID=:ID AND ID IN (SELECT ID2 FROM Test WHERE ID2=:ID)';
Query.Params.CreateParam(ftString, 'ID', ptInput).AsString := '11112121';
Query.Prepare;
// Wrong prepare sql
// Profiler log
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 sql_variant OUTPUT,@P2 sql_variant OUTPUT',N'SELECT * FROM Test WHERE ID=@P1 AND ID IN (SELECT ID2 FROM Test WHERE ID2=@P2)',1
select @p1

Code: Select all

      
Query.Execute;
//ERROR
---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EOLEDBError with message 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Parameter[2] :ID - invalid ParamType (Status = 1h).'. Process stopped. Use Step or Run to continue.
---------------------------
OK Help
---------------------------

Please how to solve the setup parameters to SQL query with nested SELECT.

Thank you for your reply.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 05 Feb 2010 08:31

To solve the problem you should use the following code:

Code: Select all

Query.SQL.Text := 'SELECT * FROM Test WHERE ID=:ID AND ID IN (SELECT ID2 FROM Test WHERE ID2=:ID2)'; 
Query.Params.CreateParam(ftString, 'ID', ptInput).AsString := '11112121'; 
Query.Params.CreateParam(ftString, 'ID2', ptInput).AsString := '11112121'; 
Query.Prepare; 
This is the specificity of working with parameters of SQL Server.

Post Reply