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 ([email protected]):
> 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 this....probably not
> > a wide audience.  But it is a nice hack!
> > 
> > Thanks for the pointers.
> > 
> > Quoting John Scoles ([email protected]):
> > > 
> > > 
> > > > Date: Thu, 27 Oct 2011 14:14:03 -0400
> > > > From: [email protected]
> > > > To: [email protected]
> > > > CC: [email protected]
> > > > 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 ([email protected]):
> > > > > 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? What is the problem with returning a C pointer via a 
> > > > > DBD::Oracle attribute? It is just a pointer to some memory and loads of 
> > > > > XS modules do this. There is an Oracle OCI module I played with for a 
> > > > > short time but it is problematic to build. I looked at it as I could 
> > > > > implement OCI calls separately from DBD::Oracle. I don't seem the harm 
> > > > > in exposing OCI handles via DBD::Oracle - it would be useful for people 
> > > > > like the OP.
> > > > > 
> > > > > >A few pointers to start.
> > > > > >
> > > > > >You will not be able to 'get' a handle and retrun it as a Scalar it will 
> > > > > >only ever be a pointer so you will just get some sort of number,
> > > > > >
> > > > > >You would simly edit the Oracle.xs file
> > > > > >
> > > > > >add in the includes to your C++ .h files
> > > > > >
> > > > > >then add a few extra
> > > > > >
> > > > > >'ora_'
> > > > > >
> > > > > >functions to take care of you C++ calls;
> > > > > >
> > > > > >A quick example
> > > > > >
> > > > > >void
> > > > > >ora_some_c_call(dbh)
> > > > > > SV *dbh
> > > > > > PREINIT:
> > > > > > D_imp_dbh(dbh); //this gets all the OCI handles for you (see dbdimp.h 
> > > > > > for the sturct imp_dbh_st)
> > > > > > CODE:
> > > > > > MYSomeC_Plus_Plus_method(dbh->envhp,dbh->svchp,dbh->seshp,dbh->srvhp);
> > > > > >
> > > > > >
> > > > > He does not need to do this surely. So long as the C++ code and 
> > > > > DBD::Oracle XS is running in the same process the pointers obtained from 
> > > > > DBD::Oracle are just as valid in the C++ code as XS. However, if the 
> > > > > code is multi-threaded there could be issues of multiple threads 
> > > > > accessing the OCI handles at the same time.
> > > > > 
> > > > > >
> > > > > >Myself I would write a small 'c' wrapper that would call you c++ and just 
> > > > > >a single .XS function that calls that small 'c' wrapper to fire your 
> > > > > >function.
> > > > > >
> > > > > My impression was that this was already done.
> > > > > I quote:
> > > > > 
> > > > > "I have created some Perl bindings for some existing custom C++ libraries."
> > > > > 
> > > > > Martin
> > > > > -- 
> > > > > 
> > > > > Martin J. Evans
> > > > > Easysoft Limited
> > > > > http://www.easysoft.com
> > > > > 
> > > > > 
> > > > > >Hope this helps
> > > > > >
> > > > > >Cheers
> > > > > >John
> > > > > >
> > > > > >
> > > > > >>Date: Thu, 27 Oct 2011 09:48:54 -0400
> > > > > >>From: [email protected]
> > > > > >>To: [email protected]
> > > > > >>CC: [email protected]
> > > > > >>Subject: Re: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh
> > > > > >>
> > > > > >>Yes, I assumed I would need to extend DBD::Oracle is some manner
> > > > > >>to allow those handles to be extracted from a $dbh object.
> > > > > >>
> > > > > >>The specific OCI handles that the C++ libraries use are
> > > > > >>
> > > > > >>- Environment handle
> > > > > >>- Service Context handle
> > > > > >>- Session handle
> > > > > >>- Server handle
> > > > > >>
> > > > > >>My initial thought process on how it might work is this
> > > > > >>
> > > > > >>Create a method in the DBD::Oracle XS code to retrieve those
> > > > > >>handles and return them back to Perl space as a scalar.
> > > > > >>
> > > > > >>Then with the Perl scalars that hold the OCI handles obtained from
> > > > > >>$dbh, pass those values to my Perl bindings to the custom C++ libraries.
> > > > > >>(I would need to extend the C++ libraries to allow the set methods
> > > > > >>to the objects that hold those OCI handles).
> > > > > >>
> > > > > >>...then the C++ libraries should work the same.
> > > > > >>
> > > > > >>The motivation for this approach is that I have a large code base
> > > > > >>of these C++ libraries that have been tested, so it would cost
> > > > > >>prohibitive to simply replace them with a pure Perl implementation.
> > > > > >>
> > > > > >>However, it is so much easier to work with Perl and DBI, it would
> > > > > >>be useful to have DBI connect to the database and do some table
> > > > > >>lookups. The C++ code could then be integrated seemlessly with my
> > > > > >>Perl code. As time allows, I would gradually peel away functionality
> > > > > >>from the legacy C++ libraries and implement it in Perl. But to
> > > > > >>ease the migration path, this approach seemed to have some merits.
> > > > > >>
> > > > > >>
> > > > > >>Quoting John Scoles ([email protected]):
> > > > > >>>
> > > > > >>>
> > > > > >>>
> > > > > >>>>Date: Wed, 26 Oct 2011 21:46:30 -0400
> > > > > >>>>From: [email protected]
> > > > > >>>>To: [email protected]
> > > > > >>>>Subject: DBD-Oracle - obtaining OCI handles from $dbh
> > > > > >>>>
> > > > > >>>>I have created some Perl bindings for some existing custom C++
> > > > > >>>>libraries.
> > > > > >>>>
> > > > > >>>>One of these C++ libraries implements a class that uses Oracle
> > > > > >>>>OCI calls.
> > > > > >>>>
> > > > > >>>>I would like to create a connection to the Oracle database
> > > > > >>>>using Perl's DBI (DBD::Oracle) module, and then ideally share
> > > > > >>>>that connection with the C++ libraries.
> > > > > >>>>
> > > > > >>>>This would require me to extract the Oracle OCI handles from
> > > > > >>>>the $dbh object...and then pass them to the C++ libraries.
> > > > > >>>>
> > > > > >>>>What would be the best way to get access to the underlying
> > > > > >>>>Oracle OCI handles from a $dbh object?
> > > > > >>>Hmm! Interesting concept. Which OCI handles are we talking about??
> > > > > >>>Like Matin said you would have to do that with .XS and you would pass I 
> > > > > >>>guess a pointer to the
> > > > > >>>'C' struct that holds the pointers to DBD::Oraclles pointers?
> > > > > >>>
> > > > > >>>You would have to cast that struct into you C++ somehow??
> > > > > >>>
> > > > > >>>There would be a good number of handles to pass over. You might be able 
> > > > > >>>to find them by looking at the Perl process tree
> > > > > >>>and finding the SV that holds the struct and then sening that over?
> > > > > >>>
> > > > > >>>All just guesses on my part.
> > > > > >>>
> > > > > >>>Interesting concept non the less.
> > > > > >>>
> > > > > >>>Cheers
> > > > > >>>John
> > > > > >>>
> > > > > > 
> > > > > 
> > >  		 	   		  
> >