It is currently Thu Nov 21, 2019 10:26 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 14 posts ] 
Author Message
 Post subject: Bug in ODBC Environment
PostPosted: Sun Dec 16, 2018 1:06 pm 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Mon Jan 07, 2019 6:58 pm 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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?


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Tue Jan 08, 2019 11:48 am 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
Also BLOB (binary large object) is wrong. It is also #PB_Database_Long!!


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Tue Apr 02, 2019 12:48 pm 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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!!!


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Wed Apr 24, 2019 1:49 pm 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
Help Help Help!

ODBC with DB2 returns WRONG Data!!!!

What can I do to fix this?

Please help!

urgent!


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Wed Apr 24, 2019 2:38 pm 
Offline
Administrator
Administrator

Joined: Fri May 17, 2002 4:39 pm
Posts: 13629
Location: France
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Wed Apr 24, 2019 3:20 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4425
Location: Germany
https://www.ibm.com/support/knowledgece ... 54460.html

Maybe for testing this is enough.


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Thu Apr 25, 2019 6:28 am 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Tue Apr 30, 2019 9:44 am 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Tue Apr 30, 2019 2:04 pm 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Mon Sep 09, 2019 6:11 pm 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Mon Oct 28, 2019 5:07 pm 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Mon Oct 28, 2019 6:19 pm 
Offline
Addict
Addict
User avatar

Joined: Wed Dec 23, 2009 10:14 pm
Posts: 3122
Location: Boston, MA
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...

_________________
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum


Top
 Profile  
Reply with quote  
 Post subject: Re: Bug in ODBC Environment
PostPosted: Wed Oct 30, 2019 10:13 am 
Offline
User
User

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 11
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


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 14 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye