Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Multiple records fetches issue using DBD::ODBC in Perl

by maria80e (Novice)
on Apr 17, 2020 at 05:29 UTC ( #11115668=perlquestion: print w/replies, xml ) Need Help??

maria80e has asked for the wisdom of the Perl Monks concerning the following question:

I have executed the stored procedure manually which retrieves 45 records where as while executing it through below perl script, it fetches only one record and that record is not the top or last record of 45 records. Whats wrong in the below code. Need support on this.

Platform :Windows Database :MS SQL SERVER

**Program**:

use strict; use DBI; use DBD::ODBC; print "Trying to connect\n"; my $server_name = "XXXXXXXX"; my $server_port = 'NNNN'; my $database_name = 'XXXXX'; my $database_user = 'XX'; my $database_pass = 'XXXXX'; my $dbh = DBI->connect("dbi:ODBC:DSN=sql_test", $database_user, $datab +ase_pass) || die "Couldn't open database: $DBI::errstr\n"; my $sql = $dbh->prepare("Exec sp_listUsers '25329601','1'"); my $more_results; $sql->execute(); my $results_ref; my @array; do { print "getting results\n"; my $names = $sql->{NAME}->[0]; print "names $names\n"; while ($results_ref = $sql->fetchrow_hashref()) { print "test $results_ref->{'user_id'}\n"; print join (", ", values %$results_ref), "\n"; push (@array, $results_ref->{'user_id'}); #print "row $results_ref\n"; } }while($more_results = $sql->{odbc_more_results}); foreach my $val (@array) { print "user id $val\n"; } $dbh->disconnect();

Replies are listed 'Best First'.
Re: Multiple records fetches issue using DBD::ODBC in Perl
by afoken (Canon) on Apr 18, 2020 at 19:58 UTC

    Your code lacks error checking. Start by changing the connect call to this:

    my $dbh = DBI->connect("dbi:ODBC:DSN=sql_test", $database_user, $datab +ase_pass, { RaiseError => 1 });

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re: Multiple records fetches issue using DBD::ODBC in Perl
by clueless newbie (Curate) on Apr 18, 2020 at 22:04 UTC

    Turn on tracing by setting DBI_TRACE via

    set DBI_TRACE=1

    then run the script and report the results.

      I enabled set DBI TRACE=1. Please find the below TRACE log.

      LOG:-

      DBI 1.636-ithread default trace level set to 0x0/1 (pid 11108 pi 7c2ff +4) at DBI.pm line 296 via test_dbi.pl line 2 Trying to connect -> DBI->connect(dbi:ODBC:DSN=sql_test, **, ****) -> DBI->install_driver(ODBC) for MSWin32 perl=5.024000 pid=11108 r +uid=0 euid =0 install_driver: DBD::ODBC version 1.52 loaded from C:/Perl/lib/ +DBD/ODBC.pm <- install_driver= DBI::dr=HASH(0x1d76c6c) !! The warn '0' was CLEARED by call to connect method <- connect('DSN=sql_test', '**', ...)= ( DBI::db=HASH(0x22ec33c) ) + [1 items] at DBI.pm line 684 info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change +d database context to 'master'. (SQL-01000) [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin +g to us_english. (SQL-01000)' (err#0) <- STORE('PrintError', 1)= ( 1 ) [1 items] at DBI.pm line 736 info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change +d database context to 'master'. (SQL-01000) [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin +g to us_english. (SQL-01000)' (err#0) <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 736 info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change +d database context to 'master'. (SQL-01000) [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin +g to us_en glish. (SQL-01000)' (err#0) <- STORE('Username', '**')= ( 1 ) [1 items] at DBI.pm line 739 info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change +d database context to 'master'. (SQL-01000) [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin +g to us_english. (SQL-01000)' (err#0) <- connected('dbi:ODBC:DSN=sql_test', '**', ...)= ( undef ) [1 ite +ms] at DBI.pm line 746 <- connect= DBI::db=HASH(0x22ec33c) info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change +d database context to 'master'. (SQL-01000) [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin +g to us_english. (SQL-01000)' (err#0) <- STORE('dbi_connect_closure', CODE(0x1d6e5ac))= ( 1 ) [1 items] +at DBI.pmline 755 !! The info '''' was CLEARED by call to prepare method <- prepare('Exec sp_listUsers '25329601','1'')= ( DBI::st=HASH(0x2 +2e969c) ) [1 items] at test_dbi.pl line 16 <- execute= ( -1 ) [1 items] at test_dbi.pl line 18 -1 <- DESTROY(DBI::st=HASH(0x22e9504))= ( undef ) [1 items] at test_d +bi.pl line 20 !! The info '''' was CLEARED by call to disconnect_all method <- disconnect_all= ( '' ) [1 items] at DBI.pm line 763 ()! <- DESTROY(DBI::dr=HASH(0x1d76c6c))= ( undef ) [1 items] during +global destruction ! <- DESTROY(DBI::db=HASH(0x22ec264))= ( undef ) [1 items] during gl +obal destruction

        This is the log resulting from Marshall's example, no? Perhaps we could see the log that results from running the code in your original post?

        Added

        That -1 (from "my $results = $sql->execute();") says that the $sql->execute() didn't "error out."

        Added

        The poster seems to have responded with this post on stackoverflow.

Re: Multiple records fetches issue using DBD::ODBC in Perl
by Marshall (Canon) on Apr 18, 2020 at 09:09 UTC
    I don't know anything about using stored procedures.
    However when you execute a prepared sql statement handle, you have to assign that result to a variable.
    Does this help?
    use strict; use DBI; use DBD::ODBC; use Data::Dump qw(pp); print "Trying to connect\n"; my $server_name = "XXXXXXXX"; my $server_port = 'NNNN'; my $database_name = 'XXXXX'; my $database_user = 'XX'; my $database_pass = 'XXXXX'; my $dbh = DBI->connect("dbi:ODBC:DSN=sql_test", $database_user, $datab +ase_pass) || die "Couldn't open database: $DBI::errstr\n"; my $sql = $dbh->prepare("Exec sp_listUsers '25329601','1'") or die "ba +d prepare $!"; my $results = $sql->execute(); pp $results;

      If I executed the above program I got the $results "-1". So I enabled set DBI TRACE=1. Please find the below TRACE log.

        I am a bit flummoxed as to how you get -1 as the result. What does this do?
        my $results = $sql->execute() or die("EXECUTE FAILED $!"); my $array_ref = $results->fetchall_arrayref(); pp $array_ref;
Re: Multiple records fetches issue using DBD::ODBC in Perl
by jo37 (Hermit) on Apr 17, 2020 at 18:36 UTC

    This problem might be related to a stored procedure as the statement to be executed. Is there a way to define a view that gives the same result and to use a select on this view to get the result set? Maybe this could help narrowing the problem.

    Greetings,
    -jo

    $gryYup$d0ylprbpriprrYpkJl2xyl~rzg??P~5lp2hyl0p$

      I have set DBI TRACE=1 and executed the code.

      my $result = $sql->execute() or die "Couldn't execute statement: " . $sql->errstr;

      after sql->execute it prints the value as -1.

      Actually, I am doing perl upgrade, The existing code has used MSSQL::DBLIB,MSSQL::SQLLIB to execute the stored procedures (MS SQL SERVER)which are deprecated in the higher version.For Windows platform i have used DBI,DBD::ODBC module to connect the Database and execute the stored procedure.

      Note: Database connection and stored procedure execution was working fine in perl old version(20 yrs old)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11115668]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (5)
As of 2022-07-07 14:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?