PureBasic Forum
http://forums.purebasic.com/english/

Bug in ODBC Environment
http://forums.purebasic.com/english/viewtopic.php?f=4&t=71932
Page 1 of 1

Author:  Jörg Burdorf [ Sun Dec 16, 2018 1:06 pm ]
Post subject:  Bug in ODBC Environment

Hi Board!

I use Purebasic with ODBC to access a IBM Db2 Database.

I'm missing an error in a deadlock/timeout situation.

Here the description of the problem:

Steps: Problems using the ODBCdatabase with DB2 V10.5 Express C on Windows or Linux. Problem: Creating a table in DB2 will lock the System Catalog table SYSIBM.SYSTABLES. When selecting with SQL against this Catalog Table with an other program, normaly you will get a SQLCODE -911 Deadlock or Timeout.

The Purebasic ODBC returns Error 0 and the Recordset has the Entries from the Catalog Table until the Locked Creator.

Thats fatal, it seems you get all the data, but you only get a subset of data. Maybe this will happen in outher circumstances. I have tested it in the following way:

Start the first program with the SQL:

create table joerg.test_lock (f1 char(20), f2 bigint, f3 timestamp)

then the program waits to user respond commit or rollback, that means, the System Catalog Table SYSIBM.SYSTABLES is locked exclusive by this program.

Start the Purebasic progamm with the SQL

select distinct creator from sysibm.systables

Then after 30 Seconds (the LOCKTIMEOUT Parameter of the DB2 DB is set to 30) the program returns all creators including joerg, wich is the
creator where the table will be created.

Other (VB dot Net) programs return the -911 deadlock error.

Expected Result:
DB2 Error -911

Actual Result:
Return Code 0 and a subset of the data

Workarounds:
no

With this behavior a programm using the Purebasic ODBC is not realy working in a multi user database
environment.

Author:  Jörg Burdorf [ Mon Jan 07, 2019 6:58 pm ]
Post subject:  Re: Bug in ODBC Environment

Hi Folks!

Next Problem in ODBC:

In DB2 Database there exists a datatype called CLOB (character large object). In PureBasic there are
constanst like #PB_Database_String and #PB_Database_Long. These can be checked against the
DatabaseColumntype function.

On Windows OS the CLOB is #PB_Database_String (can work with)
On Linux (Ubuntu) the CLOB is #PB_Database_Long (this is a bug!!)

So I must Map all Datatypes to String, because I'm not shure that all datatypes checks work.

Any comments?

Author:  Jörg Burdorf [ Tue Jan 08, 2019 11:48 am ]
Post subject:  Re: Bug in ODBC Environment

Also BLOB (binary large object) is wrong. It is also #PB_Database_Long!!

Author:  Jörg Burdorf [ Tue Apr 02, 2019 12:48 pm ]
Post subject:  Re: Bug in ODBC Environment

Hi!

Just another problem in the ODBC driver:

Whenn I select data from a table, maybe

select firstname,lastname from bank.adress where firstname = 'JOE'

and I have no right to see the data, normaly a SQLCODE -551 and Error SQL0551N
was returned from the database. With Purebasic ODBC usage, there is no error returned
and it seems that the sql has no data found.

Please fix this bugs, because they all show wrong results!!!

Author:  Jörg Burdorf [ Wed Apr 24, 2019 1:49 pm ]
Post subject:  Re: Bug in ODBC Environment

Help Help Help!

ODBC with DB2 returns WRONG Data!!!!

What can I do to fix this?

Please help!

urgent!

Author:  Fred [ Wed Apr 24, 2019 2:38 pm ]
Post subject:  Re: Bug in ODBC Environment

Unfortunately, i don't have any DB2 instance to test with, so I don't know how to fix this issue. OBDC in PB works since years for all other DB engine, so it won't be easy to see what's wrong.

Author:  infratec [ Wed Apr 24, 2019 3:20 pm ]
Post subject:  Re: Bug in ODBC Environment

https://www.ibm.com/support/knowledgece ... 54460.html

Maybe for testing this is enough.

