Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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

by taioba (Acolyte)
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 making s'mores by the fire in the courtyard of the Monastery: (10)
As of 2015-07-07 08:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (88 votes), past polls