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

[grant weyburne]

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<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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20131010/82268cf5/attachment.htm>