Re: [PATCH] DBD::Proxy to handle tables_info
> I think, you should better describe what the actual problem is?
When using DBD::Proxy without my patch and dbish to test I get the
following error message when requesting a list of tables.
DBI::Shell: table_info failed: DBD::Proxy::st execute failed: Server returned error:
Failed to execute method CallMethod: DBD::ODBC::db prepare failed:
[Microsoft][ODBC Microsoft Access Driver]
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'.
(SQL-37000)(DBD: st_prepare/SQLPrepare err=-1) at
t:\devstudio\myprojects\perl\dbi-1.14\lib/DBI/ProxyServer.pm line 256.
What I traced down was the statement handle being created with "SHOW
TABLES" as the SQL.
> The "SHOW TABLES" thing is just, because the DBI specs require to
> return a statement handle, which in turn requires a "Statement"
> attribute. We could choose whatever else for "SHOW TABLES", it's
> merely a placeholder.
That's later prepared, again, as a statement a new statement handle.
sub execute
undef $sth->{'proxy_data'}; The data returned from the table_info is gone.
...
my $rsth = $sth->{proxy_sth}; This is undefined.
...
if (!$rsth) {
...
($rsth, $numFields, $numParams, $names, $types, $numRows, @outParams) =
eval { $rdbh->prepare($sth->{'Statement'},
$sth->{'proxy_attr'}, $params) };
return DBI::set_err($sth, 1, $@) if $@;
Here the SQL statement "SHOW TABLES" is prepared against the remote server, it
fails returning an error message.
Does this better describe the problem?
Tom
[t:\devstudio\myprojects\perl\proxy]set DBI_AUTOPROXY=hostname=localhost;port=1234
[t:\devstudio\myprojects\perl\proxy]dbish
DBI::Shell 10.7 using DBI 1.14
WARNING: The DBI::Shell interface and functionality are
=== very likely to change in subsequent versions!
Use of uninitialized value in length at T:/Perl-5.6/site/lib/DBI/Shell.pm line 734.
Connecting to 'dbi:ODBC:photo' as ''...
@dbi:ODBC:photo> /tab
DBI::Shell: table_info failed: DBD::Proxy::st execute failed: Server returned error:
Failed to execute method CallMethod: DBD::ODBC:
:db prepare failed: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
:expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (SQL-37000)(DBD:
:st_prepare/SQLPrepare err=-1) at
:t:\devstudio\myprojects\perl\dbi-1.14\lib/DBI/ProxyServer.pm lin
[t:\devstudio\myprojects\perl\dbi-1.14]dbish
Argument "1.1.1.1 " isn't numeric in addition (+) at lib/DBI/Format.pm line 26.
DBI::Shell 10.7 using DBI 1.14
WARNING: The DBI::Shell interface and functionality are
=== very likely to change in subsequent versions!
Connecting to 'dbi:ODBC:photo' as ''...
@dbi:ODBC:photo> /ta
TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'MSysAccessObjects','SYSTEM
TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'MSysACEs','SYSTEM
TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'MSysObjects','SYSTEM
TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'MSysQueries','SYSTEM
TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'MSysRelationships','SYSTEM
TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'bb','TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'bbb','TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'bt','TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'myt','TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'UserID','TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'xx','TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'yy','TABLE',undef
'T:\DevStudio\MyProjects\Perl\DBD-ADO\database\photos',undef,'myv','VIEW',undef
[13 rows of 5 fields returned]
@dbi:ODBC:photo>
On Thu, Mar 15, 2001 at 11:13:43PM +0100, Wiedmann, Jochen wrote:
> sorry for replying so late, but my time for Perl development
> gets fewer and fewer.
I understand that.
> > I've included a patch to fix the a problem using table_info
> > and DBD::Proxy. Currently the DBD::Proxy attempts to execute a
> > statement called "SHOW TABLES".
>
> Sorry, but that's not the case. The table_info method of
> DBD::Proxy does indeed execute a table_info method on the server
> side:
>
> my($numFields, $names, $types, @rows) = eval { $rdbh->table_info(@_) };
--
Thomas A. Lowery[EMAIL PROTECTED]
http://tlowery.hypermart.net
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
RE: [PATCH] DBD::Proxy to handle tables_info
Hi, Tom,
sorry for replying so late, but my time for Perl development
gets fewer and fewer.
> I've included a patch to fix the a problem using table_info
> and DBD::Proxy. Currently the DBD::Proxy attempts to execute a
> statement called "SHOW TABLES".
Sorry, but that's not the case. The table_info method of
DBD::Proxy does indeed execute a table_info method on the server
side:
my($numFields, $names, $types, @rows) = eval { $rdbh->table_info(@_) };
However, it continues:
my $sth = DBI::_new_sth($dbh, {
'Statement' => "SHOW TABLES",
'proxy_params' => [],
'proxy_data' => \@rows,
'proxy_attr_cache' => { 'NAME' => $names, 'TYPE' => $types }
});
$sth->SUPER::STORE('NUM_OF_FIELDS' => $numFields);
return $sth;
The "SHOW TABLES" thing is just, because the DBI specs require to
return a statement handle, which in turn requires a "Statement"
attribute. We could choose whatever else for "SHOW TABLES", it's
merely a placeholder.
Reading through your patch, I honestly don't like it. I see no sense
in caching the table_info method. First of all, it is typically used
in interactive applications, where another 0.5 seconds don't hurt.
Second, it does not consider that the cache becomes invalid with
any CREATE TABLE or DROP TABLE statement. I am not the DBI main-
tainer, but I'd vote against, if Tim cares.
I think, you should better describe what the actual problem is?
Sorry,
Jochen
[PATCH] DBD::Proxy to handle tables_info
Jochen,
I've included a patch to fix the a problem using table_info
and DBD::Proxy. Currently the DBD::Proxy attempts to execute a
statement called "SHOW TABLES". I've included a patch that
modifies the execute and fetch methods to use cached data only.
I've used (mis-used) the proxy_cache_only to mark the data as "to
read cache only". I mark the statement as INACTIVE when all the
cached data is read (and proxy_cache_only is true).
Tom
*** Proxy.pm.orig Wed Feb 09 00:29:14 2000
--- Proxy.pmThu Mar 01 23:35:56 2001
***
*** 268,274
my $sth = DBI::_new_sth($dbh, {
'Statement' => $stmt,
'proxy_attr' => $attr,
! 'proxy_params' => []
});
$sth;
}
--- 268,275
my $sth = DBI::_new_sth($dbh, {
'Statement' => $stmt,
'proxy_attr' => $attr,
! 'proxy_params' => [],
! 'proxy_cache_only' => 0,
});
$sth;
}
***
*** 307,313
'Statement' => "SHOW TABLES",
'proxy_params' => [],
'proxy_data' => \@rows,
! 'proxy_attr_cache' => { 'NAME' => $names, 'TYPE' => $types }
});
$sth->SUPER::STORE('NUM_OF_FIELDS' => $numFields);
return $sth;
--- 308,320
'Statement' => "SHOW TABLES",
'proxy_params' => [],
'proxy_data' => \@rows,
! 'proxy_attr_cache' => {
! 'NUM_OF_PARAMS' => 0,
! 'NUM_OF_FIELDS' => $numFields,
! 'NAME' => $names,
! 'TYPE' => $types
! },
! 'proxy_cache_only' => 1,
});
$sth->SUPER::STORE('NUM_OF_FIELDS' => $numFields);
return $sth;
***
*** 351,357
my $sth = shift;
my $params = @_ ? \@_ : $sth->{'proxy_params'};
! undef $sth->{'proxy_data'};
my $dbh = $sth->{'Database'};
my $client = $dbh->{'proxy_client'};
--- 358,365
my $sth = shift;
my $params = @_ ? \@_ : $sth->{'proxy_params'};
! #
! # undef $sth->{'proxy_data'};
my $dbh = $sth->{'Database'};
my $client = $dbh->{'proxy_client'};
***
*** 359,364
--- 367,381
my ($numFields, $numParams, $numRows, $names, $types, @outParams);
+ if ($sth->{'proxy_data'}) {
+ my $attrCache = $sth->{'proxy_attr_cache'};
+ $numFields = $attrCache->{'NUM_OF_FIELDS'};
+ $numParams = $attrCache->{'NUM_OF_PARAMS'};
+ $names = $attrCache->{'NAME'};
+ $types = $attrCache->{'TYPE'};
+ $numRows = scalar @{$sth->{'proxy_data'}};
+ } else {
+
if (!$rsth) {
my $rdbh = $dbh->{'proxy_dbh'};
***
*** 384,395
--- 401,414
($numRows, @outParams) = eval { $rsth->execute($params) };
return DBI::set_err($sth, 1, $@) if $@;
}
+ }
$sth->{'proxy_rows'} = $numRows;
$sth->{'proxy_attr_cache'} = {
'NUM_OF_FIELDS' => $numFields,
'NUM_OF_PARAMS' => $numParams,
'NAME' => $names
};
+
$sth->SUPER::STORE('Active' => 1) if $numFields; # is SELECT
if (@outParams) {
***
*** 428,433
--- 447,454
$sth->{'proxy_data'} = $data = [@rows];
}
my $row = shift @$data;
+
+ $sth->SUPER::STORE(Active => 0) if ( $sth->{proxy_cache_only} and !@$data );
return $sth->_set_fbav($row);
}
*fetchrow_arrayref = \&fetch;
--
Thomas A. Lowery[EMAIL PROTECTED]
http://tlowery.hypermart.net
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
