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];

[download]

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

Replies are listed 'Best First'.
Re: DBD::mysqlPP does not support TYPE and PRECISION statement handles?
by kcott (Archbishop) 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";
}

    [download]

    -- Ken

[reply]
[d/l]
[select]

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

[reply]
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
[reply]
Re: DBD::mysqlPP does not support TYPE and PRECISION statement handles?
by NetWallah (Canon) 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();

    [download]
     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

[reply]
[d/l]

      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!

[reply]

Back to Seekers of Perl Wisdom