search

zllsdn / luadbi

Automatically exported from code.google.com/p/luadbi
MIT License
0 stars 0 forks source link

DB2 driver: cannot re-execute statement #31

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago 
Whilst testing my changes to the LUA object registration I decided to test with 
DB2 (as I have a soft spot for DB2 as I used to use it a lot).  I noticed the 
following test code does not work with the DB2 driver, although it works fine 
with the SQLite3 driver:

local DBI = require 'DBI'

local dbh = assert(DBI.Connect('DB2', 'test'))

dbh:autocommit(false)
DBI.Do(dbh, 'DROP TABLE test')
DBI.Do(dbh, 'CREATE TABLE test (id integer not null primary key, value 
varchar(255) not null)')
DBI.Do(dbh, "INSERT INTO test values (1, 'test1')")
DBI.Do(dbh, "INSERT INTO test values (3, 'test3')")
DBI.Do(dbh, "COMMIT")

local sth, err = assert(dbh:prepare('SELECT value FROM test WHERE id = ?'))
if sth == nil then
    print('Error preparing statement: ', err)
    os.exit()
end
local success, err = sth:execute(3)
if not success then
    print('Error executing statement: ', err)
    os.exit()
end
row = sth:fetch()
if row ~= nil then
    print('3 found: ', row[1])
end
success, err = sth:execute(2)
if not success then
    print('Error executing statement: ', err)
    os.exit()
end
row = sth:fetch()
if row ~= nil then
    print('2 found: ', row[1])
end
success, err = sth:execute(1)
if not success then
    print('Error executing statement: ', err)
    os.exit()
end
row = sth:fetch()
if row ~= nil then
    print('1 found: ', row[1])
end
sth:close()
dbh:close()
print('Done')

The second and subsequent sth:execute calls fail due to an invalid cursor state 
as the cursor is still open.  I also noticed that the code is quite inefficient 
because it allocates memory for the resultset after every execute rather than 
when the statement is prepared.

The attached patch keeps track of whether the cursor is still open and closes 
it when the statement is re-executed, if required.  It also moves the code to 
initialise the resultset into the statement_create function to avoid repeatedly
allocating the memory every time the statement is executed.

Any comments welcome.

Original issue reported on code.google.com by lbbrow...@gmail.com on 14 Jun 2015 at 5:26

Attachments:

GoogleCodeExporter commented 8 years ago 
I've spent some more time working on this and in the process discovered another 
bug where numeric data types cannot be accessed properly.  Here is a new patch 
series that supersedes the previous patch.

Original comment by lbbrow...@gmail.com on 23 Jul 2015 at 9:07

Attachments: