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