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#7e35ac52f3741bc9 http://groups.google.com/group/perl.dbi.users/browse_thread/thread/317f2d0479216 81c/7a1f0649bd0f07c2?q=DBD%3A%3AProxy&rnum=3#7a1f0649bd0f07c2 http://groups.google.com/group/perl.dbi.users/browse_thread/thread/563005dd18eb7 47c/c58af527694e1535?q=DBD%3A%3AProxy&rnum=17#c58af527694e1535 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" <[EMAIL PROTECTED]> 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 | [EMAIL PROTECTED] > Chief Consultant | http://www.yuikee.com.hk/ > Yui Kee Computing Ltd. | +852 28708555 > -------------------------------------------------------------------- Allan Dyer, CISSP, MHKCS, MIAP | [EMAIL PROTECTED] Chief Consultant | http://www.yuikee.com.hk/ Yui Kee Computing Ltd. | +852 28708555
