Changing databases in MSSQL and fetching more than one row of results at a time

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

I am trying to write a script that connects to my SQL 2k server though DBI in perl but I am running into 2 problems that I can not figure out. 1. When connected though DBI:ODBC I can not change the active table unless I issue the following code:

$sth = $dbh -> prepare (qq~use FFImages~);
$sth -> execute();
[download]

I though there was some way I could do something like this:

$sth = $dbh -> prepare (qq~use FFImages select * from information_sche
+ma.tables where table_catalog = 'FFImages' and table_type = 'Base Tab
+le'~) or die "Could not prepair";
$sth -> execute() or die "$DBI::errstr";
[download]

But I just get the error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state (SQL-24000)(DBD: dbd_describe/SQLNumResultCols err=-1)

2. I want to retrive all the results form an SQL query into a single array but the I only know of @arry = $sth -> fetchrow_array; which only gives me the first row of results. How do I retive all the results into the array?

2006-03-03 Retitled by planetscape, as per Monastery guidelines
Original title: 'Perl and DBI issues'

Comment on Changing databases in MSSQL and fetching more than one row of results at a time Select or Download Code

Replies are listed 'Best First'.
Re: Changing databases in MSSQL and fetching more than one row of results at a time by dorward (Curate) on Mar 02, 2006 at 08:01 UTC
1. I don't know MSSQL, but I'd be very surprised if you could issue two statements without at least terminating the first with a semi-colon. 2. From perldoc DBI: $ary_ref = $sth->fetchrow_arrayref;	[reply]
Re: Changing databases in MSSQL and fetching more than one row of results at a time by terce (Friar) on Mar 02, 2006 at 11:36 UTC
I second dorward's comment on point one. However, there's a better way to do this. Rather than switching databases before running the query, you can query across databases on the same server without switching, assuming your security model allows, with syntax like the following: `select * from FFImages..information_schema.tables where table_catalog = 'FFImages' and table_type = 'Base Table'` This is useful because it enables you to join tables from different databases on the same server, should you need to.	[reply] [d/l]
Re: Changing databases in MSSQL and fetching more than one row of results at a time by jZed (Prior) on Mar 02, 2006 at 15:56 UTC
1. I don't know about SQL Server but most RDBMSs support dotted notation in the form catalog.database.table or database.table. 2. my $arrayref = $dbh->selectall_arrayref($sql,{},@binds);	[reply]
Re: Changing databases in MSSQL and fetching more than one row of results at a time by Eagle_f90 (Acolyte) on Mar 02, 2006 at 17:14 UTC
Thank you all for your help. The dot notation has fixed my first issue. But I am still unable to pull all the results. $ary_ref = $sth->fetchrow_arrayref; Just returns "ARRAY(0x2155f8)" And I do not even understand: $arrayref = $dbh->selectall_arrayref($sql,{},@binds); I get that it is doing a selectall on the arrayref but what is $sql, {}, and @binds?	[reply]
Re^2: Changing databases in MSSQL and fetching more than one row of results at a time by jZed (Prior) on Mar 02, 2006 at 19:31 UTC
You need to understand what an array reference is - it is a reference to an array so if you have $ary_ref = $sth->fetchrow_arrayref(), you need to dereference that (turn it into a regular array) with, for example print "@$ary_ref". As for selectall(), it is not doing a select on an arrayref, it is doing a select into an arrayref, in other words the results will be dumped into the arrayref. $sql is a SQL query string such as "SELECT myCol FROM myTable WHERE ...", the {} is an empty attribut hashref (you can just put it in as-is for now), and @binds is an array of the placeholder bind parameters. If you don't have any placeholders you can just omit the {} and the @binds. See the DBI docs under selectall_arryref() for details.	[reply]