TAdoQuery.ParseSql do not work in xe4

Question

I have a project in Delphi 7 and I use TAdoQuery.ParseSql(); to load parameters. Now I compile it in XE4 and type of parameters is sometimes wrong. It's really ftInteger but created as ftSmallint. What can I do to solve this problem? My DB is SQL Server 2008 R2.

Table definition:

CREATE TABLE [dbo].[tblTest]( 
   [sysId] [int] IDENTITY(1,1) NOT NULL,  
   [Code] [nvarchar](50) NOT NULL, 
   [Name] [nvarchar](500) NOT NULL, 
CONSTRAINT [PK_tblTest] 
PRIMARY KEY CLUSTERED ( [sysId] ASC )
   WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
          ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) 
   ON [PRIMARY] )
ON [PRIMARY]

Sample data:

INSERT INTO tblTest ( Code, Name ) VALUES ( 'a1', 'name1' )

Delphi code:

ADOQuery.SQL.Text := 'SELECT * FROM tblTest WHERE sysId = :sysId';
AdoQuery.Parameters.ParseSQL(ADOQuery.SQL.Text,True);
AdoQuery.Parameters.ParamByName('sysId').value := -1;
AdoQuery.open;

ConnectionString (database: MyDb)

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial  Catalog=MyDb;Data Source=.

as a solution i use this delphi code

AdoQuery.Parameters.ParseSQL(ADOQuery.SQL.Text,false);

And now can any body Explain this? is there any side effect?

Answer 1

Firstly, before I get into any explanation about ParseSQL, check your connection string. There are two spaces between Initial and Catalog. When I used that connection string I got an error opening the query that tblTest does not exist. Removing the extra space resolved that problem.

When I saw this question I had no idea what ParseSQL does. I've been doing Delphi / SQL Server / ADO development for years and never used it. I thought this would be an opportunity to learn something new.

Please note that all the tests I ran gave identical results in Delphi 7 and Delphi XE5. I'd still be curious to see some code that works in Delphi 7 but doesn't work in Delphi XE5.

When you execute AdoQuery.SQL.Text:='...', if the connection is set and open, your application will create the parameters, and query the database server to determine their types. This code:

AdoQuery := tAdoQuery . Create(nil);
AdoQuery . Connection := AdoConnection;
ADOQuery.SQL.Text := 'SELECT * FROM tblTest WHERE sysId = :sysId';

aDataType := AdoQuery.Parameters.ParamByName ( 'sysId' ) . DataType;
Msg ( 'Parameter type = ' + DataTypeToString ( aDataType ) );

will result in:

Parameter type = ftInteger

Note, DataTypeToString is just a routine I wrote to convert tDataType to string.

function DataTypeToString ( const nDataType : tDataType ) : string;
begin
  Result := GetEnumName ( TypeInfo(tDataType),
                          integer (nDataType) );
end;

Then execute this code.

AdoQuery.Parameters.ParamByName ( 'sysId' ) . Value := -1;
AdoQuery . Open;

If you run SQL Profiler you'll see what query is sent to the SQL Server:

exec sp_executesql N'SELECT * FROM tblTest WHERE sysId = @P1
',N'@P1 int',-1

Note that @P1 is declared as int.

Note: If you have two spaces in the connection string, the parameter type will be shown as ftUnknown, and @P1 will be type smallint. The remainder of this answer will assume you have the correct connection string with one space.

What does calling ParseSQL with DoCreate=true do?

AdoQuery.Parameters.ParseSQL(ADOQuery.SQL.Text,True);

The first thing it does is clear the parameter list. In this case, that means the correctly typed parameter we already had is destroyed. ParseSQL then parses the SQL and finds the parameter. It creates a new tParameter object with name sysID and adds it to the list. The new parameter has type ftUnknown.

Why would anyone want to do this? If the query connection was NOT set, or was not OPEN, this could be very useful. The parameter list would be built for you, after which you could explicitly set their datatypes.

What if instead you were to execute ParseSQL with DoCreate=false:

AdoQuery.Parameters.ParseSQL(ADOQuery.SQL.Text,false);

The answer is: nothing. ParseSQL will parse the SQL, find the parameters, and it will return a string like this:

SELECT * FROM tblTest WHERE sysId = ?

replacing the parameter with a question mark. It will make no changes the to the parameter list. Since your code does nothing with the returned string, the net result is that this call to ParseSQL makes no changes.