problem using table_info and column_info with DBD::Proxy

I think I've found a problem with DBD::Proxy - table_info and column_info do
not work.

m using DBI 1.50, perl 5.8.6, mysql 4.1.7, Linux kernel 2.6.15
I'm trying to access a mysql database on one machine from a webserver on
another machine. I was able to do simple SELECT queries, but I ran into
difficulties when I tried to retrieve information about the tables and columns
using table_info and column_info. My Google searches found some reports of
difficulties using other databases, but nothing with a solution. So I wrote
some test scripts to isolate the problem:

For the purposes of the tests, I used a single machine, therefore the client
and server versions were the same, and network problems were eliminated.

Example 1
---------------
#!/usr/bin/perl
#
# Testing problem with table_info

use strict;
use warnings;
use Data::Dumper;
use DBI;

#my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
my $dbn= 'DBI:mysql:ima';
my $charset= 'UTF-8';
my $tds = DBI->connect( $dbn, 'root', '',
{ RaiseError => 1, PrintError => 0 } );

my $sth= $tds->table_info( );
unless ($sth) {
print STDERR "table_info error: ", $tds->errstr, "\n";
exit;
}
print STDERR "table_info OK\n";
print STDERR Dumper($sth);
my $tables = $sth->fetchall_arrayref();
print STDERR "Fetched\n";
print STDERR Dumper($tables);
exit
----------------------------------------------
output:
table_info OK
$VAR1 = bless( {}, 'DBI::st' );
Fetched
$VAR1 = [
[
undef,
undef,
'BaseKit',
'TABLE',
undef
],
.... etc
----------------------------------------------
i.e. It works with a direct database connection. However, if I change $dbn to
point to the same database via Proxy:
my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
the output is:
DBD::Proxy::db table_info failed: Server returned error: Failed to execute
method CallMethod: Can't call method "execute" without a package or object
reference at /usr/local/lib/perl5/site_perl/5.8.6/i686-linux/DBD/mysql.pm line
251.
----------------------------------------------
The program crashes at $tds->table_info( ), and an error is returned from
mysql.pm on the server.

Similarly, for column_info:

Example2
--------------
#!/usr/bin/perl
#
# Testing problem with LISTFIELDS and column_info

use strict;
use warnings;
use Data::Dumper;
use DBI;

#my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
my $dbn= 'DBI:mysql:ima';
my $charset= 'UTF-8';
my $tds = DBI->connect( $dbn, 'root', '',
{ RaiseError => 1, PrintError => 0 } );

my $sth = $tds->column_info( undef, undef, 'Product', "%");
my $cnames= $sth->fetchall_hashref( "COLUMN_NAME" );
print STDERR Dumper($cnames), "\n";
exit
----------------------------------------------
output:
$VAR1 = {
'name' => {
'COLUMN_DEF' => undef,
'mysql_values' => undef,
'NUM_PREC_RADIX' => undef,
'COLLATION_CAT' => undef,
'TABLE_SCHEM' => undef,
'DOMAIN_NAME' => undef,
'COLLATION_NAME' => undef,
'REMARKS' => undef,
'mysql_type_name' => 'varchar(60)',
'COLUMN_SIZE' => '60',
.... etc
----------------------------------------------
again, it works with a direct database connection. However, if I change $dbn to
point to the same database via Proxy:
my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
the output is:
Can't call method "fetchall_hashref" on an undefined value at ./example2.pl
line 17.
----------------------------------------------
A crash does not occur, but $tds->column_info() does not return a statement
handle, and the fetch fails.

Are these known limitations of DBD::Proxy?
Am I doing something wrong?
Is there a workaround or alternative method?

Thanks in advance,
Allan Dyer


------------------------------------------------------------ --------
Allan Dyer, CISSP, MHKCS, MIAP | adyer@yuikee.com.hk
Chief Consultant | http://www.yuikee.com.hk/
Yui Kee Computing Ltd. | +852 28708555

Re: problem using table_info and column_info with DBD::Proxy

I'm sorry if I offended everyone with my previous message - I've got no
replies, so I guess I did something wrong. I would still like to be pointed in
a useful direction...

I've found that the problem with table_info has been reported three times
before in this group:

http://groups.google.com/group/perl.dbi.users/browse_thread/ thread/7f55f6e681510
fce/7e35ac52f3741bc9?q=DBD%3A%3AProxy&rnum=12#7e35ac52f3741b c9

http://groups.google.com/group/perl.dbi.users/browse_thread/ thread/317f2d0479216
81c/7a1f0649bd0f07c2?q=DBD%3A%3AProxy&rnum=3#7a1f0649bd0f07c 2

http://groups.google.com/group/perl.dbi.users/browse_thread/ thread/563005dd18eb7
47c/c58af527694e1535?q=DBD%3A%3AProxy&rnum=17#c58af527694e15 35

as far back as 2003, but no mention of a fix. This suggests people are doing
this in a different way...

How would you recommend connecting to a mysql database on a different machine?
DBD::Proxy appeared easy.

Thanks in advance
Allan Dyer

On 24 Mar 2006 at 20:20, Allan Dyer wrote:

From: "Allan Dyer"
Organization: Yui Kee Co. Ltd.
To: dbi-users@perl.org
Date sent: Fri, 24 Mar 2006 20:20:44 +0800
Subject: problem using table_info and column_info with DBD::Proxy
Priority: normal

> I think I've found a problem with DBD::Proxy - table_info and column_info do
> not work.
>
> m using DBI 1.50, perl 5.8.6, mysql 4.1.7, Linux kernel 2.6.15
> I'm trying to access a mysql database on one machine from a webserver on
> another machine. I was able to do simple SELECT queries, but I ran into
> difficulties when I tried to retrieve information about the tables and columns
> using table_info and column_info. My Google searches found some reports of
> difficulties using other databases, but nothing with a solution. So I wrote
> some test scripts to isolate the problem:
>
> For the purposes of the tests, I used a single machine, therefore the client
> and server versions were the same, and network problems were eliminated.
>
> Example 1
> ---------------
> #!/usr/bin/perl
> #
> # Testing problem with table_info
>
> use strict;
> use warnings;
> use Data::Dumper;
> use DBI;
>
> #my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
> my $dbn= 'DBI:mysql:ima';
> my $charset= 'UTF-8';
> my $tds = DBI->connect( $dbn, 'root', '',
> { RaiseError => 1, PrintError => 0 } );
>
> my $sth= $tds->table_info( );
> unless ($sth) {
> print STDERR "table_info error: ", $tds->errstr, "\n";
> exit;
> }
> print STDERR "table_info OK\n";
> print STDERR Dumper($sth);
> my $tables = $sth->fetchall_arrayref();
> print STDERR "Fetched\n";
> print STDERR Dumper($tables);
> exit
> ----------------------------------------------
> output:
> table_info OK
> $VAR1 = bless( {}, 'DBI::st' );
> Fetched
> $VAR1 = [
> [
> undef,
> undef,
> 'BaseKit',
> 'TABLE',
> undef
> ],
> ... etc
> ----------------------------------------------
> i.e. It works with a direct database connection. However, if I change $dbn to
> point to the same database via Proxy:
> my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
> the output is:
> DBD::Proxy::db table_info failed: Server returned error: Failed to execute
> method CallMethod: Can't call method "execute" without a package or object
> reference at /usr/local/lib/perl5/site_perl/5.8.6/i686-linux/DBD/mysql.pm line
> 251.
> ----------------------------------------------
> The program crashes at $tds->table_info( ), and an error is returned from
> mysql.pm on the server.
>
> Similarly, for column_info:
>
> Example2
> --------------
> #!/usr/bin/perl
> #
> # Testing problem with LISTFIELDS and column_info
>
> use strict;
> use warnings;
> use Data::Dumper;
> use DBI;
>
> #my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
> my $dbn= 'DBI:mysql:ima';
> my $charset= 'UTF-8';
> my $tds = DBI->connect( $dbn, 'root', '',
> { RaiseError => 1, PrintError => 0 } );
>
> my $sth = $tds->column_info( undef, undef, 'Product', "%");
> my $cnames= $sth->fetchall_hashref( "COLUMN_NAME" );
> print STDERR Dumper($cnames), "\n";
> exit
> ----------------------------------------------
> output:
> $VAR1 = {
> 'name' => {
> 'COLUMN_DEF' => undef,
> 'mysql_values' => undef,
> 'NUM_PREC_RADIX' => undef,
> 'COLLATION_CAT' => undef,
> 'TABLE_SCHEM' => undef,
> 'DOMAIN_NAME' => undef,
> 'COLLATION_NAME' => undef,
> 'REMARKS' => undef,
> 'mysql_type_name' => 'varchar(60)',
> 'COLUMN_SIZE' => '60',
> ... etc
> ----------------------------------------------
> again, it works with a direct database connection. However, if I change $dbn to
> point to the same database via Proxy:
> my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
> the output is:
> Can't call method "fetchall_hashref" on an undefined value at ./example2.pl
> line 17.
> ----------------------------------------------
> A crash does not occur, but $tds->column_info() does not return a statement
> handle, and the fetch fails.
>
> Are these known limitations of DBD::Proxy?
> Am I doing something wrong?
> Is there a workaround or alternative method?
>
> Thanks in advance,
> Allan Dyer
>
>
> ------------------------------------------------------------ --------
> Allan Dyer, CISSP, MHKCS, MIAP | adyer@yuikee.com.hk
> Chief Consultant | http://www.yuikee.com.hk/
> Yui Kee Computing Ltd. | +852 28708555
>



------------------------------------------------------------ --------
Allan Dyer, CISSP, MHKCS, MIAP | adyer@yuikee.com.hk
Chief Consultant | http://www.yuikee.com.hk/
Yui Kee Computing Ltd. | +852 28708555

Re: problem using table_info and column_info with DBD::Proxy

On Sat, 08 Apr 2006 20:39:45 +0800, Allan Dyer wrote:

Hi Allan

> I'm sorry if I offended everyone with my previous message - I've

I can't see why you'd think that :-).

> got no replies, so I guess I did something wrong. I would still

I can't see why you'd guess that :-).

> like to be pointed in a useful direction...
>
> I've found that the problem with table_info has been reported three
> times before in this group:

Here's how I call table_info(...). I have not used DBD::Proxy.

my(%vendor) =3D
(
=09MYSQL =3D>
=09{
attributes => {},
catalog =3D> undef,
column =3D> '%',
schema =3D> undef,
table =3D> '%',
type =3D> 'table',
=09},
=09ORACLE =3D>
=09{
attributes => {},
catalog =3D> undef,
column =3D> '%',
schema =3D> $user,
table =3D> '%',
type =3D> 'TABLE',
=09},
=09PG =3D>
=09{
attributes => {noprefix =3D> 1, pg_noprefix =3D> 1},=09# Only supported for
$DBD::Pg::VERSION >=3D 1.31.
catalog =3D> undef, # Prior to that, use $dbh -> tables()
column =3D> '%', =09# instead of $dbh -> table_info(...).
schema =3D> 'public',
table =3D> '%',
type =3D> 'table',
=09},
);
my($sth) = $dbh -> table_info($vendor{$vendor}{'catalog'},
$vendor{$vendor}{'schema'}, $vendor{$vendor}{'table'},
$vendor{$vendor}{'type'});
my($info) = $sth -> fetchall_arrayref({});

This is from a program to dump all table, column, and index info for the 3
tested vendors.

Let me know how it goes.
--
Cheers
Ron Savage, ron@savage.net.au on 9/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: problem using table_info and column_info with DBD::Proxy

Hi Ron

Thanks...

On 9 Apr 2006 at 12:40, Ron Savage wrote:
> On Sat, 08 Apr 2006 20:39:45 +0800, Allan Dyer wrote:
>
> Hi Allan
>
> > I'm sorry if I offended everyone with my previous message - I've
>
> I can't see why you'd think that :-).

:-)

> > got no replies, so I guess I did something wrong. I would still
>
> I can't see why you'd guess that :-).

:-)

