TADOQuery SQL.add() submitting/preparing the sql

Question

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.

I wonder who downvoted you; therefore +1, as I think the question is a very valid one. — Jeroen Wiert Pluimers, Aug 03 '11 at 18:22
@Jeroen - I'm guessing but, I think the reason for the downvote is that this question is, this way or that way, *misleading*. In no condition whatsoever, calling `.SQL.Add` can run a query. It inserts the text in .SQL, closes the dataset in the change notifier, updates CommandText from the .SQL in the same, initializes parameters while doing it, clears the update flag on FieldDefs, etc, etc.. If anything, the procedure closes, clears things. There is some other unknown in this, dunno, a mis-handled event etc.. — Sertac Akyuz, Aug 03 '11 at 20:32
@Sertac - well, but why downvote ? IMO it's the perfectly formatted and valid question. If ther's a wrong way how OP is going to do something let's direct him to the right one. — , Aug 03 '11 at 20:58
@daemon - I was trying to tell that the question is *wrongly asked* by presuming '.sql.add' is running a query. That was my guess of course.. OTOH, I absolutely, definitely agree that a downvote without a comment is no help at all, and certainly will not direct the OP in any way, right or wrong. — Sertac Akyuz, Aug 03 '11 at 21:04
@Jeedee - Can't you get to anywhere by putting a breakpoint and examining the call stack, on `Recordset.Open(..` in `TCustomADODataSet.OpenCursor` in 'ADODB.pas', it should be hit twice. — Sertac Akyuz, Aug 03 '11 at 21:09
@Sertac - I agree with you: perhaps my question was poorly phrased. I appreciate the fact that sql.add doesn't actually run the query. I think that what it's doing is preparing the statement, and it's definitely doing something on the server. As I said in my original question, I can review the logs on the IBM i and if I put a breakpoint after the .sql.add but before the .active := true, a request passes through an exit point (i.e. the query is somehow submitted to the server - perhaps to prepare it?). Setting active to true runs the query, going through a different exit point. — Jeedee, Aug 04 '11 at 07:35
@Sertac (continued from above) - If I could attach a screenshot, I could show you the logs and perhaps explain better what's going on. True, the query isn't run twice, but the actual query statement is registered as passing through two different exit points (i.e. once on sql.add, and the other on active := true). I think it might be preparing the statement, rather than running it, on sql.add, and this is what's causing the problem. — Jeedee, Aug 04 '11 at 07:37
Can you post here what SQL activity you see on the iSeries? I think it will be a meta-data request, which - in some situations - can make sense. Of course it will fail when the user credentials cannot login for one reason or the other (CPF22E3 for disabled user profile, CPF22E4 for invalid password, etc). — Jeroen Wiert Pluimers, Aug 04 '11 at 11:59
@Jeroen - I can only provide the information from the exit point programs that we have processing traffic on the exit points, which is basically what I mentioned in my original question (the same query coming in via the "prepare and describe" and "open and describe" functions of the database server exit point. After much investigation though, it seems that there isn't anything that I can do to prevent this, but the issue of the profile getting disabled is down to the connection string being defined in the adoquery rather than an adoconnection, as in the answer I accepted. Thanks though. — Jeedee, Aug 04 '11 at 14:33
@Jeedee: This is a mystery for me. The .SQL property is a TWideStrings, one line of it is not guaranteed/meant to be a complete query. Even the example in the documentation calls .SQL.Add twice to complete the query. It shouldn't prepare/submit/run anything. Anyway, I'm glad that you've resolved your problem, and I'll keep in mind that using the connection string on the dataset might be problematic.. — Sertac Akyuz, Aug 05 '11 at 00:40
@Sertac - It's a mystery to me as well, and may be a specific issue on the IBM i. I couldn't work out what was going on until I put a breakpoint on the code in the above example. I stopped the code after I'd called sql.add but before active := true was called, and sure enough, there was an entry in the exit point logs on the IBM i, where the ADOQuery seemed to be preparing the statement. Running active := true then ran the query with another entry in the logs. I'd like to know why it does that, as I can't find it documented anywhere! — Jeedee, Aug 05 '11 at 12:31

score 2 · Accepted Answer · answered Aug 03 '11 at 23:23

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.

You're correct. After I'd submitted my question (and before you'd responded), I went back and re-checked the code and noticed this myself. The code, for some reason, doesn't use an ADOConnection to establish the connection. I didn't have time to check this out last night, but I'll look into it today. — Jeedee, Aug 04 '11 at 07:39
Thanks for the prod in the right direction. I should have noticed this myself, but didn't for some reason! The connection string was specified on the ADOQuery, instead of on a separate ADOConnection. I now handle the connection before I submit the query, and there is no issue with the profile being disabled. — Jeedee, Aug 04 '11 at 14:34

TADOQuery SQL.add() submitting/preparing the sql

1 Answers1