Class: DB2::Statement

Inherits:
Object
  • Object
show all
Includes:
DB2Util
Defined in:
lib/active_record/vendor/db2.rb

Instance Method Summary collapse

Methods included from DB2Util

#check_rc, #free, #handle

Constructor Details

#initialize(connection) ⇒ Statement

Returns a new instance of Statement.



101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/active_record/vendor/db2.rb', line 101

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

Instance Method Details

#adjust_content(c) ⇒ Object 



335
336
337
338
339
340
341
342
343
344
345
346
347
348
 
# File 'lib/active_record/vendor/db2.rb', line 335

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

#bind(sql, args) ⇒ Object

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.



214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/active_record/vendor/db2.rb', line 214

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

#bindParms(parms) ⇒ Object

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.



194
195
196
197
198
199
200
201
202
203
204
205
206
 
# File 'lib/active_record/vendor/db2.rb', line 194

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

#columns(table_name) ⇒ Object 



113
114
115
116
 
# File 'lib/active_record/vendor/db2.rb', line 113

def columns(table_name)
  check_rc(SQLColumns(@handle, "", "%", table_name, "%"))
  fetch_all
end

#exec_direct(sql) ⇒ Object 



249
250
251
252
 
# File 'lib/active_record/vendor/db2.rb', line 249

def exec_direct(sql)
  check_rc(SQLExecDirect(@handle, sql))
  self
end

#execute(*parms) ⇒ Object 



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
 
# File 'lib/active_record/vendor/db2.rb', line 143

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

#fetch ⇒ Object 



291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 
# File 'lib/active_record/vendor/db2.rb', line 291

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

#fetch_all ⇒ Object 



277
278
279
280
281
282
283
284
285
286
287
288
289
 
# File 'lib/active_record/vendor/db2.rb', line 277

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

#fetch_as_hash ⇒ Object 



309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
 
# File 'lib/active_record/vendor/db2.rb', line 309

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

#get_col_desc ⇒ Object 



327
328
329
330
331
332
333
 
# File 'lib/active_record/vendor/db2.rb', line 327

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

#get_cursor_name ⇒ Object 



259
260
261
262
263
 
# File 'lib/active_record/vendor/db2.rb', line 259

def get_cursor_name
  rc, name = SQLGetCursorName(@handle)
  check_rc(rc)
  name
end

#num_result_cols ⇒ Object 



271
272
273
274
275
 
# File 'lib/active_record/vendor/db2.rb', line 271

def num_result_cols
  rc, cols = SQLNumResultCols(@handle)
  check_rc(rc)
  cols
end

#prepare(sql) ⇒ Object 



123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/active_record/vendor/db2.rb', line 123

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

#row_count ⇒ Object 



265
266
267
268
269
 
# File 'lib/active_record/vendor/db2.rb', line 265

def row_count
  rc, rowcount = SQLRowCount(@handle)
  check_rc(rc)
  rowcount
end

#set_cursor_name(name) ⇒ Object 



254
255
256
257
 
# File 'lib/active_record/vendor/db2.rb', line 254

def set_cursor_name(name)
  check_rc(SQLSetCursorName(@handle, name))
  self
end

#tables ⇒ Object 



118
119
120
121
 
# File 'lib/active_record/vendor/db2.rb', line 118

def tables
  check_rc(SQLTables(@handle, "", "%", "%", "TABLE"))
  fetch_all
end

#tokens(sql) ⇒ Object

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.



244
245
246
247
 
# File 'lib/active_record/vendor/db2.rb', line 244

def tokens(sql)
  toks = sql.scan(/('([^'\\]|''|\\.)*'|"([^"\\]|""|\\.)*"|\?\??|[^'"?]+)/)
  toks.collect { |t| t[0] }
end