DBD::Oracle error when switching between utf8 and non-utf8
Hi list.
I believe I have tripped a DBD::Oracle bug in the way it binds utf8
parameters. If I create a statement and execute it with a non-utf8
parameter, it works. If I then execute that *same sth* with a utf8
parameter (scalar with UTF8 flag on), I receive the following error:
ORA-01460: unimplemented or unreasonable conversion requested
Example:
use Encode qw(decode);
my $sth = $dbh-prepare(select ? from dual) || die $dbh-errstr;
my $non_utf8 = X;
$sth-execute($non_utf8) || die $sth-errstr;
my $utf8 = decode('utf8', $non_utf8);
$sth-execute($utf8) || die $sth-errstr; # DIES
The problem does *NOT* occur when:
1) the order is reversed (utf8 before non-utf8) or
2) the statement is prepared again before the second execute or
3) NLS_LANG does not indicate utf8 or
4) ora_csform is specified as SQLCS_NCHAR or SQLCS_IMPLICIT.
I made a script for the test suite to reproduce the problem. You can
get it from
http://www.pgarrett.net/rebind_nchar.t
Is this the appropriate forum for this kind of thing? Should I post
elsewhere also?
Thanks much,
Philip
All of my DBI and DBD::Oracle tests passed.
Perl: 5.8.3
DBI: 1.4.1
DBD::Oracle: 1.17
OS: Linux 2.6.5
Oracle server: 9.2.5
Oracle client: 9.2.4
Oracle database charset: US7ASCII
Oracle nchar charset: AL16UTF16
NLS_LANG env var: AMERICAN_AMERICA.AL32UTF8
Here are the bits from DBI trace that looked relevant:
First execution (non-utf8):
- execute for DBD::Oracle::st (DBI::st=HASH(0x84121f8)~0x8416818
'X') thr#814f008
bind :p1 == 'X' (type 0)
rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p1 == 'X' (size 1/2/0, ptype 4, otype 1)
bind :p1 == 'X' (size 1/1, otype 1, indp 0, at_exec 1)
bind :p1 == 'X' (in, not-utf8, csid 1-0-1, ftype 1, csform
0-0, maxlen 1, maxdata_size 0)
Second execution (utf8):
- execute for DBD::Oracle::st (DBI::st=HASH(0x84121f8)~0x8416818
X) thr#814f008
bind :p1 == X (type 0)
rebinding :p1 (is-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p1 == X (size 1/2/0, ptype 4, otype 1)
bind :p1 == 'X' (size 1/1, otype 1, indp 0, at_exec 1)
rebinding :p1 with UTF8 value so setting csform=SQLCS_IMPLICIT
bind :p1 == X (in, is-utf8, csid 1-0-871, ftype 1, csform 0-2,
maxlen 1, maxdata_size 0)
dbd_st_execute SELECT (out0, lob0)...
in ':p1' [0,0]: len 1, ind 0
OCIErrorGet after OCIStmtExecute (er1:ok): -1, 1460: ORA-01460:
unimplemented or unreasonable conversion requested
RE: DBD::Oracle error when switching between utf8 and non-utf8
-Original Message- From: Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] Sent: Friday, March 03, 2006 1:50 PM To: dbi-users@perl.org Subject: DBD::Oracle error when switching between utf8 and non-utf8 [snip] DBI: 1.4.1 Probably obvious, but I meant 1.41. Philip
RE: DBD::Oracle error when switching between utf8 and non-utf8
-Original Message- From: John Scoles [mailto:[EMAIL PROTECTED] Sent: Monday, March 06, 2006 8:54 AM To: dbi-users@perl.org Subject: Re: DBD::Oracle error when switching between utf8 and non-utf8 Just ran the same test in the windows environment and the test did fail as follows t\rebind_nchar..skipped all skipped: Unable to encode utf8 I will have to look into it further in windows anyway, Unfortunately, that's probably just my inexperience with the encoding modules showing through. There may be a way to make it work in Windows, but I don't know off the top of my head what it is. Philip
RE: Binding undefs in DBD::Oracle
[Dbi-dev is for driver maintainers, not for general support questions.
I reposted to dbi-users.]
-Original Message-
From: Paul G. Weiss [mailto:[EMAIL PROTECTED]
Sent: Monday, March 06, 2006 11:47 AM
To: dbi-dev@perl.org
Subject: Binding undefs in DBD::Oracle
I always that the binding undef's in DBD::Oracle was the way to bind a
null, yet:
DB5 x $dbh-selectall_arrayref('select x,b from tbl where a=?',
undef,
4)
0 ARRAY(0x9b53010)
0 ARRAY(0x9ba6284)
0 'bbc0dfec7ba578f2dafc8f2eb42fbd3ae17cf300'
1 undef
but
DB3 x $dbh-selectall_arrayref('select x,b from tbl where a=? and
b=?', undef, 4, undef) 0 ARRAY(0x9ba0530)
empty array
i.e. the 'b=?' in the statement where I bind the value to undef does
_not_ act like 'b is null'.
Didn't this used to work? I've tried this with DBD::Oracle v1.16 and
1.17.
-P
No, that won't work. There is a section dedicated to this in the DBI
manual,
under the heading NULL Values.
http://search.cpan.org/~timb/DBI-1.50/DBI.pm#Placeholders_and_Bind_Value
s
I usually use (b = ? or (b is null and ? is null)).
Philip
RE: DBD::Oracle error when switching between utf8 and non-utf8
-Original Message- From: John Scoles [mailto:[EMAIL PROTECTED] Sent: Monday, March 06, 2006 11:29 AM To: dbi-users@perl.org Subject: Re: DBD::Oracle error when switching between utf8 and non-utf8 Well find a few things The windows error comes from the fact that I did not have Encode.pm installed so I fixed that and it runs. It also looks like this may be the old issue related to DBI not being fully compatible with UTF8 . Check out this link http://www.mhonarc.org/archive/html/perl-unicode/2003-12/msg00013.html Perhaps. I'd be surprised if that was the case, though, because that deals with variables that DBI/DBD *create*. This might help. This is what DBI-trace says when I bind the utf8 before ever having bound a non-utf8: rebinding :p1 with UTF8 value so setting csform=SQLCS_IMPLICIT bind :p1 == X (in, is-utf8, csid 1-0-871, ftype 1, csform 0-2, maxlen 1, maxdata_size 0) But when I've bound a non-utf8 to that statement before, this is what I get when binding utf8: rebinding :p1 with UTF8 value so setting csform=SQLCS_IMPLICIT bind :p1 == X (in, is-utf8, csid 873-0-873, ftype 1, csform 0-2, maxlen 1, maxdata_size 0) I don't really know anything about OCI, so I can't interpret the difference between 1-0-871 and 873-0-873. Does that mean anything to you? Thanks, Philip
RE: DBI::ODBC in a web application
-Original Message- From: Robert Hicks [mailto:[EMAIL PROTECTED] Sent: Thursday, March 23, 2006 7:13 AM To: dbi-users@perl.org Subject: DBD::ODBC in a web application If I create a test script to connect to a Microsoft Access database that is on a network (UNC) drive I can connect to it fine. If I try to do the same thing from a web application running under Apache I get the following error: Error executing run mode 'display_task_page': Can't connect to data source driver=Microsoft Access Driver (*.mdb);dbq=\\Resolver\Users\BH- icks\Public\Project_task_report_2003.mde, no database driver specified and DBI_DSN env var not set at C:/Perl/site/lib/CGI/Application/Plugin/DBH.pm line 42 at D:/Projects/www/DEV/exp/index.cgi line 14 I have tried mapping that UNC path to a local drive letter as well with the same error. I am not sure if the problem resides with Apache, the modules I am using for the web application of DBD::ODBC itself. Any pointers? It looks like the dbi:ODBC:your_datasource_name parameter isn't making it into the DBI-connect call (although some part of it is). If you enable DBI-trace(1), the call to DBI-connect will go into your Apache error_log, so you can see exactly what is getting passed to the connect method. Philip
RE: forcing utf8 on selected values
What database are you using? Perhaps the driver has an option to do this. I
know Oracle does.
-Original Message-
From: Mark Hedges [mailto:[EMAIL PROTECTED]
Sent: Tue 3/28/2006 9:39 PM
To: dbi-users@perl.org
Subject: forcing utf8 on selected values
There are several to-do items regarding utf8 that would be
great, but aren't here yet.
My database has utf8 columns, server/client character sets and
collation. Yet when I call fetchrow, resulting Perl scalars do
not have the utf8 flag set. That's really annoying. Am I
supposed to call
map { utf8::upgrade($_) } $every,$stupid,$little,$scalar
every time I do a select statement? Or map through the results
of any selectall_*ref's? This fixes the display problems when
printing a page to a browser with charset utf-8.
Is there an easier way to do this? Please tell me there is.
Mark
RE: Problem on Solaris 8 64-bit.
-Original Message- From: Rhugga Harper [mailto:[EMAIL PROTECTED] Sent: Monday, April 03, 2006 10:39 AM To: Jonathan Leffler Cc: dbi-users@perl.org Subject: Re: Problem on Solaris 8 64-bit. On 3/30/06, Jonathan Leffler [EMAIL PROTECTED] wrote: On 3/30/06, Rhugga Harper [EMAIL PROTECTED] wrote: I'm running Oracle 10.2.0.1 on Solaris 8 64-bit. I running DBI 1.50, DBD::Oracle 1.16, and Perl 5.8.7. When I run a script that uses DBD::Oracle, it complains about wrong ELF class: Can't load '/usr/local/lib/perl5/site_perl/5.8.7/sun4-solaris/auto/DBD/Oracle/Oracl e.so' for module DBD::Oracle: ld.so.1: snapshot_tracker: fatal: /u01/app/oracle/product/10.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64 at /usr/local/lib/perl5/5.8.7/sun4-solaris/DynaLoader.pm line 230. at ./snapshot_tracker line 10 Compilation failed in require at ./snapshot_tracker line 10. BEGIN failed--compilation aborted at ./snapshot_tracker line 10 Even if I set LD_LIBRARY_PATH=/u01/app/oracle/product/10.2/lib32 in my shell environment and also explicitly set this using $ENV inside my script it still complains. If I copy the 32-bit client library into the /u01/app/oracle/product/10.2/lib directory my perl scripts work but then sqlplus is broken. (and subsequently all my shell scripts) [snip] This is a 32-bit version of perl. Building a 64-bit version isn't an option as it would take months to get it certified for deployment onto production gear. (assuming of course that it will pass certification) Is there any way to force perl itself or anything I can do with each script to have it use $ORACLE_HOME/lib32. LD_LIBRARY_PATH is being ignored. If you run: LD_DEBUG=libs perl -MDBD::Oracle -e1 You'll see what paths the dynamic linker is searching for the Oracle libs. Maybe that will help. Philip
RE: Not exactly a dbi question
-Original Message-
From: Jonathan Mangin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 04, 2006 10:37 AM
To: dbi-users@perl.org
Subject: Not exactly a dbi question
Hi all,
I hope someone here can help me.
The DBD::mysql docs seem to say that mysql_affected_rows is still a
viable attribute. I have:
$sql = update user
set type = ?,
last_name = ?,
state_id = ?,
org_id = ?,
email = ?
where id = ?;
$sth = $dbh-prepare($sql);
$sth-execute($user_type, $last_name, $state_id, $org_id,
$email, $user_id) || die
Cannot update: . $sth-errstr();
$result = $sth-{'mysql_affected_rows'};
but $result is always undefined though execute() is successful. I
_think_ $result is supposed to be the number of rows affected. What am
I doing wrong and/or is there a better way?
The execute() method returns the number of rows affected for DML
(insert/update/delete) statements, e.g.
my $rows = $sth-execute($sql) || die can't update: .
$sth-errstr;
print Updated $rows rows.\n;
The || die stuff still works even when 0 rows are affected because in
that case, it returns 0 but true.
Philip
RE: Not exactly a dbi question
-Original Message- From: Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 10:52 AM To: dbi-users@perl.org Subject: RE: Not exactly a dbi question -Original Message- From: Jonathan Mangin [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 10:37 AM To: dbi-users@perl.org Subject: Not exactly a dbi question [snip] but $result is always undefined though execute() is successful. I _think_ $result is supposed to be the number of rows affected. What am I doing wrong and/or is there a better way? The execute() method returns the number of rows affected for DML (insert/update/delete) statements, e.g. my $rows = $sth-execute($sql) || die can't update: . $sth-errstr; print Updated $rows rows.\n; Err, that should be $sth-execute(). Philip
RE: Not exactly a dbi question
-Original Message-
From: Jonathan Mangin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 04, 2006 3:14 PM
To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
Subject: Re: Not exactly a dbi question
The execute() method returns the number of rows affected for DML
(insert/update/delete) statements, e.g.
my $rows = $sth-execute($sql) || die can't update: .
$sth-errstr;
print Updated $rows rows.\n;
The || die stuff still works even when 0 rows are affected because
in that case, it returns 0 but true.
Philip
Thanks, Philip, that works though not as expected.
Running the following snippet multiple times:
my $user_id = 'harvey';
my $user_type = 'PAT';
my $last_name = 'Wallbanger';
my $state_id = '1';
my $org_id = 'IN2';
my $email = '[EMAIL PROTECTED]';
my $sql = update user
set type = ?,
last_name = ?,
state_id = ?,
org_id = ?,
email = ?
where id = ?;
my $sth = $dbh-prepare($sql);
my $result = $sth-execute($user_type, $last_name, $state_id,
$org_id, $email, $user_id) || die
Cannot update: . $sth-errstr(); my
$action = defined($result) ? 'MOD' : undef;
print $result\n;
print $action\n;
always returns '1' and 'MOD' whether an update actually occurred or
not. How can I define $action only upon an actual update? Perhaps I
need to upgrade?
No upgrade necessary. The $result will *always* be defined unless there
was an
error. Otherwise, it contains a numeric value for the number of rows
affected
(which also happens to always evaluate to true in boolean context).
Try this:
my $rows = $sth-execute(...) || die $sth-errstr;
if ($rows == 0) {
print No rows updated.\n;
}
else {
printf Updated $rows rows.\n;
}
Philip
RE: Not exactly a dbi question
-Original Message-
From: Jonathan Mangin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 04, 2006 3:14 PM
To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
Subject: Re: Not exactly a dbi question
The execute() method returns the number of rows affected for DML
(insert/update/delete) statements, e.g.
my $rows = $sth-execute($sql) || die can't update: .
$sth-errstr;
print Updated $rows rows.\n;
The || die stuff still works even when 0 rows are affected
because
in that case, it returns 0 but true.
Philip
Thanks, Philip, that works though not as expected.
Running the following snippet multiple times:
my $user_id = 'harvey';
my $user_type = 'PAT';
my $last_name = 'Wallbanger';
my $state_id = '1';
my $org_id = 'IN2';
my $email = '[EMAIL PROTECTED]';
my $sql = update user
set type = ?,
last_name = ?,
state_id = ?,
org_id = ?,
email = ?
where id = ?;
my $sth = $dbh-prepare($sql);
my $result = $sth-execute($user_type, $last_name, $state_id,
$org_id, $email, $user_id) || die
Cannot update: . $sth-errstr(); my
$action = defined($result) ? 'MOD' : undef;
print $result\n;
print $action\n;
always returns '1' and 'MOD' whether an update actually occurred
or
not. How can I define $action only upon an actual update? Perhaps
I
need to upgrade?
No upgrade necessary. The $result will *always* be defined unless
there
was an
error. Otherwise, it contains a numeric value for the number of
rows
affected
(which also happens to always evaluate to true in boolean context).
Try this:
my $rows = $sth-execute(...) || die $sth-errstr;
if ($rows == 0) {
print No rows updated.\n;
}
else {
printf Updated $rows rows.\n;
}
OK. Tried that. Always says:
Updated 1 row.
The data never changed. The record was not truly updated.
The timestamp verifies this. Now what?
Did you commit()?
Philip
RE: Not exactly a dbi question
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 4:20 PM To: Jonathan Mangin; Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: Re: Not exactly a dbi question On 4/4/06 15:06, Jonathan Mangin [EMAIL PROTECTED] wrote: Autocommit is on. Perhaps you're misunderstanding. An update is not supposed to happen if the column to be updated is equal to the data being stuffed into it. I want $row to reflect that but $row is always 1 even when an update did not happen. Is it broke? --Jon No, the default changed: 2003-06-22 Rudy Lippan [EMAIL PROTECTED] (2.9002) * moved pod into mysql.pm from mysql.pod * Changed the default behaviour of mysql_found_rows, so now 'UPDATE table set field=?' will return the number of rows matched and not the number of rows physically changed. You can get the old behaviour back by adding mysql_found_rows=0 to the dsn passed to connect. Sorry, I've been answering your question while assuming the mysql driver conformed to the documented DBI interface. Perhaps this is a mysql thing, not a DBD::mysql thing? Oh well. Philip
RE: Not exactly a dbi question
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 2:55 AM To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: Re: Not exactly a dbi question On 4/4/06 15:31, Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] wrote: -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 4:20 PM To: Jonathan Mangin; Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: Re: Not exactly a dbi question On 4/4/06 15:06, Jonathan Mangin [EMAIL PROTECTED] wrote: Autocommit is on. Perhaps you're misunderstanding. An update is not supposed to happen if the column to be updated is equal to the data being stuffed into it. I want $row to reflect that but $row is always 1 even when an update did not happen. Is it broke? --Jon No, the default changed: 2003-06-22 Rudy Lippan [EMAIL PROTECTED] (2.9002) * moved pod into mysql.pm from mysql.pod * Changed the default behaviour of mysql_found_rows, so now 'UPDATE table set field=?' will return the number of rows matched and not the number of rows physically changed. You can get the old behaviour back by adding mysql_found_rows=0 to the dsn passed to connect. Sorry, I've been answering your question while assuming the mysql driver conformed to the documented DBI interface. Perhaps this is a mysql thing, not a DBD::mysql thing? Perhaps. What is the non-conformity to the documented DBI interface to which you refer? The DBI pod says: For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns 0E0, which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then execute returns -1. The OP said that execute() was returning 1, whether the row was affected or not. Philip
RE: Not exactly a dbi question
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 10:41 AM To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: Re: Not exactly a dbi question [snip] Sorry, I've been answering your question while assuming the mysql driver conformed to the documented DBI interface. Perhaps this is a mysql thing, not a DBD::mysql thing? Perhaps. What is the non-conformity to the documented DBI interface to which you refer? The DBI pod says: For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns 0E0, which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then execute returns -1. The OP said that execute() was returning 1, whether the row was affected or not. I don't think there is an error here unless the meaning of affected becomes defined more precisely. For non-SELECT statements, rows affected can mean either rows matched (regardless of whether actually changed) or rows changed. The default for MySQL is the rows-changed value, and that was also the default for DBD::mysql until the 2.9002 change. The default for DBD::mysql now is the rows-matched value. Does the DBI spec require some particular interpretation of affected? (The JDBC spec requires the rows-matched value.) It's making more sense now. I'm a little confused though -- how does a DML operation have a different number for rows-matched and rows-changed? Isn't the point of DML to change all rows matched? I'll go looking at the JDBC docs for an explanation. Philip
RE: Not exactly a dbi question
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 10:56 AM To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: Re: Not exactly a dbi question On 4/5/06 9:46, Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] wrote: -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 10:41 AM To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: Re: Not exactly a dbi question [snip] Sorry, I've been answering your question while assuming the mysql driver conformed to the documented DBI interface. Perhaps this is a mysql thing, not a DBD::mysql thing? Perhaps. What is the non-conformity to the documented DBI interface to which you refer? The DBI pod says: For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns 0E0, which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then execute returns -1. The OP said that execute() was returning 1, whether the row was affected or not. I don't think there is an error here unless the meaning of affected becomes defined more precisely. For non-SELECT statements, rows affected can mean either rows matched (regardless of whether actually changed) or rows changed. The default for MySQL is the rows-changed value, and that was also the default for DBD::mysql until the 2.9002 change. The default for DBD::mysql now is the rows-matched value. Does the DBI spec require some particular interpretation of affected? (The JDBC spec requires the rows-matched value.) It's making more sense now. I'm a little confused though -- how does a DML operation have a different number for rows-matched and rows-changed? Isn't the point of DML to change all rows matched? I'll go looking at the JDBC docs for an explanation. It's most easily seen for a statement such as this: UPDATE tbl_name SET col_name = 0 WHERE col_name = 0; If you've selected the rows-changed count, $sth-rows() will always return 0, because the statement doesn't actually change any col_name value from its current value. If you've selected the rows-matched count, $sth-rows() will return the number of rows for which col_name is 0. Thanks for the explanation. I'm glad we had this discussion, because I'm sure that would have bitten me had I been using JDBC or MySQL. I tend to think that the row is still affected in a logical sense. Whether the DBMS backend decides to physically write a row that hasn't actually changed really isn't any of my business. It gets even more confusing if there's a trigger on the table. In that case, a trigger could be fired even though the row wasn't physically updated (with Oracle, anyway). Philip
RE: Oracle
No, DBD::Oracle requires the Oracle client libraries to be installed. There are other options, though, depending on your requirements: * DBD::Proxy - requires another system that DOES have DBD::Oracle (or, you could proxy through a Windows server that has an ADO driver for Oracle) * DBD::JDBC - In theory, can connect without the Oracle client if you use Oracle's thin JDBC driver Philip -Original Message- From: Maniace Libi - lmania [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 12:09 PM To: dbi-users@perl.org Subject: DBD:Oracle I have a developer who wants DBD::ORACLE installed on a Solaris server. There is not an instance of Oracle installed on this server. He is certain that it can be installed, but I can't find information supporting this. It always errors out with wanting Oracle_home. Is there a way to install DBD without oracle installed. Thanks Libi *** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.
RE: Semantics of InactiveDestroy
-Original Message-
From: Aleksander Adamowski [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 18, 2006 4:39 AM
To: dbi-users@perl.org
Subject: Semantics of InactiveDestroy
Hi!
I have a problem with parsing the documentation regarding the
InactiveDestroy attribute on DB handles.
Citing the documentation:
InactiveDestroy (boolean) The InactiveDestroy attribute can be
used to disable the *database engine* related effect of DESTROYing
a handle (which would normally close a prepared statement or
disconnect from the database etc). The default value, false, means
a handle will be fully destroyed when it passes out of scope.
For a database handle, this attribute does not disable an
*explicit* call to the disconnect method, only the implicit
call from DESTROY that happens if the handle is still marked
as Active.
Think of the name as meaning 'treat the handle as not-Active
in the DESTROY method'.
This attribute is specifically designed for use in Unix
applications that fork child processes. Either the parent or
the child process, but not both, should set InactiveDestroy
on all their shared handles. Note that some databases,
including Oracle, don't support passing a database connection
across a fork.
To help tracing applications using fork the process id is
shown in the trace log whenever a DBI or handle trace() method
is called. The process id also shown for *every* method call
if the DBI trace level (not handle trace level) is set high
enough to show the trace from the DBI's method dispatcher,
e.g. = 9.
What's missing here is a more detailed description of what actually
happens if I set it to non-default value (which is more interesting).
I suspect by negation, that setting it to true would fully destroy
the handle (or partly) destroy the handle when it goes out of scope,
but there should be a clear explanation what it actually does.
It's actually the reverse. It means, in practice, don't *automatically*
call disconnect() when the $dbh is destroyed. It can save you from some
pretty nasty trouble when you fork processes. You don't want your child
process exiting to close your parent's database connection. That's what
this solves.
$dbh-{InactiveDestroy} = 1;# safe(r) for forking
Philip
RE: last insert id
-Original Message- From: Dr.Ruud [mailto:[EMAIL PROTECTED] Sent: Friday, April 21, 2006 6:37 AM To: dbi-users@perl.org Subject: Re: last insert id [snip] I did not say it extracts the colons, and I did not say it returns the connect string with the embedded colons, and I did not say ... etc. Whatever anchors are there, should be used, as I already pointed at in my first reaction. Since, you haven't supplied a correction to your code. So we're back at: /\A(?:mysql|pg)\z/i /\A(?i:mysql|pg)\z/ If you insist on carrying on this ridiculous off-topic conversation, please take it off list. Thanks, Philip
RE: last insert id
-Original Message- From: Ronald J Kimball [mailto:[EMAIL PROTECTED] Sent: Friday, April 21, 2006 10:38 AM To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: RE: last insert id Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] wrote: Whatever anchors are there, should be used, as I already pointed at in my first reaction. Since, you haven't supplied a correction to your code. So we're back at: /\A(?:mysql|pg)\z/i /\A(?i:mysql|pg)\z/ If you insist on carrying on this ridiculous off-topic conversation, please take it off list. I must have missed a memo... It's now off-topic to discuss code that uses DBI on the dbi-users list? (Grudgingly replies to closed thread) This has nothing to do with DBI. It's regular expression pedantry. Philip
RE: DBD module loading problem
- From: Kevin Moore [mailto:[EMAIL PROTECTED] Sent: Sun 4/23/2006 12:15 AM To: dbi-users@perl.org Subject: DBD module loading problem Objective - execute perl modules from apache that access an oracle database Oracle database - 10gr2, SUSE SLES9 linux Apache server - RHAS 4.0, oracle 10gr2 instant client Perl Version perl -v This is perl, v5.8.5 built for i386-linux-thread-multi Apache Version apachectl -v Server version: Apache/2.0.55 Server built: Feb 28 2006 10:15:42 mod_perl, DBI, DBD versions mod_perl -2.0.2 DBI-1.50 DBD-Oracle 1.17 Apache DBI-0.9901 Error when running perl module from apache error_log file [Sat Apr 22 20:48:04 2006] emahni.pl: install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread- multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libnnz10.so: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread- multi/DynaLoader.pm line 230. ORACLE_HOME, LD_LIBRARY_PATH ($ORACLE_HOME/lib), TWO_TASK, and LD_RUN_PATH($ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib) set in httpd.conf Have you added PassEnv and PerlPassEnv directives for all of these variables? You'll need them for things to work right. See: http://tinyurl.com/kunxp [httpd.apache.org] http://tinyurl.com/jr7dw [perl.apache.org] hth, Philip
RE: problems getting Hebrew strings from Oracle
-Original Message-
From: Sagiv Barhoom [mailto:[EMAIL PROTECTED]
Sent: Friday, April 28, 2006 11:28 AM
To: dbi-users@perl.org
Subject: problems getting Hebrew strings from Oracle
Hi all,
I have 2 machines: linux_perl_machine and sql_server (Oracle 9i).
I am trying to connect from the perl machine to the sql_server and
execute :
select Hebrew from Hebrew_Table ,
but all I get is somthing like: ' ??'
Non-latin character sets are represented with question marks by some
terminal
emulators (SecureCRT for one). You haven't said exactly where you're
seeing
??. Is it in your terminal window? In the browser in some
web
application?
One way to find out if you're actually getting the right strings back
from the
database is to use Data::Dumper like this:
use Data::Dumper;
while (my ($hebrew) = $sth-fetchrow) {
print Dumper($hebrew);
}
If you see '$VAR1 = ??' then you probably have a client
encoding
problem (see below).
If you see something like '$VAR1 = \x{12c}\x{25a}...' (the idea being
the
characters are Unicode, not actual question marks) then your problem is
simply
the program you're using to view the query results.
The perl machine is not local and I work on it via ssh.
The db encoding is logical and the linux_perl_machine is:
LANG=he_IL.UTF-8
[snip]
These will affect how Perl handles locales. They don't really affect the
Oracle client, though. You'll need to set NLS_LANG to get Oracle working
right, e.g.:
export NLS_LANG=HEBREW_ISRAEL.AL32UTF8
# might also be necessary:
export NLS_NCHAR=AL32UTF8
See
http://search.cpan.org/~pythian/DBD-Oracle-1.17/Oracle.pm#Perl_and_Unico
de
hth,
Philip
RE: DBI-data_sources('Oracle')
-Original Message-
From: Loo, Peter # PHX [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 2:15 PM
To: DBI users
Subject: DBI-data_sources('Oracle')
Hi All,
Will someone please tell me where this module is getting the
information from? One of my severs that I am trying to connect to is
NOT listed in the output of:
@databases = $dbh-data_sources('Oracle');
Thanks.
Peter
This will tell you for sure:
export DBI_TRACE=1
perl -MDBI -le 'print foreach DBI-data_sources(Oracle)'
Philip
RE: DBD-Oracle.ppd
-Original Message-
From: Cole, Ben [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 09, 2006 10:45 AM
To: dbi-users@perl.org
Subject: DBD-Oracle.ppd
Hello,
I have been desperately searching for a 5.8.* DBD-Oracle.ppd for quite
some time now. The machine on which I need to install it has no
internet connection. From what I can tell ftp.esoftmatic.com no longer
exists, and Ive been looking on http://www.cedet.dk/perl/, but for
some reason the .ppd links for DBD-Oracle do not work, they simply
give me this email address. Any assistance you can provide would be
much appreciated.
If you're running Windows, you can get to Oracle through ADO. You won't
have all the features of the DBD::Oracle driver, but you can at least
access the database.
You'll need to have the Oracle client installed. After that, you'll need
to make a Data Source (Control Panel-Administrative Tools-Data Sources
(ODBC)). Then, use DBD::ADO to connect to the database.
my $dbh = DBI-connect('dbi:ADO:your_odbc_data_source_name',...)
|| die $DBI::errstr;
my $sth = $dbh-prepare(select * from dual);
hth,
Philip
RE: :XBase, STDOUT, and IO issue
-Original Message- From: Mark Galbreath [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 9:11 AM To: dbi-users@perl.org Subject: DBD::XBase, STDOUT, and IO issue Hi Guys, I'm using a subclass of DBI called DBD::XBase for reading Oralce DBF tablespace datafiles, translating them into delimited text files, and then loading them into MySQL with DBI. The problem is the only way DBD:XBase can output the text file with field delimiters I need is it slurps the whole file to STDOUT. So I did this (don't laugh): my $table = new XBase; open STDOUT, data.txt; $table-dump_records( fs = | ); close STDOUT; This does exactly what I need, except that now STDOUT is closed for further output (like for print statements), and if I do not close it, all STDOUT goes to the data file (I told you not to laugh!). I searched all night and cannot find an example of how to do this correctly. Capture the table dump's STDOUT with IO::Pipe somehow? The documentation of IO::Pipe is pretty sparse. Any suggestion is greatly appreciated. This should do it: use IO::Handle; no warnings 'once'; # perl doesn't see the 2nd ref in the string # temporarily replace STDOUT open( SAVED_STDOUT, STDOUT ) or die can't dup stdout: $!; open( OUT_FILE, , data.txt) or die can't create file: $!; STDOUT-fdopen(fileno(OUT_FILE), w) || die can't fdopen: $!; # print data to temporary STDOUT $table-dump_records( fs = | ); # restore STDOUT open( STDOUT, SAVED_STDOUT ) or die can't dup saved: $!; # close/flush data file close(OUT_FILE) || die can't close: $!; Hth, Philip
RE: :XBase, STDOUT, and IO issue
-Original Message- From: Dr.Ruud [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 11:08 AM To: dbi-users@perl.org Subject: Re: :XBase, STDOUT, and IO issue Garrett, Philip: Mark Galbreath: I searched all night and cannot find an example of how to do this correctly. Capture the table dump's STDOUT with IO::Pipe somehow? The documentation of IO::Pipe is pretty sparse. Any suggestion is greatly appreciated. This should do it: use IO::Handle; no warnings 'once'; # perl doesn't see the 2nd ref in the string # temporarily replace STDOUT open( SAVED_STDOUT, STDOUT ) or die can't dup stdout: $!; open( OUT_FILE, , data.txt) or die can't create file: $!; STDOUT-fdopen(fileno(OUT_FILE), w) || die can't fdopen: $!; # print data to temporary STDOUT $table-dump_records( fs = | ); # restore STDOUT open( STDOUT, SAVED_STDOUT ) or die can't dup saved: $!; # close/flush data file close(OUT_FILE) || die can't close: $!; Doesn't select() do what you need? perldoc -f select Yeah, you're right. I don't know XBase, so I just assumed it was explicitly printing to STDOUT (which select() wouldn't help with). I just now looked at the XBase code and it does use the default filehandle. So, Mark, this will do it too: select(OUT_FILE); $table-dump_records(...); select(STDOUT);
RE: install_driver(Pg) failed: Can't load Pg.so
-Original Message- From: louis gonzales [mailto:[EMAIL PROTECTED] Sent: Thursday, May 11, 2006 2:36 PM To: pgsql-admin@postgresql.org; dbi-users@perl.org Subject: install_driver(Pg) failed: Can't load Pg.so OS = Solaris 9 PERL v = 5.8.7 postgreSQL = 8.0.1 I'm getting the following error when attempting to interface through my web browser, immediately after supplying the login information. Software error: [snip] fatal: libgcc_s.so.1: open failed: No such file or directory ^ You need to install libgcc. http://www.sunfreeware.com/programlistsparc9.html#libgcc34 Philip
RE: install_driver(Pg) failed: Can't load Pg.so
-Original Message- From: louis gonzales [mailto:[EMAIL PROTECTED] Sent: Thursday, May 11, 2006 2:52 PM To: Garrett, Philip (MAN-Corporate) Cc: dbi-users@perl.org Subject: Re: install_driver(Pg) failed: Can't load Pg.so Garrett, Philip (MAN-Corporate) wrote: From: louis gonzales [mailto:[EMAIL PROTECTED] OS = Solaris 9 PERL v = 5.8.7 postgreSQL = 8.0.1 I'm getting the following error when attempting to interface through my web browser, immediately after supplying the login information. Software error: [snip] fatal: libgcc_s.so.1: open failed: No such file or directory ^ You need to install libgcc. http://www.sunfreeware.com/programlistsparc9.html#libgcc34 Thanks for the reply. I've got bash-2.05# pkginfo -i | grep gcc application SMCgcc gcc bash-2.05# pkginfo -l SMCgcc PKGINST: SMCgcc NAME: gcc CATEGORY: application ARCH: sparc VERSION: 3.3.2 BASEDIR: /usr/local VENDOR: Free Software Foundation PSTAMP: Steve Christensen INSTDATE: Feb 12 2006 23:02 EMAIL: [EMAIL PROTECTED] STATUS: completely installed FILES: 2274 installed pathnames 8 shared pathnames 6 linked files 157 directories 58 executables 780481 blocks used (approx) and I _still_ need libgcc? Nah, you should'nt. Libgcc should come along with gcc. Make sure that /usr/local/lib is in your LD_LIBRARY_PATH. Philip
RE: how to invoke .sql file from dbi
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 23, 2006 2:40 PM
To: DBI-Users
Subject: how to invoke .sql file from dbi
Hello all,
We have bunch of sql files ( .sql ) that we want to invoke from dbi (
Oracle ). How does it work. Dbi expects actual sql statements to be
given as it's arguments whether in case of do() or prepare() methods.
I wonder is there any option to just point the dbi to a sql file and
dbi taking care of it.
No, there isn't.
I do understand that running sql file is sqlplus feature and may not
be applicable to dbi, as dbi is more generic. We have hundreds of sql
files to be run like this. Each sql file has multiple sql statements
within it.
I know these are my options..
1) Parse sql files and assign each sql statement to string variable
and pass the string as an argument to do(). File handler routines
may come in handy for this.
This is undesirable if your SQL files have anything other than VERY
simple
SQL. For example, if your SQL files contain semicolons embedded in
quotes,
your parser will have to handle that. Also, if the files are written
specifically for sqlplus, they may contain directives to sqlplus (as
opposed to
the database) -- bind variables are one example.
2) Do not use DBI. Instead call 'sqlplus' from perl itself with file
as input parameter.
This is probably both safest and easiest. Rather than spawning a new
sqlplus for every sql file, though, you could just open a pipe to
sqlplus and
feed it the files from within perl, e.g.
use File::Copy;
open(my $SQLPLUS, '|sqlplus user/[EMAIL PROTECTED]') or die $!;
foreach (@files) {
copy($_, $SQLPLUS);
}
close($SQLPLUS);
hth,
Philip
RE: perl- dbi
-Original Message- From: Jonathan Leffler [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 31, 2006 12:02 PM To: R, Rajsekar Cc: dbi-users@perl.org Subject: Re: perl- dbi On 5/31/06, R, Rajsekar [EMAIL PROTECTED] wrote: how do i ensure that DBI is installed in my machine.. will it be automatically installed when perl is installed... i am getiing error when i start using use DBI; and i need to connet to ORACLE DATABASE. You've received a few workable answers - but there's a better one. perl -MDBI -e 'print $DBI::VERSION\n' Or this: perl -MDBI -e 'DBI-installed_versions' which tells you what DBD modules are installed, and the versions of pretty much anything that matters to DBI, as someone recently pointed out to me. E.g.: $ perl -MDBI -e 'DBI-installed_versions' Perl: 5.008003(i586-linux-thread-multi) OS : linux (2.6.5) DBI : 1.50 DBD::Sponge : 11.10 DBD::SQLite : 1.11 DBD::SQLRelay : 0.37 DBD::Proxy : install_driver(Proxy) failed: Can't locate RPC/PlClient.pm in @INC DBD::Oracle : 1.17 DBD::File : 0.33 DBD::ExampleP : 11.12 DBD::DBM: 0.03 Philip
RE: Speed test for connecting to Oracle for Windows via ODBC
-Original Message- From: Ron Savage [mailto:[EMAIL PROTECTED] Sent: Monday, June 12, 2006 9:22 PM To: List - DBI users Subject: Speed test for connecting to Oracle for Windows via ODBC Hi Folks Using a DSN of dbi:ODBC:xyz, the DBI - connect(...) call takes 16 (sic) seconds with both the Perl script and Oracle running on the same PC under Windows. Control Panel - Administrative Tools - Data Sources (ODBC) - (Your DSN) - Configure - Test Connection How long does that take to acknowledge success? Philip
RE: Speed test for connecting to Oracle for Windows via ODBC
-Original Message- From: Ron Savage [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 13, 2006 6:11 PM To: List - DBI users Subject: RE: Speed test for connecting to Oracle for Windows via ODBC On Tue, 13 Jun 2006 09:57:38 -0400, Garrett, Philip \(MAN-Corporate\) wrote: - Test Connection How long does that take to acknowledge success? It's 16 seconds after clicking Test before the dialog box pops up asking for the password. That may well be a Window-inspired delay of course. Then, after entering the pw and clicking OK, it's usually 1 sec. However on the first test after booting, after clicking OK it was around 7 .. 8 seconds (sound familiar?) This is all on a home PC with a broadband connexion to the internet, and no other PCs in the house. My first guess is that the server is attempting to do reverse lookup on you. My (general, not Oracle) experience has been that when there's a several second delay when connecting, but everything is fine thereafter, it's usually reverse DNS. I use DBD::ADO to get at Oracle over ODBC without any problems. But of course changing to ADO won't help you, because you have a network/Oracle setup problem. How long does it take to connect via sqlplus? tnsping? Philip
RE: Non-Standard Installation
-Original Message-
From: Tyler MacDonald [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 14, 2006 2:54 PM
To: Kandi, Santosh
Cc: dbi-users@perl.org
Subject: Re: Non-Standard Installation
Kandi, Santosh [EMAIL PROTECTED] wrote:
I thought that since DBI.pm and DBD (Oracle.pm) are basically Perl
modules I could just copy the pm modules in my personal lib and set
the PERL5LIB to point to them.
Is my assumption wrong?
They are basically perl modules, but they also have components written
in C; you need to copy the accompanying .so files (DBI.so and
Oracle.so) to the right place as well. You're probably better off just
rebuilding them from CPAN with the appropriate PREFIX and LIB options
to place them where you want.
Here's one way to do it:
wget http://www.perl.com/CPAN/modules/by-module/DBI/DBI-1.51.tar.gz
tar xvfz DBI-1.51.tar.gz
cd DBI-1.51.tar.gz
# change this directory to wherever you want to install the module
perl Makefile.PL INSTALLSITELIB=$HOME/lib
make test make install
# should show the DBI module in your own lib directory
PERL5LIB=$HOME/lib perl -MDBI -le 'print $INC{DBI.pm}'
You can do the same thing with DBD::Oracle (but be sure to read the
documentation -- getting it to test properly requires a little effort).
Philip
RE: Getting spatial data?
-Original Message- From: Todd Chisholm Sent: Thursday, June 22, 2006 1:54 PM Subject: Getting spatial data? my $query = select geometry from processed_product where id=15601; my $sth = $connect-prepare($query); $sth-execute(); [snip] DBD::Oracle::db prepare failed: ERROR OCIDefineObject call needed but not implemented yet [for Statement select geometry from processed_product where id=15601] at /home/corp14/tchishol/oracleTst.pl line 11. You can't use native PL/SQL object types directly in Perl. You'll have to select just the properties you need in the select clause, like: select geometry.get_dims() from processed_product where id=15601 Can't call method execute on an undefined value at ...oracleTst.pl line 12. Just nit picking here, but don't forget || die $connect-errstr after your prepare statement. Philip
RE: Always Die (was: RE: Getting spatial data?)
Yeah, you're right. T definitely IMTOWTDI. Using RaiseError is a valid
choice, as is checking each call for errors. However, you do need one or the
other. Calling a method on a undefined variable is rarely one of TWTDI.
From: Rutherdale, Will [mailto:[EMAIL PROTECTED]
Sent: Thu 6/22/2006 6:22 PM
To: dbi-users@perl.org
Subject: Always Die (was: RE: Getting spatial data?)
Just to expand on that piece of the discussion, there are cases where I
don't use 'die' on DBI statements at all.
For instance, I may have a system script (not for end users) whose
purpose is to do some database update operations and either succeed or
fail. If it succeeds then it does the whole job correctly, if it fails
then it makes no changes and returns an error code. The specific errors
in the failure case will show up in the log, and everything will be
rolled back.
For that purpose I will open the DBI connection with these options
(amongst others): { PrintError = 1, RaiseError = 1, AutoCommit = 0
}.
As Larry says, there is more than one way to do it, and for some
purposes you can use this as a consistent and clean error handling
policy without the explicit 'die' on every statement.
-Will
-Original Message-
From: Garrett, Philip (MAN-Corporate)
[mailto:[EMAIL PROTECTED]
Sent: Thursday 22 June 2006 14:08
To: dbi-users@perl.org
Subject: RE: Getting spatial data?
Just nit picking here, but don't forget || die $connect-errstr
after your prepare statement.
- - - - - Appended by Scientific Atlanta, a Cisco company - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is
solely intended for the named addressee (or a person responsible for delivering
it to the addressee). If you are not the intended recipient of this message,
you are not authorized to read, print, retain, copy or disseminate this message
or any part of it. If you have received this e-mail in error, please notify the
sender immediately by return e-mail and delete it from your computer.
RE: dbi-users@perl.org
-Original Message- From: Lihong Sun [mailto:[EMAIL PROTECTED] Sent: Monday, June 26, 2006 2:49 PM To: dbi-users@perl.org Subject: dbi-users@perl.org Dear folks, I have a question for you here. I just installed DBI and Oracle DBD module. I tried a test script but got the following errors: install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread- multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.9.0: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread- multi/DynaLoader.pm line 230. at (eval 3) line 3 Compilation failed in require at (eval 3) line 3. Perhaps a required shared library or dll isn't installed where expected at lsun.pl line 7 Does anyone know what is wrong? I could login to the database using sqlplus username/[EMAIL PROTECTED] Try this before running your script: export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH That should help it to find the library. Philip
RE: CSV problem on Solaris
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 28, 2006 5:26 AM
To: dbi-users@perl.org
Subject: DBD:CSV problem on Solaris
Hello,
I do have a problem with a littler perl script I wrote.
It uses DBD:CSV and I don´t know why it doesn´t work as I would expect it.
[snip]
my $dbh = DBI-connect(DBI:CSV:);
Add this after connecting:
$dbh-{RaiseError} = 1;
This will cause DBI to die with an error message when there is a
problem.
Regards,
Philip
RE: I18N in Oracle documentation
cheryl fillekes wrote: Tim, In the most excellent http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Oracle_and_Unicode there is reference to the oracle internationalization documentation. Do you have a more specific reference for that, particularly as relates to 9i? A link would be great (even if it requires logging in to the Oracle support network) would be great, or an indication of which pages etc. in which book, since there are a lot of duds out there. [Globalization Support Guide] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/toc. htm [Other Docs] http://www.oracle.com/technology/tech/globalization/index.html The Best Practices whitepaper is pretty good. It has Perl examples, and even though it claims to be for Oracle's app server, the concepts transfer well to other environments. hth Philip
RE: sth-do()
Loo, Peter # PHX wrote: Hi All, I need to issue commands such as (CREATE TABLE, DROP TABLE, ALTER TABLE, etc...), however, whenever I use sth-do($sqlString), I get an error. I understand that using sth-do(), it goes through the process of prepare and execute. With that, it is expecting a returned value. You should be using $dbh-do(), not $sth-do(). Philip
RE: Connecting to Oracle.
[EMAIL PROTECTED] wrote: Guys, Can anyone answer following questions please 1) Do I have to install sqlplus to connect to Oracle database from Perl No, but it typically comes with the files that you DO need (the Oracle client). Google oracle instantclient 2) What , how and where can I get the modules required. Install DBI and DBD::Oracle from CPAN. Be sure to read the included instructions carefully. Philip
RE: [Templates] make install failed (v2.15) on cygwin
LI Yi wrote: I'm trying to install v2.15 on cygwin tar zxvf Template-Toolkit-2.15.tar.gz cd Template-Toolkit-2.15 perl Makefile.PL make make test make install I get Can't locate AppConfig.pm in @INC (@INC contains: blib/lib blib/arch [snip] This means you need the AppConfig module. perl -MCPAN -e install AppConfig From the INSTALL: The 'ttree' utility uses the AppConfig module (version 1.56 or above) for parsing command line options and configuration files. It is available from CPAN: http://www.cpan.org/authors/Andy_Wardley/ Philip
RE: DBI-installed_drivers() usage
Paul Griffin wrote:
I'm running Win XP with Perl 5.8.8.
If I try and run the following code :
use DBI;
my %drivers = DBI-installed_drivers();
foreach (keys( %drivers)) {
print $_ uses $drivers{$_}\n;
}
Nothing is returned. Yet if I use :
my @drivers = DBI-available_drivers();
I get a list of drivers that I can then access with :
@dataSources = DBI-data_sources($_);
Am I using DBI-installed_drivers() incorrectly?
DBI-installed_drivers returns only the drivers that are actually loaded
into the current process. Installed in this case means installed into
memory.
This code will load all the drivers (generally not a good thing) and
then list them with installed_drivers():
use DBI;
eval { DBI-install_driver($_) }
foreach DBI-available_drivers;
my %drivers = DBI-installed_drivers;
print $_: $drivers{$_}\n foreach sort keys %drivers;
Philip
RE: (Fwd) dbd-oracle-1.16
Do you have root on this box? If so, su to nobody (or whoever your web
server runs as) and try to change into that lib directory. I have a
feeling you'll find a directory somewhere in that tree that won't let
you in.
Once you've fixed the permissions and can enter that directory, your
webserver should be able to, too.
Philip
Oscar Gomez wrote:
Ok,
drwxr-xr-x 3 oracle oinstall 4096 Jul 21 10:26
/opt/local/m01/app/oracle/product/8.17/lib/
-rwxr-xr-x 1 oracle oinstall 6352174 Jul 21 10:26
/opt/local/m01/app/oracle/product/8.17/lib/libclntsh.so.8.0
Running the script from the command line connects to the database and
returns correct results.
Oscar,
That information is owner/group id. I am asking about read/execute
permissions for this file. It might be the user who is executing the
Perl script does not have permissions into the $OH directory tree.
-Original Message-
From: Oscar Gomez [mailto:[EMAIL PROTECTED]
Sent: Friday, July 21, 2006 9:20 AM
To: Reidy, Ron; dbi-users@perl.org
Subject: RE: (Fwd) dbd-oracle-1.16
Hi Reidy, Thanks
The permissions are oracle.oinstall
--
Open WebMail Project (http://openwebmail.org)
-- Original Message ---
From: Reidy, Ron [EMAIL PROTECTED]
To: Tim Bunce [EMAIL PROTECTED], dbi-users@perl.org
Sent: Thu, 20 Jul 2006 05:16:06 -0600
Subject: RE: (Fwd) dbd-oracle-1.16
Oscar,
Is Oracle installed? If so, what the permissions on
$ORACLE_HOME/lib and $ORACLE_HOME/libclntsh.so.8.0?
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 19, 2006 11:45 PM
To: dbi-users@perl.org
Cc: [EMAIL PROTECTED]
Subject: (Fwd) dbd-oracle-1.16
- Forwarded message from Oscar Gomez [EMAIL PROTECTED] -
X-Pobox-Antispam: require_ptr/ returned deny: 200.13.228.34 has no
PTR record, or PTR and A records do not match From: Oscar Gomez
[EMAIL PROTECTED] To: Tim Bunce [EMAIL PROTECTED]
Subject: dbd-oracle-1.16
Date: Wed, 19 Jul 2006 16:54:32 -0500
X-OriginatingIP: 10.0.1.50 (ogomez)
Tim
I have the following error when execute program in apache-cgi-oracle
[Wed Jul 19 17:08:18 2006] [error] [client 10.0.1.50]
install_driver(Oracle) failed: Can't load
'/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle/
Oracle.so'
for module DBD::Oracle: libclntsh.so.8.0: cannot open shared object
file: No such file or directory at
/usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line
230., referer: http://10.0.0.6/cgi-bin/FGI?py6110
[Wed Jul 19 17:08:18 2006] [error] [client 10.0.1.50] at (eval 1)
line 3, referer: http://10.0.0.6/cgi-bin/FGI?py6110
[Wed Jul 19 17:08:18 2006] [error] [client 10.0.1.50] Compilation
failed in require at (eval 1) line 3., referer:
http://10.0.0.6/cgi-bin/FGI?py6110
[Wed Jul 19 17:08:18 2006] [error] [client 10.0.1.50] Perhaps a
required shared library or dll isn't installed where expected,
referer: http://10.0.0.6/cgi-bin/FGI?py6110
perl version 5.8.5
lunux enterprise ES 4
DBD-Oracle-1.16
DBI-1.30
Thanks
--
Open WebMail Project (http://openwebmail.org)
-- Original Message ---
From: Tim Bunce [EMAIL PROTECTED]
To: Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED]
Sent: Wed, 19 Jul 2006 08:37:27 -0700
Subject: Re: DBI-installed_drivers() usage
On Wed, Jul 19, 2006 at 09:24:53AM -0400, Garrett, Philip (MAN-
Corporate) wrote:
Paul Griffin wrote:
I'm running Win XP with Perl 5.8.8.
If I try and run the following code :
use DBI;
my %drivers = DBI-installed_drivers();
foreach (keys( %drivers)) {
print $_ uses $drivers{$_}\n;
}
Nothing is returned. Yet if I use :
my @drivers = DBI-available_drivers();
I get a list of drivers that I can then access with :
@dataSources = DBI-data_sources($_);
Am I using DBI-installed_drivers() incorrectly?
DBI-installed_drivers returns only the drivers that are actually
loaded into the current process. Installed in this case means
installed into memory.
Ah. The docs weren't clear:
--- DBI.pm (revision 6618)
+++ DBI.pm (working copy)
@@ -2753,10 +2753,15 @@
%drivers = DBI-installed_drivers();
-Returns a list of driver name and driver handle pairs for all
-installed drivers. The driver name does not include the 'DBD::'
-prefix. Added in DBI 1.49.
+Returns a list of driver name and driver handle pairs for all
drivers +'installed' (loaded) into the current process. The driver
name does not +include the 'DBD::' prefix.
+To get a list of all drivers available in your perl instalation
you
can use +L/available_drivers. + +Added in DBI 1.49. + =item
Cinstalled_versions
DBI-installed_versions;
Tim.
--- End of Original Message ---
- End forwarded message -
This electronic message transmission is a PRIVATE communication
which contains information which may be confidential or privileged.
The information is intended to be for the use
RE: Problem with DBI
Palisetti, Krishna_Mohan wrote: Hi, I'm seeing the following warning message from DBIx::ContextualFetch intermittently. Use of uninitialized value in null operation at /usr/local/lib/perl5/site_perl/5.8.6/DBIx/ContextualFetch.pm line 51. What does it mean? Sorry, I am not in a position to provide a simple test case as I still can't reproduce the problem at will. [EMAIL PROTECTED]:~$ perl -MDBI -e 'DBI-installed_versions;' Perl: 5.008006(i86pc-solaris) OS : solaris (2.10) DBI : 1.48 What version of DBIx::ContextualFetch do you have installed? [EMAIL PROTECTED]:~$ perl -MDBIx::ContextualFetch -le 'print $DBIx::ContextualFetch::VERSION' 1.02 I can't be sure, but it looks like it's probably a bug in the DBD you're using. What driver are you using with this connection? Is it the latest version? Philip
RE: Problem with DBI
Palisetti, Krishna_Mohan wrote: Hi, I'm seeing the following warning message from DBIx::ContextualFetch intermittently. Use of uninitialized value in null operation at /usr/local/lib/perl5/site_perl/5.8.6/DBIx/ContextualFetch.pm line 51. What does it mean? Sorry, I am not in a position to provide a simple test case as I still can't reproduce the problem at will. [EMAIL PROTECTED]:~$ perl -MDBI -e 'DBI-installed_versions;' Perl: 5.008006(i86pc-solaris) OS : solaris (2.10) DBI : 1.48 What version of DBIx::ContextualFetch do you have installed? [EMAIL PROTECTED]:~$ perl -MDBIx::ContextualFetch -le 'print $DBIx::ContextualFetch::VERSION' 1.02 I can't be sure, but it looks like it's probably a bug in the DBD you're using. What driver are you using with this connection? Is it the latest version? We are using, DBD::Sybase v1.02_01. The latest one seems to be v1.07. We could try the upgrade but it would be difficult to convince the production group unless we can say for sure that the latest version addresses this problem. You could try to install the latest version in a temporary test location in your dev area and test that. mkdir /tmp/dbd-syb-latest perl Makefile.PL INSTALLSITELIB=/tmp/dbd-syb-latest make test make install # from shell export PERL5LIB=$HOME/dbd-syb-latest:$PERL5LIB ./your-script.pl # from Apache: SetEnv PERL5LIB /tmp/dbd-syb-latest PassEnv PERL5LIB PerlPassEnv PERL5LIB Philip
RE: Problem with DBI
This part of DBIx::ContextualFetch is just a statement handle subclass.
It's trying to call $sth-SUPER::execute() which is where the error is
occurring. I suppose it could be something about DBI instead of
DBD::Sybase, but I use DBIx::ContextualFetch with Oracle and I've never
seen the error.
The offending code:
46 # local $sth-{Taint} leaks in old perls :(
47 sub _untaint_execute {
48 my $sth = shift;
49 my $old_value = $sth-{Taint};
50 $sth-{Taint} = 0;
51 my $ret = $sth-SUPER::execute(@_);
52 $sth-{Taint} = $old_value;
53 return $ret;
54 }
Philip
[EMAIL PROTECTED] wrote:
Hi,
I have no knowledge of the DBIx::ContextualFetch module, and so have
no idea whether upgrading DBD::Sybase could fix the problem.
The issue could be related to multiple-result sets, or to some other
Sybase-specific issue that isn't handled properly by
DBIx::ContextualFetch, or maybe something else entirely...
Michael
Extranet
[EMAIL PROTECTED] - 24/07/2006 17:27
We are using, DBD::Sybase v1.02_01. The latest one seems to be v1.07.
We could try the upgrade but it would be difficult to convince the
production group unless we can say for sure that the latest version
addresses this problem.
cc:ing the author to see if he has something to offer.
-Mohan
-Original Message-
From: Garrett, Philip (MAN-Corporate)
[mailto:[EMAIL PROTECTED]
Sent: Monday, July 24, 2006 8:17 PM
To: Palisetti, Krishna_Mohan; dbi-users@perl.org
Subject: RE: Problem with DBI
Palisetti, Krishna_Mohan wrote:
Hi, I'm seeing the following warning message from
DBIx::ContextualFetch intermittently. Use of uninitialized value
in null operation at
/usr/local/lib/perl5/site_perl/5.8.6/DBIx/ContextualFetch.pm line
51.
What does it mean? Sorry, I am not in a position to provide a
simple
test case as I still can't reproduce the problem at will.
[EMAIL PROTECTED]:~$ perl -MDBI -e 'DBI-installed_versions;'
Perl: 5.008006(i86pc-solaris)
OS : solaris (2.10)
DBI : 1.48
What version of DBIx::ContextualFetch do you have installed?
[EMAIL PROTECTED]:~$ perl -MDBIx::ContextualFetch -le 'print
$DBIx::ContextualFetch::VERSION' 1.02
I can't be sure, but it looks like it's probably a bug in the DBD
you're using. What driver are you using with this connection? Is it
the
latest version?
Philip
RE: techniques for proper quoting?
Corey wrote:
Greetings!
I'm using DBI w/ DBD::Pg, and am in the midst of attempting to
simplify some existing code. I happened on the following older
perl.com article: http://www.perl.com/pub/a/2001/03/dbiokay.html
which provides what
seems to be a nicely concise way of constructing INSERT statements:
@fields = qw( country firstname lastname );
$fields = join(', ', @fields);
$values = join(', ', map { $dbh-quote($_) }
@[EMAIL PROTECTED]);
Blech! Quoting values this way is generally a bad idea. Google DBI
placeholders for reasons why. One good article is
http://www.stupidfool.org/perl/docs/perltut/dbi/dbiplace.html.
$sql = INSERT INTO foo ($fields) VALUES ($values);
... which works great - so long as the data types of the columns
defined in the table each have the same quoting requirements.
But what if, for example, there were a couple more columns added to
the above example - say, a couple 'integer', 'numeric' and/or
'boolean' types? What would be an elegant/simple solution? How would
I first detect/determine the datatype of the field as defined in the
schema, and then properly quote it in the map?
You should use placeholders. Here's how:
@fields = qw( country firstname lastname );
$sql = INSERT INTO FOO (
. join(,, @fields)
. ) VALUES (
. join(,, (?) x @fields) # a ? for each field
. );
$dbh-do($sql, undef, @fields);
Philip
RE: techniques for proper quoting?
Garrett, Philip (MAN-Corporate) wrote:
Corey wrote:
Greetings!
I'm using DBI w/ DBD::Pg, and am in the midst of attempting to
simplify some existing code. I happened on the following older
perl.com article: http://www.perl.com/pub/a/2001/03/dbiokay.html
which provides what seems to be a nicely concise way of constructing
INSERT statements:
@fields = qw( country firstname lastname );
$fields = join(', ', @fields);
$values = join(', ', map { $dbh-quote($_) }
@[EMAIL PROTECTED]);
You should use placeholders. Here's how:
@fields = qw( country firstname lastname );
$sql = INSERT INTO FOO (
. join(,, @fields)
. ) VALUES (
. join(,, (?) x @fields) # a ? for each field
. );
$dbh-do($sql, undef, @fields);
Doh! That should be:
$dbh-do($sql, undef, @[EMAIL PROTECTED]);
Regards,
Philip
RE: ANNOUNCE: DBD::Oracle 1.18
John Scoles wrote: DBD::Oracle 1.18 has been released. With this release DBD::Oracle finally implements Oracle's native Array Interface. You will see very dramatic increase in speed. For example; the time for a 2 million plus insert query dropped from well over an hour to less than 10 minutes when using execute_array() and the new code. This new feature is really great. Thanks, John and Kristian. The announcement and docs seem to really emphasize execute_array() over execute_for_fetch(), though, which puzzles me. I see a dramatic speed increase using execute_for_fetch() instead of execute_array(). I first converted my code to accumulate columns into individual parallel arrays to be passed into execute_array(). I was happy to see a 56% speed increase using the bulk insert. Now, my program was cpu-bound and a lot of the cpu time was being eaten by execute_array (which was, to my surprise, simply converting the parallel arrays back into the format I originally used!). I changed the program to just accumulate rows as arrayrefs and then called execute_for_fetch with a simple shift() sub. Voila, another 20% faster. Is there a reason I would want to use execute_array() with ArrayTupleFetch instead of execute_for_fetch()? The latter is more direct, since execute_array() just calls it in the end anyway. Would I be missing out on some potential future optimization? Also, is there any reason to convert old code to accumulate into single- column arrays to be passed to execute_array, instead of using the subroutine reference to return tuples? I assumed that the columnar format was due to some driver implementation detail but I got burned by that. It's much easier and much faster to convert old code to use the fetch sub, since all it requires is to convert $sth-execute(@params) to push(@rows,[EMAIL PROTECTED]). Philip
RE: ORA-01017 ... but only when script is run as CGI
Angus McIntyre wrote: I'm using current versions of DBI and DBD::Oracle in a CGI script, Oracle 10.2 client talking to an Oracle 9 database, RedHat Linux. The script previously ran successfully on a similar machine with the Oracle 9 client. I have a simple test script, in which the username, password and SID are hardcoded. If I run this script from the command line, it connects without problems. If I call the script as a CGI script, I get ORA-01017 in OCISessionBegin, which is the bad username or password error. As far as I can tell, environment variables are the same in both contexts, the same tnsnames.ora file is used, and so forth. I have identified and resolved permissions errors, so I think that isn't the problem. Has anyone encountered something similar, or does anyone have any suggestions for other things that I should check in order to resolve this? I have had this problem before. I'm not positive, but I think it was caused by ORACLE_HOME not being passed through by Apache. If you're running Apache, you'll need this directive in your httpd.conf: PassEnv ORACLE_HOME hth Philip
RE: Re: AutoCommit does not work
Artem Harutyunyan wrote: Hi, Are you using a table type in mysql that supports transactions? Well, I am not sure, how can I check it ? mysql show table status like '%transaction%'; The second column (engine) is what you're interested in... needs to be InnoDB to support transactions. Regards, Philip
RE: Rows returned are out of sync with the request.
Stephen Carville wrote: I'm not quite sure how to describe this problem. When I make a call to an Oracle 10g database using DBI and SQLRelay (for connection pooling) I seem to get results from the previous SELECT. What happens when you use DBI *without* SQLRelay? Philip
RE: Problem on Perl DBI for Oracle-DBD-1.18
Lam, Keith (MGS) wrote: Hi DBI experts, Could you please help me to resolve this Perl Oracle DBD problem. I am using Oracle Client 10g, Perl 5.8.5 (default from RedHat ES4 installation) and Oracle-DBD-1.18. The SQLPLUS and Perl cgi program runs and displays output as expected without any errors from the command line I have the following error when running perl CGI script using Oracle DBD from the web browser: install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle/ Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected [snip] 5. All authority/access and symbolic links for libclntsh.so.10.1, libclntsh.so, libocci.so.10.1 and libocci.so are set up properly in /home/oracle/oracle/product/10.2.0/oraclient/lib What are the permissions on that directory itself. If you su to nobody (or whoever your server runs as), can you cd into that dir? Could you please suggest me what else I should try to resolve this problem? I searched on google extensively but found no other sources of solutions beside those listed above. If you're using Apache, you can start it with the -X option to run in single-process debug mode. That will make it easier to use the LD_DEBUG environment variable to track down your problem. # dumps actual library search paths LD_DEBUG=libs httpd -X After the server has started up, you can hit the web page that will load DBD::Oracle, at which point you should see the search path that ld is using to find the library. Regards Philip
RE: DBI to BerkeleyDB?
ManKyu Han wrote: Hi. Again. I did benchmark using BerkeleyDB (Hash) module (random select) and the number I got was around 10,000 / sec. I also did similar benchmark using DBD::DBM (with BerkeleyDB Hash support). But this time, the number was too low. (less than 200 /sec). I moved prepare outside loop, so DBD::DBM (BerkeleyDB) works almost twice faster (100 - 200 /sec) but compare to BerkeleyDB, it is still slower. Since DBD::DBM is now using BerkeleyDB, shouldn't it perform as well as BerkeleyDB ?? (or at least not as bad as what I got?) You're comparing the speed of a very low-level dbm file interface to the speed of a high-level rdbms interface that is implemented in pure Perl. With BerkeleyDB there is almost no overhead -- you're essentially calling the C library directly. With DBD::DBM, though, you're going through several layers of abstraction -- at least DBI, a pure-perl DBD, and a pure-perl SQL engine. I don't have much experience in DBD::DBM, but this speed difference doesn't really seem unreasonable. If you have the option to use BerkeleyDB (specifically, if you only want to store an index of name-value pairs), then you should probably use that directly. It's one of the fastest (if not THE fastest) ways to persist a hash. If you might need multi-column support in the future and you need an in-process database, try DBD::SQLite. It's relatively robust and is implemented in C. Regards, Philip
RE: $dbh-{RowCacheSize} = 1000
Loo, Peter # PHX wrote: Hi, Can someone please tell me what the size is when I set it to 1000? Is it one meg? It's $your_query_row_size * 1000. It's not a byte size, it's a number of rows. Regards, Philip
RE: $dbh-{RowCacheSize} = 1000
Loo, Peter # PHX wrote: RowCacheSize (integer) What would you recommend I set this option to if I am sending all the SELECTed rows to a named pipe? I'd recommend you leave it alone, unless your database roundtrips are really expensive. And if they are, the only thing that can tell you what the best value would be is to benchmark different settings. It will only help if the program on the other end of the named pipe can accept data faster than you can send it. Philip
RE: $dbh-{RowCacheSize} = 1000
I would expect a C program to be able to write to a file faster than any
database loader could load data.
From: Loo, Peter # PHX [mailto:[EMAIL PROTECTED]
Sent: Thu 8/31/2006 5:49 PM
To: Garrett, Philip (MAN-Corporate); DBI-Users
Subject: RE: $dbh-{RowCacheSize} = 1000
The reason I asked the question is that the Pro*C program appears to be
writing the output to a file faster than my Perl program can write to
the named pipe. The other end of the named pipe is a native database
utility similar to the sqlldr (nzload). I don't know where the bottle
neck is so I might have to break up the program into to separate parts.
Have Perl dump the table to a file then feed the file using nzload and
calculate the total time for each step.
Peter
-Original Message-
From: Garrett, Philip (MAN-Corporate)
[mailto:[EMAIL PROTECTED]
Sent: Thursday, August 31, 2006 2:44 PM
To: DBI-Users
Subject: RE: $dbh-{RowCacheSize} = 1000
Loo, Peter # PHX wrote:
RowCacheSize (integer)
What would you recommend I set this option to if I am sending all the
SELECTed rows to a named pipe?
I'd recommend you leave it alone, unless your database roundtrips are
really expensive. And if they are, the only thing that can tell you
what the best value would be is to benchmark different settings.
It will only help if the program on the other end of the named pipe can
accept data faster than you can send it.
Philip
This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.
RE: Getting DBD::Oracle tests working
John Scoles wrote:
t/26exe_array...DBD::Oracle::st execute_array warning:
ORA-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: error
possibly near * indicator at char 66 in 'INSERT INTO
dbd_ora__drop_me ( row_1, row_2, row_3) VALUES (:p1,:*p2,:p3)')
[for Statement INSERT INTO dbd_ora__drop_me ( row_1, row_2, row_3)
VALUES (?,?,?) with ParamValues: :p3=undef, :p1=undef, :p2=undef]
at t/26exe_array.t line
77.
Again, this doesn't seem to be particularly serious, and the install
proceeds anyway.
You are right this is a deliberate error that is testing to see if an
error in the tuple is returned. So you can ignore it.
Hi John,
The following patch hides the warning but still checks for its
existence.
If the long lines get wrapped, you can get the pristine patch here:
http://www.pgarrett.net/DBD-Oracle-ExeArray.patch
Regards,
Philip
--- 26exe_array.t.orig 2006-09-07 00:08:58.091338000 +1000
+++ 26exe_array.t 2006-09-07 00:07:29.814842000 +1000
@@ -4,7 +4,7 @@
use DBD::Oracle qw(ORA_RSET SQLCS_NCHAR);
use strict;
-use Test::More tests =13 ;
+use Test::More tests =14 ;
unshift @INC ,'t';
require 'nchar_test_lib.pl';
@@ -74,18 +74,35 @@
@var2 = (2,2,2,2,'s',2,2,2,2,2);
-ok (!$sth-execute_array(
- {ArrayTupleStatus = $tuple_status},
-[EMAIL PROTECTED],
-[EMAIL PROTECTED],
- [EMAIL PROTECTED],
- ), '... execute_array should return flase');
+{
+ # trap the intentional failure of one of these rows
+ my $warn_count = 0;
+ local $SIG{__WARN__} = sub {
+my $msg = shift;
+if ($warn_count++ == 0 $msg =~ /ORA-24381/) {
+ # this is the first warning, and it's the expected one
+ return;
+}
+
+# unexpected warning, pass it through
+warn $msg;
+ };
+
+ ok (!$sth-execute_array(
+{ArrayTupleStatus = $tuple_status},
+ [EMAIL PROTECTED],
+ [EMAIL PROTECTED],
+ [EMAIL PROTECTED],
+ ), '... execute_array should return flase');
+
+cmp_ok(scalar @{$tuple_status}, '==', 10, '... we should have 10
tuple_status');
+
+cmp_ok( $tuple_status-[4]-[1],'ne','-1','... we should get
text');
+
+cmp_ok( $tuple_status-[3],'==',-1,'... we should get -1');
- cmp_ok(scalar @{$tuple_status}, '==', 10, '... we should have 10
tuple_status');
-
- cmp_ok( $tuple_status-[4]-[1],'ne','-1','... we should get text');
-
- cmp_ok( $tuple_status-[3],'==',-1,'... we should get -1');
+is($warn_count, 1, ... we should get a warning);
+}
# siple test with execute_for_fetch
RE: Getting DBD::Oracle tests working
Tim Bunce wrote:
It would be simpler to
$h-{PrintWarn} = 0;
at the right spot. (Perhaps in a block and using local.)
Hi Tim,
Yeah, that would be simpler (and it's what I do in my own code), but my
impression was that John wanted to leave the warning in there to test
that it would be generated.
Regards,
Philip
RE: Apostrophie problem(s)
Owen wrote: The code below creates a database, then reads it but it fails when I try to match an apostrophied name. Any suggestions as to where I am going wrong would be much appreciated. $SQL = select * FROM tennisclub where last LIKE '%$name%' ; $cursor = $dbh-prepare($SQL); $cursor-execute(); Since the percent signs in the LIKE clause are part of a string, you should send that entire string operand as a bind parameter. Untested code, but hopefully it'll get the idea across: $SQL = select * from tennisclub where last like ? escape ?; $cursor = $dbh-prepare($SQL); # '%', and '_' are special characters for the LIKE operator. # Put a backslash ahead of each one, and backslashes too. (my $escaped = $name) =~ s/([\\%_])/\\$1/g; # include the literal %s in the like operand. $cursor-execute(%$escaped%, '\'); Philip
RE: Help needed for DBD-Oracle Installation in Windows 2003
You can also use DBD::ODBC or DBD::ADO to go through the Windows odbc layer to get to Oracle. It isn't as fully featured as DBD::Oracle, but it works. From: Hameed Shahul-E4007Z [mailto:[EMAIL PROTECTED] Sent: Thu 9/14/2006 1:56 AM To: dbi-users@perl.org Subject: Help needed for DBD-Oracle Installation in Windows 2003 [snip] Also please suggest that is there any other way through which I can write Perl programs to connect to Oracle db. Thanks in advance. Regards, Shahul Hameed N.N. Motorola MDB IT - Application Global Support Team Tel: +1.847.523.4427 (Work) +1.847.204.7631 (Mobile) +1.847.984.2909 (Home) E-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
RE: Migration dbi files ?
Henrik Nilsson wrote:
What Perl binary hash file? I was referring to the contents of the
database (tables, procedures, constraints, etc). If you have a binary
object stored in the dB, export/import won't make it portable.
No, this was no Oracle database matter but a Perl hash file (which I
think is in binary format) matter. Sorry, perhaps I didn't make this
clear. So the problem is really, how to make such a file work on
another os...?
1) If you can get the data you need out of Oracle, use its
exp/imp programs, otherwise:
2) If you're trying to move a small DBM file (one of the AnyDBM_File
derivatives) from one platform to another, follow these steps. By
small, I mean that it will fit in your system's virtual memory.
A) Open the database with tie() like usual.
# replace SOME_File with the right module name.
tie my %database, 'SOME_File', 'filename', ...
B) use Storable's nstore to store a portable binary representation:
# note, you cannot store by reference, you must make a copy.
use Storable qw(nstore);
nstore({%database}, 'filename.bin') || die $!;
C) On the destination system, use Storable's retrieve to load the
data, and put it back into the database.
use Storable qw(retrieve);
$db_data = retrieve('filename.bin') || die $!;
%database = %$db_data;
3) If you're trying to move a *large* DBM file (larger than will fit in
virtual memory), you'll need to dump it a little at a time. Using a
CSV file is an option:
A) Open the database with tie() like usual.
# replace SOME_File with the right module name
tie my %database, 'SOME_File', 'filename', ...
B) Iterate over the name/value pairs (using each -- not keys), and
store each one in the CSV file.
use Text::CSV_XS;
my $csv = Text::CSV_XS-new(); # be sure to read perldoc
while (my ($k,$v) = each %database) {
$csv-combine($k,$v)
|| die can't combine row for key '$k';
print OUTFILE $csv-string(), \n;
}
C) On the destination system, read the CSV file and populate the
database file with its contents.
use Text::CSV_XS;
my $csv = Text::CSV_XS-new();
while () {
$csv-parse($_) || die can't parse line $.;
my ($k,$v) = $csv-fields;
$database{$k} = $v;
}
Regards,
Philip
RE: Passing an Array to Oracle using DBI
[EMAIL PROTECTED] wrote: Hello All Is it possible to pass an array as a parameter into an Oracle Stored procedure. I am using Oracle 1.18a. I have looked all over the place and have not found anything that states that this is possible. If so - does anyone have an example? Unfortunately, no. There is no *direct* way to bind a Perl array to a PL/SQL array. (As soon as I post this, I just know someone is going to say otherwise and show me how I've been doing it wrong all this time ;-) When I've needed to do this, I've done one of two things: 1) For small arrays of simple elements (e.g. array of varchar2) I've encoded them into a string on the Perl side, and then decoded that string into a PL/SQL array on the Oracle side. Messy, but relatively fast. 2) For larger arrays or arrays of composite elements (e.g. array of rowtype) I've inserted the elements as rows in a global temporary table. I then select those rows into the array bulk collect on the Oracle side. Flexible but slower. You could also do this one using a package array variable which you make PL/SQL calls to extend repeatedly. Once you've extended and populated the package array, you can pass it into the procedure. This might be faster than the GTT method, but I've found maintaining packages to be more work than maintaining tables. Regards, Philip
RE: Creating XML from an Oracle DB
[EMAIL PROTECTED] wrote: Hi Tim, I have spent about three hours using Google and Metalink trying to find the answer to what must be a FAQ when generating XML from Oracle but I cannot find anything that answers the question. I work for a company in the UK and we are generating an XML file from an Oracle 9.2 database using DBMS_XMLQUERY and DBMS_LOB. The XML file has to be in UTF-8 format (encoding=utf-8) but this is not the format that it is held in the database. From what I have read, it seems that it is AL32UTF8. Oracle's utf-8 support for the XML packages is shameful. http://www.dbforums.com/showthread.php?t=1212787. For any of this to happen automatically, you need to make sure that your original data (what you're making the XML from) is stored correctly in the database's character set. This means that if your data actually contains utf-8, the database characterset should be AL32UTF8. It appears your data is indeed in utf-8, because 49827 (0xC2A3) is the utf-8 representation of the pound sign. You can determine your database's character set with this query: select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET' Assuming your database characterset actually matches the data that's in it, you can just set the client character set to your desired output, and the encoding is done for you: # data from Oracle will now be converted correctly # into Perl's internal encoding. export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 # in perl... # convert from Perl's internal encoding to utf-8 when # printing to stdout. binmode(STDOUT, ':utf8');# convert from Perl's internal to utf-8 If your database characterset does not match the data (for example, your database characterset is US7ASCII) then sorry, you will have to manually convert each unicode column. If you're using Oracle 10G, then there are functions to convert XML encodings explicitly using character set Ids. Otherwise, you'll have to figure out the right hocus pocus to transfer the data from Oracle to Perl without losing character information, and then encode/decode in Perl with the Encode module. If you haven't already, I recommend reading the Oracle 9i Globalization Best Practices document. It can help get your head around how the character sets work. http://tinyurl.com/mtsxg [oracle.com]. Hope it helps. Regards, Philip
RE: Oracle and bindcolumns
Robert Hicks wrote: Any gotchas there? I am opening an Access db via ODBC and binding those columns (including a date field) and passing that to the Oracle handle to do inserts (i.e. Access - Oracle migration). Only gotcha is with formatting -- you'll need to either: 1) alter session set nls_date_format = '...' to the date format you're supplying Oracle, or: 2) use to_char(?,'...') on the date fields Philip
RE: Oracle and bindcolumns
Robert Hicks wrote: Garrett, Philip (MAN-Corporate) wrote: Robert Hicks wrote: Any gotchas there? I am opening an Access db via ODBC and binding those columns (including a date field) and passing that to the Oracle handle to do inserts (i.e. Access - Oracle migration). Only gotcha is with formatting -- you'll need to either: 1) alter session set nls_date_format = '...' to the date format you're supplying Oracle, or: 2) use to_char(?,'...') on the date fields Philip Oracle won't accept it if I do TO_DATE($start_date, 'DD/MM/') ? Yeah, sorry -- that should have been TO_DATE. Philip
RE: DBI - error
Sumitra Gatade wrote: Hi, I am trying to execute the stored procedure using dbh. The procedure details are as follows: proc_dequeue( BALID,strRequestXML,strStatus) where: BALID - Integer, strRequestXML - XMLType, strStatus - varchar The perl script implemented is : my $sth = $dbh1-prepare(begin proc_dequeue(:BALID,:strRequestXML,:strStatus); end;); $sth-bind_param_inout(:BALID,\$o_balid,20,\%attr ); $sth-bind_param_inout(:strRequestXML,\$o_requestXML,5,\%attr); $sth-bind_param_inout(:strStatus,\$o_status,2,\%attr); and the error message i am getting is : DBD::Oracle::st execute failed: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'proc_dequeue' You are passing a string to a function that expects XMLType, an opaque Oracle object type. The XMLType API provides a constructor that takes a varchar2 argument, so try changing your statement to: begin proc_dequeue(:BALID,XMLType(:strRequestXML),:strStatus); end; Regards, Philip
RE: Retrying a fetch after an error, without restarting the whole loop?
Jonathan Leffler wrote:
On 11/8/06, Bart Lateur [EMAIL PROTECTED] wrote:
[snip]
1) What's the best way to temporarily disable RaiseError when I want
to have it enabled for the rest of the script? Say, for one SQL
statement?
$sth-{RaiseError} = 0;
Or:
$dbh-{RaiseError} = 0;
$dbh-do(something that might fail);
$dbh-{RaiseError} = 1;
In situations where I want RaiseError (or AutoCommit, or whatever) to be
reset once a certain piece is done, I use the local keyword.
This ensures that:
1) The flag always gets reset, even if something in that code
block dies, and
2) The value is always what it used to be, without you having to save
the old value in a temp variable.
# e.g.
{
local $dbh-{RaiseError} = 0;
$dbh-do(something that might fail);
$obj-operation(); # this dies, but RaiseError is still reset
}
Regards,
Philip
RE: Possible Problem with bind_param
FWIW, I tested a simple SELECT ? with your '[EMAIL PROTECTED]'
value against my Postgres 7.2 (DBD::Pg 1.21). It bound fine and
returned the correct value.
David Wu wrote:
Hi Brian,
Thanks for the suggestion. I thought that might be the case as well,
and I've already tried adding the type attribute as a varchar, but
there was no change.
Dave
On Nov 10, 2006, at 4:31 PM, CAMPBELL, BRIAN D (BRIAN) wrote:
Here's a guess, as I'm not familiar with your particular Database
and DBD.
It looks like the DBD is treating your value as in integer.
Perhaps the DBD looks at the first part of the string, and thinks,
here's a number, so I'll pass an integer type to the database
engine.
Suggestion: try adding a type attribute to the the bind_param call,
forcing the value to treated as a CHAR type.
-Original Message-
From: David Wu [mailto:[EMAIL PROTECTED]
Sent: Friday, November 10, 2006 4:34 PM
To: dbi-users@perl.org
Subject: Possible Problem with bind_param
Hi all,
I came across some funny behavior in Perl with the DBI package, and I
don't know how to explain it or why it is happening. I've tried
googling it, but haven't found anything yet. Here is the scenario:
I have some code that automatically tries to update a user's email
address if it has changed when they come through using an
integration. However, it seems that although there was no error with
the database, the update does not go through. Here is the code for
reference:
my $sql = UPDATE m_user SET email = ?, first_name = ?,
last_name = ? WHERE id = ?;; my $sth =
$dbh-prepare($sql); $sth-bind_param(1,
$get_vars-{new_email}); $sth-bind_param(2,
$get_vars-{userfirstname}); $sth-bind_param(3,
$get_vars-{userlastname}); $sth-bind_param(4,
$api_user_id); $sth-execute;
$sth-finish;
So, as an example, say somehow the value of the previous email
address for this one user was 341. I print out the value of
$get_vars-{new_email} before and after the SQL statement, and the
value is what I expect it to be, something like
[EMAIL PROTECTED]. There are no errors and I hit the $dbh-
commit line. But if I run the query to retrieve the email of that
same user, the email hasn't changed. Also, Rich helped me to tail
the database logs so that I could see the query as it was being
executed, and I saw that it has truncated the value for the email,
looking something like this: UPDATE m_user SET email = 341,
first_name =
So this leads me to believe that there is something in the Perl that
is causing the value to be truncated, something to do with bind_param
or something like that. I think it doesn't like the underscore
character, or something. What I don't understand is that other
values have been working, like [EMAIL PROTECTED] or
[EMAIL PROTECTED], but the moodle ones doesn't
work. However, I haven't been able to find any explanation or fix.
I've also tried reforming the query like UPDATE m_user set email =
' . $get-vars-{new_email} .', first_name = ' . $get-vars-
{userfirstname} . ', last_name = ' . $get_vars-{userlastname} . '
WHERE id = . $api_user_id . ';;, but it wouldn't execute
correctly.
I can correctly update the email directly in the database and in a
perl command line script that uses the same format of the code as
what I included above, but it won't work within the application.
I was wondering if anyone else ever encountered anything like this or
new of a solution? I'm using Perl 5.8.7 and DBI 1.52 and PostgreSQL
8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian
1:3.3.5-13) as the database.
Thanks,
Dave
[EMAIL PROTECTED]
RE: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?
Chris Drake wrote: I'm expanding, and adding extra web servers to handle my load. My Apache/mod_perl environment maintains a new connection for each process, and these processes seem to chew up a lot of server resources. What is the normal acceptable way to maintain large numbers of simultaneous connections? I have enabled shared server support in my database, but I suspect it's not being utilized, since I only get a few hundred connections before hitting the too many connections error. Am I supposed to be passing a switch in with my initical connection requests to request a shared (instead of dedicated) connection maybe ? You'll want to read the Oracle Net Services Administrator's Guide. http://www.lc.leidenuniv.nl/awcourse/oracle/network.920/a96580/mts.htm#4 53189 From a higher-level architectural viewpoint, you could use some load balancing. Look into SQLRelay. It provides out-of-process connection pooling that can work for mod_perl servers. http://sqlrelay.sourceforge.net/ Regards, Philip
RE: Creating XML from an Oracle DB
Hi, Chris wrote: How about trying to create a brand new database using UTF8 *instead* of AL32UTF8 ? Oracle's UTF8 char set is old and incomplete. AL32UTF8 is the newer, more complete version. See http://tinyurl.com/y4qjd9 (oracle.com). Oracle recommends that you switch to AL32UTF8 for full support of supplementary characters in the database character set. Regards, Philip
RE: the same compiler that was used...
Steve Canfield wrote: the same compiler that was used... [snip] The entirety of the output of perl Makefile.PL, make, and perl -V are echoed below. However, I believe the problem lies in this error and others like it: /usr/lib/perl/5.8/CORE/perl.h:382:24: error: sys/types.h: No such file or directory Looks to me like you need the libdevel/libc6-dev package: http://packages.ubuntu.com/edgy/libdevel/libc6-dev Good luck! Regards, Philip
RE: bind_param oddity?
Kevin Spencer wrote: Hi guys, I'm sure I'm missing something *very* obvious but this one has me scratching my head. Using DBI 1.52, DBD::mysql 3.0007, Perl 5.8.8, MySQL 5.0.18. [snip] my $SQL = EOSQL; select count(*) from ? where TRXSTATUS = ? and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) ? EOSQL You can't bind a table name -- binding is only for value types. This is attempting to execute: select count(*) from 'CCHISTORY' ... (which won't run in mysql either) Regards, Philip
RE: Connecting to more than one oracle database
Malka Cymbalista wrote:
I am running Apache 2.0.55 with mod_perl 2.0.1 and Perl 5.8.1 on a Sun
Solaris machine. I am using DBI/DBD to connect to an Oracle database.
I am running DBI 1.39 and DBD-Oracle 1.16.
In order to connect to an oracle database on a different machine I
have a tnsnames.ora file with the appropriate definition. I also have
a file startup.pl with the following information:
$ENV{ORACLE_HOME} = '/usr/local/ora9i/9.2.0';
$ENV{ORACLE_SID} = 'asdb';
$ENV{TWO_TASK} = 'asdb';
$ENV{TNS_ADMIN} = '/usr/local/ora9i/9.2.0/network/admin/DEV_wiccdb';
In httpd.conf I have the line
PerlRequire /www/httpd/conf/startup.pl
I am currently connecting to an oracle database on a different machine
and everything is working fine. I would now like to connect to
another oracle database on another machine. My problem is how to
define my environment variables. ORACLE_HOME and TNS_ADMIN do not
seem to be a problem since I assume they can be the same. However,
how do I define ORACLE_SID and TWO_TASK so that they allow connecting
to 2 different databases on 2 different machines?
TWO_TASK and ORACLE_SID are defaults, and are not necessary if you can
specify the database name when you connect. Since you're using TNS, you
can just reference the TNS names in your connect string, e.g.:
my ($db,$user,$pass);
($db,$user,$pass) = ('asdb','user','pass');
my $dbh_1 = DBI-connect(dbi:Oracle:$db,$user,$pass)
|| die can't connect: $DBI::errstr;
($db,$user,$pass) = ('other_db','user','pass');
my $dbh_2 = DBI-connect(dbi:Oracle:$db,$user,$pass)
|| die can't connect: $DBI::errstr;
Regards,
Philip
RE: Connecting to more than one Oracle database
Malka Cymbalista wrote: [snip] I connect in the following manner: DBI-connect(DBI:Oracle:asdb,user1,password1); and DBI-connect(DBI:Oracle:asdb2,user2,password2); I have a script that works from the command line but does not work when I run it from the web. When I run from the web it gives very inconsistent results. Sometimes it succeeds in connecting to the first database I specify and sometimes it can't connect to either. I tried specifying the environment variables ORACLE_SID and TWO_TASK from within the script but that didn't seem to help consistently. That is, sometimes it worked and sometimes it didn't and we couldn't figure out exactly when it would or would not work. Does anyone know how I can connect via the web to 2 different oracle databases sitting on 2 different machines from within the same script. I don't see anything in your example that would prevent you from making connections to both databases from inside a web server. At this point, it would be helpful to post a small program that demonstrates the problem you're having. Regards, Philip
RE: Connecting to more than one Oracle database
Rohit V. Bhute wrote: Does anyone know how I can connect via the web to 2 different oracle databases sitting on 2 different machines from within the same script. If its Perl CGI and Apache, have you added the following to your httpd.conf? SetEnv ORACLE_HOME path to your Oracle setup The OP found his problem and emailed me directly. It was a logic error, apparently. Regards, Philip
RE: utf8 encoding problem
The easiest way to know is to try it out. If you want to just test it
without replacing your installation's DBD::Oracle, you can do a
temporary install of a newer version of DBD::Oracle by using the
INSTALLSITELIB parameter to Makefile.PL:
wget
http://www.perl.com/CPAN/modules/by-module/DBD/DBD-Oracle-1.14.tar.gz
tar xvfz DBD-Oracle-1.14.tar.gz
cd DBD-Oracle-1.14.tar.gz
export ORACLE_HOME=/path/to/your/oracle/installation
export ORACLE_USERID=user/pass
export ORACLE_SID=your-sid
perl Makefile.PL INSTALLSITELIB=$HOME/tmp-dbd-lib
make test
make install
If the build process was successful, you'll have DBD::Oracle installed
in $HOME/tmp-dbd-lib. You can try it out using the -I parameter for
perl:
perl -I$HOME/tmp-dbd-lib your_script.pl
If it doesn't help, just rm -rf $HOME/tmp-dbd-lib. No harm, no foul!
Regards
Philip
-Original Message-
From: Anand.K.S. [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 21, 2006 1:18 AM
To: dbi-users@perl.org
Subject: utf8 encoding problem
Hello,
I had posted this question in CPAN forum but i was directed here for a
better answer.
Here is the problem I am facing. In the following piece of code I get a
customer name from the database and append a pound symbol to the
customer
name and update the same in the database. The NLS_LANG parameter is set
to
AMERICAN_AMERICA.WE8DEC (western European character set) When I query
the
database to see if the name has been updated correctly I see some
garbage
value appended to the name instead of just a pound symbol. Then I did
some
research to find out the version oralce DBD version being used is 1.12.
I
found some difference between 1.12 and 1.14 relating to unicode
documentation. All I want to know is whether this was a bug in 1.12
which
was fixed in the later version of 1.14 and will an upgrade to 1.14 fix
this
problem. Could anyone please confirm me on this? The closer I could get
was
to find this link http://search.cpan.org/dist/DBD-Oracle/Changes#___top.
However the work around was to use use Encoding (Which is commented
out in
the following code) in perl which fixed the problem.
Cheers,
Anand.
use strict;
use encoding 'utf8';
use atadb;
use vars qw($opt_u);
my $db = atadb::connect($opt_u);
my $sql = SELECT NODE_NAME FROM CUSTOMER_NODE_HISTORY WHERE
CUSTOMER_NODE_ID = 9295370 ;
my $csr = $db-prepare($sql) || ataerr::dbprepare($db);
$csr-execute() || ataerr::dbexecute($db);
my $node_name = $csr-fetchrow; $csr-finish;
$node_name = 'wre'; my $pound = \xc2\xa3; print Pound =$pound\n;
my $node_name_upper = \U$node_name\E.$pound; $node_name.=$pound; print
Node name.$node_name.\n;;
print $node_name_upper.\n; my $sql1 = UPDATE CUSTOMER_NODE_HISTORY
SET
NODE_NAME = ? , NODE_NAME_UPPERCASE = ? WHERE CUSTOMER + +_NODE_ID =
9295370
;
my $csr1 = $db-prepare($sql1) || ataerr::dbprepare($db); print
Executing
... \n; #use Encode; #$node_name = encode($ENV{PERL_ENCODING},
$node_name);
#$node_name_upper = encode($ENV{PERL_ENCODING}, $node_name_upper);
$csr1-execute($node_name, $node_name_upper) || ataerr::dbexecute($db);
print Committing ... \n;
$db-commit || die(commit: .$db-errstr.\n);
$csr1-finish; $db-disconnect;
RE: Oracle DBD runtime error
Terry Maragakis wrote: I just installed DBI and Oracle DBD. Did you also *just* install the Oracle client? Are you able to run sqlplus without problems? Try this with your own user ID: sqlplus scott/[EMAIL PROTECTED] I can run the following program without error as root: #!/bin/perl -w use DBI; use DBD::Oracle; my $dbh=DBI-connect(dbi:Oracle:DWHDEVR1,scott,tiger); $dbh-disconnect(); But when I try to run it under my own user ID I get the following error: Can't load '/usr/perl5/site_perl/5.8.4/sun4-solaris- 64int/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: ld.so.1: perl: fatal: /software/app/oracle/product/10.1.0/db_1/lib32/libclntsh.so.10.1: Permission denied at /usr/perl5/5.8.4/lib/sun4-solaris- 64int/DynaLoader.pm line 230. at ./test.pl line 4 Compilation failed in require at ./test.pl line 4. BEGIN failed--compilation aborted at ./test.pl line 4. It looks like a permissions problem but I cannot figure out where. My guess is that the Oracle installer set the permissions on the Oracle client needlessly strict. With your own user ID, try to cd into /software/app/oracle/product/10.1.0/db_1/lib32, one directory at a time until you are stopped by permissions, e.g.: [EMAIL PROTECTED]:~ cd /software [EMAIL PROTECTED]:/software cd app [EMAIL PROTECTED]:/software/app cd oracle [EMAIL PROTECTED]:/software/app/oracle cd product [EMAIL PROTECTED]:/software/app/oracle/product cd 10.1.0 [EMAIL PROTECTED]:/software/app/oracle/product/10.1.0 cd db_1 -bash: cd: db_1: Permission denied If you don't run into problems there, check the permissions on libclntsh.so.10.1 itself. Maybe it's missing the o+r bit. Regards, Philip
RE: DBD-Oracle-1.19 Installation problem
Hi David, You can ignore that error. It is just testing that the array execute feature generates the expected warning. Since the test is written against a 9i database, it could be that 10g just doesn't produce that warning. List: has anybody else had this problem installing against 10g? I don't have it... Regards, Philip -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 10:53 AM To: dbi-users@perl.org Subject: DBD-Oracle-1.19 Installation problem I'm attempting to load DBD-Oracle-1.19 on a RH ES 4(latest update) proliant system and have an error when running 'make test'. 10gR2(10.2.0.10) is loaded on the server. Any help would be appreciated. Thanks ORACLE_USERID set to dba user : [EMAIL PROTECTED] DBD-Oracle-1.19]# make test PERL_DL_NONLAZY=1 /usr/bin/perl -MExtUtils::Command::MM -e test_harness(0, 'blib/lib', 'blib/arch') t/*.t t/01baseok t/10general.ok t/15nls.ok t/20select..ok t/21nchar... Database and client versions and character sets: Database 10.2.0.1.0 CHAR set is WE8ISO8859P1 (Non-Unicode), NCHAR set is AL16UTF16 (Unicode) Client 10.2.0.1 NLS_LANG is 'american', NLS_NCHAR is 'unset' t/21nchar...ok t/22nchar_al32utf8..ok t/22nchar_utf8..ok t/23wide_db.skipped all skipped: Database character set is not Unicode t/23wide_db_8bitskipped all skipped: Database character set is not Unicode t/23wide_db_al32utf8skipped all skipped: Database character set is not Unicode t/24implicit_utf8...ok t/25plsql...ok t/26exe_array...ok 8/14# Failed test (t/26exe_array.t at line 103) t/26exe_array...NOK 9# got: '0' # expected: '1' t/26exe_array...ok 14/14# Looks like you failed 1 tests of 14. t/26exe_array...dubious Test returned status 1 (wstat 256, 0x100) DIED. FAILED test 9 Failed 1/14 tests, 92.86% okay t/30longok 122/470 skipped: various reasons t/31lob.ok t/40ph_type.ok 3/19 Placeholder behaviour for ora_type=1 (the default) varies with Oracle version. Oracle 7 didn't strip trailing spaces, Oracle 8 did, until 9.2.x Your system doesn't. If that seems odd, let us know. t/40ph_type.ok t/50cursor..ok t/55nested..ok t/60reauth..ORACLE_USERID_2 not defined. Tests skipped. skipped all skipped: no reason given t/70metaok Failed Test Stat Wstat Total Fail Failed List of Failed --- t/26exe_array.t1 256141 7.14% 9 4 tests and 122 subtests skipped. Failed 1/20 test scripts, 95.00% okay. 1/1925 subtests failed, 99.95% okay. make: *** [test_dynamic] Error 255 [EMAIL PROTECTED] DBD-Oracle-1.19]#
RE: can't execute use $database
Patrix Diradja wrote: Dear my friends, I wonder why my perl can not execute query use $database. Sorry, I don't know for sure, but my guess is that the use database command is a *client* command, and not valid SQL. Generally speaking, when you want to connect to a specific database, it should be part of your DSN. You would specify the database name in the Windows ODBC manager. Or, if you're using connect strings, you'd specify it there. Your code indicates that, at some point, you will want to connect to several different databases using the same DSN. Rather than trying to force the same $dbh to connect to a different database, why not disconnect and then reconnect to the new database? Regards, Philip
RE: Errors in DBI installation!!
Hi, From: Goverdhan Reddy [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 06, 2007 3:00 PM To: dbi-users@perl.org Subject: Errors in DBI installation!! I am getting the errors when trying to install DBI module. Please have a look at the attached files and let me know where I am going wrong. You have a few problems: Warning: prerequisite File::Spec 1 not found... Install File::Spec from CPAN. Warning: prerequisite Storable failed to load... Install Storable from CPAN. cc -c -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xO3 -xdepend - DVERSION=\1.53\ -DXS_VERSION=\1.53\ -KPIC -I/usr/perl5/5.6.1/lib/sun4-solaris- 64int/CORE -DDBI_NO_THREADS Perl.c cc: unrecognized option `-KPIC' cc: language depend not recognized cc: Perl.c: linker input file unused because linking not done The perl you are using (system perl, perhaps?) was built with a different compiler than the one you're using to build DBI. Either install the Sun C compiler, or build a new perl with gcc. The compiler used to build perl should match the compiler used to build modules, including DBI. Regards, Philip
RE: Balasan: RE: can't execute use $database
Patrix Diradja wrote:
Dear my friend, Garret.
I Use Perl on Windows Vista, MS SQL Server 2005, DBI, ADO.
I create 'myperl' as DSN from ODBC Administration Tools.
Here is my connection string:
my $dbh1 =
DBI-connect(dbi:ADO:database=gua;host=127.0.0.1;port=1433,
$uname, $pword) || die Could not open SQL connection.
Your 'myperl' DSN should already know the database, host and port names.
Once you have configured that DSN, you just reference it by name:
my $dbh = DBI-connect('dbi:ADO:myperl', $uname, $pword)
|| die $DBI::errstr;
Regards,
Philip
RE: Balasan: RE: Balasan: RE: can't execute use $database
Patrix Diradja wrote: Yeah, that's the problem Garrett. I usually use that way to connect to ms msql. But now, I want that my perl code can work with several databases. So I need somewhat like the Connection String which I can modify (forming string) and than just does $dbh-connect(still be secret for me). and does $dbh-close before doing again connect to another database ($dbh-connect). Ok, try this: (found on www.connectionstrings.com) my $connstr = Provider=SQLNCLI; . Server=$server; . Database=$database;; my $dbh = DBI-connect(dbi:ADO:$connstr,$user,$pass) || die $DBI::errstr; If you are using SQL Server 2005 Express, you'll need to add \EXPRESS after the server name like this: Server=$server\\EXPRESS; Philip
RE: Balasan: RE: Balasan: RE: Balasan: RE: can't execute use $database
DBI::ADO is not a module. The module is DBD::ADO. But you don't need
to load it -- DBI will load it for you.
use DBI;
# DBI automatically loads ADO
my $dbh = DBI-connect('dbi:ADO:...',...);
-Original Message-
From: Patrix Diradja [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 08, 2007 10:54 AM
To: dbi-users@perl.org
Subject: Balasan: RE: Balasan: RE: Balasan: RE: can't execute use
$database
Dear Garrett it still doesn't work.
Here is the error message:
Can't locate DBI/ADO.pm in @INC (@INC contains: C:\Program
Files\ActiveState Perl Dev Kit 6.0\lib\ C:/Perl/site/lib C:/Perl/lib .)
at cldump.pl line 8.
BEGIN failed--compilation aborted at cldump.pl line 8.
Please tell me furthermore.
here is my code:
#
use strict;
use DBI::ADO;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
use warnings;
my $uname=sa;
my $pword=penguin;
my $host=127.0.0.1;
my @bd4l=(FinanCore);
#if (@ARGV){
#my @[EMAIL PROTECTED];
#}else{
#my @bd4l=(AprovaApp1);
#}
print I am dumping... @bd4l\n;
foreach my $elemenbd4l(@bd4l){
my $dsn = Provider=SQLNCLI;
. Server=$host;
. Database=$elemenbd4l;;
my $dbh1 = DBI-connect(dbi:ADO:$dsn, $uname, $pword) or die
$DBI::errstr;
Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] wrote:
Patrix Diradja wrote:
Yeah, that's the problem Garrett.
I usually use that way to connect to ms msql.
But now, I want that my perl code can work with several databases.
So I need somewhat like the Connection String which I can modify
(forming string) and than just does $dbh-connect( for me). and
does $dbh-close before doing again connect to
another database ($dbh-connect).
Ok, try this: (found on www.connectionstrings.com)
my $connstr = Provider=SQLNCLI;
. Server=$server;
. Database=$database;;
my $dbh = DBI-connect(dbi:ADO:$connstr,$user,$pass)
|| die $DBI::errstr;
If you are using SQL Server 2005 Express, you'll need to add \EXPRESS
after the server name like this:
Server=$server\\EXPRESS;
Philip
-
Kunjungi halaman depan Yahoo! Indonesia yang baru!
RE: :Oracle - Any advance on inserting CLOB to XMLTYPE?
I can confirm I have the same problems as Paul when inserting large
XMLType.
Running on:
1. SUSE LINUX Enterprise Server 9 (i586)
2. DBI v1.50
3. DBD::Oracle v1.18
4. Oracle client: 9.2.0.4.0
5. Oracle server: 9.2.0.7.0 - 64bit
Regards,
Philip
Paul Gallagher wrote:
Thanks for the info Ron. I think you may not have quite hit the limit
though.
I tried your code (are you explicitly typing the bind to :file_header?
I'm assuming not), and for small data sizes its ok, but once I go very
large it fails.
# fyi, I'm creating a long structure like this. max i 10 is ok, max i
3000 is not:
my @books;
my %dslong;
for (my $i=1; $i3000; $i++) {
push(@books, {id = $i, title = [ the book $i title ] } );
}
$dslong{book} = [EMAIL PROTECTED];
# and binding like this:
$sth-bind_param(:file_header, XMLout( \%dslong , RootName =
books) );
NB: I'm presently testing this with DBD-Oracle-1.17 and DBI-1.52-r1 on
Windows (ActiveState)
On 2/13/07, Reidy, Ron [EMAIL PROTECTED] wrote:
Well, it works for me out of the box as advertised.
Code snippet:
my $sth_admin_audit_files = $dbh-prepare(qq{
INSERT INTO array_audit.admin_audit_files
(instance_id
,file_crdt
,fname
,file_header
,file_header_raw
)
VALUES
(:instance_id
,TO_DATE(:file_crdt, 'DD/MM/ HH24:MI:SS')
,:fname
,SYS.XMLType.CREATEXML(:file_header)
,:file_header_raw
)
RETURNING admin_audit_file_id, crmo
INTO :admin_audit_file_id, :crmo
}) || die $DBI::errstr;
Running on
1. RH Linux 3.0 and 4.0
2. DBI v1.47
3. DBD::Oracle v1.16
4. Oracle v9.2.0.5.0; v10.2.1.0.3; v10.2.0.2.0
My files are around the order of 37Kb in size and I do nothing
special
with them (aside from convert the text into XML). My user has only
insert on the table with the XMLTYPE in it.
Does the user running the Perl program have INSERT privs on the
table?
Is there a synonym issue or a role issue?
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Paul Gallagher [mailto:[EMAIL PROTECTED]
Sent: Monday, February 12, 2007 12:04 PM
To: dbi-users@perl.org
Subject: DBD::Oracle - Any advance on inserting CLOB to XMLTYPE?
It seems the current state of affairs is that for inserting to
XMLTYPE
fields:
a) for 32k, can just insert text
b) for 32k, must insert to CLOB, then use a procedure to update
XMLTYPE (see
http://www.issociate.de/board/post/206125/DBD::Oracle_and_XMLType.html
for a summary of the details)
Personally I've tried just about every trick in the book to try and
get a direct-insert of large xml documents into xmltype fields, but
to
no avail.
What troubles me is that this _should_ work:
# NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))
$sth = $dbh-prepare( INSERT INTO tryit (formname, x) VALUES (?,
XMLTYPE(?)) );
$sth-bind_param(1, INSERTXMLTYPE );
$sth-bind_param(2, XMLout( \%dslong , RootName = books), { TYPE
=
SQL_CLOB } );
$sth-execute or warn INSERTXMLTYPE creation failure;
but it actually just gives ORA-00942: table or view does not exist.
this is a bogus message (search metalink for XMLTYPE ORA-00942).
try
an alternative like INSERT INTO tryit (formname, x) VALUES (?,
XMLTYPE(CAST(? as CLOB))) and you get ORA-00932: inconsistent
datatypes.
All of the above is old news I think.
Why I raise this now is that I discovered the python guys seem to
have
got it working OK. See
http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=20050401
connection = cx_Oracle.Connection(user/pw at tns)
cursor = connection.cursor()
cursor.setinputsizes(value = cx_Oracle.CLOB)
cursor.execute(insert into xmltable values (xmltype(:value)),
value = A very long XML string)
Seems very much like a binding issue on the DBI/DBD side.
Any thoughts?
~paul
This electronic message transmission is a PRIVATE communication
which contains information which may be confidential or privileged.
The information is intended to be for the use of the individual or
entity named above. If you are not the intended recipient, please be
aware that any disclosure, copying, distribution or use of the
contents of this information is prohibited. Please notify the sender
of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your
system.
RE: Oracle 10G and perl v5.8.0
Krishnan, Vijaya wrote: Just wanted to know if Oracle 10G and perl v5.8.0 are compatible or an upgrade to perl 6 would be required. They're compatible. I'd recommend using the latest stable version of Perl, though -- 5.8.8. (Perl 6 is not released yet...) Philip
RE: :Oracle - Any advance on inserting CLOB to XMLTYPE?
I ran this against v1.19 and had the same limit.
-Original Message-
From: Paul Gallagher [mailto:[EMAIL PROTECTED]
Sent: Friday, February 16, 2007 8:25 PM
To: Garrett, Philip (MAN-Corporate)
Cc: Reidy, Ron; dbi-users@perl.org
Subject: Re: :Oracle - Any advance on inserting CLOB to XMLTYPE?
Thanks Philip for the extra info.
I've attached a test case I'm working with .. wondering if anyone
using DBD 1.19 can try this out too and report the results?
Ron, can you check this also ... maybe your code is different in some
way?
For me the limit is at 63/64 elements (results are in the file)
perl xmltypeinsert-testcase.pl ORCL scott tiger 63
- is ok
perl xmltypeinsert-testcase.pl ORCL scott tiger 64
- fails
On 2/13/07, Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED]
wrote:
I can confirm I have the same problems as Paul when inserting large
XMLType.
Running on:
1. SUSE LINUX Enterprise Server 9 (i586)
2. DBI v1.50
3. DBD::Oracle v1.18
4. Oracle client: 9.2.0.4.0
5. Oracle server: 9.2.0.7.0 - 64bit
Regards,
Philip
Paul Gallagher wrote:
Thanks for the info Ron. I think you may not have quite hit the
limit
though.
I tried your code (are you explicitly typing the bind to
:file_header?
I'm assuming not), and for small data sizes its ok, but once I go
very
large it fails.
# fyi, I'm creating a long structure like this. max i 10 is ok, max
i
3000 is not:
my @books;
my %dslong;
for (my $i=1; $i3000; $i++) {
push(@books, {id = $i, title = [ the book $i title ] } );
}
$dslong{book} = [EMAIL PROTECTED];
# and binding like this:
$sth-bind_param(:file_header, XMLout( \%dslong , RootName =
books) );
NB: I'm presently testing this with DBD-Oracle-1.17 and DBI-1.52-r1
on
Windows (ActiveState)
On 2/13/07, Reidy, Ron [EMAIL PROTECTED] wrote:
Well, it works for me out of the box as advertised.
Code snippet:
my $sth_admin_audit_files = $dbh-prepare(qq{
INSERT INTO array_audit.admin_audit_files
(instance_id
,file_crdt
,fname
,file_header
,file_header_raw
)
VALUES
(:instance_id
,TO_DATE(:file_crdt, 'DD/MM/ HH24:MI:SS')
,:fname
,SYS.XMLType.CREATEXML(:file_header)
,:file_header_raw
)
RETURNING admin_audit_file_id, crmo
INTO :admin_audit_file_id, :crmo
}) || die $DBI::errstr;
Running on
1. RH Linux 3.0 and 4.0
2. DBI v1.47
3. DBD::Oracle v1.16
4. Oracle v9.2.0.5.0; v10.2.1.0.3; v10.2.0.2.0
My files are around the order of 37Kb in size and I do nothing
special
with them (aside from convert the text into XML). My user has only
insert on the table with the XMLTYPE in it.
Does the user running the Perl program have INSERT privs on the
table?
Is there a synonym issue or a role issue?
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Paul Gallagher [mailto:[EMAIL PROTECTED]
Sent: Monday, February 12, 2007 12:04 PM
To: dbi-users@perl.org
Subject: DBD::Oracle - Any advance on inserting CLOB to XMLTYPE?
It seems the current state of affairs is that for inserting to
XMLTYPE
fields:
a) for 32k, can just insert text
b) for 32k, must insert to CLOB, then use a procedure to update
XMLTYPE (see
http://www.issociate.de/board/post/206125/DBD::Oracle_and_XMLType.html
for a summary of the details)
Personally I've tried just about every trick in the book to try and
get a direct-insert of large xml documents into xmltype fields, but
to
no avail.
What troubles me is that this _should_ work:
# NB: CREATE TABLE tryit ( formname VARCHAR(25), x XMLTYPE )
INSERT INTO tryit (formname, x) VALUES (?, XMLTYPE(?))
$sth = $dbh-prepare( INSERT INTO tryit (formname, x) VALUES (?,
XMLTYPE(?)) );
$sth-bind_param(1, INSERTXMLTYPE );
$sth-bind_param(2, XMLout( \%dslong , RootName = books), { TYPE
=
SQL_CLOB } );
$sth-execute or warn INSERTXMLTYPE creation failure;
but it actually just gives ORA-00942: table or view does not exist.
this is a bogus message (search metalink for XMLTYPE ORA-00942).
try
an alternative like INSERT INTO tryit (formname, x) VALUES (?,
XMLTYPE(CAST(? as CLOB))) and you get ORA-00932: inconsistent
datatypes.
All of the above is old news I think.
Why I raise this now is that I discovered the python guys seem to
have
got it working OK. See
http://blog.gmane.org/gmane.comp.python.db.cx-oracle/month=20050401
connection = cx_Oracle.Connection(user/pw at tns)
cursor = connection.cursor()
cursor.setinputsizes(value = cx_Oracle.CLOB)
cursor.execute(insert into xmltable values (xmltype(:value)),
value = A very long XML string)
Seems very much like a binding issue on the DBI/DBD side.
Any thoughts?
~paul
This electronic message transmission is a PRIVATE communication
which contains information which may be confidential or privileged
RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2
Hi, RaviChandra Chelikam wrote: perl: fatal: /usr/local/opt/oracle/product/ldcnmd/lib32/libclntsh.so.9.0: Permission denied at . ^ The permissions on your Oracle installation are not letting Perl load the Oracle client library. The library file libclntsh.so.9.0 (or one of the directories leading to it more likely) is not accessible as the user running the Perl script. Fix the permissions and your program will probably work ok. Regards, Philip
RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2
You need to rebuild DBD::Oracle against the new 10g client you installed. You don't necessarily need to upgrade (which would be a good idea though), but you DO need to rebuild it. From: RaviChandra Chelikam [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 1:23 PM To: Garrett, Philip (MAN-Corporate) Cc: dbi-users@perl.org Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 Philip We have Changed the permissions . Even after that, I am getting the following error as install_driver(Oracle) failed: Can't load '/usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DBD/Oracle/Oracl e.so' for module DBD::Oracle: ld.so.1: perl: fatal: libclntsh.so.9.0: open failed: No such file or directory at /usr/local/lib/perl5/5.6.1/sun4-solaris/DynaLoader.pm line 206. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at a.pl line 25 Actually it is searching for libclntsh.so.9.0(since the previous oracle version was 9i) in /usr/local/opt/oracle/product/10.2.0/lib32/and Failing to open the file. But since we migrated to 10g now /usr/local/opt/oracle/product/10.2.0/lib32/ is having libclntsh.so.10.1 So, what should I do ,to point to libclntsh.so.10.1 instead of libclntsh.so.9.0 Thanks Regards Ravi -Original Message- From: Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 9:47 PM To: RaviChandra Chelikam; dbi-users@perl.org Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 Hi, RaviChandra Chelikam wrote: perl: fatal: /usr/local/opt/oracle/product/ldcnmd/lib32/libclntsh.so.9.0: Permission denied at . ^ The permissions on your Oracle installation are not letting Perl load the Oracle client library. The library file libclntsh.so.9.0 (or one of the directories leading to it more likely) is not accessible as the user running the Perl script. Fix the permissions and your program will probably work ok. Regards, Philip Tech Mahindra, formerly Mahindra-British Telecom. Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review at http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/Disclaimer.html internally within Tech Mahindra.
RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2
I've already answered this off-list. Here's the entire thread for posterity: You need to: 1) Transfer the .tar.gz to the remote machine 2) Unpack it there 3) cd into DBD-Oracle-1.15 4) read the README and follow the instructions. You will need a C compiler on that machine, and you'll need a full install of the Oracle client (meaning it has the headers necessary to compile OCI and Pro*C applications). From: RaviChandra Chelikam [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 2:11 PM To: Garrett, Philip (MAN-Corporate) Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 Philip I Have downloaded the DBD::Oracle 1.15 from the link u have given and copied to my local machine. Should I compile it first in my local machine and then kept it in the remote machine where the oracle 10g is installed . Else Should I directly copy the whole folder DBD::Oracle 1.15 to remote machine and then compile. Actually the folder structure of DBD::Oracle 1.15 which I downloaded from the cpan link and the one which is already existing in remote machine is different. There is not even Makefile.pl file in the remote machine. Then how could I compile it in the remote machine. Could u plz let me know. Thanks Regards Ravi From: Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 12:29 AM To: RaviChandra Chelikam Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 (2) is probably the safest route (least likely to require code changes). (3) is probably best long-term, but it might also require upgrading Perl and DBI. From: RaviChandra Chelikam [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 1:53 PM To: Garrett, Philip (MAN-Corporate) Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 Philip Sorry for disturbing u again. Since I am new to perl , I am getting many doubts. Thanks for ur patience and helping me . 1. Could u plz let me know whether , i should rebuild the existing DBD::Oracle 1.15 which is already existing in my application. If so, could u plz tell me the steps how to rebuild DBD::Oracle against the new 10g client . 2. Else should I download the DBD::Oracle 1.15 from the path u have given and then rebuild against the new 10g client. 3. Else should I download new version of DBD::Oracle and then rebuild against the new 10g client. Thanks Regards Ravi From: Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 11:55 PM To: RaviChandra Chelikam Cc: dbi-users@perl.org Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 You need to rebuild DBD::Oracle against the new 10g client you installed. You don't necessarily need to upgrade (which would be a good idea though), but you DO need to rebuild it. From: RaviChandra Chelikam [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 1:23 PM To: Garrett, Philip (MAN-Corporate) Cc: dbi-users@perl.org Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 Philip We have Changed the permissions . Even after that, I am getting the following error as install_driver(Oracle) failed: Can't load '/usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DBD/Oracle/Oracl e.so' for module DBD::Oracle: ld.so.1: perl: fatal: libclntsh.so.9.0: open failed: No such file or directory at /usr/local/lib/perl5/5.6.1/sun4-solaris/DynaLoader.pm line 206. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at a.pl line 25 Actually it is searching for libclntsh.so.9.0(since the previous oracle version was 9i) in /usr/local/opt/oracle/product/10.2.0/lib32/and Failing to open the file. But since we migrated to 10g now /usr/local/opt/oracle/product/10.2.0/lib32/ is having libclntsh.so.10.1 So, what should I do ,to point to libclntsh.so.10.1 instead of libclntsh.so.9.0 Thanks Regards Ravi -Original Message- From: Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 9:47 PM To: RaviChandra Chelikam; dbi-users@perl.org Subject: RE: install_driver(Oracle) failed while connecting to oracle 10.2.0.2 Hi, RaviChandra Chelikam wrote: perl: fatal: /usr/local/opt/oracle/product/ldcnmd/lib32/libclntsh.so.9.0: Permission denied
RE: How to call DBD::Oracle's dbms_output_get from within a DBIx
Tim Bunce wrote:
On Thu, Feb 22, 2007 at 09:12:14AM +, Martin Evans wrote:
Tim Bunce wrote:
On Wed, Feb 21, 2007 at 04:25:54PM +, Martin J. Evans wrote:
and I still get
Deep recursion on subroutine DBD::Oracle::db::dbms_output_get
dbms_output_get does create a new statement, prepare and execute it
and it appears when execute is called it ends up back in my
DBIx::Log4perl::execute method. I don't understand why this happens
:-(
Ah. Of course. Why would you except it not to happen? ;-)
dbms_output_get calls execute() on a statement handle created from
your subclassed dbh handle. So your DBIx::Log4perl::st::execute will
be called when dbms_output_get calls the execute method.
You need to either use a separate non-DBIx::Log4perl dbh for the
dbms_output_get call, or try something more hackish like
my @d = $dbh-func('dbms_output_get')
unless $sth-{Statement} =~ /^begin dbms_output.get_line/;
A slightly more efficient (maybe less hackish? ;-) way would be to use a
localized package variable as a semaphore:
sub execute {
my ($sth, @args) = @_;
my $ret = $sth-SUPER::execute(@args);
if (!$DBIx::Log4perl::st::DBMS_OUTPUT_GET) {
local $DBIx::Log4perl::st::DBMS_OUTPUT_GET = 1;
my $dbh = $sth-FETCH('Database');
my @d = $dbh-func('dbms_output_get');
}
return $ret;
}
- Philip
RE: DBI 1.53 Compile Does not Pass Test on RHEL 3.0
Unless you plan to use DBI and threads in the same process, you can ignore that test. -Original Message- From: Mike Southworth [mailto:[EMAIL PROTECTED] Sent: Thursday, February 22, 2007 12:23 AM To: dbi-users@perl.org Subject: DBI 1.53 Compile Does not Pass Test on RHEL 3.0 I have been trying all night to get DBI 1.53 installed on Perl 5.008 that comes with RHEL 3.0. When CPAN failed I have tried downloading and compiling, however I get the same problems. Here is a snapshot of the process: [EMAIL PROTECTED] DBI-1.53]# perl Makefile.PL *** You are using a perl configured with threading enabled. *** You should be aware that using multiple threads is *** not recommended for production environments. *** Note: The optional PlRPC-modules (RPC::PlServer etc) are not installed. If you want to use the DBD::Proxy driver and DBI::ProxyServer modules, then you'll need to install the RPC::PlServer, RPC::PlClient, Storable and Net::Daemon modules. The CPAN Bundle::DBI may help you. You can install them any time after installing the DBI. You do *not* need these modules for typical DBI usage. Optional modules are available from any CPAN mirror, in particular http://search.cpan.org/ http://www.perl.com/CPAN/modules/by-module http://www.perl.org/CPAN/modules/by-module ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module Your perl was compiled with gcc (version 3.2.3 20030502 (Red Hat Linux 3.2.3-54)), okay. Creating DBI::PurePerltest variant: t/zvpp_01basics.t Creating DBI::PurePerltest variant: t/zvpp_02dbidrv.t Creating DBI::PurePerltest variant: t/zvpp_03handle.t Creating DBI::PurePerltest variant: t/zvpp_04mods.t Creating DBI::PurePerltest variant: t/zvpp_05thrclone.t (use threads) Creating DBI::PurePerltest variant: t/zvpp_06attrs.t Creating DBI::PurePerltest variant: t/zvpp_07kids.t Creating DBI::PurePerltest variant: t/zvpp_08keeperr.t Creating DBI::PurePerltest variant: t/zvpp_09trace.t Creating DBI::PurePerltest variant: t/zvpp_10examp.t Creating DBI::PurePerltest variant: t/zvpp_11fetch.t Creating DBI::PurePerltest variant: t/zvpp_14utf8.t Creating DBI::PurePerltest variant: t/zvpp_15array.t Creating DBI::PurePerltest variant: t/zvpp_20meta.t Creating DBI::PurePerltest variant: t/zvpp_30subclass.t Creating DBI::PurePerltest variant: t/zvpp_40profile.t Creating DBI::PurePerltest variant: t/zvpp_41prof_dump.t Creating DBI::PurePerltest variant: t/zvpp_42prof_data.t Creating DBI::PurePerltest variant: t/zvpp_43prof_env.t Creating DBI::PurePerltest variant: t/zvpp_50dbm.t Creating DBI::PurePerltest variant: t/zvpp_60preparse.t Creating DBI::PurePerltest variant: t/zvpp_70callbacks.t Creating DBI::PurePerltest variant: t/zvpp_72childhandles.t Creating DBI::PurePerltest variant: t/zvpp_80proxy.t I see you're using perl 5.008 on i386-linux-thread-multi, okay. Remember to actually *read* the README file! Use 'make' to build the software (dmake or nmake on Windows). Then 'make test' to execute self tests. Then 'make install' to install the DBI and then delete this working directory before unpacking and building any DBD::* drivers. Writing Makefile for DBI [EMAIL PROTECTED] DBI-1.53]# make gcc -c -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -march=i386 -mcpu=i686 -DVERSION=\1.53\ -DXS_VERSION=\1.53\ -fPIC -I/usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE -W -Wall -Wpointer-arith -Wbad-function-cast -Wno-comment -Wno-sign-compare -Wno-cast-qual -Wmissing-noreturn -Wno-unused-parameter DBI.c Running Mkbootstrap for DBI () chmod 644 DBI.bs rm -f blib/arch/auto/DBI/DBI.so gcc -shared -L/usr/local/lib DBI.o -o blib/arch/auto/DBI/DBI.so \ \ chmod 755 blib/arch/auto/DBI/DBI.so cp DBI.bs blib/arch/auto/DBI/DBI.bs chmod 644 blib/arch/auto/DBI/DBI.bs cp dbiprof blib/script/dbiprof /usr/bin/perl -MExtUtils::MY -e MY-fixin(shift) blib/script/dbiprof cp dbiproxy blib/script/dbiproxy /usr/bin/perl -MExtUtils::MY -e MY-fixin(shift) blib/script/dbiproxy Manifying blib/man1/dbiprof.1 Manifying blib/man1/dbiproxy.1 Manifying blib/man3/DBI::FAQ.3pm Manifying blib/man3/DBD::Proxy.3pm Manifying blib/man3/DBI::SQL::Nano.3pm Manifying blib/man3/DBI::Const::GetInfo::ANSI.3pm Manifying blib/man3/DBD::DBM.3pm Manifying blib/man3/DBI::Const::GetInfoReturn.3pm Manifying blib/man3/DBI.3pm Manifying blib/man3/DBD::Sponge.3pm Manifying blib/man3/DBI::Const::GetInfoType.3pm Manifying blib/man3/Roadmap.3pm Manifying blib/man3/DBI::DBD::Metadata.3pm Manifying blib/man3/DBI::W32ODBC.3pm Manifying blib/man3/DBI::Const::GetInfo::ODBC.3pm Manifying blib/man3/DBI::ProfileDumper::Apache.3pm Manifying blib/man3/Bundle::DBI.3pm Manifying blib/man3/TASKS.3pm Manifying blib/man3/DBI::Profile.3pm Manifying
RE: DBI 1.53 Compile Does not Pass Test on RHEL 3.0
William R. Mussatto wrote: On Thu, February 22, 2007 15:35, Garrett, Philip \(MAN-Corporate\) said: Unless you plan to use DBI and threads in the same process, you can ignore that test. What is the impact of mod_apache if you are using threaded apache server (vs. child spawning). I'm not sure, but that falls outside the unless I posed. Philip
RE: (Fwd) DBI's method for reading [row x,field y]
Hi Bob,
- Forwarded message from Bob Hunter [EMAIL PROTECTED]
-
Date: Tue, 27 Feb 2007 08:03:19 -0800 (PST)
From: Bob Hunter [EMAIL PROTECTED]
Subject: DBI's method for reading [row x,field y]
Tim,
I am porting an application from Pg to DBI, and make extensive use of
the following method:
Returns the value of the given record and field number:
$sth-getvalue($rn,$fn)
In particular, I use expressions like
$sth-getvalue($rn+$i,$fn-$j)
where the the number of record/field are displaced by variables. As
far as I can see from your book, DBI has a method to work one row at
the time, in sequence. This is way too simple to handle the case
above. I looked for a more powerful DBI method, but it does not seem
to exist. Is it so? Please give me an insider's view of this problem.
Does DBI have an equivalent to Pg' method getvalue? If not, can you
please copy it from Pg's module, and make it available in DBI?
If you absolutely MUST refer to query results by (x,y) coordinates,
you can use fetchall_arrayref. I don't know your application, but this
is definitely not the most efficient way to do things with large
result sets. You'd probably be better off in the long run by
converting your code to use the while ($sth-fetch) idiom.
# pulls entire result set from database to client
my $sth = $dbh-prepare(some sql);
$sth-execute();
my $results = $sth-fetchall_arrayref();
my $ntuples = @$results;
my $nfields = $sth-{NUM_OF_FIELDS};
for (my $i = 0; $i $ntuples; $i++) {
for (my $j = 0; $j $nfields; $j++) {
# replacement for getvalue()
my $value = $results-[$i][$j];
print Value at (i,j): $value\n;
}
}
Regards,
Philip
RE: (Fwd) DBI's method for reading [row x,field y]
-Original Message-
From: Bob Hunter [mailto:[EMAIL PROTECTED]
Sent: Tue 2/27/2007 5:07 PM
To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
Cc: [EMAIL PROTECTED]
Subject: RE: (Fwd) DBI's method for reading [row x,field y]
The same example, using Pg...
# use Pg;
# my $dbh = Pg::connectdb(dbname=dbname);
# my $sth = $dbh-exec(SQL STATEMENT);
# for (my $i = 0; $i $sth-ntuples; $i++) {
# for (my $j = 0; $j $sth-nfields; $j++) {
#print Value at ($i,$j):
$sth-getvalue($i,$j)\n;
# }}
It is more concise, and more intuitive.
It is a pity that DBI is so cumbersome.
Yes, DBI is faster and independent from specific
databases, but Pg is far more elegant. Too bad one
cannot have both worlds.
Beauty is in the eye of the beholder. Your code example is not very
Perlish -- it is accessing database records at a low level like
a C-style multidimensional array, rather than as named fields in records.
I, for one, think this is more intuitive than your example, since it
treats the data by name rather than by some computer-assigned
numeric index:
my $dbh = DBI-connect;
my $sth = $dbh-prepare(SELECT * FROM BOOKS);
$sth-execute;
while (my $book = $sth-fetchrow_hashref) {
print $book-{ISBN}: $book-{TITLE}\n;
}
or
my $sth = $dbh-prepare(SELECT TITLE, ISBN FROM BOOKS);
$sth-execute;
while (my ($title,$isbn) = $sth-fetchrow) {
print $isbn: $title\n;
}
Regards,
Philip
RE: Perl-Postgres connection: 'Commit' method not found in DBI. Advice?
It looks like you're calling commit() on the $sth instead of the $dbh. Philip Andrew Edson wrote: I am working with a perl file intended to enter a postgresql database (as user postgres), select a list of key values based on one of those values and delete any records which match the returned results. The purpose of the program is to delete inactive records from the database that are over two years in age. (For the record, the system in question is running SuSE 9.3 os, Apache2, Postgres 8.1.3, and appears to be running Perl 5.8.6) When I first ran the perl script, it did not appear to be working; entering the database afterwards would show that the data was still present. After finding and uncommenting a 'commit' statement at the end, I am now ending up with the error message Can't locate object method commit via package DBI::st at ./purge_acnt line 242 (#1) (F) You called a method correctly, and it correctly indicated a package functioning as a class, but that package doesn't define that particular method, nor does any of its base classes. See perlobj. Uncaught exception from user code: Can't locate object method commit via package DBI::st at ./purge_acnt line 242. at ./purge_acnt line 242 Database handle destroyed without explicit disconnect at ./purge_acnt line 242. Database handle destroyed without explicit disconnect at ./purge_acnt line 242. I am asuming that the last two lines are because the program crashed, artificially severing the connections to the DB. (The last couple of lines of the program are normal disconnect statements, but occur after the 'commit' line that triggered the error.) It's the rest of the error that I am confused about. If I am reading it correctly, it seems to be saying that there is no 'commit' function in the DBI. Do I need to update my DBI files somehow, or is there another way to prevent this error from occuring? And if I have to update, would someone please be kind enough to link me to a good set of instructions on doing so? Thank you for your consideration. - Sucker-punch spam with award-winning protection. Try the free Yahoo! Mail Beta.
RE: Installing DBD::Oracle on Cygwin/Windows XP 32 bit. Make test not resolving TNS
April Blumenstiel wrote: Hello, I have downloaded the instant client and the header files for Oracle 10g, and DBD::Oracle is compiling without error, but the make tests are failing. The TNS is failing to resolve. I have set my TNS_ADMIN var to the location of the file. I know it's a good TNS file, because it works fine on my old system. Does SQL*Plus work? If not, get that working first, then try DBD::Oracle. If I recall correctly, you can't use an environment variable for Oracle stuff on Windows -- you have to use a registry setting instead. But don't have any facts to back that up :-) - Philip
RE: Installing DBD::Oracle on Cygwin/Windows XP 32 bit. Make test not resolving TNS
Have you set TWO_TASK or ORACLE_SID? What's the exact error you're getting from the test suite? From: April Blumenstiel [mailto:[EMAIL PROTECTED] Sent: Monday, March 05, 2007 3:37 PM To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org Subject: Re: Installing DBD::Oracle on Cygwin/Windows XP 32 bit. Make test not resolving TNS Oh, actually, just tried it again and sqlplus is working fine. I just had to pass it the connection string to the remote DB when I ran it. On 3/5/07, April Blumenstiel [EMAIL PROTECTED] wrote: That was a good tip, because I just tried sqlplus and I get 'ORA-12560: TNS: protocol adapter error So there's a clue. The client is working, and I tried tnsping and it was fine. It used the TNS file that I have in my TNS_ADMIN var. And I've been using Toad. So it's intersting that slqplus and OCI don't seem to be working. Still hunting... On 3/5/07, Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: April Blumenstiel wrote: Hello, I have downloaded the instant client and the header files for Oracle 10g, and DBD::Oracle is compiling without error, but the make tests are failing. The TNS is failing to resolve. I have set my TNS_ADMIN var to the location of the file. I know it's a good TNS file, because it works fine on my old system. Does SQL*Plus work? If not, get that working first, then try DBD::Oracle. If I recall correctly, you can't use an environment variable for Oracle stuff on Windows -- you have to use a registry setting instead. But don't have any facts to back that up :-) - Philip
RE: .pl code errors after oracle upgrade
Arokia Samy Joseph wrote: Hi, We recently upgraded our oracle database 8.1.7.4 to 9.2.6.0 After the update we tried to execute a .pl code. The following error is listed. Your suggestion / solution is much appreciated . Thanks! ld.so.1: perl: fatal: libclntsh.so.8.0: ^^^ You need to reinstall DBD::Oracle, because it is linked with the old version of the Oracle libraries (which have been replaced by version 9). - Philip
RE: Unexpected error with DBD::SQLite
This works on my (admittedly ancient) DBD::SQLite v1.11.
However, if you're running version 2, this looks like it might be a bug
in DBD::SQLite, based on the first section of 5.0 Examples here:
http://www.sqlite.org/datatypes.html.
It might be helpful to submit a defect on rt.cpan.org per the
DBD::SQLite docs.
- Philip
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Will Parsons
Sent: Tuesday, March 13, 2007 9:41 PM
To: dbi-users@perl.org
Subject: Unexpected error with DBD::SQLite
This program produces an error which looks wrong to me:
---
#!/usr/bin/env perl
use strict;
use DBI;
my $dbh = DBI-connect('dbi:SQLite:test.db', '', '',
{ AutoCommit = 1,
PrintError = 0,
RaiseError = 1 });
eval { $dbh-do(drop table X); };
$dbh-do(create table X(a TEXT, b TEXT, unique(a, b)));
my $sth = $dbh-prepare(insert into X(a, b) values('N', ?));
$sth-execute('1.1');
$sth-execute('1.10');
---
When run, I see:
$ ./test-sqlite.pl
DBD::SQLite::st execute failed: columns a, b are not unique(1) at
dbdimp.c line 401 at ./test-sqlite.pl line 17.
Is this correct behaviour?
- Will
