search

kubo / ruby-oci8

Ruby-oci8 - Oracle interface for ruby
Other
169 stars 75 forks source link

dbh.exec("insert into dummy values (:1)",nil) fails, why? #224

Closed jean-marc-voillequin closed 4 years ago

jean-marc-voillequin commented 4 years ago 
require 'oci8'
dbh = OCI8.new('me/password@db')
dbh.exec("drop table dummy") rescue nil
dbh.exec("create table dummy(c char)")
dbh.exec("insert into dummy values (:1)",nil)

Why this simple statement fails with:
Traceback (most recent call last):                                                                                                                         
        9: from a.rb:5:in `<main>'                                                                                                                         
        8: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/oci8.rb:262:in `exec'                                      
        7: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/oci8.rb:271:in `exec_internal'                             
        6: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/cursor.rb:128:in `exec'                                    
        5: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/cursor.rb:556:in `bind_params'                             
        4: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/cursor.rb:556:in `each_with_index'                         
        3: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/cursor.rb:556:in `each'                                    
        2: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/cursor.rb:560:in `block in bind_params'                    
        1: from C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/cursor.rb:104:in `bind_param'                              
C:/app/Ruby25-x64/lib/ruby/gems/2.5.0/gems/ruby-oci8-2.2.8-x64-mingw32/lib/oci8/cursor.rb:487:in `make_bind_object': bind type is not given. (RuntimeError)

ruby-oci8 (2.2.8 x64-mingw32)

kubo commented 4 years ago

You need to set the type of NULL.

cursor = dbh.parse("insert into dummy values (:1)")
cursor.bind_param(1, nil, String)
cursor.exec

The following is the same with the above except cursor is closed automatically.

dbh.exec("insert into dummy values (:1)", [nil, String])
jean-marc-voillequin commented 4 years ago

Thanks kubo. I can also make a dbh.exec("insert into dummy values (:1)",'') which has the same effect as ''=null in oracle. But why don't you do it automatically? The null case could be managed transparently by oci8? I know that NULL values have a datatype in Oracle, but implicit conversions made by oracle can be usefull here. It could avoid me to have to determine the datatype of the bind variable for a NULL bind variable. Am I wrong?

kubo commented 4 years ago

dbh.exec("insert into dummy values (:1)",'') works well in Oracle when the datatypes is string, number, date, timestamp and so on. However not all datatypes can be implicitly converted from string. For example object types.