Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DBD::mysqlPP does not support TYPE and PRECISION statement handles?

by taioba (Novice)
on Oct 25, 2010 at 18:56 UTC ( #867324=perlquestion: print w/ replies, xml ) Need Help??
taioba has asked for the wisdom of the Perl Monks concerning the following question:

Your Holinesses,

I'm trying to recover the name, type and precision of fields in tables stored in a MySQL database using a database handle created via DBD::mysqlPP. I tried the code:

package FieldAttribs; use DBI; $dbh = ( "dbi:mysqlPP:database=$database;host=$host;port=$port", $user +name, $password, {'RaiseError'=> 1} ); $sth = $dbh->prepare( "SELECT * FROM $table" ); $sth->execute(); printf "%s %s %s", $sth->{NAME_lc}->[0], $sth->{TYPE}->[0], $sth->{PRE +CISION}->[0];

But then I get:

Can't set DBI::st=HASH(0x370ee64)->{TYPE}: unrecognised attribute name or invalid value at C:/Perl/site/lib/DBD/mysqlPP.pm line 439.

Can't set DBI::st=HASH(0x370ee64)->{PRECISION}: unrecognised attribute name or invalid value at C:/Perl/site/lib/DBD/mysqlPP.pm line 439.

Use of uninitialized value in printf at D:\Marcos\Perl\SVN\WbMx\trunk\Auxiliary\TestmysqlPP.pl line 11.

Use of uninitialized value in printf at D:\Marcos\Perl\SVN\WbMx\trunk\Auxiliary\TestmysqlPP.pl line 11.

field1

Where 'field1' is actually the name of the field. Thus, NAME seems to be only statement handle that works. If I try the same code using the driver 'dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb)' targeting an Access database with an identical table, I get:

field1 4 10

Any ideas? Many thanks in advance!

Comment on DBD::mysqlPP does not support TYPE and PRECISION statement handles?
Download Code
Re: DBD::mysqlPP does not support TYPE and PRECISION statement handles?
by NetWallah (Abbot) on Oct 25, 2010 at 19:11 UTC
    You are missing one step.

    From the manual:
    For SELECT statements, execute simply "starts" the query within the database engine.

    To actually get/iterate through the results, you need to do a "fetch" command like

    my $rowref = $sth->fetchrow_arrayref();
    Update1: Ugh!. Misread the problem. Sorry.

    Update2: Ok - so it looks like the underlying mysql driver does not support those attributes.
    But you probably already knew that.

         Syntactic sugar causes cancer of the semicolon.        --Alan Perlis

      Yep, and once again Komodo ran the wrong script and I thought I had it! It does that sometimes. Your "solution" did not work, but I switched from DBD::mysqlPP to DBD::mysql and this driver supports all those handles. Thanks at any rate!

Re: DBD::mysqlPP does not support TYPE and PRECISION statement handles?
by kcott (Abbot) on Oct 25, 2010 at 22:45 UTC

    Firstly, take a look at the documentation for DBI Statement Handle Attributes which discusses the types of errors you're seeing.

    Next, see these for driver specific details:

    As the statement handle is a hashref (DBI::st=HASH(0x370ee64)), you can write a short piece of code which will list all available attributes for you. It should look something like the following untested code and placed before your current printf statement:

    for my $key (keys %$sth) { say $key, ' : ', ($sth->{$key} // 'undef'); # Use this if your Perl version < 5.10 #print $key, ' : ', (defined $sth->{$key} ? $sth->{$key} : 'undef' +), "\n"; }

    -- Ken

      Thanks! I ended up finding out that DBD::mysql actually supports all those handles. Life is good again! Best wishes!

Re: DBD::mysqlPP does not support TYPE and PRECISION statement handles?
by aquarium (Curate) on Oct 25, 2010 at 23:37 UTC
    on a separate note..if memory serves correctly..there's a standard way to interrogate the database schema via DBI, rather than getting the information indirectly. please have a look at DBI Catalog Methods
    the hardest line to type correctly is: stty erase ^H

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://867324]
Approved by NetWallah
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2014-10-25 20:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (148 votes), past polls