获取OCIError:ORA-00932:不一致的数据类型从Rails 3的 [英] Getting OCIError: ORA-00932: inconsistent datatypes From Rails 3
问题描述
下面是该方案。
我在写我的地缘红宝石的Oracle适配器Ruby on Rails的它支持与SDO_GEOMETRY工作开箱。
它进行得很顺利。我写了codeS选择从Oracle数据库SDO_GEOMETRY对象成功。
一切都毁了,当我想写插入和更新部分。
下面是什么在我的脑海里。我希望能够做到这一点的语句:
Here is the scenario.
I'm writing my geo-ruby oracle adapter for Ruby On Rails which supports working with SDO_GEOMETRY out of box.
It was going well. I wrote codes for selecting SDO_GEOMETRY objects from Oracle DB successfully.
Everything ruins up when I wanted to write insert and update parts.
Following is what's in my mind. I want to be able to do this statement:
g = GeoShape.new(name:"point1", shape: Point.from_x_y(-34,-43,4326))
g.save
我产生以下从上面的语句SQL查询:
I generated following sql query from the above statements:
INSERT INTO "GEO_SHAPES" ("CREATED_AT", "ID", "NAME", "SHAPE", "UPDATED_AT") VALUES (:a1, :a2, :a3, :a4, :a5) [["created_at", Tue, 03 Jul 2012 08:42:01 UTC +00:00], ["id", 10112], ["name", "point1"], ["shape", "SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(-34,-43))"], ["updated_at", Tue, 03 Jul 2012 08:42:01 UTC +00:00]]
但执行上面的查询给了我这个错误:
But execution of above query gave me this error:
ActiveRecord::StatementInvalid: OCIError: ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR
我进入oracle_enhanced_adapter追查问题。我试图猴子修补它与手动初始化绑定[3] [1](这是SDO_GEOMETRY列在我的数据库中的值),如下所示:
I got into oracle_enhanced_adapter to trace the problem. I tried to monkey patch it and manually initialize binds[3][1] (which is the value of sdo_geometry column in my DB) as follows:
def exec_insert(sql, name, binds)
log(sql, name, binds) do
returning_id_index = nil
cursor = if @statements.key?(sql)
@statements[sql]
else
@statements[sql] = @connection.prepare(sql)
end
binds[3][1] = "SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(-34,-43))" ### DAVE
binds.each_with_index do |bind, i|
col, val = bind
if col == :returning_id
returning_id_index = i + 1
cursor.bind_returning_param(returning_id_index, Integer)
else
if val == "SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(-34,-43))" ### DAVE
cursor.bind_param(i + 1, val, OCI8::Object::Mdsys::SdoGeometry) ###DAVE
else ### DAVE
cursor.bind_param(i + 1, type_cast(val, col), col && col.type)
end
end
end
cursor.exec_update
不幸的是,它并没有帮助。我还是得到了同样的错误ORA-00932。
有任何想法吗?它是如此的关键,我来解决这个问题。
Unfortunately, it didn't help. I still got the same error ORA-00932.
Any ideas? It's so critical for me to fix this.
PS:### DAVE部分是oracle_enhanced_adapter.rb
我的猴子补丁
P.S:这是我的配置
P.S: ###DAVE parts are my monkey patches of oracle_enhanced_adapter.rb
P.S: Here is my config.
- 在甲骨文11.2
- 红宝石版本1.9.3(I386-darwin11.3.0)
- 的Rails 3.2.5版
- 在活动记录3.2.5版本
推荐答案
最后,我设法解决这个问题。
增强型适配器使用bind_param方法来进行绑定变量到适当的Oracle类型。这里曾是问题和错误。
我不得不重写某些codeS里oracle_enhanced_adpter.rb。
我添加一个方法到这个文件手工创建一个SDO_GEOMETRY对象。
然后,我绑定这个返回的对象为OCI8:对象:: MDSYS :: SdoGeometry类型。
Finally, I managed to fix the problem.
Enhanced adapter uses bind_param method to perform binding variables to their appropriate oracle types. Here was the problem and error.
I had to override some codes in oracle_enhanced_adpter.rb.
I add a method to this file to manually build a SDO_GEOMETRY object.
Then I bind this returned object to OCI8::Object::Mdsys::SdoGeometry type.
我的方法包含这些codeS(现在,仅用于创建SDO_GEOMETRY分):
My method contains these codes (Now, only for creating SDO_GEOMETRY Points):
#I needed a connection to my oracle database. Connection placeholder is conn.
def create_sdo_geometry_object(conn, gtype, srid, point, x, y)
local_cursor = conn.parse("BEGIN :geom := SDO_GEOMETRY(:sdo_gtype, :sdo_srid, :sdo_point, :sdo_elem_info_array, :sdo_ordinate_array); END;")
local_cursor.bind_param(:sdo_gtype, OraNumber)
local_cursor.bind_param(:sdo_srid, OraNumber)
local_cursor.bind_param(:sdo_point, OCI8::Object::Mdsys::SdoPointType)
local_cursor.bind_param(:sdo_elem_info_array, OCI8::Object::Mdsys::SdoElemInfoArray)
local_cursor.bind_param(:sdo_ordinate_array, OCI8::Object::Mdsys::SdoOrdinateArray)
local_cursor.bind_param(:geom, OCI8::Object::Mdsys::SdoGeometry)
sdo_gtype = OraNumber.new(gtype)
sdo_srid = OraNumber.new(srid)
sdo_point = nil #Temporarily I set it to nil
#sdo_elem_info_array must be [1,1,1] for creating points.
sdo_elem_info_array = OCI8::Object::Mdsys::SdoElemInfoArray.new(conn,OraNumber.new(1),OraNumber.new(1),OraNumber.new(1))
#I want to create a point with x and y coordinates
sdo_ordinate_array = OCI8::Object::Mdsys::SdoOrdinateArray.new(conn,OraNumber.new(x),OraNumber.new(y))
local_cursor[:sdo_gtype] = sdo_gtype
local_cursor[:sdo_srid] = sdo_srid
local_cursor[:sdo_point] = sdo_point
local_cursor[:sdo_elem_info_array] = sdo_elem_info_array
local_cursor[:sdo_ordinate_array] = sdo_ordinate_array
local_cursor.exec
local_cursor[:geom]
end
然后,当我想这个参数我用下面的code绑定:
Then when I wanted to bind this parameter I used the following code:
cursor.bind_param(i + 1, create_sdo_geometry_object("PARAMS HERE"), OCI8::Object::Mdsys::SdoGeometry)
它的工作就像一个魅力!
It worked like a charm!
这篇关于获取OCIError:ORA-00932:不一致的数据类型从Rails 3的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!