Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

DBI weirdness: Arrays as strings.

by DStaal (Chaplain)
on May 06, 2012 at 02:13 UTC ( #969102=perlquestion: print w/replies, xml ) Need Help??
DStaal has asked for the wisdom of the Perl Monks concerning the following question:

I have a bit of weirdness going on with selectrow_array(). Example:

#!/usr/bin/perl use strict; use warnings; use v5.14; use DBI; my $dbh = DBI->connect('dbi:Pg:dbname=$dbname', '$user', '$pass', { AutoCommit => 0 } ); my ($text, $lang, $author) = $dbh->selectrow_array( q[ select( text_element, resource.language, display_name) from resource join users on ( created_by = +) where short_id = '12345';] ); say $text; say $lang; say $author;

(Database name, user, and password replaced with placeholders for posting. No other changes.) Result:

("text_element_contents",eng,"Daniel Staal") Use of uninitialized value $lang in say at line 22. Use of uninitialized value $author in say at line 23.

So, instead of getting an array back, or even an array reference, I'm getting the entire result set as a string, which looks like an array. Any ideas what's going on here? (Note: it does the same with selectrow_arrayref() or selectrow_hashref(), except that the string is wrapped in an array/hash ref.)

(I have trimmed text_element_contents here as the actual contents are several kilobytes of text, but I get the same result with it left out entirely.)

Edit: Solved it! Weirdness... If you take out the parenthesis in the SQL statement (lines 13 and 16), it works. Now, why couldn't I have figured that out in the hour I've been beating my head against this before I posted on Perlmonks...

Replies are listed 'Best First'.
Re: DBI weirdness: Arrays as strings. (ROW constructor)
by erix (Parson) on May 06, 2012 at 05:51 UTC

    Yeah, there is no weirdness here. Parentheses make a regular select-list into a ROW-constructor (see the FM: "The key word ROW is optional when there is more than one expression in the list." ):

    So it is:

    testdb=# select x,y,z from (values ('abc', 'def', 'ghi')) as f(x,y,z); x | y | z -----+-----+----- abc | def | ghi (1 row)

    but with parentheses:

    testdb=# select (x,y,z) from (values ('abc', 'def', 'ghi')) as f(x,y,z +); row --------------- (abc,def,ghi) (1 row) -- also: -- "The key word ROW is optional when there is more than one expressio +n in the list." -- testdb=# select (x,y,z) = row(x,y,z) from (values ('abc', 'def', 'ghi')) as f(x,y,z); ?column? ---------- t (1 row)

    (latest postgresql 9.1.3.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://969102]
Approved by planetscape
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (2)
As of 2018-01-23 19:00 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (253 votes). Check out past polls.