> > like to be pointed in a useful direction...
> >
> > I've found that the problem with table_info has been reported three
> > times before in this group:
>
> Here's how I call table_info(...). I have not used DBD::Proxy.
....
> This is from a program to dump all table, column, and index info for the 3
> tested vendors.
>
> Let me know how it goes.

OK, I tried that and got basically the same result as my test program: if I
specify a direct connection to mysql, it works OK, but if I specify a proxy
connection to the same database, it fails (my full listing & sample output
below).

The error is from mysql, so it appears that what the proxy gives to mysql is
not the same as what I give to the proxy.

My listing: (added statements to set variables & connect to the database)
#!/usr/bin/perl
#
# Testing problem with table_info

use strict;
use warnings;
use Data::Dumper;
use DBI;

#my $dbn= 'DBI:Proxy:hostname=localhost;port=3333;dsn=DBI:mysql:ima';
my $dbn= 'DBI:mysql:ima';
my $charset= 'UTF-8';
my $dbh = DBI->connect( $dbn, 'root', '',
{ RaiseError => 1, PrintError => 0 } );

my $user= '';
my(%vendor) =
(
MYSQL =>
{
attributes => {},
catalog => undef,
column => '%',
schema => undef,
table => '%',
type => 'table',
},
ORACLE =>
{
attributes => {},
catalog => undef,
column => '%',
schema => $user,
table => '%',
type => 'TABLE',
},
PG =>
{
attributes => {noprefix => 1, pg_noprefix => 1}, # Only supported for
$DBD::Pg::VERSION >= 1.31.
catalog => undef, # Prior to that, use $dbh -> tables()
column => '%', # instead of $dbh -> table_info(...).
schema => 'public',
table => '%',
type => 'table',
},
);

