TADOQuery SQL.add() 提交/准备 sql
概述：
我编写了一个应用程序，允许用户定义查询、将其提交到服务器并查看结果。该软件可以运行在DB2或MySQL上。
问题：
我们在 DB2 版本中遇到了问题，用户尝试运行查询，但发现查询失败，因为他们的用户配置文件已被禁用。为了在 DB2（在 IBM i 上）上运行查询，连接字符串中提供了用户的配置文件名和密码。服务器上的安全性可以指定用户的配置文件在两次或三次错误登录后被禁用。
问题：
我调试了该应用程序，发现问题在于提交了两次查询。如果用户的密码错误，那么当然，这会产生禁用其个人资料的连锁反应。
经过进一步检查，当我检查服务器上的日志（同时逐行调试）时，我发现当您调用 TADOQuery.sql.add() 时，查询会提交到服务器，并且当 TADOQuery 的active 属性设置为 true （这是我期望将查询提交到服务器的点）。这是我用来运行查询的代码示例：
adoqry.active := false;
adoqry.sql.clear;
adoqry.sql.add('SELECT * FROM SOMEDB.SOMETABLE');
adoqry.active := true;
因此我的问题非常简单：
1. 为什么 TADOQuery.sql.add() 方法提交查询（它应该只是将 sql 添加到 TADOQuery 的 sql 属性）？
2. 我可以做什么来防止这种情况发生？即有什么办法可以防止当我调用add()方法时提交sql？
对于那些想要有关日志的额外信息的人，IBM i 上的出口点日志显示，当我在上面的示例中调用 adoqry.sql.add 时，查询是通过“数据库服务器-SQL 请求”出口运行的通过“准备和描述”功能进行点应用。当我在上面的示例中调用 adoqry.active := true 时，相同的查询会通过相同的退出点应用程序，但通过“Open/Describe”函数。
如果您不熟悉 IBM i，请不要担心 - 我只是将该信息作为我已跟踪两次提交的查询的证据。真正的问题在于 TADOQuery 的 sql.add() 处理。
Overview:
I have written an application that allows a user to define a query, submit it to a server and view the results. The software can run on DB2 or MySQL.
Problem:
We've had issues in the DB2 version where a user has tried to run a query, and found that it has failed because their user profile has been disabled. In order to run a query on DB2 (on an IBM i), the user's profile name and password are provided in the connection string. Security on the server can specify that a user's profile is disabled after two or three incorrect logins.
Question:
I've debugged the application and found that the problem is down to the query being submitted twice. If the user's password is wrong, then of course, this is having the knock-on effect of disabling their profile.
On further inspection, when I've inspected the logs on the server (while debugging line by line), I've found that the query is submitted to the server when you call TADOQuery.sql.add(), and again when the TADOQuery's active propery is set to true (which is the point at which I would expect the query to be submitted to the server). Here's an example of the code that I'm using to run the query:
adoqry.active := false;
adoqry.sql.clear;
adoqry.sql.add('SELECT * FROM SOMEDB.SOMETABLE');
adoqry.active := true;
My question is therefore quite simple:
1. Why does the TADOQuery.sql.add() method submit the query (when it should just be adding the sql to the TADOQuery's sql property)?
2. What can I do to prevent this? i.e. is there any way to prevent the sql being submitted when I call the add() method?
For those of you that would like extra information about the logs, the exit point logs on the IBM i show that when I call adoqry.sql.add in the above example, the query is run through the "Database Server-SQL Requests" exit point application, via function "Prepare and Describe". When I call adoqry.active := true in the above example, the same query goes through the same exit point application, but via the "Open/Describe" function.
If you're not familiar with the IBM i, don't worry about it - I'm just including that information as proof that I have traced the query being submitted twice. The real issue is with the TADOQuery's sql.add() processing.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据您对问题的描述，我假设您指定了 ADOQuery 的 ConnectionString。这样做将数据库登录与查询的运行结合起来。您发现，当用户的凭据无效时，这会产生不良副作用。
使用 ADOConnection 将数据库登录与查询分开。指定 ADOConnection 的 ConnectionString 并将 ADOConnection 分配给 ADOQuery.Connection 属性。通过这种方式，您可以控制数据库登录并可以捕获使用错误凭据的登录。此外，ADOConnection.Open 方法允许您指定用户名和密码，因此您不必将它们放入 ConnectionString 中。
虽然这不能回答您的具体问题，但此方法将通过将登录与查询的运行分开来帮助您解决用户配置文件被禁用的问题。
From your description of your problem, I assume you specify the ConnectionString of the ADOQuery. Doing this combines the database login with the running of the query. You have found that this has undesirable side effects when the user's credentials are invalid.
Separate the database login from the query by using an ADOConnection. Specify the ConnectionString of the ADOConnection and assign the ADOConnection to the ADOQuery.Connection property. This way, you control the database login and can catch logins with bad credentials. Additionally the ADOConnection.Open method allows you to specify the username and password so you do not have to put them in the ConnectionString.
While this does not answer you specific questions, this approach will help you solve the problem of the user's profile being disabled by separating the login from the running of the query.