Author:  Jörg Burdorf [ Thu Apr 25, 2019 6:28 am ]
Post subject:  Re: Bug in ODBC Environment

Yes, the DB2 Express C Edition is the right for testing. If you can install DB2 and are willing to test the
behavior of timeout/deadlock errors, I will guide you to the test.

regards

Jörg

Author:  Jörg Burdorf [ Tue Apr 30, 2019 9:44 am ]
Post subject:  Re: Bug in ODBC Environment

It seems that all DB2 Errors that based on a timeout (default for Query is 30 seconds) are not captured by the odbc implementation of Purebasic

regards Joerg

Author:  Jörg Burdorf [ Tue Apr 30, 2019 2:04 pm ]
Post subject:  Re: Bug in ODBC Environment

Next thougt :

DB2 uses row level locking. So if a table has 10 rows and then 7th is locked, the first 6 rows where fetched and a lock wait happens on the 7th row. After 30 seconds the timeout returns a sqlcode.

It seems that Purebasic thinks that when fetched a row the query runs with success.

In some cases DB2 locks the whole table, so no rows where fetched and then after 30 seconds
Purebasic returns the right error message.

For example you create a table an set autocommit of, the table is created and the database catalog is
updated with the new metadata, but no commit is run. So if you try select * from this table, you get the timeout / daedlock message.

Author:  Jörg Burdorf [ Mon Sep 09, 2019 6:11 pm ]
Post subject:  Re: Bug in ODBC Environment

Hi.

I feel blue about no solution with my deadlock/timeout error that was not captured by purebasic. So I try to
make a Windows ODBC Trace. In this trace the error was recorded but not returned to purebasic. Purebasic returns no error.

Please help!

Here is the trace:


SPOC 1a74-23f8 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x05622280
SQLHANDLE * 0x021F5260

SPOC 1a74-23f8 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x05622280
SQLHANDLE * 0x021F5260 ( 0x05625FA0)

SPOC 1a74-23f8 ENTER SQLExecDirectW
HSTMT 0x05622D40
WCHAR * 0x02388F88 [ 55] "select distinct tabschema from syscat.tables order by 1"
SDWORD 55

SPOC 1a74-23f8 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0x05622D40
WCHAR * 0x02388F88 [ 55] "select distinct tabschema from syscat.tables order by 1"
SDWORD 55

SPOC 1a74-23f8 ENTER SQLNumResultCols
HSTMT 0x05622D40
SWORD * 0x0019FEB8

SPOC 1a74-23f8 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)
HSTMT 0x05622D40
SWORD * 0x0019FEB8 (1)

SPOC 1a74-23f8 ENTER SQLDescribeColW
HSTMT 0x05622D40
UWORD 1
WCHAR * 0x0019FDA4
SWORD 128
SWORD * 0x00000000
SWORD * 0x0019FEB0
SQLULEN * 0x0019FEA8
SWORD * 0x0019FEA4
SWORD * 0x0019FEAC

SPOC 1a74-23f8 EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS)
HSTMT 0x05622D40
UWORD 1
WCHAR * 0x0019FDA4 [ -3] "TABSCHEMA\ 0"
SWORD 128
SWORD * 0x00000000
SWORD * 0x0019FEB0 (12)
SQLULEN * 0x0019FEA8 (128)
SWORD * 0x0019FEA4 (0)
SWORD * 0x0019FEAC (0)

SPOC 1a74-23f8 ENTER SQLDescribeColW
HSTMT 0x05622D40
UWORD 1
WCHAR * 0x02380668
SWORD 4096
SWORD * 0x0019FE9C
SWORD * 0x0019FEA0
SQLULEN * 0x0019FE98
SWORD * 0x0019FEA4
SWORD * 0x0019FEB8

SPOC 1a74-23f8 EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS)
HSTMT 0x05622D40
UWORD 1
WCHAR * 0x02380668 [ 9] "TABSCHEMA"
SWORD 4096
SWORD * 0x0019FE9C (9)
SWORD * 0x0019FEA0 (12)
SQLULEN * 0x0019FE98 (128)
SWORD * 0x0019FEA4 (0)
SWORD * 0x0019FEB8 (0)

