Re: [Haskell-cafe] 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 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,@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 mailing list
>> haskel...@haskell.org
>> http://www.haskell.org/**mailman/listinfo/haskell-cafe
>>
>
> ___
> Haskell-Cafe mailing list
> Haskell-Cafe@haskell.org
> http://www.haskell.org/mailman/listinfo/haskell-cafe
>
>
___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] 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,@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 mailing list
> haskel...@haskell.org
> http://www.haskell.org/mailman/listinfo/haskell-cafe
>
___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] 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,@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 mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
