[Haskell-cafe] hdbc-odbc not getting any data using mssql stored procedure

[Gauthier Segay]

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, at 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, at ODBCVer=4 
>
> So I prefixed the stored proc call in haskell with "exec 
> [sys].sp_datatype_info_100 0, at 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, at 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, at 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... at haskell.org <javascript:> 
> http://www.haskell.org/mailman/listinfo/haskell-cafe 
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20131010/0783647d/attachment.htm>