grant | 1 Feb 01:16 2013
Picon

hdbc-odbc not getting any data using mssql stored procedure

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:

(Continue reading)

Gauthier Segay | 10 Oct 14:58 2013
Picon

Re: hdbc-odbc not getting any data using mssql stored procedure

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
http://www.haskell.org/mailman/listinfo/haskell-cafe
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
grant weyburne | 10 Oct 16:08 2013
Picon

Re: hdbc-odbc not getting any data using mssql stored procedure

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.segay <at> 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, <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
http://www.haskell.org/mailman/listinfo/haskell-cafe

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe


_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Gmane