This is Bavaria, Germany!
Here are my examplescripts ordered by themes:
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;
}
}
# 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"; |
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"; } |
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"; } |
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(); |
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; } |
my $dsn = "dbi:mysql:database=bookkeeping;host=10.1.1.1"; my $dbh = DBI->connect($dsn,"myusername","mypassword"); |