Perl->odbc->sql server stored proc wierdness 
Author Message
 Perl->odbc->sql server stored proc wierdness

OK, I have struggled with this for a few days and can't figure out
what I am doing wrong.

Basically, I have perl script.  This perl script executes a stored
procedure on Microsoft SQL server 2000.  The problem is this...
depending on what goes on in the stored procedure, not all of it will
be executed by perl, and no error will be reported.

Observe:
1.  Create a stored procedure:
----BEGIN SQL Code

as
SET NOCOUNT ON
truncate table geofftest




BEGIN
        print '1234567890123456789012345678901234567890123456789012345678901234567890'
        select * from geofftest

END

------END SQL code

2.  Run this from the query analizer window:
exec sp_geofftest 'one', 'two'

3.  Now check to see that both values got inserted into the table:
select * from geofftest
The result set will contain both values, 'one' and 'two'

4.  Now, run this perl script:
-------BEGIN PERL CODE
use DBI;
$DSN = 'DBI:ODBC:DSN';
$USER = 'user';
$PASS = 'pass';
$DB = 'db_name';
#Connect to DB
my $dbh = DBI->connect($DSN, $USER, $PASS, { PrintError => 1,
RaiseError => 1, AutoCommit => 1 });
#Call Stored Proc
my $statement = "exec $DB.dbo.sp_geofftest 'one', 'two'";
print $statement."\n";
my $sth = $dbh->prepare($statement );
$sth->execute;
#clean up
$sth->finish;
$dbh->disconnect;
------END perl code
Note that no error are returned when running this code...

5.  Now check to see (again) that both values got inserted into the
table:
select * from geofftest
The result set will contain only the first value!!!! ('one')

I have tried different drivers (ADO, W32ODBC) and get the same
results.  Am I doing something wrong with my perl code?  Or is it a
driver/odbc issue?  If anyone has any insight, I would greatly
appreciate it.

Thank you,
Geoffrey Nix



Sun, 04 Jul 2004 11:10:18 GMT  
 Perl->odbc->sql server stored proc wierdness


Quote:
>use DBI;
>$DSN = 'DBI:ODBC:DSN';
>$USER = 'user';
>$PASS = 'pass';
>$DB = 'db_name';
>#Connect to DB
>my $dbh = DBI->connect($DSN, $USER, $PASS, { PrintError => 1,
>RaiseError => 1, AutoCommit => 1 });
>#Call Stored Proc
>my $statement = "exec $DB.dbo.sp_geofftest 'one', 'two'";
>print $statement."\n";
>my $sth = $dbh->prepare($statement );
>$sth->execute;

I suggest to insert here:

while( my $h = $sth->fetchrow_hashref() ){
        ; # you might want to  dump  $h

Quote:
}
>#clean up
>$sth->finish;
>$dbh->disconnect;
>------END perl code

Since SELECTs are fetched and served to the $sth handle, I have
reason  to suspect that you 19 cycles just fill up  some buffer.

Hope This Helps
Andrea



Mon, 12 Jul 2004 05:34:01 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. WinNT->DBI->DBD::ODBC->Jet->INSERT == SQL-S1000

2. HELL=>Perl->SQL Server on Alpha NT

3. LONG: Req: Help in retrieving values from SQL Server stored procedure using Win32::ODBC

4. ODBC, SQL Server, Stored Procedures

5. ODBC, SQL Server, Stored Procedures

6. open(PROC, "|proc >&FD")

7. Linex web server -> NT SQL Server

8. Perl/ODBC/SQL Wierdness?

9. PERL->ADO->SQL

10. PERL->ADO->SQL

11. ADO->PERL->SQL

12. PERL->ADO->SQL

 

 
Powered by phpBB® Forum Software