Need to log all the SQL commands from an ODBC connection. How?

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
30,589
Hi guys,

Would like to do the items as mentioned in the topic title but haven't found anything other than the ODBC admin logger while, it works isn't the most user friendly.
Figured someone must have had to do this in the past and my google-fu is failing me.

Any help would be appreciated.
 
Hamish McPanji

Hamish McPanji

Honorary Master
Joined
Oct 29, 2009
Messages
42,084
Pho3nix said:
Hi guys,

Would like to do the items as mentioned in the topic title but haven't found anything other than the ODBC admin logger while, it works isn't the most user friendly.
Figured someone must have had to do this in the past and my google-fu is failing me.

Any help would be appreciated.
Click to expand...
What database?
 
S

SBSP

Senior Member
Joined
Sep 7, 2007
Messages
663
Assuming MSSQL ,If a SQL server allows connections from an ODBC driver does it not by default allow SQL profiler on the same port ?
I imagine you can connect directly to the SQL server using SQL profiler ? Unless the intention is to programmatically capture the queries and store it somewhere.
 
Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
30,589
SBSP said:
Assuming MSSQL ,If a SQL server allows connections from an ODBC driver does it not by default allow SQL profiler on the same port ?
I imagine you can connect directly to the SQL server using SQL profiler ? Unless the intention is to programmatically capture the queries and store it somewhere.
Click to expand...

Exactly this. Need to pseudo reverse investigate.
 
S

SBSP

Senior Member
Joined
Sep 7, 2007
Messages
663
The ODBC connector (ODBCAD32.EXE) Has a tracing tab with options log the data to file. If you enable it you could read the data from that file. Never tried it before
 
Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
30,589
You mean this?
odbc.png

It works but not the most user friendly as I'm working with a bunch of various queries. Anything else you can suggest?
 
Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
30,589
Please see below.
3 sections for a select :(
Code: 
EXCEL           257c-4460	EXIT  SQLDriverConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HDBC                0x000002A37756A5F0
		HWND                0x0000000000070816
		WCHAR *             0x00007FFF5242CF80 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FFF5242CF80 <Invalid buffer length!> [-3]
		SWORD                       -3 
		SWORD *             0x0000000000000000
		UWORD                        1 <SQL_DRIVER_COMPLETE>

		DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'testDB'. (5701) 

		DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (5703) 

EXCEL           257c-4460	ENTER SQLAllocHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x000002A37756A5F0
		SQLHANDLE *         0x000002A37594B638

EXCEL           257c-4460	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x000002A37756A5F0
		SQLHANDLE *         0x000002A37594B638 ( 0x000002A36A78AAA0)

EXCEL           257c-4460	ENTER SQLSetStmtAttr 
		SQLHSTMT            0x000002A36A78AAA0
		SQLINTEGER                   4 <SQL_ATTR_ASYNC_ENABLE>
		SQLPOINTER                 1 <SQL_ASYNC_ENABLE_ON>
		SQLINTEGER                  -5 

EXCEL           257c-4460	EXIT  SQLSetStmtAttr  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x000002A36A78AAA0
		SQLINTEGER                   4 <SQL_ATTR_ASYNC_ENABLE>
		SQLPOINTER                 1 <SQL_ASYNC_ENABLE_ON>
		SQLINTEGER                  -5 

EXCEL           257c-4460	ENTER SQLExecDirectW 
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36

EXCEL           257c-4460	EXIT  SQLExecDirectW  with return code 2 (SQL_STILL_EXECUTING)
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36

EXCEL           257c-17d4	ENTER SQLExecDirectW 
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36

EXCEL           257c-17d4	EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36
 
Last edited:
X

XennoX

Expert Member
Joined
Nov 15, 2007
Messages
2,205
Not as crazy as I thought it would be. Seems you just need to look for the WCHAR * lines. Which makes sense, as WCHAR * seems to be a pointer, and the 36 seems to be the size of the string. I cannot say for certain, as only one sample has been provided.

Any way, a simple Python 3 script like so should help give you an indication.

Code: 
import pymssql as sql

path_to_file = 'D:\\'
file_name = 'odbc_log.log'
with open(path_to_file + file_name, 'r') as log_file:
    ls = [line.rstrip() for line in log_file.readlines()]

valid = []
for line_num, value in enumerate(ls):
    if 'WCHAR' in value and len(value) > 53:
        valid.append(value)
    
server = 'localhost'
database = 'testDB'
user_id = 'log_writer'
password = 'password_for_log_writer'

with sql.connect(server=server, database=database,
                 user=user_id, password=password) as sqlConn:
    write_cursor = sqlConn.cursor()

    for query in valid:
            write_cursor.execute("INSERT INTO log_table (Executed_Query) VALUES ('%s')",
                                 query)

    sqlConn.commit()

A note, I haven't tested this out. So there may be syntax errors.
 
Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
30,589
Thanks mate. Will look into this later tonight.
Guess I'll be learning Python :)
 
You must log in or register to reply here.
Top