Recent

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

Helios

  • New Member
  • *
  • Posts: 10
ODBC SQLCancel not (yet?) implemented in Free Pascal?
« on: April 21, 2022, 01:49:05 pm »
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

  • Hero Member
  • *****
  • Posts: 1284
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #1 on: April 21, 2022, 08:42:04 pm »
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
« Last Edit: April 21, 2022, 08:45:36 pm by af0815 »
regards
Andreas

Helios

  • New Member
  • *
  • Posts: 10
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #2 on: April 21, 2022, 11:23:13 pm »
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

  • Hero Member
  • *****
  • Posts: 2300
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #3 on: April 22, 2022, 08:28:16 am »
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

Helios

  • New Member
  • *
  • Posts: 10
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #4 on: April 22, 2022, 09:12:06 am »
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

  • Hero Member
  • *****
  • Posts: 2300
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #5 on: April 22, 2022, 12:10:49 pm »
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
Probably not, since there is no interface implemented calling that function
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

Helios

  • New Member
  • *
  • Posts: 10
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #6 on: April 22, 2022, 09:56:16 pm »
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

  • Hero Member
  • *****
  • Posts: 691
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #7 on: April 26, 2022, 01:11:42 pm »
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

  • Hero Member
  • *****
  • Posts: 1284
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #8 on: April 26, 2022, 01:19:16 pm »
May 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.
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #9 on: April 26, 2022, 01:20:16 pm »
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 ...
The Problem is not adding the binding, but where to offer it to the user.
TODBCConnection IMO being the only candidate...
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

Helios

  • New Member
  • *
  • Posts: 10
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #10 on: April 28, 2022, 12:17:04 pm »
@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

  • Hero Member
  • *****
  • Posts: 2300
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #11 on: April 28, 2022, 01:11:24 pm »
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
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

Helios

  • New Member
  • *
  • Posts: 10
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #12 on: April 29, 2022, 02:08:41 am »
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

  • Hero Member
  • *****
  • Posts: 2300
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #13 on: April 29, 2022, 08:17:41 am »
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
https://wiki.freepascal.org/Helper_types
Something like
Code: Pascal  [Select][+][-]
  1. Type
  2.    TODBCHelper = Class helper for TODBCConnection
  3.         Function CancelSQL(StatementHndl:AHandleType):SomeReturnType;
  4.    End;
  5.  
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: 1828
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: ODBC SQLCancel not (yet?) implemented in Free Pascal?
« Reply #14 on: April 29, 2022, 12:54:27 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.


 

TinyPortal © 2005-2018