Forum > Databases

ODBC SQLCancel not (yet?) implemented in Free Pascal?

(1/4) > >>

Helios:
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:
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:
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:
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:
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

Navigation

[0] Message Index

[#] Next page

Go to full version