TAdoQuery.ParseSql在xe4中不起作用 [英] TAdoQuery.ParseSql do not work in xe4
问题描述
我在Delphi 7中有一个项目，我使用 TAdoQuery.ParseSql（）; 加载参数。现在，我在XE4中进行编译，参数类型有时是错误的。它实际上是 ftInteger ，但创建为 ftSmallint 。我该怎么做才能解决这个问题？我的数据库是SQL Server 2008 R2。
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.
表定义：
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]
样本数据：
INSERT INTO tblTest ( Code, Name ) VALUES ( 'a1', 'name1' )
Delphi代码：
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 （数据库： MyDb ）
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDb;Data Source=.
作为解决方案，我使用此delphi代码
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?
推荐答案
首先，在我对ParseSQL进行任何解释之前，请检查您的连接字符串。初始和目录之间有两个空格。当我使用该连接字符串时，打开打开tblTest不存在的查询时出错。删除多余的空间可以解决该问题。
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.
当我看到这个问题时，我不知道ParseSQL是做什么的。我从事Delphi / SQL Server / ADO开发已经有多年了，但从未使用过。我认为这将是一个学习新知识的机会。
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.
请注意，我运行的所有测试在Delphi 7和Delphi XE5中给出的结果相同。我仍然很好奇看到某些代码可以在Delphi 7中运行，但不能在Delphi XE5中运行。
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.
当您执行 AdoQuery时。 SQL.Text：='...'，如果已设置并打开连接，则您的应用程序将创建参数，并查询数据库服务器以确定其类型。这段代码：
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 ) );
将导致：
Parameter type = ftInteger
注意，DataTypeToString只是我编写的例程
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;
然后执行此代码。
AdoQuery.Parameters.ParamByName ( 'sysId' ) . Value := -1;
AdoQuery . Open;
如果运行SQL事件探查器，您将看到向SQL Server发送的查询：
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
请注意，@ P1被声明为 int 。
Note that @P1 is declared as int.
注意：如果连接字符串中有两个空格，则参数类型将显示为 ftUnknown ，@ P1将输入 smallint 。该答案的其余部分将假定您具有一个空格的正确连接字符串。
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.
调用 ParseSQL 与 DoCreate = true 在一起吗？
AdoQuery.Parameters.ParseSQL(ADOQuery.SQL.Text,True);
它要做的第一件事就是清除参数列表。在这种情况下，这意味着我们已经拥有的正确键入的参数将被销毁。 ParseSQL 然后解析SQL并找到参数。它创建一个名为 sysID 的新 tParameter 对象，并将其添加到列表中。新参数的类型为 ftUnknown 。
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.
如果改为使用 DoCreate = false ParseSQL ，该怎么办？ $ c>：
What if instead you were to execute ParseSQL with DoCreate=false:
AdoQuery.Parameters.ParseSQL(ADOQuery.SQL.Text,false);
答案是：什么都没有。 ParseSQL将解析SQL，找到参数，并返回如下字符串：
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 = ?
用问号替换参数。不会对参数列表进行任何更改。由于您的代码对返回的字符串不执行任何操作，因此最终结果是对ParseSQL的此调用未进行任何更改。
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.
这篇关于TAdoQuery.ParseSql在xe4中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
