Re: DBD-Oracle - obtaining OCI handles from $dbh
On Fri, Oct 28, 2011 at 11:09:20AM -0400, Lyle Brooks wrote: > > The only other comment I have on that approach is that the� > interface comes out looking very C-like...meaning, the Perl > interface looks almost like the OCI C interface, which is > not too surprising. I found myself wishing that Oracle::OCI > would look more Perl-ish. Of course, I understand the� > argument that by mirroring the OCI C interface, then the Perl > interface is consistent with the OCI C documentation and hence > there would not be a need to learn "two" OCI interfaces. I'd hope people would develop higher-level interfaces for various aspects of Oracle functionality using Oracle::OCI as an underlying API. An Oracle::AQ module for example. > My work uses a good deal of the Oracle AQ functionality, and > so I've found myself repeated wishing that I could find a� > way to Oracle::OCI work...but it has left me mostly frustrated. Hopefully between Charles, Martin, and yourself, we can breath some new life into Oracle::OCI. Tim.
Re: DBD-Oracle - obtaining OCI handles from $dbh
On Fri, Oct 28, 2011 at 05:19:33PM +0100, Martin J. Evans wrote: > On 28/10/2011 16:34, Charles Jardine wrote: > >I still use Oracle::OCI. There is a patch below which may > >allow you to build it under Oracle 11.2. > > > >As you will see, I have kept it going by botching things. > >Whenever a new feature of OCI causes trouble, I add it to the > >list of features to omit. I would like to do a better job, but > >I simply don't have the time. All I can do is to offer this > >patch on an as-seen basis, in the hope that it might inspire > >some else to do a proper job of rescuing Oracle::OCI. > > > Aha, I thought it was you Charles who helped me last time I tried to > build Oracle::OCI. > > Perhaps I'll find time to give it another go. That would be awesome. Tim.
Re: DBD-Oracle - obtaining OCI handles from $dbh
On 28/10/2011 16:34, Charles Jardine wrote: On 28/10/11 16:09, Lyle Brooks wrote: Tim, I have looked at the Oracle::OCI module. I really like the concept, and it would undeniably be an extremely useful and powerful tool. However, I couldn't get it to work (by that I mean...I couldn't get it to build). I still use Oracle::OCI. There is a patch below which may allow you to build it under Oracle 11.2. As you will see, I have kept it going by botching things. Whenever a new feature of OCI causes trouble, I add it to the list of features to omit. I would like to do a better job, but I simply don't have the time. All I can do is to offer this patch on an as-seen basis, in the hope that it might inspire some else to do a proper job of rescuing Oracle::OCI. Aha, I thought it was you Charles who helped me last time I tried to build Oracle::OCI. Perhaps I'll find time to give it another go. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: DBD-Oracle - obtaining OCI handles from $dbh
On 28/10/2011 16:09, Lyle Brooks wrote: Tim, I have looked at the Oracle::OCI module. I really like the concept, and it would undeniably be an extremely useful and powerful tool. However, I couldn't get it to work (by that I mean...I couldn't get it to build). As I understand Oracle::OCI, rather than provide a handcrafted XS� interface to all the OCI C functions, the Oracle::OCI module attempts to parse the OCI C header files and then auto-build the XS glue routines. This is a laudable approach, as there are a significant number of OCI functions to inteface with. The difficulty I ran into was that I could not get the parsing of the OCI header files to work properly. I tried the altered h2xs program that came with Oracle::OCI, but I failed in getting that to work with my Oracle installation. I also tried looking into a couple other approaches to parsing the OCI header files and autogenerating the XS routines, but again...my results were not fruitful. The only other comment I have on that approach is that the� interface comes out looking very C-like...meaning, the Perl interface looks almost like the OCI C interface, which is not too surprising. I found myself wishing that Oracle::OCI would look more Perl-ish. Of course, I understand the� argument that by mirroring the OCI C interface, then the Perl interface is consistent with the OCI C documentation and hence there would not be a need to learn "two" OCI interfaces. My work uses a good deal of the Oracle AQ functionality, and so I've found myself repeated wishing that I could find a� way to Oracle::OCI work...but it has left me mostly frustrated. Lyle I looked at Oracle::OCI briefly a year or so ago (may be more). I had loads of trouble getting it to work. Someone on this list gave me some good pointers but I'm not at work right now and so cannot dig the email out (might have been private). You could try searching this list (or the dbi-dev) list for me and Oracle::OCI. I think it might have been Charles Jardine that gave me the hints that got it working. I cannot find the post now. There was also a request from Tim in 2008 for someone to take it over but nothing came of it. I'd like to see Oracle::OCI working again too but when I last looked the headers had changed so much it looked a real pain - unfortunately I've no time right now to do it myself. Last time I wanted Oracle::OCI I ended up patching DBD::Oracle to do what I needed. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com Quoting Tim Bunce (tim.bu...@pobox.com): I'm late to this thread (it went into a different mail folder). Lyle, have you see the Oracle::OCI module? It provides deep integration between Oracle::OCI and DBD::Oracle. To make that possible I added a get_oci_handle function pointer to DBD::Oracle's handles. It lets you ask for any kind of OCI handle from any kind of DBI handle (drh, dbh, sth). Oracle::OCI calls it like this: void * get_oci_handle(SV *h, int handle_type, int flags) { STRLEN lna; typedef void *(*hook_type)_((imp_xxh_t *imp_xxh, int handle_type, int flags)); hook_type hook; /* D_imp_xxh(h); */ imp_xxh_t *imp_xxh; if (flags& 1) warn("get_oci_handle(%s,%d,%d)", SvPV(h,lna), handle_type, flags); imp_xxh = (imp_xxh_t*)(DBIh_COM(h)); if (DBIc_TYPE(imp_xxh) == DBIt_ST) hook = (hook_type)((imp_sth_t*)imp_xxh)->get_oci_handle; else if (DBIc_TYPE(imp_xxh) == DBIt_DB) hook = (hook_type)((imp_dbh_t*)imp_xxh)->get_oci_handle; else croak("Can't get oci handle type %d from %s. Unsupported DBI handle type", handle_type, SvPV(h,lna)); return hook(imp_xxh, handle_type, flags); } Then calls it like this: get_oci_handle(arg, OCI_HTYPE_ERROR, 0); get_oci_handle(arg, OCI_HTYPE_SVCCTX, 0); get_oci_handle(arg, OCI_HTYPE_ENV, 0); get_oci_handle(arg, OCI_HTYPE_SERVER, 0); get_oci_handle(arg, OCI_HTYPE_SESSION, 0); get_oci_handle(arg, OCI_HTYPE_STMT, 0); I'd have no objection to adding a perl method to DBD::Oracle to access the get_oci_handle function. Perhaps ora_get_oci_handle($handle_type). Tim [still sad that Oracle::OCI doesn't get more love] On Thu, Oct 27, 2011 at 02:39:15PM -0400, Lyle Brooks wrote: Ok, so with the following addition to Oracle.pm DBD::Oracle::db->install_method("ora_oci_handles"); my little test program "worked". By "worked", I mean I did a connect to the database, then did my @h = $dbh->ora_oci_handles(); and it returned 4 integers (ie. the value of the pointers), which is what I expected/wanted. I haven't yet tested that I can now pass these pointer values to the C++ libraries and have them digest it properly...but that would be next. As for how much anyone else might find use for thisprobably not a wide audience. But it is a nice hack! Thanks for the pointers. Quoting John Scoles (byter...@h
Re: DBD-Oracle - obtaining OCI handles from $dbh
On 28/10/11 16:09, Lyle Brooks wrote: > Tim, > > I have looked at the Oracle::OCI module. I really like the > concept, and it would undeniably be an extremely useful and > powerful tool. However, I couldn't get it to work (by that > I mean...I couldn't get it to build). I still use Oracle::OCI. There is a patch below which may allow you to build it under Oracle 11.2. As you will see, I have kept it going by botching things. Whenever a new feature of OCI causes trouble, I add it to the list of features to omit. I would like to do a better job, but I simply don't have the time. All I can do is to offer this patch on an as-seen basis, in the hope that it might inspire some else to do a proper job of rescuing Oracle::OCI. diff -ur Oracle-OCI-0.06.clean/01base.t Oracle-OCI-0.06.patched/01base.t --- Oracle-OCI-0.06.clean/01base.t 2001-08-31 23:02:45.0 +0100 +++ Oracle-OCI-0.06.patched/01base.t2011-10-17 10:52:24.204427839 +0100 @@ -96,7 +96,7 @@ ok OCIAttrSet($$authp, OCI_HTYPE_SESSION, @user_buf_len, OCI_ATTR_USERNAME, $errhp), 0; ok OCIAttrSet($$authp, OCI_HTYPE_SESSION, @pass_buf_len, OCI_ATTR_PASSWORD, $errhp), 0; -ok $status=OCISessionBegin($svchp, $errhp, $authp, OCI_CRED_RDBMS, OCI_DEFAULT), 0; +ok $status=OCISessionBegin($svchp, $errhp, $authp, $user ? OCI_CRED_RDBMS : OCI_CRED_EXT, OCI_DEFAULT), 0; warn get_oci_error($errhp, $status) unless $status == OCI_SUCCESS; ok OCIAttrSet($$svchp, OCI_HTYPE_SVCCTX, $$authp, 0, OCI_ATTR_SESSION, $errhp), 0; diff -ur Oracle-OCI-0.06.clean/boot Oracle-OCI-0.06.patched/boot --- Oracle-OCI-0.06.clean/boot 2001-08-30 16:49:24.0 +0100 +++ Oracle-OCI-0.06.patched/boot2011-10-17 11:31:37.856430344 +0100 @@ -1,9 +1,8 @@ -#!/opt/perl5/bin/perl -w - require 5.6.0; use strict; use Carp; +use Config; use File::Path; use File::Copy; use File::Basename; @@ -17,6 +16,15 @@ $| = 1; +# The following is based on conventions entirely local to Jackdaw. +# Perl install trees are at /usr/local/perl/-. +# Libraries for DBD::Oracle are at $ORACLE_HOME/PERL/-/lib/DBD/Oracle. + +my $perlpath = $Config{perlpath}; +my $perlvl = (split /\//, $perlpath)[-3]; +my $ora_arch_dir = "$ENV{ORACLE_HOME}/PERL/$perlvl/lib/auto/DBD/Oracle"; + +print "Oracle arch dir is $ora_arch_dir\n"; # configuration section. @@ -99,6 +107,21 @@ TypeArrayByRef => "?", EnvCallback => "?", SharedLibInit => "uses 'dvoid *argv[]' type that we've not supported yet", +XmlDbInitXmlCtx => "", +XmlDbFreeXmlCtx => "", +XmlDbGetFullyQualifiedSchemaUrl => "", +XmlDbMemCallback => "", +XmlDbOrastreamFromLob => "", +XmlDbRewriteXMLDiff => "", +XmlDbStreamClose => "", +XmlDbStreamFromXMLType => "", +XmlDbStreamRead => "", +XmlFreeDRCtx => "", +XmlInitDRCtx => "", +LobGetDeduplicateRegions => "", +XStreamInLCRCallbackSend => "", +XStreamOutLCRCallbackReceive => "", + NumberToRealArray => "", '_ORACLE' => "empty define - used to prevent multiple #includes", '_FLAGS'=> "empty define - used to prevent multiple #includes", } } ], @@ -131,15 +154,14 @@ -d $orahome or croak "Error: no such directory: $orahome"; -r _ && -x _ or croak "Error: bad mods on $orahome"; -my $demodir = "$orahome/rdbms/demo"; --d $demodir or croak "Error: no directory '$demodir'"; --r _ && -x _ or croak "Error: bad mods on '$demodir'"; -my $oci_hdr = "$demodir/oci.h"; +my $publicdir = "$orahome/rdbms/public"; +-d $publicdir or croak "Error: no directory '$publicdir'"; +-r _ && -x _ or croak "Error: bad mods on '$publicdir'"; +my $oci_hdr = "$publicdir/oci.h"; -e $oci_hdr or croak "Error: hit by a missing oci.h"; -r _or croak "Error: unreadable $oci_hdr"; -my @ora_dirs = ($demodir, - "$orahome/rdbms/public", +my @ora_dirs = ($publicdir, "$orahome/network/public", "$orahome/plsql/public", ); @@ -165,7 +187,8 @@ my $skip_regex = '^OCI(?!' . join('|',@skip_list) . ')|^SQL'; my @h2xsargz = ( -qw( ./h2xs -d -O -n Oracle::OCI ), +$perlpath, +qw( h2xs -d -O -n Oracle::OCI ), "-F ".join(" ", map { "-I$_" } @ora_dirs), "-I".join(",", @ora_dirs), "-E get_oci_error,get_oci_handle,oci_buf_len,OCIAttrGet", @@ -293,8 +316,7 @@ use DBI::DBD; my $dbi_dir = dbd_dbi_dir(); my $dbi_arch_dir = dbd_dbi_arch_dir(); - (my $ora_arch_dir = $dbi_arch_dir) =~ s!DBI$!DBD/Oracle!; - }."\n" if $. == 1; + my $ora_arch_dir = }."'$ora_arch_dir';\n\n" if $. == 1; s!^\);! CONFIGURE => \\&wmf_config,\n);!; print $new_mk $_; } @@ -323,7 +345,7 @@ } print "Building...\n"; -system("perl Makefile.PL && make && make test"); +system("$perlpath Makefile.PL && make && make test") && exit 1
RE: DBD-Oracle - obtaining OCI handles from $dbh
Tim, I have looked at the Oracle::OCI module. I really like the concept, and it would undeniably be an extremely useful and powerful tool. However, I couldn't get it to work (by that I mean...I couldn't get it to build). As I understand Oracle::OCI, rather than provide a handcrafted XS? interface to all the OCI C functions, the Oracle::OCI module attempts to parse the OCI C header files and then auto-build the XS glue routines. This is a laudable approach, as there are a significant number of OCI functions to inteface with. The difficulty I ran into was that I could not get the parsing of the OCI header files to work properly. I tried the altered h2xs program that came with Oracle::OCI, but I failed in getting that to work with my Oracle installation. I also tried looking into a couple other approaches to parsing the OCI header files and autogenerating the XS routines, but again...my results were not fruitful. The only other comment I have on that approach is that the? interface comes out looking very C-like...meaning, the Perl interface looks almost like the OCI C interface, which is not too surprising. I found myself wishing that Oracle::OCI would look more Perl-ish. Of course, I understand the? argument that by mirroring the OCI C interface, then the Perl interface is consistent with the OCI C documentation and hence there would not be a need to learn "two" OCI interfaces. My work uses a good deal of the Oracle AQ functionality, and so I've found myself repeated wishing that I could find a? way to Oracle::OCI work...but it has left me mostly frustrated. Lyle Quoting Tim Bunce (tim.bu...@pobox.com): > I'm late to this thread (it went into a different mail folder). > Lyle, have you see the Oracle::OCI module? > It provides deep integration between Oracle::OCI and DBD::Oracle. > > To make that possible I added a get_oci_handle function pointer > to DBD::Oracle's handles. It lets you ask for any kind of OCI handle > from any kind of DBI handle (drh, dbh, sth). > > Oracle::OCI calls it like this: > > void * > get_oci_handle(SV *h, int handle_type, int flags) { > STRLEN lna; > typedef void *(*hook_type)_((imp_xxh_t *imp_xxh, int handle_type, int > flags)); > hook_type hook; > /* D_imp_xxh(h); */ > imp_xxh_t *imp_xxh; > if (flags & 1) > warn("get_oci_handle(%s,%d,%d)", SvPV(h,lna), handle_type, > flags); > imp_xxh = (imp_xxh_t*)(DBIh_COM(h)); > if (DBIc_TYPE(imp_xxh) == DBIt_ST) > hook = (hook_type)((imp_sth_t*)imp_xxh)->get_oci_handle; > else if (DBIc_TYPE(imp_xxh) == DBIt_DB) > hook = (hook_type)((imp_dbh_t*)imp_xxh)->get_oci_handle; > else croak("Can't get oci handle type %d from %s. Unsupported DBI > handle type", > handle_type, SvPV(h,lna)); > return hook(imp_xxh, handle_type, flags); > } > > Then calls it like this: > > get_oci_handle(arg, OCI_HTYPE_ERROR, 0); > get_oci_handle(arg, OCI_HTYPE_SVCCTX, 0); > get_oci_handle(arg, OCI_HTYPE_ENV, 0); > get_oci_handle(arg, OCI_HTYPE_SERVER, 0); > get_oci_handle(arg, OCI_HTYPE_SESSION, 0); > get_oci_handle(arg, OCI_HTYPE_STMT, 0); > > I'd have no objection to adding a perl method to DBD::Oracle to access > the get_oci_handle function. Perhaps ora_get_oci_handle($handle_type). > > Tim [still sad that Oracle::OCI doesn't get more love] > > > On Thu, Oct 27, 2011 at 02:39:15PM -0400, Lyle Brooks wrote: > > Ok, so with the following addition to Oracle.pm > > > > DBD::Oracle::db->install_method("ora_oci_handles"); > > > > my little test program "worked". By "worked", I mean I did > > a connect to the database, then did > > > > my @h = $dbh->ora_oci_handles(); > > > > and it returned 4 integers (ie. the value of the pointers), which > > is what I expected/wanted. > > > > I haven't yet tested that I can now pass these pointer values to > > the C++ libraries and have them digest it properly...but that would > > be next. > > > > As for how much anyone else might find use for thisprobably not > > a wide audience. But it is a nice hack! > > > > Thanks for the pointers. > > > > Quoting John Scoles (byter...@hotmail.com): > > > > > > > > > > Date: Thu, 27 Oct 2011 14:14:03 -0400 > > > > From: bro...@deseret.com > > > > To: martin.ev...@easysoft.com > > > > CC: dbi-users@perl.org > > > > Subject: Re: DBD-Oracle - obtaining OCI handles from $dbh > > > > > > > > > > > > Thanks for those pointers. > > > > > > > > I do agree with what Martin points out. My Perl script using DBI > > > > and some XS bindings to the legacy C++ libraries would share the > > > > same address space (not using threads in my application). This > > > > is why I thought I could return the handles/pointers as scalars. > > > > > > > > Taking John's suggestions, here is a quick code hack that I made > > > > to Oracle.xs (I haven't tested this ...other than it compiles). > > > > "Looks lik
Re: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh
I'm late to this thread (it went into a different mail folder). Lyle, have you see the Oracle::OCI module? It provides deep integration between Oracle::OCI and DBD::Oracle. To make that possible I added a get_oci_handle function pointer to DBD::Oracle's handles. It lets you ask for any kind of OCI handle from any kind of DBI handle (drh, dbh, sth). Oracle::OCI calls it like this: void * get_oci_handle(SV *h, int handle_type, int flags) { STRLEN lna; typedef void *(*hook_type)_((imp_xxh_t *imp_xxh, int handle_type, int flags)); hook_type hook; /* D_imp_xxh(h); */ imp_xxh_t *imp_xxh; if (flags & 1) warn("get_oci_handle(%s,%d,%d)", SvPV(h,lna), handle_type, flags); imp_xxh = (imp_xxh_t*)(DBIh_COM(h)); if (DBIc_TYPE(imp_xxh) == DBIt_ST) hook = (hook_type)((imp_sth_t*)imp_xxh)->get_oci_handle; else if (DBIc_TYPE(imp_xxh) == DBIt_DB) hook = (hook_type)((imp_dbh_t*)imp_xxh)->get_oci_handle; else croak("Can't get oci handle type %d from %s. Unsupported DBI handle type", handle_type, SvPV(h,lna)); return hook(imp_xxh, handle_type, flags); } Then calls it like this: get_oci_handle(arg, OCI_HTYPE_ERROR, 0); get_oci_handle(arg, OCI_HTYPE_SVCCTX, 0); get_oci_handle(arg, OCI_HTYPE_ENV, 0); get_oci_handle(arg, OCI_HTYPE_SERVER, 0); get_oci_handle(arg, OCI_HTYPE_SESSION, 0); get_oci_handle(arg, OCI_HTYPE_STMT, 0); I'd have no objection to adding a perl method to DBD::Oracle to access the get_oci_handle function. Perhaps ora_get_oci_handle($handle_type). Tim [still sad that Oracle::OCI doesn't get more love] On Thu, Oct 27, 2011 at 02:39:15PM -0400, Lyle Brooks wrote: > Ok, so with the following addition to Oracle.pm > > DBD::Oracle::db->install_method("ora_oci_handles"); > > my little test program "worked". By "worked", I mean I did > a connect to the database, then did > > my @h = $dbh->ora_oci_handles(); > > and it returned 4 integers (ie. the value of the pointers), which > is what I expected/wanted. > > I haven't yet tested that I can now pass these pointer values to > the C++ libraries and have them digest it properly...but that would > be next. > > As for how much anyone else might find use for thisprobably not > a wide audience. But it is a nice hack! > > Thanks for the pointers. > > Quoting John Scoles (byter...@hotmail.com): > > > > > > > Date: Thu, 27 Oct 2011 14:14:03 -0400 > > > From: bro...@deseret.com > > > To: martin.ev...@easysoft.com > > > CC: dbi-users@perl.org > > > Subject: Re: DBD-Oracle - obtaining OCI handles from $dbh > > > > > > > > > Thanks for those pointers. > > > > > > I do agree with what Martin points out. My Perl script using DBI > > > and some XS bindings to the legacy C++ libraries would share the > > > same address space (not using threads in my application). This > > > is why I thought I could return the handles/pointers as scalars. > > > > > > Taking John's suggestions, here is a quick code hack that I made > > > to Oracle.xs (I haven't tested this ...other than it compiles). > > > "Looks like it should work." ;-) > > > > > > void > > > ora_oci_handles(dbh) > > > SV *dbh > > > PREINIT: > > > D_imp_dbh(dbh); > > > PPCODE: > > > > > > /* Verify what is passed in is a $dbh object */ > > > if ( ! sv_derived_from(ST(0), "DBI::db")) { > > > Perl_croak(aTHX_ "dbh is not of type DBI::db"); > > > } > > > > > > mXPUSHi( (IV) imp_dbh->envhp ); /* Environment handle */ > > > mXPUSHi( (IV) imp_dbh->svchp ); /* Service Context handle */ > > > mXPUSHi( (IV) imp_dbh->srvhp ); /* Server handle */ > > > mXPUSHi( (IV) imp_dbh->authp ); /* Session handle */ > > > > > > XSRETURN(4); > > > > > > > > > Then my idea is to use this in Perl space... > > > > > > my($envhp, $svchp, $srvhp, $authp) = $dbh->ora_oci_handles(); > > > > > > > > > # Now share the OCI handles from DBI with the custom > > > # C++ libraries. > > > > > > my $cpp_dbh = MyCppOracleClass->new(); # creates custom C++ object > > > > > > $cpp_dbh->envhp($envhp); > > > $cpp_dbh->svchp($svchp); > > > $cpp_dbh->srvhp($srvhp); > > > $cpp_dbh->authp($authp); > > > > > > # Do something interesting with the C++ object > > > > > > $cpp_dbh->make_legacy_call_to_db(); > > > > > > > > > > > > > Yup that should work I didn't put two and two together and figure you > > already had XS for the C++. > > > > It could be something we could add to DBD::Oracle but I would return all > > of the handles not just the few you need. > > > > I wonder how much it would be used though?? > > > > > > Cheers > > > > > > > > > > > Quoting Martin J. Evans (martin.ev...@easysoft.com): > > > > On 27/10/2011 17:43, John Scoles wrote: > > > > >Hmm!! > > > > > > > > > >Well yes could be done but not as part of any release of DBD::Oracle > > > > >it > > > > >would have to be you own hacked version > > > > > > > > > Why is that John? Wh