Hello Grant, pulling this topic out of the archive as I face similar issue and found a work around. I'm unsure what's happening in gp_somestoredproc but if using the sql management studio, you see some output such as (X row(s) affected) then you might want to put "set nocount on" before issuing the statement I'm unsure what's the status of multiple resultset in hdbc / hdbc-odbc but I did succeed pulling results out of first resultset of a stored procedure call (sorry for html email, sending this from google groups) On Friday, 1 February 2013 01:16:38 UTC+1, grant wrote:
Hi,
I am trying to call a stored procedure (exec gp_somestoredproc 123,22) using microsoft sql server 2008 R2 using hdbc-odbc.
Here is the haskell code:
import Database.HDBC import Database.HDBC.ODBC import Control.Monad import Data.Maybe import qualified Data.ByteString.Char8 as B
test1 = do c <- connectODBC "Driver={SQL Server};Server=?;Database=?;uid=sa;pwd=?;" p <- prepare c "exec gp_somestoredproc 123,22" -- returns no data -- p <- prepare c "exec [sys].sp_datatype_info_100 0,@ODBCVer=4;exec gp_somestoredproc 123,22" -- all is good e <- execute p [] -- returns 6 (number of rows) putStrLn $ "execute " ++ show e r <- fetchAllRows' p putStrLn $ "fetchAllRows' " ++ show r
The problem is that this code returns the number of rows correctly but doesn't return data nor are there any errors.
However, I ran a perl program using perl dbi and got the data correctly.
Here is the perl code:
#!/usr/bin/perl use DBI;
my $user = 'sa'; my $pw = '????'; my $dsn = '????'; my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pw, {PrintError => 1, RaiseError => 1}); if (!$dbh) { print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n"; } my $type_info = $dbh->type_info(93); while(my($key, $value) = each(%$type_info)){ print "$key => $value\n"; }; my $sql = "exec gp_somestoredproc 123,22"; my $sth = $dbh->prepare($sql); my $r = $sth->execute; while (my ($db) = $sth->fetchrow_array) { print $db . "\n===\n";
} $dbh->disconnect if $dbh;
I traced both versions and noticed that the perl dbi version first called
exec [sys].sp_datatype_info_100 0,@ODBCVer=4
So I prefixed the stored proc call in haskell with "exec [sys].sp_datatype_info_100 0,@ODBCVer=4;" and it worked fine.
In short:
FAILS p <- prepare c "exec gp_somestoredproc 123,22" -- returns number of rows but no data
WORKS p <- prepare c "exec [sys].sp_datatype_info_100 0,@ODBCVer=4;exec gp_somestoredproc 123,22"
I have no idea why this works.
sp_datatype_info_100 just dumps out the fields types ...
More information:
The stored procedure returns data with user defined field types. I have managed to do selects against tables with user defined field types without any problems using hdbc-odbc. I couldn't emulate this error on a local older version of mssql server (Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) express) but the perl dbi prefixed the stored procedure call with "exec sp_datatype_info 0,@ODBCVer=3" I am running this against Microsoft SQL Server 2008 R2 (RTM) - 10.50.1797.0 (X64)
I would appreciate any pointers you can give me. Thanks Grant
_______________________________________________ Haskell-Cafe mailing list Haskel...@haskell.org javascript: http://www.haskell.org/mailman/listinfo/haskell-cafe