PageRenderTime 52ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 1ms

/test/test_oci8.rb

https://github.com/yaks/ruby-oci8
Ruby | 434 lines | 397 code | 27 blank | 10 comment | 15 complexity | 4ed8610143e9fd5b306dfc9650f686a2 MD5 | raw file
  1. require 'oci8'
  2. require 'test/unit'
  3. require File.dirname(__FILE__) + '/config'
  4. require 'bigdecimal'
  5. require 'rational'
  6. class TestOCI8 < Test::Unit::TestCase
  7. def setup
  8. @conn = get_oci8_connection
  9. end
  10. def teardown
  11. @conn.logoff
  12. end
  13. def test_rename
  14. drop_table('test_table')
  15. drop_table('test_rename_table')
  16. sql = <<-EOS
  17. CREATE TABLE test_rename_table
  18. (C CHAR(10) NOT NULL)
  19. EOS
  20. @conn.exec(sql)
  21. @conn.exec("RENAME test_rename_table TO test_table")
  22. drop_table('test_rename_table')
  23. end
  24. # USE_DYNAMIC_FETCH doesn't work well...
  25. # This test is disabled.
  26. def _test_long_type
  27. drop_table('test_table')
  28. @conn.exec('CREATE TABLE test_table (id number(38), lng long)')
  29. test_data1 = 'a' * 70000
  30. test_data2 = 'b' * 3000
  31. test_data3 = nil
  32. test_data4 = 'c' * 70000
  33. @conn.exec('insert into test_table values (:1, :2)', 1, test_data1)
  34. @conn.exec('insert into test_table values (:1, :2)', 2, [test_data2, :long])
  35. @conn.exec('insert into test_table values (:1, :2)', 3, [nil, :long])
  36. @conn.exec('insert into test_table values (:1, :2)', 4, [test_data4, :long])
  37. [8000, 65535, 65536, 80000].each do |read_len|
  38. @conn.long_read_len = read_len
  39. cursor = @conn.parse('SELECT lng from test_table order by id')
  40. cursor.exec
  41. assert_equal(test_data1, cursor.fetch[0])
  42. assert_equal(test_data2, cursor.fetch[0])
  43. assert_equal(test_data3, cursor.fetch[0])
  44. assert_equal(test_data4, cursor.fetch[0])
  45. cursor.close
  46. end
  47. drop_table('test_table')
  48. end
  49. def test_long_type
  50. @conn.long_read_len = 80000
  51. drop_table('test_table')
  52. @conn.exec('CREATE TABLE test_table (id number(38), lng long)')
  53. test_data1 = 'a' * 70000
  54. test_data2 = 'b' * 3000
  55. test_data3 = nil
  56. test_data4 = 'c' * 70000
  57. @conn.exec('insert into test_table values (:1, :2)', 1, test_data1)
  58. @conn.exec('insert into test_table values (:1, :2)', 2, [test_data2, :long])
  59. @conn.exec('insert into test_table values (:1, :2)', 3, [nil, :long])
  60. @conn.exec('insert into test_table values (:1, :2)', 4, [test_data4, :long])
  61. cursor = @conn.parse('SELECT lng from test_table order by id')
  62. cursor.exec
  63. assert_equal(test_data1, cursor.fetch[0])
  64. assert_equal(test_data2, cursor.fetch[0])
  65. assert_equal(test_data3, cursor.fetch[0])
  66. assert_equal(test_data4, cursor.fetch[0])
  67. cursor.close
  68. drop_table('test_table')
  69. end
  70. def test_select
  71. drop_table('test_table')
  72. sql = <<-EOS
  73. CREATE TABLE test_table
  74. (C CHAR(10) NOT NULL,
  75. V VARCHAR2(20),
  76. N NUMBER(10, 2),
  77. D1 DATE, D2 DATE, D3 DATE, D4 DATE,
  78. INT NUMBER(30), BIGNUM NUMBER(30))
  79. STORAGE (
  80. INITIAL 4k
  81. NEXT 4k
  82. MINEXTENTS 1
  83. MAXEXTENTS UNLIMITED
  84. PCTINCREASE 0)
  85. EOS
  86. @conn.exec(sql)
  87. cursor = @conn.parse("INSERT INTO test_table VALUES (:C, :V, :N, :D1, :D2, :D3, :D4, :INT, :BIGNUM)")
  88. 1.upto(10) do |i|
  89. if i == 1
  90. dt = [nil, OraDate]
  91. else
  92. dt = OraDate.new(2000 + i, 8, 3, 23, 59, 59)
  93. end
  94. cursor.exec(format("%10d", i * 10), i.to_s, i, dt, dt, dt, dt, i * 11111111111, i * 10000000000)
  95. end
  96. cursor.close
  97. cursor = @conn.parse("SELECT * FROM test_table ORDER BY c")
  98. cursor.define(5, Time) # define 5th column as Time
  99. cursor.define(6, Date) # define 6th column as Date
  100. cursor.define(7, DateTime) # define 7th column as DateTime
  101. cursor.define(8, Integer) # define 8th column as Integer
  102. cursor.define(9, Bignum) # define 9th column as Bignum
  103. cursor.exec
  104. assert_equal(["C", "V", "N", "D1", "D2", "D3", "D4", "INT", "BIGNUM"], cursor.get_col_names)
  105. 1.upto(10) do |i|
  106. rv = cursor.fetch
  107. assert_equal(format("%10d", i * 10), rv[0])
  108. assert_equal(i.to_s, rv[1])
  109. assert_equal(i, rv[2])
  110. if i == 1
  111. assert_nil(rv[3])
  112. assert_nil(rv[4])
  113. assert_nil(rv[5])
  114. assert_nil(rv[6])
  115. else
  116. tm = Time.local(2000 + i, 8, 3, 23, 59, 59)
  117. dt = Date.civil(2000 + i, 8, 3)
  118. dttm = DateTime.civil(2000 + i, 8, 3, 23, 59, 59, Time.now.utc_offset.to_r/86400)
  119. assert_equal(tm, rv[3])
  120. assert_equal(tm, rv[4])
  121. assert_equal(dt, rv[5])
  122. assert_equal(dttm, rv[6])
  123. end
  124. assert_equal(i * 11111111111, rv[7])
  125. assert_equal(i * 10000000000, rv[8])
  126. end
  127. assert_nil(cursor.fetch)
  128. # fetch_hash with block
  129. cursor.exec
  130. i = 1
  131. cursor.fetch_hash do |row|
  132. assert_equal(format("%10d", i * 10), row['C'])
  133. assert_equal(i.to_s, row['V'])
  134. assert_equal(i, row['N'])
  135. if i == 1
  136. assert_nil(row['D1'])
  137. assert_nil(row['D2'])
  138. assert_nil(row['D3'])
  139. assert_nil(row['D4'])
  140. else
  141. tm = Time.local(2000 + i, 8, 3, 23, 59, 59)
  142. dt = Date.civil(2000 + i, 8, 3)
  143. dttm = DateTime.civil(2000 + i, 8, 3, 23, 59, 59, Time.now.utc_offset.to_r/86400)
  144. assert_equal(tm, row['D1'])
  145. assert_equal(tm, row['D2'])
  146. assert_equal(dt, row['D3'])
  147. assert_equal(dttm, row['D4'])
  148. end
  149. assert_equal(i * 11111111111, row['INT'])
  150. assert_equal(i * 10000000000, row['BIGNUM'])
  151. i += 1
  152. end
  153. assert_equal(11, i)
  154. cursor.close
  155. drop_table('test_table')
  156. end
  157. def test_bind_cursor
  158. # FIXME: check again after upgrading Oracle 9.2 to 9.2.0.4.
  159. return if $oracle_version < OCI8::ORAVER_10_1
  160. drop_table('test_table')
  161. sql = <<-EOS
  162. CREATE TABLE test_table
  163. (C CHAR(10) NOT NULL,
  164. V VARCHAR2(20),
  165. N NUMBER(10, 2),
  166. D1 DATE, D2 DATE, D3 DATE,
  167. INT NUMBER(30), BIGNUM NUMBER(30))
  168. STORAGE (
  169. INITIAL 4k
  170. NEXT 4k
  171. MINEXTENTS 1
  172. MAXEXTENTS UNLIMITED
  173. PCTINCREASE 0)
  174. EOS
  175. @conn.exec(sql)
  176. cursor = @conn.parse("INSERT INTO test_table VALUES (:C, :V, :N, :D1, :D2, :D3, :INT, :BIGNUM)")
  177. 1.upto(10) do |i|
  178. if i == 1
  179. dt = [nil, OraDate]
  180. else
  181. dt = OraDate.new(2000 + i, 8, 3, 23, 59, 59)
  182. end
  183. cursor.exec(format("%10d", i * 10), i.to_s, i, dt, dt, dt, i, i)
  184. end
  185. cursor.close
  186. plsql = @conn.parse("BEGIN OPEN :cursor FOR SELECT * FROM test_table ORDER BY c; END;")
  187. plsql.bind_param(':cursor', nil, OCI8::Cursor)
  188. plsql.exec
  189. cursor = plsql[':cursor']
  190. cursor.define(5, Time) # define 5th column as Time
  191. cursor.define(6, Date) # define 6th column as Date
  192. cursor.define(7, Integer) # define 7th column as Integer
  193. cursor.define(8, Bignum) # define 8th column as Integer
  194. assert_equal(["C", "V", "N", "D1", "D2", "D3", "INT", "BIGNUM"], cursor.get_col_names)
  195. 1.upto(10) do |i|
  196. rv = cursor.fetch
  197. assert_equal(format("%10d", i * 10), rv[0])
  198. assert_equal(i.to_s, rv[1])
  199. assert_equal(i, rv[2])
  200. if i == 1
  201. assert_nil(rv[3])
  202. assert_nil(rv[4])
  203. assert_nil(rv[5])
  204. else
  205. dttm = DateTime.civil(2000 + i, 8, 3, 23, 59, 59, Time.now.utc_offset.to_r/86400)
  206. tm = Time.local(2000 + i, 8, 3, 23, 59, 59)
  207. dt = Date.civil(2000 + i, 8, 3)
  208. assert_equal(tm, rv[3])
  209. assert_equal(tm, rv[4])
  210. assert_equal(dt, rv[5])
  211. end
  212. assert_equal(i, rv[6])
  213. assert_equal(i, rv[7])
  214. end
  215. assert_nil(cursor.fetch)
  216. cursor.close
  217. drop_table('test_table')
  218. end
  219. def test_cursor_in_result_set
  220. drop_table('test_table')
  221. sql = <<-EOS
  222. CREATE TABLE test_table (N NUMBER(10, 2))
  223. STORAGE (
  224. INITIAL 4k
  225. NEXT 4k
  226. MINEXTENTS 1
  227. MAXEXTENTS UNLIMITED
  228. PCTINCREASE 0)
  229. EOS
  230. @conn.exec(sql)
  231. cursor = @conn.parse("INSERT INTO test_table VALUES (:1)")
  232. 1.upto(10) do |i|
  233. cursor.exec(i)
  234. end
  235. cursor.close
  236. cursor = @conn.exec(<<EOS)
  237. select a.n, cursor (select a.n + b.n
  238. from test_table b
  239. order by n)
  240. from test_table a
  241. order by n
  242. EOS
  243. 1.upto(10) do |i|
  244. row = cursor.fetch
  245. assert_equal(i, row[0])
  246. cursor_in_result_set = row[1]
  247. 1.upto(10) do |j|
  248. row2 = cursor_in_result_set.fetch
  249. assert_equal(i + j, row2[0])
  250. end
  251. assert_nil(cursor_in_result_set.fetch) # check end of row data
  252. cursor_in_result_set.close
  253. end
  254. assert_nil(cursor.fetch) # check end of row data
  255. drop_table('test_table')
  256. end
  257. def test_binary_float
  258. return if $oracle_version < OCI8::ORAVER_10_1
  259. # Oracle 10g or upper
  260. cursor = @conn.parse("select CAST(:1 AS BINARY_FLOAT), CAST(:2 AS BINARY_DOUBLE) from dual")
  261. bind_val = -1.0
  262. cursor.bind_param(1, 10.0, :binary_double)
  263. cursor.bind_param(2, nil, :binary_double)
  264. while bind_val < 10.0
  265. cursor[2] = bind_val
  266. cursor.exec
  267. rv = cursor.fetch
  268. assert_equal(10.0, rv[0])
  269. assert_equal(bind_val, rv[1])
  270. bind_val += 1.234
  271. end
  272. [-1.0/0.0, # -Infinite
  273. +1.0/0.0, # +Infinite
  274. 0.0/0.0 # NaN
  275. ].each do |num|
  276. cursor[1] = num
  277. cursor[2] = num
  278. cursor.exec
  279. rv = cursor.fetch
  280. if num.nan?
  281. assert(rv[0].nan?)
  282. assert(rv[1].nan?)
  283. else
  284. assert_equal(num, rv[0])
  285. assert_equal(num, rv[1])
  286. end
  287. end
  288. cursor.close
  289. end
  290. def test_clob_nclob_and_blob
  291. return if OCI8::oracle_client_version < OCI8::ORAVER_8_1
  292. drop_table('test_table')
  293. sql = <<-EOS
  294. CREATE TABLE test_table (id number(5), C CLOB, NC NCLOB, B BLOB)
  295. STORAGE (
  296. INITIAL 100k
  297. NEXT 100k
  298. MINEXTENTS 1
  299. MAXEXTENTS UNLIMITED
  300. PCTINCREASE 0)
  301. EOS
  302. @conn.exec(sql)
  303. cursor = @conn.parse("INSERT INTO test_table VALUES (:1, :2, :3, :4)")
  304. 0.upto(9) do |i|
  305. val = format('%d', i) * 4096
  306. cursor.exec(i, OCI8::CLOB.new(@conn, val), OCI8::NCLOB.new(@conn, val), OCI8::BLOB.new(@conn, val))
  307. end
  308. cursor.close
  309. cursor = @conn.exec("select * from test_table order by id")
  310. 0.upto(9) do |i|
  311. rv = cursor.fetch
  312. val = format('%d', i) * 4096
  313. assert_equal(i, rv[0])
  314. assert_instance_of(OCI8::CLOB, rv[1])
  315. assert_instance_of(OCI8::NCLOB, rv[2])
  316. assert_instance_of(OCI8::BLOB, rv[3])
  317. assert_equal(val, rv[1].read)
  318. assert_equal(val.length, rv[2].size)
  319. assert_equal(val, rv[2].read)
  320. assert_equal(val, rv[3].read)
  321. end
  322. assert_nil(cursor.fetch)
  323. cursor.close
  324. drop_table('test_table')
  325. end
  326. def test_select_number
  327. drop_table('test_table')
  328. @conn.exec(<<EOS)
  329. CREATE TABLE test_table (n NUMBER, n20 NUMBER(20), n14_2 NUMBER(14,2), n15_2 NUMBER(15,2), flt FLOAT)
  330. STORAGE (
  331. INITIAL 100k
  332. NEXT 100k
  333. MINEXTENTS 1
  334. MAXEXTENTS UNLIMITED
  335. PCTINCREASE 0)
  336. EOS
  337. @conn.exec(<<EOS)
  338. INSERT INTO test_table values(12345678901234, 12345678901234567890, 123456789012.34, 1234567890123.45, 1234.5)
  339. EOS
  340. @conn.exec("select * from test_table") do |row|
  341. assert_equal(row[0], 12345678901234)
  342. assert_equal(row[1], 12345678901234567890)
  343. assert_equal(row[2], 123456789012.34)
  344. assert_equal(row[3], BigDecimal("1234567890123.45"))
  345. assert_equal(row[4], 1234.5)
  346. assert_instance_of(BigDecimal, row[0])
  347. assert_instance_of(Bignum, row[1])
  348. assert_instance_of(Float, row[2])
  349. assert_instance_of(BigDecimal, row[3])
  350. assert_instance_of(Float, row[4])
  351. end
  352. drop_table('test_table')
  353. end
  354. def test_bind_number_with_implicit_conversions
  355. src = [1, 1.2, BigDecimal("1.2"), Rational(12, 10)]
  356. int = [1, 1, 1, 1]
  357. flt = [1, 1.2, 1.2, 1.2]
  358. dec = [BigDecimal("1"), BigDecimal("1.2"), BigDecimal("1.2"), BigDecimal("1.2")]
  359. rat = [Rational(1), Rational(12, 10), Rational(12, 10), Rational(12, 10)]
  360. cursor = @conn.parse("begin :1 := :2; end;")
  361. # Float
  362. cursor.bind_param(1, nil, Float)
  363. cursor.bind_param(2, nil, Float)
  364. src.each_with_index do |s, idx|
  365. cursor[2] = s
  366. cursor.exec
  367. assert_equal(cursor[1], flt[idx])
  368. assert_kind_of(Float, cursor[1])
  369. end
  370. # Fixnum
  371. cursor.bind_param(1, nil, Fixnum)
  372. cursor.bind_param(2, nil, Fixnum)
  373. src.each_with_index do |s, idx|
  374. cursor[2] = s
  375. cursor.exec
  376. assert_equal(cursor[1], int[idx])
  377. assert_kind_of(Fixnum, cursor[1])
  378. end
  379. # Integer
  380. cursor.bind_param(1, nil, Integer)
  381. cursor.bind_param(2, nil, Integer)
  382. src.each_with_index do |s, idx|
  383. cursor[2] = s
  384. cursor.exec
  385. assert_equal(cursor[1], int[idx])
  386. assert_kind_of(Integer, cursor[1])
  387. end
  388. # BigDecimal
  389. cursor.bind_param(1, nil, BigDecimal)
  390. cursor.bind_param(2, nil, BigDecimal)
  391. src.each_with_index do |s, idx|
  392. cursor[2] = s
  393. cursor.exec
  394. assert_equal(cursor[1], dec[idx])
  395. assert_kind_of(BigDecimal, cursor[1])
  396. end
  397. # Rational
  398. cursor.bind_param(1, nil, Rational)
  399. cursor.bind_param(2, nil, Rational)
  400. src.each_with_index do |s, idx|
  401. cursor[2] = s
  402. cursor.exec
  403. assert_equal(cursor[1], rat[idx])
  404. assert_kind_of(Rational, cursor[1])
  405. end
  406. end
  407. end # TestOCI8