Performance Suggestions
Working with an Oracle database, there are several ways to fetch rows from table. In
terms of performance which best? Here is a snippet from an existing script, are there
ways to improve it?
...
my $sql = qq { SELECT a.duns_num, b.name, c.last_upd, c.start_dt, c.end_dt FROM
$schema.s_org_ext a, $schema.s_org_ext b,
$schema.s_quote_soln c
WHERE c.serv_accnt_id = b.row_id AND c.inv_accnt_id = a.row_id AND c.action_cd =
'EnergyServices'
AND c.last_upd >= ? AND c.last_upd < ? $id
ORDER BY c.start_dt};
my $sth = $dbh->prepare($sql);
my $rc = $sth->execute($strt,$end);
my $aref = $sth->fetchall_arrayref({});
foreach (@$aref) {
$$_{STARTTIME} = $$_{START_DT};
$$_{STOPTIME} = $$_{END_DT};
$$_{STATUS} = 'A';
}
return($aref);
Would an ref cursor be better?
Thanks.
DBI DB2 - No more handles
Help Tim!!! I'm been backwards, forwards, and all over this code. It is, admittedly, processing millions of records, and the code is 3808 lines long. But I am _convinced_ that I Connect to the various database connections at the beginning of execution, discconect at the end, and in-between cycle through a process of Prepare, Execute, Fetch or Commit and Finish over and over of course. Is there something I can call to clear used statement handles? I am using Redhat 9 and DB2 Version 8 with the latest fixpacks all properly bound up and everything. perl-5.8.0-88. Perl Client and DB2 server are two different machines (Same OS config). Both equalised. After approxiamately 50,000 iterations, it dies with the below... prepare SQL statement: [IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 [EMAIL PROTECTED] db2 "? CLI0129E" CLI0129E No more handles. Explanation: A call was made to SQLAllocEnv, SQLAllocConnect, or SQLAllocStmt but no more handles are available in the driver. User Response: Call SQLFreeEnv, SQLFreeConnect, or SQLFreeStmt to free up some handles which are no longer being used in the application.
Re: Apache/Perl/Oracle9i
Kevin Moore wrote: > I'm attempting to use perl DBI/DBD in a .cgi script to access an > oracle > database. I can run the script using perl file.cgi successfully but > when > I attempt to execute the same cgi script through apache I receive the > following: > >> Software error: >> >> install_driver(Oracle) failed: Can't load >> '/usr/lib/perl5/site_perl/5.6.1/i386-linux/auto/DBD/Oracle/Oracle.so' >> for module DBD::Oracle: libclntsh.so.8.0: cannot open shared object ^^^ It _looks_ like you have a DBD::Oracle compiled against Oracle 8i libraries, your local environment is set to an 8i ORACLE_HOME (so it works) but your Apache server either is missing the required paths (ORACLE_HOME, LD_LIBRARY_PATH) or it's pointing to a 9i home (since you mention 9i in the subject). -- Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk) Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Apache/Perl/Oracle9i
Hi All I'm attempting to use perl DBI/DBD in a .cgi script to access an oracle database. I can run the script using perl file.cgi successfully but when I attempt to execute the same cgi script through apache I receive the following: Software error: install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.6.1/i386-linux/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.8.0: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.6.1/i386-linux/DynaLoader.pm line 206. at (eval 4) line 3 Compilation failed in require at (eval 4) line 3. Perhaps a required shared library or dll isn't installed where expected at /home/httpd/cgi-bin/signup.cgi line 32 Are there environment variables I need to set to find the path to this file, if so, where can I find a list of these variables. The install of DBI and DBD were successful. Also, the oracle files/shared library files are indeed where they are supposed to be. Thanks for any help. Kevin
Re: DBD-Oracle-1.14
Wang, Yuting wrote: > Hi, is DBD-Oracle-1.14 the latest oracle driver? Yes. > is it compatible with Oracle9i? Yes. -- Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk) Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
error with glibc connecting to mysql database using dbi
Hi,
I am getting this following error in my redhat 9.0 linux box when i am
trying to connect to the mysql server using the dbi.
==
install_driver(mysql) failed: Can't load
'/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBD/mysql/mysql.so'
for module DBD::mysql:
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBD/mysql/mysql.so:
symbol errno, version GLIBC_2.0 not defined in file libc.so.6 with link
time reference at
/usr/lib/perl5/5.8.0/i386-linux-thread-multi/DynaLoader.pm line 229.
at (eval 2) line 3
Compilation failed in require at (eval 2) line 3.
Perhaps a required shared library or dll isn't installed where expected
=
This is the script that i have used for test purpose
#!/usr/bin/perl -w
use strict;
use DBI;
my @arr = DBI->available_drivers();
foreach (@arr) {
print $_,"\n";
next if $_ =~ /proxy/i;
my @datasources = DBI->data_sources($_);
foreach my $val(@datasources) {
print "\tData Source is $val\n";
}
print "\n";
}
exit;
===
There were no error or problem before my upgradation to redhat 9.0 from
8.0. Here are the softwares and their corresponding version that i have
in my system.
MySQL-devel-3.23.55-1
MySQL-3.23.55-1
MySQL-client-3.23.55-1
MySQL-shared-3.23.55-1
MySQL-Max-3.23.55-1
glibc-2.3.2-27.9
perl-DBI-1.32-5
I don't know exactly the DBD::mysql version installed in my system. But
the driver is present as i am getting the man pages wiht 'perldoc
DBD::mysql and the driver name as available drivers.
Any idea how to solve this problem. And why this happening.
Thanks in advance.
-siddhartha
Re: SQL statement to find and delete double entries
--- "Morrison, Trevor (Trevor)" <[EMAIL PROTECTED]> wrote: > What would be an SQL statement that will find duplicate order numbers in > table and then delete them? Try a google statement, like "sql delete duplicate rows" __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
RE: Double entries
Hello,
Have u check the @new_orders used in the foreach where u execute
The insert
Don't u have a primary key causing an error for duplicate entry ???
It's seems that u've got a db design problem too ...
Regards,
Didier Degey
System & Network Administrator
TI Automotive Belgium
http://www.tiautomotive.com
http://www.entreprises-wallonnes.com/tiautomotive
> -Original Message-
> From: Morrison, Trevor (Trevor) [mailto:[EMAIL PROTECTED]
> Sent: samedi 16 août 2003 17:50
> To: [EMAIL PROTECTED]
> Subject: Double entries
>
>
> Hi,
>
> I am running the latest MySql on a Windows 2000 machine. I
> also use the
> control center gui to do all of my work. I have a Perl program that
> parses online order data and then using Perl's DBI, I write
> this data to the
> database. Now, It all works fine except that each order and
> that orders' items
> are written twice to the database. I have looked over my
> code but only see
> one insert statement for the order information another for
> the ordered items. I guess I
> need a fresh set of eyes to see where my error is!
>
> I have included the code below with the database connection
> information
> highlighted in red:
>
>
> TIA
>
> Trevor
>
> #!/usr/local/bin/perl
>
> #
> # Main script
> #
> use strict;
> use MivaOrder;
> use Data::Dumper;
>
>
> #Use Perl's Database Interface (DBI) with the NySQL module to
> connect the
> Maverick database
> use DBI;
> my %attr = (PrintError => 1, RaiseError => 1);
> my $dbname='maverick';
> my $dbuser='root';
> my $dbpass='';
> my
> $dbh=DBI->connect('DBI:mysql:database=maverick;host=localhost;
> port=3306',"$d
> buser","$dbpass", \%attr) || die "Unable to connect to
> database maverick on
> localhost: $DBI::errstr\n";
>
> my @new_orders;
> #my $email = qw(c:\\maverick\\trevor_trial2.txt);
> open(ORDER,$ARGV[0]) or die "Error opening \"$ARGV[0]\": $!\n";
>
> my $order = undef;
> while() {
>
> # Keep appending to the order string until we reach the end
> of the order.
> unless(/^Site rating\.\.\: \"/) {
> $order .= $_ and next;
> }
>
> # We are done with the order.
>
> my $obj = MivaOrder->new($order);
> #print Dumper(\$obj);
> push @new_orders,$obj;
> $order = undef;
> }
>
> my $sth1 =$dbh->prepare("INSERT INTO
> miva_retail_orders(wholesale_company,order_number,date,credit_
> card_type,ship
> ped,shiptype,bill_name,bill_company,bill_addr1,bill_addr2,bill
> _city,bill_sta
> te,bill_zip,bill_country,ship_name,ship_addr1,ship_addr2,ship_
> city,ship_stat
> e,ship_zip,ship_country,email,bill_phone_number,bill_phone_num
> ber2,ship_phon
> e_number,ship_phone_number2,sales_tax,shipping_amount,coupon_t
> ype,coupon_amo
> unt,total) VALUES
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
> foreach my $new_order(@new_orders) {
>
> # Enter the database and write the data
> $sth1->execute($new_order->wholesale_company(),$new_order->ord
> er_number(),$n
> ew_order->date(),$new_order->credit_card_type(),$new_order->sh
> ipped(),$new_o
> rder->ship_type(),$new_order->bill_name(),$new_order->bill_com
> pany_name(),$n
> ew_order->bill_to_street(),$new_order->bill_to_street2(),$new_
> order->bill_to
> _city(),$new_order->bill_to_state(),$new_order->bill_to_zip(),
> $new_order->bi
> ll_to_country(),$new_order->ship_name(),$new_order->ship_to_st
> reet(),$new_or
> der->ship_to_street2(),$new_order->ship_to_city(),$new_order->
> ship_to_state(
> ),$new_order->ship_to_zip(),$new_order->ship_to_country(),$new
> _order->email_
> address(),$new_order->bill_phone_number(),$new_order->bill_pho
> ne_number2(),$
> new_order->ship_phone_number(),$new_order->ship_phone_number2(
> ),$new_order->
> sales_tax(),$new_order->shipping_amount(),$new_order->coupon_t
> ype(),$new_ord
> er->coupon_amount(),$new_order->total());
>
> }
>
> $sth1->finish;
> $dbh->disconnect;
>
> #==
> # Package MivaOrder
> #--
> package MivaOrder;
> use strict;
> use Carp;
> use English;
> use vars qw($AUTOLOAD);
>
>
> my %fields = (
> wholesale_company =>undef,
> order_number =>undef,
> date =>undef,
>
> bill_name =>undef,
> bill_email_address =>undef,
> bill_phone_number =>undef,
> bill_phone_number2 =>undef,
> bill_business_name =>undef,
> bill_to_street=>undef,
> bill_to_city =>undef,
> bill_to_state =>undef,
> bill_to_zip =>undef,
> bill_to_country =>undef,
>
> ship_name =>undef,
> ship_email_address=>undef,
> ship_phone_number =>undef,
> ship_phone_number2 =>undef,
> ship_business_name =>undef,
> ship_to_street=>undef,
> ship_to_city =>undef,
> ship_to_state =>undef,
>
SQL statement to find and delete double entries
Hi, What would be an SQL statement that will find duplicate order numbers in table and then delete them? TIA Trevor
Double entries
Hi,
I am running the latest MySql on a Windows 2000 machine. I also use the
control center gui to do all of my work. I have a Perl program that
parses online order data and then using Perl's DBI, I write this data to the
database. Now, It all works fine except that each order and that orders' items
are written twice to the database. I have looked over my code but only see
one insert statement for the order information another for the ordered items. I guess
I
need a fresh set of eyes to see where my error is!
I have included the code below with the database connection information
highlighted in red:
TIA
Trevor
#!/usr/local/bin/perl
#
# Main script
#
use strict;
use MivaOrder;
use Data::Dumper;
#Use Perl's Database Interface (DBI) with the NySQL module to connect the
Maverick database
use DBI;
my %attr = (PrintError => 1, RaiseError => 1);
my $dbname='maverick';
my $dbuser='root';
my $dbpass='';
my
$dbh=DBI->connect('DBI:mysql:database=maverick;host=localhost;port=3306',"$d
buser","$dbpass", \%attr) || die"Unable to connect to database maverick on
localhost: $DBI::errstr\n";
my @new_orders;
#my $email = qw(c:\\maverick\\trevor_trial2.txt);
open(ORDER,$ARGV[0]) or die "Error opening \"$ARGV[0]\": $!\n";
my $order = undef;
while() {
# Keep appending to the order string until we reach the end of the order.
unless(/^Site rating\.\.\: \"/) {
$order .= $_ and next;
}
# We are done with the order.
my $obj = MivaOrder->new($order);
#print Dumper(\$obj);
push @new_orders,$obj;
$order = undef;
}
my $sth1 =$dbh->prepare("INSERT INTO
miva_retail_orders(wholesale_company,order_number,date,credit_card_type,ship
ped,shiptype,bill_name,bill_company,bill_addr1,bill_addr2,bill_city,bill_sta
te,bill_zip,bill_country,ship_name,ship_addr1,ship_addr2,ship_city,ship_stat
e,ship_zip,ship_country,email,bill_phone_number,bill_phone_number2,ship_phon
e_number,ship_phone_number2,sales_tax,shipping_amount,coupon_type,coupon_amo
unt,total) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
foreach my $new_order(@new_orders) {
# Enter the database and write the data
$sth1->execute($new_order->wholesale_company(),$new_order->order_number(),$n
ew_order->date(),$new_order->credit_card_type(),$new_order->shipped(),$new_o
rder->ship_type(),$new_order->bill_name(),$new_order->bill_company_name(),$n
ew_order->bill_to_street(),$new_order->bill_to_street2(),$new_order->bill_to
_city(),$new_order->bill_to_state(),$new_order->bill_to_zip(),$new_order->bi
ll_to_country(),$new_order->ship_name(),$new_order->ship_to_street(),$new_or
der->ship_to_street2(),$new_order->ship_to_city(),$new_order->ship_to_state(
),$new_order->ship_to_zip(),$new_order->ship_to_country(),$new_order->email_
address(),$new_order->bill_phone_number(),$new_order->bill_phone_number2(),$
new_order->ship_phone_number(),$new_order->ship_phone_number2(),$new_order->
sales_tax(),$new_order->shipping_amount(),$new_order->coupon_type(),$new_ord
er->coupon_amount(),$new_order->total());
}
$sth1->finish;
$dbh->disconnect;
#==
# Package MivaOrder
#--
package MivaOrder;
use strict;
use Carp;
use English;
use vars qw($AUTOLOAD);
my %fields = (
wholesale_company =>undef,
order_number=>undef,
date=>undef,
bill_name =>undef,
bill_email_address =>undef,
bill_phone_number =>undef,
bill_phone_number2 =>undef,
bill_business_name =>undef,
bill_to_street =>undef,
bill_to_city=>undef,
bill_to_state =>undef,
bill_to_zip =>undef,
bill_to_country =>undef,
ship_name =>undef,
ship_email_address =>undef,
ship_phone_number =>undef,
ship_phone_number2 =>undef,
ship_business_name =>undef,
ship_to_street =>undef,
ship_to_city=>undef,
ship_to_state =>undef,
ship_to_zip =>undef,
ship_to_country =>undef,
code=>undef,
name=>undef,
quantity=>undef,
price =>undef,
shipping_method =>undef,
shipping_amount =>undef,
sales_tax =>undef,
total =>undef,
notes =>undef,
coupon_type =>undef,
coupon_amount =>undef,
credit_card_type=>undef,
COD
=>undef
);
sub AUTOLOAD {
my ($self,$value) = @_;
$AUTOLOAD =~ /.*::(\w+)/;
$self->{$1} = $value if($value);
return
Problems using DBD::Oracle 1.14, Returning an Oracle 9i RECORD datatype
Is it possible to return a RECORD datatype from Oracle 9i using
DBD::Oracle 1.14?
I've searched the archives and documentation and can find no mention of
not being able to
yet I can't get it to work.
I have a package with the following function
TYPE User_Profile IS RECORD (
sid SYS.V_$SESSION.SID%TYPE,
login SYS.V_$SESSION.USERNAME%TYPE,
role DISPATCHERS.ROLE_ID%TYPE
);
FUNCTION UserInfo RETURN User_Profile;
It works fine in PL/SQL but when I use the following perl v5.8.0
script with DBD::Oracle 1.14
==
#!/usr/local/bin/perl
#
# Given an adnum and address return a list of customer that match
#
use DBI;
use DBD::Oracle qw(:ora_types);
use DBD::Oracle qw(:ora_session_modes);
my ($dbh,$sth, @row);
#Connect to the oms database
$dbh = DBI->connect("dbi:Oracle:oms","oms","oms", {PrintError => 1} )
or die "Can't connect to Oracle:oms: $DBI::errstr\n";
$adnum="6015";
$adnum_quoted=$dbh->quote("%$adnum%");
$adstreet="M";
$adstreet_quoted=$dbh->quote("%$adstreet%");
$gsn='153827417400';
$gsn_quoted=$dbh->quote($gsn);
#$sql = "Select * from customer where adnum like $adnum_quoted and
upper(adstreet) like upper($adstreet_quoted)";
$sql = q{ BEGIN
:return := oms_pkg.UserInfo;
END; };
print "$sql\n";
$sth = $dbh->prepare($sql) or die "Can't prepare statement:$sql: ",
$dbh->erstr(), "\n";
my $sth2;
$sth->bind_param_inout(":return", \$sth2, 0, { ora_type => ORA_RSET }
);
$sth->execute();
#$row=$sth->dump_results();
#while ( $row_ref = $sth->fetchrow_hashref('NAME_lc') ) {
# print
"$row_ref->{namelast},$row_ref->{adnum},$row_ref->{adstreet}\n";
#}
while ( $row_ref = $sth->fetchrow_arrayref ) {
push @row, [ @$row_ref ];
}
# Print the array
foreach $row_ref ( @row ) {
foreach $row_ref ( @row ) {
print "@$row_ref\n";
}
#Be correct and disconnect
$dbh->disconnect or warn "Disconnection failed:
$DBI::errstr\n";
exit;
==
I works fine if I return an array of varchar2 as with the select * from
customers statement.
But with a function returning a RECORD datatype I get the following
error
$ ./FindCustomer.prl
BEGIN
:return := oms_pkg.UserInfo;
END;
DBD::Oracle::st execute failed: ORA-06550: line 2, column 24:
PLS-00382: expression is of wrong type
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute) [for statement ``
BEGIN
:return := oms_pkg.UserInfo;
END; '' with params:
:return=DBI::st=HASH(0x30c8c)]) at ./FindCustomer.prl line 38.
DBD::Oracle::st fetchrow_arrayref failed: ERROR no statement executing
(perhaps you need to call execute first) [for statement `` BEGIN
:return := oms_pkg.UserInfo;
END; '' with params:
:return=DBI::st=HASH(0x30c8c)]) at ./FindCustomer.prl line 47.
$
Any ideas,
Thanks,
Steve
[EMAIL PROTECTED]
Chugach Electric Association, Inc
5601 Minnesota Drive
Anchorage, Alaska 99519
907-762-4830