SPOC 1a74-23f8 ENTER SQLDescribeColW
HSTMT 0x05622D40
UWORD 1
WCHAR * 0x02380668
SWORD 4096
SWORD * 0x0019FE98
SWORD * 0x0019FE9C
SQLULEN * 0x0019FE94
SWORD * 0x0019FEA0
SWORD * 0x0019FEB4

SPOC 1a74-23f8 EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS)
HSTMT 0x05622D40
UWORD 1
WCHAR * 0x02380668 [ 9] "TABSCHEMA"
SWORD 4096
SWORD * 0x0019FE98 (9)
SWORD * 0x0019FE9C (12)
SQLULEN * 0x0019FE94 (128)
SWORD * 0x0019FEA0 (0)
SWORD * 0x0019FEB4 (0)

SPOC 1a74-23f8 ENTER SQLFetch
HSTMT 0x05622D40

SPOC 1a74-23f8 EXIT SQLFetch with return code -1 (SQL_ERROR)
HSTMT 0x05622D40

DIAG [40001] [IBM][CLI Driver][DB2/NT64] SQL0911N Die aktuelle Transaktion wurde rückgängig gemacht. Ursache: Deadlock oder Zeitüberschreitung. Ursachencode: "68". SQLSTATE=40001
(-911)

SPOC 1a74-23f8 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x05622D40

SPOC 1a74-23f8 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x05622D40

Author:  Jörg Burdorf [ Mon Oct 28, 2019 5:07 pm ]
Post subject:  Re: Bug in ODBC Environment

Hi folks!

Again, I have a problem with the error handling in the ODBC implementation of Purebasic.

In the above trace you can see that the SQL runs ok:

SPOC 1a74-23f8 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0x05622D40
WCHAR * 0x02388F88 [ 55] "select distinct tabschema from syscat.tables order by 1"
SDWORD 55

but when fetching the rows an error occures:

SPOC 1a74-23f8 ENTER SQLFetch
HSTMT 0x05622D40

SPOC 1a74-23f8 EXIT SQLFetch with return code -1 (SQL_ERROR)
HSTMT 0x05622D40

DIAG [40001] [IBM][CLI Driver][DB2/NT64] SQL0911N Die aktuelle Transaktion wurde rückgängig gemacht. Ursache: Deadlock oder Zeitüberschreitung. Ursachencode: "68". SQLSTATE=40001
(-911)

Purebasic not handles this Error. It returns the rows that where fetched till the deadlock occures. No error returns.

Please, fix that bug!

Jörg

Author:  skywalk [ Mon Oct 28, 2019 6:19 pm ]
Post subject:  Re: Bug in ODBC Environment

Jörg - While waiting for an ODBC solution, you may want to consider creating a DB2 wrapper to accomplish your queries. I must use ImportC to enable special SQLite functions:
Code:
  ImportC ""    ; UseSQLiteDatabase() must be called prior
    sqlite3_open(filename.s, *hDB)    ;UTF-8
    sqlite3_close(hDB.i)
    sqlite3_errcode(hDB.i)
    sqlite3_libversion()
    sqlite3_threadsafe()
    sqlite3_last_insert_rowid(hDB.i)
    ;- Extensions
    sqlite3_enable_load_extension(hDB.i, onoff.i)  ; Enable all DB connections.
    sqlite3_db_config(hDB.i, op1.i, op2.i, op3.i)  ; Enable single DB connection.
    sqlite3_load_extension(hDB.i, op1.i, op2.i, op3.i)
...and so on...

Author:  Jörg Burdorf [ Wed Oct 30, 2019 10:13 am ]
Post subject:  Re: Bug in ODBC Environment

Thak you for your support! I don't now if it is working for DB2.

The ODBC usage in my tool also works on Linux and Mac OS, so i prefere the fix of the bug in the Purebasic ODBC Implementation.

regards
Joerg

Page 1 of 1 All times are UTC + 1 hour
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/