It is currently Sun Aug 25, 2019 12:01 am

All times are UTC + 1 hour




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

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

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

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

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

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 8
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: 13611
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: 4282
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
New User
New User

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

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

Joined: Sun Dec 16, 2018 12:54 pm
Posts: 8
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  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 1 guest


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