Dear Monks,
My Setup:
I have got a Linux (RH7.2) with perl 5x, and I am connecting to an Oracle 8 database
which is on a Win 2k machine using DBIproxy (DBI + DBD::ODBC, want to stick to DBD::ODBC as it allows me code portability to a great extent).
Perl and the DBI and the other required modules are installed on the Win 2K too.
ODBC's been configured on the WIN 2K to allow me to connect to the Oracle DB.
Trouble:
Via DBIproxy I am able to execute commands like select, insert, update and delete, but I am not able to "describe
".
Here's where I need your help. Does anyone know why I am not able to execute the "describe table" command and what should I do to get it working by hook or crook :) (I haven't tried commands like create table or drop table etc. don't expect I'll need to do that.)
Below is a snippet of my code with the error that it returns
# http://www.awilcox.com/geek_stuff/perl/proxy.html
use DBI;
use Data::Dumper;
# declare variables
my ($dbh, $sth, $row);
$dbh= DBI->connect("DBI:Proxy:hostname=192.168.200.101;port=3522;debug
+=1;dsn=dbi:ODBC:dbtest",'test','test') or die $DBI::errstr;
$sth = $dbh->prepare("select f1,f2,f3 from table1 where f1=1");
$sth->execute;
Output:
$VAR1 = {
'f1' => '1',
'f2' => 'A123',
'f3' => 'Vista'
};
The code and output of the desc command:
$sth = $dbh->prepare("desc table1");
$sth->execute;
Output:
DBD::Proxy::st execute failed: Server returned error: Failed to execut
+e method CallMethod: DBD::ODBC::st execute failed: [Oracle][ODBC][Ora
+]ORA-00900: invalid SQL statement
(SQL-42000)(DBD: st_execute/SQLExecute err=-1) at C:/Perl/site/lib/DB
+I/ProxyServer.pm line 344.
Any help will be greatly appreciated.
(Sorry for the long post)
Oyster
|
Re: 'Describe table' doesn't work (Oracle/dbiproxy)
by perrin (Chancellor) on Jan 17, 2004 at 16:45 UTC
|
It's because "describe" is not a part of SQL. It is something that Oracle's sqlplus shell implements, not the server. There are other ways to get a list of columns in a table, if that's what you want. Check the DBI docs. | [reply] |
Re: 'Describe table' doesn't work (Oracle/dbiproxy)
by graff (Chancellor) on Jan 18, 2004 at 02:57 UTC
|
To expand a bit on perrin's reply, seek into the DBI docs for the section titled "Database Handle Methods", and probe down there quite a ways to find "table_info", "column_info" and the next few methods after that. If it seems kind of complicated, or if you're concerned by the opening statement in that part of the docs:
Warning: This method is experimental and may change.
don't worry -- there's a reasonable alternative using a query to oracle's own "data dictionary" tables. Here's a query that I've used to get something pretty similar to the output of "describe" -- just do your normal "prepare, execute, fetch_row" sequence using this query, and the results will be the list of columns and some of their attributes:
my $sql = "select column_name, data_type, data_length, nullable from a
+ll_tab_columns where table_name = ?";
(Just have a scalar variable that holds the table name in ALL_CAPS, and provide that variable when you "execute" the prepared statement.) That query might not tell you everything you want to know about the definitions of the columns -- in which case, just consult a good oracle book about how to query for the attributes you want. | [reply]
[d/l] |
|
