Recent

Author Topic: ODBC SQLCancel not (yet?) implemented in Free Pascal?  (Read 2509 times)

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #15 on: April 29, 2022, 01:22:59 pm »
Making the helper class for TODBCConnection is okay, but it gives you not much more than "syntax sugar" -- it cannot do much more than a separate function which takes TODBCConnection as a parameter.

Anyway, here is my thinking about the blocking problem, absolutely not tested. Of course, all that assuming that this SQLCancel was somehow implemented, with a helper class or not.

I believe that blocking problem can be solved by starting the query in separate thread, let's call it thread2, and from separate transaction created inside this thread. I don't know if a separate connection component is needed, I don't think so, but perhaps it is.

The main thread should take care of the time passed since thread2 starts.
When the query successfully finishes, thread2 should notify the main thread in synchronized procedure and then safely terminate.
After some time passed since thread2 was started and if no notification about successful finish came from thread2, then the main thread can call cancel from its own transaction.
Yes and No.
There is a scenario not having anything to do with any blocking and/or separate threads: Calling a stored Procedure.
IIRC, calling a stored procedure is "shoot and forget", so there wouldn't be any blocking appearing.
Acc. to the documentation, you can fire a SQLCancel (provided you have the Statement-Handle) against such a (long) running SP as long as that Procedure is SQL_STILL_EXECUTING
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zoran

  • Hero Member
  • *****
  • Posts: 1824
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #16 on: April 29, 2022, 01:41:28 pm »
Thanks, Zvoni, I don't really know, as I actually never used odbc, but I use something very similar to method I described with FBConnection. I'll say more about what I do.

First, to be complete, it should be added to my previous post that the thread2 should obtain the transaction (or connection) handle and pass it to main thread (in a synchronized procedure, of course) before it starts the query execution, so that the main thread can use it to kill the transaction (connection) if needed.

As I said, I don't know much about ODBC, but I do use the very similar method with FBConnection, where I kill the query by executing in main thread (see here)
Code: SQL  [Select][+][-]
  1. DELETE FROM mon$attachments WHERE mon$attachment_id = <connection_id>
.
Of course, <connection_id> is replaced with actual value, which had been got earlier from thread2, when the thread2 started, with 'select current_connection from rdb$database'.

Note that it kills the connection, not just transaction (ie. it needs separate connection component in the thread).
Perhaps it might be done by killing the statement, but I don't know how would I get the statement id before the statement starts (and when the statement starts, the thread is blocked).
Firebird documentation does not give the similar way to kill the transaction, as it seems you can only either kill a statement or whole connection.

This works quite well for me, and in my use case, it is quite okay to kill the connection, it's just that thread has to create and use it's own connection component (not just transaction, which is surely needed, as transaction is not thread safe), so I didn't bother to find the way to kill just the particular statement or the transaction.

Helios

  • New Member
  • *
  • Posts: 10
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #17 on: April 29, 2022, 04:24:21 pm »
Hello Zvoni and Zoran,

with Firebird DB, SQL Statement blocking is no problem (anymore). This was my solution (see german lazarus forum) SQLThread5.zip:
https://www.lazarusforum.de/viewtopic.php?p=122499#p122499
Here I only had to use one thread because of the suggested
Code: Pascal  [Select][+][-]
  1.     DELETE FROM mon$attachments WHERE mon$attachment_id = <connection_id>
Firebird (Server) SQL Cancel capabilities.
The problem with the ODBC Blocking thread is, I get not rid of it. Not even when I use 2 threads as Zoran already suggested.
After some SQL cancelings the application freezes and or the whole OS (Windows 10) freezes too.
See here: https://www.lazarusforum.de/viewtopic.php?p=124523#p124523 BlockingThread4.zip
That is the reason why I investigated further and trapped into the ODBC SQLCancel and the
implementation with python/pyodbc. This is the functionality I need with Free Pascal for my project.
Thanks and regards
Helios

Helios

  • New Member
  • *
  • Posts: 10
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #18 on: May 01, 2022, 11:54:30 am »
A simple project was missing until now. Here is what I got so far with much help from other free pascal users (thanks to all).
The SQL statement must be changed in the *.sql file in the working directory. Up to 4 (return)values (Strings) will be displayed
if the selected ODBC DSN is correct (user/pwd must be given via DSN settings if applicable).
The SQLCancel does a good job. Until now it seems to work very stable (using Firebird and Impala ODBC Driver).
What is still missing is the integration of SQLCancel into ODBCConnection. For me it is not clear where I can get the (ODBC) SQL-Statement Handle.
Executing the SQL statement is normaly done by a TQuery.Open and TQuery is related to TODBCConnection by something like
"TQuery.DataBase := TODBCConnection". Here is my lack of knowledge. Can somebody help pointing me to the right direction?
Thanks
Helios

 

TinyPortal © 2005-2018