#!/usr/local/bin/perl # ##### Work produced by LEX Solutions, Inc. # http://www.lexsolutions.com # # FILE NAME: # blob # # DESCRIPTION: # Inserts or retrieves a BLOB from an Oracle database. # See usage for details. # # This program requires Perl version 5.004_04, plus the # DBI and DBD::Oracle libraries. See www.cpan.org for details. # # HISTORY: # Date Developer Version Description Of Changes Made # ---- --------- ------- --------------------------- # 1/1999 Ed Hughes 1.0 Original # use DBI; ## Parameters $doDebug = 0; $| = 1; # set stdout to flush $LONG_RAW_TYPE=24; # Oracle type id for blobs # Get args ($operation, $connectString, $sqlStmt, $blobFileName) = @ARGV; # Check args if ( (($operation ne "-select") && ($operation ne "-insert")) || (($operation eq "-insert") && (! "$blobFileName")) || (($operation eq "-insert") && (! grep(/\:blob/, $sqlStmt))) ) { print STDERR < ed2.jpg EOF exit(-1); } # Parse connect string ($tmp, $dbName) = split(/\@/, $connectString); ($dbUser, $dbPasswd) = split(/\//, $tmp); $doDebug && print "user/passwd\@db = $dbUser/$dbPasswd\@$dbName \n"; # Connect to DB $dataSource = "dbi:Oracle:${dbName}"; # interface:driver:db_name $doDebug && print "dataSource = $dataSource"; $db = DBI->connect($dataSource, $dbUser, $dbPasswd); $db || die "Error connecting to db: $DBI::errstr\n"; &blobSelect() if ($operation eq "-select"); &blobInsert() if ($operation eq "-insert"); exit(0); sub blobInsert() { open(BLOB, "$blobFileName"); $bytes = 0; $bytes = read(BLOB, $buf, 500000); print STDERR "Read $bytes bytes...\n"; close(BLOB); $stmt = $db->prepare($sqlStmt) || die "\nPrepare error: $DBI::err .... $DBI::errstr\n"; # Bind variable. Note that long raw (blob) values must have their attrib explicitly specified $attrib{'ora_type'} = $LONG_RAW_TYPE; $stmt->bind_param(":blob", $buf, \%attrib); $stmt->execute() || die "\nExecute error: $DBI::err .... $DBI::errstr\n"; print STDERR "Complete.\n"; } sub blobSelect() { $db->{LongReadLen}=500000; # Make sure buffer is big enough for BLOB $stmt = $db->prepare($sqlStmt) || die "\nPrepare error: $DBI::err .... $DBI::errstr\n"; $stmt->execute() || die "\nExecute error: $DBI::err .... $DBI::errstr\n"; $row = 0; while ($blob = $stmt->fetchrow) { printf STDERR "Fetching row %d \n", $row++; print STDOUT $blob; } $stmt->finish(); if (!$row) { print STDERR "No data found.\n"; } else { print STDERR "Complete.\n"; } }