Home

Search this site:
 

Horshack’s
very
own site

 

This is Bavaria, Germany!
You will find links to Perl,
bootdisks for system administration,
a lot of Linux,
Windows administration
and some personal belongings.

Databases with Perl

Here are my examplescripts ordered by themes:

 

Checking in Perl wether a table in a database exist

sub does_table_exist {
    # check wether tables exists in the database
    my $dbh = shift;
    my $tablename = shift;
    eval { $dbh->do("select count(*) from $tablename where 1=0") };
    if ($dbh->err) {
        # Table does not exist
        print "Table $tablename does not exist.\n";
        return 0;
    }
    else {
        # Table exists
        return 1;
    }
}

Accessing databases with (perl-)DBI under Windows and Unix

# Example for connecting to a database
use CGI;
use DBI;
my $debug = 1;

# $ENV{\'ORACLE_HOME\'}="/oracle/u01/app/oracle/product/8.0.5";
$ENV{\'ORACLE_HOME\'} = "d:\\\\orant";

# eventually because of german umlauts
# $ENV{\'NLS_LANG\'}="GERMAN_GERMANY.WE8ISO8859P1";
my ( $user, $pass ) = ( "username12", "secret" );

# dbi:Oracle, dbi:mysql, dbi:ODBC, ...
my $dsn = "dbi:Oracle:$dbname";

# DBnames under Unix are usually capitalized
my $dbname = "KUNDAT";

my @feldnamen;
$dbh = DBI->connect( $dbname, $user, $pass, $dbd )
  || die "Could not establish connection to $dbname: $DBI::errstr";

$sql = "select knr, name from kv_sta " . "where knr like \\\'09576%\\\'";

print "sql is *** $sql ***<br>\\n" if $debug;
$cur = $dbh->prepare($sql);
$cur->execute
  || die "Error in prepare: $DBI::err ... $DBI::errstring";

# find out names of the columns
@fieldname = @{ $cur->{NAME} };

print "The columns are: @fieldname\\n" if $debug;
while ( (@row) = $cur->fetchrow ) {
    print "Resultline is: @row\\n" if $debug;
}
$cur->finish();

# close database connection
$dbh->disconnect();

print "Ready with $0\\n";

Accessing an Access-database with ODBC
only by using a filename

use strict;
use DBI;

my $access_fqn  = "phrase.mdb";
my $access_info =
  sprintf \'Driver=Microsoft Access Driver (*.mdb);dbq=%s;Exclusive=1\',
  $access_fqn;
my $dsn = "dbi:ODBC:" . $access_info;

my ( $user, $pass ) = ( undef, undef );
my $dbh = DBI->connect( $dsn, $user, $pass )
  || die "Error. Could not establish connection to db: $DBI::errstr";

# for blobs define that:
$dbh->{LongReadLen} = 2e3;

my $sql = "select * from phrasebook";
my $cur = $dbh->prepare($sql)
  || die "Cannot prepare SQL: $sql";
$cur->execute;

# find out fieldnames
my @fieldnames = @{ $cur->{NAME} };

while ( my $row = $cur->fetchrow_hashref ) {
    foreach my $fieldname (@fieldnames) {
        printf "%s: %s\\n", $fieldname, $row->{$fieldname};
    }
    print "---\\n";
}

Insert to an Oracle database-table with a "long raw" column

Requirement: everything you need for accessing your database from perl: dbi, dbd-oracle (see above).

The difficulty is that long-raw-columns cannot be filled and requested by a usual inert- or select-statement. You see this in SQL-Plus:

SQL> create table goodslongraw
(goods_id integer not null,goods_description long raw);

Tabelle created.

SQL> insert into goodslongraw (goods_id,goods_description) values (1,\'test\');

Error in line 1:
ORA-00984: \'test\' non-valid hexadecimalnumber

Long raw-columns are not handled like simple text-columns. In Perl we have to work with an SQL-statement with Parameter: insert into goodslongraw (:1,:2), :1 und :2 get values. :2 will also get the "kind of value":

$sql = "insert into goodslongraw values (:1,:2)";
$cur=$dbh->prepare();
...
print "declare :2\\n";
$attrib{\'ora_type\'} = 24; # type 24 = long raw, 8 = long
$cur->bind_param(2,"addresses of beerdrinkers in southern bavaria",\\%attrib)
|| die($dbh->errstr);

 

# file: longraw_example.pl
use CGI;
use DBI;
my $debug = 1;
### Oracleinstallation is here
# $ENV{\'ORACLE_HOME\'}="/oracle/u01/app/oracle/product/8.0.5";
$ENV{\'ORACLE_HOME\'} = "e:\\\\orant";

# evtl. because of german umlauts
# $ENV{\'NLS_LANG\'}="GERMAN_GERMANY.WE8ISO8859P1";
my ( $user, $pass ) = ( "myuser", "mypass" );

# DBnames under Unix are usually capitalized
my $dsn = "dbi:Oracle:SALES";
my @feldnamen;
&tellme("Try to connect to database $user\\@$dsn ...");
$dbh = DBI->connect( $dsn, $user, $pass )
  || die "Could not connect to database $dsn : $DBI::errstr.";

# create table with long raw columns
&tellme("create table with a long raw column");
$sql =
    "create table goodslongraw ("
  . "goods_id integer not null,"
  . "goods_description long raw)";
print "prepare for \\\'$sql\\\'\\n";
$cur = $dbh->prepare($sql);
print "execute for SQL-statement\\n";
$cur->execute
  || die "Prepare-Error: $DBI::err .... $DBI::errstring";

# Insert in table with "long raw"
&tellme("executing SQL-statement \\\'insert\\\' with long raw.");
$sql =
  "insert into goodslongraw (goods_id,goods_description) " . "values(:1,:2)";
print "prepare for $sql\\n";
$cur = $dbh->prepare($sql) || die( $dbh->errstr );

# tell the content of (:1)
print "declare :1\\n";
$cur->bind_param( 1, 116 ) || die( $dbh->errstr );

# tell the content AND type of (:2)
print "declare :2\\n";
$attrib{\'ora_type\'} = 24;    # Type 24 = long raw, 8 = long
$cur->bind_param( 2, "adresses of beerdrinkers in southern bavaria", \\%attrib )
  || die( $dbh->errstr );
print "execute for SQL-statement\\n";
$cur->execute()
  || die( $dbh->errstr );
$dbh->commit;

# fetch data again
&tellme("fetch data again.");
$sql = "select goods_id, goods_description from goodslongraw";
$cur = $dbh->prepare($sql);
$cur->execute() || die( $dbi->errstr );
while ( ( $id, $desc ) = $cur->fetchrow ) {
    print "id/desc: $id/$desc\\n";
}

# drop testtable
&tellme("drop testtable");
$sql = "drop table goodslongraw";
$cur = $dbh->prepare($sql);
$cur->execute() || die( $dbi->errstr );
$cur->finish();

# Close database connection
$db->disconnect();
print "Ready with $0\\n";
exit;

sub tellme {
    my ($sag_es) = @_;
    print "*** $sag_es\\n";
}

Getting data from memo-fields in Access

First: you CAN get larger data from memo-fields in Perl with ODBC! The situation is that you usually get text without problems from a memo-field, but if the memo-field reaches a special border of characters in the field perl thinks that this was the last row.

use strict;
use DBI;

# open database
my ( $user, $pass ) = ( undef, undef );
my $dsn = "dbi:ODBC:wwwdata";
my $dbh = DBI->connect( $dsn, $user, $pass )
  || die "Could not establish connection to $dsn $DBI::errstr";

# tell DBI to read larger blob-fields
print "Read BLOBs (memo etc.)-values until $length_blob\\ bytes.\\n";
my $length_blob = 2e3;
$dbh->{LongReadLen} = $length_blob;

# get data
my $sql = "select number, blobtext from blobtable";
my $cur = $dbh->prepare($sql);
$cur->execute()
  || die "Prepare-error: $DBI::err .... $DBI::errstring";

# find out names of the columns
@fieldnames = @{ $cur->{NAME} };

while ( my $line = $cur->fetchrow_hashref ) {
    foreach (@fieldnames) {
        printf "%s: %s\\n", $_, $line->{$_};
    }
}
$cur->finish();

# close database connection
$dbh->disconnect();

 

 Subroutines for checking wether a table exists
and number of rows in a table

use strict;
use DBI;

[...]

my ($table) = "accounts";
if ( &does_table_exist( $dbh, $table ) ) {
    print "Table exists and has "
      . &how_many_records( $dbh, $table )
      . "records.\\n";
}

exit;

sub does_table_exist {
    my ( $dbh, $whichtable ) = @_;
    my ( $table, @alltables, $found );
    @alltables = $dbh->tables();
    $found     = 0;
    foreach $table (@alltables) {
        $found = 1 if ( $table eq $test_tabelle );
    }

    # return true if table was found
    # return false if not found
    return $found;
}

sub how_many_records {
    my ( $dbh, $whichtable ) = @_;
    my ( $result, $cur, $sql, @row );

    $sql = "select count(*) from $whichtable";
    $cur = $dbh->prepare($sql);
    $cur->execute();
    @row    = $cur->fetchrow;
    $result = $row[0];
    $cur->finish;
    return $result;
}

 

 Using a DSN to reach remote databases with mysql

my $dsn = "dbi:mysql:database=bookkeeping;host=10.1.1.1"; 
my $dbh = DBI->connect($dsn,"myusername","mypassword");


 
Impressum