<div dir="ltr"><span style="font-family:arial,sans-serif;font-size:13px">Hi Gauthier, that answer was perfect! I just tried it out and It completely solved my problem. Thanks so much! Grant.</span><br></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Thu, Oct 10, 2013 at 8:58 AM, Gauthier Segay <span dir="ltr"><<a href="mailto:gauthier.segay@gmail.com" target="_blank">gauthier.segay@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Hello Grant, pulling this topic out of the archive as I face similar issue and found a work around.<br><br>I'm unsure what's happening in gp_somestoredproc but if using the sql management studio, you see some output such as<br>
<br>(X row(s) affected)<br><br>then you might want to put "set nocount on" before issuing the statement<br><br>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<br>
<br>(sorry for html email, sending this from google groups)<div><div class="h5"><br><br>On Friday, 1 February 2013 01:16:38 UTC+1, grant wrote:</div></div><blockquote class="gmail_quote" style="margin:0;margin-left:0.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div class="h5">Hi,
<br>
<br>I am trying to call a stored procedure (exec gp_somestoredproc 123,22)
<br>using microsoft sql server 2008 R2 using hdbc-odbc.
<br>
<br>Here is the haskell code:
<br>
<br>import Database.HDBC
<br>import Database.HDBC.ODBC
<br>import Control.Monad
<br>import Data.Maybe
<br>import qualified Data.ByteString.Char8 as B
<br>
<br>test1 = do
<br> c <- connectODBC "Driver={SQL Server};Server=?;Database=?;<u></u>uid=sa;pwd=?;"
<br> p <- prepare c "exec gp_somestoredproc 123,22" -- returns no data
<br> -- p <- prepare c "exec [sys].sp_datatype_info_100 0,@ODBCVer=4;exec
<br>gp_somestoredproc 123,22" -- all is good
<br> e <- execute p [] -- returns 6 (number of rows)
<br> putStrLn $ "execute " ++ show e
<br> r <- fetchAllRows' p
<br> putStrLn $ "fetchAllRows' " ++ show r
<br>
<br>
<br>The problem is that this code returns the number of rows correctly but
<br>doesn't return data nor are there any errors.
<br>
<br>However, I ran a perl program using perl dbi and got the data correctly.
<br>
<br>Here is the perl code:
<br>
<br>#!/usr/bin/perl
<br>use DBI;
<br>
<br> my $user = 'sa';
<br> my $pw = '????';
<br> my $dsn = '????';
<br> my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pw,
<br> {PrintError => 1, RaiseError => 1});
<br> if (!$dbh) {
<br> print "error: connection: $DBI::err\n$DBI::errstr\n$DBI:<u></u>:state\n";
<br> }
<br> my $type_info = $dbh->type_info(93);
<br> while(my($key, $value) = each(%$type_info)){
<br> print "$key => $value\n";
<br> };
<br> my $sql = "exec gp_somestoredproc 123,22";
<br> my $sth = $dbh->prepare($sql);
<br> my $r = $sth->execute;
<br> while (my ($db) = $sth->fetchrow_array) {
<br> print $db . "\n===\n";
<br>
<br> }
<br> $dbh->disconnect if $dbh;
<br>
<br>I traced both versions and noticed that the perl dbi version first called
<br>
<br> exec [sys].sp_datatype_info_100 0,@ODBCVer=4
<br>
<br>So I prefixed the stored proc call in haskell with "exec
<br>[sys].sp_datatype_info_100 0,@ODBCVer=4;" and it worked fine.
<br>
<br>In short:
<br>
<br>FAILS p <- prepare c "exec gp_somestoredproc 123,22" -- returns number of rows
<br>but no data
<br>
<br>WORKS p <- prepare c "exec [sys].sp_datatype_info_100 0,@ODBCVer=4;exec
<br>gp_somestoredproc 123,22"
<br>
<br>I have no idea why this works.
<br>
<br>sp_datatype_info_100 just dumps out the fields types ...
<br>
<br>More information:
<br>
<br>The stored procedure returns data with user defined field types.
<br>I have managed to do selects against tables with user defined field types
<br>without any problems using hdbc-odbc.
<br>I couldn't emulate this error on a local older version of mssql server
<br>(Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) express) but the perl dbi
<br>prefixed the stored procedure call with "exec sp_datatype_info 0,@ODBCVer=3"
<br>I am running this against Microsoft SQL Server 2008 R2 (RTM) - 10.50.1797.0
<br>(X64)
<br>
<br>I would appreciate any pointers you can give me.
<br>Thanks
<br>Grant
<br>
<br>
<br>
<br>______________________________<u></u>_________________
<br>Haskell-Cafe mailing list
<br></div></div><a>Haskel...@haskell.org</a>
<br><a href="http://www.haskell.org/mailman/listinfo/haskell-cafe" target="_blank">http://www.haskell.org/<u></u>mailman/listinfo/haskell-cafe</a>
<br></blockquote></div><br>_______________________________________________<br>
Haskell-Cafe mailing list<br>
<a href="mailto:Haskell-Cafe@haskell.org">Haskell-Cafe@haskell.org</a><br>
<a href="http://www.haskell.org/mailman/listinfo/haskell-cafe" target="_blank">http://www.haskell.org/mailman/listinfo/haskell-cafe</a><br>
<br></blockquote></div><br></div>