How to read/write BLOB in the Postgres DB from Perl DBI?
Hello, I'm porting a DBI-Mysql application to Postgres, but I've big problems with the BLOBs. I have beening searching the internet for a while now, but unfortunately couldn't find much information about how to read/write BLOB data from Perl DBI in the Postgres DB. Is there anyone who has dealt with this problem? If yes could you please show me how to do this? (A little example would be great) Many thanks in advance! best regards ZHU Jia
RE: :ODBC-0.28
Mr. Zhang, 0.29 or better should work, but get the latest from CPAN. Look at mytest/testinout.pl for examples. If you still have problems, please post to [EMAIL PROTECTED], as this list is for development discussions. Jeff Hi, I am a programmer working for Betasphere, in Silicon Valley. I have a question: I am trying to use bind_param_inout for the output parameters in either function or procedure I created in SQL server 2000. I always get this error message: Can't bind output values ( currently). I am using DBD::ODBC version 0.28 I also tried the exact examples mentioned in DBD::ODBC-0.29 developers release. It doesn't work for me either. So I am wondering whether verions DBD::ODBC-0.28 support bind_param_inout or not. Really appreciate if anyone can drop me a line. Zishan Zhang
PERL MODULE DBD::ODBC
Hi all, I am having a problem installing the above module (Version 0.32). I have succeeded in the past with a statically linked version but cannot get the dynamic one going. The driver I'm using is Redbrick (a data warehouse now provided by Informix (IBM)). The only tweak that I have made is a symbolic link from the rbsql*.h files provided with Redbrick to the required sql*.h files. I am attaching the make output along with version info. The tests fail with a libodbcinst.sl error. make.out If I have to go with a static link, then I will but I'd rather not. Can any one help? Thx, Ali Young make.out Description: Binary data
Re: Problems DBI/DBD::Oracle/Perl..
Michael A Chase wrote: Sorry for interuption, would you tell more about why the database handles should be openned in the children. I want to know more more about this. I just made some changes to an Perl/DBI application so that it can run long processes of executing big SQL statements. The change I made is that the only significant line in the children is $sth-execute(); And it works. Maybe it works now. I would not count on it without very extensive testing. Some databases, Oracle in particular, don't behave well with handles passed from parent to child. See InactiveDestroy in the manual (perldoc DBI). I use Solid version 2.2, not sure about Oracle though. BTW, I have another issue concerning DBI need to consult your gurus. According to the experts from the Solid company, for a statement if there are less than 10 values inside IN() in the WHERE clause, the solid version 2.2 engine will do unions by each value, and this will take about 3 minutes for one of my SQL statement when I run it from the Solid Editor. But, once there are more than 10 values inside IN(), for the same statement also running from the Solid editor it would take forever, because the solid engine in this case will scan the whole tables. The experts from the solid company told me I can change the default value by running the statement SET SQL CONVERTORTOUNIONS YES COUNT 100 After I ran the above SQL statement via the Solid Editor to the database, my own SQL statement with 21 values inside IN() only takes 6 minutes. But, the problem is it still takes about one hour if I run my statement via DBI Perl application. So, my question is, why there is a so big difference in time between the Solid Editor and the DBI application? Or DBI has its own strategy to optimize query plan? I noticed after the default value changed to 100, my DBI application runs better (1 hour) than before (endless). Any input from your gurus are highly appreciated. Best Bao -- BAO RuiXian, PROGRAMMER, Technologies Team, Project Services Group AtBusiness Communications Corp., Kaapeliaukio 1, FIN-00180 Helsinki tel. +358-9-2311 6674, mob. +358-50-329 6275, fax +358-9-2311 6601 http://www.atbusiness.com, email: [EMAIL PROTECTED]
Re: Create database redux
Glen, Whoops! Misspoke there the first time... I asked Is it possible without using the ODBC administrator? I meant, is it possible to create the databases in perl instead of first creating them in Access? Glen I think the 'cheat' posted in response to your original post was a really cool one and does exactly what you want: i) for 'creation' of a database you let Perl just copy a template, and ii) you connect to that database via a dns-less connection. However, if you even want to avoid that one time you create the database template manually with Access, you can do that remote controlling Access from within Perl using the Win32::OLE module. Access will stay in play anyways. HTH Bodo
RE: How to read/write BLOB in the Postgres DB from Perl DBI?
You set LongReadLen and then proceed as usual. We need to know your problem, before we can make an attempt to figure out what it is. Ilya -Original Message- From: ZHU Jia To: [EMAIL PROTECTED] Sent: 2/7/02 2:32 AM Subject: How to read/write BLOB in the Postgres DB from Perl DBI? Hello, I'm porting a DBI-Mysql application to Postgres, but I've big problems with the BLOBs. I have beening searching the internet for a while now, but unfortunately couldn't find much information about how to read/write BLOB data from Perl DBI in the Postgres DB. Is there anyone who has dealt with this problem? If yes could you please show me how to do this? (A little example would be great) Many thanks in advance! best regards ZHU Jia
RE: PERL MODULE DBD::ODBC
I suggest you use a driver manager, have DBD::ODBC link with the driver manager and have the driver manager load your driver. unixODBC or iODBC *should* work. As a side note, It looks like dld.sl can't find the path '../exe/libodbcinst.sl' It could be a problem that it's a relative path... Jeff -Original Message- From: YOUNG,ALEX (Non-HP-UnitedKingdom,ex2) [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 5:49 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: PERL MODULE DBD::ODBC Hi all, I am having a problem installing the above module (Version 0.32). I have succeeded in the past with a statically linked version but cannot get the dynamic one going. The driver I'm using is Redbrick (a data warehouse now provided by Informix (IBM)). The only tweak that I have made is a symbolic link from the rbsql*.h files provided with Redbrick to the required sql*.h files. I am attaching the make output along with version info. The tests fail with a libodbcinst.sl error. make.out If I have to go with a static link, then I will but I'd rather not. Can any one help? Thx, Ali Young
Re: msSQL Server AutoCommit / placeholder
What version of DBD::ADO are you using?
perl -MDBD::ADO -e 'print $DBD::ADO::$VERSION;'
Tom
On Wed, Feb 06, 2002 at 02:34:43PM +0100, jc plat wrote:
I want to set the AutoCommit property when opening my database
connection on a SQL server 7 on the same W2000 box.
I'm using ActiveState Perl 5.6.1.
I've tried the following:
my $dbh = DBI-connect('DBI:ADO:absolut','sa','',{ AutoCommit = 0 })
or die Couldn't connect to database : . DBI-errstr;
which give me the error:
Can't disable AutoCommit at C:/Perl/site/lib/DBI.pm line 433
Did anyone know how to resolv that problem ?
Then I also have trouble to use placeholders (?) in sql statement. Here is my
code:
$sth = $dbh-prepare(select * from Category where CategoryId=?);
$sth-execute('1');
and the error:
DBD::ADO::st execute failed: Can't execute statement 'select * from Category
where CategoryId=?': -2147217904: OLE exception from Microsoft OLE DB
Provider for ODBC Drivers:
[Microsoft][ODBC SQL Server Driver]Champ COUNT incorrect
Win32::OLE(0.1502) error 0x80040e10
in METHOD/PROPERTYGET Execute
-2147217904: at tmp.pl line 11.
Thanks
--
Thomas A. Lowery
See DBI/FAQ http://tlowery.hypermart.net
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Re: Perl DBI-connect
Are you sure you're using DBI?
If the password is incorrect, the connection should fail.
The behavior you describe is typical of Sqlplus.
Assuming that you have a scott/tiger account, the following script
should simply fail and report an error: ( change the value of $db first )
#!/usr/bin/perl
use warnings;
use DBI;
use strict;
my ($db, $username, $password) = qw{ts01 scott notiger};
my $dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{ RaiseError = 1, AutoCommit = 0, ora_session_mode = 0 }
);
die Connect to $db failed \n unless $dbh;
my $MySql=select \* from dual;
my $sth = $dbh-prepare($MySql);
$sth-execute;
my $href = $sth-fetchrow_hashref;
print DUMMY: $href-{DUMMY}\n;
$sth-finish;
$dbh-disconnect;
Jared
On Thursday 07 February 2002 08:06, Khamneian, Mahtaj wrote:
I have written a perl DBI script to check passwords of some 1500 users on
an oracle database. I have set up a loop to go through a list containing
userid:password of each user on a separate line. I use DBI-connect to
connect to the instance using the userid/password from the list. If
connection is successful, I record the info in a file and disconnect from
the db and check the next userid/passwd with another connect. The problem
is when the wrong password is supplied, oracle prompts for correct userid.
What oracle state is this(I cannot disconnect because I'm not connected)?
And how can I handle it in perl? Is there a more efficient way to do this
in perl than what I have explained above?
Thanks,
-
Mahtaj Khamneian
University of Missouri - ASP Phone : (573) 884-2281
1805 East Walnut Fax : (573) 884-3070
Columbia, MO 65201-6425 [EMAIL PROTECTED]
FW: CPAN Upload: J/JU/JURL/DBD-ODBC-0.34.tar.gz
There are some significant changes in this release.
Please note that some tests may fail or report they are
unsupported on this platform. Notably Oracle's ODBC driver
will fail the advanced binding tests in t/08bind2.t.
These tests run perfectly under SQL Server 2000. This is
normal and expected. Until Oracle fixes their drivers to
do the right thing from an ODBC perspective, it's going to
be tough to fix the issue.
Also note that some tests may be skipped, such as
t/09multi.t, if your driver doesn't seem to support
returning multiple result sets.
Recent updates:
DBD::ODBC 0.34
Further revamped tests to attempt to determine if SQLDescribeParam
will work to handle the binding types. The t/08bind.t attempts
to determine if SQLDescribeParam is supported. note that Oracle's
ODBC driver under NT doesn't work correctly when binding dates
using the ODBC date formatting {d } or {ts }. So, test #3 will
fail in t/08bind.t
New support for primary_key_info thanks to patches by Martin Evans.
New support for catalog, schema, table and table_type in table_info
thanks to Martin Evans. Thanks Martin for your work and your
continuing testing, suggestions and general support!
Support for upcoming dbi get_info.
Support for upcoming dbi foreign_key_info
DBD::ODBC 0.33_3
Revamped tests to include tests for multiple result sets.
The tests are ODBC driver platform specific and will be skipped
for drivers which do not support multiple result sets.
=item BDBD::ODBC 0.33_2
Finally tested new binding techniques with SQL Server 2000,
but there is a nice little bug in their MDAC and ODBC
drivers according to the knowledge base article # Q273813, titled
FIX: Incorrect Syntax near the Keyword 'by'
Error Message with Column Names of C, CA or CAS (Q273813)
DBD::ODBC now does not name any of the columns A, B, C, or D
they are now COL_A, COL_B, COL_C, COL_D.
*** NOTE: *** I AM STRONGLY CONSIDERING MAKING THE NEW
BINDING the default for future versions. I do not believe
it will break much existing code (if any) as anyone binding
to non VARCHAR (without the ODBC driver doing a good conversion
from the VARCHAR) will have a problem. It may be subtle, however,
since much code will work, but say, binding dates may not with
some drivers.
Please comment soon...
=item BDBD::ODBC 0.33_1
*** WARNING: ***
Changes to the binding code to allow the use of SQLDescribeParam
to determine if the type of column being bound. This is
experimental and activated by setting
$dbh-{odbc_default_bind_type} = 0; # before creating the query...
Currently the default value of odbc_default_bind_type = SQL_VARCHAR
which mimicks the current behavior. If you set
odbc_default_bind_type to 0, then SQLDescribeParam will be
called to determine the columen type. Not ALL databases
handle this correctly. For example, Oracle returns
SQL_VARCHAR for all types and attempts to convert to the
correct type for us. However, if you use the ODBC escaped
date/time format such as: {ts '1998-05-13 00:01:00'} then
Oracle complains. If you bind this with a SQL_TIMESTAMP type,
however, Oracle's ODBC driver will parse the time/date correctly.
Use at your own risk!
Fix to dbdimp.c to allow quoted identifiers to begin/end
with either or '.
The following will not be treated as if they have a bind placeholder:
isEstimated?
'01-JAN-1987 00:00:00'
'Does anyone insert a ?'
The uploaded file
DBD-ODBC-0.34.tar.gz
has entered CPAN as
file: $CPAN/authors/id/J/JU/JURL/DBD-ODBC-0.34.tar.gz
size: 225340 bytes
md5: ec689ff661842538741d16da19354d74
No action is required on your part
Request entered by: JURL (Jeff Urlwin)
Request entered on: Thu, 07 Feb 2002 16:24:13 GMT
Request completed: Thu, 07 Feb 2002 16:25:25 GMT
Virtually Yours,
Id: paused,v 1.74 2001/05/20 14:59:52 k Exp k
Topic Changes _Really_ Needs a New Subject
You are not doing yourself any favors by changing the topic without changing the subject and removing the non-applicable messages. Because I had been following this thread, I read your new comments, but anyone who might be able to help with Solid will skip the message. What's worse, I've been answering based on the assumption that the thread involved Oracle. It appears that has not been true since you chipped in. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. - Original Message - From: BAO RuiXian [EMAIL PROTECTED] To: Michael A Chase [EMAIL PROTECTED] Cc: ariel mastracchio [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, February 07, 2002 02:50 Subject: Re: Problems DBI/DBD::Oracle/Perl.. . . . I use Solid version 2.2, not sure about Oracle though. BTW, I have another issue concerning DBI need to consult your gurus. According to the experts from the Solid company, for a statement if there are less than 10 values inside IN() in the WHERE clause, the solid version 2.2 engine will do unions by each value, and this will take about 3 minutes for one of my SQL statement when I run it from the Solid Editor. But, once there are more than 10 values inside IN(), for the same statement also running from the Solid editor it would take forever, because the solid engine in this case will scan the whole tables. The experts from the solid company told me I can change the default value by running the statement SET SQL CONVERTORTOUNIONS YES COUNT 100 After I ran the above SQL statement via the Solid Editor to the database, my own SQL statement with 21 values inside IN() only takes 6 minutes. But, the problem is it still takes about one hour if I run my statement via DBI Perl application. So, my question is, why there is a so big difference in time between the Solid Editor and the DBI application? Or DBI has its own strategy to optimize query plan? I noticed after the default value changed to 100, my DBI application runs better (1 hour) than before (endless). Any input from your gurus are highly appreciated.
Re: Perl DBI-connect
From: Khamneian, Mahtaj [EMAIL PROTECTED]
To: Khamneian, Mahtaj [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, February 07, 2002 10:20
Subject: RE: Perl DBI-connect
Well, apparently my suspicion was not correct.
The following script fails with a segmentation fault on the second user.
The first user's id and password are not correct, so connect fails and the
script behaves as expected - it continues with the next userid/password. The
2nd password is correct, so the connect should be successful. In this case,
a message should be recorded in the log file, the current user disconnected,
script paused and restart with the next userid/password set. But the
connect fails and nothing is recorded. Is there something obvious in the
script that I am missing?
#!/usr/local/bin/perl -w
use DBI;
use Env qw(PATH ORACLE_HOME ORACLE_SID);
use strict;
use strict 'vars';
$PATH = ;
$ORACLE_HOME = /u01/app/oracle/product/806;
my ($user, $password, $sid);
my $line;
$sid = DBNAME;
my $filename = login_list;
# open log file for recording
open LOG, '/tmp/bd_passwd_list';
# Unbuffer LOG so none of the output is lost
select LOG; $| = 1; select STDOUT;
open(INPUT, $filename)
or die can't open $filename \n;
DBI-trace (0);
while($line = INPUT)
{
my @login=split(/:/,$line);
$user = $login[0];
$password = $login[1];
# connect to target db
print logging into $sid as $user/$password\n;
my $dbh = DBI-connect (dbi:Oracle:$sid, $user, $password, {
PrintError = 0, RaiseError = 0 });
if ($dbh)
{
print LOG $user is using birthday $password as password\n;
# You don't need to disconnect, each assignment to $dbh does that for you.
# Why sleep? I'd think you'd want to get through this as quickly as
possible.
$dbh-disconnect;
sleep 5;
}
}
close LOG;
close INPUT;
Here's the output:
logging into DBNAME as USER1/PASS1#this password is incorrect
logging into DBNAME as USER2/PASS2#this password is correct
Segmentation Fault
I don't think we really know where you are when the fault occurs. There may
be several lines lost in LOGs buffer.
Have you tried stepping through this with the Perl debugger (perl -d
script)?
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: PERL MODULE DBD::ODBC
YOUNG,ALEX (Non-HP-UnitedKingdom,ex2) wrote: Hi all, I am having a problem installing the above module (Version 0.32). I have succeeded in the past with a statically linked version but cannot get the dynamic one going. The driver I'm using is Redbrick (a data warehouse now provided by Informix (IBM)). The only tweak that I have made is a symbolic link from the rbsql*.h files provided with Redbrick to the required sql*.h files. There was someone a couple of days ago using just this driver. Is it on Linux ? if so it may be better to install to a ODBC Driver manager first. Nick
RE: Re: Re: Oracle DBI commit/rollback question...
Mac, Ok, I've got a problem with rollback(), I think. I'm not sure it's a database problem or if it's my code, but here's the output from my DBI script, and the code itself is attached (NOTE - I intentionally used the wrong column in my delete statement to force an error): AUTO_COMMIT is set to: AUTO_COMMIT is set to: create table t_dave_test as select trade_date, host_cust_id from cm_trade_fact where trade_id = 210960459_compile_ This SQL Statement was Processed Successfully. Return Status: 1 (Rows Effected or Error Code if function or procedure) AUTO_COMMIT is set to: delete from t_dave_test where trade_id = 'WA339482'_compile_ This SQL Statement Produced an Error... Rollback Status: 1 Database Execution Error: DBD::Oracle::db do failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute) at test.pl line 154. ROLLING BACK TRANSACTION... Disconnected from Database and Terminating. I probably shouldn't say ROLLING BACK TRANSACTION because the Rollback Status = 1. But I expect because AutoCommit = (I set it OFF) that the table I created in the first SQL statement would not exist. But it does!! So I don't know why it won't rollback. Can you offer any more suggestions? Is it perhaps a setting in Oracle which overrides DBI rollback()? -Dave- -Original Message- From: mchase Sent: Wednesday, February 06, 2002 5:58 PM To: Wren, David; dbi-users Cc: mchase Subject: Re: Re: Oracle DBI commit/rollback question... Please keep this on the list; I have added dbi-users to my response. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 14:41 Subject: RE: Re: Oracle DBI commit/rollback question... Thanks Mac, but what I'm doing is looping different DML statements through several calls to do() in the same database connection. You can prepare many statement handles with the same database handle. Then all you need is execute() at the appropriate points, not do() which includes prepare() as well. With placeholders, which I strongly recommend, a statement handle can be executed many times. I'm calling commit() or rollback() (depending on results from eval) after all DML statements have been passed, but before I disconnect. I'm wondering if only the last DML statement before error (or program completion) gets committed or rolled back, or if all statements do. A transaction is a unit of work, all DML in the transaction gets committed or rolledback together. -Original Message- From: mchase Sent: Wednesday, February 06, 2002 5:26 PM To: Wren, David; dbi-users Cc: mchase Subject: Re: Oracle DBI commit/rollback question... Until your execute() or do() the statement hasn't occurred yet, so there's nothing to commit or rollback. Each time you commit or rollback, a new transaction starts. Transactions are session specific, so the transactions in two open database handles in the same program are independent of each other. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 14:03 Subject: Oracle DBI commit/rollback question... Does anybody know how Oracle8i handles transactions from DML statements passed from a DBI database connection? Do the dbh-commit() and dbh-rollback() statements commit or rollback all DML instructions issued while the connection is open, or just those which get passed by sth-do() or sth-execute()? test.pl Description: Binary data Visit our website at http://www.ubswarburg.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments.
Re: How to read/write BLOB in the Postgres DB from Perl DBI?
Hi.
- Original Message -
From: ZHU Jia [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, February 07, 2002 6:32 PM
Subject: How to read/write BLOB in the Postgres DB from Perl DBI?
Hello,
I'm porting a DBI-Mysql application to Postgres, but I've big problems
with the BLOBs.
I have beening searching the internet for a while now, but unfortunately
couldn't find much information about how to read/write BLOB data from
Perl DBI in the Postgres DB.
Is there anyone who has dealt with this problem? If yes could you please
show me how to do this? (A little example would be great)
If your PostgreSQL has BYTEA type (maybe 7.1.3 or later),
you can use it.
If you say about large object of PostgreSQL, you should read
about func method of DBD::Pg. (with perldoc DBD::Pg)
[Ex. Using BYTEA]
CREATE TABLE ALBUM(
TITLE VARCHAR(80),
CREDT DATE,
PICIMG BYTEA,
PICTYP VARCHAR(80),
PRIMARY KEY (TITLE)
);
--
sub insData($$$) {
my ($sTitle, $sDate, $sPict) = @_;
my $hDb = DBI-connect(@aConn,
{RaiseError=1, AutoCommit=0}) or
die CONNECT ERROR $DBI::errstr;
eval {
my $hSt = $hDb-prepare(
q/INSERT INTO ALBUM (TITLE, CREDT, PICIMG)
VALUES (?, ?, ?)/);
$hSt-bind_param(3, undef, DBI::SQL_BINARY);
$hSt-execute($sTitle, $sDate, $sPict);
};
my $sRes;
if($@) {
$hDb-rollback;
$sRes = NG: $@;
}
else {
$hDb-commit;
$sRes = 'OK:';
}
$hDb-disconnect();
return $sRes;
}
---
sub getImg($) {
my ($sParam) = @_;
my $hDb = DBI-connect(@aConn,
{RaiseError=1, AutoCommit=0}) or
die CONNECT ERROR $DBI::errstr;
$hDb-{LongReadLen}=2_000_000;
$hDb-{LongTruncOk}=1;
my $hSt = $hDb-prepare(
q/SELECT PICIMG FROM ALBUM WHERE TITLE = ?/ );
$hSt-execute($sParam);
my ($sRes) = $hSt-fetchrow_array();
$hSt-finish();
$hDb-disconnect();
return $sRes;
}
[Ex. lo_creat, lo_write]
use strict;
use DBI;
my $hDb = DBI-connect('dbi:Pg:host=lins;dbname=test',
'scott', 'tiger', {RaiseError=1, AutoCommit=0}) or
die CONNECT ERROR $DBI::errstr;
$hDb-commit;
my $oId = $hDb-func($hDb-{pg_INV_WRITE}, 'lo_creat');
my $oFd = $hDb-func($oId, $hDb-{pg_INV_WRITE}, 'lo_open');
my $hSt = $hDb-prepare('INSERT INTO STIMG VALUES (?);');
open(IN, 'test.png');
my($sBuff, $iLen);
while($iLen = read(IN, $sBuff, 2048)){
$hDb-func($oFd, $sBuff, $iLen, 'lo_write');
}
close(IN);
$hDb-func($oFd, 'lo_close');
$hSt-execute($oId);
$hDb-commit;
$hDb-disconnect;
==
Kawai, Takanori(Hippo2000)
Mail: [EMAIL PROTECTED] [EMAIL PROTECTED]
http://member.nifty.ne.jp/hippo2000
==
Re: Re: Re: Oracle DBI commit/rollback question...
On Thu, Feb 07, 2002 at 04:31:08PM -0500, [EMAIL PROTECTED] wrote: I probably shouldn't say ROLLING BACK TRANSACTION because the Rollback Status = 1. But I expect because AutoCommit = (I set it OFF) that the table I created in the first SQL statement would not exist. But it does!! So I don't know why it won't rollback. Can you offer any more suggestions? Is it perhaps a setting in Oracle which overrides DBI rollback()? Selects, updates, and deletes are part of a transaction; they can be commited and rolled back. Creates and alters are not; they happen immediately and cannot be rolled back. Ronald
RE: Re: Oracle DBI commit/rollback question...
Looking at the Oracle 8 Server Concepts manual, a transaction is committed explicitly by a commit statement or implicity when a DDL statement is issued. My understanding is that a create table statement is a DDL statement. Thus an implied commit occurs at that point in your code and you can not roll back to a point in time before that. -dpf- --- David P. Fannin Database Administrator [EMAIL PROTECTED] UM-Rolla Computing and Information Services FAX (573) 341-4216 URL http://www.umr.edu/~dpf PHONE (573) 341-4841 --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 3:31 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Re: Oracle DBI commit/rollback question... Mac, Ok, I've got a problem with rollback(), I think. I'm not sure it's a database problem or if it's my code, but here's the output from my DBI script, and the code itself is attached (NOTE - I intentionally used the wrong column in my delete statement to force an error): AUTO_COMMIT is set to: AUTO_COMMIT is set to: create table t_dave_test as select trade_date, host_cust_id from cm_trade_fact where trade_id = 210960459_compile_ This SQL Statement was Processed Successfully. Return Status: 1 (Rows Effected or Error Code if function or procedure) AUTO_COMMIT is set to: delete from t_dave_test where trade_id = 'WA339482'_compile_ This SQL Statement Produced an Error... Rollback Status: 1 Database Execution Error: DBD::Oracle::db do failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute) at test.pl line 154. ROLLING BACK TRANSACTION... Disconnected from Database and Terminating. I probably shouldn't say ROLLING BACK TRANSACTION because the Rollback Status = 1. But I expect because AutoCommit = (I set it OFF) that the table I created in the first SQL statement would not exist. But it does!! So I don't know why it won't rollback. Can you offer any more suggestions? Is it perhaps a setting in Oracle which overrides DBI rollback()? -Dave- -Original Message- From: mchase Sent: Wednesday, February 06, 2002 5:58 PM To: Wren, David; dbi-users Cc: mchase Subject: Re: Re: Oracle DBI commit/rollback question... Please keep this on the list; I have added dbi-users to my response. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 14:41 Subject: RE: Re: Oracle DBI commit/rollback question... Thanks Mac, but what I'm doing is looping different DML statements through several calls to do() in the same database connection. You can prepare many statement handles with the same database handle. Then all you need is execute() at the appropriate points, not do() which includes prepare() as well. With placeholders, which I strongly recommend, a statement handle can be executed many times. I'm calling commit() or rollback() (depending on results from eval) after all DML statements have been passed, but before I disconnect. I'm wondering if only the last DML statement before error (or program completion) gets committed or rolled back, or if all statements do. A transaction is a unit of work, all DML in the transaction gets committed or rolledback together. -Original Message- From: mchase Sent: Wednesday, February 06, 2002 5:26 PM To: Wren, David; dbi-users Cc: mchase Subject: Re: Oracle DBI commit/rollback question... Until your execute() or do() the statement hasn't occurred yet, so there's nothing to commit or rollback. Each time you commit or rollback, a new transaction starts. Transactions are session specific, so the transactions in two open database handles in the same program are independent of each other. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 14:03 Subject: Oracle DBI commit/rollback question... Does anybody know how Oracle8i handles transactions from DML statements passed from a DBI database connection? Do the dbh-commit() and dbh-rollback() statements commit or rollback all DML instructions issued while the connection is open, or just those which get passed by sth-do() or sth-execute()?
RE: Re: Oracle DBI commit/rollback question...
Thanks. (I'm used to using Sybase... I din't know that) What about inserts? Aren't they also part of a transaction? -Dave- -Original Message- From: dpf Sent: Thursday, February 07, 2002 4:48 PM To: Wren, David Cc: dpf; dbi-users Subject: RE: Re: Oracle DBI commit/rollback question... Looking at the Oracle 8 Server Concepts manual, a transaction is committed explicitly by a commit statement or implicity when a DDL statement is issued. My understanding is that a create table statement is a DDL statement. Thus an implied commit occurs at that point in your code and you can not roll back to a point in time before that. -dpf- --- David P. Fannin Database Administrator [EMAIL PROTECTED] UM-Rolla Computing and Information Services FAX (573) 341-4216 URL http://www.umr.edu/~dpf PHONE (573) 341-4841 --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 3:31 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Re: Oracle DBI commit/rollback question... Mac, Ok, I've got a problem with rollback(), I think. I'm not sure it's a database problem or if it's my code, but here's the output from my DBI script, and the code itself is attached (NOTE - I intentionally used the wrong column in my delete statement to force an error): AUTO_COMMIT is set to: AUTO_COMMIT is set to: create table t_dave_test as select trade_date, host_cust_id from cm_trade_fact where trade_id = 210960459_compile_ This SQL Statement was Processed Successfully. Return Status: 1 (Rows Effected or Error Code if function or procedure) AUTO_COMMIT is set to: delete from t_dave_test where trade_id = 'WA339482'_compile_ This SQL Statement Produced an Error... Rollback Status: 1 Database Execution Error: DBD::Oracle::db do failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute) at test.pl line 154. ROLLING BACK TRANSACTION... Disconnected from Database and Terminating. I probably shouldn't say ROLLING BACK TRANSACTION because the Rollback Status = 1. But I expect because AutoCommit = (I set it OFF) that the table I created in the first SQL statement would not exist. But it does!! So I don't know why it won't rollback. Can you offer any more suggestions? Is it perhaps a setting in Oracle which overrides DBI rollback()? -Dave- -Original Message- From: mchase Sent: Wednesday, February 06, 2002 5:58 PM To: Wren, David; dbi-users Cc: mchase Subject: Re: Re: Oracle DBI commit/rollback question... Please keep this on the list; I have added dbi-users to my response. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 14:41 Subject: RE: Re: Oracle DBI commit/rollback question... Thanks Mac, but what I'm doing is looping different DML statements through several calls to do() in the same database connection. You can prepare many statement handles with the same database handle. Then all you need is execute() at the appropriate points, not do() which includes prepare() as well. With placeholders, which I strongly recommend, a statement handle can be executed many times. I'm calling commit() or rollback() (depending on results from eval) after all DML statements have been passed, but before I disconnect. I'm wondering if only the last DML statement before error (or program completion) gets committed or rolled back, or if all statements do. A transaction is a unit of work, all DML in the transaction gets committed or rolledback together. -Original Message- From: mchase Sent: Wednesday, February 06, 2002 5:26 PM To: Wren, David; dbi-users Cc: mchase Subject: Re: Oracle DBI commit/rollback question... Until your execute() or do() the statement hasn't occurred yet, so there's nothing to commit or rollback. Each time you commit or rollback, a new transaction starts. Transactions are session specific, so the transactions in two open database handles in the same program are independent of each other. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 14:03 Subject: Oracle DBI commit/rollback question... Does anybody know how Oracle8i handles transactions from DML statements passed from a DBI database connection? Do the dbh-commit() and dbh-rollback() statements commit or rollback all DML instructions issued while the connection is open, or just those which get passed by sth-do() or sth-execute()? Visit our website at http://www.ubswarburg.com This message contains confidential information and is
Re: Re: Oracle DBI commit/rollback question...
INSERT, UPDATE, and DELETE are DML. Please at least browse the Oracle concepts manual. Your Oracle DBA can tell you where to find it. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, February 07, 2002 13:53 Subject: RE: Re: Oracle DBI commit/rollback question... Thanks. (I'm used to using Sybase... I din't know that) What about inserts? Aren't they also part of a transaction?
Re: Re: Re: Oracle DBI commit/rollback question...
On Thu, Feb 07, 2002 at 04:41:31PM -0500, Ronald J Kimball wrote: Selects, updates, and deletes are part of a transaction; they can be commited and rolled back. Creates and alters are not; they happen immediately and cannot be rolled back. Selects? Oops. :D I didn't think that explanation through completely. Rolling back a select wouldn't even make sense. I should have said inserts, updates, and deletes. Ronald
Re: DBI-1.21 DBI.xs patch
On Thu, Feb 07, 2002 at 07:12:12PM +0300, Andrew Ivanov wrote:
Dear Tim,
I receive the error Can't read $DBI::err, last handle unknown or destroyed
during global destruction many times (and for $DBI::errstr too).
I'm not able to say what is the reason of these errors. Setting the trace levelel
to 10 didn't help, and trace files were the same for erroneous and correct runs.
Have any DBI handles been used before $DBI::err is used?
Does it happen in when the program exits?
Does it happen in an error handler?
Which DBD?
Please try to cut down the code to the smallest part that reproduces the problem.
I would like to ask you to apply this patch to DBI.xs, because you've mentioned
this: /* warn() may be changed to a debug later */
I would like to ask you to apply patch provided, which do exactly that thing.
But I'd rather not sweep something under the carpet without knowing what it is!
In occasion, I would like to thank you for this great module. Thanks!
Thanks!
Tim.
p.s. Instead of $DBI::err you can use $h-err.
Best regards,
Andrew Ivanov.
--- DBI.xs.orig Thu Feb 7 18:27:39 2002
+++ DBI.xsThu Feb 7 18:29:10 2002
@@ -2960,8 +2960,10 @@
SvROK_off(DBI_LAST_HANDLE);
XSRETURN(1);
}
-if ( !ok ) { /* warn() may be changed to a debug later */
- warn(Can't read $DBI::%s, last handle unknown or destroyed, meth);
+if ( !ok ) {
+ if (trace) {
+ PerlIO_printf(DBILOGFP, Can't read $DBI::%s, last handle unknown or
destroyed\n, meth);
+ }
XSRETURN_UNDEF;
}
Re: DBI
Hi I've seen this problem on a box I have that has a couple of different perl (and module) installs on it. Either you have 2 versions of perl installed and you're picking up part of DBI from the wrong one or (more likely) you have had an older version of DBI installed at some time and the new version didn't overwrite one of the files in the ../auto/DBI/ directory. What the error is telling you is that the binary portion of DBI which the module is trying to autoload isn't the version that is required. i.e. your DBI module is version 1.15 but it's found the headers / libraries or binaries for 1.14. If you only have one perl install on the box, the safest bet is to completely get rid of DBI and reinstall it. Places to look for parts of the module : /opt/perl5/lib/site_perl/5.6.1/PA-RISC2.0/DBI/* /opt/perl5/lib/site_perl/5.6.1/PA-RISC2.0/DBI.pm /opt/perl5/lib/site_perl/5.6.1/PA-RISC2.0/auto/DBI/* The above paths are based on perl installed in /opt/perl5 on and HPUX PA RISC 2 (K Class) machine. The version of perl is 5.6.1. Your path will change depending on your platform, perl version and install path. Hope this helps. Paul *** Important. Confidentiality: This communication is intended for the above-named person and may be confidential and/or legally privileged. Any opinions expressed in this communication are not necessarily those of the company. If it has come to you in error you must take no action based on it, nor must you copy or show it to anyone; please delete/destroy and inform the sender immediately. Monitoring/Viruses Orange may monitor all incoming and outgoing emails in line with current legislation. Although we have taken steps to ensure that this email and attachments are free from any virus, we advise that in keeping with good computing practice the receipient should ensure they are actually virus free. Orange PCS Limited is a subsidiary of Orange SA and is registered in England No 2178917, with its address at St James Court, Great Park Road, Almondsbury Park, Bradley Stoke, Bristol BS32 4QJ. ***
API suggestions
Hi,
I think the new table_info, column_info etc methods for DBI are
really useful, but I was wondering whether there are any plans for some
less verbose/more simplistic API's for retrieving 'data dictionary' type
information, eg I think it would be useful to have something like:
my @columnNames = $dbh-columnsNamesForTable($schema, $table);
That would give me the column names for a given table,
or something like:
if ($dbh-columnIsNullable($schema, $table, $column){ ...
Now of course, I can probably write wrappers around the existing methods
that exist, but it would be great to get support at the DBI level for
these types of queries. Currently I do the bad (= non-portable) thing of
looking directly in the Oracle database dictionary for these types of
information.
By the way, thanks for the new DBI version!
Cheers,
Gavin
___
Gavin Sherlock
Dept. of Genetics
Center for Clinical Sciences Research
269 Campus Drive,
Room 2255b,
Stanford,
CA 94305-5166
Tel: 650 498 6012
Fax: 650 723 7016
Re: ANNOUNCE: DBI 1.21
How do I install it in Window 2000 using PPM ?
- Original Message -
From: Tim Bunce [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, February 06, 2002 7:41 PM
Subject: ANNOUNCE: DBI 1.21
file: $CPAN/authors/id/T/TI/TIMB/DBI-1.21.tar.gz
size: 208384 bytes
md5: c781eee2559de5e4a72e28a8120cb1d9
Changes in DBI 1.21,7th February 2002
The minimum supported perl version is now 5.005_03.
Fixed DBD::Proxy support for AutoCommit thanks to Jochen Wiedmann.
Fixed DBI::ProxyServer bind_param(_inout) handing thanks to Oleg
Mechtcheriakov.
Fixed DBI::ProxyServer fetch loop thanks to [EMAIL PROTECTED]
Fixed install_driver do-the-right-thing with $@ on error. It, and
connect(),
will leave $@ empty on success and holding the error message on error.
Thanks to Jay Lawrence, Gavin Sherlock and others for the bug report.
Fixed fetchrow_hashref to assign columns to the hash left-to-right
so later fields with the same name overwrite earlier ones
as per DBI 1.15, thanks to Kay Roepke.
Changed tables() to use quote_indentifier() if the driver returns a
true value for $dbh-get_info(29) # SQL_IDENTIFIER_QUOTE_CHAR
Changed ping() so it no longer triggers RaiseError/PrintError.
Changed connect() to not call $class-install_driver unless needed.
Changed DESTROY to catch fatal exceptions and append to $@.
Added ISO SQL/CLI ODBCv3 data type definitions thanks to Steffen
Goeldner.
Removed the definition of SQL_BIGINT data type constant as the value is
inconsistent between standards (ODBC=-5, SQL/CLI=25).
Added $dbh-column_info(...) thanks to Steffen Goeldner.
Added $dbh-foreign_key_info(...) thanks to Steffen Goeldner.
Added $dbh-quote_identifier(...) insipred by Simon Oliver.
Added $dbh-set_err(...) for DBD authors and DBI subclasses
(actually been there for a while, now expanded and documented).
Added $h-{HandleError} = sub { ... } addition and/or alternative
to RaiseError/PrintError. See the docs for more info.
Added $h-{TraceLevel} = N attribute to set/get trace level of handle
thus can set trace level via an (eg externally specified) DSN
using the embedded attribute syntax:
$dsn = 'dbi:DB2(PrintError=1,TraceLevel=2):dbname';
Plus, you can also now do: local($h-{TraceLevel}) = N;
(but that leaks a little memory in some versions of perl).
Added some call tree information to trace output if trace level = 3
With thanks to Graham Barr for the stack walking code.
Added experimental undocumented $dbh-preparse(), see t/preparse.t
With thanks to Scott T. Hildreth for much of the work.
Added Fowler/Noll/Vo hash type as an option to DBI::hash().
Documentation changes:
Added DBI::Changes so now you can perldoc DBI::Changes, yeah!
Added selectrow_arrayref selectrow_hashref docs thanks to Doug Wilson.
Added 'Standards Reference Information' section to docs to gather
together all references to relevant on-line standards.
Added link to poop.sourceforge.net into the docs thanks to Dave Rolsky.
Added link to hyperlinked BNF for SQL92 thanks to Jeff Zucker.
Added 'Subclassing the DBI' docs thanks to Stephen Clouse, and
then changed some of them to reflect the new approach to subclassing.
Added stronger wording to description of $h-{private_*} attributes.
Added docs for DBI::hash().
Driver API changes:
Now a COPY of the DBI-connect() attributes is passed to the driver
connect() method, so it can process and delete any elements it wants.
Deleting elements reduces/avoids the explicit
$dbh-{$_} = $attr-{$_} foreach keys %$attr;
that DBI-connect does after the driver connect() method returns.
As you can see, this is a major release.
Enjoy!
Tim.
ANN: PPM for DBI 1.20 available
A ppm package is now available. ppm install http://www.xmlproj.com/PPM/DBI-1_20.ppd Ilya
DBD-Oracle-1.12 for HP-UX 11 (B.11.00 U 9000/800) - Problem building
Dear Dbi-users, I am having trouble building the above DBD and I have NOT a clue what the error messages are telling me. I built Perl 5.6.1 using gcc 3.0 and then built DBI-1.20 successfully. make test failed with the following errors: Can't shl_load() a library containing Thread Local Storage: /usr/lib/libpthread.1/usr/lib/dld.sl: Exec format error t/base..ok 1/5 t/base..ok 2/5 t/base..ok 3/5 t/base..FAILED tests 4-5Failed 2/5 tests, 60.00% Summary of my perl5 (revision 5.0 version 6 subversion 1) configuration: Platform: osname=hpux, osvers=11.00, archname=PA-RISC2.0 uname='hp-ux hpmgt003 b.11.00 u 9000800 134424616 unlimited-user license ' config_args='-de -Uusemymalloc' hint=recommended, useposix=true, d_sigaction=define usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef useperlio=undef d_sfio=undef uselargefiles=undef usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef Compiler: cc='gcc', ccflags ='-D_HPUX_SOURCE -L/lib/pa1.1 -DUINT32_MAX_BROKEN -fno-strict-aliasing', optimize='-O', cppflags='-D_HPUX_SOURCE -L/lib/pa1.1 -DUINT32_MAX_BROKEN -fno-strict-aliasing' ccversion='', gccversion='3.0', gccosandvers='hpux11.00' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=4 alignbytes=8, usemymalloc=n, prototype=define Linker and Libraries: ld='ld', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib libs=-lnsl -lnm -lndbm -lmalloc -ldld -lm -lc -lndir -lcrypt -lsec perllibs=-lnsl -lnm -lmalloc -ldld -lm -lc -lndir -lcrypt -lsec libc=/lib/libc.sl, so=sl, useshrplib=false, libperl=libperl.a Dynamic Linking: dlsrc=dl_hpux.xs, dlext=sl, d_dlsymun=undef, ccdlflags='-Wl,-E -Wl,-B,deferred ' cccdlflags='-fpic', lddlflags='-b -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: Built under hpux Compiled at Feb 8 2002 10:12:03 @INC: /opt/perl5/lib/5.6.1/PA-RISC2.0 /opt/perl5/lib/5.6.1 /opt/perl5/lib/site_perl/5.6.1/PA-RISC2.0 /opt/perl5/lib/site_perl/5.6.1 /opt/perl5/lib/site_perl . I would appreciate any help on what to do next from you experts out there. Thanks, Kent Szeto Capacity Management Hosting Internet/ISP Solution Infrastructure Planning/Infrastructure Management Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Re: Perl DBI-connect
- Original Message - From: Bart Lateur [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 07, 2002 19:14 Subject: Re: Perl DBI-connect On Thu, 7 Feb 2002 12:14:02 -0800, Michael A Chase wrote: # You don't need to disconnect, each assignment to $dbh does that for you. If only with a warning. You get a warning if you let the program exit without disconnecting. Letting $dbh go out of scope at the end of the loop might avoid the warning, but I'm not completely sure. It is definitely not necessary to call disconnect() every time through the loop. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
