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

31 Jan 2013

      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] hdbc-odbc not getting any data using mssql stored procedure

grant