column_info

[Philip M. Gollucci]

Hi, I've got an issue with $dbh->column_info for DBD::Oracle.

Basically the function returns undef to me and I've seen the exact
same code
work under DBD::ODBC.

foreach my $column_nm (@column_nms) {
my $sth = $dbh->column_info(undef, $schema, $table_nm,
$column_nm);
my $hashref = $sth->fetchrow_hashref(); ### line 278
my $type_name = $hashref->{'type_name'};
my $size = $hashref->{'column_size'};

}

Basically the fetchrow_hashref failes because:
Can't call method "fetchrow_hashref" on an undefined value at
/opt/ejpress/ams/cgi-bin/AMSDbMaintenance.pm line 278.


I have 2 setups one of them is a clients (doesn't work) one of them is
my
local dev (works). Based on
http://search.cpan.org/author/TIMB/DBD-Oracle-1.14/Oracle.pm#column_info_
http://search.cpan.org/author/TIMB/DBI-1.37/DBI.pm

Client:
I do _not_ have root on this system.
uname -a
SunOS ejpnode1 5.9 Generic_112233-02 sun4u sparc SUNW,UltraAX-i2
perl -v
This is perl, v5.8.0 built for sun4-solaris
perl -MDBI -e 'print $DBI::VERSION' => 1.37
perl -MDBD::Oracle -e 'print $DBD::Oracle::VERSION' => 1.14

Local:
Win2k SP3
perl -v
This is perl, v5.6.1 built for MSWin32-x86-multi-thread
perl -MDBD::Oracle -e 'print $DBD::Oracle::VERSION' => 1.14
perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION' => 1.05

------------------------------------------------------------------------------
Philip M. Gollucci
eJournalPress
DBA / Software Engineer / System Administrator
E-Mail: pgollucci [at] ejournalpress.com
URL : http://www.ejournalpress.com
Phone : 301.530.6375

 

[Philip M. Gollucci]

The relevant line from the .trace file with level at 12

T <- column_info(undef 'ejp' ...)= undef at AMSDbMaintenance.pm line 281

You may also want to check with the DBD::Oracle documentation as well.
I am not certain, but some methods are available with some drivers
(DBD modules) and others are not. Again, I'm not sure about this, so
you may want to double check me.

Yes you are correct about some being available in one driver and not
in others. The only reason I even asked this question is because
_OF_ the documentation.

Inerestingly, I had already checked. The DBI documentation lists it.
The Oracle Documentation lists it. The ODBC documentation does _NOT_.
Quite the opposite of what I would have expected.

I've even look through the Code of DBI.
in ~1.32
sub column_info() {
shift->_not_implent(.....)

around 1.37 this now calls _columns(). This must be an XS function
cause it was no where to be found.

At any rate, I've worked around it, but would still like to know whats going on.

SELECT table_name, column_name, data_type, nullable char_col_decl_length
FROM all_tab_columns
WHERE table_name = UPPER(?)
AND owner = UPPER(?)

 

[Philip M. Gollucci]

I've now run into another problem.

Constraints and Indexes

my $constraints;

foreach my $table_nm (sort @table_nms) {
$table_nm = lc $table_nm;
my @key_names = $dbh->primary_key(undef, $cf->get('dbusername'), $table_nm);
@key_names = sort grep { $_ = lc $_} @key_names;

push @$constraints, {
name => "$table_nm\_cst",
columns => \@key_names
};
}


If there is not Primary Key (CONSTRAINT - _cst) then I get the index (_idx)

I may be able to use
select index_name from user_indexes

to work around this, but one would hope there is a better way.

If your wondering, next up are Referential Integrity, Sequences
and triggers. But I'll save those for tomorrow.

Thanks again/in advance.