Oracle SDO_GEOMETRY and DBI binding problem

iamh2o has asked for the wisdom of the Perl Monks concerning the following question:

Hello learned ones-

I'm having a problem using DBI with the ORACLE spatial data type SDO_GEOMETRY.

Specifically, I am attempting to use bind_param with this data type. When I try this the error returned states that Oracle is expecting SDO_GEOMETRY, but is getting CHAR...

here is what I've tried:
1)Binding naively
-----
my $insq = "insert into SHAPES (some_rectangles) values (?)";
$sth = $DBH->prepare($insq);

my $rectangle = "SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY($targetstart,$chrm_map->{$target_chrm_syn}->{'depth'}, $targetend,$chrm_map->{$target_chrm_syn}->{'depth'}))";

$sth->bind_param(1, $rectangle);

##This will give the "expecting GEOMETRY, got CHAR" type error from oracle.

I've also tried adding:
----
use DBD::Oracle qw(:ora_types);

#and then
----
$sth->bind_param(1,$rectangle,{ora_type => ORA_CLOB}) #i also tried ORA_BLOB...

no luck, same error.
If I try to specify SDO_GEOMETRY as the ora_type, DBI throws an error that this type is not supported.

I can prepare each insert and select from an insert string, and skip the binding, but that is not going to work long term as i have too many queries to make...

Thanks in Advance for your help & insight!

john major

Comment on Oracle SDO_GEOMETRY and DBI binding problem

Replies are listed 'Best First'.
Re: Oracle SDO_GEOMETRY and DBI binding problem by runrig (Abbot) on Jul 11, 2008 at 04:32 UTC
You can't bind strings that look like functions and then expect them to still behave like functions. This might work: `my $insq = "insert into SHAPES (blah..blah) values (SDO_GEOMETRY(2003, +NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(?,?,?,?))) +"; $sth = $DBH->prepare($insq);` [download] and then execute with appropriate values bound to the ?'s.	[reply] [d/l]
Re^2: Oracle SDO_GEOMETRY and DBI binding problem by Anonymous Monk on Jul 11, 2008 at 18:38 UTC
Thanks for the suggestion runrig! I gave it a try, and still no dice ('Invalid number' error thrown). I'm guessing that the commas internal to the SDO object definition are confusing the binding. I tried various quoting combinations, and still no luck.	[reply]
Re: Oracle SDO_GEOMETRY and DBI binding problem by pjotrik (Friar) on Jul 11, 2008 at 07:30 UTC
Please, enclose your code in `<code>`, so that it doesn't break the page layout. Thanks	[reply] [d/l]