1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
|
require 'db2/db2cli.rb'
module DB2
module DB2Util
include DB2CLI
def free() SQLFreeHandle(@handle_type, @handle); end
def handle() @handle; end
def check_rc(rc)
if ![SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND].include?(rc)
rec = 1
msg = ''
loop do
a = SQLGetDiagRec(@handle_type, @handle, rec, 500)
break if a[0] != SQL_SUCCESS
msg << a[3] if !a[3].nil? and a[3] != '' # Create message.
rec += 1
end
raise "DB2 error: #{msg}"
end
end
end
class Environment
include DB2Util
def initialize
@handle_type = SQL_HANDLE_ENV
rc, @handle = SQLAllocHandle(@handle_type, SQL_NULL_HANDLE)
check_rc(rc)
end
def data_sources(buffer_length = 1024)
retval = []
max_buffer_length = buffer_length
a = SQLDataSources(@handle, SQL_FETCH_FIRST, SQL_MAX_DSN_LENGTH + 1, buffer_length)
retval << [a[1], a[3]]
max_buffer_length = [max_buffer_length, a[4]].max
loop do
a = SQLDataSources(@handle, SQL_FETCH_NEXT, SQL_MAX_DSN_LENGTH + 1, buffer_length)
break if a[0] == SQL_NO_DATA_FOUND
retval << [a[1], a[3]]
max_buffer_length = [max_buffer_length, a[4]].max
end
if max_buffer_length > buffer_length
get_data_sources(max_buffer_length)
else
retval
end
end
end
class Connection
include DB2Util
def initialize(environment)
@env = environment
@handle_type = SQL_HANDLE_DBC
rc, @handle = SQLAllocHandle(@handle_type, @env.handle)
check_rc(rc)
end
def connect(server_name, user_name = '', auth = '')
check_rc(SQLConnect(@handle, server_name, user_name, auth))
end
def set_connect_attr(attr, value)
value += "\0" if value.class == String
check_rc(SQLSetConnectAttr(@handle, attr, value))
end
def set_auto_commit_on
set_connect_attr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON)
end
def set_auto_commit_off
set_connect_attr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF)
end
def disconnect
check_rc(SQLDisconnect(@handle))
end
def rollback
check_rc(SQLEndTran(@handle_type, @handle, SQL_ROLLBACK))
end
def commit
check_rc(SQLEndTran(@handle_type, @handle, SQL_COMMIT))
end
end
class Statement
include DB2Util
def initialize(connection)
@conn = connection
@handle_type = SQL_HANDLE_STMT
@parms = [] #yun
@sql = '' #yun
@numParms = 0 #yun
@prepared = false #yun
@parmArray = [] #yun. attributes of the parameter markers
rc, @handle = SQLAllocHandle(@handle_type, @conn.handle)
check_rc(rc)
end
def columns(table_name)
check_rc(SQLColumns(@handle, "", "%", table_name, "%"))
fetch_all
end
def tables
check_rc(SQLTables(@handle, "", "%", "%", "TABLE"))
fetch_all
end
def prepare(sql)
@sql = sql
check_rc(SQLPrepare(@handle, sql))
rc, @numParms = SQLNumParams(@handle) #number of question marks
check_rc(rc)
#--------------------------------------------------------------------------
# parameter attributes are stored in instance variable @parmArray so that
# they are available when execute method is called.
#--------------------------------------------------------------------------
if @numParms > 0 # get parameter marker attributes
1.upto(@numParms) do |i| # parameter number starts from 1
rc, type, size, decimalDigits = SQLDescribeParam(@handle, i)
check_rc(rc)
@parmArray << Parameter.new(type, size, decimalDigits)
end
end
@prepared = true
self
end
def execute(*parms)
raise "The statement was not prepared" if @prepared == false
if parms.size == 1 and parms[0].class == Array
parms = parms[0]
end
if @numParms != parms.size
raise "Number of parameters supplied does not match with the SQL statement"
end
if @numParms > 0 #need to bind parameters
#--------------------------------------------------------------------
#calling bindParms may not be safe. Look comment below.
#--------------------------------------------------------------------
#bindParms(parms)
valueArray = []
1.upto(@numParms) do |i| # parameter number starts from 1
type = @parmArray[i - 1].class
size = @parmArray[i - 1].size
decimalDigits = @parmArray[i - 1].decimalDigits
if parms[i - 1].class == String
valueArray << parms[i - 1]
else
valueArray << parms[i - 1].to_s
end
rc = SQLBindParameter(@handle, i, type, size, decimalDigits, valueArray[i - 1])
check_rc(rc)
end
end
check_rc(SQLExecute(@handle))
if @numParms != 0
check_rc(SQLFreeStmt(@handle, SQL_RESET_PARAMS)) # Reset parameters
end
self
end
#-------------------------------------------------------------------------------
# The last argument(value) to SQLBindParameter is a deferred argument, that is,
# it should be available when SQLExecute is called. Even though "value" is
# local to bindParms method, it seems that it is available when SQLExecute
# is called. I am not sure whether it would still work if garbage collection
# is done between bindParms call and SQLExecute call inside the execute method
# above.
#-------------------------------------------------------------------------------
def bindParms(parms) # This is the real thing. It uses SQLBindParms
1.upto(@numParms) do |i| # parameter number starts from 1
rc, dataType, parmSize, decimalDigits = SQLDescribeParam(@handle, i)
check_rc(rc)
if parms[i - 1].class == String
value = parms[i - 1]
else
value = parms[i - 1].to_s
end
rc = SQLBindParameter(@handle, i, dataType, parmSize, decimalDigits, value)
check_rc(rc)
end
end
#------------------------------------------------------------------------------
# bind method does not use DB2's SQLBindParams, but replaces "?" in the
# SQL statement with the value before passing the SQL statement to DB2.
# It is not efficient and can handle only strings since it puts everything in
# quotes.
#------------------------------------------------------------------------------
def bind(sql, args) #does not use SQLBindParams
arg_index = 0
result = ""
tokens(sql).each do |part|
case part
when '?'
result << "'" + (args[arg_index]) + "'" #put it into quotes
arg_index += 1
when '??'
result << "?"
else
result << part
end
end
if arg_index < args.size
raise "Too many SQL parameters"
elsif arg_index > args.size
raise "Not enough SQL parameters"
end
result
end
## Break the sql string into parts.
#
# This is NOT a full lexer for SQL. It just breaks up the SQL
# string enough so that question marks, double question marks and
# quoted strings are separated. This is used when binding
# arguments to "?" in the SQL string. Note: comments are not
# handled.
#
def tokens(sql)
toks = sql.scan(/('([^'\\]|''|\\.)*'|"([^"\\]|""|\\.)*"|\?\??|[^'"?]+)/)
toks.collect { |t| t[0] }
end
def exec_direct(sql)
check_rc(SQLExecDirect(@handle, sql))
self
end
def set_cursor_name(name)
check_rc(SQLSetCursorName(@handle, name))
self
end
def get_cursor_name
rc, name = SQLGetCursorName(@handle)
check_rc(rc)
name
end
def row_count
rc, rowcount = SQLRowCount(@handle)
check_rc(rc)
rowcount
end
def num_result_cols
rc, cols = SQLNumResultCols(@handle)
check_rc(rc)
cols
end
def fetch_all
if block_given?
while row = fetch do
yield row
end
else
res = []
while row = fetch do
res << row
end
res
end
end
def fetch
cols = get_col_desc
rc = SQLFetch(@handle)
if rc == SQL_NO_DATA_FOUND
SQLFreeStmt(@handle, SQL_CLOSE) # Close cursor
SQLFreeStmt(@handle, SQL_RESET_PARAMS) # Reset parameters
return nil
end
raise "ERROR" unless rc == SQL_SUCCESS
retval = []
cols.each_with_index do |c, i|
rc, content = SQLGetData(@handle, i + 1, c[1], c[2] + 1) #yun added 1 to c[2]
retval << adjust_content(content)
end
retval
end
def fetch_as_hash
cols = get_col_desc
rc = SQLFetch(@handle)
if rc == SQL_NO_DATA_FOUND
SQLFreeStmt(@handle, SQL_CLOSE) # Close cursor
SQLFreeStmt(@handle, SQL_RESET_PARAMS) # Reset parameters
return nil
end
raise "ERROR" unless rc == SQL_SUCCESS
retval = {}
cols.each_with_index do |c, i|
rc, content = SQLGetData(@handle, i + 1, c[1], c[2] + 1) #yun added 1 to c[2]
retval[c[0]] = adjust_content(content)
end
retval
end
def get_col_desc
rc, nr_cols = SQLNumResultCols(@handle)
cols = (1..nr_cols).collect do |c|
rc, name, bl, type, col_sz = SQLDescribeCol(@handle, c, 1024)
[name.downcase, type, col_sz]
end
end
def adjust_content(c)
case c.class.to_s
when 'DB2CLI::NullClass'
return nil
when 'DB2CLI::Time'
"%02d:%02d:%02d" % [c.hour, c.minute, c.second]
when 'DB2CLI::Date'
"%04d-%02d-%02d" % [c.year, c.month, c.day]
when 'DB2CLI::Timestamp'
"%04d-%02d-%02d %02d:%02d:%02d" % [c.year, c.month, c.day, c.hour, c.minute, c.second]
else
return c
end
end
end
class Parameter
attr_reader :type, :size, :decimalDigits
def initialize(type, size, decimalDigits)
@type, @size, @decimalDigits = type, size, decimalDigits
end
end
end
|