Doubts about Class:DBI

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

On moving the database access of my system to Class::DBI I'm not getting the columns value other than primary key. Looking at SQL Profile, i see the prepare command are getting just the primary key, as you can see above :

-- SQL Code created by Class::DBI
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, N'@P1 nvarchar(6)', N'SELECT orderid
FROM   Orders
WHERE  shipcountry = @P1
', 1
select @P1
[download]

This behavior can be reproduced by these code:

#
# Northwind DBI Class
#
package Northwind;

use base 'Class::DBI';
use strict;

__PACKAGE__->set_db('Main',
                    'dbi:ODBC:driver={SQL Server};
                    Server=SERVER;
                    database=Northwind;',
                    'test',
                    '654123');

1;

package Northwind::Order;
use strict;

use base 'Northwind';

__PACKAGE__->table   ('Orders');
__PACKAGE__->columns (Primary   => qw/OrderID/);
__PACKAGE__->columns (Others    => qw/CustomerID EmployeeID OrderDate 
+RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipReg
+ion ShipCity ShipPostalCode ShipCountry/);

1;
[download]

#
# Testing Northwind database access
#
use Northwind;

my @Orders = Northwind::Order->search( ShipCountry => 'France' );

foreach my $Order ( @Orders ){
    print "$Order->ShipCity\n";
}
[download]

I've read Class::DBI POD and about "Lazy population", but I couldn't understand why I'm not getting the city on 'print "$Order->ShipCity\n";'.

Thank's for any help.

Solli Moreira Honorio
Sao Paulo - Brazil

Comment on Doubts about Class:DBI Select or Download Code

Replies are listed 'Best First'.
Re: Doubts about Class:DBI by dtr (Scribe) on Aug 24, 2005 at 14:24 UTC
You can stop Class::DBI from doing "Lazy population" by declaring your columns as "Essential" instead of "Others". This will allow you to quickly eliminate the lazy population as the cause of your problem. From what you say, though, I don't think that this is the cause of your problem. Have you checked the database to make sure that that field is actually populated? Doh! Beaten to this suggestion by davidrw	[reply]
Re: Doubts about Class:DBI by shonorio (Hermit) on Aug 24, 2005 at 15:08 UTC
Well, I got the problem. I change the print line from `print "$Order->ShipCity\n"` to `print $Order->ShipCity . "\n"`, and than start to work as I'd like. Thanks all Solli Moreira Honorio Sao Paulo - Brazil	[reply] [d/l] [select]
Re: Doubts about Class:DBI by perrin (Chancellor) on Aug 24, 2005 at 14:11 UTC
You already about lazy population, which explains why it only fetches the primary key until you ask for more, so unless you have more questions about that your actual question here is why the ShipCity() method call fails to return anything. Can you show us the database table you're using? Are you positive it has data for ShipCity? Can you try printing the other columns to see if they work? UPDATE: davidrw has it right: just an interpolation problem.	[reply]
Re^2: Doubts about Class:DBI by shonorio (Hermit) on Aug 24, 2005 at 14:51 UTC
I'm using the Northwind database, available on all SQL Server, and I've tried to print all columns whith the same output, "primarykey->columnname". # Output code 10248->ShipCity 10251->ShipCity 10265->ShipCity 10274->ShipCity 10295->ShipCity 10297->ShipCity 10311->ShipCity 10331->ShipCity 10334->ShipCity 10340->ShipCity 10350->ShipCity 10358->ShipCity 10360->ShipCity 10362->ShipCity 10371->ShipCity 10408->ShipCity 10413->ShipCity 10425->ShipCity 10436->ShipCity 10449->ShipCity 10450->ShipCity 10454->ShipCity 10459->ShipCity 10470->ShipCity 10478->ShipCity 10480->ShipCity 10493->ShipCity 10500->ShipCity 10511->ShipCity 10525->ShipCity 10546->ShipCity 10559->ShipCity 10566->ShipCity 10584->ShipCity 10609->ShipCity 10610->ShipCity 10628->ShipCity 10631->ShipCity 10634->ShipCity 10663->ShipCity 10671->ShipCity 10679->ShipCity 10683->ShipCity 10715->ShipCity 10730->ShipCity 10732->ShipCity 10737->ShipCity 10738->ShipCity 10739->ShipCity 10755->ShipCity 10763->ShipCity 10787->ShipCity 10789->ShipCity 10806->ShipCity 10814->ShipCity 10826->ShipCity 10827->ShipCity 10832->ShipCity 10843->ShipCity 10850->ShipCity 10858->ShipCity 10860->ShipCity 10871->ShipCity 10876->ShipCity 10890->ShipCity 10907->ShipCity 10923->ShipCity 10927->ShipCity 10932->ShipCity 10940->ShipCity 10964->ShipCity 10971->ShipCity 10972->ShipCity 10973->ShipCity 11043->ShipCity 11051->ShipCity 11076->ShipCity [download] Solli Moreira Honorio Sao Paulo - Brazil	[reply] [d/l]
Re^3: Doubts about Class:DBI by davidrw (Prior) on Aug 24, 2005 at 14:57 UTC
oh. it's just an interpolation problem. change your print statement to one of these: `print $Order->ShipCity . "\n"; printf "%s\n", $Order->ShipCity;` [download]	[reply] [d/l]
Re^4: Doubts about Class:DBI by shonorio (Hermit) on Aug 24, 2005 at 15:11 UTC
Re: Doubts about Class:DBI by davidrw (Prior) on Aug 24, 2005 at 14:19 UTC
if you make it `__PACKAGE__->columns (Essential =>` instead of `__PACKAGE__->columns (Others =>` does it work (since it should load the Essential columns by default)? If so, is that an acceptable work around (i.e. do you really need to take advantage of lazy population in this case)? Obviously that's not an answer to the root question, but might provide extra information at least..	[reply] [d/l] [select]
Re^2: Doubts about Class:DBI by shonorio (Hermit) on Aug 24, 2005 at 14:59 UTC
I've tried this without sucess. Solli Moreira Honorio Sao Paulo - Brazil	[reply]
Re: Doubts about Class:DBI by monkey_boy (Priest) on Aug 24, 2005 at 14:24 UTC
I once had a problem like this, i never got to the bottom of it but it was fixed by installing Scalar::Util. Class::DBI uses it if it is installed, but doesnt complain if its not, hope ths helps. Update: My mind is muddy, it may have been caused by installing Scalar::Util, i cant remember!, anyhow, see if it is installed, then install/uninstall to see if this fixes the problem. This is not a Signature...	[reply] [d/l]