Menu
▾
▴
[Ruby-DBI-devel-CVS] CVS: src/lib/dbd_db2 DB2.rb,1.6,1.6.4.1
|
From: Elias K. <el...@us...> - 2002-12-28 14:36:36
|
Update of /cvsroot/ruby-dbi/src/lib/dbd_db2
In directory sc8-pr-cvs1:/tmp/cvs-serv27888/lib/dbd_db2
Modified Files:
Tag: DB2BLOBS
DB2.rb
Log Message:
experimental support for BLOBs via SQLBindParameter
Index: DB2.rb
===================================================================
RCS file: /cvsroot/ruby-dbi/src/lib/dbd_db2/DB2.rb,v
retrieving revision 1.6
retrieving revision 1.6.4.1
diff -u -r1.6 -r1.6.4.1
--- DB2.rb 3 Jul 2002 16:48:35 -0000 1.6
+++ DB2.rb 28 Dec 2002 14:36:30 -0000 1.6.4.1
@@ -29,6 +29,8 @@
#
# $Id$
#
+# Severly hacked by Elias Karakoulakis <ek...@na...>
+# to support large object functionality
require 'db2/db2cli.rb'
@@ -36,7 +38,7 @@
module DBD
module DB2
-USED_DBD_VERSION = "0.1"
+USED_DBD_VERSION = "0.2.2"
module Util
include DB2CLI
@@ -44,11 +46,16 @@
private
def rc_ok(rc)
- rc == SQL_SUCCESS or rc == SQL_SUCCESS_WITH_INFO
+ rc == SQL_SUCCESS or rc == SQL_SUCCESS_WITH_INFO or rc == SQL_NEED_DATA
end
- def error(rc, msg)
- raise DBI::DatabaseError.new(msg) unless rc_ok(rc)
+ def error(rc, msg, handleType=nil, handle=nil)
+ begin
+ unless handle.nil? or handleType.nil?
+ SQLGetDiagRec(handleType, handle, 1, 512).each { |a| msg << "\n\t#{a}" }
+ end
+ raise DBI::DatabaseError.new(msg, rc)
+ end unless rc_ok(rc)
end
end # module DB2Util
@@ -65,10 +72,10 @@
def connect(dbname, user, auth, attr)
rc, dbc = SQLAllocHandle(SQL_HANDLE_DBC, @env)
- error(rc, "Could not allocate Database Connection")
+ error(rc, "Could not allocate Database Connection", SQL_HANDLE_ENV, @env)
rc = SQLConnect(dbc, dbname, user, auth)
- error(rc, "Could not connect to Database")
+ error(rc, "Could not connect to Database", SQL_HANDLE_DBC, dbc)
return Database.new(dbc, attr)
end
@@ -107,25 +114,26 @@
class Database < DBI::BaseDatabase
include Util
- include SQL::BasicBind
- include SQL::BasicQuote
+ #include SQL::BasicBind
+ #include SQL::BasicQuote
def disconnect
rollback
rc = SQLDisconnect(@handle)
- error(rc, "Could not disconnect from Database")
+ error(rc, "Could not disconnect from Database", SQL_HANDLE_DBC, @handle)
rc = SQLFreeHandle(SQL_HANDLE_DBC, @handle)
- error(rc, "Could not free Database handle")
- end
+ error(rc, "Could not free Database handle", SQL_HANDLE_DBC, @handle)
+ end
+=begin
def tables
rc, stmt = SQLAllocHandle(SQL_HANDLE_STMT, @handle)
- error(rc, "Could not allocate Statement")
+ error(rc, "Could not allocate Statement", SQL_HANDLE_DBC, @handle)
rc = SQLTables(stmt, "", "%", "%", "TABLE, VIEW")
- error(rc, "Could not execute SQLTables")
+ error(rc, "Could not execute SQLTables", SQL_HANDLE_STMT, stmt)
st = Statement.new(stmt, nil)
res = st.fetch_all
@@ -133,7 +141,13 @@
res.collect {|row| row[1].to_s + "." + row[2].to_s}
end
+=end
+ def tables
+ execute("SELECT TABSCHEMA,TABNAME FROM SYSCAT.TABLES").fetch_all.collect { |row|
+ "#{row[0].strip}.#{row[1].strip}"
+ }
+ end
def ping
begin
@@ -145,45 +159,37 @@
return false
end
end
-
- def do(stmt, *bindvars)
- rc, sth = SQLAllocHandle(SQL_HANDLE_STMT, @handle)
- error(rc, "Could not allocate Statement")
-
- sql = bind(self, stmt, bindvars)
- rc = SQLExecDirect(sth, sql)
- error(rc, "Could not execute statement")
-
- rc, rpc = SQLRowCount(sth)
- error(rc, "Could not get RPC")
-
- rc = SQLFreeHandle(SQL_HANDLE_STMT, sth)
- error(rc, "Could not free Statement")
-
- return rpc
+
+ def prepare(sql)
+ new_statement = Statement.new(@handle, sql)
+ new_statement.prepare
+ return new_statement
end
- def prepare(statement)
- rc, stmt = SQLAllocHandle(SQL_HANDLE_STMT, @handle)
- error(rc, "Could not allocate Statement")
-
- Statement.new(stmt, statement)
+ def []=(attribute, value)
+ rc = case attribute.downcase
+ when 'autocommit'
+ SQLSetConnectAttr(@handle, SQL_ATTR_AUTOCOMMIT, (value ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF), SQL_IS_INTEGER)
+ else -1
+ end
+ error(rc, "Could not set DB2 connection attribute (#{attribute.inspect}=#{value.inspect})", SQL_HANDLE_DBC, @handle)
+ @attr[attribute] = value
+ end
+
+ def [](attribute)
+ @attr[attribute]
end
-
- # TODO
- #def []=(attr, value)
- #end
# TODO: method columns(table)
def commit
rc = SQLEndTran(SQL_HANDLE_DBC, @handle, SQL_COMMIT)
- error(rc, "Could not commit transaction")
+ error(rc, "Could not commit transaction", SQL_HANDLE_DBC, @handle)
end
def rollback
rc = SQLEndTran(SQL_HANDLE_DBC, @handle, SQL_ROLLBACK)
- error(rc, "Could not rollback transaction")
+ error(rc, "Could not rollback transaction", SQL_HANDLE_DBC, @handle)
end
end # class Database
@@ -193,22 +199,102 @@
include Util
include SQL::BasicBind
include SQL::BasicQuote
-
- def initialize(handle, statement)
+
+ attr_reader :handle
+
+ # WARNING: Statement.new first argument is its parent database handle
+ # and NOT a statement handle (to converge with other DBD drivers API)
+ def initialize(dbhandle, statement)
super(nil)
- @handle = handle
+ @dbhandle = dbhandle
@statement = statement
+ rc, @handle = SQLAllocHandle(SQL_HANDLE_STMT, dbhandle)
+ error(rc, "Could not allocate Statement", SQL_HANDLE_DBC, dbhandle)
@arr = []
- @params = []
@cols = nil
- @cols = get_col_info if @statement.nil?
+ #@cols = get_col_info if @statement.nil?
+ #
+ @prepared = false
+ @params = Hash.new
+ @boundvars = 0
+ puts "#{self}.initialize handle=#{@handle}, dbhandle=#{dbhandle}, statement=#{statement}" if $DEBUG
end
-
+
+ #Ruby's finalizers are strange: finalizer closure must be defined OUTSIDE self.new()
+ # so that no reference to object is visible to the finalizer...!
+ @@__statement_new__ = Statement.method('new')
+ def self.new(*args)
+ obj = @@__statement_new__[args[0], args[1]]
+ puts "new Statement, id=#{obj.id}" if $DEBUG
+ ObjectSpace.define_finalizer(obj, Statement.finalizer)
+ return obj
+ end
+ def self.finalizer
+ return proc {|id|
+ puts "Statement id=#{id} is FINALIZED" if $DEBUG
+ DB2CLI.free_bound_params(id)
+ }
+ end
+ def prepare
+ puts "#{self}.prepare hnd=#{@handle} stmt=#{@statement}" if $DEBUG
+ raise DBI::DatabaseError.new('statement already prepared') if @prepared
+ rc = SQLPrepare(@handle, @statement)
+ error(rc, "Could not prepare statement", SQL_HANDLE_STMT, @handle)
+ if $DEBUG
+ rc, count = SQLNumParams(@handle)
+ puts "#{self}.prepare: DB2CLI counted #{count} parameters in statement"
+ end
+ parse_params
+ @prepared = true
+ end
+
+ def bind_param(param_id, param_value, attribs=nil)
+ puts "#{self}.bind_param: idx=#{param_id}, param_value.size=#{param_value.to_s.length}, attribs=#{attribs.inspect}" if $DEBUG
+ raise InterfaceError, "only '?' parameters supported" unless param_id.is_a? Fixnum
+ direction = SQL_PARAM_INPUT
+ # TODO: ruby-to-CLI mapping should be done by the DBI layer, not the driver
+ rc, db2_sql_type, size, decimalDigits = SQLDescribeParam(@handle, param_id)
+ error(rc, 'SQLDescribeParam failed', SQL_HANDLE_STMT, @handle)
+ if attribs
+ case attribs['direction']
+ when 'in' then direction = SQL_PARAM_INPUT
+ when 'out' then direction = SQL_PARAM_OUTPUT
+ when 'inout' then direction = SQL_PARAM_INPUT_OUTPUT
+ end
+ if attribs['sql_type']
+ db2_sql_type = DBI_to_DB2_type_mapping[attribs['sql_type']][0]
+ puts 'db2_sql_type overriden: ' + DBI::Utils::ConstResolver.to_name(DBI::DB2CLI, db2_sql_type).join(',') if $DEBUG
+ end
+ end
+ # do the doo
+ rc = SQLBindParameter(@handle, param_id, direction, db2_sql_type, param_value)
+ error(rc, "Could not bind parameter #{param_id.inspect}", SQL_HANDLE_STMT, @handle)
+ @boundvars += 1
+ end
+
+=begin
def bind_param(param, value, attribs)
raise InterfaceError, "only ? parameters supported" unless param.is_a? Fixnum
@params[param-1] = value
end
+=end
+
+ def execute
+ puts "#{self}.execute" if $DEBUG
+ if @prepared and (@params.size != @boundvars)
+ errstr = (@params.size < @boundvars)? 'Too many' : 'Not enough'
+ errstr << " SQL parameters (expected #{@params.size}, got #{@boundvars})"
+ raise DBI::DatabaseError.new(errstr)
+ end
+
+ rc = SQLExecute(@handle)
+ error(rc, "Could not execute statement:\n#{@statement}", SQL_HANDLE_STMT, @handle)
+
+ @cols = get_col_info
+ backbind_params
+ end
+=begin
def execute
sql = bind(self, @statement, @params)
@@ -220,10 +306,12 @@
#rc = SQLExecute(@handle)
#error(rc, "Could not execute statement")
end
+=end
def finish
+ flush_params
rc = SQLFreeHandle(SQL_HANDLE_STMT, @handle)
- error(rc, "Could not free Statement")
+ error(rc, "Could not free Statement", SQL_HANDLE_STMT, @handle)
end
def fetch
@@ -250,18 +338,55 @@
def cancel
rc = SQLFreeStmt(@handle, SQL_CLOSE)
- error(rc, "Could not close/cancel statment")
+ error(rc, "Could not close/cancel statement", SQL_HANDLE_STMT, @handle)
@cols = nil
end
def rows
rc, rpc = SQLRowCount(@handle)
- error(rc, "Could not get RPC")
+ error(rc, "Could not get RPC", SQL_HANDLE_STMT, @handle)
return rpc
end
-
+ #------
private
+ #------
+
+ # parses statements for parameters
+ def parse_params
+ puts "#{self}.parse_params" if $DEBUG
+ result = ""
+ @params = []
+ #flush_params
+ #puts "statement tokens: #{tokens(@statement).join(', ')}" if $DEBUG
+ tokens(@statement).each { |part|
+ case part
+ when '?'
+ @params << part
+ result << '?'
+ when '??'
+ result << '?'
+ else
+ result << part
+ end
+ }
+ puts "#{self}.parse_params: params size=#{@params.size}" if $DEBUG
+ end
+
+ # binds inout/out (result) parameter buffers after query execution into ruby values
+ def backbind_params
+ puts "#{self}.backbind_params" if $DEBUG
+ flush_params
+ # TODO: some beautiful code that uses @boundvars to return new Ruby values
+ # from stored procedure execution
+ end
+
+ # frees buffers allocated for inout/out parameters
+ def flush_params
+ puts "#{self}.flush_params" if $DEBUG
+ DB2CLI.free_bound_params(self.id) # let CLI free up its internal param buffers
+ @boundvars = 0
+ end
# TODO: check typenames
DB2_to_DBI_type_mapping = {
@@ -300,6 +425,42 @@
DB2CLI::SQL_LONGVARGRAPHIC => [DBI::SQL_OTHER, 'LONG VARGRAPHIC']
}
+ DBI_to_DB2_type_mapping = {
+ DBI::SQL_DOUBLE => [DB2CLI::SQL_DOUBLE, 'DOUBLE'],
+ DBI::SQL_FLOAT => [DB2CLI::SQL_FLOAT, 'FLOAT'],
+ DBI::SQL_REAL => [DB2CLI::SQL_REAL, 'REAL'],
+
+ DBI::SQL_INTEGER => [DB2CLI::SQL_INTEGER, 'INTEGER'],
+ DBI::SQL_BIGINT => [DB2CLI::SQL_BIGINT, 'BIGINT'],
+ DBI::SQL_SMALLINT => [DB2CLI::SQL_SMALLINT, 'SMALLINT'],
+
+ DBI::SQL_DECIMAL => [DB2CLI::SQL_DECIMAL, 'DECIMAL'],
+ DBI::SQL_NUMERIC => [DB2CLI::SQL_NUMERIC, 'NUMERIC'],
+
+ DBI::SQL_DATE => [DB2CLI::SQL_TYPE_DATE, 'DATE'],
+ DBI::SQL_TIME => [DB2CLI::SQL_TYPE_TIME, 'TIME'],
+ DBI::SQL_TIMESTAMP => [DB2CLI::SQL_TYPE_TIMESTAMP,'TIMESTAMP'],
+
+ DBI::SQL_CHAR => [DB2CLI::SQL_CHAR, 'CHAR'],
+ DBI::SQL_VARCHAR => [DB2CLI::SQL_VARCHAR, 'VARCHAR'],
+ DBI::SQL_LONGVARCHAR => [DB2CLI::SQL_LONGVARCHAR, 'LONG VARCHAR'],
+ DBI::SQL_CLOB => [DB2CLI::SQL_CLOB, 'CLOB'],
+
+ DBI::SQL_BINARY => [DB2CLI::SQL_BINARY, 'BINARY'],
+ DBI::SQL_VARBINARY => [DB2CLI::SQL_VARBINARY, 'VARBINARY'],
+ DBI::SQL_LONGVARBINARY => [DB2CLI::SQL_LONGVARBINARY, 'LONG VARBINARY'],
+ DBI::SQL_BLOB => [DB2CLI::SQL_BLOB, 'BLOB'],
+
+ # DB2 specific types
+ 'SQL_BLOB_LOCATOR' => [DB2CLI::SQL_BLOB_LOCATOR, 'BLOB LOCATOR'],
+ 'SQL_CLOB_LOCATOR' => [DB2CLI::SQL_CLOB_LOCATOR, 'CLOB LOCATOR'],
+ 'SQL_DBCLOB' => [DB2CLI::SQL_DBCLOB, 'DBCLOB'],
+ 'SQL_DBCLOB_LOCATOR' => [DB2CLI::SQL_DBCLOB_LOCATOR, 'DBCLOB LOCATOR'],
+ 'SQL_GRAPHIC' => [DB2CLI::SQL_GRAPHIC, 'GRAPHIC'],
+ 'SQL_VARGRAPHIC' => [DB2CLI::SQL_VARGRAPHIC, 'VARGRAPHIC'],
+ 'SQL_LONGVARGRAPHIC' => [DB2CLI::SQL_LONGVARGRAPHIC, 'LONG VARGRAPHIC']
+ }
+
MAX_COL_SIZE = 256
#
@@ -307,11 +468,11 @@
#
def get_col_info
rc, nr_cols = SQLNumResultCols(@handle)
- error(rc, "Could not get number of result columns")
+ error(rc, "Could not get number of result columns", SQL_HANDLE_STMT, @handle)
(1..nr_cols).collect do |c|
rc, column_name, buflen, data_type, column_size, decimal_digits, nullable = SQLDescribeCol(@handle, c, MAX_COL_SIZE)
- error(rc, "Could not describe column")
+ error(rc, "Could not describe column", @handle, SQL_HANDLE_STMT)
sql_type, type_name = DB2_to_DBI_type_mapping[data_type]
@@ -329,11 +490,11 @@
def do_fetch(rc)
return nil if rc == SQL_NO_DATA_FOUND
- error(rc, "Could not fetch row")
+ error(rc, "Could not fetch row", @handle, SQL_HANDLE_STMT)
@cols.each_with_index do |c, i|
rc, content = SQLGetData(@handle, i+1, c['db2_type'], c['precision'])
- error(rc, "Could not get data")
+ error(rc, "Could not get data", @handle, SQL_HANDLE_STMT)
@arr[i] =
case content
|
