column_info

Discussion in 'Perl Misc' started by Philip M. Gollucci, Aug 20, 2003.

  1. 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, Aug 20, 2003
    #1
    1. Advertising

  2. 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, Aug 20, 2003
    #2
    1. Advertising

  3. 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.
     
    Philip M. Gollucci, Aug 20, 2003
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page