ODBC SQLCancel not (yet?) implemented in Free Pascal? lazarus / 16-Databases ---------------------------------------------------------------------- Helios · 2022-04-21 11:49 Some ODBC Servers are able to cancel long running queries by the call the SQLCancel ODBC API function. https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcancel- function?view=sql-server-ver15 Is there a special reason why this function is missing in the Free Pascal ODBC implementation? Kind regards Helios ---------------------------------------------------------------------- af0815 · 2022-04-21 18:42 Who should call the SQLCancel ? Make a sample to see when it is possible to call SQLCancel. If you activate a query, you are blocked. BTW: There must be a discussion few week ago about this. Wrong -> it was a disussion in the german forum https://lazarusforum.de/viewtopic. php?f=17&t=14223&p=127774&hilit=sqlcancel#p127774 so it is a crosspost ---------------------------------------------------------------------- Helios · 2022-04-21 21:23 Yes I was the one who asked a similar question in the german lazarus forum. But now I am 2 weeks ahead with my investigations and I came to the conclusion, that this is a question which may only be answered by the FPC core developer/designer who implemented the ODBC API whithin Free Pascal. The SQLCancel function exist since ODBC 1.0 (thats a long time) and until now it seems to me, no FPC/Lazarus user (like me?) needed such a function (to stop long running (ODBC) SQL Statements) in the ODBC database connectivity world. Can this be true? Is there (maybe?) no need to use SQLCancel because this ODBC API function can be achieved in an other (better) way? Here is a implementation of SQLCancel used in python with pyodbc I got to work with much help from the internet. The ODBC SQL statement is canceled by a 20 seconds watchdog. (Python is fine but pascal is my favorite programming language especialy when I want a nice GUI and a small (standalone) executable;-) import pyodbc import time import threading def query_with_time_out(conn, query, timeout):     def watchdog(cursor, time_out):         time.sleep(time_out)         # Calls ODBC SQLCancel         cursor.cancel()     cursor = conn.cursor()     t = threading.Thread(target=watchdog, args=(cursor, timeout))     t.start()     try:         result = cursor.execute(query)         cnt = 0         while True:             row = cursor.fetchone()             if row == None:                 break             print(row)             cnt += 1                     result = str(cnt) + ' rows processed.'             except pyodbc.Error:         result = 'timed out'     return result # This is a long running SQL Statement query = 'SELECT COUNT(*) FROM measurement' conn = pyodbc.connect('DSN=TestDB') print(query_with_time_out(conn, query, 20)) For the tests I used a Firebird 3 Database with ODBC 2.0.5 driver under Windows 10 64Bit. Thanks for any hint/help implementing SQLCancel in Free Pascal or any other suggestion stopping long running ODBC SQL Statements. Helios ---------------------------------------------------------------------- Zvoni · 2022-04-22 06:28 Acc. to the documentation, SQLCancel is for asynchronous Queries https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcancel- function?view=sql-server-ver15 ---------------------------------------------------------------------- Helios · 2022-04-22 07:12 Yes, but it can also used in multithreaded environments as stated in the Chapter „Canceling Functions Executing on Another Thread“. Did someone used this Feature successfully with FreePascal? Kind regards Helios ---------------------------------------------------------------------- Zvoni · 2022-04-22 10:10 Quote from: Helios on April 22, 2022, 09:12:06 amYes, but it can also used in multithreaded environments as stated in the Chapter „Canceling Functions Executing on Another Thread“. Did someone used this Feature successfully with FreePascal? Kind regards Helios Probably not, since there is no interface implemented calling that function ---------------------------------------------------------------------- Helios · 2022-04-22 19:56 Ok, then I will try to get a simple Implemetation to work. Can take a while, Hope that it will work like I expected. Thanks and kind regards Helios ---------------------------------------------------------------------- LacaK · 2022-04-26 11:11 Yes in package "odbc" unit odbcsqldyn.pas is not implemented SQLCancel API binding. May be that nobody required it. However it is no problem to add it ... ---------------------------------------------------------------------- af0815 · 2022-04-26 11:19 Quote from: LacaK on April 26, 2022, 01:11:42 pmMay be that nobody required it. For me it make a sense for me, if we can use asynchronous queries. Then it should be possible to cancel longer running queries. But actual the queries are only blocking. ---------------------------------------------------------------------- Zvoni · 2022-04-26 11:20 Quote from: LacaK on April 26, 2022, 01:11:42 pmYes in package "odbc" unit odbcsqldyn.pas is not implemented SQLCancel API binding. May be that nobody required it. However it is no problem to add it ... The Problem is not adding the binding, but where to offer it to the user. TODBCConnection IMO being the only candidate... ---------------------------------------------------------------------- Helios · 2022-04-28 10:17 @af0815 Yes, blocking is the big problem in this case and @Zvoni Yes, TODBCConnection would be one of the best candidates IMHO. TQuery would also be nice but I think this would lead to too much changes in non ODBC stuff inside TQuery. Unfortunately my knowledge ist not deep enough to do such a change on FPC and its ODBC libraries, but for sure can help to test this new feature on different ODBC platforms (Firebird, Microsoft, Impala etc.). Thanks again for your interest and support Kind regards Helios ---------------------------------------------------------------------- Zvoni · 2022-04-28 11:11 Well, you could start out as a Class-Helper. If everything works and is/gets thoroughly tested it might even get entrance into the FPC-SourceCode ---------------------------------------------------------------------- Helios · 2022-04-29 00:08 Hello Zvoni, sorry, can you explain to me, how to „start out as a Class-Helper“ in more detail? Then I will get prepared. Thanks and kind regards Helios ---------------------------------------------------------------------- Zvoni · 2022-04-29 06:17 Quote from: Helios on April 29, 2022, 02:08:41 amHello Zvoni, sorry, can you explain to me, how to „start out as a Class-Helper“ in more detail? Then I will get prepared. Thanks and kind regards Helios https://wiki.freepascal.org/Helper_types Something like Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Type   TODBCHelper = Class helper for TODBCConnection        Function CancelSQL(StatementHndl:AHandleType):SomeReturnType;   End; ---------------------------------------------------------------------- Zoran · 2022-04-29 10:54 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. ---------------------------------------------------------------------- Zvoni · 2022-04-29 11:22 Quote from: Zoran on April 29, 2022, 12:54:27 pmMaking 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 ---------------------------------------------------------------------- Zoran · 2022-04-29 11:41 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DELETE FROM mon$attachments WHERE mon$attachment_id = . Of course, 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 · 2022-04-29 14:24 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     DELETE FROM mon$attachments WHERE mon$attachment_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 · 2022-05-01 09:54 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