DBD::ODBC and TSQL

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

I have some code that I need to execute from withing SQL Server and it is written in TSQL. The short version is that there are several sql statements and are all being executed at one time, back-to-back, with one query and this works fine with Microsoft Query Analyzer but not with DBD.

For instance, if I have the following query (which is valid in Microsoft Query Analyzer), how would I run it through DBD:

use Northwind

declare @d datetime
declare @c int

select @d = getdate()
print @d
print @d - 365*8

select @c = count(*) from Orders where ShippedDate > @d - 365*6
print @c
[download]

And my Perl code:

print "=== processing $server server ===\n";

my $DSN = "driver={SQL Server};Server=$server;database=master;uid=;pwd
+=;";

my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

$tsql = <<'END_QUERY';

use Northwind

declare @d datetime
declare @c int

select @d = getdate()
print @d
print @d - 365*8

select @c = count(*) from Orders where ShippedDate > @d - 365*6
print @c

END_QUERY

# create a modified sp_helprotect that will output results in table ve
+rsion
$sth = $dbh->prepare ($tsql);
$sth->execute();
$out = "";

while (@row = $sth->fetchrow_array()) {         # this is line 44
    print $server,':',join(':',@row),"\n";
}

$sth->finish();

$dbh->disconnect();
[download]

Here is the result if I run it through Perl DBD:

C:\Data\Scripts\sql>tsql_test.pl
=== processing new-borg server ===
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement current
+ly executing err=-1) at C:\Data\Scripts\sql\tsql_test.pl line 44.

C:\Data\Scripts\sql>
[download]

Comment on DBD::ODBC and TSQL Select or Download Code

Replies are listed 'Best First'.
Re: DBD::ODBC and TSQL by mpeppler (Vicar) on Apr 24, 2004 at 06:38 UTC
You can certainly execute T-SQL code via DBI. However, you should be aware of certain limitations. First, the `use some_database` command needs to be run in its own batch (or at least that is true of Sybase - I suspect the same is true of MS-SQL). Second, you return data from your script with `print @c`. This will not return a row of data - it will return what is known as out-of-band data that is normally directed at an error/message handler - thus calling `fetchrow_array` won't work. You could change the SQL code from `print @c` to `select @c` and your script will most likely work. Michael	[reply] [d/l] [select]
Re: DBD::ODBC and TSQL by mifflin (Curate) on Apr 23, 2004 at 21:12 UTC
DBI does not know TSQL. It knows SQL. In Oracle I'd do something like ... `$sql = <<EOT; select count() from Orders where ShippedDate > sysdate - 3656 EOT $sth = $dbh->prepare($sql); $sth->execute(); if (($count) = $sth->fetchrow_array()) { print "$server $count\n"; } else { print "$server Unable to get count\n"; } $sth->finish();` [download]	[reply] [d/l]
Re: DBD::ODBC and TSQL by Grygonos (Chaplain) on Apr 24, 2004 at 02:31 UTC
Be wary of unportable code like PL/SQL and TSQL. They are great for their platforms, but are not cross-platform. Coding it in something like Perl, and using ANSI standard SQL queries to retrieve your data, and then Perl to organize it, will better serve the portability and maintainability of your code Grygonos	[reply]
Re: DBD::ODBC and TSQL by bilfurd (Hermit) on Apr 23, 2004 at 21:36 UTC
I suspect that the SQL is returning a series of values, not a recordset object. The easiest way to check would be to save the code in a view and see if it works from there. You might be better off taking a different approach with your SQL statement. Something along the lines of `SELECT GETDATE() as DateToday, GETDATE()-(3658) as EightYearsAgo, count(OrderID) as OrderCount from Orders WHERE ShippedDate > (GETDATE()-(3658)) - (GETDATE()-(365*6))` would be recognized as a query, though I am unclear as to the intent of the statement so I cannot guarantee that the statement is what you are looking for. For multiple statments (DELETE followed by INSERT INTO followed by a SELECT, for example), I recommend using stored procedures. Grab a TSQL manual and page through the SELECT statement again -- it's pretty flexible. Good Luck!	[reply] [d/l]