rinceWind has asked for the wisdom of the Perl Monks concerning the following question:

Delving my way through some DBI code, I found that the easiest way to pull out a list of columns for a table was to use
$sth->{NAME}

[download]
after executing a query on $sth. To my surprise, I did not find this in the DBI documentation.

This has aroused my curiousity. What else is hiding in a statement handle? However, examining with the debugger came as a shock:

  DB<3> x $sth
0  DBI::st=HASH(0x402426c0)
     empty hash

[download]
I had expected $sth to be merely a blessed object implemented as a hash ref, and at least to contain the key 'NAME'. Looking at DBI.pm, I had a hint that the variable might be tied, aha.
  DB<4> x tied %$sth
0  DBI::st=HASH(0x400221f0)
   'Database' => DBI::db=HASH(0x4026e6fc)
        empty hash
   'Debug' => 0
   'Err' => SCALAR(0x402326cc)
      -> undef
   'Errstr' => SCALAR(0x402326fc)
      -> undef
   'FetchHashKeyName' => 'NAME'
   'Handlers' => ARRAY(0x40242684)
        empty array
   'ImplementorClass' => 'DBD::Oracle::st'
   'State' => SCALAR(0x401c98d0)
      -> undef
   'Statement' => 'select * from my.foo_test'
  DB<5>

[download]
What's going on here? Where is the key 'NAME'? Or where is the tie FETCH method that gets called? I had a look in DBD::Oracle.pm but couldn't find anything there. DBI.pm does have several TIEHASHes, but no obvious FETCH routines, and no reference to a key of NAME.

Any insight would be appreciated. Curiousity has certainly got the better of this cat. :-)

--rW

Replies are listed 'Best First'.
Re: What's in a DBI statement handle?
by lachoy (Parson) on May 17, 2002 at 16:03 UTC

    This is in the DBI docs -- look under the head 'Statement Handle Attributes'.

    Chris
    M-x auto-bs-mode

[reply]
      Thanks lachoy.

      So, it was in the DBI documentation all along. Well, in my defence, that POD is quite large and easy to get lost in.

      And, you've given me a pointer to the rest of the information I wanted from a query result. lachoy++

[reply]
        Also remember to read the pod for the DBD driver you are using. It may support additional attributes (which should start with a lower-case string that refers to that driver, eg ora_, syb_, etc).

        Michael

[reply]
Re: What's in a DBI statement handle?
by tadman (Prior) on May 17, 2002 at 16:01 UTC
    If you really must find out, you can use Data::Dumper to examine the contents. It can even be a quick one-liner:
    use Data::Dumper; print Dumper($sth);
    I think the reason you are missing data is because you just created a $sth out of thin air, and not throught the proper factory method, which presumably has access to additional data.

    Either way, any information you discover with this or other methods is considered classified. It might change. It might become French or Swedish at the whim of the programmer, and if your program breaks when they do, that's the price you pay. NAME one day and BORK the next, you never know. After all, that kind of change won't break any programs that follow the guidelines in the documentation.

    There is probably a reason why it is not exposed with a proper method, but this could be almost anything. DBI is used on a lot of different things.
[reply]
[d/l]

      Unfortunately, this won't work -- you'll get the Data::Dumper equivalent of what rinceWind showed. Since the statement handle is a tied hash and the DBI is ultra-concerned with efficiency and speed, the properties don't actually get filled until you call them. Best bet is to read the docs.

      Chris
      M-x auto-bs-mode

[reply]
        So DBI hides the keys it contains?

        Yves / DeMerphq
        ---
        Writing a good benchmark isnt as easy as it might look.

[reply]

Back to Seekers of Perl Wisdom