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 ([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
>>>>>>>>>
>>>>