my $vendor= 'MYSQL';

my($sth) = $dbh -> table_info($vendor{$vendor}{'catalog'},
$vendor{$vendor}{'schema'}, $vendor{$vendor}{'table'},
$vendor{$vendor}{'type'});
my($info) = $sth -> fetchall_arrayref({});

print Dumper( $info);

exit
------------------------------------------------
Output using direct connection: my $dbn= 'DBI:mysql:ima';
$VAR1 = [
{
'REMARKS' => undef,
'TABLE_NAME' => 'BaseKit',
'TABLE_CAT' => undef,
'TABLE_SCHEM' => undef,
'TABLE_TYPE' => 'TABLE'
},
{
'REMARKS' => undef,
'TABLE_NAME' => 'Manufacturer',
'TABLE_CAT' => undef,
'TABLE_SCHEM' => undef,
'TABLE_TYPE' => 'TABLE'
},
.... etc.
------------------------------------------------
Output using proxy connection:
DBD::Proxy::db table_info failed: Server returned error: Failed to execute
method CallMethod: Can't call method "execute" without a package or object
reference at /usr/lib/perl5/site_perl/5.8.8/i486-linux/DBD/mysql.pm line 262.
-------------------------------------------------

Who's maintaining DBD::Proxy? Perhaps I should contact them direct.

Cheers
Allan

> --
> Cheers
> Ron Savage, ron@savage.net.au on 9/04/2006
> http://savage.net.au/index.html
> Let the record show: Microsoft is not an Australian company
>
>



------------------------------------------------------------ --------
Allan Dyer, CISSP, MHKCS, MIAP | adyer@yuikee.com.hk
Chief Consultant | http://www.yuikee.com.hk/
Yui Kee Computing Ltd. | +852 28708555

Re: problem using table_info and column_info with DBD::Proxy

On Mon, 10 Apr 2006 15:14:21 +0800, Allan Dyer wrote:

Hi Allan

> OK, I tried that and got basically the same result as my test
> program: if I specify a direct connection to mysql, it works OK,
> but if I specify a proxy connection to the same database, it fails

PITA.

> Who's maintaining DBD::Proxy? Perhaps I should contact them direct.

Don't know, but that's probably your next best bet.
--
Cheers
Ron Savage, ron@savage.net.au on 10/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: problem using table_info and column_info with DBD::Proxy

On Mon, Apr 10, 2006 at 03:14:21PM +0800, Allan Dyer wrote:
> > >
> > > I've found that the problem with table_info has been reported three
> > > times before in this group:
> >
> > Here's how I call table_info(...). I have not used DBD::Proxy.

> Who's maintaining DBD::Proxy? Perhaps I should contact them direct.

I'd *love* someone to help maintain DBD::Proxy. Any volunteers?

Meanwhile, see this in Proxy.pm:

# XXX probably many more methods need to be added here.
# See notes in ToDo about method metadata
sub commit;
sub connected;
sub rollback;
sub ping;

try adding extra lines for any methods that seem unsupported by DBD::Proxy.

Please let me know if that helps.

Tim.

Re: problem using table_info and column_info with DBD::Proxy

On 18 Apr 2006 at 23:00, Tim Bunce wrote:
> On Mon, Apr 10, 2006 at 03:14:21PM +0800, Allan Dyer wrote:
> > > >
> > > > I've found that the problem with table_info has been reported three
> > > > times before in this group:
> > >
> > > Here's how I call table_info(...). I have not used DBD::Proxy.
>
> > Who's maintaining DBD::Proxy? Perhaps I should contact them direct.
>
> I'd *love* someone to help maintain DBD::Proxy. Any volunteers?

Sorry, I can't commit on this. I can help document this problem, and test any
solutions suggested.

> Meanwhile, see this in Proxy.pm:
>
> # XXX probably many more methods need to be added here.
> # See notes in ToDo about method metadata
> sub commit;
> sub connected;
> sub rollback;
> sub ping;
>
> try adding extra lines for any methods that seem unsupported by DBD::Proxy.

I didn't find any explanation of method metadata in ToDo, so I added:
sub table_info;
sub column_info;

> Please let me know if that helps.

For my column_info example, a change. I previously got:
Can't call method "fetchall_hashref" on an undefined value at ./example2.pl
line 17.
Now I get:
DBD::Proxy::db column_info failed: Server returned error: Failed to execute
method CallMethod: Can't call method "execute" without a package or object
reference at /usr/lib/perl5/site_perl/5.8.8/i486-linux/DBI.pm line 1584.

For my table_info example, no change, I still get:
DBD::Proxy::db table_info failed: Server returned error: Failed to execute
method CallMethod: Can't call method "execute" without a package or object
reference at /usr/lib/perl5/site_perl/5.8.8/i486-linux/DBD/mysql.pm line 262.

it seems that table_info in mysql.pm is not getting a valid statement handle,
which implies the database handle it has been given is invalid. I looked at
ProxyServer.pm and found this comment in table_info:

# We wouldn't need to send all the rows at this point, instead we could
# make use of $rsth->fetch() on the client as usual.
# The problem is that some drivers (namely DBD::ExampleP, DBD::mysql and
# DBD::mSQL) are returning foreign sth's here, thus an instance of
# DBI::st and not DBI::ProxyServer::st. We could fix this by permitting
# the client to execute method DBI::st, but I don't like this.

I'm wondering if this is related, but I'm finding it difficult to follow what's
happening. Did the behaviour of DBD::mysql change after DBD:Proxy was written,
perhaps?

Allan


------------------------------------------------------------ --------
Allan Dyer, CISSP, MHKCS, MIAP | adyer@yuikee.com.hk
Chief Consultant | http://www.yuikee.com.hk/
Yui Kee Computing Ltd. | +852 28708555

Re: problem using table_info and column_info with DBD::Proxy

At this point your guess is as good as mine as I've no time to dig deeper.
Sorry.

Tim.

On Wed, Apr 19, 2006 at 01:07:35PM +0800, Allan Dyer wrote:
> On 18 Apr 2006 at 23:00, Tim Bunce wrote:
> > On Mon, Apr 10, 2006 at 03:14:21PM +0800, Allan Dyer wrote:
> > > > >
> > > > > I've found that the problem with table_info has been reported three
> > > > > times before in this group:
> > > >
> > > > Here's how I call table_info(...). I have not used DBD::Proxy.
> >
> > > Who's maintaining DBD::Proxy? Perhaps I should contact them direct.
> >
> > I'd *love* someone to help maintain DBD::Proxy. Any volunteers?
>
> Sorry, I can't commit on this. I can help document this problem, and test any
> solutions suggested.
>
> > Meanwhile, see this in Proxy.pm:
> >
> > # XXX probably many more methods need to be added here.
> > # See notes in ToDo about method metadata
> > sub commit;
> > sub connected;
> > sub rollback;
> > sub ping;
> >
> > try adding extra lines for any methods that seem unsupported by DBD::Proxy.
>
> I didn't find any explanation of method metadata in ToDo, so I added:
> sub table_info;
> sub column_info;
>
> > Please let me know if that helps.
>
> For my column_info example, a change. I previously got:
> Can't call method "fetchall_hashref" on an undefined value at ./example2.pl
> line 17.
> Now I get:
> DBD::Proxy::db column_info failed: Server returned error: Failed to execute
> method CallMethod: Can't call method "execute" without a package or object
> reference at /usr/lib/perl5/site_perl/5.8.8/i486-linux/DBI.pm line 1584.
>
> For my table_info example, no change, I still get:
> DBD::Proxy::db table_info failed: Server returned error: Failed to execute
> method CallMethod: Can't call method "execute" without a package or object
> reference at /usr/lib/perl5/site_perl/5.8.8/i486-linux/DBD/mysql.pm line 262.
>
> it seems that table_info in mysql.pm is not getting a valid statement handle,
> which implies the database handle it has been given is invalid. I looked at
> ProxyServer.pm and found this comment in table_info:
>
> # We wouldn't need to send all the rows at this point, instead we could
> # make use of $rsth->fetch() on the client as usual.
> # The problem is that some drivers (namely DBD::ExampleP, DBD::mysql and
> # DBD::mSQL) are returning foreign sth's here, thus an instance of
> # DBI::st and not DBI::ProxyServer::st. We could fix this by permitting
> # the client to execute method DBI::st, but I don't like this.
>
> I'm wondering if this is related, but I'm finding it difficult to follow what's
> happening. Did the behaviour of DBD::mysql change after DBD:Proxy was written,
> perhaps?
>
> Allan
>
>
> ------------------------------------------------------------ --------
> Allan Dyer, CISSP, MHKCS, MIAP | adyer@yuikee.com.hk
> Chief Consultant | http://www.yuikee.com.hk/
> Yui Kee Computing Ltd. | +852 28708555
>