Re: Simple db select output script hanging up.

So, when a table has x-columns, but some of them are empty, nul,
while (@results = $sth->fetchrow_array()) {.....}
[download]
does NOT initialize all the x-elements of @results???

According to the description of fetchrow_array in the DBI manual:

     Null fields are returned as "undef" values in the list.

This seems reasonable. If you really want to use "SELECT * ..." as your SQL statement, maybe you'd rather use fetchrow_hashref: it might be a little slower in terms of runtime, but then you'll have a little more structure in what you get back, so you can easily test the hashref elements for "undef" values and know which fields are null.

Spending that extra runtime will save you from having to get serious about your SQL usage -- i.e. doing things like naming the specific fields that you want from the query, using "SELECT IF(field1,field1,'Null_field1'), IF(field2,field2,'Null_field2') ... " or similar nonsense to make sure that all returned fields get defined values. What a pain. :)

(update: Actually, the proper SQL nonsense to use would most likely be something like SELECT IFNULL(field1,'Null_field1'), IFNULL(field2,'Null_field2')..." -- which will return empty strings and zeros when "non-null" fields are set to those values.)

Comment on Re: Simple db select output script hanging up. Select or Download Code

Replies are listed 'Best First'.
Re^2: Simple db select output script hanging up. by jZed (Prior) on Jun 23, 2007 at 17:33 UTC
I agree that named, specific columns are better in most cases than SELECT *, but if you don't have your constraints and NULLABLEs set correctly, you can still end up with NULLs where you don't expect them. So I'd say either die on undef (if you want to check that your database logic ensures no NULLs) or, something along the lines of: `while(my $results = $sth->fetchrow_arrayref) { push @data, [map {defined($_) ? $_ : ''} @$results]; }` [download]	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: Simple db select output script hanging up.
by jZed (Prior) on Jun 23, 2007 at 17:33 UTC

I agree that named, specific columns are better in most cases than SELECT *, but if you don't have your constraints and NULLABLEs set correctly, you can still end up with NULLs where you don't expect them. So I'd say either die on undef (if you want to check that your database logic ensures no NULLs) or, something along the lines of:

while(my $results = $sth->fetchrow_arrayref) {
    push @data, [map {defined($_) ? $_ : ''} @$results];
}
[download]

[reply]
[d/l]