Hi Gauthier, that answer was perfect! I just tried it out and It completely solved my problem. Thanks so much! Grant.
On Thu, Oct 10, 2013 at 8:58 AM, Gauthier Segay <gauthier.se...@gmail.com>wrote: > 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 >> http://www.haskell.org/**mailman/listinfo/haskell-cafe<http://www.haskell.org/mailman/listinfo/haskell-cafe> >> > > _______________________________________________ > Haskell-Cafe mailing list > Haskell-Cafe@haskell.org > http://www.haskell.org/mailman/listinfo/haskell-cafe > >
_______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe