Re: please help with DBD-Oracle-1.76
Also the SDK package needs to be installed or the build fails. Minimum for DBD::Oracle are the Basic and SDK packages. On Sep 5, 2022, at 6:43 AM, kmo...@esntech.com<mailto:kmo...@esntech.com> wrote: Hi Daniel, I have three questions. 1. Did you install the instant client as root? I always install it as Oracle. 2. LD_LIBRARY_PATH should be set as =/root/ora2pg/instantclient_21_7/lib in your case 3. The link you provided has a download to sqlplus, I’d suggest you download and install it to satisfy what is in your post Regards, Kevin From: Tim Bunce mailto:tim.bu...@pobox.com>> Sent: Monday, September 5, 2022 3:16 AM To: dbi-users@perl.org<mailto:dbi-users@perl.org> Subject: Fwd: please help with DBD-Oracle-1.76 Begin forwarded message: From: Daniel Chmielewski mailto:daniel.chmielew...@gmail.com>> Subject: Fwd: please help with DBD-Oracle-1.76 Date: 5 September 2022 at 10:03:15 IST To: t...@cpan.org<mailto:t...@cpan.org> Tim, please help. Regards, Daniel -- Forwarded message - Od: Daniel Chmielewski mailto:daniel.chmielew...@gmail.com>> Date: śr., 31 sie 2022 o 15:10 Subject: please help with DBD-Oracle-1.76 To: mailto:t...@cpan.org>> Hi TIm, Can you help me. I have problem with installing DBD-Oracle-1.76. At all it works (make install), but tests fail (make tests). It seems that the software does not recognized oracle instant client version 21.7. This is a 'portable' version with only requires unpack in place. Have you ever made any test with this ? Please help. Do you have any suggestions? Regards, Daniel Short description: 1. DBD-Oracle-1.76 requires DBI, and all the tests of DBI works fine. 2. DBI-1.63 (all tests are successful, make, make test, make install => no error at all). 3. I also install instant client from oracle: https://www.oracle.com/pl/database/technologies/instant-client/linux-x86-64-downloads.html export ORACLE_HOME=/root/ora2pg/instantclient_21_7 export LD_LIBRARY_PATH=/root/ora2pg/instantclient_21_7 export PATH=$ORACLE_HOME/bin:$PATH 4. and set env. variable (all at root users), database is located on other server, not on this on which is perl installed. But with DBD-Oracle-1.76 installed on it I get the following error: root@stlx DBD-Oracle-1.76]# perl Makefile.PL Using DBI 1.643 (for perl 5.026003 on x86_64-linux-thread-multi) installed in /usr/local/lib64/perl5/auto/DBI/ Configuring DBD::Oracle for perl 5.026003 on linux (x86_64-linux-thread-multi) If you encounter any problem, a collection of troubleshooting guides are available under lib/DBD/Oracle/Troubleshooting. 'DBD::Oracle::Troubleshooting' is the general troubleshooting guide, while platform-specific troubleshooting hints live in their labelled sub-document (e.g., Win32 hints are gathered in 'lib/DBD/Oracle/Troubleshooting/Win32.pod'). Installing on a linux, Ver#4.18 Using Oracle in /root/ora2pg/instantclient_21_7 Can't find sqlplus. Pity, it would have helped. I'm having trouble finding your Oracle version number... trying harder WARNING: Could not determine Oracle client version, defaulting to version 9.2.0.4.0. Some features of DBD::Oracle may not work. Oracle version-based logic in Makefile.PL may produce erroneous results. You can use "perl Makefile.PL -V X.Y.Z" to specify your client version. Oracle Version 9.2.0.4.0 (9.2) Looks like an Instant Client installation, okay Your LD_LIBRARY_PATH env var is set to '/root/ora2pg/instantclient_21_7' Oracle sysliblist: Found header files in /root/ora2pg/instantclient_21_7/sdk/include. client_version=9.2 DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"9.2.0.4.0\" Checking for functioning wait.ph<http://wait.ph/> _FORTIFY_SOURCE requires compiling with optimization (-O) at /usr/lib64/perl5/features.ph<http://features.ph/> line 207. System: perl5.026003 linux x86-038.build.eng.bos.redhat.com<http://x86-038.build.eng.bos.redhat.com/> 4.18.0-305.17.1.el8_4.x86_64 #1 smp mon aug 30 07:26:31 edt 2021 x86_64 x86_64 x86_64 gnulinux Compiler: gcc -g -D_REENTRANT -D_GNU_SOURCE -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fwrapv -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 Linker: /usr/bin/ld Sysliblist: Linking with -lclntsh. LD_RUN_PATH=/root/ora2pg/instantclient_21_7 Using DBD::Oracle 1.76. Using DBD::Oracle 1.76. = ==== Please help, Daniel -- Daniel Chmielewski -- Daniel Chmielewski -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Strange issue with inserting varchar2()
That would work, I think. Or, just changing the datatype in the table for the
comments to a 4K CLOB in the *first* place, then the perl side truncate would
work. This table is truncated each year, so there’s no real need to maintain
any sort of historical data.
On May 28, 2021, at 3:55 AM, jurl...@urlwincc.com<mailto:jurl...@urlwincc.com>
wrote:
What about truncating the data in PLSQL? Eg an insert function? It would have
to input a CLOB I would guess…
Sent from my iPhone
On May 28, 2021, at 4:15 AM, Marcus Bergner
mailto:marcus.berg...@vizrt.com>> wrote:
The way I have done it is to ensure that truncation only happens on valid UTF-8
boundaries but length calculation is done in "use bytes" mode, and then try to
guesstimate where to make the UTF-8 cut, and try that repeatedly until you
manage to go under the max size you specified. Then you should have a valid
UTF-8 string whos' byte encoded representation is less than the given number of
bytes. There might be circumstances where this truncates slightly more but has
worked well in practice for me for exactly this purpose of storing slightly too
long text values in varchar columns.
sub truncByteString
{
my ($str, $bytes) = @_;
my ($len,$blen);
return undef unless defined $str;
while(1) {
do { use bytes; $blen = length $str };
last unless $blen > $bytes;
$len = length $str;
my $scaled_diff = int (($blen-$bytes)/($blen/$len))-1;
$scaled_diff = 1 if $scaled_diff<1;
my $nlen = $len - $scaled_diff;
$str = substr $str,0,$nlen;
}
return $str;
}
/ Marcus
--
Marcus Bergner, M.Sc CSEE-mail:
marcus.berg...@vizrt.com<mailto:marcus.berg...@vizrt.com>
Software Architect, Vizrt.Mobile: +46 (0)730-808025
From: Fennell, Brian mailto:fenne...@radial.com>>
Sent: Friday, May 28, 2021 03:11
To: Bruce Johnson
mailto:john...@pharmacy.arizona.edu>>; dbi users
mailto:dbi-users@perl.org>>
Subject: RE: Strange issue with inserting varchar2()
One more
https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fstackoverflow.com%2Fquestions%2F1454952%2Fdummys-guide-to-unicodedata=04%7C01%7CMarcus.Bergner%40vizrt.com%7C1767beb561424d97b19f08d92175a617%7Cc63c3ba740db460ebdf1e63a02a3ad59%7C0%7C0%7C637577611463282317%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000sdata=wqFX4kaJyubBeA2vuUo4U5bhggujKZcyukf1%2FN5%2BwEg%3Dreserved=0
The information contained in this electronic mail transmission is intended only
for the use of the individual or entity named in this transmission. If you are
not the intended recipient of this transmission, you are hereby notified that
any disclosure, copying or distribution of the contents of this transmission is
strictly prohibited and that you should delete the contents of this
transmission from your system immediately. Any comments or statements contained
in this transmission do not necessarily reflect the views or position of Radial
or its subsidiaries and/or affiliates.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Strange issue with inserting varchar2()
I’m beginning to think that just truncating the string to something well short of the column limit is going to be the simplest way to go. This has been a persistent issue in the past with earlier versions of this app that were handled by the previous programmer/dba by…manually editing the file to fit by trial and error. It happens with just a few records every time (this is a once-a-year process) and it’s always like this: the input is too long by a relative handful of bytes. (The actual data is supposed to be *short* comments in a review process, and some of the reviewers are really wordy. All I ever get is a csv of the responses, so I’m limited in my options. I could move to a BLOB but this is a lot of work to manage a few responses in this system. We retain the original data always so if anyone really wants the whole novella-length ’short comment’ for review we can get it. :-) Thanks for the deep dive into the complications I'm facing! A lot of food for thought. On May 27, 2021, at 5:09 PM, Fennell, Brian mailto:fenne...@radial.com>> wrote: There are also "lossless" ways of encoding "illegal characters", such as UTF-8 encode first then Mime Q-Encode (quoted printable) or using XML / XHTML entities like this - or UTF-8 encode then percent-encode. This can be used work around special characters when you are dealing with mostly English with some European characters and an encoding that is either 7-bit-ascii compatible or can easily be converted to and from (losslessly) 7-bit-ascii (such as EBCIDIC with some creativity). And there is always good old hexadecimal. It all depends on your use-cases, how much control you have over your environment and how much time you can spend being clever. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Strange issue with inserting varchar2()
I'm working on an app that processes (among other thing) long sections of text, and I’m running into odd multi-byte characters in some of the entries. The column in question is a varchar2(4000) so I am truncating the input to 4000 bytes. (via use “bytes; $string=substr($orig,0,4000); ” in the section where I actually truncate the string.) When I do the insert I get an ORA-12899: value too large for column "AWARD"."PRECEPT_NOMINATIONS"."ONCOLOGY_COMMENTS" (actual: 4054, maximum: 4000) error When I check the actual length of the offending string in bytes on the perl side it is 4000 *bytes* long Original Oncology comment is 4804 chars (4824 bytes) long and when truncated in character mode to 4000 chars it’s 4018 bytes long, when truncated in byte mode, it’s 4000 chars and bytes. What’s confusing me is that the size of the string being reported by oracle in the error doesn’t match any of the values that I see in perl. I’m not sure this is a DBI issue or not. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: DBI.pm - Memoryfault(coredump)
On Oct 26, 2020, at 7:31 PM, Garry T. Williams mailto:gtwilli...@gmail.com>> wrote: On Monday, October 26, 2020 11:47:54 AM EDT David Nicol wrote: if the Oracle module is maintained by red hat, yum update should help. If you built it in-house, you may need to yum update the perl development environment and reinstall. This is good advice. The original poster also should make sure the Oracle C-API libraries are still installed properly on the client machine. It's impossible to really tell from what the original poster wrote what the code that is running is doing. (The code he posted is a shell script that executes the actual code, which is unknown to us.) But... It probably is DBD::Oracle running under DBI. That is the preferred, simple, and reliable way to access Oracle databases from Perl. There is no need to complicate things with an intermediate Java program. Just use the direct interface to the Oracle C-API -- DBD::Oracle. (I have used Perl DBD::Oracle for over 20 years on many different operating systems and releases with zero problems.) The error the original poster is getting is probably due to the DBD::Oracle module never being updated to the new RHEL release. Fixing it may require recompiling by hand, using CPAN. (I do not know if RHEL has an already-built DBD::Oracle module available from their repositories. I install from CPAN -- that is compile -- in Fedora.) Or the client isn’t installed or the environment variables are not set properly, or, or... RHEL, Fedora and CentOS do NOT include DBD::Oracle in their pre-built perl modules. Like Gary I’ve always had to build it with CPAN. You need to have an Oracle client setup installed first. By far the simplest method is to install the Oracle Instant Client, (literally download the rpms from Oracle and run yum localinstall rpmfile.rpm ) you need the base IC and the SDK rpms. I usually also install the SQL*Plus package but that is not strictly necessary. It is, however, very useful for troubleshooting connectivity. Then establish the ORACLE_HOME, LD_LIBRARY_PATH and TNS_ADMIN Environment variables and appropriate config file for the latter, then build DBD::Oracle in CPAN. I've never had an issue doing it that way. If you’re using JDBC I think all you need is the base client install. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: DBD::Oracle 1.80 & Oracle client 19c Segfaults on Destroy
> On Jun 19, 2020, at 3:48 PM, Scott wrote: > > We have run into an issue when we upgraded to Oracle client 19c. Some of the > users processes are segfaulting on exit. > > #0 0x7f82ee84ccc0 in pthread_mutex_lock () from /lib64/libpthread.so.0 > #1 0x7f82e6444f43 in kputxabt () from > /u01/app/oracle/product/19.3.0.0/lib/libclntsh.so.19.1 > #2 0x7f82e926e6c3 in ora_db_rollback () from > /usr/local/perl-5.22.0-thr/lib/site_perl/5.22.0/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so > #3 0x7f82e9266b11 in XS_DBD__Oracle__db_DESTROY () from > /usr/local/perl-5.22.0-thr/lib/site_perl/5.22.0/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so > #4 0x7f82ed10291d in XS_DBI_dispatch () from > /usr/local/perl-5.22.0-thr/lib/site_perl/5.22.0/x86_64-linux-thread-multi/auto/DBI/DBI.so > > I tested the same process on a server still using the 18c client and the > core dump does not happen. Our DBA is creating > ticket with Oracle, but I wanted to see if anyone else has had the same issue. I am not seeing this issue with the 19C instant client ( 19.6 ) running on CentOS8, DBD::Oracle 1.80, DBI 1.643. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: frustrating error DBD::mysql CentOS8
Well I got it to work, but I don’t understand why the fix worked.
A person on the mod_perl list suggested I go over my ‘use’ statements in the
mod_perl startup file run when Apache is initialized.
The script worked without errors in mod_perl after adding
'/usr/lib64/perl5/vendor_perl’ to my ‘use’ statement in startup.pl, EVEN THOUGH
it’s already in @INC (the list below were from before I made this change) , and
DBD::mysql itself is located in /usr/local/lib64/perl5/DBD.
(Based on Andreas suggestion about libraries I uninstalled the CentOS
perl-DBD-MySQL package and then installed it from scratch via cpan so that the
installed mariadb client libraries were used to compile it...which by itself
didn’t fix the issue)
I still don’t know why DBI was throwing that peculiar error about DBD::mysql.
Going to put this in my notes, edge slowly away and not mess with it more :-)
On Jun 3, 2020, at 10:37 AM, Bruce Johnson
mailto:john...@pharmacy.arizona.edu>> wrote:
On Jun 2, 2020, at 12:30 PM, Andreas Mock
mailto:andreas.m...@web.de>> wrote:
Hi Bruce,
only some hints. It sounds to me like a shared object mess which may be
possible after upgrading.
It may be something in my mod_perl environment.
I constructed a quick test script that queries the user table.
#!/usr/bin/perl
use strict;
use DBI;
use lib "/home/allwebfiles/perl/LocalModules";
use PharmApps::AllDefaults qw(PrintHeader);
my $dsn = "DBI:mysql:database=mysql;host=localhost;port=3306" ;
my $dbh = DBI->connect($dsn, ‘xx', ‘xx') or print $DBI::errstr;
my $csr= $dbh->prepare("select Host, User from user");
my ($i, $j);
$csr->execute();
print PrintHeader;
print "HostUser\n";
while (($i, $j) = $csr->fetchrow()){print "$i$j\n";}
print "\n";
print "\@INC\n";
foreach $i (sort @INC){print "$i \n";}
print "";
On the command line it works without error. When I altered the settings to run
it as a cgi script (ie not with a mod_perl handler) it also works; it appears
that the error is only happening in the context of the script running in Apache
(with ModPerl enabled.)
HostUser
% pharmacyevents
127.0.0.1 root
localhost pharmacyevents
localhost root
@INC
/home/allwebfiles/perl/LocalModules
/home/allwebfiles/perl/LocalModules/
/usr/lib64/perl5
/usr/lib64/perl5/vendor_perl
/usr/local/lib64/perl5
/usr/local/share/perl5
/usr/share/perl5
/usr/share/perl5/vendor_perl
The only difference I can see is that the @INC when it’s running in ModPerl is
that two additional paths are included:
@INC
/etc/httpd <<<<
/home/allwebfiles/perl/LocalModules
/home/allwebfiles/perl/LocalModules/
/usr/lib64/perl5
/usr/lib64/perl5/vendor_perl
/usr/lib64/perl5/vendor_perl/Bundle <<<<
/usr/local/lib64/perl5
/usr/local/share/perl5
/usr/share/perl5
/usr/share/perl5/vendor_perl
Adding those two paths to @INC (via 'use lib’ statements) doesn’t break the
script in CGI mode.
Very strange, because I cannot think of any reason that running it under
mod_perl would cause a problem with DBI, it doesn’t happen with any other
systems I’m running it on. I”m going to take this off to the mod_perl list, I
guess.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: frustrating error DBD::mysql CentOS8
On Jun 2, 2020, at 12:30 PM, Andreas Mock
mailto:andreas.m...@web.de>> wrote:
Hi Bruce,
only some hints. It sounds to me like a shared object mess which may be
possible after upgrading.
It may be something in my mod_perl environment.
I constructed a quick test script that queries the user table.
#!/usr/bin/perl
use strict;
use DBI;
use lib "/home/allwebfiles/perl/LocalModules";
use PharmApps::AllDefaults qw(PrintHeader);
my $dsn = "DBI:mysql:database=mysql;host=localhost;port=3306" ;
my $dbh = DBI->connect($dsn, ‘xx', ‘xx') or print $DBI::errstr;
my $csr= $dbh->prepare("select Host, User from user");
my ($i, $j);
$csr->execute();
print PrintHeader;
print "HostUser\n";
while (($i, $j) = $csr->fetchrow()){print "$i$j\n";}
print "\n";
print "\@INC\n";
foreach $i (sort @INC){print "$i \n";}
print "";
On the command line it works without error. When I altered the settings to run
it as a cgi script (ie not with a mod_perl handler) it also works; it appears
that the error is only happening in the context of the script running in Apache
(with ModPerl enabled.)
HostUser
% pharmacyevents
127.0.0.1 root
localhost pharmacyevents
localhost root
@INC
/home/allwebfiles/perl/LocalModules
/home/allwebfiles/perl/LocalModules/
/usr/lib64/perl5
/usr/lib64/perl5/vendor_perl
/usr/local/lib64/perl5
/usr/local/share/perl5
/usr/share/perl5
/usr/share/perl5/vendor_perl
The only difference I can see is that the @INC when it’s running in ModPerl is
that two additional paths are included:
@INC
/etc/httpd <<<<
/home/allwebfiles/perl/LocalModules
/home/allwebfiles/perl/LocalModules/
/usr/lib64/perl5
/usr/lib64/perl5/vendor_perl
/usr/lib64/perl5/vendor_perl/Bundle <<<<
/usr/local/lib64/perl5
/usr/local/share/perl5
/usr/share/perl5
/usr/share/perl5/vendor_perl
Adding those two paths to @INC (via 'use lib’ statements) doesn’t break the
script in CGI mode.
Very strange, because I cannot think of any reason that running it under
mod_perl would cause a problem with DBI, it doesn’t happen with any other
systems I’m running it on. I”m going to take this off to the mod_perl list, I
guess.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: frustrating error DBD::mysql CentOS8
To clarify the DBD::mysql module is present, is reachable via @INC and has a
‘driver’ method present.
On Jun 2, 2020, at 10:55 AM, Bruce Johnson
mailto:john...@pharmacy.arizona.edu>> wrote:
I’ve copied a working script from one server to another (upgrading a web
application server from Cnetos6 to centos8) and I keep getting the error:
[Tue Jun 02 10:29:32.289698 2020] [:error] [pid 3729:tid 140210163652352]
DBD::mysql initialisation failed: Can't locate object method "driver" via
package "DBD::mysql" at /usr/lib64/perl5/vendor_perl/DBI.pm line
827.\n\nPerhaps the capitalisation of DBD 'mysql' isn't right. at
/home/allwebfiles/perl/badges/choose.pl line 54.\n
Mysql (actually MariaDB) is installed and working. both DBI and DBD::MySQL were
installed via the CentOS repositories. (perl-DBI perl-DBD-MySQL packages)
DBI itself is functional since other DB methods are working Oracle, for example.
This is the (working on the other server!) code to establish the database
handle:
my $dsn = "DBI:mysql:database=$dbname;host=$dbhost;port=$dbport" or die
$DBI::errstr;
my $dbh = DBI->connect($dsn, $dbuser, $dbpass,{'RaiseError' => 1});
Pretty standard stuff. I'm not sure why this is happening. It is NOT the error
being reported...
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
frustrating error DBD::mysql CentOS8
I’ve copied a working script from one server to another (upgrading a web
application server from Cnetos6 to centos8) and I keep getting the error:
[Tue Jun 02 10:29:32.289698 2020] [:error] [pid 3729:tid 140210163652352]
DBD::mysql initialisation failed: Can't locate object method "driver" via
package "DBD::mysql" at /usr/lib64/perl5/vendor_perl/DBI.pm line
827.\n\nPerhaps the capitalisation of DBD 'mysql' isn't right. at
/home/allwebfiles/perl/badges/choose.pl line 54.\n
Mysql (actually MariaDB) is installed and working. both DBI and DBD::MySQL were
installed via the CentOS repositories. (perl-DBI perl-DBD-MySQL packages)
DBI itself is functional since other DB methods are working Oracle, for example.
This is the (working on the other server!) code to establish the database
handle:
my $dsn = "DBI:mysql:database=$dbname;host=$dbhost;port=$dbport" or die
$DBI::errstr;
my $dbh = DBI->connect($dsn, $dbuser, $dbpass,{'RaiseError' => 1});
Pretty standard stuff. I'm not sure why this is happening. It is NOT the error
being reported...
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: DBD::Oracle install issue on CentOS 8 missing libnsl
> On Feb 18, 2020, at 9:58 AM, Bruce Johnson > wrote: > > Just a heads up for those of you on the close-to-the-bleeding-edge. libnsl is > no longer included with the standard glibc libs with CentOS (and I'm guessing > also in RHEL 8, and whatever the current Fedora build is) , and the > DBD::Oracle install fails during make test: > > Can't load > '/root/.cpan/build/DBD-Oracle-1.80-1/blib/arch/auto/DBD/Oracle/Oracle.so' for > module DBD::Oracle: libnsl.so.1: cannot open shared object file: No such file > or directory at /usr/lib64/perl5/DynaLoader.pm line 193. > at -e line 1. > Compilation failed in require at -e line 1. > > fortunately it’s still in the repo, so yum install libnsl fixes it. And because I have the memory of a crack-addled goldfish sometimes, I came back here to mention this the next time I ran into this problem :-) Since libnsl.so.2 IS installed if you you do the Dev tools group install (so you can compile DBD::Oracle in the first place), simply symlinking it to libnsl.so.1 also works. " ln -s /usr/lib64/libnsl.so.2 /usr/lib64/libnsl.so.1” Although to be fair Feb 18 does feel like it was a year or two ago… -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
DBD::Oracle install issue on CentOS 8 missing libnsl
Just a heads up for those of you on the close-to-the-bleeding-edge. libnsl is no longer included with the standard glibc libs with CentOS (and I'm guessing also in RHEL 8, and whatever the current Fedora build is) , and the DBD::Oracle install fails during make test: Can't load '/root/.cpan/build/DBD-Oracle-1.80-1/blib/arch/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libnsl.so.1: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 193. at -e line 1. Compilation failed in require at -e line 1. fortunately it’s still in the repo, so yum install libnsl fixes it. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Perl script excessively executing statement
On Feb 13, 2020, at 8:30 AM, Fennell, Brian mailto:fenne...@radial.com>> wrote: Try to figure out how you can do the whole thing in a single SQL statement, then just process the results one row at time. Well, based on his example code that’s exactly what he was doing. The only loop was processing the returned rows via $arraySelect->fetchrow_array() I am honestly with Geoffrey on this, I suspect something was broken on the DB itself and it was only coincidental that John’s script was running at the time the db became unresponsive. 1) this was a change in behavior without a change in code; the script had been running just fie for a long time. 2) barring the query returning enormous numbers of rows with a hugely inefficient query involving multiple remote database, etc , even a fairly minimal Oracle install can manage 4 queries per second (12K queries in 50 minutes) without becoming unresponsive, even if they do return tens or hundreds of thousands of rows. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Weird issues using DBI + mod_perl
Just a follow up…it was embarrassingly stupid. You know how you can stare at an error in a line dozens of times and not see it? Like the ‘#' commenting out "use Apache::DBI” in your startup.pl file ... On Aug 16, 2019, at 2:47 PM, Andreas Mock mailto:andreas.m...@web.de>> wrote: Hi Bruce, I'm just guessing, but this sounds much like reusing or double using of Oracle db handles, context handles or statement handles. You have to have a look at the bookkeeping of the handles per request. Also forking of the childs may byte you. You must ensure that db handles are ONLY opened in the child process doing the work and get closed properly. With DBI in use you can use the variable DBI_TRACE to get extensive log. Google for that in combination with mod_perl. Just some hints. Best regards Andreas -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Weird issues using DBI + mod_perl
So I’ve built a site using mod_perl in CGI mode, with DBI and Oracle as my database back end. Everything works fine for a while but then I get intermittent weird errors that start happening for every page load, which is resulting in non-functional pages and error messages “Something happened contact your system administrator” "DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual”]” at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line 522.\n There are no bound variables in the query... Another one: [error] DBD::Oracle::st execute failed: ORA-01007: variable not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual"] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line 522.\n Other errors that happen are : DBD::Oracle::st execute failed: ORA-01007: variable not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist, bldg_id, start_task from login_info where cookie =?" with ParamValues: :p1='NS9DdciuH7XPeSVygRyUjoviZ’] DBD::Oracle::db prepare failed: ORA-01002: fetch out of sequence (DBD ERROR: OCIStmtExecute/Describe) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual"] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 266, line 522.\n DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist, bldg_id, start_task from login_info where cookie =?" with ParamValues: :p1='v2bpY8jnoPw5yf0x71I4wZPx6'] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 307 Most of these errors (that cause the DBI stuff to break) are happening in this defaults module, which IS referenced more than once per page load as the pages have ajax calls to other scripts that reference this module. restarting httpd fixes the problem, for a while. This isn’t a very heavily loaded site, right now it’s the middle of the afternoon and I'm seeing 'accesses per minute' kind of traffic. And clues as to where I should start to look? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: install DBD-mysql to macOS 10.13.x High Sierra
I used MacPorts, and I replaced my whole stack: perl, MySQL and DBI with the MacPorts versions. https://www.macports.org/ It’s about as close as I’ve seen to installing stuff on a mac as yum is on Linux boxes, super-simple: sudo port install . It puts everything off in /opt so the bane of Apple updates blowing away all your stuff is eliminated. brew tries to keep everything inside the Apple library structure. The .dmg installs of MySQL from Oracle have never worked very well for me, which is why I ended up using MacPorts. The Apple perl is still there for the OS stuff that uses it, but all of the perl modules I’ve installed from cpan are in the macports directory tree. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs On 1/15/19, 2:59 PM, "Rajeev Jain" mailto:jainr...@gmail.com>> wrote: Anyone had success install DBD-mysql on macOS 10.13.x High Sierra? I've tried using home-brew, cpan paths but always encounter various errors. I'm now manually installing and getting an error during the final link: $>DBD-mysql-4.050$ make Skip blib/lib/DBD/mysql/INSTALL.pod (unchanged) Skip blib/lib/DBD/mysql.pm (unchanged) Skip blib/lib/Bundle/DBD/mysql.pm (unchanged) Skip blib/lib/DBD/mysql/GetInfo.pm (unchanged) Running Mkbootstrap for mysql () chmod 644 "mysql.bs" "/usr/bin/perl" -MExtUtils::Command::MM -e 'cp_nonempty' -- mysql.bs blib/arch/auto/DBD/mysql/mysql.bs 644 cc -c -I/System/Library/Perl/Extras/5.18/darwin-thread-multi-2level/auto/DBI -I/usr/local/mysql/include -DDBD_MYSQL_WITH_SSL -g -arch x86_64 -arch i386 -g -pipe -fno-common -DPERL_DARWIN -fno-strict-aliasing -fstack-protector -Os -DVERSION="4.050" -DXS_VERSION="4.050" "-I/System/Library/Perl/5.18/darwin-thread-multi-2level/CORE" dbdimp.c cc -c -I/System/Library/Perl/Extras/5.18/darwin-thread-multi-2level/auto/DBI -I/usr/local/mysql/include -DDBD_MYSQL_WITH_SSL -g -arch x86_64 -arch i386 -g -pipe -fno-common -DPERL_DARWIN -fno-strict-aliasing -fstack-protector -Os -DVERSION="4.050" -DXS_VERSION="4.050" "-I/System/Library/Perl/5.18/darwin-thread-multi-2level/CORE" mysql.c cc -c -I/System/Library/Perl/Extras/5.18/darwin-thread-multi-2level/auto/DBI -I/usr/local/mysql/include -DDBD_MYSQL_WITH_SSL -g -arch x86_64 -arch i386 -g -pipe -fno-common -DPERL_DARWIN -fno-strict-aliasing -fstack-protector -Os -DVERSION="4.050" -DXS_VERSION="4.050" "-I/System/Library/Perl/5.18/darwin-thread-multi-2level/CORE" socket.c rm -f blib/arch/auto/DBD/mysql/mysql.bundle LD_RUN_PATH="/usr/local/mysql/lib" cc -arch x86_64 -arch i386 -bundle -undefined dynamic_lookup -fstack-protector dbdimp.o mysql.o socket.o -o blib/arch/auto/DBD/mysql/mysql.bundle -L/usr/local/mysql/lib -lmysqlclient -lssl -lcrypto \ ld: warning: The i386 architecture is deprecated for macOS (remove from the Xcode build setting: ARCHS) ld: warning: ignoring file /usr/local/mysql/lib/libmysqlclient.dylib, file was built for x86_64 which is not the architecture being linked (i386): /usr/local/mysql/lib/libmysqlclient.dylibld: warning: ignoring file /usr/local/mysql/lib/libssl.dylib, file was built for x86_64 which is not the architecture being linked (i386): /usr/local/mysql/lib/libssl.dylib ld: warning: ignoring file /usr/local/mysql/lib/libcrypto.dylib, file was built for x86_64 which is not the architecture being linked (i386): /usr/local/mysql/lib/libcrypto.dylib chmod 755 blib/arch/auto/DBD/mysql/mysql.bundle Manifying 3 pod documents My high sierra was upgraded from a older OS version so my thinking is the i386 flag is left over from the prior install. Additionally mysql and mysqlclient was installed using a dmg package (not home brew). How can the -arch i386 flag be removed? Any help or guidance will be appreciated. TIA
Re: install DBD-mysql to macOS 10.13.x High Sierra
I’ve always just used the Bundle package and not had any problem: https://metacpan.org/pod/Bundle::DBD::mysql -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs On 1/15/19, 3:34 PM, "Daniël van Eeden" mailto:daniel.van.ee...@myname.nl>> wrote: Hi, On 2019-01-15 22:58, Rajeev Jain wrote: Anyone had success install DBD-mysql on macOS 10.13.x High Sierra? I'll look into this. Not sure what the issue is, but first I need to find a way to test on macOS as I don't have a macOS machine at the moment.
Re: (Fwd) Perl with Oracle 12c
On Apr 10, 2018, at 8:02 AM, tim.bu...@pobox.com<mailto:tim.bu...@pobox.com> wrote: - Forwarded message from "Seidler, Reinhard" <reinhard.seid...@amtc-dresden.com<mailto:reinhard.seid...@amtc-dresden.com>> - Date: Tue, 10 Apr 2018 12:16:09 + From: "Seidler, Reinhard" <reinhard.seid...@amtc-dresden.com<mailto:reinhard.seid...@amtc-dresden.com>> To: "tim.bu...@pobox.com<mailto:tim.bu...@pobox.com>" <tim.bu...@pobox.com<mailto:tim.bu...@pobox.com>> Subject: Perl with Oracle 12c Hi Tim, We develop Perl applications connecting and working with Oracle database (DBD:Oracle). So far it was Oracle version 11. Now the database will be migrated to 12.2.0.1. How should we deal with that? Any answer would be appreciated. Here is the canonical client compatibility guide for Oracle Database. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
I probably mentioned this the last time I installed DBD:Oracle but I couldn't find it...
Oracle’s latest instant client for 64-bit linux puts the demo.mk file in /usr/share/oracle/12.2/client64/demo The DBD::Oracle installer only looks in /usr/share/oracle/12.2/client64/ to find it and thus installing from cpan fails. Dunno if this is really a reportable bug or not. I just put a symlink to it in the expected directory and everything worked just fine. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Perl DBI libraries for connecting 12c
> On Oct 9, 2017, at 3:52 PM, John R Pierce <pie...@hogranch.com> wrote: > > On 10/9/2017 2:49 PM, Furst, Carl wrote: >> I believe you have to go to Oracle to get those.. You can get DBI and even >> DBB::Oracle but it won’t build. You won’t have the Oracle C libs to link off >> of. You’ll need to install an oracle client library. > > > as I recall, to use CPAN and build modules that work with the Solaris Perl, > you have to have the Sun/Oracle Studio C compiler (formerly called Forte), > not GCC, unless you leap through some hoops. > > This used to work, but I've not been down these bunny trails for years and > years : http://search.cpan.org/~aburlison/Solaris-PerlGcc-1.3/pod/perlgcc.pod > ... you also need to make sure you installed the full oracle developer stuff > as part of the client. With Instant Client you have to install the Basic and the SDK packages. The latter gets the developer header files and libs that DBD::Oracle uses to compile the c code. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: (Fwd) Oracle.pm
On Mar 6, 2017, at 5:59 PM, Gowtham
<shiningstargau...@gmail.com<mailto:shiningstargau...@gmail.com>> wrote:
Hi Ron,
I did that.
I have two scenarios here. Both the scenarios have same
($dbh,$dbname,$user,$auth,$attr) values.
Scenario 1: executed code with ORACLE_HOME set to correct value.
Scenario 2: executed code with incorrect/wrong ORACLE_HOME env variable.
- So my code returns undef in the second scenario. I'm trying to understand the
execution part inside the subroutine _login to establish the dependency of
ORACLE_HOME on DBI connection.
$ORACLE_HOME is critical to any API involving oracle; this is not a function of
_login or exclusive to DBI. If it’s set incorrectly or undef, you will always
get a failure like this.
Without more details on your particular application, it’s difficult to
determine why your script is failing, but most common is that the perl module
is executed via some sort of process spawning a new shell and the environment
in that shell is not properly set.
What you need to ensure that the environment variable $ORACLE_HOME is set in
the process that is failing; either explicitly in the perl script via
$ENV{‘ORACLE_HOME’}= 'path to oracle_home’; or by ensuring that the defaults
for the process include that variable.
In Apache you can set it via a SetEnv statement:
SetEnv ORACLE_HOME /path/to/oracle/home
Be aware that more than just ORACLE_HOME is needed, you also need to set
LD_LIBRARY_PATH at a minimum, and perhaps TNS_ADMIN , ORACLE_BASE and
ORACLE_SID.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: (Fwd) Issues with Oracle DBD in Cygwin
> On Sep 17, 2016, at 7:41 AM, Tim Bunce <tim.bu...@pobox.com> wrote: > > > Couple of things I have noticed that seem off to me and might be an issue??? > > 1. after unpacking the instant client files into > c:\oracle\instantclient_12_1 (set as ORACLE_HOME) > I do NOT have a "lib" or "rdbms/lib" subdirectory - am I missing something? > I have been using > LD_LIBRARY_PATH=$ORACLE_HOME/lib and I see it and the rdbms/lib referenced > in LD_RUN_PATH but those > directories do not exist. If it were a problem I sure would expect some > sort of error message but > nothing jumps out at me??? It’s been a while but IIRC the Windows Instant Client just dumps everything into a single directory (at least as of the last version I installed, which would be 11.2) try setting LD_LIBRARY_PATH and any other needed environment vars to just $ORACLE_HOME. The first step before trying to get DBI working is getting SQLPlus to work. Bet the LD_LIBRARY_PATH fix above fixes that as well. Don’t forget you also need to get the additional *Instant Client Package - SDK", since that has the header files needed to compile DBD::Oracle. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Bundle::DBI and DBD::Oracle failing
> On Jun 22, 2016, at 1:26 PM, Tony D'Alfonso <tony.dalfo...@smi-ieso.ca> wrote: > > SDK was installed too: > > # yum list oracle-instantclient* > Loaded plugins: product-id, search-disabled-repos, subscription-manager > Installed Packages > oracle-instantclient-basic.x86_64 > 10.2.0.3-1 installed > oracle-instantclient-devel.x86_64 > 10.2.0.3-1 installed > oracle-instantclient-sqlplus.x86_64 > 10.2.0.3-1 installed > You know it’s vaguely coming back to me that I had to install several _devel packages as well as the lib packages when I last set up a linux server. You may need the libstdc++-devel.x86_64 package... -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Bundle::DBI and DBD::Oracle failing
On Jun 22, 2016, at 1:34 PM, John R Pierce <pie...@hogranch.com<mailto:pie...@hogranch.com>> wrote: if root installed it to /root, no other user can see or access that. running cpan as root is fine, since that doesn’t affect the permissions of the final perl executables. The only thing it affects is keeping track of what has been installed via cpan, since the .cpan metadata lives in root’s home directory. Heck in OS X, I have to run cpan via sudo or I can’t install anything. and no way no how you should be doing Oracle database work while logged on as the unix root user. Oracle won’t even let you install it as root as of 11.2 (at least, probably much earlier but I went from 8.1.2 to 11.2 in one swell foop) . -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Bundle::DBI and DBD::Oracle failing
> On Jun 22, 2016, at 12:32 PM, Tony D'Alfonso <tony.dalfo...@smi-ieso.ca> > wrote: > > Hi Carl, > > I used CPAN to install them and it appeared as though they are in place: > > # cpan -i Bundle::DBI > Reading '/root/.cpan/Metadata' > Database was generated on Wed, 22 Jun 2016 13:29:02 GMT > DBI is up to date (1.636). > DBI::Shell is up to date (11.95). > Storable is up to date (2.51). > Net::Daemon is up to date (0.48). > RPC::PlServer is up to date (0.2020). > DBD::Multiplex is up to date (2.11). > [root@screendoor ~]# cpan -i DBD::Oracle > Reading '/root/.cpan/Metadata' > Database was generated on Wed, 22 Jun 2016 13:29:02 GMT > DBD::Oracle is up to date (1.74). > > I'm using the Oracle 10 client. Defined the environment variables as: > export ORACLE_HOME=/usr/lib/oracle/10.2.0.3/client64 > export LD_LIBRARY_PATH=$ORACLE_HOME/lib > export LD_RUN_PATH=$ORACLE_HOME/lib > export PATH=$PATH:$ORACLE_HOME/bin I know I’ve gotten this error with Instant Client when the SDK component wasn’t installed. I haven’t used the full client in years so I don’t know if the SDK is automatically installed or not. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: help to connect
> On May 12, 2016, at 4:12 AM, Jefferson Elias <jefferson.el...@chu.ulg.ac.be>
> wrote:
>
> Hi,
>
> I've been trying many differents things with the following error, but nothing
> seems to be working.
>
>
> Let me start to explain my problem from the beginning.
>
> (Perl version: v5.10.)
>
> I use a configuration file that I parse using Config::General::ParseConfig.
> This file is encoded in UTF8 as shown below:
>
> $ file config/application.conf
> config/application.conf: UTF-8 Unicode English text
>
>
> In this configuration file, there is a field called
> 'target_db_list_default_password' which contains a default password to be
> used for my monitoring.
> This password is then used in conjunction with DBI->connect() method
> primarily to contact an Oracle Database instance.
>
> I always get the following message at execution:
>
> Perl Error message: DBI connect('MY_DB','halfonz',...) failed:
> ORA-01017: invalid username/password;
> logon denied (DBD ERROR: OCISessionBegin) at
> /home/jeff.elias/Projects/DBA_SOURCES/branches/develop-next/Templates/Projet
> Perl simple/src/libs/Common/DbConnection.pm line 210
>
>
> The password cannot be changed that easily. I've printed out the password I
> get back and copy-pasted it into a SQL Developer new connection dialog then
> tried to connect and it worked.
> So, my conclusion is that the password can be considered as the OK.
Make sure the Oracle environment variables, particularly the SID are the same
for both SQL Developer and in your perl application; perhaps the config file is
pointing to the wrong DB? (a development versus production thing? )
remotely it MIGHT be default NLS_LANG thing, but I don’t think so.
A quick test would be to connect to the DB with hardcoded credentials in a test
perl script? This will determine whether it’s perl/DBI or encoding issues from
the config file.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
DBD::Oracle and RHEL Instant Client rpms
Has anyone else had trouble installing DBD::Oracle with the latest 12.1 instant client? I installed the Basic and Development IC rpm’s and the DBD installer complained it couldn’t determine what version I had, then complained it couldn’t find demo.mk. I finally got it to work by manually telling it what version and the path to the .mk file (which hadn’t changed from previous versions: /usr/share/oracle//client64…) The various env values were set correctly, and the files were installed in the usual place, the only difference between this time and the last time I set up a RHEL system was I used the latest 12.1 instant client files rather than 11.2. [root@kalendaetest DBD-Oracle-1.74-EIJTlU]# set |grep ora LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib ORACLE_HOME=/usr/lib/oracle/12.1/client64 PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/lib/oracle/12.1/client64/bin TNS_ADMIN=/usr/lib/oracle/12.1/client64/admin It worked, eventually, just wondering if others have run into the issue... -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: DBD::Oracle and RHEL Instant Client rpms
> On Oct 20, 2015, at 2:22 PM, Martin J. Evans <boh...@ntlworld.com> wrote: > > > I don't use an RPM based system. I download instantclient basic, devel and > sqlplus zips and unzip them then point DBD::Oracle at them by setting > LD_LIBRARY_PATH. > > Did you download the sqlplus RPM (assuming there is one) as DBD::Oracle uses > sqlplus during the install to work out what version of the Oracle client you > have? No I didn’t, since I don’t need it on this system, and figured I’d save some time/disk space. Probably a bad idea on my part. The server is working fine though, I was just a bit puzzled. I”ll have to go look back and see if I did install SQLPlus on the other systems. The problem is I set up a new server just once in a blue moon, not all the time, and forget all the bits and bobs I need. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Oracle character column sizes from {PRECISION}
> On Sep 8, 2015, at 3:42 PM, Steve Baldwin <stbald...@multiservice.com> wrote: > > I have a table defined as follows: > > SQL> desc sb1 > Name Null?Type > - > C1 VARCHAR2(30 CHAR) > C2 VARCHAR2(30) > > Note the difference is the character semantics, c1 is CHAR, c2 is BYTE. > isn’t that dependent on what ns_lang is set to? for single-byte encoding this is correct, as 30 char == 30 bytes. <http://www.dba-oracle.com/t_nls_lang.htm> -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: (Fwd) DBI Dilemma
DBD::CSV, or at least the current version 0.48 http://search.cpan.org/~hmbrand/DBD-CSV-0.48/lib/DBD/CSV.pm uses SQL::Statement for it’s syntax . See http://search.cpan.org/~rehsack/SQL-Statement-1.407/lib/SQL/Statement/Syntax.pod#Identifiers_(table__column_names) This might help you get started. Another, cruder, solution might be to simply change the column header “DESC” to something acceptable to SQL in the csv file before parsing it with your sql statement, then changing it back before exporting it. And this is quite old, but relevant to your question http://www.perlmonks.org/?node_id=673399 indicating that this line of research is on the right track, I think. On 2015-08-18 7:21 AM, Adkins, Blake wrote: Let me do a better job of explaining the situation. We use GE's Cimplicity to monitor and control a SCADA system. GE provides an interface to the database but it's very limited. The easiest way is to export to a .csv file and operate on that and then import changes. My script works on the .csv file using dbi:CSV:f_dir and pointing to different files for different projects. The problem comes when I try to include DESC in SELECT or WHERE. I have tried 'DESC', DESC and `DESC`. The last two fail at run-time. The first one doesn't fail but it returns something like PT_ID,DESC PT_1,DESC PT_2,DESC ... PT_N,DESC. I'm using the DBI module in Perl, would this be better with the other modules mentioned? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: (Fwd) DBI Dilemma
On Aug 17, 2015, at 2:42 PM, tim.bu...@pobox.com wrote: - Forwarded message from Adkins, Blake blake.adk...@intel.com - Date: Mon, 17 Aug 2015 17:51:41 + From: Adkins, Blake blake.adk...@intel.com To: tim.bu...@pobox.com tim.bu...@pobox.com Subject: DBI Dilemma Tim, I've been using your module to enable people in my group to do searches on a database that is regularly backed up as a .csv file. The problem here is with a particular column name. Of the 140 columns in the database, one is named DESC, short for description. This was established well before my time at the company and I believe the name comes from GE who makes the Cimplicity product. If I try to do a SELECT using that column, the script dies, or quietly passes DESC in the column header and all the rows. I've tried to figure out how to get around it without success. Do you have any suggestions aside from renaming the column? (I was thinking along the lines of escaping the name) How you do this depends on the database, but yeah escaping the name is what I’d investigate first. The table had to be created somehow, and if the DB works, it’s valid; can you query it in the database itself using built in tools? (EG: sqlplus or mysql or psql, for example) The issue may well not be DBI, but the particular DBD module in use. I know for a fact that I can make a column named ‘DESC’ in oracle and it just works. But Oracle’s got a long history of letting you get away with murder in naming things... -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: DBD:Pg problems
On Jul 16, 2015, at 9:59 AM, COULLEIT Guy guy.coull...@swift.com wrote: Hi, Any idea what is going wrong here again? [taranis@betrnt01 tmp]$ cd DBD-Pg-3.5.1 [taranis@betrnt01 DBD-Pg-3.5.1]$ perl Makefile.PL Configuring DBD::Pg 3.5.1 PostgreSQL version: 80420 (default port: 5432) POSTGRES_HOME: (not set) POSTGRES_INCLUDE: /usr/include POSTGRES_LIB: /usr/lib64 OS: linux Could not load DBI::DBD - is the DBI module installed? [taranis@betrnt01 DBD-Pg-3.5.1]$ exit Exit [root@betrnt01 DBI-1.633]# cpan Loading internal null logger. Install Log::Log4perl for logging messages Terminal does not support AddHistory. cpan shell -- CPAN exploration and modules installation (v2.11) Enter 'h' for help. cpan[1] install DBI::DBD Reading '/root/.cpan/Metadata’ You installed DBI as root, but are trying to install DBD::Pg as taranis. Path or other environment issues? Any reason you’re not installing DBD::Pg via cpan (as root) as well? I’ve found that minimizes these kinds of things... -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: help with odd DBI perpare/execute errors
On Jun 3, 2015, at 6:57 AM, William Bulley w...@umich.edu wrote: Yep, I've been all over the net looking for this issue. I am not doing anything wrong -- the invalid string is the darn ?!!! Make sure your original $query is delimited by double quotes, not single. if you do $sth-prepare(‘select column from table where column = ?’); you’ll get that error. That’s the only way the ? would get past DBI, I’d think, which is what your oracle error seems to be indicating. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: help with odd DBI perpare/execute errors
On Jun 3, 2015, at 7:19 AM, William Bulley w...@umich.edu wrote:
According to Bruce Johnson john...@pharmacy.arizona.edu on Wed, 06/03/15
at 10:10:
Make sure your original $query is delimited by double quotes, not single.
I've tried _everything_!!
Single quotes. Double quotes. q{} and qq{} (using the latter now).
But no matter what I try DBI complains about the darn question mark!
It is infuriating, I tell you! :-)
Well, I just tested MY theory (RHEL v6.5, oracle 11.2g, oracle instant client
for 11.2, perl, v5.10.1 (*) built for x86_64-linux-thread-multi) with:
#!/usr/bin/perl
use strict;
use DBI;
my $dbh= DBI-connect(“dbi:Oracle:host=$host, $user, $pass, {RaiseError =1});
my $qry1 ='select ? from dual';
my $qry2 = select ? from dual;
my $sth = $dbh-prepare($qry1);
$sth-execute('foo');
my ($res)=$sth-fetchrow();
print single quote result is $res \n;
$sth =$dbh-prepare($qry2);
$sth-execute('bar');
($res)=$sth-fetchrow();
print double quote result is $res \n;
exit;
And got:
# ./qmarktest.pl
single quote result is foo
double quote result is bar
So yet another fine theory destroyed by reality…
Possibly some sort of character set mess up? Could your 'question mark' be
something else in the script? Maybe a 16-bit vs 8-bit character? I’ve had some
weird issues in the past when I was handed a 16-bit unicode text file of insert
statements and tried to run them.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: help with odd DBI perpare/execute errors
On Jun 3, 2015, at 9:44 AM, William Bulley w...@umich.edu wrote:
Martin thinks the parsing in the dbd_preparse() function within the
dbdimp.c file (part of DBD::Oracle) has issues so that it cannot deal
with the second question mark given the preceding single quote(s).
It seems plausible, yet odd, to me, but it isn't my module. Perhaps
I have an older version? I dunno... :-(
I have numerous constructions like yours in my scripts and they work just fine
(and have for a very long time, I’m pretty sure prior to 2006 which is when
1.19 was released, so I don’t believe it’s your DBD::Oracle version )
Double check those single quotes, though, I’ve seen editors that try to sneak
in ‘smart quotes’ and those’ll bollix you every time.
Here’s a quicky test:
#!/usr/bin/perl
use strict;
foreach my $i (qw (? ' “ ” ‘ ’ )){
print $i is ascii .(ord $i).\n
}
exit;
This is what it produces:
dbdev2:~ johnson$ perl test
? is ascii 63
' is ascii 39
is ascii 34
? is ascii 210
? is ascii 211
? is ascii 212
? is ascii 213
Note the displayed ‘?’’s….this is in my standard OSX terminal, which is a
VT-100 emulator using UTF-8 as the text encoding.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: First time DBI user crashing perl
On Oct 30, 2014, at 2:44 PM, Furst, Carl carl.fu...@mlb.com wrote: Is the driver you are using the one you used to install the DBD libs with? If you installed the DBD libs with one driver and then, say, upgraded the driver, you would have to re-install the DBD libs. Also, I don’t know about DB2, and less about Strawberry perl :-/ but I do know a very common issue with is bitness mismatch in the database vendor drivers; 32-bit database driver vs 64-bit perl and vice versa, although there are usually install time errors that will signal this. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: I have a weird issue with a script running under cron
On Feb 20, 2014, at 4:56 AM, Peter J. Holzer h...@wsr.ac.at wrote:
On 2014-02-19 11:14:30 -0800, Bill Ward wrote:
Remember that use is a kind of BEGIN block. You have to set the ENV
variables in a BEGIN block before the use line.
Also, on several Unix-like systems (including Solaris and Linux/x86_64),
changing LD_LIBRARY_PATH while a process is running has no effect. So
this:
On Feb 19, 2014 9:23 AM, Bruce Johnson john...@pharmacy.arizona.edu
wrote:
$ENV{ORACLE_HOME}=/usr/lib/oracle/11.2/client64;
$ENV{ORACLE_SID}=phmweb;
$ENV{LD_LIBRARY_PATH}=/usr/lib/oracle/11.2/client64/lib;
use DBI;
[...]
my $dbh = DBI-connect( dbi:Oracle:host=$dbhost, $login, $dbpass,
{ RaiseError = 1 } );
doesn't work even with a BEGIN {} block around the assignments to %ENV.
You have to set LD_LIBRARY_PATH in a wrapper script.
And I just moved this script from a Linux i686 to a Linux X86_64 system
when the trouble started.
But, soft! what light through yonder hard skull finally breaks!”
Thank you all for your patient assistance.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: I have a weird issue with a script running under cron
Well, I’ve identified my solution, but I’m not entirely certain why it’s
working.
Setting the environment variables in a shell script wrapper around my perl
script works. Despite %ENV being identical, when set within the perl script, I
get the error, when set in the .sh wrapper it works.
It appears to be that called modules via ‘use MODULE;’ do NOT share the
environment of the calling perl script?
These are my test scripts:
test.sh script
#!/bin/sh
ORACLE_HOME=/usr/lib/oracle/11.2/client64
LD_LIBRARY_PATH=$ORACLE_HOME/lib
ORACLE_SID=PHMWEB
export ORACLE_HOME LD_LIBRARY_PATH ORACLE_SID
echo testing connection
/home/allwebfiles/perl/kfs/test.pl
echo Done testing connection
test.pl script
#!/usr/bin/perl -w
$ENV{ORACLE_HOME}=/usr/lib/oracle/11.2/client64;
$ENV{ORACLE_SID}=phmweb;
$ENV{LD_LIBRARY_PATH}=/usr/lib/oracle/11.2/client64/lib;
use DBI;
my $login = 'scott';
my $dbpass = 'tiger';
my $dbhost='pharmacopeia.pharmacy.arizona.edu;sid=phmweb’ substitute your own
host here
print Environment:\n;
foreach my $e (keys %ENV){
print env var $e = $ENV{$e} \n;
}
print \n;
printDB test: sysdate = ;
my $dbh = DBI-connect( dbi:Oracle:host=$dbhost, $login, $dbpass,
{ RaiseError = 1 } );
my $csr= $dbh-prepare(select sysdate from dual);
$csr-execute();
my ($out) = $csr-fetchrow();
print $out\n;
$csr-finish();
$dbh-disconnect;
I tested this first by commenting out the $ENV{}=‘’; lines in the perl script,
leaving the ones in test.sh intact:
[root@merthiolate ~]# env -i /home/allwebfiles/perl/kfs/test.sh
testing connection
Environment:
env var ORACLE_HOME = /usr/lib/oracle/11.2/client64
env var SHLVL = 1
env var LD_LIBRARY_PATH = /usr/lib/oracle/11.2/client64/lib
env var PWD = /root
env var _ = /home/allwebfiles/perl/kfs/test.pl
env var ORACLE_SID = PHMWEB
DB test: sysdate = 19-FEB-14
Done testing connection
Then I commented out the first four lines of test.sh and uncommented the lines
in test.pl
env -i /home/allwebfiles/perl/kfs/lddtest.sh
testing connection
Environment:
env var ORACLE_HOME = /usr/lib/oracle/11.2/client64
env var LD_LIBRARY_PATH = /usr/lib/oracle/11.2/client64/lib
env var SHLVL = 1
env var PWD = /root
env var _ = /home/allwebfiles/perl/kfs/test.pl
env var ORACLE_SID = phmweb
install_driver(Oracle) failed: Can't load
'/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle:
libocci.so.11.1: cannot open shared object file: No such file or directory at
/usr/lib64/perl5/DynaLoader.pm line 200.
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 /home/allwebfiles/perl/kfs/test.pl line 18
DB test: sysdate = Done testing connection
It appears that DBI is NOT inheriting the environment when it’s called via ‘use
DBI’ even though it’s called after I explicitly set them within the perl script.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: I have a weird issue with a script running under cron
On Feb 18, 2014, at 4:02 AM, Peter J. Holzer h...@wsr.ac.at wrote: On 2014-02-17 22:43:58 +, Bruce Johnson wrote: On Feb 17, 2014, at 2:48 PM, John D Groenveld jdg...@elvis.arl.psu.edu wrote: The OP shouldn't need to set a LD_LIBRARY_PATH so long as he built DBD::Oracle with the correct runtime link path, but a simple shell script to see which libraries aren't resolving would be a useful test: #!/bin/ksh /bin/env - /usr/bin/ldd /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so Everything works on the command line interactively. The error only happens when the script is run via cron; Yes, so obviously you should run John's little script via cron, too. What is the result? interactively: ldd /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so linux-vdso.so.1 = (0x7fffc2dd4000) libocci.so.11.1 = /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1 (0x7fe71e965000) libclntsh.so.11.1 = /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x7fe71c0d3000) libpthread.so.0 = /lib64/libpthread.so.0 (0x7fe71beab000) libc.so.6 = /lib64/libc.so.6 (0x7fe71bb18000) libstdc++.so.6 = /usr/lib64/libstdc++.so.6 (0x7fe71b811000) libm.so.6 = /lib64/libm.so.6 (0x7fe71b58d000) libgcc_s.so.1 = /lib64/libgcc_s.so.1 (0x7fe71b377000) libnnz11.so = /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x7fe71afaa000) libdl.so.2 = /lib64/libdl.so.2 (0x7fe71ada6000) libnsl.so.1 = /lib64/libnsl.so.1 (0x7fe71ab8d000) libaio.so.1 = /lib64/libaio.so.1 (0x7fe71a98b000) /lib64/ld-linux-x86-64.so.2 (0x7fe71edfd000) Via cron (with the Oracle environment variables set as in the script in question, please read the OP!): SHELL=/bin/sh USER=root LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_SID=PHMWEB PATH=/usr/bin:/bin PWD=/root HOME=/root SHLVL=2 LOGNAME=root ORACLE_HOME=/usr/lib/oracle/11.2/client64 linux-vdso.so.1 = (0x7fff33dff000) libocci.so.11.1 = /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1 (0x 7ff92b1e) libclntsh.so.11.1 = /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x7ff92894e000) libpthread.so.0 = /lib64/libpthread.so.0 (0x7ff928726000) libc.so.6 = /lib64/libc.so.6 (0x7ff928393000) libstdc++.so.6 = /usr/lib64/libstdc++.so.6 (0x7ff92808c000) libm.so.6 = /lib64/libm.so.6 (0x7ff927e08000) libgcc_s.so.1 = /lib64/libgcc_s.so.1 (0x7ff927bf2000) libnnz11.so = /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x7ff9 27825000) libdl.so.2 = /lib64/libdl.so.2 (0x7ff927621000) libnsl.so.1 = /lib64/libnsl.so.1 (0x7ff927408000) libaio.so.1 = /lib64/libaio.so.1 (0x7ff927206000) /lib64/ld-linux-x86-64.so.2 (0x7ff92b678000) No difference. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: I have a weird issue with a script running under cron
On Feb 18, 2014, at 10:47 AM, John D Groenveld jdg...@elvis.arl.psu.edu wrote:
In message 2076ef99-9f11-4eda-846a-f0a946e85...@pharmacy.arizona.edu, Bruce
J
ohnson writes:
Via cron (with the Oracle environment variables set as in the script in
questi
on, please read the OP!):
SHELL=/bin/sh
USER=root
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
ORACLE_SID=PHMWEB
PATH=/usr/bin:/bin
PWD=/root
HOME=/root
SHLVL=2
LOGNAME=root
ORACLE_HOME=/usr/lib/oracle/11.2/client64
linux-vdso.so.1 = (0x7fff33dff000)
libocci.so.11.1 = /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1 (0
x
7ff92b1e)
libclntsh.so.11.1 = /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.
1
(0x7ff92894e000)
/tmp/test.sh
#!/bin/ksh
export ORACLE_HOME
ORACLE_HOME=/usr/lib/oracle/11.2/client64
/tmp/dbi.pl
/tmp/dbi.pl
#!/usr/local/bin/perl -w
use DBI;
my $dbh = DBI-connect( dbi:Oracle:PHMWEB, scott, tiger,
{ RaiseError = 1 } );
$dbh-disconnect;
$ /bin/env -i /tmp/test.sh
John
groenv...@acm.org
This is functioning, since no errors came up.
One quick perl-related question, though…what environment do perl modules
imported via ‘use module;’ commands have? I would think it was the environment
of the importing program.
One of the use statements has an exported function that checks something in the
database, and there is a db connection made if the function is called. The
error line number doesn’t make sense, since the error refers to the line making
my db connection in the main script, and doesn’t correspond to an existing line
in the perl module, but I’ve run into oddball misdirections in perl error
statements in the past.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
I have a weird issue with a script running under cron
I get the following error: install_driver(Oracle) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libocci.so.11.1: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 200, DATA line 749. at (eval 10) line 3 Compilation failed in require at (eval 10) line 3, DATA line 749. Perhaps a required shared library or dll isn't installed where expected at /home/allwebfiles/perl/kfs/kfsupdate.pl line 21 The script runs just fine when run interactively in a shell. This normally means an issue with Oracle environment variables, but I wrote another script that simply lists %ENV. When run in the same crontab I get: Environment variables HOME = /root LD_LIBRARY_PATH = /usr/lib/oracle/11.2/client64/lib LOGNAME = root ORACLE_HOME = /usr/lib/oracle/11.2/client64 ORACLE_SID = phmweb PATH = /usr/bin:/bin PWD = /root SHELL = /bin/sh SHLVL = 1 USER = root _ = /home/allwebfiles/perl/kfs/showenvcron.pl These are the correct values. So what am I missing? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: I have a weird issue with a script running under cron
On Feb 17, 2014, at 2:48 PM, John D Groenveld jdg...@elvis.arl.psu.edu wrote: In message 5302803c.4080...@triad.rr.com, Richie writes: Is LD_LIBRARY_PATH exported? It's hard to see whats going on without a full test case. The OP shouldn't need to set a LD_LIBRARY_PATH so long as he built DBD::Oracle with the correct runtime link path, but a simple shell script to see which libraries aren't resolving would be a useful test: #!/bin/ksh /bin/env - /usr/bin/ldd /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so Everything works on the command line interactively. The error only happens when the script is run via cron; however the ORACLE_HOME and LD_LIBRARY_PATH variables appear to be set correctly. This is a fairly lengthy script that hits tables in use during working hours, yet depends on remote databases not available to me most of the nights, so it may take a few days to work through checking stuff since I only have about two hours a day when I can actually run it :-) -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Getting Error Message Can't load 'C:/Perl64/lib/auto/DBD/Oracle/Oracle.dll While Connecting to Oracle
On Jan 27, 2014, at 12:04 PM, Alexander Foken alexan...@foken.de wrote: I'm running 64 bit Windows and 64 bit Perl. Is it possible the DBD loader is failing because it is trying to load a 32 bit version of the Oracle driver? Yes. The bitness of Perl and the database libraries must match, not only for Oracle, but for every database (and every C/C++ library you want to use from Perl). [...] Try to get a 64 bit Oracle client, or use a 32 bit Perl. The Oracle Instant Client is very useful for this; you can use both 32-bit and 64-bit Oracle libraries on one system; just install them in different directories and ensure that the environmental variables are properly set prior to running the perl code. I never install the full Oracle client on anything any more. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Getting Error Message Can't load 'C:/Perl64/lib/auto/DBD/Oracle/Oracle.dll While Connecting to Oracle
On Jan 27, 2014, at 12:25 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote: On Jan 27, 2014, at 12:04 PM, Alexander Foken alexan...@foken.de wrote: I'm running 64 bit Windows and 64 bit Perl. Is it possible the DBD loader is failing because it is trying to load a 32 bit version of the Oracle driver? Yes. The bitness of Perl and the database libraries must match, not only for Oracle, but for every database (and every C/C++ library you want to use from Perl). [...] Try to get a 64 bit Oracle client, or use a 32 bit Perl. The Oracle Instant Client is very useful for this; you can use both 32-bit and 64-bit Oracle libraries on one system; just install them in different directories and ensure that the environmental variables are properly set prior to running the perl code. I should clarify this statement: you can do this with the appropriately compiled perl and DBI modules as well. I’ve mainly had to do this when there were 32-bit apps other than perl on the system that needed to connect to Oracle, like Office ODBC and other applications. To use different bit-ness perl applications you need to maintain two separate perl stacks. Also not impossible, but a lot easier on a linux system than Windows. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Error appears in web log but script runs ok on the command line
On Oct 22, 2013, at 10:50 AM, Alexander Foken alexan...@foken.de wrote: Just a wild guess: mod_perl sets %ENV, but *AFTER* loading DBD::Oracle, so the XS part of DBD::Oracle won't see the required environment variables. It is possible to manipulate %ENV from inside Perl (this includes mod_perl) and then load DBD::Oracle, or at least it was possible for a long time. DBD::Oracle isn't being loaded until the script is run on the web server; in theory setting the env in startup.pl is supposed to do this. The mod_perl manual even has a specific troubleshooting section for this problem, using DBD::Oracle as the example. http://perl.apache.org/docs/2.0/user/troubleshooting/troubleshooting.html#C_Libraries_Don_t_See_C__ENV__Entries_Set_by_Perl_Code The solution in this section *still* doesn't work. I fear we've run into the same thing as this guy: http://stackoverflow.com/questions/17050541/unable-to-use-dbdoracle-from-apache-mod-perl and we just don't have the time/patience to screw around trying all the various versions and builds. At this point we're giving up on getting it to work in a mod_perl handler, and just using CGI. If we have performance issues we'll revisit the issue. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Error appears in web log but script runs ok on the command line
Nope, that failed, too. I'm not even trying to use a persistent connection
At this point I believe it's a bug in apache, mod_perl or DBD::Oracle, or some
unholy partial bug in all three that's combining to make my life miserable.
I've been advised to recompile DBD::Oracle with -rpath in the LDFLAGS section
of the Makefile, but I'm unable to find any references to this, do I need to
specify the path with this flag or not?
On Oct 22, 2013, at 11:06 AM, Richie listm...@triad.rr.com wrote:
Or what about modding the shell or init scripts that start apache and make
use of PerlPassEnv
http://perl.apache.org/docs/2.0/user/config/config.html#C_PerlPassEnv_
On 10/22/2013 1:50 PM, Alexander Foken wrote:
Just a wild guess:
mod_perl sets %ENV, but *AFTER* loading DBD::Oracle, so the XS part of
DBD::Oracle won't see the required environment variables. It is possible to
manipulate %ENV from inside Perl (this includes mod_perl) and then load
DBD::Oracle, or at least it was possible for a long time.
I would try to make mod_perl execute something like the following code as
early as possible, even before DBI is loaded:
$ENV{'ORACLE_HOME'}='/some/where';
# maybe set up other Oracle related environment variables
require DBD::Oracle; # loads the XS part AFTER setting up the environment
Alexander
On 21.10.2013 23:36, Bruce Johnson wrote:
Nope, built and run with the same path, same install. I installed the
Instant Client Basic and Development packages via the rpms, set up the
proper environment variables, used cpan to install DBI, then DBD::Oracle
and both built without any errors.
It's absolutely a mod_perl handler thing, I just don't know where the
problem is. It's acting as though it's ignoring its environment settings,
even though a perl script run by that handler says the environment is
correct.
On Oct 21, 2013, at 2:19 PM, Furst, Carl carl.fu...@mlb.com wrote:
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Error appears in web log but script runs ok on the command line
Oh it turned out to be even simpler than that, and for once it wasn't MY fat
fingers that fat fingered it this time :-)
At first, it wasn't working because the SetEnv variable was not set in the
virtual host conf files, just in the main httpd.conf .
Then I didn't check closely enough when the person doing the work said he'd
copied those to the conf where the virtual host definitions were set.
Working system httpd.conf:
grep LD_LIB /etc/httpd/conf/httpd.conf
SetEnv LD_LIBRARY_PATH /usr/lib/oracle/11.2/client64/lib
Broken system httpd.conf:
grep LD_LIB /etc/httpd/conf/httpd.conf
#SetEnv LD_LIBRARY_PATH /usr/lib/oracle/12.1/client64/lib
Broken system ssl.conf:
grep LD_LIB /etc/httpd/conf.d/ssl.conf
SetEnv LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
SetEnv LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
Oops.
Thanks for your help and suggestions, and Carl, who was right all along!
On Oct 20, 2013, at 5:53 AM, lesleyb lesl...@herlug.org.uk wrote:
Hi Bruce
On Fri, Oct 18, 2013 at 07:56:52PM +, Bruce Johnson wrote:
First thing I checked, and they're set correctly;
From %ENV on the broken system:
ORACLE_HOME -- /usr/lib/oracle/12.1/client64
LD_LIBRARY_PATH -- /usr/lib/oracle/12.1/client64/lib
but the original error is
Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so'
The error under the web server environment is saying Oracle.so is not in
/usr/local/lib64/perl5/auto/DBD/Oracle which is where it is looking for it.
I'm assuming Oracle.so is installed on your system somewhere because the
script
works on the command line but not from within Apache.
Perhaps check and compare the %ENV in Apache with the command-line %ENV? And
note Apache is looking for the /usr/local/lib64 directory - not anything in
/usr/lib or /usr/lib64.
Kind regards
Lesley
This is the only thing I can think of that could cause this error. I've
compared permissions, etc with a working server and I see nothing wrong. The
only main difference is that the problem system is running version 12 of the
Instant Client and the working one is running 11.2, but that would cause the
program to fail on the command line as well.
On Oct 18, 2013, at 12:30 PM, Furst, Carl carl.fu...@mlb.com
wrote:
Ld_library_path is getting set in apache configs or wrapper scripts?
Have you tried setting
SetEnv LD_LIBRARY_PATH
In httpd.conf?
Or have it set when you call apachectl??
Carl Furst
On 10/18/13 3:10 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote:
I'm getting the following error on a newly set-up server:
[Fri Oct 18 12:02:06 2013] [error] install_driver(Oracle) failed: Can't
load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module
DBD::Oracle: libocci.so.12.1: cannot open shared object file: No such
file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.\n at (eval
11) line 3\nCompilation failed in require at (eval 11) line 3.\nPerhaps a
required shared library or dll isn't installed where expected\n at
/home/webfiles/apply/perl/oratest.pl line 7\n
It's a really simple script that just connects to the database:
#!/usr/bin/perl
use DBI;
use strict;
my $login=xx;
my $dbpass='xx';
my $dbname=host=x..x.;sid=xx;
my $lda = DBI-connect(dbi:Oracle:$dbname, $login, $dbpass, {RaiseError
=1});
print Content-type: text/html\n\n;
print It Works;
exit;
I've confirmed that Apache has the correct LD_LIBRARY_PATH and
ORACLE_HOME vars set yet whenever we run the script on the web server we
get the error.
On the command line it's:
[root@ perl]# perl oratest.pl
Content-type: text/html
It Works
Any ideas?
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
**
MLB.com: Where Baseball is Always On
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
--
Bruce Johnson
Wherever you go, there you are. B. Banzai, PhD
Re: Error appears in web log but script runs ok on the command line
On Oct 20, 2013, at 1:03 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote: Oh it turned out to be even simpler than that, and for once it wasn't MY fat fingers that fat fingered it this time :-) Cue the sad trombone, because I was wrong, again. It turns out the issue seems to have something to do with mod_perl. It works find from the command line, it works fine when the script is executed as a classic CGI script, it fails when run as a mod_perl handler with an error that indicates that LD_LIBRARY_PATH is wrong or missing. I can recreate the error in CGI mode by commenting out the server directive 'SetEnv LD_LIBRARY_PATH /usr/lib/oracle/11.2/client64/lib' When I found the errors in the conf file, I didn't know that my co-worker had tried that because he'd read that in some web page somewhere and he was starting to get desperate, because nothing was working. I have, however managed to isolate the problem, and it's got to do with mod_perl I'm chasing this on the mod_perl list, but without much luck and am hoping if anyone else has run into this issue using mod_perl and DBD::Oracle. We've set a directory to be handled by mod_perl as follows: Alias /card_access /home/allwebfiles/perl/catcard Directory /home/allwebfiles/perl/catcard SetHandler perl-script PerlResponseHandler ModPerl::Registry PerlOptions +ParseHeaders Options +ExecCGI PerlSetEnv LD_LIBRARY_PATH /usr/lib/oracle/11.2/client64/lib PerlSetEnv ORACLE_HOME /usr/lib/oracle/11.2/client64 /Directory With those directives in place, I get the error every time. (I've added ORACLE_SID and TNS_ADMIN to the PerlSetEnv declarations, but it makes no difference. LD_LIBRARY_PATH is set, this is the contents of %ENV and %INC according to the server, with that mod_perl directive in place: DOCUMENT_ROOT -- /home/allwebfiles/static GATEWAY_INTERFACE -- CGI/1.1 HTTPS -- on HTTP_ACCEPT -- text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8 HTTP_ACCEPT_ENCODING -- gzip,deflate,sdch HTTP_ACCEPT_LANGUAGE -- en-US,en;q=0.8 HTTP_CONNECTION -- keep-alive HTTP_COOKIE -- __qca=P0-1946018635-1381167733063; SESS360e9fa4a6458358b044501f2b5b21b9=4862153113fc157562a1fc7691eecb36; __utma=219252696.1214622818.1382045504.1382045504.1382045504.1; __utmz=219252696.1382045504.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); CASAUTHOK=1; COPInt=ghZxjY9mJ74QPBgWE13I8cGha HTTP_HOST -- x.pharmacy.arizona.edu HTTP_USER_AGENT -- Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36 LD_LIBRARY_PATH -- /usr/lib/oracle/11.2/client64/lib MOD_PERL -- mod_perl/2.0.4 MOD_PERL_API_VERSION -- 2 ORACLE_HOME -- /usr/lib/oracle/11.2/client64 PATH -- /sbin:/usr/sbin:/bin:/usr/bin QUERY_STRING -- REMOTE_ADDR -- 128.196.45.237 REMOTE_PORT -- 49295 REQUEST_METHOD -- GET REQUEST_URI -- /card_access/envvars.pl SCRIPT_FILENAME -- /home/allwebfiles/perl/catcard/envvars.pl SCRIPT_NAME -- /card_access/envvars.pl SERVER_ADDR -- 150.135.124.49 SERVER_ADMIN -- root@localhost SERVER_NAME -- x.pharmacy.arizona.edu SERVER_PORT -- 443 SERVER_PROTOCOL -- HTTP/1.1 SERVER_SIGNATURE -- Apache/2.2.15 (Red Hat) Server at x.pharmacy.arizona.edu Port 443 SERVER_SOFTWARE -- Apache/2.2.15 (Red Hat) SSL_TLS_SNI -- .pharmacy.arizona.edu INC INC- /usr/local/lib64/perl5 INC- /usr/local/share/perl5 INC- /usr/lib64/perl5/vendor_perl INC- /usr/share/perl5/vendor_perl INC- /usr/lib64/perl5 INC- /usr/share/perl5 INC- . INC- /etc/httpd But attempting to create a database handle results in: [Mon Oct 21 10:10:37 2013] [error] install_driver(Oracle) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libocci.so.11.1: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.\n at (eval 11) line 3\nCompilation failed in require at (eval 11) line 3.\nPerhaps a required shared library or dll isn't installed where expected\n at /home/allwebfiles/perl/catcard/oratest.pl line 9\n EVEN THOUGH libocci.so.11.1 is right where it's supposed to be, in $LD_LIBRARY_PATH. # cd $LD_LIBRARY_PATH # pwd /usr/lib/oracle/11.2/client64/lib # ls -al total 185024 drwxr-xr-x 2 root root 4096 May 20 12:07 . drwxr-xr-x 5 root root 4096 May 20 14:12 .. -rw-r--r-- 1 root root 368 Sep 17 2011 glogin.sql lrwxrwxrwx 1 root root17 May 20 12:07 libclntsh.so - libclntsh.so.11.1 -rw-r--r-- 1 root root 52761218 Sep 17 2011 libclntsh.so.11.1 -rw-r--r-- 1 root root 7955322 Sep 17 2011 libnnz11.so lrwxrwxrwx 1 root root15 May 20 12:07 libocci.so - libocci.so.11.1 -rw-r--r-- 1 root root 1971762 Sep 17 2011 libocci.so.11.1 -rw-r--r-- 1 root root 118408281 Sep 17 2011 libociei.so -rw-r--r-- 1 root root164836 Sep 17 2011 libocijdbc11.so -rw-r--r-- 1 root root 1503303 Sep 17 2011 libsqlplusic.so -rw-r--r-- 1 root root 1477446 Sep 17 2011 libsqlplus.so -rw-r--r-- 1 root
Re: Error appears in web log but script runs ok on the command line
Nope, built and run with the same path, same install. I installed the Instant Client Basic and Development packages via the rpms, set up the proper environment variables, used cpan to install DBI, then DBD::Oracle and both built without any errors. It's absolutely a mod_perl handler thing, I just don't know where the problem is. It's acting as though it's ignoring its environment settings, even though a perl script run by that handler says the environment is correct. On Oct 21, 2013, at 2:19 PM, Furst, Carl carl.fu...@mlb.com wrote: So did you build DBD::Oracle with that LD_LIBRARY_PATH? And against that install? Maybe Oracle.so is using LD_LIBRARY_PATH set at build time instead of runtime? Carl Furst On 10/21/13 4:03 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote: On Oct 20, 2013, at 1:03 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote: Oh it turned out to be even simpler than that, and for once it wasn't MY fat fingers that fat fingered it this time :-) Cue the sad trombone, because I was wrong, again. It turns out the issue seems to have something to do with mod_perl. It works find from the command line, it works fine when the script is executed as a classic CGI script, it fails when run as a mod_perl handler with an error that indicates that LD_LIBRARY_PATH is wrong or missing. I can recreate the error in CGI mode by commenting out the server directive 'SetEnv LD_LIBRARY_PATH /usr/lib/oracle/11.2/client64/lib' When I found the errors in the conf file, I didn't know that my co-worker had tried that because he'd read that in some web page somewhere and he was starting to get desperate, because nothing was working. I have, however managed to isolate the problem, and it's got to do with mod_perl I'm chasing this on the mod_perl list, but without much luck and am hoping if anyone else has run into this issue using mod_perl and DBD::Oracle. We've set a directory to be handled by mod_perl as follows: Alias /card_access /home/allwebfiles/perl/catcard Directory /home/allwebfiles/perl/catcard SetHandler perl-script PerlResponseHandler ModPerl::Registry PerlOptions +ParseHeaders Options +ExecCGI PerlSetEnv LD_LIBRARY_PATH /usr/lib/oracle/11.2/client64/lib PerlSetEnv ORACLE_HOME /usr/lib/oracle/11.2/client64 /Directory With those directives in place, I get the error every time. (I've added ORACLE_SID and TNS_ADMIN to the PerlSetEnv declarations, but it makes no difference. LD_LIBRARY_PATH is set, this is the contents of %ENV and %INC according to the server, with that mod_perl directive in place: DOCUMENT_ROOT -- /home/allwebfiles/static GATEWAY_INTERFACE -- CGI/1.1 HTTPS -- on HTTP_ACCEPT -- text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8 HTTP_ACCEPT_ENCODING -- gzip,deflate,sdch HTTP_ACCEPT_LANGUAGE -- en-US,en;q=0.8 HTTP_CONNECTION -- keep-alive HTTP_COOKIE -- __qca=P0-1946018635-1381167733063; SESS360e9fa4a6458358b044501f2b5b21b9=4862153113fc157562a1fc7691eecb36; __utma=219252696.1214622818.1382045504.1382045504.1382045504.1; __utmz=219252696.1382045504.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(no ne); CASAUTHOK=1; COPInt=ghZxjY9mJ74QPBgWE13I8cGha HTTP_HOST -- x.pharmacy.arizona.edu HTTP_USER_AGENT -- Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36 LD_LIBRARY_PATH -- /usr/lib/oracle/11.2/client64/lib MOD_PERL -- mod_perl/2.0.4 MOD_PERL_API_VERSION -- 2 ORACLE_HOME -- /usr/lib/oracle/11.2/client64 PATH -- /sbin:/usr/sbin:/bin:/usr/bin QUERY_STRING -- REMOTE_ADDR -- 128.196.45.237 REMOTE_PORT -- 49295 REQUEST_METHOD -- GET REQUEST_URI -- /card_access/envvars.pl SCRIPT_FILENAME -- /home/allwebfiles/perl/catcard/envvars.pl SCRIPT_NAME -- /card_access/envvars.pl SERVER_ADDR -- 150.135.124.49 SERVER_ADMIN -- root@localhost SERVER_NAME -- x.pharmacy.arizona.edu SERVER_PORT -- 443 SERVER_PROTOCOL -- HTTP/1.1 SERVER_SIGNATURE -- Apache/2.2.15 (Red Hat) Server at x.pharmacy.arizona.edu Port 443 SERVER_SOFTWARE -- Apache/2.2.15 (Red Hat) SSL_TLS_SNI -- .pharmacy.arizona.edu INC INC- /usr/local/lib64/perl5 INC- /usr/local/share/perl5 INC- /usr/lib64/perl5/vendor_perl INC- /usr/share/perl5/vendor_perl INC- /usr/lib64/perl5 INC- /usr/share/perl5 INC- . INC- /etc/httpd But attempting to create a database handle results in: [Mon Oct 21 10:10:37 2013] [error] install_driver(Oracle) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libocci.so.11.1: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.\n at (eval 11) line 3\nCompilation failed in require at (eval 11) line 3.\nPerhaps a required shared library or dll isn't installed where expected\n at /home/allwebfiles/perl/catcard/oratest.pl line 9\n EVEN THOUGH libocci.so.11.1
Re: Failed DBD oracle module
On Sep 20, 2013, at 8:57 AM, Lieng, Gia gia.li...@au.fujitsu.com wrote: $ perl perl_test.script Can't load '/usr/local/lib/perl5/site_perl/5.18.1/sun4-solaris/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: ld.so.1: perl: fatal: /opt/oracle/10.2.0.3/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64 at /usr/local/lib/perl5/5.18.1/sun4-solaris/DynaLoader.pm line 190. at perl_test.script line 4. Compilation failed in require at perl_test.script line 4. BEGIN failed--compilation aborted at perl_test.script line 4. I think that 'Wrong ELF class' error means you have a mismatch in bit-ness between either Perl and Oracle or DBD and Oracle; I think this one means you have the 64-bit Oracle libraries installed on a system expecting 32-bits? Perhaps you don't have the correct ORACLE_HOME and LD_LIBRARY_PATH environment variables set? This can happen if you have 32-bit oracle db installed on the system, but use the 64-bit Oracle Instant Client to compile DBD::Oracle. You can run a 32-bit Oracle DB and use a 64-Bit oracle client on the same system, you just need to know which set of libraries and ORACLE_HOME to look at. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Failed DBD oracle module
On Sep 23, 2013, at 2:02 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote: This can happen if you have 32-bit oracle db installed on the system, but use the 64-bit Oracle Instant Client to compile DBD::Oracle. Or Vice-versa, of course, which might be what your problem is. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Error running make command for DBI1.51
On Aug 19, 2013, at 9:00 PM, pawan bajoria pawan...@yahoo.co.in wrote: /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:380:24: error: sys/types.h: No such file or directory /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:411:19: error: ctype.h: No such file or directory /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:423:23: error: locale.h: No such file or directory /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:440:20: error: setjmp.h: No such file or directory /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:446:26: error: sys/param.h: No such file or directory /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:451:23: error: stdlib.h: No such file or directory /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:456:23: error: unistd.h: No such file or directory /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:488:23: error: string.h: No such file or directory These all appear to be standard C library files, which means, I think, that you don't have the RHEL development packages installed, or if they are, their location is not in your $PATH. If you're using RHEL, why not just update to the current perl package, which on my just installed RHEL system is: This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: connect not connecting
On Jun 28, 2013, at 10:44 AM, eric.b...@barclays.com wrote: DBI-trace(…) might help. You should also review the docs on the use of errstr. You should be using $DBI::errstr, not DBI-errstr to the best of my knowledge and according to the docs. Correct, you need to use either $DBI::errstr or $dbh-errstr. And RaiseError=1 should spit out the error on your stdout device, anyway. Dan, is this related to your ODBC issues earlier this week? If you messed around with or updated your ODBC driver you *might* need to recompile the relevant DBD package and/or check that all the various files and such are where they're expected to be. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Just a note...
Updated my home box to 10.8 at last, and installed the latest Developer tools from the App store, which no longer install the various command-line tools in /Developer/usr/bin but straight up in /usr/bin . My first attempt at re-installing DBI failed because it couldn't find clang. I took the lazy way out and created a Developer directory and symlinked /usr into it. Worked fine, but going forward this is probably something to consider in the installer. -- Bruce Johnson Wherever you go, there you are B. Banzai, PhD
Re: (Fwd) Perl DBI question
On May 9, 2013, at 9:37 AM, tim.bu...@pobox.com wrote: However, then I want to put data into it, with a command like the following: load data local infile 'TEMP_LOAD_DATA_26021' into table ports ; And I get: DBD::mysql::db do failed: The used command is not allowed with this MySQL version at read_excel_write_mysql.pl line 140. I find this in the MySQL docs: If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by default. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”, for information on how to change this. http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Huh? 4=3?
Getting the error:
DBD::Oracle::st execute failed: called with 3 bind variables when 4 are needed
[for Statement insert into employee_fte_annualrate_l (emplid, emptype_cd, fte,
annual_rate) values(?,?,?,?) with ParamValues: :p1='22057713', :p2='R',
:p3='1', :p4='47311'] at /home/oraweb/perl/frs/kfsupdate.pl line 64, DATA
line 581.
I'm pretty sure I count 4 placeholders and 4 parameter values in that error
message, so where is the '3 bind variables' coming from?
here's the cursor definition:
my $csr_emp_info = $lda-prepare(insert into employee_fte_annualrate_l
(emplid, emptype_cd, fte, annual_rate) values(?,?,?,?));
I'm pulling the data from an LDAP query, here's the offending line 64 (where
$mesg is the returned LDAP object):
$csr_emp_info-execute($mesg-entry($n)-get_value('emplId'),
$mesg-entry($n)-get_value('employeeType'),$mesg-entry($n)-get_value('employeeFTE'),$mesg-entry($n)-get_value('employeeTotalAnnualRate'));
All the columns allow null entries, and these are all single-valued entries in
the LDAP schema.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Huh? 4=3?
Well, changing the code to:
my $eid=$mesg-entry($n)-get_value('emplId');
my $etp=$mesg-entry($n)-get_value('employeeType');
my $efte=$mesg-entry($n)-get_value('employeeFTE');
my $ear=$mesg-entry($n)-get_value('employeeTotalAnnualRate');
$csr_emp_info-execute($eid,$etp,$efte,$ear);
like you suggested fixed it.
Thanks.
On May 8, 2013, at 9:30 AM, Bill Ward b...@wards.net wrote:
My guess is that get_value() is returning an empty array rather than an undef
scalar when the values are null. Try copying each one to a scalar variable
and including the list of variables in the execute(). It'd be more readable
that way anyway. Or if you must put them all one one line like this, add the
scalar() function on each argument.
On Wed, May 8, 2013 at 9:18 AM, Bruce Johnson john...@pharmacy.arizona.edu
wrote:
Getting the error:
DBD::Oracle::st execute failed: called with 3 bind variables when 4 are
needed [for Statement insert into employee_fte_annualrate_l (emplid,
emptype_cd, fte, annual_rate) values(?,?,?,?) with ParamValues:
:p1='22057713', :p2='R', :p3='1', :p4='47311'] at
/home/oraweb/perl/frs/kfsupdate.pl line 64, DATA line 581.
I'm pretty sure I count 4 placeholders and 4 parameter values in that error
message, so where is the '3 bind variables' coming from?
here's the cursor definition:
my $csr_emp_info = $lda-prepare(insert into employee_fte_annualrate_l
(emplid, emptype_cd, fte, annual_rate) values(?,?,?,?));
I'm pulling the data from an LDAP query, here's the offending line 64 (where
$mesg is the returned LDAP object):
$csr_emp_info-execute($mesg-entry($n)-get_value('emplId'),
$mesg-entry($n)-get_value('employeeType'),$mesg-entry($n)-get_value('employeeFTE'),$mesg-entry($n)-get_value('employeeTotalAnnualRate'));
All the columns allow null entries, and these are all single-valued entries
in the LDAP schema.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
--
Check out my LEGO blog at brickpile.com
Follow/friend me: Facebook • Flickr • Twitter • LinkedIn
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Huh? 4=3?
On May 8, 2013, at 10:58 AM, fe...@crowfix.com wrote: Is that what's going on here -- the original code imparted a list context, which triggered another perl gotcha, whereby missing list values simply disappear: And I remember now the reason the error message is confusing, I'll bet that was an actual bug I discovered in (iirc) DBI or DBD::Oracle which was fixed, but not on this particular server, because we're still constrained to running a very old version for various reasons. The error message actually displays the values for the last successful insert not the one that failed. Mystery now solved. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: installation problem make: command not found
On Feb 18, 2013, at 11:07 AM, Jon three1...@gmail.com wrote: Hello Abeer, It appears that make is not installed. You can install make by itself (depending on your platform) CentOS/RHEL: yum install make Debian/Ubuntu: apt-get install make or install the development meta package CentOS/RHEL: yum install Developement Tools Debian/ubuntu: apt-get install build-essential Given that the OP is attempting this on a Mac, neither of these suggestions will work at all. If make is not found, you need to install the command-line components and Unix toolchain for the particular version of Apple'd development tools that you have. Or Go to en entirely /opt/ centered setup using MacPorts (you'll likely need to install the MacPorts perl as well. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Trouble installing DBD::Oracle in OS X 10.8 ; Oracle 32 bit drivers the issue?
# Failed test 'install_driver' # at t/01base.t line 24. # got: 'install_driver(Oracle) failed: Can't load '/Users/johnson/.cpan/build/DBD-Oracle-1.52-1xhbkg/blib/arch/auto/DBD/Oracle/Oracle.bundle' for module DBD::Oracle: dlopen(/Users/johnson/.cpan/build/DBD-Oracle-1.52-1xhbkg/blib/arch/auto/DBD/Oracle/Oracle.bundle, 2): Library not loaded: /b/227/rdbms/lib/libclntsh.dylib.10.1 # Referenced from: /Users/johnson/.cpan/build/DBD-Oracle-1.52-1xhbkg/blib/arch/auto/DBD/Oracle/Oracle.bundle # Reason: no suitable image found. Did find: # /oracle32/libclntsh.dylib.10.1: mach-o, but wrong architecture at /opt/local/lib/perl5/5.12.4/darwin-thread-multi-2level/DynaLoader.pm line 204. # at (eval 9) line 3 Is this because my perl is 64-bit and I'm using the 32-bit Oracle drivers? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Trouble installing DBD::Oracle in OS X 10.8 ; Oracle 32 bit drivers the issue?
On Dec 17, 2012, at 10:22 AM, John Scoles byter...@hotmail.com wrote: Is this because my perl is 64-bit and I'm using the 32-bit Oracle drivers? In a word Yes. I have gotten DBD::Oracle on 64big Mac once a while ago. I have to re-compile the perl and DBD::Oracle on the same compiler and it worked also had a full 64 bit client for Oracle as well. Yeah, I had this working just fine in 10.6 with the 64-bit Instant Client. Grrr. Wish oracle'd get off their butts and fix this. :-( Thanks. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Cannot connect to Oracle db; script will not run
On Jul 31, 2012, at 5:54 PM, Warren James - jawarr wrote:
--Beyond some training, I'm very much a newbie to Perl (and this list). So,
please indulge me with my first attempt at a posted question to you; please
see below (*with the full code Perl script toward the bottom of my email)...
-I've been dealing with an issue in a Perl script that I'm writing, similar
to what was posted recently under 'Subject: Script to test connecting to
Oracle DBs' (and 'Subject: Re: Script to test connecting to Oracle DBs' by
Rob Dixon).
Specifically, from my code:
$ENV{TWO_TASK} = lady;
$dbh = DBI-connect($connString, $ladyUser, $ladyPass,
{ AutoCommit=0, RaiseError=0, PrintError=0,
ora_check_sql=0 }) or die Could not connect to database: . DBI-errstr ;
You may need to change your connection string, depending on how your oracle
client is set up. mine is:
host=oracle.host.dns.name;sid=SID
As I dimly recall we had to make that change at some point in the past to get
dbi working, if you're not using the standard port, you need to put that in
there too.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Well now it's just jerking me around...
On Jun 30, 2012, at 9:20 PM, Bruce Johnson wrote: This code fragment: After struggling with it for a bit (which included copying the offending code from the non-working script, and pasting it into a test script with just that bit...which worked)...I ended up pasting in the working code from my SQL editor, replaced the pasted-in values with ?'s again and it worked. Guess I just had to rattle the magic beans sufficiently to distract the gremlins. -- Bruce Johnson Wherever you go, there you are B. Banzai, PhD
Possibly Silly q
Does a new execution of a cursor clear out any existing data or do I have to close the cursor? I have a situation where I'll step through a cursor, fetching rows and might break out of the loop before retrieving all the rows. Then the enclosing loop I'll execute the cursor with a new parameter. DO I have to do $csr-close(); ? or can I just do $csr-execute($param); again? -- Bruce Johnson Wherever you go, there you are B. Banzai, PhD
Well now it's just jerking me around...
This code fragment:
my $csr_resendlist=$lda-prepare(select rl.reservation_id,
h.disp_hour_end from reslist rl, hourofday h where rl.time_block
-trunc(rl.time_block)= h.jfrac_hour and rl.reservation_id =? and
trunc(rl.time_block) = ? and rl.time_block = ? and rl.last_block = 1 order by
time_block);
$csr_resendlist-execute($showreslist{$i}{'resid'},$showdate_j,
$showreslist{$i}{'tb'});
$last_block=0;
until ($last_block){
($last_block, $res_end)=$csr_resendlist-fetchrow();
if ($last_block ==1){$showreslist{$evt_count}{'end'} =
$res_end;}
}
...
Is giving me the error:
Sat Jun 30 20:39:21 2012] [error] [client 128.196.117.44] DBD::Oracle::st
fetchrow failed: ERROR no statement executing (perhaps you need to call execute
first) [for Statement select rl.reservation_id, h.disp_hour_end from reslist
rl, hourofday h where rl.time_block -trunc(rl.time_block)= h.jfrac_hour and
rl.reservation_id =? and trunc(rl.time_block) = ? and rl.time_block = ? and
rl.last_block = 1 order by time_block with ParamValues: :p1='2456108.28',
:p2='2456108', :p3='248227'] at /home/allwebfiles/perl/list_all_fixed.pl line
195, DATA line 558., referer:
https://resource-scheduler.pharmacy.arizona.edu/s/list_all_fixed.pl
[Sat Jun 30 20:39:21 2012] [error] [client 128.196.117.44] DBD::Oracle::st
fetchrow failed: ERROR no statement executing (perhaps you need to call execute
first) [for Statement select rl.reservation_id, h.disp_hour_end from reslist
rl, hourofday h where rl.time_block -trunc(rl.time_block)= h.jfrac_hour and
rl.reservation_id =? and trunc(rl.time_block) = ? and rl.time_block = ? and
rl.last_block = 1 order by time_block with ParamValues: :p1='2456108.28',
:p2='2456108', :p3='248227'] at /home/allwebfiles/perl/list_all_fixed.pl line
195, DATA line 558., referer:
https://resource-scheduler.pharmacy.arizona.edu/s/list_all_fixed.pl
Clearly I am executing the thing, it has the param values!
(the query works in DB visualizer with the param values plugged into the right
place...this is driving me mad...I'm supposed to be on vacation., [Belushi/]But
No![/Belushi] I have to debug code)
--
Bruce Johnson
Wherever you go, there you are B. Banzai, PhD
Odd error using bind_param_inout
I'm getting the following error:
[Mon Jun 04 09:14:49 2012] [error] [client 128.196.45.237] DBD::Oracle::db do
failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for
Statement insert into resources (
short_name,long_name,building_id,room_desc,isaroom,numseats,numtables,hour_open,hour_close,available,computer,enet_num,approved_text)values(
'B340','SP Training Room','1062','','1','','','7','20','A','','','') returning
resource_id into :new_id] at /home/allwebfiles/perl/resource_mgmt2.pl line
67., referer:
https://resource-scheduler.pharmacy.arizona.edu/calendar/resource_mgmt.pl
The relevant perl code is:
my $new_resource_id = 0;
my $csr_insert = $lda-do($sq_insert);
$csr_insert-bind_param_inout(':new_id', \$new_resource_id, 25);
$csr_insert-execute();
There's only one variable, and as far as I can see it's correct.
$sq_insert is the statement listed in the logged error. resource_id is created
via an 'on insert' trigger.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Odd error using bind_param_inout
D'OH!
Thanks!
On Jun 4, 2012, at 11:12 AM, Jeffrey Seger wrote:
$dbh-do() executes your sql. You are binding the parameter after this.
Use $dbh-prepare instead.
On Mon, Jun 4, 2012 at 2:01 PM, Bruce Johnson
john...@pharmacy.arizona.eduwrote:
I'm getting the following error:
[Mon Jun 04 09:14:49 2012] [error] [client 128.196.45.237]
DBD::Oracle::db do failed: ORA-01008: not all variables bound (DBD ERROR:
OCIStmtExecute) [for Statement insert into resources (
short_name,long_name,building_id,room_desc,isaroom,numseats,numtables,hour_open,hour_close,available,computer,enet_num,approved_text)values(
'B340','SP Training Room','1062','','1','','','7','20','A','','','')
returning resource_id into :new_id] at /home/allwebfiles/perl/
resource_mgmt2.pl line 67., referer:
https://resource-scheduler.pharmacy.arizona.edu/calendar/resource_mgmt.pl
The relevant perl code is:
my $new_resource_id = 0;
my $csr_insert = $lda-do($sq_insert);
$csr_insert-bind_param_inout(':new_id', \$new_resource_id, 25);
$csr_insert-execute();
There's only one variable, and as far as I can see it's correct.
$sq_insert is the statement listed in the logged error. resource_id is
created via an 'on insert' trigger.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
--
Champions do not become champions when they win the event, but in the
hours, weeks, months and years they spend preparing for it. The victorious
performance itself is merely the demonstration of their championship
character. -T. Alan Armstrong
The Ow that can be expressed is not the true Ow. - Ao Tzu
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: It's a bad day here...
Oh man this is embarrassing :-) A sharp-eyed reader pointed out that I was not
doing what I thought I was doing!
I noticed the following (edited for emphasis):
foreach $i (@resources){
$csr_prefapp-execute($i)
$csr_prefapp-fetchrow())
if (!$have_pref){
$csr_allapp-execute($i)
$csr_prefapp-fetchrow())
}
}
Shouldn't that last prefapp be allapp instead?
Indeed it should, and with that error corrected, all is well!
The error makes perfect sense now, since I'd already returned all the rows for
that cursor.
(and really dumb me, I've seen this very error in the past when I've referenced
the wrong cursor)
--
Bruce Johnson
Wherever you go, there you are B. Banzai, PhD
Error I've not seen before from oracle DBD
Can't mix placeholder styles (:foo/?) at /usr/local/lib64/perl5/DBD/Oracle.pm
line 329., referer:
https://resource-scheduler.pharmacy.arizona.edu/calendar/reserve.pl
I thought this is the code fragment producing this error:
my $lda = DBI-connect(dbi:Oracle:$dbname, $login, $dbpass) or die
$DBI::errstr;
my $sq_res_pend = insert into reservations_pend (pid, email, cn, purpose,
reserver_affstring) values(?,?,?,?,?) returning reservations_pend_id into
:NEWID;
my $csr_res_pend = $lda-prepare($sq_res_pend) or die $DBI::errstr;
$csr_res_pend-bind_param(1,$res_pid) or die $DBI::errstr;
$csr_res_pend-bind_param(2,$res_email) or die $DBI::errstr;
$csr_res_pend-bind_param(3,$res_name) or die $DBI::errstr;
$csr_res_pend-bind_param(4,$res_purp) or die $DBI::errstr;
$csr_res_pend-bind_param(5,$res_affil) or die $DBI::errstr;
$csr_res_pend-bind_param_inout(:NEWID,\$new_res_id, 25) or die $DBI::errstr;
$csr_res_pend-execute();
But when I changed it to all ':foo style, I still got the same error:
my $sq_res_pend = insert into reservations_pend (pid, email, cn, purpose,
reserver_affstring) values(:1,:2,:3,:4,:5) returning reservations_pend_id into
:NEWID;
my $csr_res_pend = $lda-prepare($sq_res_pend) or die $DBI::errstr;
$csr_res_pend-bind_param(':1',$res_pid) or die $DBI::errstr;
$csr_res_pend-bind_param(':2',$res_email) or die $DBI::errstr;
$csr_res_pend-bind_param(':3',$res_name) or die $DBI::errstr;
$csr_res_pend-bind_param(':4',$res_purp) or die $DBI::errstr;
$csr_res_pend-bind_param(':5',$res_affil) or die $DBI::errstr;
$csr_res_pend-bind_param_inout(:NEWID,\$new_res_id, 25) or die $DBI::errstr;
$csr_res_pend-execute();
This is the only place in my program where I even used bind_param; every other
cursor is of the 'select foo, bar, bax from thetable where id =?' style.
Also, the line number in the error is from Oracle.pm, how do I find out what
line in my program caused this?
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Error I've not seen before from oracle DBD
On Mar 29, 2012, at 1:46 PM, Bruce Johnson wrote:
Can't mix placeholder styles (:foo/?) at /usr/local/lib64/perl5/DBD/Oracle.pm
line 329., referer:
https://resource-scheduler.pharmacy.arizona.edu/calendar/reserve.pl
Commenting out this section code gets rid of the error, so I know this is the
offending section. What is causing this error? The code looks right to me.
my $sq_res_pend = insert into reservations_pend (pid, email, cn, purpose,
reserver_affstring) values(:1,:2,:3,:4,:5) returning reservations_pend_id
into :NEWID;
my $csr_res_pend = $lda-prepare($sq_res_pend) or die $DBI::errstr;
$csr_res_pend-bind_param(':1',$res_pid) or die $DBI::errstr;
$csr_res_pend-bind_param(':2',$res_email) or die $DBI::errstr;
$csr_res_pend-bind_param(':3',$res_name) or die $DBI::errstr;
$csr_res_pend-bind_param(':4',$res_purp) or die $DBI::errstr;
$csr_res_pend-bind_param(':5',$res_affil) or die $DBI::errstr;
$csr_res_pend-bind_param_inout(:NEWID,\$new_res_id, 25) or die
$DBI::errstr;
$csr_res_pend-execute();
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Error I've not seen before from oracle DBD
On Mar 29, 2012, at 1:58 PM, Bill Ward wrote:
Try not using numbers for the placeholder names?
That worked...
my $sq_res_pend = insert into reservations_pend (pid, email, cn, purpose,
reserver_affstring) values(:A,:B,:C,:D,:E) returning reservations_pend_id into
:NEWID;
my $csr_res_pend = $lda-prepare($sq_res_pend) or die $DBI::errstr;
$csr_res_pend-bind_param(':A',$res_pid) or die $DBI::errstr;
$csr_res_pend-bind_param(':B',$res_email) or die $DBI::errstr;
$csr_res_pend-bind_param(':C',$res_name) or die $DBI::errstr;
$csr_res_pend-bind_param(':D',$res_purp) or die $DBI::errstr;
$csr_res_pend-bind_param(':E',$res_affil) or die $DBI::errstr;
$csr_res_pend-bind_param_inout(:NEWID,\$new_res_id, 25) or die $DBI::errstr;
$csr_res_pend-execute();
Got right through.
Is this a bug or a rule I'm not aware of?
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
It's a bad day here...
another odd error:
[for Statement select distinct a.cn, a.email from admins a, resourceadminaffil
r, resources r2, adminaffil a2, affils a3 where a.pid = r.pid and r.resource_id
= r2.resource_id and a.pid = a2.pid and a2.affil_id = a3.affil_id and
a3.affil_code in ('1901','PHRM') and r2.resource_id =? with ParamValues:
:p1='149'] at /home/allwebfiles/perl/reserve2.pl line 124., referer:
https://resource-scheduler.pharmacy.arizona.edu/calendar/reserve.pl
Execute is being called, so what else could cause this error?
Happens at:
my $sq_prefapp =select distinct a.cn, a.email from admins a,
resourceadminaffil r, resources r2, adminaffil a2, affils a3 where a.pid =
r.pid and r.resource_id = r2.resource_id and a.pid = a2.pid and a2.affil_id =
a3.affil_id and a3.affil_code in ('$dept','$coll') and r2.resource_id =?;
my $csr_prefapp= $lda-prepare($sq_prefapp) or die $DBI::errstr;
my $sq_allapp =select distinct a.cn, a.email from admins a, resourceadminaffil
r, resources r2 where a.pid = r.pid and r.resource_id = r2.resource_id and
r2.resource_id =?;
my $csr_allapp =$lda-prepare($sq_allapp) or die $DBI::errstr;
my ($have_pref, %approvers);
foreach $i (@resources){
$have_pref=0;
$csr_prefapp-execute($i) or die
$DBI::errstr; I know this is being called,
because the right value is in the ParamValues part of the error message
while (($k, $j) =
$csr_prefapp-fetchrow()){$approvers{$k}=$j;$have_pref=1;}
if (!$have_pref){
$csr_allapp-execute($i) or die $DBI::errstr;
while (($k, $j) = $csr_prefapp-fetchrow()){$approvers{$k}=$j;}
}
}
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: It's a bad day here...
On Mar 29, 2012, at 2:45 PM, Bruce Johnson wrote:
another odd error:
Accidentally clipped off the actual error:
DBD::Oracle::st fetchrow failed: ERROR no statement executing (perhaps you need
to call execute first)
[for Statement select distinct a.cn, a.email from admins a,
resourceadminaffil r, resources r2, adminaffil a2, affils a3 where a.pid =
r.pid and r.resource_id = r2.resource_id and a.pid = a2.pid and a2.affil_id =
a3.affil_id and a3.affil_code in ('1901','PHRM') and r2.resource_id =? with
ParamValues: :p1='149'] at /home/allwebfiles/perl/reserve2.pl line 124.,
referer: https://resource-scheduler.pharmacy.arizona.edu/calendar/reserve.pl
Execute is being called, so what else could cause this error?
Happens at:
my $sq_prefapp =select distinct a.cn, a.email from admins a,
resourceadminaffil r, resources r2, adminaffil a2, affils a3 where a.pid =
r.pid and r.resource_id = r2.resource_id and a.pid = a2.pid and a2.affil_id =
a3.affil_id and a3.affil_code in ('$dept','$coll') and r2.resource_id =?;
my $csr_prefapp= $lda-prepare($sq_prefapp) or die $DBI::errstr;
my $sq_allapp =select distinct a.cn, a.email from admins a,
resourceadminaffil r, resources r2 where a.pid = r.pid and r.resource_id =
r2.resource_id and r2.resource_id =?;
my $csr_allapp =$lda-prepare($sq_allapp) or die $DBI::errstr;
my ($have_pref, %approvers);
foreach $i (@resources){
$have_pref=0;
$csr_prefapp-execute($i) or die
$DBI::errstr; I know this is being called,
because the right value is in the ParamValues part of the error message
while (($k, $j) =
$csr_prefapp-fetchrow()){$approvers{$k}=$j;$have_pref=1;}
if (!$have_pref){
$csr_allapp-execute($i) or die $DBI::errstr;
while (($k, $j) = $csr_prefapp-fetchrow()){$approvers{$k}=$j;}
}
}
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
question about bind_param_inout and oracle
In the docs there's a note that bind_param_inout_array requires a maxlen value, but that Oracle ignores this. The third parameter of bind_param_inout_array, (0 in the example), maxlen is required by DBI but not used by DBD::Oracle Is this true for the bind_param_inout method? as well? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: question about bind_param_inout and oracle
On Jan 5, 2012, at 1:56 PM, John Scoles wrote: From: john...@pharmacy.arizona.edu Subject: question about bind_param_inout and oracle Date: Thu, 5 Jan 2012 11:37:50 -0700 To: dbi-users@perl.org In the docs there's a note that bind_param_inout_array requires a maxlen value, but that Oracle ignores this. bind_param_inout_array is an undocumented and implimented feature of DBI spec, I think DBD::Oracle is the only one that implements it, it doesn't need it but it is required as it is part of the DBI spec so it needs to take the empty value. The third parameter of bind_param_inout_array, (0 in the example), maxlen is required by DBI but not used by DBD::Oracle Is this true for the bind_param_inout method? as well? bind_param_inout is implimented fully on the DBI side so what ever the DBI spec says it needs it then it needs it. Hope this helps. It does. Thank you. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: problem getting DBD::mysql working in OS X 10.7
On Dec 30, 2011, at 6:55 PM, Bruce Johnson wrote: I would say yes, here's mine (from MacPorts): Well, I found this: http://probably.co.uk/problems-installing-dbdmysql-on-os-x-snow-leopard.html that was supposed to fix the issue. It didn't, so I took the brute-force kludge of symlinking the dylib to /usr/lib That works. otool still shows the dylib file sans path, but the original script now works... But python is still broken, which is another part of my toolchain. Gonna rip it all out and use MacPorts... -- Bruce Johnson Wherever you go, there you are B. Banzai, PhD
problem getting DBD::mysql working in OS X 10.7
I tried installing Bundle::DBD::mysql via cpan, but got errors about how it couldn't find mysql_config, so I downloaded the DBD::mysql package manually and used perl Makefile.pl --mysql_config=/usr/local/mysql/bin/mysql_config which is where my mysql_config file lives (it's a standard install for 10.7 from oracle) That installed correctly, but scripts run by apache on the system fail with the error: [Thu Dec 29 15:25:25 2011] [error] [client 192.168.1.114] install_driver(mysql) failed: Can't load '/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle, 1): Library not loaded: libmysqlclient.18.dylib Now the original attempt at installing with cpan did fail on the same library, but it's right there: bruces-Mac-Pro:~ johnson$ cd /usr/local/mysql/lib bruces-Mac-Pro:lib johnson$ ls -l total 375848 -rwxr-xr-x 1 root wheel 3787624 Nov 23 08:07 libmysqlclient.18.dylib -rw-r--r-- 1 root wheel 10008536 Nov 23 08:07 libmysqlclient.a lrwxr-xr-x 1 root wheel23 Dec 28 16:28 libmysqlclient.dylib - libmysqlclient.18.dylib lrwxr-xr-x 1 root wheel20 Dec 28 16:28 libmysqlclient_r.18.dylib - libmysqlclient.dylib lrwxr-xr-x 1 root wheel16 Dec 28 16:28 libmysqlclient_r.a - libmysqlclient.a lrwxr-xr-x 1 root wheel20 Dec 28 16:28 libmysqlclient_r.dylib - libmysqlclient.dylib -rw-r--r-- 1 root wheel 93405392 Nov 23 08:04 libmysqld-debug.a -rw-r--r-- 1 root wheel 85193728 Nov 23 08:07 libmysqld.a -rw-r--r-- 1 root wheel 8488 Nov 23 08:07 libmysqlservices.a drwxr-xr-x 14 root wheel 476 Nov 23 08:09 plugin Any ideas? -- Bruce Johnson Wherever you go, there you are B. Banzai, PhD
Re: problem getting DBD::mysql working in OS X 10.7
On Dec 29, 2011, at 3:36 PM, Bruce Johnson wrote: I tried installing Bundle::DBD::mysql via cpan, but got errors about how it couldn't find mysql_config, so I downloaded the DBD::mysql package manually and used perl Makefile.pl --mysql_config=/usr/local/mysql/bin/mysql_config which is where my mysql_config file lives (it's a standard install for 10.7 from oracle) That installed correctly, but scripts run by apache on the system fail with the error: [Thu Dec 29 15:25:25 2011] [error] [client 192.168.1.114] install_driver(mysql) failed: Can't load '/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle, 1): Library not loaded: libmysqlclient.18.dylib locally run perl scripts also fail with the same error. -- Bruce Johnson Wherever you go, there you are B. Banzai, PhD
Re: Design Pattern to keep a connection opened?
On Nov 5, 2011, at 3:57 PM, Brandon Phelps wrote: Can anyone point me in the right direction for keeping a connection to my database opened? The script I need to write will run 24/7 executing a postgresql stored procedure which inserts records into the database. The problem I think I might have is using a standard procedural syntax of: Open connection Loop Call stored procedure/function ...if my connection gets interrupted somewhere during the loop any subsequent iterations will obviously fail, so I need a way of reconnecting to the database should the need arise. Basically something like this (I'm just not sure how to actually code this): Why not just open a database connection, run the procedure and close it again? : Loop open db run proc close db End loop ? If the connection is staying open long enough to get stale and be disconnected, then you shouldn't be running into timing issues. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: UPDATE statements causing me grief!
On Oct 7, 2011, at 7:25 AM, tiger peng wrote: Some time, even committed, the data was still mysteriously disappearing while another tool worked perfectly. Why?? ROFL. A while back, while I was making changes to a production system, I modified the authentication scripts so that when I logged in all my changes went to test tables, not the production tables. (after all, I'm not the worlds most interesting man http://memegenerator.net/instance/10264631) This was a system that I didn't use for real a whole lot. A couple months later I go to use the system for real...and it doesn't work...I spent a half-day trying to figure out why it worked for other people, and not me...:-) I suspect we've all committed more than our share of Stupid Programmer Tricks. I was playing on different databases! I know it sounds silly but did you forget to commit? I've been doing DB programming for years but every now and again I waste 10-15 minutes on mysteriously disappearing data because I forgot to commit. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: DBD::ODBC fails, SQL*Plus works
On Oct 5, 2011, at 9:09 AM, Scott Stansbury wrote: It returns (after a few seconds) with an ORA-12154 error: TNS:could not resolve the connect identifier specified ( SQL-08004). Basic questions: the script is running in an environment where the env variables $ORACLE_HOME and $TNS_ADMIN are available? Your tnsnames.ora file is present and correct? (if not, the those vars and put this: PROD77 = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))) in a text file, save it as tnsnames.ora in your $ORACLE_HOME directory and see if it works now) -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Tail Module + DBI Module, can\'t keep up!
On Sep 21, 2011, at 8:55 AM, Curtis Leach wrote: Here's a way that might speed things up for you considerably by eliminating a DB hit. 4. Perl script from (3) constantly reads in the /var/log files using the Tail module. It does the following: a. When a connection is opened, it INSERTs into the sonicwall_connections table b. When a connection is closed, it SELECTs from the sonicwall_connection table the last record id that matches the protocol, source_ip, source_port, and destination_port c. If a record exists matching this criteria, it UPDATEs that record's close_dt column with the time the connection was closed d. If a record does not exist, then in our case this means that the connection was denied due to firewall rules, and we instead INSERT into a different table, sonicwall_denied [...] If the slowdown is truly this step of inserting data into the DB, how about just inserting the unchecked log entry into the database and doing all the ruke matching post insert, or doing the matching as views into the raw table; manage all the above business rules in the database. If you're just looking for open and close records, for example: Perl script watches the log if a log entry matches either OPEN or CLOSE dump it to the raw log table, which is indexed on protocol, source_ip, source_port, and destination_port. Then with the data in the database, you can do the queries needed to find the open and close of a connection and easily find rows with a close without an open to find the sonicwall-denied entries. (this, in fact was exactly how we used to manage the same type of information about a Cisco terminal server, looking for abnormally dropped connections and identifying who was connected when to what IP address) Basically you just need to process the log entries into row inserts without doing any other queries to the database, which will be about as fast as you can manage, especially if you do block commits. Then if it's still not fast enough to keep up, you need to look elsewhere for speed improvements Quit making Perl do the work of Mysql, in other words. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: One of us can't count....
On Feb 18, 2011, at 1:55 PM, Bruce Johnson wrote:
Figured it out.
The second ldap query did not return all the attributes I was looking for,
and when it returned no value for that attribute, the bind variable wasn't
populated, leading to the mismatch between expected and actual. The error is
correct.
The error MESSAGE, however contained the last known value for that number
bind variable, which may be a bug.
I wasn't quite correct, the error message actually lists the parameter values
of the last successful insert statement.
Here is a test script that demonstrates the problem. (I saw this on oracle,
dunno if it's actually a DBD oracle issue or a DBI issue.)
---
#!/usr/bin/perl
use DBI;
$login=useyourown;
$dbpass=useyourown;
$dbname=host=server.name;sid=sid_name;
# Create table statement
$sqcreate = SQ;
create table test (
foo varchar2(10),
bar varchar2(10),
baz number)
SQ
$sql = insert into test (bar, baz, foo) values (?,?,?);
$dbh = DBI-connect(dbi:Oracle:$dbname, $login, $dbpass);
$dbh-do($sqcreate) or die $dbh-errstr;
$csr = $dbh-prepare($sql) or die $dbh-errstr;
$parms{1}{'foo'}=Bill;
$parms{1}{'bar'}=Kaboom;
$parms{1}{'baz'}=123;
$parms{2}{'foo'}=Mike;
$parms{3}{'foo'}=Jane;
$parms{3}{'bar'}=Kuunch;
$parms{4}{'foo'}=Alice;
$parms{4}{'bar'}=Dorrp;
$parms{4}{'baz'}=456;
$parms{5}{'foo'}=Pat;
$parms{5}{'bar'}=PaDing;
for ($i=1;$i6;$i++){
@inparms =();
foreach $k(sort keys %{$parms{$i}}){
push @inparms, $parms{$i}{$k};
}
$csr-execute(@inparms);
}
exit;
--
This is the output of this script:
DBD::Oracle::st execute failed: called with 1 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
43.
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
43.
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Dorrp', :p2=456, :p3='Alice'] at ./testofparamarray.pl line
43.
My environment is:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
DBI (1.607) - Database independent interface for Perl
DBD::Oracle (1.22) - Oracle database driver for the DBI module
perl --version
This is perl, v5.10.0 built for i686-linux
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: One of us can't count....
On Feb 21, 2011, at 11:11 AM, Bruce Johnson wrote:
On Feb 18, 2011, at 1:55 PM, Bruce Johnson wrote:
Figured it out.
The second ldap query did not return all the attributes I was looking for,
and when it returned no value for that attribute, the bind variable wasn't
populated, leading to the mismatch between expected and actual. The error is
correct.
The error MESSAGE, however contained the last known value for that number
bind variable, which may be a bug.
I wasn't quite correct, the error message actually lists the parameter values
of the last successful insert statement.
Here is a test script that demonstrates the problem.
Here is a somewhat clearer one that tells you what data we're trying to insert:
-
#!/usr/bin/perl
use DBI;
$login=pharmmail;
$dbpass=nhy329;
$dbname=host=tonic.pharmacy.arizona.edu;sid=phmweb;
# Create table statement
$sqcreate = SQ;
create table test (
foo varchar2(10),
bar varchar2(10),
baz number)
SQ
$sql = insert into test (bar, baz, foo) values (?,?,?);
$dbh = DBI-connect(dbi:Oracle:$dbname, $login, $dbpass);
$dbh-do($sqcreate) or die $dbh-errstr;
$csr = $dbh-prepare($sql) or die $dbh-errstr;
$parms{1}{'foo'}=Bill;
$parms{1}{'bar'}=Kaboom;
$parms{1}{'baz'}=123;
$parms{2}{'foo'}=Mike;
$parms{3}{'foo'}=Jane;
$parms{3}{'bar'}=Kuunch;
$parms{4}{'foo'}=Alice;
$parms{4}{'bar'}=Dorrp;
$parms{4}{'baz'}=456;
$parms{5}{'foo'}=Pat;
$parms{5}{'bar'}=PaDing;
for ($i=1;$i6;$i++){
@inparms =();
print inserting data for $parms{$i}{'foo'}\n;
foreach $k(sort keys %{$parms{$i}}){
push @inparms, $parms{$i}{$k};
}
$csr-execute(@inparms);
}
exit;
And the error:
oraweb@tonic:~/perl/pharmmail ./testofparamarray.pl
inserting data for Bill
inserting data for Mike
DBD::Oracle::st execute failed: called with 1 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
44.
inserting data for Jane
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
44.
inserting data for Alice
inserting data for Pat
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Dorrp', :p2=456, :p3='Alice'] at ./testofparamarray.pl line
44.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: One of us can't count....
On Feb 21, 2011, at 11:20 AM, Bruce Johnson wrote: On Feb 21, 2011, at 11:11 AM, Bruce Johnson wrote: On Feb 18, 2011, at 1:55 PM, Bruce Johnson wrote: Figured it out. The second ldap query did not return all the attributes I was looking for, and when it returned no value for that attribute, the bind variable wasn't populated, leading to the mismatch between expected and actual. The error is correct. The error MESSAGE, however contained the last known value for that number bind variable, which may be a bug. I wasn't quite correct, the error message actually lists the parameter values of the last successful insert statement. Here is a test script that demonstrates the problem. Here is a somewhat clearer one that tells you what data we're trying to insert: - #!/usr/bin/perl use DBI; $login=pharmmail; $dbpass= Ever have one of those days? My year has been like that so far...:-( forgot to scrub the 'better version' of actual account info. schema password changed, please forget you ever saw this :-/ (this oracle user only has access to a handful of tables on a db that cannot be reached from the outside without authentication, and is used on a web script that also cannot be reached from the outside without authentication..as security breaches go, this was about as good as it gets.) -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
One of us can't count....
I'm getting the following error: (some data has been change to protect the bystanders.) DBD::Oracle::st execute failed: called with 18 bind variables when 19 are needed [for Statement insert into edsbase (cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,employeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL OF PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411', :p16='PO BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='1', :p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S', :p9='wsz'] at ./edsload.pl line 97, DATA line 532. I've counted three times: I have 19 fields in the db, 19 '?'s, and 19 param values being passed, why am I getting this error? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: One of us can't count....
No, this is done as: $csr=$dbh-prepare($sql) then as $csr-execute(@parms) inside of a loop. On Feb 18, 2011, at 12:33 PM, Bill Ward wrote: You're probably calling do($sql, @args) when you should call do($sql, undef, @args) On Fri, Feb 18, 2011 at 11:31 AM, Bruce Johnson john...@pharmacy.arizona.edu wrote: I'm getting the following error: (some data has been change to protect the bystanders.) DBD::Oracle::st execute failed: called with 18 bind variables when 19 are needed [for Statement insert into edsbase (cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,employeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL OF PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411', :p16='PO BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='1', :p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S', :p9='wsz'] at ./edsload.pl line 97, DATA line 532. I've counted three times: I have 19 fields in the db, 19 '?'s, and 19 param values being passed, why am I getting this error? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs -- Check out my LEGO blog at http://www.brickpile.com/ View my photos at http://flickr.com/photos/billward/ Follow me at http://twitter.com/williamward -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: One of us can't count....
This is a quickie version of what I'm doing:
$sql =insert into foo (bar, baz, buz) values(?,?,?);
$csr = $dbh-prepare($sql);
while (ldap query runs){
@parms =($val1,$val2,$val3);
if ($val1 eq 'A'){ $csr-execute(@parms);}
}
The thing is, this is the second query to the LDAP server in the program, the
first one, using identical DBI code, works.
The only difference is the LDAP query.
On Feb 18, 2011, at 1:10 PM, Furst, Carl wrote:
Are you calling $csr-finish at the end of each iteration? Might not be the
issue but indeed good practice.
Carl Furst
o/~ What a difference a byte makes... o/~
-Original Message-
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu]
Sent: Friday, February 18, 2011 3:07 PM
Cc: DBI Users List (dbi-users@perl.org)
Subject: Re: One of us can't count
No, this is done as:
$csr=$dbh-prepare($sql)
then as $csr-execute(@parms) inside of a loop.
On Feb 18, 2011, at 12:33 PM, Bill Ward wrote:
You're probably calling do($sql, @args) when you should call do($sql,
undef,
@args)
On Fri, Feb 18, 2011 at 11:31 AM, Bruce Johnson
john...@pharmacy.arizona.edu wrote:
I'm getting the following error: (some data has been change to protect
the
bystanders.)
DBD::Oracle::st execute failed: called with 18 bind variables when 19 are
needed [for Statement insert into edsbase
(cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate
,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,emplo
yeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip
)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL
OF
PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411',
:p16='PO
BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='1',
:p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S',
:p9='wsz'] at ./edsload.pl line 97, DATA line 532.
I've counted three times: I have 19 fields in the db, 19 '?'s, and 19
param
values being passed, why am I getting this error?
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
--
Check out my LEGO blog at http://www.brickpile.com/
View my photos at http://flickr.com/photos/billward/
Follow me at http://twitter.com/williamward
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
**
MLB.com: Where Baseball is Always On
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: One of us can't count....
Tried it that way, same error. On Feb 18, 2011, at 12:33 PM, Bill Ward wrote: You're probably calling do($sql, @args) when you should call do($sql, undef, @args) On Fri, Feb 18, 2011 at 11:31 AM, Bruce Johnson john...@pharmacy.arizona.edu wrote: I'm getting the following error: (some data has been change to protect the bystanders.) DBD::Oracle::st execute failed: called with 18 bind variables when 19 are needed [for Statement insert into edsbase (cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,employeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL OF PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411', :p16='PO BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='1', :p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S', :p9='wsz'] at ./edsload.pl line 97, DATA line 532. I've counted three times: I have 19 fields in the db, 19 '?'s, and 19 param values being passed, why am I getting this error? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs -- Check out my LEGO blog at http://www.brickpile.com/ View my photos at http://flickr.com/photos/billward/ Follow me at http://twitter.com/williamward -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: One of us can't count....
Figured it out.
The second ldap query did not return all the attributes I was looking for, and
when it returned no value for that attribute, the bind variable wasn't
populated, leading to the mismatch between expected and actual. The error is
correct.
The error MESSAGE, however contained the last known value for that number bind
variable, which may be a bug.
Simplified, here's what I think is happening:
I'm running an LDAP query (using LDAP::Simple, which returns the query as a big
hash) looking for cn, email, employeeId, and username attributes.
this would be my data set:
'Joe Blow', 'j...@here.com','123','jblow'
'Mike Smith',,'234',
'Jane Someone','jane@elsewhere','567',
My sql is:
insert into emptable (cn, email, eid, uname) values (?,?,?,?)
when I query the LDAP server I'll get a hash for each row:
$return{1}{'cn'} would give me 'Joe Blow'
$return{1}{'email'} would give me 'j...@here.com'
$return{1}{'eid'} would give me '123'
$return{1}{'uname'} would give me 'jblow'
This one works.
$return{2}{'cn'} would give me 'Mike Smith'
$return{2}{'email'} wouldn't exist because there is no email attribute in the
ldap database for Mike Smith.
$return{2}{'eid'} would give me '234'
$return{2}{'uname'} wouldn't exist because there is no uname attribute in the
ldap database for Mike Smith.
This one would throw the error:
'DBD::Oracle::st execute failed: called with 2 bind variables when 4 are needed
[for Statement insert into emptable (cn, email, eid, uname) values (?,?,?,?)
with ParamValues: :p1='Mike Smith',:p2='j...@here.com',:p3='234', :p4='jblow']
$return{3}{'cn'} would give me 'Jane Someone'
$return{3}{'email'} would give me 'jane@elsewhere'
$return{3}{'eid'} would give me '567'
$return{3}{'uname'} wouldn't exist because there is no uname attribute in the
ldap database for Jane Someone.
This one would throw the error:
'DBD::Oracle::st execute failed: called with 3 bind variables when 4 are needed
[for Statement insert into emptable (cn, email, eid, uname) values (?,?,?,?)
with ParamValues: :p1='Jane Someone',:p2='jane@elsewhere',:p3='567',
:p4='jblow']
I had to look through a bunch of the errors to find the pattern.
Fortunately there were only two fields in the LDAP query I was using that might
not exist in my second data set; checking those and explicitly populating them
with perl nulls ('') if there was no key of that name, fixed it, and the script
runs without errors.
On Feb 18, 2011, at 1:20 PM, Bruce Johnson wrote:
This is a quickie version of what I'm doing:
$sql =insert into foo (bar, baz, buz) values(?,?,?);
$csr = $dbh-prepare($sql);
while (ldap query runs){
@parms =($val1,$val2,$val3);
if ($val1 eq 'A'){ $csr-execute(@parms);}
}
The thing is, this is the second query to the LDAP server in the program, the
first one, using identical DBI code, works.
The only difference is the LDAP query.
On Feb 18, 2011, at 1:10 PM, Furst, Carl wrote:
Are you calling $csr-finish at the end of each iteration? Might not be the
issue but indeed good practice.
Carl Furst
o/~ What a difference a byte makes... o/~
-Original Message-
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu]
Sent: Friday, February 18, 2011 3:07 PM
Cc: DBI Users List (dbi-users@perl.org)
Subject: Re: One of us can't count
No, this is done as:
$csr=$dbh-prepare($sql)
then as $csr-execute(@parms) inside of a loop.
On Feb 18, 2011, at 12:33 PM, Bill Ward wrote:
You're probably calling do($sql, @args) when you should call do($sql,
undef,
@args)
On Fri, Feb 18, 2011 at 11:31 AM, Bruce Johnson
john...@pharmacy.arizona.edu wrote:
I'm getting the following error: (some data has been change to protect
the
bystanders.)
DBD::Oracle::st execute failed: called with 18 bind variables when 19 are
needed [for Statement insert into edsbase
(cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate
,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,emplo
yeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip
)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL
OF
PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411',
:p16='PO
BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='1',
:p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S',
:p9='wsz'] at ./edsload.pl line 97, DATA line 532.
I've counted three times: I have 19 fields in the db, 19 '?'s, and 19
param
values being passed, why am I getting this error?
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
--
Check out my LEGO blog at http://www.brickpile.com/
View my photos at http://flickr.com/photos/billward/
Follow me at http://twitter.com/williamward
--
Bruce
Re: Does DBD::Oracle support left outer join
On May 21, 2010, at 9:25 AM, White, Richard L wrote: I'm getting an error when I try to use a left outer join in my Perl program. I have not found anything about joins in the documentation of the DBD:Oracle module. I'm using DBD::Oracle v1.18 on Perl v5.8.5 to connect to our Oracle 10.02.0400 dB. make sure the query works in SQLPlus. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: PERL DBI ORACLE DATABASE OUTPUT TO A FILE
On May 11, 2010, at 12:25 PM, tech422 wrote:
Hi,
I am trying to query an Oracle database and output the results to a
file using PERL but its not working. I have tried 2 approaches. 1 uses
bind the other does not. Could you please advise?
This is my usual route:
while (($col1,$col2,$cms_gateway_name,$cms_gateway_ip_address,
$cms_subnet_mask,$cms_subnet_notation,$cms_subnet_name,$cms_vlan_no) =
$sth-fetchrow()){...}
I know that works, I do it all the time.
Silly question, have you tested the query in SQLPlus or some other tool to
confirm that you're getting any rows?
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Stupid Oracle question
If $dbh is my database handle, to roll back the current transaction I do: $dbh-rollback(); right? The DBD::Oracle docs don't explicitly say -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Stupid Oracle question
On May 6, 2010, at 11:34 AM, John Scoles wrote:
Bruce Johnson wrote:
Depends if you have |AutoCommit| on or not and if you DB and DBD
friver can do a rollback.
I've explicitly turned autocommit off, so I can roll back transactions
if an error occurs.
In the old Oraperl syntax it's:
if ($ora_errstr){
print $ora_errstr occurred with $statement;
ora_rollback($dbh);
}
ora_commit($dbh);
I'm redoing some old scripts to use DBI instead, so I'm guessing the
equivalent DBI code is:
if ($ora_errstr){
print $ora_errstr occurred with $statement;
$dbh-rollback();
}
$dbh-commit();
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: How to discard the error in SQL query
On Apr 30, 2010, at 1:26 PM, Parag Kalra wrote:
I am executing simple insert SQL queries in a loop
I have a simple requirement - While insert quieries are being
executed, if
any error occurs it should print that message and move to next
insert sql
query. I am using DBD::Oracle on Unix
Try this instead of using $dbh-do().
(This presumes that $dbh is your working database handle, and the
arrays @name, @address, @email, @custid are the arrays holding your
data to be inserted into the table. $i is the number of rows you're
inserting.)
$sqinsert = Insert into foo (name, address, email, custid)
values(?,?,?,?);
$csr= $dbh-prepare($sqinsert);
if($ora_errstr) {print $ora_errstr happened with $sqinsert;}
#this catches errors in the sql
for ($n=0;$n$i;$n++){
$csr-execute($name[$n],$address[$n],$email[$n],$custid[$n]);
if($ora_errstr) {print $ora_errstr happened with data values
$name[$n],$address[$n],$email[$n],$custid[$n];}
}
I JUST did something similar yesterday dumping some data into our
oracle database.
Heck, even if you don't trap the error with if ($ora_errstr)...
Oracle DBI doesn't stop the script, it just doesn't work, and goes to
the next statement.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Help on using on *nix and Windows
On Apr 20, 2010, at 3:49 AM, John Scoles wrote: Without DBI you are very limited in what you can do. You might want to take a time machine back and give Oraperl a go http://search.cpan.org/~pythian/DBD-Oracle-1.24a/Oraperl.pm Old and unmaintained but at least I think you can run it without dbi Not even this will work without DBI, from the CPAN Oraperl page: Oraperl is an extension to Perl which allows access to Oracle databases. The original oraperl was a Perl 4 binary with Oracle OCI compiled into it. The Perl 5 Oraperl module described here is distributed with DBD::Oracle (a database driver what operates within DBI) and adds an extra layer over DBI method calls. The Oraperl module should only be used to allow existing Perl 4 oraperl scripts to run with minimal changes; any new development should use DBI directly. If the PHB won't allow DBI to be installed, you're SOL. You can try doing what you need with sqlplus scripts and procedures. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: (Fwd) How to loop through a database, row by row, and select and update one row at a time
On Apr 13, 2010, at 1:56 AM, Tim Bunce wrote:
However, when I put any sort of an update command after that, as in:
while ( @xml_content = $sth-fetchrow_array() ) {
$sth = $dbh-prepare(SELECT msgid, xmlcontent FROM messages WHERE
msgid = 1892362);
print Message ID = $msgid\n;
$sth-execute();
$update_cmd = UPDATE messages SET alteredcontent = '$alteredmsg'
WHERE msgid = $msg_id;
$sth = $dbh-do($update_cmd);
}
$sth is the handle to your cursor that you're iterating through. When
you redefine it in the first line, you kill the one you were iterating
through, ergo no more lines.
The proper (Perl, that is, dunno about the SQL...) is to use more than
one cursor:
while ( @xml_content = $sth-fetchrow_array() ) {
$sth2 = $dbh-prepare(SELECT msgid, xmlcontent FROM messages WHERE
msgid = 1892362);
print Message ID = $msgid\n;
$sth2-execute();
[I presume there's some missing steps in here, because otherwise
absolutely nothing has happened]
$update_cmd = UPDATE messages SET alteredcontent = '$alteredmsg'
WHERE msgid = $msg_id;
$dbh-do($update_cmd);
}
Also, doing 'prepare()' like this inside of a loop is horribly
inefficient, and can easily be avoided by doing the prepare statement
outside the look with execution parameters, then put the value of the
parameter in the execute() statement:
$sth2 = $dbh-prepare(SELECT msgid, xmlcontent FROM messages WHERE
msgid = ?);
while ( @xml_content = $sth-fetchrow_array() ) {
print Message ID = $msgid\n;
$sth2-execute(1892362);
This is a LOT faster, from experience.
Also SELECT msgid, xmlcontent FROM messages WHERE msgid = 1892362 is
also mildly inefficient, why are you returning the msgid when you
already know it?
do SELECT xmlcontent FROM messages WHERE msgid = 1892362 instead.
It's not much, but you're saving cycles and memory inside of a loop.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: Installing DBD::mysql on Mac OS 10.6.3 Snow Leopard
On Apr 6, 2010, at 8:14 AM, Bobby wrote: I have MAMP installed and running. Also I don't care about connecting to localhost, is there a way to run the drop table tests ect on a server over the internet? Thanks. If you have MAMP running, the perl in OS X doesn't know about it. So when you run the cpan command to install the DBD-MYSQL it's failing because it can't find MySQL. MAMP runs everything (Perl, MySQL, Apache, PHP, etc) WITHIN the MAMP App. IF you want to run MySQL with the default Perl and apache, etc in OS X, you need to let OS X know where stuff is. The simplest way to get MySQL working properly with the local perl, in my experience, is to use MacPorts http://www.macports.org/ (used to be DarwinPorts), and make sure the /opt/... paths are in your ENV. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
parameter issues in DBD:Oracle
I'm trying the following:
$sqcollision = select reserved_id, reserver, purpose,
to_char(starttime, 'HH:MI'), to_char(stoptime, 'HH:MI') from
reservedroomtest where roomid in (?) and ((to_date(?, 'MM/DD/
HH24:MM') = starttime and to_date(?, 'MM/DD/ HH24:MM') =
starttime) or (to_date(?, 'MM/DD/ HH24:MM') = starttime and
to_date(?, 'MM/DD/ HH24:MM') stoptime));
$csr2= $lda-prepare($sqcollision);
$csr2-execute($ridlist, $rstart, $rstop, $rstart, $rstart);
if ( $ora_errstr ) {
print params sent $ridlist; $rstart; $rstop; $rstart; $rstart
p $ora_errstr\n;
exit;
}
Which results in punting with the following error:
params sent 105, 106, 110; 03/23/2010 19:00; 03/23/2010 20:00;
03/23/2010 19:00; 03/23/2010 19:00
ORA-01722: invalid number (DBD ERROR: error possibly near *
indicator at char 135 in 'select reserved_id, reserver, purpose,
to_char(starttime, 'HH:MI'), to_char(stoptime, 'HH:MI') from
reservedroomtest where roomid in (:*p1) and ((to_date(:p2, 'MM/DD/
HH24:MM') = starttime and to_date(:p3, 'MM/DD/ HH24:MM') =
starttime) or (to_date(:p4, 'MM/DD/ HH24:MM') = starttime and
to_date(:p5, 'MM/DD/ HH24:MM') stoptime))')
Does the DBD enclose all parameters in ''s?
Am I actually trying to execute:
select reserved_id, reserver, purpose, to_char(starttime, 'HH:MI'),
to_char(stoptime, 'HH:MI') from reservedroomtest where roomid in
('105, 106, 110') and ((to_date('03/23/2010 19:00', 'MM/DD/
HH24:MM') = starttime and to_date('03/23/2010 20:00', 'MM/DD/
HH24:MM') = starttime) or (to_date('03/23/2010 19:00', 'MM/DD/
HH24:MM') = starttime and to_date('03/23/2010 19:00', 'MM/DD/
HH24:MM') stoptime))
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: parameter issues in DBD:Oracle
On Mar 26, 2010, at 11:01 AM, Bruce Johnson wrote:
Does the DBD enclose all parameters in ''s?
Am I actually trying to execute:
select reserved_id, reserver, purpose, to_char(starttime, 'HH:MI'),
to_char(stoptime, 'HH:MI') from reservedroomtest where roomid in
('105, 106, 110')
To answer my own question, yes it does appear to be that way.
If I put the clause inline in the SQL (as 'in($rlist)' ) rather than
as a parameter (as 'in(?)' ), it works as expected (or at least it
does when I fix my most common datetime conversion error: MM ==
Months, MI== Minutes, yah idjit Johnson!)
Is there a way to pass an unquoted list as a parameter? I can't do it
as 'in(?,?,?,?)' etc, because the number of list elements varies from
execution to execution.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
