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 



340
341
342
343
344
345
346
347
348
349
350
351
352
353
 
# File 'lib/active_record/vendor/db2.rb', line 340

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.



219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
 
# File 'lib/active_record/vendor/db2.rb', line 219

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.



199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/active_record/vendor/db2.rb', line 199

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, schema_name = '%') ⇒ Object 



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

def columns(table_name, schema_name = '%')
  check_rc(SQLColumns(@handle, '', schema_name.upcase, table_name.upcase, '%'))
  fetch_all
end

#exec_direct(sql) ⇒ Object 



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

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

#execute(*parms) ⇒ Object 



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

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 



296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
 
# File 'lib/active_record/vendor/db2.rb', line 296

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 



282
283
284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/active_record/vendor/db2.rb', line 282

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 



314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
 
# File 'lib/active_record/vendor/db2.rb', line 314

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 



332
333
334
335
336
337
338
 
# File 'lib/active_record/vendor/db2.rb', line 332

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 



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

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

#indexes(table_name, schema_name = '') ⇒ Object 



123
124
125
126
 
# File 'lib/active_record/vendor/db2.rb', line 123

def indexes(table_name, schema_name = '')
  check_rc(SQLStatistics(@handle, '', schema_name.upcase, table_name.upcase, SQL_INDEX_ALL, SQL_ENSURE))
  fetch_all
end

#num_result_cols ⇒ Object 



276
277
278
279
280
 
# File 'lib/active_record/vendor/db2.rb', line 276

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

#prepare(sql) ⇒ Object 



128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/active_record/vendor/db2.rb', line 128

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 



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

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

#set_cursor_name(name) ⇒ Object 



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

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

#tables(schema_name = '%') ⇒ Object 



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

def tables(schema_name = '%')
  check_rc(SQLTables(@handle, '', schema_name.upcase, '%', '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.



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

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