DBI weirdness: Arrays as strings.

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 = users.id 
+)
                        where short_id = '12345';]
        );

say $text;
say $lang;
say $author;
[download]

(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 perl_db_test.pl line 22.

Use of uninitialized value $author in say at perl_db_test.pl line 23.
[download]

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...

Comment on DBI weirdness: Arrays as strings. Select or Download Code

Replies are listed 'Best First'.
Re: DBI weirdness: Arrays as strings. (ROW constructor) by erix (Prior) 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)` [download] 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)` [download] (latest postgresql 9.1.3.)	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: DBI weirdness: Arrays as strings. (ROW constructor)
by erix (Prior) 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)
[download]

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

(latest postgresql 9.1.3.)

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom