• Topic
  • Discussion
  • UdaWikiWeb.CodeSamplesPerl(Last) -- Owiki? , 2016-08-19 14:59:44 Edit owiki 2016-08-19 14:59:44

    #!/usr/bin/perl 
    
    #------------------------------------------------------------------------------- 
    # The DBI module is the interface to the database, and the strict module 
    # forces us to declare variables so we don't get burned by a global variable 
    # with the same name. 
    #------------------------------------------------------------------------------- 
    
    #require DBI; 
    use DBI; 
    use strict; 
    
    #------------------------------------------------------------------------------- 
    # declare local variables 
    #------------------------------------------------------------------------------- 
    
    
    my $dbh; 
    my $sth; 
    
    my $data_source; 
    my $username; 
    my $password; 
    my %dbOptions; 
    
    my $rc; 
    my $rowCount; 
    my $empno; 
    my $ename; 
    my @resultRow; 
    
    
    #------------------------------------------------------------------------------- 
    # declare variables with database connection information 
    #------------------------------------------------------------------------------- 
    
    
    $data_source = "dbi:ODBC:ORA8I_SOLARIS"; 
    $username ='scott'; 
    $password = 'tiger'; 
    %dbOptions = {PrintError => 1, AutoCommit => 2}; 
    
    #------------------------------------------------------------------------------- 
    # connect to the database 
    #------------------------------------------------------------------------------- 
    
    $dbh = DBI->connect("$data_source", '', '') or die "$DBI::errstr\n"; 
    $dbh->{PrintError} = 0; 
    $dbh->{AutoCommit} = 0; 
    $dbh->{LongReadLen} = 64000; 
    
    if ( defined($DBI::err) ) { 
        if ( $DBI::err != 0 ) { 
            printf( STDERR "$0: Connect to Oracle server failed with the following 
    error:\n"); 
            printf( STDERR "    %s\n\n", $DBI::errstr); 
            exit(1); 
        } 
    } 
    
    #------------------------------------------------------------------------------- 
    # set up the SQL statement to be sent 
    #------------------------------------------------------------------------------- 
    
    $rc = $dbh->do("create table foo (id int)"); 
    
    if ( !defined($rc) ) { 
        printf( STDERR "$0: Create table failed with the following error:\n"); 
        printf( STDERR "    %s\n\n", $DBI::errstr); 
    } 
    else { 
        printf( STDERR "$0: Create table succeeded.\n"); 
    } 
    
    #------------------------------------------------------------------------------- 
    # commit 
    #------------------------------------------------------------------------------- 
    
    $rc = $dbh->commit; 
    
    #------------------------------------------------------------------------------- 
    # set up the SQL statement to be sent 
    #------------------------------------------------------------------------------- 
    
    $sth = $dbh->prepare("select empno, ename from emp order by empno"); 
    
    if ( $DBI::err != 0 ) { 
        printf(STDERR "Error parsing SQL:\n"); 
        printf(STDERR "%s\n\n", $DBI::errstr); 
        exit(-1); 
    } 
    
    #------------------------------------------------------------------------------- 
    # execute the SQL statement 
    #------------------------------------------------------------------------------- 
    
    $rc = $sth->execute; 
    
    # rc is useless for select statements 
    
    if ( defined($DBI::err) ) { 
        if ( $DBI::err != 0 ) { 
            printf( STDERR "$0: Select from emp table failed with the following error:\n"); 
            printf( STDERR "    %s\n\n", $DBI::errstr); 
        } 
    } 
    
    #------------------------------------------------------------------------------- 
    # fetch each row of data from the database and process it 
    #------------------------------------------------------------------------------- 
    
    for ( @resultRow = $sth->fetchrow_array; 
          @resultRow != (); 
          @resultRow = $sth->fetchrow_array ) { 
            ($empno, $ename) = @resultRow; 
            printf("%8d %1s\n", $empno, $ename); 
    } 
    
    #------------------------------------------------------------------------------- 
    # free resources from the select cursor 
    #------------------------------------------------------------------------------- 
    
    $rc = $sth->finish; 
    
    #------------------------------------------------------------------------------- 
    # Get the row count (THIS ONLY WORKS IF YOU FETCH ALL OF THE ROWS) 
    #------------------------------------------------------------------------------- 
    
    $rowCount = $sth->rows; 
    printf("\n%d rows processed.\n\n", $rowCount); 
    
    #------------------------------------------------------------------------------- 
    # disconnect from the database 
    #------------------------------------------------------------------------------- 
    
    $rc  = $dbh->disconnect; 
    
    #------------------------------------------------------------------------------- 
    # return success to the OS 
    #------------------------------------------------------------------------------- 
    
    exit(0); 
    

    Referenced by...