Wonderfully helpful reply Jason. Many thanks.
I wrote JDBC as an experiment. I don't have time to maintain it myself,
sadly, but I'd be very happy for others to either contribute or take
over the module.
Tim.
On Tue, Sep 29, 2009 at 01:52:25PM -0400, Jason Stelzer wrote:
> I never heard of the JDBC module before now, but having looked at the
> code, it makes a couple of naive assumptions. Fortunately they're
> pretty easily fixed and not a big deal. To be clear, given how new
> JDBC is, it doesn't shock me that there are a couple minor bugs around
> some drivers.
>
> At its core, the problem is type casting. Oracle's drivers are....
> annoying to deal with here. The problem you're experiencing is due to
> the fact that you're getting back the wrong interface types from the
> driver. Without patching JDBC.pm you can get the script below to work
> like this:
>
> #!/usr/bin/perl
> use JDBC;
> use Inline::Java qw(cast);
> use strict;
> JDBC->load_driver("oracle.jdbc.driver.OracleDriver");
> my $url = "jdbc:oracle:thin:system/passwo...@localhost:1521:xe";
> my $con = cast('java.sql.Connection',JDBC->getConnection($url));
> my $sql = "SELECT TABLE_NAME FROM USER_TABLES";
> my $ps = $con->prepareStatement($sql);
> my $rs = cast('java.sql.ResultSet',$ps->executeQuery());
> # Fake out a row num, select it if you really care about it.
> my $foo = 0;
> while ($rs->next) {
> my $bar = $rs->getString(1);
> print "row: foo=$foo, bar=$bar\n";
> $foo++;
> }
>
>
> One of the problems that the JDBC module is going to face is that
> there are a lot of buggy JDBC drivers out there that do similar things
> as to what is going on here with oracle. One strategy would be to
> create some perl facade objects to wrap the java objects and enforce
> the api by wrapping the calls to the internal java object and cast
> under the hood. I've done some tricks with autoloader so that I've
> only had to wrap methods that need wrapped.
>
> For instance, if one were to take the JDBC java api and hash method
> names to expected return types, you could put a jdbc object within a
> perl object. $obj = PACKAGE->new($java_ref);
>
> Then, take the $method => returnType above and handle it via
> AUTOLOAD.... something like
>
> sub AUTOLOAD {
> my $self = shift;
> return if $AUTOLOAD =~ /::DESTROY$/;
> my ($meth) = $AUTOLOAD =~ /::(\w+)$/;
> $meth = "SUPER::$meth";
> return $self->castByInternalMap($self->$meth);
> }
>
> Obviously this solution isn't perfect as it doesn't handle arguments,
> but that is trivial. What I'm getting at is, in order for JDBC.pm to
> really shine, it's going to need to do a little more work to enforce
> the rules of the API, but that work isn't that far of a reach. As
> we've seen, there are vendors out there that rely on some magic in
> order to work.
>
> If you were to go that far, it'd be a pretty cool trick to wrap some
> tied arrays and hashes around some things (like result sets) and fake
> out the (old) DBI interface. I did some stuff like this with ejbs. Its
> very convenient to be able to do things in perl like:
>
> my $ar = $ejb->getBigListOfStuff();
> foreach my $rec (@$ar){
> .....
> }
>
> And let the tied array handle the whole calling next, getting an iterator,
> etc.
>
> Anyways, I hope that helps.
>
>
> On Tue, Sep 29, 2009 at 12:44 PM, <j...@joedog.org> wrote:
> > Hi. I posted this question to the author of JDBC and he proposed that I
> > post it here.
> >
> > I'm trying to execute the following code:
> >
> > #!/usr/bin/perl
> > use JDBC;
> > use strict;
> > JDBC->load_driver("oracle.jdbc.driver.OracleDriver");
> > my $url = "jdbc:oracle:thin:haha/pa...@server.haha.com:1521:lsd1";
> > my $con = JDBC->getConnection($url);
> > my $sql = "SELECT TABLE_NAME FROM USER_TABLES";
> > my $ps = $con->prepareStatement($sql);
> > my $rs = $con->executeQuery();
> >
> > while ($rs->next) {
> > my $foo = $rs->getInt(1);
> > my $bar = $rs->getString(2);
> > print "row: foo=$foo, bar=$bar\n";
> > }
> >
> > And I get the following error:
> >
> > lsnas003 # perl ./haha
> > You are not allowed to invoke method prepareStatement in class
> > oracle.jdbc.driver.T4CConnection: Class InlineJavaUserClassLink can not
> > access a member of class oracle.jdbc.driver.PhysicalConnection with
> > modifiers "public synchronized" at (eval 56) line 927
> > at ./haha line 12
> >
> > Any thoughts?
> >
> > TIA,
> > Jeff
> >
> >
> >
> >
