TADOQuery SQL.add() submitting/preparing the sql

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 fu开发者_如何学Crther 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.

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.