Recent

Author Topic: [SOLVED]Open a query will change another active query of the same db-connection?  (Read 1442 times)

kinlion

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
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?
« Last Edit: December 08, 2018, 07:52:19 am by kinlion »
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

kinlion

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #1 on: November 26, 2018, 09:44:39 am »
FPC 3.04 & MySqlConnection 5.6
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

rvk

  • Hero Member
  • *****
  • Posts: 3836
Re: Open a query will change another active query of the same db-connection?
« Reply #2 on: November 26, 2018, 09:49:30 am »
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

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #3 on: November 26, 2018, 09:57:04 am »
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. :)
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

kinlion

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #4 on: November 26, 2018, 10:15:16 am »
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?
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

rvk

  • Hero Member
  • *****
  • Posts: 3836
Re: Open a query will change another active query of the same db-connection?
« Reply #5 on: November 26, 2018, 10:25:01 am »
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

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #6 on: November 26, 2018, 10:37:06 am »
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;
 
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

kinlion

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #7 on: November 26, 2018, 10:42:08 am »
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 :D
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

rvk

  • Hero Member
  • *****
  • Posts: 3836
Re: Open a query will change another active query of the same db-connection?
« Reply #8 on: November 26, 2018, 10:50:57 am »
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

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #9 on: November 26, 2018, 12:49:34 pm »
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.  :(
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

rvk

  • Hero Member
  • *****
  • Posts: 3836
Re: Open a query will change another active query of the same db-connection?
« Reply #10 on: November 26, 2018, 12:53:52 pm »
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

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #11 on: November 26, 2018, 01:32:31 pm »
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.
« Last Edit: November 26, 2018, 01:36:10 pm by kinlion »
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

rvk

  • Hero Member
  • *****
  • Posts: 3836
Re: Open a query will change another active query of the same db-connection?
« Reply #12 on: November 26, 2018, 01:46:08 pm »
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

  • New Member
  • *
  • Posts: 45
  • I Love Lazarus
Re: Open a query will change another active query of the same db-connection?
« Reply #13 on: November 26, 2018, 02:24:33 pm »
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  :)
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32