[SOLVED]Open a query will change another active query of the same db-connection?

[kinlion]

I found that in one query's loop, that is: while not query1.eof do somthing..., if I open another query of the same db-connection, query1.FieldByName('xxx') will raise exception that FieldName xxx not found.

I traced into the function Dataset.FieldByName, then I found the query1.Fields has been cleared after the other query opened.

I don't know whether it's a bug or the database server's limitation.

Is there any way to fetch all dataset just after query opened?

[kinlion]

FPC 3.04 & MySqlConnection 5.6

[rvk]

I found that in one query's loop, that is: while not query1.eof do somthing..., if I open another query of the same db-connection, query1.FieldByName('xxx') will raise exception that FieldName xxx not found.

Opening another query for the db-connection doesn't do anything with the original query1.

But you need to create another TSQLQuery. You can't use query1 for that because then you close and open a new dataset and the fields in query1 change (and you can get that "FieldName xxx not found" exception).

So make sure your second query is done with a Query2 and not with the Query1.

To see exactly what's going wrong in your code you need to post some code.

[kinlion]

I used 2 queries, and each one is created just before using. That's why I feel strange.
Later I will post some code.
Anyway, thanks to rvk.

[kinlion]

I found that in one query's loop, that is: while not query1.eof do somthing..., if I open another query of the same db-connection, query1.FieldByName('xxx') will raise exception that FieldName xxx not found.

Opening another query for the db-connection doesn't do anything with the original query1.

But you need to create another TSQLQuery. You can't use query1 for that because then you close and open a new dataset and the fields in query1 change (and you can get that "FieldName xxx not found" exception).

So make sure your second query is done with a Query2 and not with the Query1.

To see exactly what's going wrong in your code you need to post some code.

BTW, query1's auto-commit is set to false. Is that the problem?

[rvk]

BTW, query1's auto-commit is set to false. Is that the problem?

No, that shouldn't be a problem. But if Query2 has autocommit true, that CAN cause a problem. Because in that case, when you close Query2, there is a transaction.commit done. And when you commit a transaction, ALL queries (datasets) connected to that connection will close.

You can set autocommit to false for query2 (if you don't change anything in the database) or set sqoKeepOpenOnCommit for Query1.

[kinlion]

Yes, I just write some simple code to test and you are right
Below is the test code.
---------------------------------
procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Options := SQLQuery1.Options - [sqoAutoCommit];
  SQLQuery2.Options := SQLQuery2.Options - [sqoAutoCommit];

  SQLQuery1.SQL.Text := 'select * from table_a';
  SQLQuery1.open;
  while not SQLQuery1.EOF do
  begin
    SQLQuery2.SQL.Text := 'select * from table_b';
    SQLQuery2.Open;

    while not SQLQuery2.EOF do SQLQuery2.Next;

    // THIS LINE MAKES SQLQuery1.Fields.Count = 0
    TSQLTransaction(SQLQuery2.Transaction).Commit;
    // if the above line removed, there will be no problem

    if SQLQuery1.Fields.Count = 0 then
      ShowMessage('Query1 fields lost!');
    SQLQuery1.Next;
  end;
  TSQLTransaction(SQLQuery1.Transaction).Commit;
end;
 

[kinlion]

BTW, query1's auto-commit is set to false. Is that the problem?

No, that shouldn't be a problem. But if Query2 has autocommit true, that CAN cause a problem. Because in that case, when you close Query2, there is a transaction.commit done. And when you commit a transaction, ALL queries (datasets) connected to that connection will close.

You can set autocommit to false for query2 (if you don't change anything in the database) or set sqoKeepOpenOnCommit for Query1.

Finally, I add sqoKeepOpenOnCommit to query1's options, that's the easiest way.
Thanks again

[rvk]

Why do you need to commit the transaction (of query2) in the Next loop of query1?

If you don't change anything it's best to set autocommit to false for query2. In that case you can just close query2 within the query1 loop without affecting query1 itself.

Your way works too but I don't see a need to commit the transaction within the loop.

[kinlion]

Why do you need to commit the transaction (of query2) in the Next loop of query1?

If you don't change anything it's best to set autocommit to false for query2. In that case you can just close query2 within the query1 loop without affecting query1 itself.

Your way works too but I don't see a need to commit the transaction within the loop.

I'm using MaraiDB. For deault transaction level of MariaDB, if you exec select without commit, you always get the same result as the first execution. So I have to commit even after each select. 

[rvk]

I'm using MaraiDB. For deault transaction level of MariaDB, if you exec select without commit, you always get the same result as the first execution. So I have to commit even after each select. 

Or use Cancel but that has the same effect as Commit (I.E. closing the connected tables).

The reason you get the same result is because you are still in the same transaction (you could change the isolation level though).

But you could do a cancel or commit after the loop of query1. After that query1 and query2 get the new results (committed by other clients).

Or is query1 that large/long that you need changes posted during the loop of query1 in query2?

[kinlion]

Or use Cancel but that has the same effect as Commit (I.E. closing the connected tables).

The reason you get the same result is because you are still in the same transaction (you could change the isolation level though).

But you could do a cancel or commit after the loop of query1. After that query1 and query2 get the new results (committed by other clients).

Or is query1 that large/long that you need changes posted during the loop of query1 in query2?

In fact, in my project, query2 is opened in a procedure which can be called by anyone, I'm not sure if  the caller will commit after calling it.
Thus I have to commit myself.
Oh, maybe I can check whethor query2 has been in a transaction. If not, it can commit safely.

[rvk]

Oh, maybe I can check whethor query2 has been in a transaction. If not, it can commit safely.

The problem with that, is that even with a SELECT, the transaction is activated and active. You can't see if the procedure changed the table.

I usually work with separate transaction if needed. And only commit them when needed. If that query2 can also change values you could pass a transaction as parameter and when it's nil, create it in the procedure and commit it at the end, otherwise leave it alone. When calling it from the loop you can pass the query1.transaction and the procedure won't commit it.

That way you have more control over your transaction but you'll need to think about the transactions and your isolation level while developing your program (which is a good thing anyway ).

[kinlion]

The problem with that, is that even with a SELECT, the transaction is activated and active. You can't see if the procedure changed the table.

I usually work with separate transaction if needed. And only commit them when needed. If that query2 can also change values you could pass a transaction as parameter and when it's nil, create it in the procedure and commit it at the end, otherwise leave it alone. When calling it from the loop you can pass the query1.transaction and the procedure won't commit it.

That way you have more control over your transaction but you'll need to think about the transactions and your isolation level while developing your program (which is a good thing anyway ).

Good idea.
Thanks