RE: Re: FAQ-O-Matic for DBI FAQ(Please submit FAQs for a patch)
Maybe there is another Scott that was going to set up FAQ-O-Matic, I don't know anything about that, I was just going to send a "Redbrick-ODBC" Faq. On 09-Apr-2002 Sterin, Ilya wrote: > Ah, yes. I'm confused, not enough sleep lately:-) Sorry. > > Still please let me know when you plan on setting up the FAQ-O-Matic. > > Ilya > > -Original Message- > From: Scott T. Hildreth > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Sent: 4/9/02 9:32 AM > Subject: FWD: Re: FAQ-O-Matic for DBI FAQ(Please submit FAQs for a patch) > > > Ilya, > I think you have me confused with this person. > >>Scott, thanks for the perlmonks link to db questions as I got quite a >>few FAQs from there. > > -FW: <[EMAIL PROTECTED]>- > > Date: Mon, 08 Apr 2002 11:17:00 -0400 (EDT) > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: Re: FAQ-O-Matic for DBI FAQ(Please submit FAQs for a patch) > > Sorry I read my email only after the weekend, I hope better now than > never. > > Excellent idea to have web-accesible FAQ. > Link to this FAQ site should be added to each email > sent by [EMAIL PROTECTED] manager, IMHO. > > PerlMonks site (www.permonks.com) has bunch of DBI FAQ at > http://www.perlmonks.com/index.pl?node_id=1831. > Maybe link there coud be added? > > In my work we use excellent (perl open-source) wiki clone, TWiki > (http://twiki.org). It has user authentication, > access control based on user groups, full version control, > site search, and as in each wiki, it's very easy to link between pages. > > Comparing with FAQ-O-Matic, it will allow > -- correct/comment pages of others, > -- ability to see who added what, > -- possibly to revert to previous version, > -- move pages beween subcategories (TWiki call it "web") > -- fine-grained categorisation, where each page >can belong to multiple categories (Using TWikiForms) > -- embedded search system > -- email notification if any page changed (by special subscription) > > TWiki is easy to install on Linux/Unix, I can help/answer questions > (I did install it for us). TWiki really rocks! > > Peter Masiar, [EMAIL PROTECTED] > > - > Quoting "Sterin, Ilya" <[EMAIL PROTECTED]>: > >> To make it easier to maintain while we are going through the rewrite > of >> the DBI FAQ, I've installed and configured FAQ-O-Matic to help us >> through this process. > . >> For those willing to contribute, please send me your email address >> you'd >> like to use as a username and a prefered password. I'll set you up > and >> you'll be able to add and update items. (you can only update item's >> you've added, so any other changes will have to be made by me). > > --End of forwarded message- > > -- > E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> > Date: 09-Apr-2002 > Time: 09:29:38 > -- -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 09-Apr-2002 Time: 09:39:49 --
Question about Perl DBI DBD's
Any DBD for Oracle 9i Scott Newman SBC Internet Services, Network Operations Tools Development 1701 Alma Dr. Room 1S-141, Plano, TX 75075 phone: 214-495-2521 pager: 888-270-9719mobile: 214-478-9316 fax: 214-495-2253 ICQ#:135226947Current ICQ status:
Re: DBI vs. piping query to Mysql
At 04:28 PM 4/3/02 -0500, Kevin Old wrote:
>Hello all,
>
>I am a consultant brought in to manage and restructure some Perl scripts that
>were written some time ago. The programmer at that time was using the
>following code to do a query from within a CGI page.
>
> ${query} = "SELECT ccyymmddhh FROM inventory ORDER BY ccyymmddhh ;" ;
> open( INPUT, "echo \"${query}\" |
>/usr/local/mysql/bin/mysql -A -q -N gso|" ) ;
> @{ccyymmddhh} = ;
> chomp( @{ccyymmddhh} ) ;
> close( INPUT ) ;
*Shudder*
>I think that I should clean this up and reprogram this to use DBD::mysql
>rather than the way he does it here.
That's an understatement.
>Anyone have any idea if it would improve performance?
The only way to be sure is to try both ways, but... I would bet long odds
that the performance will be greatly improved. The above has to fire off a
subprocess and build up and tear down a connection for each query. If the
DBI way turns out to be slower, look me up at the Perl Conference and I'll
buy you a drink. So, I suspect, will Tim Bunce :-)
>I'd love to hear from people that have gone doing it this way to using DBI.
>
>Obviously I can run benchmarks before and after and see which takes longer,
>and I think that using DBI is not only much easier to read and manage, but
>probably a little faster. Just seeking the advice of others.
Go with easier to read and manage first. The above code is NOT capable of
being reused in obvious ways (suppose $query contained quote marks or shell
metacharacters).
--
Peter Scott
Pacific Systems Design Technologies
http://www.perldebugged.com
RE: UPDATE Statement Problem...
IT'S ALIVE
I would swear that I had tried this before, but it now works. Below
is the UPDATE line that works. I may, no must, be an idiot. I would
honestly swear that I did this at least twice before, but here it is
working.
$dbh->do(qq{UPDATE systems SET $set WHERE Name = '$name'});
I must say that this is really SWEET. I have been fighting with
this issue for a lot longer than just this morning. Anyway, thanks. Now on
to my next problem. I will try to straighten this one out without having to
bug you guys again.
-Original Message-
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 11:47 AM
To: 'Michael Ragsdale'; '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
OK. Sorry for my being stupid. Everyone keeps telling me the same
thing, and I must be doing something wrong here. Let me try to make clear
how I am handling the SET parameter.
The SET parameter ($set) is actually built earlier in the Perl
script. I scavenged this portion of the code from a book that I picked up.
I am including this portion of the code below, and will briefly explain how
I think it works. I am doing this so that hopefully someone can tell me
that it does or does not work.
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
OK. The above code tests each field value from the db against the
corresponding value from the text file being processed. If the values do
NOT match, then the hash key, which is the column name, and the value are
put together and "built" into the $set variable. This results in the $set
variable holding both the column name and the value in a form such as,
'col_name1 = expr1'.
I have printed out the SQL statement resulting from this and used it
to successfully UPDATE the db from the MySQL Console. This is why I keep
"skirting" the issue of the SET parameter in my UPDATE statement. Now, if I
am fundamentally flawed in my logic (my wife would definitely say this is
the case) and this simply will NOT work, please let me know.
Once again, I really appreciate all of the assistance. I also
understand that if I were to spend a few days reading, I might have better
luck resolving some of these issues on my own. Unfortunately, I am quite
limited on time, as I am sure everyone is. Thank you all very much for the
help. Please bear with me, and I will do my best to refrain from being
thick headed. :)
-----Original Message-
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 11:15 AM
To: NIPP, SCOTT V (SBCSI); '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 12:01 PM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> New problem now. I appear have resolved my earlier problems. The
>final issue appears to have been that the SQL statement was interpreting
the
>WHERE clause as having multiple arguments. Quoting the where clause seems
>to have resolved the error, but the data still is not making it into the
>database?!?! Below is the "working" UPDATE line:
>
> $dbh->do(qq{UPDATE systems SET $set WHERE 'Name = $name'});
No, not a new problem. You've got the same problem. The data is not
making it into the database because you are not telling the data where to
go! At least two people have commented on your syntax being incorrect and
you keep skirting the issue.
Correct syntax: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1 =
expr1, ... [WHERE where_definition] [LIMIT #]
Where is your 'col_name = ' part of the statement? It will not work
without it! You'll want to remove those quotes that you placed around the
where clause as well because once you fix the 'col_name = ' part of the
syntax, then the quotes are likely to create more problems. Follow the
syntax rules - don't make up your own.
-Mike
RE: UPDATE Statement Problem...
OK. Sorry for my being stupid. Everyone keeps telling me the same
thing, and I must be doing something wrong here. Let me try to make clear
how I am handling the SET parameter.
The SET parameter ($set) is actually built earlier in the Perl
script. I scavenged this portion of the code from a book that I picked up.
I am including this portion of the code below, and will briefly explain how
I think it works. I am doing this so that hopefully someone can tell me
that it does or does not work.
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
OK. The above code tests each field value from the db against the
corresponding value from the text file being processed. If the values do
NOT match, then the hash key, which is the column name, and the value are
put together and "built" into the $set variable. This results in the $set
variable holding both the column name and the value in a form such as,
'col_name1 = expr1'.
I have printed out the SQL statement resulting from this and used it
to successfully UPDATE the db from the MySQL Console. This is why I keep
"skirting" the issue of the SET parameter in my UPDATE statement. Now, if I
am fundamentally flawed in my logic (my wife would definitely say this is
the case) and this simply will NOT work, please let me know.
Once again, I really appreciate all of the assistance. I also
understand that if I were to spend a few days reading, I might have better
luck resolving some of these issues on my own. Unfortunately, I am quite
limited on time, as I am sure everyone is. Thank you all very much for the
help. Please bear with me, and I will do my best to refrain from being
thick headed. :)
-Original Message-
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 11:15 AM
To: NIPP, SCOTT V (SBCSI); '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 12:01 PM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> New problem now. I appear have resolved my earlier problems. The
>final issue appears to have been that the SQL statement was interpreting
the
>WHERE clause as having multiple arguments. Quoting the where clause seems
>to have resolved the error, but the data still is not making it into the
>database?!?! Below is the "working" UPDATE line:
>
> $dbh->do(qq{UPDATE systems SET $set WHERE 'Name = $name'});
No, not a new problem. You've got the same problem. The data is not
making it into the database because you are not telling the data where to
go! At least two people have commented on your syntax being incorrect and
you keep skirting the issue.
Correct syntax: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1 =
expr1, ... [WHERE where_definition] [LIMIT #]
Where is your 'col_name = ' part of the statement? It will not work
without it! You'll want to remove those quotes that you placed around the
where clause as well because once you fix the 'col_name = ' part of the
syntax, then the quotes are likely to create more problems. Follow the
syntax rules - don't make up your own.
-Mike
RE: UPDATE Statement Problem...
New problem now. I appear have resolved my earlier problems. The
final issue appears to have been that the SQL statement was interpreting the
WHERE clause as having multiple arguments. Quoting the where clause seems
to have resolved the error, but the data still is not making it into the
database?!?! Below is the "working" UPDATE line:
$dbh->do(qq{UPDATE systems SET $set WHERE 'Name = $name'});
Not sure what is wrong now though. :(
-Original Message-
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:46 AM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
I actually have that covered, I think. The variable $set actually
expands to something like ip='192.168.0.1' , speed='450' , etc. This
portion is working OK, I think. I can print the SQL statement and then
paste it into the MySQL Console and it UPDATEs OK. Below is the portion of
code I use to generate the $set:
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
Thanks again.
-Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:40 AM
To: NIPP, SCOTT V (SBCSI)
Subject: RE: UPDATE Statement Problem...
THe problem with the update statement is that you need a column name
before the value you are setting it to. EG. UPDATE systems SET col_set
= $set WHERE name = $name;
You are missing the col_set (or whatever the column name is).
Gordon
-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:28 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
A bit more information... Here is the error message from the
Perl
script on the UPDATE failure.
DBD::mysql::db do failed: You have an error in your SQL syntax near
'WHERE
Name ='$name' LIMIT 1' at line 1 at sys_db_update1.pl line 41,
line
81.
-Original Message-
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:20 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
Wow!!! This is one awesome mailing list. I really appreciate
all
of the responses. Several of your suggestions have helped me to narrow
in
on the problem. Here is what I have come up with so far, but still not
quite there.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name
='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
if ($set) {
print "$name found in database. Updating information for
$name.
\n";
print "$set \n";
# print "$dbh->do(q{UPDATE systems SET $set WHERE Name = $name
LIMIT 1})";
$dbh->do(q{UPDATE systems SET $set WHERE Name = $name LIMIT
1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry
for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem',
'$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
I am now looking figuring out how to print out the error message
from the failed SQL statement (told you I am a newbie). Once I get that
part figured out and going, I think I can home in on the problem rather
quickly. Then I will just have to port this portion of code from my
laptop
to my Unix platform.
Thanks again for all of the helpful suggestions. Almost
everything
you guys have responded with so far has helped to push me along in the
correct direction.
-Original Message-
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:03 AM
To: NIPP, SCOTT V (SBCSI); 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 10:50 AM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> I added a COMMIT immediately after the UPDATE, and still have
the
>same problem. Below is exactly what I added, with the lines
immediately
>before and after.
>
> $dbh->do(q{UPDATE systems SET = $set WHERE Name = $name
LIMIT
1});
q{} does not interpolate your scalars. Try qq{}
-Mike
RE: UPDATE Statement Problem...
I actually have that covered, I think. The variable $set actually
expands to something like ip='192.168.0.1' , speed='450' , etc. This
portion is working OK, I think. I can print the SQL statement and then
paste it into the MySQL Console and it UPDATEs OK. Below is the portion of
code I use to generate the $set:
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
Thanks again.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:40 AM
To: NIPP, SCOTT V (SBCSI)
Subject: RE: UPDATE Statement Problem...
THe problem with the update statement is that you need a column name
before the value you are setting it to. EG. UPDATE systems SET col_set
= $set WHERE name = $name;
You are missing the col_set (or whatever the column name is).
Gordon
-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:28 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
A bit more information... Here is the error message from the
Perl
script on the UPDATE failure.
DBD::mysql::db do failed: You have an error in your SQL syntax near
'WHERE
Name ='$name' LIMIT 1' at line 1 at sys_db_update1.pl line 41,
line
81.
-Original Message-
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:20 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
Wow!!! This is one awesome mailing list. I really appreciate
all
of the responses. Several of your suggestions have helped me to narrow
in
on the problem. Here is what I have come up with so far, but still not
quite there.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name
='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
if ($set) {
print "$name found in database. Updating information for
$name.
\n";
print "$set \n";
# print "$dbh->do(q{UPDATE systems SET $set WHERE Name = $name
LIMIT 1})";
$dbh->do(q{UPDATE systems SET $set WHERE Name = $name LIMIT
1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry
for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem',
'$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
I am now looking figuring out how to print out the error message
from the failed SQL statement (told you I am a newbie). Once I get that
part figured out and going, I think I can home in on the problem rather
quickly. Then I will just have to port this portion of code from my
laptop
to my Unix platform.
Thanks again for all of the helpful suggestions. Almost
everything
you guys have responded with so far has helped to push me along in the
correct direction.
-Original Message-
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:03 AM
To: NIPP, SCOTT V (SBCSI); 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 10:50 AM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> I added a COMMIT immediately after the UPDATE, and still have
the
>same problem. Below is exactly what I added, with the lines
immediately
>before and after.
>
> $dbh->do(q{UPDATE systems SET = $set WHERE Name = $name
LIMIT
1});
q{} does not interpolate your scalars. Try qq{}
-Mike
RE: UPDATE Statement Problem...
Following another suggestion, I tried using the 'qq' quoting method
and received the following error.
DBD::mysql::db do failed: Unknown column 'harry' in 'where clause' at
sys_db_update1.pl line 41, line 81.
-Original Message-
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:28 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
A bit more information... Here is the error message from the Perl
script on the UPDATE failure.
DBD::mysql::db do failed: You have an error in your SQL syntax near 'WHERE
Name ='$name' LIMIT 1' at line 1 at sys_db_update1.pl line 41, line
81.
-Original Message-
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:20 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
Wow!!! This is one awesome mailing list. I really appreciate all
of the responses. Several of your suggestions have helped me to narrow in
on the problem. Here is what I have come up with so far, but still not
quite there.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name ='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
if ($set) {
print "$name found in database. Updating information for $name.
\n";
print "$set \n";
# print "$dbh->do(q{UPDATE systems SET $set WHERE Name = $name
LIMIT 1})";
$dbh->do(q{UPDATE systems SET $set WHERE Name = $name LIMIT 1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem','$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
I am now looking figuring out how to print out the error message
from the failed SQL statement (told you I am a newbie). Once I get that
part figured out and going, I think I can home in on the problem rather
quickly. Then I will just have to port this portion of code from my laptop
to my Unix platform.
Thanks again for all of the helpful suggestions. Almost everything
you guys have responded with so far has helped to push me along in the
correct direction.
-Original Message-
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:03 AM
To: NIPP, SCOTT V (SBCSI); 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 10:50 AM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> I added a COMMIT immediately after the UPDATE, and still have the
>same problem. Below is exactly what I added, with the lines immediately
>before and after.
>
> $dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT
1});
q{} does not interpolate your scalars. Try qq{}
-Mike
RE: UPDATE Statement Problem...
A bit more information... Here is the error message from the Perl
script on the UPDATE failure.
DBD::mysql::db do failed: You have an error in your SQL syntax near 'WHERE
Name ='$name' LIMIT 1' at line 1 at sys_db_update1.pl line 41, line
81.
-Original Message-----
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:20 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
Wow!!! This is one awesome mailing list. I really appreciate all
of the responses. Several of your suggestions have helped me to narrow in
on the problem. Here is what I have come up with so far, but still not
quite there.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name ='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
if ($set) {
print "$name found in database. Updating information for $name.
\n";
print "$set \n";
# print "$dbh->do(q{UPDATE systems SET $set WHERE Name = $name
LIMIT 1})";
$dbh->do(q{UPDATE systems SET $set WHERE Name = $name LIMIT 1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem','$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
I am now looking figuring out how to print out the error message
from the failed SQL statement (told you I am a newbie). Once I get that
part figured out and going, I think I can home in on the problem rather
quickly. Then I will just have to port this portion of code from my laptop
to my Unix platform.
Thanks again for all of the helpful suggestions. Almost everything
you guys have responded with so far has helped to push me along in the
correct direction.
-Original Message-
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:03 AM
To: NIPP, SCOTT V (SBCSI); 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 10:50 AM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> I added a COMMIT immediately after the UPDATE, and still have the
>same problem. Below is exactly what I added, with the lines immediately
>before and after.
>
> $dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT
1});
q{} does not interpolate your scalars. Try qq{}
-Mike
RE: UPDATE Statement Problem...
Wow!!! This is one awesome mailing list. I really appreciate all
of the responses. Several of your suggestions have helped me to narrow in
on the problem. Here is what I have come up with so far, but still not
quite there.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name ='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=\'$$key\'";
}
}
if ($set) {
print "$name found in database. Updating information for $name.
\n";
print "$set \n";
# print "$dbh->do(q{UPDATE systems SET $set WHERE Name = $name
LIMIT 1})";
$dbh->do(q{UPDATE systems SET $set WHERE Name = $name LIMIT 1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem','$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
I am now looking figuring out how to print out the error message
from the failed SQL statement (told you I am a newbie). Once I get that
part figured out and going, I think I can home in on the problem rather
quickly. Then I will just have to port this portion of code from my laptop
to my Unix platform.
Thanks again for all of the helpful suggestions. Almost everything
you guys have responded with so far has helped to push me along in the
correct direction.
-Original Message-
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:03 AM
To: NIPP, SCOTT V (SBCSI); 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 10:50 AM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> I added a COMMIT immediately after the UPDATE, and still have the
>same problem. Below is exactly what I added, with the lines immediately
>before and after.
>
> $dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT
1});
q{} does not interpolate your scalars. Try qq{}
-Mike
RE: UPDATE Statement Problem...
I added a COMMIT immediately after the UPDATE, and still have the
same problem. Below is exactly what I added, with the lines immediately
before and after.
$dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT 1});
$dbh->do("COMMIT"); #Just added!!!
} else {
-Original Message-
From: Tielman J de Villiers [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 9:39 AM
To: NIPP, SCOTT V (SBCSI)
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
Check how you connect -- if Autocommit is not on, then you need to commit
after you update
Tielman J de Villiers
BondNet Pty Ltd
-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 5:40 PM
To: '[EMAIL PROTECTED]'
Subject: UPDATE Statement Problem...
Hey guys. I am still quite new to the database world and obviously
in need of help. Not sure if I am just stupid, or if there is really very
little information to be found concerning UPDATE statements. I am working
on a MySQL database with Perl 5.6.0 and the latest DBI version. I am doing
fine with connectivity, in that I have INSERT and SELECT statements working
fine. My problem is that an UPDATE statement that I have been working with
is NOT working. Here is, I hope, the relevant portion of the code.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name ='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=$$key";
}
}
if ($set) {
print "$name found in database. Updating information for $name.
\n";
print "$set \n";
$dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT 1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem','$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
This database stores system information. I have written Perl
scripts to collect all of this information from the systems, format the
output, and FTP it over to the database server. I have no problem INSERTing
new systems into the database, the problem I have is UPDATEing existing
systems. I know that I could simply DELETE and then INSERT the system again
with all of the new information, but this seems a very inelegant way of
handling this. Any help would be GREATLY appreciated.
Scott Nipp
Systems Analyst
SBC Long Distance
(214) 858-1289
UPDATE Statement Problem...
Hey guys. I am still quite new to the database world and obviously
in need of help. Not sure if I am just stupid, or if there is really very
little information to be found concerning UPDATE statements. I am working
on a MySQL database with Perl 5.6.0 and the latest DBI version. I am doing
fine with connectivity, in that I have INSERT and SELECT statements working
fine. My problem is that an UPDATE statement that I have been working with
is NOT working. Here is, I hope, the relevant portion of the code.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name ='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=$$key";
}
}
if ($set) {
print "$name found in database. Updating information for $name.
\n";
print "$set \n";
$dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT 1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem','$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
This database stores system information. I have written Perl
scripts to collect all of this information from the systems, format the
output, and FTP it over to the database server. I have no problem INSERTing
new systems into the database, the problem I have is UPDATEing existing
systems. I know that I could simply DELETE and then INSERT the system again
with all of the new information, but this seems a very inelegant way of
handling this. Any help would be GREATLY appreciated.
Scott Nipp
Systems Analyst
SBC Long Distance
(214) 858-1289
RE: Oracle 8i client for Mac OS X
perldoc DBD::Proxy On 26-Feb-02 Chuck Tomasi wrote: > I also saw on a press release from Oracle (posted to comp.sys.mac.something) > that the Oracle libraries were built in to OS X server. Is it possible to > use the libraries from server on a desktop workstation? > > Are there any other creative alternatives to getting an DBI app talking to > an Oracle server? > > --Chuck > >> -Original Message- >> From: Dan Horne [mailto:[EMAIL PROTECTED]] >> Sent: Monday, February 25, 2002 8:05 PM >> To: Chuck Tomasi; [EMAIL PROTECTED] >> Subject: RE: Oracle 8i client for Mac OS X >> >> >> According to metalink.oracle.com, there is no 8i client >> support for MAc OS X >> (as at Jan 2002) >> >> Dan >> >> -Original Message- >> From: Chuck Tomasi [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, February 26, 2002 8:33 AM >> To: '[EMAIL PROTECTED]' >> Subject: Oracle 8i client for Mac OS X >> >> >> Does anyone know if there is an Oracle 8i client available >> for Mac OS X? >> This is the only piece I'm missing to move my Perl project >> development on to >> my laptop. I did some quick checking in other places, but >> came up empty. I >> see an 8i client for OS 9, but I have a hard time believing >> that OS X isn't >> out there somewhere. >> >> --Chuck Tomasi >> --Corporate Systems Administrator >> --Plexus Corp. - Neenah WI >> --(920)751-3327 >> >> >> -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 26-Feb-02 Time: 10:36:36 --
Re: time difference
There are a number of cpan tools that will do that - you just basically need anything that will do julian or other time format conversion. I have seen others use Time::Calc and I tend to prefer Time::Manip. Scott --- Charitha <[EMAIL PROTECTED]> wrote: > > > Hello all, > > Can i find the difference between two times in perl programming > Please do help me > it is urgent > > -- > Regards, > Charitha. > __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com
DBI install on Irix
>cc ERROR: cannot exec /usr/lib32/cmplrs/fec You probably need the C Front-end, versions" should show both c_dev and c_fe Its part of the os release, you've just got to find the right cd I c_dev08/17/98 C Headers and Libraries, 7.2.1 I c_fe 10/01/98 C Front-end, 7.2.1 hth, dave scott iowa state university When you get to the point where you really understand your computer, it's probably obsolete.
Re: Embedding perl in Oracl
On Fri, Feb 08, 2002 at 12:25:45PM -0500, Richard A. Nakroshis wrote:
yes is does, but you can get it at,
ftp://pause.perl.org/pub/PAUSE/authors/id/TIMB/
> Scott,
>
> I saw your message and Pause message too, but wasn't able to find it on
> cpan.org. Does it take a while to appear?
>
> Thanks,
>
> Rick
>
> "Scott T. Hildreth" wrote:
> >
> > Got the Pause message, thanks Tim.
> >
> >STH
> >
> > On 08-Feb-02 Scott T. Hildreth wrote:
> > >
> > > Tim,
> > >
> > > Will you be posting the this talk any time soon?
> > >
> > >
> > > On 27-Dec-01 Tim Bunce wrote:
> > >> I'm putting together a "Using Perl with Oracle" talk (for the Perl
> > >> Whirl '02 GeekCruise conference) and would very much like to hear
> > >> from anyone who's used Jeff's extproc_perl.
> > >>
> > >> The talk, like all my others, will be available from
> > >> http://cpan.valueclick.com/authors/id/TIMB/
> > >> once it's done.
> > >>
> > >> Thanks.
> > >>
> > >> Tim.
> > >>
> > >
> > >> p.s. cross-posted to oracle-l and dbi-users, please reply direct or
> > >> ammend any followups.
> > >>
> > >>
> > >> On Wed, Dec 05, 2001 at 02:22:07AM -0800, Andy Duncan wrote:
> > >>> Hi Tim,
> > >>>
> > >>> > > I've done it, it works. Be aware that setting it up is not trivial,
> > >>> > > as the documentation is somewhat incomplete.
> > >>> > > That is being remedied however...
> > >>> > > http://www.cpan.org/modules/by-authors/Jeff_Horwitz/
> > >>> > http://www.cpan.org/modules/by-authors/Jeff_Horwitz/extproc_perl-0.93.read
> > >>> > m
> > >>> > e
> > >>> > I'll take a look and add that to my Perl Whirl talk.
> > >>>
> > >>> You can also get hold of all of Jeff's other work, including the latest
> > >>> extproc_perl, at his personal site:
> > >>>
> > >>> => http://www.smashing.org/
> > >>>
> > >>> It's groovy, baby! :-)
> > >>>
> > >>> Also, some useful extra utiltities for using Doug MacEachern's
> > >>> ExtUtils::Embed,
> > >>> which is used to drive extproc_perl, along with OCIExtProcContext et al,
> > >>> can
> > >>> be
> > >>> found in the full ExtUtils::Embed tarball download. Particularly useful
> > >>> for
> > >>> Win32 users, is the genmake utility:
> > >>>
> > >>> => http://www.cpan.org/authors/id/DOUGM/
> > >>> => http://www.cpan.org/authors/id/DOUGM/ExtUtils-Embed-1.14.tar.gz
> > >>>
> > >>> The extproc_perl Oracle Perl Procedure Library is, IMHO, an amazing piece
> > >>> of
> > >>> work. Just for starters, as a super-basic example, you can write a
> > >>> subroutine
> > >>> in a Perl bootfile, like this:
> > >>>
> > >>> sub perl_localtime {
> > >>>my $x = localtime(time);
> > >>>return $x;
> > >>> }
> > >>>
> > >>> And get output like this:
> > >>>
> > >>> SQL> select perl('perl_localtime') localtime from dual;
> > >>>
> > >>> LOCALTIME
> > >>> -
> > >>> Wed Dec 5 10:12:20 2001
> > >>>
> > >>> 1 row selected.
> > >>>
> > >>> SQL>
> > >>>
> > >>> You can also link back to the Oracle database from within the Perl bootfile
> > >>> script using DBI, stay within the original transaction, and not create a
> > >>> new
> > >>> connection, as with SQLJ etc. Fantastic stuff!!!
> > >>>
> > >>> I really _do_ have to get out more! 8-)
> > >>>
> > >>> Rgds,
> > >>> AndyD
> > >>>
> > >>> =
> > >>> Make Someone Happy. Buy a Copy of:
> > >>> => http://www.oreilly.com/catalog/oracleopen/
> > >>> -BEGIN GEEK CODE BLOCK-
> > >>> GO/SS/TW d- s+:+ a C++$ U++$ P$ L++$ !E W+ N+ K- W O-
> > >>> M+ V-- PS+ PE++ Y+ PGP t+@ 5 X- R* tv- b+++ DI++ D G e++
> > >>> h r+++ y
> > >>> --END GEEK CODE BLOCK--
> > >>>
> > >>> __
> > >>> Do You Yahoo!?
> > >>> Buy the perfect holiday gifts at Yahoo! Shopping.
> > >>> http://shopping.yahoo.com
> > >
> > > --
> > > E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> > > Date: 08-Feb-02
> > > Time: 09:20:40
> > > --
> >
> > --
> > E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> > Date: 08-Feb-02
> > Time: 11:01:42
> > --
>
> --
> Rick Nakroshis
> Applications Interface Manager
> SEIT Contract Team
>
> Defense Security Service
> Ft. Meade, Maryland
>
> (301) 677-5015
> DSN 622-5015
Re: Embedding perl in Oracle
Got the Pause message, thanks Tim.
STH
On 08-Feb-02 Scott T. Hildreth wrote:
>
> Tim,
>
> Will you be posting the this talk any time soon?
>
>
> On 27-Dec-01 Tim Bunce wrote:
>> I'm putting together a "Using Perl with Oracle" talk (for the Perl
>> Whirl '02 GeekCruise conference) and would very much like to hear
>> from anyone who's used Jeff's extproc_perl.
>>
>> The talk, like all my others, will be available from
>> http://cpan.valueclick.com/authors/id/TIMB/
>> once it's done.
>>
>> Thanks.
>>
>> Tim.
>>
>
>> p.s. cross-posted to oracle-l and dbi-users, please reply direct or
>> ammend any followups.
>>
>>
>> On Wed, Dec 05, 2001 at 02:22:07AM -0800, Andy Duncan wrote:
>>> Hi Tim,
>>>
>>> > > I've done it, it works. Be aware that setting it up is not trivial,
>>> > > as the documentation is somewhat incomplete.
>>> > > That is being remedied however...
>>> > > http://www.cpan.org/modules/by-authors/Jeff_Horwitz/
>>> > http://www.cpan.org/modules/by-authors/Jeff_Horwitz/extproc_perl-0.93.read
>>> > m
>>> > e
>>> > I'll take a look and add that to my Perl Whirl talk.
>>>
>>> You can also get hold of all of Jeff's other work, including the latest
>>> extproc_perl, at his personal site:
>>>
>>> => http://www.smashing.org/
>>>
>>> It's groovy, baby! :-)
>>>
>>> Also, some useful extra utiltities for using Doug MacEachern's
>>> ExtUtils::Embed,
>>> which is used to drive extproc_perl, along with OCIExtProcContext et al,
>>> can
>>> be
>>> found in the full ExtUtils::Embed tarball download. Particularly useful
>>> for
>>> Win32 users, is the genmake utility:
>>>
>>> => http://www.cpan.org/authors/id/DOUGM/
>>> => http://www.cpan.org/authors/id/DOUGM/ExtUtils-Embed-1.14.tar.gz
>>>
>>> The extproc_perl Oracle Perl Procedure Library is, IMHO, an amazing piece
>>> of
>>> work. Just for starters, as a super-basic example, you can write a
>>> subroutine
>>> in a Perl bootfile, like this:
>>>
>>> sub perl_localtime {
>>>my $x = localtime(time);
>>>return $x;
>>> }
>>>
>>> And get output like this:
>>>
>>> SQL> select perl('perl_localtime') localtime from dual;
>>>
>>> LOCALTIME
>>> -
>>> Wed Dec 5 10:12:20 2001
>>>
>>> 1 row selected.
>>>
>>> SQL>
>>>
>>> You can also link back to the Oracle database from within the Perl bootfile
>>> script using DBI, stay within the original transaction, and not create a
>>> new
>>> connection, as with SQLJ etc. Fantastic stuff!!!
>>>
>>> I really _do_ have to get out more! 8-)
>>>
>>> Rgds,
>>> AndyD
>>>
>>> =
>>> Make Someone Happy. Buy a Copy of:
>>> => http://www.oreilly.com/catalog/oracleopen/
>>> -BEGIN GEEK CODE BLOCK-
>>> GO/SS/TW d- s+:+ a C++$ U++$ P$ L++$ !E W+ N+ K- W O-
>>> M+ V-- PS+ PE++ Y+ PGP t+@ 5 X- R* tv- b+++ DI++ D G e++
>>> h r+++ y
>>> --END GEEK CODE BLOCK--
>>>
>>> __
>>> Do You Yahoo!?
>>> Buy the perfect holiday gifts at Yahoo! Shopping.
>>> http://shopping.yahoo.com
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 08-Feb-02
> Time: 09:20:40
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 08-Feb-02
Time: 11:01:42
--
Re: Embedding perl in Oracle
Tim,
Will you be posting the this talk any time soon?
On 27-Dec-01 Tim Bunce wrote:
> I'm putting together a "Using Perl with Oracle" talk (for the Perl
> Whirl '02 GeekCruise conference) and would very much like to hear
> from anyone who's used Jeff's extproc_perl.
>
> The talk, like all my others, will be available from
> http://cpan.valueclick.com/authors/id/TIMB/
> once it's done.
>
> Thanks.
>
> Tim.
>
> p.s. cross-posted to oracle-l and dbi-users, please reply direct or
> ammend any followups.
>
>
> On Wed, Dec 05, 2001 at 02:22:07AM -0800, Andy Duncan wrote:
>> Hi Tim,
>>
>> > > I've done it, it works. Be aware that setting it up is not trivial,
>> > > as the documentation is somewhat incomplete.
>> > > That is being remedied however...
>> > > http://www.cpan.org/modules/by-authors/Jeff_Horwitz/
>> > http://www.cpan.org/modules/by-authors/Jeff_Horwitz/extproc_perl-0.93.readm
>> > e
>> > I'll take a look and add that to my Perl Whirl talk.
>>
>> You can also get hold of all of Jeff's other work, including the latest
>> extproc_perl, at his personal site:
>>
>> => http://www.smashing.org/
>>
>> It's groovy, baby! :-)
>>
>> Also, some useful extra utiltities for using Doug MacEachern's
>> ExtUtils::Embed,
>> which is used to drive extproc_perl, along with OCIExtProcContext et al, can
>> be
>> found in the full ExtUtils::Embed tarball download. Particularly useful for
>> Win32 users, is the genmake utility:
>>
>> => http://www.cpan.org/authors/id/DOUGM/
>> => http://www.cpan.org/authors/id/DOUGM/ExtUtils-Embed-1.14.tar.gz
>>
>> The extproc_perl Oracle Perl Procedure Library is, IMHO, an amazing piece of
>> work. Just for starters, as a super-basic example, you can write a
>> subroutine
>> in a Perl bootfile, like this:
>>
>> sub perl_localtime {
>>my $x = localtime(time);
>>return $x;
>> }
>>
>> And get output like this:
>>
>> SQL> select perl('perl_localtime') localtime from dual;
>>
>> LOCALTIME
>> -
>> Wed Dec 5 10:12:20 2001
>>
>> 1 row selected.
>>
>> SQL>
>>
>> You can also link back to the Oracle database from within the Perl bootfile
>> script using DBI, stay within the original transaction, and not create a new
>> connection, as with SQLJ etc. Fantastic stuff!!!
>>
>> I really _do_ have to get out more! 8-)
>>
>> Rgds,
>> AndyD
>>
>> =
>> Make Someone Happy. Buy a Copy of:
>> => http://www.oreilly.com/catalog/oracleopen/
>> -BEGIN GEEK CODE BLOCK-
>> GO/SS/TW d- s+:+ a C++$ U++$ P$ L++$ !E W+ N+ K- W O-
>> M+ V-- PS+ PE++ Y+ PGP t+@ 5 X- R* tv- b+++ DI++ D G e++
>> h r+++ y
>> --END GEEK CODE BLOCK--
>>
>> __
>> Do You Yahoo!?
>> Buy the perfect holiday gifts at Yahoo! Shopping.
>> http://shopping.yahoo.com
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 08-Feb-02
Time: 09:20:40
--
Re: SQL Implementation Specifics
On 25-Jan-02 Jeff Zucker wrote: > Tim Bunce wrote: >> >> p.s. Of course the SQL standards team should be ashamed of creating >> a syntax that risks breakage with things like: >> "update foo set bar=bar-$value" >> (If you can't see it, consider what happens if $value is negative." > > Good point. How will the preparser handle this? It will presumably have > to turn double minus style comments into C-style comments -- It will turn them into what is acceptable. The driver will tell DBI what to translate or leave as a comment style. There is 2 styles for '--', '--' & '-- '(dash dash white space). and therefore > will have to decide if any given double minus sign occuring in a string > is a comment or a minus operator applied to a negative number. I would > guess the best way is to treat "--X", where X is a positive number, as > part of a statement and all other double minuses as comment introducers. I did bring this idea up, Tim thought it was an interesting one yet somehow it was not...wait let me look for his email. okay found it, >Would it be to far fetched to parse '--(digits) ' as not a comment? Interesting but still a fudge. The right fix is to support both DBIpp_cm_dd & DBIpp_cm_dw. ...I don't know what to think. If you are using Oracle(for example) the user has to know that if they have an expression that is '6--5' the '5' will be treated as a comment. I personally would always write the expressions with space in them, 6 - -5, beacause I know Oracle treats -- as a comment. Mysql only allows '-- ' for dash-dash style comments. But to answer your question, currently the preparser will treat '--' as comment and it will return what ever the driver indicates is acceptable. STH -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 25-Jan-02 Time: 12:59:39 --
Re: DBI::Proxy configuration help
I have it working with Build 629. On 21-Jan-02 Tim Bunce wrote: > On Mon, Jan 21, 2002 at 09:27:29AM +0100, [EMAIL PROTECTED] wrote: >> On 20 Jan 2002, at 21:47, Tim Bunce wrote: >> >> > On Tue, Jan 15, 2002 at 09:51:28AM +, Simon Oliver wrote: >> > > Beware if runing ActiveState perl. ActivePerl is compiled with >> > > iThreads, which is not compatible with Storable >> > >> > Why not? >> > Does the author know? >> > Do p5p and/or ActiveState know? >> > Is the problem being worked on by anyone? >> > >> > Tim. >> >> This is copied'n'pasted from the release notes of the current ActivePerl >> build: >> >> The fork() emulation has known limitations. See perlfork for a detailed summary. In >particular, >> fork() emulation will not work correctly with extensions that are either not >thread-safe, or maintain >> internal state that cannot be cloned in the psuedo-child process. This caveat >currently applies to >> extensions such as Tk and Storable. >> >> I don't know if the above can lead to basic restrictions in the usage of >> DBD::ProxyServer by design, but I know from own experience, that there has >> been at least one ActivePerl release (Perl 5.6.0, build 622) where Storable >> crashed upon errors when retrieve()ing or thaw()ing in eval blocks (="pseudo- >> child-processes" ?). Perhaps opinions about ActivePerl and Storable are >> triggered by this and probably other broken ActivePerl releases. The current >> release (631) does not exhibit this problem. > > Are you saying that DBD::ProxyServer works with release 631? > > Tim. -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 21-Jan-02 Time: 08:16:00 --
DBIx::MSSQLReporter
Sorry if this is off-topic (not directly DBI), but I can't seem to figure out what's going on... I've never used the MSSQLReporter module before, and can't seem to get anything to work! When I call $reporter -> get_fieldNames($fieldname) with any variable I get a prepare failed error on the table name: DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near 'billcodes'. (SQL-37000) [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-37000)(DBD: st_prepare/SQLPrepare err=-1) at C:/Perl/site/lib/DBIx/MSSQLReporter.pm line 116. You can see that the variable looks to be working, as my table name is in the error. By replacing the variable with the table name (in single or double quotes) I don't have this problem. I've tried quoting or not quoting the variable, but it just doesn't seem to work! I'm using Activestate Perl 5.6.1 build 626 on Win2k with the latest MSSQLReporter (1.00) from Activestate's PPM repository against a MS-SQL 7.0 database. Thanks in advance for any help, Scott Phelps [EMAIL PROTECTED] Webkorner Internet Services --- [ This E-mail scanned for viruses by www.WebKorner.com ]
RE: DBI::AnyData question
I've been helping Tim with a preparser for DBI, which in his vision all the DBD's would use. Basically the Driver would tell the preparser what is acceptable for comments, placeholders, & escape chars and what to return for comments, placeholders, & escape chars. It is a hybrid of the dbd_preparse from DBD::Oracle & DBD::Informix. I guess it is best explained with an example, $sql = "Select * from foo where id = :1 # a comment" $r = DBI::preparse(0, $sql, DBIpp_cm_hs|DBIpp_ph_cn, DBIpp_cm_dw|DBIpp_ph_qs) DBIpp_ph_cn is a flag that says accept ':1' style placeholders DBIpp_cm_hs is a flag that says accept '#' style comments DBIpp_ph_qs is a flag that says return the placeholder as '?' style. DBIpp_cm_dw is a flag that says return the comments with '-- ' style. ...so the return statement would be "Select * from foo where id = ? -- a comment". The new code is not currently in DBI, unfortunately I was not able to get the work done in time, before Tim left. There is code in DBI for the preparser, but it is old and has changed considerably. Anyway short explaination, I have run to a meeting. STH On 11-Jan-02 Sterin, Ilya wrote: > Escaping with a backslash might become a problem later. Jeff and I talked > before, and not sure about his vision on this, I want SQL::Parser, which > comes with SQL::Statement, to become a universal SQL::Parser and therefore > possibly used by all DBDs and possibly DBI iteself, for any SQL parsing > needs. It will of course have to be configurable. So a possible config can > be an attribute with an escape character. > > Ilya > > -Original Message- > From: Simon Oliver > To: Ronald J Kimball > Cc: Ron Hall; [EMAIL PROTECTED] > Sent: 1/11/02 8:07 AM > Subject: Re: DBI::AnyData question > >> According to the docs Simon quoted, for DBD::AnyData > Actually, it's due to SQL::Statement which is used by a number of DBD > modules > including DBD::AnyData and DBD::CSV > >> you need to escape a >> single quote with a backslash, not with another single quote, as you > would >> for most other DBDs. > > Which is why it is better to use the $dbh->quote method or placeholders > - they > are more portable! > > -- > Simon Oliver -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 11-Jan-02 Time: 09:42:05 --
RE: connection frustrations
Use Proxy, perldoc DBD::Proxy On 07-Dec-01 Brian LaMere wrote: > My situation is simple (to describe). I have Oracle 8.1.7 installed on a > solaris 2.8 environment. I have a multitude of linux systems that need to > connect to said server, via perl. Oracle, as is its purpose, will be > housing a database...duh. The various linux systems need to be able to > access that database through Perl. > > Do I REALLY have to install Oracle on all these systems, just to connect via > Perl? Aside from the fact that when I run runInstaller that it just sits > there (can be resolved...but still)...isn't a minimal install almost a gig? > Seems like an extreme waste to install Oracle on many linux servers just so > they can connect to the Sun server that the Oracle database I want to > connect to is on. > > Can anyone clue me in here? What do I need to be able to have my linux > boxes connect to the Oracle server via Perl? Every README seems to assume a > local Oracle install...why? Is what I'm trying to do not valid enough? > > Eventually, I'll even want to have windows clients connect to the Oracle > database via Perl. Hate to think I'd need to go around installing Oracle on > ever person's box that needs to do this... > > Help?!? > > Brian LaMere > Diversa -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 07-Dec-01 Time: 12:56:15 --
RE: Has anyone COMPILED in the 9i libraries with DBD ???
Only on Linux, we don't have 9i for Solaris yet. Although the Linux doesn't mean diddly squat to your situation, I indeed have compiled DBD::Oracle with the 9i libraries. On 07-Dec-01 Keith Kwiatek wrote: > Hello, > > Let me ask this a differnt way has anyone compiled in the 9i libraries > with DBD ? > > I an NOT talking about using an existing DBI/DBD setup using 8.x.x oracle > libraries to connect to an oracle 9i database. > > Keith > > > -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 07-Dec-01 Time: 08:59:08 --
RE: Anybody else getting these emails?
Turns out it is a Worm, "BadTrans" check out www.sarc.com. On 04-Dec-01 Fox, Michael wrote: > not me - and I'm on the OCI list too > > -Original Message- > From: Scott T. Hildreth [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, December 05, 2001 6:51 AM > To: [EMAIL PROTECTED] > Subject: Anybody else getting these emails? > > > > I just got a second email, the first came from the OCI list, with an > attachment. This one has "HAMSTER.DOC" attached, from a derrick.steel. > The other email I got had a "MENUDE.MPG" or something like that. I assume > these are viruses. Just wondering if anyone else is recieving these. > > -- > E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> > Date: 04-Dec-01 > Time: 13:45:17 > -- > Australia Post is committed to providing our customers with excellent service. If >we can assist you in any way > please either telephone 13 13 18 or visit our website www.auspost.com.au. > > CAUTION > > This e-mail and any files transmitted with it are privileged and confidential >information intended for the use of the > addressee. The confidentiality and/or privilege in this e-mail is not waived, lost >or destroyed if it has been > transmitted to you in error. If you have received this e-mail in error you must (a) >not disseminate, copy or take any > action in reliance on it; (b) please notify Australia Post immediately by return >e-mail to the sender; and (c) please > delete the original e-mail. > -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 04-Dec-01 Time: 16:06:32 --
Anybody else getting these emails?
I just got a second email, the first came from the OCI list, with an attachment. This one has "HAMSTER.DOC" attached, from a derrick.steel. The other email I got had a "MENUDE.MPG" or something like that. I assume these are viruses. Just wondering if anyone else is recieving these. ------ E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 04-Dec-01 Time: 13:45:17 --
RE: Oracle 9i
If you want to use the 9i libraries, you definitely have to recomplie
DBD::Oracle. If the 8.0.5 instance is still on the machine you could
use the current DBD::Oracle and connect through sqlnet,
$dbh = DBI->connect("dbi:Oracle:", 'user/passwd@sid', '', {});
/\
this would be the 9i instance.
On 04-Dec-01 Ron Peled wrote:
> Hey Scott,
>
>First of all thanks for the quick answer.
> Second :Connecting via SQLPLUS connects me to the DB just fine.Looking in
> the tnsnames.ora it looks just fine and so is the ORACLE_SID .
> I have to admit that I'm quite new at this.
>
> some facts that might help is that on the same machine(SUN) the same program
> worked fine with an oracle 8.0.5 until yesterday when our DBA upgraded the
> DB .
> maybe I should remake the DBD:Oracle on the machine(sounds weired to me?)
>
> thanks
>
>
>
> -Original Message-
> From: Scott T. Hildreth [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, December 04, 2001 5:04 PM
> To: Ron Peled
> Cc: [EMAIL PROTECTED]
> Subject: RE: Oracle 9i
>
>
>
> I found it works fine. You have a listner problem,
>
> 12514, 0, "TNS:listener could not resolve SERVICE_NAME given in connect
> descriptor"
> // *Cause: The SERVICE_NAME in the CONNECT_DATA was not found in the
> listener's tables.
> // *Action: Check to make sure that the SERVICE_NAME specified is correct.
> // *Comment: This error will be returned if the database instance has not
> // registered with the listener; the instance may need to be started.
>
> the message from DBD::Oracle is that it can't find the above text, which
>is extracted from the Oracle Home directories. Try 'oerr ora 12514' from
>a shell prompt, see if the above message is printed.
>
> On 04-Dec-01 Ron Peled wrote:
>> Hello every one
>>
>> Does anybody know if there is a DBD:oracle that connects/work good with
>> Oracle 9i???
>> HELP!
>>
>> I'm currently using DBI 1.20 and the DBD:ORACLE 1.12 and I keep getting
> the
>> lines :
>> Message 12514 not found; product=RDBMS;facility=ORA (DBD ERROR:
>> OCIServerAttach)
>>
>>
>> Thanks
>> Ron
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 04-Dec-01
> Time: 08:59:33
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 04-Dec-01
Time: 10:31:22
--
RE: Oracle 9i
I found it works fine. You have a listner problem, 12514, 0, "TNS:listener could not resolve SERVICE_NAME given in connect descriptor" // *Cause: The SERVICE_NAME in the CONNECT_DATA was not found in the listener's tables. // *Action: Check to make sure that the SERVICE_NAME specified is correct. // *Comment: This error will be returned if the database instance has not // registered with the listener; the instance may need to be started. the message from DBD::Oracle is that it can't find the above text, which is extracted from the Oracle Home directories. Try 'oerr ora 12514' from a shell prompt, see if the above message is printed. On 04-Dec-01 Ron Peled wrote: > Hello every one > > Does anybody know if there is a DBD:oracle that connects/work good with > Oracle 9i??? > HELP! > > I'm currently using DBI 1.20 and the DBD:ORACLE 1.12 and I keep getting the > lines : > Message 12514 not found; product=RDBMS;facility=ORA (DBD ERROR: > OCIServerAttach) > > > Thanks > Ron -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 04-Dec-01 Time: 08:59:33 --
RE: Oracle DBD BLOB's and ActiveState Perl
Ilya has DBI 1.20 & DBD-Oracle 1.12 available, >From Earlier Posts, I made the DBI 1.20 available for windows PPM utility. This is how you must install. You must first set your repository to www.xmlproj.com/PPM by doing... ppm set repository XMLPROJ http://www.xmlproj.com/PPM Then you can install using ppm install DBI-1_20 Let me know if everything works. Ilya _ D:\>ppm PPM interactive shell (2.1.5) - type 'help' for available commands. PPM> set repository XMLPROJ http://www.xmlproj.com/PPM PPM> search DBD-Oracle* Packages available from http://www.xmlproj.com/PPM: DBD-Oracle-1_12 [1.12] DBD-Oracle 1.12 Packages available from http://ppm.ActiveState.com/cgibin/PPM/ppmserver.pl?urn:/ PPMServer: DBD-Oracle [1.06] Oracle database driver for the DBI module DBD-Oracle8 [1.06] Oracle 8 database driver for the DBI module PPM> install DBD-Oracle-1_12 Install package 'DBD-Oracle-1_12?' (y/N): y Installing package 'DBD-Oracle-1_12'... Bytes transferred: 98144 Mark On 29-Nov-01 Jared Still wrote: > > > Imagine my dissapoint at the results of running my new script > on Win32 and seeing the message below: > > DBD::Oracle::st execute failed: ORA-00932 inconsisten datatypes > ( DBD oexfet error; e.g., can't select LOB fields using DBD::Oracle > built for Oracle 7 ) > > > This all runs fine on Linux, Oracle 8.1.7, Perl 5.6.1 and DBD::Oracle 1.12. > > Alas, the win32 ActiveState DBD is 1.06 and apparently compiled with > moth eaten old libs. > > I've found some references in the archives to using blob_read ( ala LONG > columns ), but those are a year old. > > Does anyone know of an alternative to the PPM that is available at > ActiveState? Rewriting is an option ( not a very attractive one mind you ) > but I don't really want to go that route. It's not a lot of code, but it's > rather disheartening to use an archaic method to work with new features. > > Only on windoze. > > Sigh... > > Jared -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 29-Nov-01 Time: 08:04:58 --
RE: Red Brick Perl on Linux
Attached is an old email I have that helped others.
On 28-Nov-01 Patrick Dennis wrote:
> Hello,
>
> I've seen a few messages in here a while back explaining how to setup Perl
> DBI, DBD::ODBC using the Red Brick ODBC driver (and either the iODBC or
> unixODBC driver manager) to gain access to the Red Brick database. There
> seems to be some issues around SQLDescribeParam which requires some altering
> off the MakeFile.PL script and I was wondering if someone had a complete
> instructions on how to do this.
>
> Currently we have our Perl scripts running on a Win2K machine connecting to
> a Red Brick database using the Red Brick risql Win32 client. I would like
> to migrate all of those Perl scripts to a Linux box but I need to test the
> DBI/DBD::ODBC on a Linux box.
>
> If anyone can help me out or point me in the right direction, I would very
> much appreciate it.
>
> Regards,
>
> Patrick Dennis
>
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 28-Nov-01
Time: 07:53:25
--
Thanks a million;
I haven't got all the test to run yet, but I'm connecting and
pulling data! I wasted more than 1 night on this.
thanks again,
-shawn
> -Original Message-
> From: Scott T. Hildreth [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, November 24, 1998 8:17 AM
> To: Holladay, Shawn
> Cc: [EMAIL PROTECTED]
> Subject: RE: Redbrick-perl
>
>
> Hi Shawn,
>
> I know that it is frustrating, so I wiil help in anyway that I can.
> I would definitely use the Redbrick's ODBC driver since it is specific to
> Redbrick, I'm not an expert, I believe you have to use the Redbrick driver
> to access the database, since it is the ODBC Calls that compliant, not how
> they are implemented. Here are some checks to get started.
>
> -In your ODBC-20 directory change the Makefile.PL
>
> #my $libs = "odbc";
> my $libs = "rbodbc";
> #$opts{LIBS} = " -L$lib_d1 -R$lib_d1 -L$lib_d2 -R$lib_d2 -l$libs";
> $opts{LIBS} = " -L$lib_d1 -l$libs";
>
> my $myodbc = 'rbodbc'; # edit and hack to suit!
>
> add :
>
> elsif ($myodbc eq 'rbodbc')
> {
> $opts{INC} .= " -I$ENV{ODBCHOME}/include";
> $opts{DEFINE} = "";
> print SQLH qq{#include \n#include \n};
> }
>
> -Make sure you have the enviorment variables set :
>
> ODBCHOME=/usr/redbrick/odbc - where you have the rbodbc lib/ & include/
>
> DBI_DSN=dbi:ODBC:spring
> DBI_PASS=manager
> DBI_USER=system
>
> -Also you have to have a .odbc.ini in your $HOME, a template is in
> the redbrick odbc directory.
>
> -In the DBD-ODBC-0.20 directory add this to the dbdimp.c code :
>
> RETCODE SQL_API SQLDescribeParam (
> HSTMT hstmt,
> UWORD ipar,
> SWORD FAR* pfSqlType,
> UDWORD FAR* pcbColDef,
> SWORD FAR* pibScale,
> SWORD FAR* pfNullable )
> { return SQL_ERROR; }
>
> ..Then remake DBD::ODBC.
>
> If this works, some notes on the tests,
>
> -in the t/ directory there is the ODBCTEST.pm. I changed
>
>foreach $type (@{ $TestFieldInfo{$f} }) {
> $sth = $dbh->func($type, GetTypeInfo);
> # probably not right, but get the first compat type
> @row = $sth->fetchrow();
> last if @row;
> }
> print "\n\nUnable to find a suitable test type for field
> $f\n\n"
> unless @row;
> /\
> ||
> This was a die, but I changed it to print, since Redbrick ODBC
>
> returns a 0 for SQL_CONVERT_LONGVARCHAR, obviously
> unsupported,
> but I want the rest of the test to finish.
>
> The tab_create() fails because of syntax, I have not gotten back to
> changing
> the test, but I can connect, and I plan to finish testing by accessing a
> database I have stored in Redbrick.
>
> I hope this helps.
>
> STH
>
> On 24-Nov-98 Holladay, Shawn wrote:
> >Scott,
> >I've noticed your last couple letters on the DBI archives about
> >installing DBD:ODBC on your Digital Box. I'm having the same problems
> >that you were with the "infamous dynaloader" (unable to load ODBC.so).
> >I'm also on Digital (OFS V4.0 (rev 564)) trying to connect to Redbrick v
> >5.0.15.
> >I'm already on DBI 1.02 and ODBC 0.20, but I'm having the same dynaloader
> >problem.
> >I was wondering if you knew of any other problems with this setup to
> cause
> >this problem? Is it better to try use redbrick's ODBC driver
> (librbodbc.so)
> > over the packaged one with the ODBC module (iodbc-2.12.so)? Same
> problem
> >for me either way. Any information will be greatly appreciated.
> >thanks,
> >[EMAIL PROTECTED]
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 24-Nov-98
> Time: 09:16:39
>
> This message was sent by XFMail
> --
Re: inserting into CLOB field
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Wolfgang Weisselberg) wrote: > > Hi, I am new to this newsgroup, so I hope this is not a bad question. > > It's a good question, but I thought it was a mailing list :-) actually it's both.. point your news reader at nntp://nntp.perl.org :) -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/ It is not necessary to cc: me via e-mail unless you mean to speak off-group. I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
Re: inserting into CLOB field
...or I could be wrong :-) > Your DBD::Oracle is built using the Oracle7 OCI API, it doesn't > know about LOBS. > > Tim. On 16-Nov-01 Scott T. Hildreth wrote: > Versions do matter, I believe CLOB was fixed in DBD::Oracle 1.09. > I had emailed Becka yesterday to try the new versions. > -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 16-Nov-01 Time: 21:09:56 --
Re: inserting into CLOB field
Versions do matter, I believe CLOB was fixed in DBD::Oracle 1.09. I had emailed Becka yesterday to try the new versions.
RE: inserting into CLOB field
> use strict; use DBI; use DBD::Oracle qw(:ora_types);
>
> $insert_cr->bind_param(1, $cr, {ora_type => ORA_CLOB});
>
Not that I'm doubting you, but I had the same error and I found
that the above bind works for me. Can you send a Trace use level 9.
> my $result_ins = $insert_cr ->execute() || print "Insert into CRR table
> failed!!\n";
>
> if(!defined($result_ins)) {
> print "Insert into CRR table failed!!\n";
> } else {
> print "Insert into CRR table result: $result_ins\n";
> }
>
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 15-Nov-01
Time: 15:37:34
--
Re: bind_param question
In article
<[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Brad Watts) wrote:
> print header;
> start_html("ATT Canada - (NCMRS) Network Capacity Management Report
> Server");
that should be
print header(),
start_html("ATT Canada - (NCMRS) Network Capacity Management
Report Server");
> #
> # Define and Call Select Statement For First Screen #
> #
>
> my $dbh =
> DBI->connect("DBI:mysql:password=bash111:user=stats;database=CAPACITY",
> {'RaiseError' => 1});
>
> my $sth_1 = $dbh->prepare("SELECT FORE_ADDRESS_T.CITY CITY
> ,FORE_ADDRESS_T.REAL_LOCATION ADDRESS
> ,COUNT(DISTINCT FORE_HARD_WARE_T.IP_ADDRESS) TOTAL_BOXES
> ,COUNT(DISTINCT FORE_HARD_WARE_T.SLOT_NUM) TOTAL_SLOTS
> ,COUNT(FORE_HARD_WARE_T.PORT_NUM) TOTAL_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'up\',1,0)) AS UP_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'down\',1,0)) AS DOWN_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'standby\',1,0)) AS
> STANDBY_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'\',1,0)) AS NO_STATUS_PORTS
> FROM FORE_ADDRESS_T INNER JOIN FORE_HARD_WARE_T USING (IP_ADDRESS)
> WHERE FORE_ADDRESS_T.IP_ADDRESS = FORE_HARD_WARE_T.IP_ADDRESS
> AND FORE_ADDRESS_T.CITY <> \'UNKNOWN\'
> AND FORE_HARD_WARE_T.DATE = " . $dbh->quote( $sql_date ) . "
> AND FORE_ADDRESS_T.CITY = " . $dbh->quote( $sql_city ) . "
> # AND FORE_ADDRESS_T.PROVINCE = ?
> AND FORE_ADDRESS_T.REAL_LOCATION LIKE " . $dbh->quote( "%$sql_location%" )
> . "
> GROUP BY FORE_ADDRESS_T.CITY, FORE_ADDRESS_T.REAL_LOCATION") or die
> "Couldn't prepare statement: " . $dbh->errstr;
each of those $dbh->quote( ) parts can be replaced with a ? thusly,
(although I haven't parsed the rest of your SQL for correctness)
my $sth_1 = $dbh->prepare("SELECT FORE_ADDRESS_T.CITY CITY
,FORE_ADDRESS_T.REAL_LOCATION ADDRESS
,COUNT(DISTINCT FORE_HARD_WARE_T.IP_ADDRESS) TOTAL_BOXES
,COUNT(DISTINCT FORE_HARD_WARE_T.SLOT_NUM) TOTAL_SLOTS
,COUNT(FORE_HARD_WARE_T.PORT_NUM) TOTAL_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'up\',1,0)) AS UP_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'down\',1,0)) AS DOWN_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'standby\',1,0)) AS
STANDBY_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'\',1,0)) AS NO_STATUS_PORTS
FROM FORE_ADDRESS_T INNER JOIN FORE_HARD_WARE_T USING (IP_ADDRESS)
WHERE FORE_ADDRESS_T.IP_ADDRESS = FORE_HARD_WARE_T.IP_ADDRESS
AND FORE_ADDRESS_T.CITY <> \'UNKNOWN\'
AND FORE_HARD_WARE_T.DATE = ?
AND FORE_ADDRESS_T.CITY = ?
AND FORE_ADDRESS_T.REAL_LOCATION LIKE '%?%'
GROUP BY FORE_ADDRESS_T.CITY, FORE_ADDRESS_T.REAL_LOCATION")
or die("Couldn't prepare statement: ", $dbh->errstr);
and then do
> my @data;
>
> #
> # Decide Whether or not to Execute Select Statement #
> #
>
> if ( defined $sql_date && $sql_date =~ /\d+\-\d+\-\d+/ ) {
> my @data;
# it wil call quote() on these automatically if you do it this way :)
$sth_1->execute($sql_date, $sql_city, $sql_location)
or die "Couldn't execute statement:" . $sth_1->errstr;
> }
>
> if ( defined $sql_date and ! defined $sth_1->fetchrow_array()) {
> print "Sorry, I was unable to
> process your request. Please try again.. \n"
> ;
> }
I don't believe you can use a block-level header like H3 in a table
caption. (and you also never close the Center tag, which has been
deprecated anyway, since html 3.2 years ago)
You might be better off setting this with a stylesheet and importing it
in your start_html() like this
# in CSS never set color without also setting background color --
# user's stylesheets may override otherwise.
my $stylesheet=<<"EOS";
caption {background: white; color: #3366ff; font-size: large ;}
EOS
my $pagetitle = 'ATT Canada - (NCMRS) Network Capacity Management Report
Server';
print header(),
start_html({-'style'=>{-'code'=>$style}, -title=>$pagetitle});
and later...
print caption("Sorry, I was unable to process your request. Please try
again.. "), "\n";
Much neater eh?
:-)
if you want to have a caption that's differently colored than normal for
the error you can do
caption.error { background: white; color: #3366ff; font-size: large ; }
in the stylesheet and then
print caption({-class=>'error'}, "Sorry, I was unable to process your
request. Please try again.. "), "\n";
HTH
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
It is not necessary to cc: me via e-mail unless you mean to speak off-group.
I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
Re: command syntax
try one of the $dbh-select(all|row)_arrayref, this does a prepare & execute for you. Do a 'perldoc DBI' to read about them.
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Michael Peppler) wrote:
> > my $rows = $sth->rows;
> > # only expecting one row for a unique ID . this should NEVER happen.
> >safe_error("invalid number of rows returned from database ($rows) for
> > ID $id")
> > if $rows > 1;
> > # although this might...
> > safe_error("no match in database for ID $id")
> > if $rows < 1;
>
> Be careful here!
>
> Most DBI drivers will return -1 for $sth->rows() for a SELECT query.
=:o
> In the case of DBD::Sybase $h->rows() will return the correct number
> of rows only *after* all the rows have been fetched.
I guess I'm fortunate that DBD::'s CSV, AnyData, and mysql all work this
way.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Tim Bunce) wrote:
> On Fri, Nov 02, 2001 at 02:18:15PM +0100, Bart Lateur wrote:
> > On Fri, 02 Nov 2001 07:27:49 -0500, Scott R. Godin wrote:
> >
> > >my %db;
> > >$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
> > >
> > >while ($sth->fetch)
> > >{
> > >#... and no worries about which order the columns get returned in
> > >#... since you access them via the $db{ColumnName} method :)
> >
> > What's the advantage of this approach over
> >
> > while(my $db = fetchrow_hashref) {
> > ...
> > }
> >
> > and accessing the datae through $db->{ColumnName}?
>
> Speed! It's many times faster (assuming the loop is empty :)
>
> (But use $sth->{NAME_lc} or $sth->{NAME_uc} for portability.
>
> Tim.
with the exception of my case where neither mod_perl nor Apache::DBI is
compiled in..
in the php vs perl thread earlier this (last?) month, I posted some
"benchmarks" done by the site admin on a search of 5100 rows for "c" by
their ph script and my perl script.. the results were staggeringly
different, even with the help of this (see the script I posted in that
thread for details on what I was doing) the thread issues are posted
here: <[EMAIL PROTECTED]>
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBI 1.15+ establishes multiple connections under parent mod_perl process
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Tim Bunce) wrote: > > There were connect() changes made between DBI 1.14 and 1.15 but I'd need > people to look into it for me. Should be trivial to debug by enabling > DBI tracing and Apache::DBi debug. Unless your admin refuses to run any of the mod_perl and Apache::DBI stuff compiled in, because he's a php freak and thinks mod_perl is a resource pig. :\ -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBI 1.15+ establishes multiple connections under parent mod_perl process
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Eric Kolve) wrote:
> I have traced it back to prepare_cached() (at least that is what I
> notice).
>
> Scott, try replacing your calls on startup with prepare() instead of
> prepare_cached().
no, I'm using prepare(). an earlier post thread of mine (php vs perl)
has a copy of the script I'm running in it.
> I was also able to eliminate the problem if I commented out the
> following line in DBI.pm
>
> # $dbh->STORE('CachedKids', $cache = {}) unless $cache; # line 1021
> sub prepare_cached
I haven't tried this though
> Of course this is not a solution, but it may give someone else with more
> knowledge enough to fix the problem. I will keep digging for answers.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBI 1.15+ establishes multiple connections under parent mod_perl process
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Eric Kolve) wrote: > I think I have found a curious bug in DBI. It seems that since DBI 1.15 > - 1.20, when you bring up apache/mod_perl and execute queries against > the database handle in the parent process (startup.pl), multiple > connections result against the database. If I switch to DBI 1.14, no > such problem occurs. I have found this problem occurs with: > > DBI 1.20 + DBD::Oracle 1.12 > DBI 1.15 + DBD::Oracle 1.07 > DBI 1.16 + DBD::Oracle 1.07 > > > I have turned on Apache::DBI::DEBUG and trace(2) in DBI. Could someone > tell me what I should be looking for or can someone else shed any light > on this? I am not sure if this is necessarily a mod_perl issue or if > mod_perl is just eliciting a bug in DBI. > > thanks, > > --eric I've noticed this too, and it has *seriosly* damaged any credibility I might have gained with the admin I'm up against who is a major PHP proponent, and who refused to even think about installing mod_perl to help the script along after he saw this. :/ -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Bart Lateur) wrote:
> On Thu, 01 Nov 2001 18:56:18 -0800, Venkataramana Mokkapati wrote:
>
> >How do I get column names and order of column names
> >for a "select * from ..." query.
>
> If you have
>
> $sth = $dbh->prepare("select * from ...");
>
> then try
>
> @column names = @{$sth->{NAME}};
>
> You may have to do an "execute" first, for this to return anything of
> value.
>
> It's in the DBI docs under the heading "Statement Handle Attributes", in
> the DBI POD formatted as text around line 2284.
the absolute neatest trick I've seen with this, that is so totally
perlish it defies description.. you stare at it for a bit and suddenly
all becomes clear.
$sth->execute
or die("Cannot Execute SQL Statement: ", $sth->errstr(), "\n");
my $rows = $sth->rows;
# only expecting one row for a unique ID . this should NEVER happen.
safe_error("invalid number of rows returned from database ($rows) for
ID $id")
if $rows > 1;
# although this might...
safe_error("no match in database for ID $id")
if $rows < 1;
my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBD::CSV incorrect detection of numeric fields, patch?
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Sam Roberts) wrote:
> I'm sorry its taken me so long to get back to you. I lost your
> patch, hacked something that worked for my particular problem,
> and then got pulled into other things.
>
> However, I'm back. I'm having the problem with DBD:CSV, wherein things
> like DISTINCT and SORT don't work:
>
[snip]
> ~/w/svv/tools/dbtools $ perl db-tool export "select distinct date from
> svv_bug_count"
> 2001/09/18 11:25
I know that in MySQL you can do something along the lines of
SELECT date FROM svv_bug_count ORDER BY date DESC LIMIT 1
which will return a descending list of dates from the most recent on
down, and limit the return to a single value :)
I don't know if DBD::CSV will do it, but you *could* try Jeff Zucker's
DBD::AnyData.pm and AnyData.pm modules, which are the replacement for
the old DBD::CSV/DBD::RAM modules. I DO know that there were some things
that DBD::CSV was choking on that DBD::AnyData was able to do admirably
on MacPerl (setting a different csv_eol for example :)
> Can you send me what I need (a newer SQL::Statement, at least) to try
> your newest stuff, and I'll do so Monday, and get back to you right away
> (this time) on how it works.
>
> Thanks a lot, sorry I appeared to drop off the earth.
>
> Sam
>
> p.s. Totally offtopic: I'm an SQL newbie, do you happen to know an SQL
> query that will get me the latest date? I was going to do a
> sort|uniq|head on "select date" (the perl equiv, that is) to get the
> latest date for which data exists, but was wondering if there was a
> purely SQL way. The sort and uniq is supported by SQL, but getting only
> the single greates valued item?
see above :)
> p.p.s. Is there any kind of special date handling in SQL::Statement?
> I've chosen "/mm/dd hh:mm" (with 24 hour hh) because it a textual
> sort is equivalent to a date sort, but it would be nice if there was
> a way to treat a field as a date, and do things like:
this is *very* close to the formatting used by MySQL for it's DATETIME
field, which is "-mm-dd HH:MM:SS"...
> select distinct year-month-day(date) from a_table
>
> so that the SQL engine new that the data field was date data, and just
> returned the day-month-year part, stripping out the timeof day part.
>
> Not SQLs problem, or just not supported by DBD::CSV?
with the proper formatting (and yours should suffice even if you didn't
want to convert it to the standard MySQL DATETIME format) you should be
able to extract it with a simple regex, since you KNOW the format that
you can expect to be returned.
With DBD::mysql I'd do: (I don't offhand know whether it supports an
extraction but I suppose it does.. for the sake of argument lets assume
it doesn't (cuz I'm too tired to check, and it's likely that CSV or
AnyData don't anyway) and that we want a regex to extract the return
value (and that RaiseError => 1 is set))
my $sth = $dbh->prepare("SELECT date FROM svv_bug_count ORDER BY date
DESC LIMIT 1");
$sth->execute();
$sth->bind_columns( \($date) );
while ( $sth->fetch() )
{
my($yyymmdd, $hhmmss) = split / /, $date;
my($, $mm, $dd) = split m|/|, $mmdd; # or split /-/, etc.
print "Year: $, Month: $mm, Day, $dd\n";
}
$sth->finish if $sth;
$dbh->disconnect();
does that help?
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: php vs perl again (reposted - original post was incomplete)
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Scott R. Godin) wrote: > > Well post the script and we can look. Please eliminate the parts that are > > not relevant if you script is big. > > it's about 255 lines of code incuding comments.. I'll remove the > comments from the file to shorten it a bit. The linewrapping will suck > but I'll try and clean it up a bit. just following up to my previous reply since I haven't seen any further follow-up regarding the code I posted, and wondering if it got misplaced... ? -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: php vs perl again (reposted - original post was incomplete)
r( td({-colspan=>3, -align=>'center', }, b("No Match
Found") ));
return '';
}
print Tr( td({-colspan=>3, -align=>'center', }, b("Found $rowcount
matches") ));
my( $type, $id, $filename, $title, $size, $reviewfile, $rating,
$rated, $oldtype );
$sth->bind_columns(\$type, \$id, \$filename, \$title, \$size,
\$reviewfile, \$rating);
while ( $sth->fetch )
{
# in english: If oldrating is empty, or different from the
# previous rating AND the rating is now < 0
if ( !defined($rated) or ($rated != $rating and $rating < 0) )
{
# then check to see whether we're rated or unrated and print
# an appropriate header for that section
if ($rating < 0)
{
print Tr( td({-colspan=>3, -align=>'center', }, b(
u("Unrated Maps")) )),
Tr(
th({-align=>"center"}, "Map Name"),
th({-align=>"right"}, "Size"),
th({-align=>"center"}, "Rating"),
);
$oldtype = -1; #re-set oldtype ;)
}
else
{
print Tr( td({-colspan=>3, -align=>'center', }, b(
u("Rated Maps")) )),
Tr(
th({-align=>"center"}, "Map Name"),
th({-align=>"right"}, "Size"),
th({-align=>"center"}, "Rating"),
);
};
};
# okay so we have nice section headers..
# how about some type section headers for the unrated section?
if ( $rating == -1 and $type != $oldtype )
{
print Tr( td({-colspan=>3, -align=>'center'},
$files_list{$type} ) );
}
$filename= unescapeHTML($filename);
print Tr(
td({-align=>"left", -valign=>"top"},
a({-href=>"${download_url}$types[${type}]/${filename}.zip",
-target=>"_new"}, $title ),
),
td({-align=>"right", -valign=>"top"},
size_calc($size),
),
td({-align=>"center", -valign=>"top",},
checkrating($rating, $reviewfile, $id),
),
), "\n";
# adjust loop vars for prettyprint
$rated = $rating;
$oldtype = $type;
};
die $sth->errstr if $sth->err;
}
my $search_obj = escapeHTML( param('searchfor') ) || '';
# un-taint the search object
$search_obj =~ m/([ a-zA-Z-_\[\]\{\}0-9]+)/;
$search_obj = $1;
if (!param() && cgi_error()) {
print header(-status=>cgi_error());
goto FINISH;# don't call exit 0; !!! (unless you LIKE killing your
perl process over and over, ass-hat) :P
}
my $expires = (localtime(time + 30));
print header({'head'=>meta( {-http_equiv=>'Expires', -content=>$expires
} )}),
start_html({-Title=>"FuzzBuster's NaliCity Quick Search!",
-Style=>{-Code=>$newStyle},
-bgcolor=>'#003366',
-text=>'white',
-"link"=>'#99',
-vlink=>'yellow'});
print start_form,
div({-align=>'center'},
h3("Map Search"),
p("Enter the name of a map title or file to search for:"),
textfield(-name=>'searchfor',
-default=>'',
-size=>30,
-maxlength=>68,
-override=>1),
br,
submit(-name=>'Submit', -value=>'Submit'),
),
end_form, hr;
if ( $search_obj eq '' )
{
# skip the database query
print end_html;
goto FINISH;
}
my $query = "SELECT Type, ID, FileName, Title, Size, ReviewFile,
ROUND(Rating, 2)
FROM $map_db
WHERE FileName LIKE '%$search_obj%' OR Title LIKE
'%$search_obj%'
ORDER BY Rating DESC, Type, FileName";
print start_div({-align=>"center"}),
start_table({-border=>"0", -cellpadding=>"0", -cellspacing=>"2",
-width=>"300"});
create_dbi_table($query);
print end_table,
end_div,
hr,
end_html;
FINISH:
# end of code
If anyone is interested I can provide particulars on the maps table in
the MySQL database as well.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: php vs perl again (reposted - original post was incomplete)
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Stephen Clouse) wrote: > Having said all that, is this really on topic for dbi-users? indeed it is, since the script in question is banking heavily upon DBI and DBD::mysql to do the work. -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
RE: php vs perl again (reposted - original post was incomplete)
Well I'm not a web developer, never used mod_perl, there is
the article on Perl.com describing how they built e-toys with
mod_perl. I think at the time it was the 3rd busiest web site
at the time. I don't know if the hardware even compares, but
you can take a look, if you haven't already.
On 24-Oct-01 Scott R. Godin wrote:
> here's a missive fired off by the site admin after he "benchmarked" two
> scripts, one written in php and one written in perl/cgi
>
>>
>> First of all.
>>
>> Dude. you're out of your mind. Im serious.
>>
>> The WHOLE point about why PHP is faster than Perl is because the
>> interpreter is compiled into Apache.
>
> he's not running a perl interpreter compiled into Apache.
>
>> Also. the fact that your script caused MySQL to use up all of it's
>> connections has -nothing- to do with PHP being compiled into Apache.
>
> I find this terribly difficult to believe, but I'm willing to post my
> cgi for review both here and in the DBI list
>
>> We simply do -not- have the hardware to run mod_perl. With our level of
>> traffic, we would need a load balanced cluster to handle this.
>>
>> We skewed nothing. We ran the same apache bench command for both
>> scripts. Same number of concurrent requests, same number of times. Do not
>> confuse ApacheBench with some useless little tool. This is for serious
>> server benchmarking. The fact that we're using gemini table types and
>> your database tables are indexed just further shows the limitations of
>> Perl.
>>
>> You simply don't get it. PHP, in all of it's forms, blows perl out of the
>> water. I've been writing both since early 1993, and in every case, in
>> every instance, PHP crushes perl for speed. That's -why- it was created
>> (build in interpreter). That's -why- Zend released the PHP4 engine.
>> That's -why- we're running Zend Optimizer. If perl was the shit for doing
>> CGI, why would anyone even bother creating things like PHP? That's like
>> the Chewbacca website. It makes no sense.
>>
>> mod_perl is a resource pig. I refuse to install something on a server that
>> will make life miserable for everyone else. I've seen GHz machines hauled
>> off of their foundations because of mod_perl, while the same server
>> running PHP code has no problems whatsoever.
>
> I responded with certain information along these lines:
>
> -=-
>> > If you use CGI.pm in many of your mod_perl scripts, you may want to
>> > preload
>> > CGI.pm and its methods at server startup time. To do this,
>> > add the following line to httpd.conf:
>> >
>> > PerlScript /home/httpd/conf/startup.pl
>> >
>> > Create the file /home/httpd/conf/startup.pl and put in it all the modules
>> > you
>> > want to load. Include CGI.pm among them and call its
>> > compile() method to precompile its autoloaded methods.
>> >
>> > #!/usr/local/bin/perl
>> >
>> > use CGI ();
>> > CGI->compile(':all');
>> >
>> > Change the path to the startup script according to your preferences.
>>
>> if you're gonna benchmark at least do it right.
>>
>> don't flap statistics at my face when you've got sandbags tied around the
>> feet of all my peasants, and shot each one in the foot as well, please.
>>
>> Also, Yoda's script is not performing (and from what I can see, can not
>> perform ) the same query mine was (again skewing the 'benchmark')
>>
>> searching his script for 'c' does not return even the same list of maps mine
>> does. I feel that a certain degree of *accuracy* is also important in a
>> benchmark.
>>
>> I've also gone to the trouble of doing things such as this:
>>
>> my( $type, $id, $filename, $title, $size, $reviewfile, $rating, $rated,
>> $oldtype );
>> $sth->bind_columns(\$type, \$id, \$filename, \$title, \$size,
>> \$reviewfile, \$rating);
>>
>> which binds the results of each return into the same variable reference to
>> save on memory and processing while looping through the fetch, instead of
>> thrashing the symbol table.
>>
>> and other things like this
>>
>> # die with status error if necessary if cgi itself got an error
>> if (!param() && cgi_error()) {
>> print header(-status=>cgi_error());
>> goto FINISH;# don't call exit 0; !!! (unless you LIKE kil
Re: Unwanted error message with DBD::CSV
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Ilya Sterin) wrote: > As much as I shouldn't say this, but run the script without the -w to get > rid of this message, or define a __WARN__ handler. > > Ilya or use local $^W; within the loop -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
php vs perl again (reposted - original post was incomplete)
cess and CGI process (and DBI process) like you DO have a > persistent php and php-with-mysql process, causing perl and CGI and DBI to > re-execute and recompile themselves each time. (what kind of results DID you > expect doing something like this? =:P) > > C> The accuracy of the returned result of the query as performed by each > script is also in question. (look at the result count reported by each script > as to how many maps it returned from the query, and tell me something's not > wrong with one of them. =:P) > > try again. -=- here's the "benchmark" result he returned to me We benched your script using ApacheBench. We let it run with 100 concurrent connections. A couple of things happened. 1. MySQL died with a "too many connections" error. Our forums, with 150 users on them, can't even do that. 2. The load average on the box jumped to 11. Not 1 or 2. 11. 3. Yoda has written a PHP search engine which already incorporates all of the advanced features for NC. His script ran 229 times faster than yours, and the load average never moved above 0.5. MySQL was also perfectly fine, after being benched under the same conditions. I strongly recommend at this point that you do not use Perl for Nalicity. I have included the results of our benchmarks (performed by Chris), so you can see for yourself. This sort of load would be unacceptible in the BU environment. Begin ab log: Yoda's version: [root@beyondunreal bin]# ./ab -n100 -c10 -k http://nalicity.beyondunreal.com/testbed/news2.php?executesearch=1&search by_titles=on&tSearchText=c&sortby=2&sorttype=1 This is ApacheBench, Version 1.3c <$Revision: 1.45 $> apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2000 The Apache Group, http://www.apache.org/ Server Software:Apache/1.3.22 Server Hostname:nalicity.beyondunreal.com Server Port:80 Document Path: /testbed/news2.php?executesearch=1 Document Length:769 bytes Concurrency Level: 10 Time taken for tests: 0.574 seconds Complete requests: 100 Failed requests:0 Keep-Alive requests:0 Total transferred: 97206 bytes HTML transferred: 78438 bytes Requests per second:174.22 Transfer rate: 169.35 kb/s received Connnection Times (ms) min avg max Connect:1 517 Processing:2044 244 Total: 2149261 Fuzzbuster's version: [root@beyondunreal bin]# ./ab -n100 -c10 -k http://nalicity.beyondunreal.com/cgi-bin/simplesearch.cgi?searchfor=c This is ApacheBench, Version 1.3c <$Revision: 1.45 $> apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2000 The Apache Group, http://www.apache.org/ Server Software:Apache/1.3.22 Server Hostname:nalicity.beyondunreal.com Server Port:80 Document Path: /cgi-bin/simplesearch.cgi?searchfor=c Document Length:698276 bytes Concurrency Level: 10 Time taken for tests: 115.382 seconds Complete requests: 100 Failed requests:0 Keep-Alive requests:0 Total transferred: 71242100 bytes HTML transferred: 71215208 bytes Requests per second:0.87 Transfer rate: 617.45 kb/s received Connnection Times (ms) min avg max Connect:1 122 3017 Processing: 9771 11087 11502 Total: 9772 11209 14519 I'm willing to post my script here to see if any of you individuals can tell me what, if anything, I did wrong with MY script that could have caused MySQL to die with "too many connections" or whether this is a problem with DBI and DBD::MySQL in its present form. I await your response. (with heavy sighs and a great deal of frustration) -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
php vs perl again
here's a missive fired off by the site admin after he "benchmarked" two
scripts, one written in php and one written in perl/cgi
>
> First of all.
>
> Dude. you're out of your mind. Im serious.
>
> The WHOLE point about why PHP is faster than Perl is because the
> interpreter is compiled into Apache.
he's not running a perl interpreter compiled into Apache.
> Also. the fact that your script caused MySQL to use up all of it's
> connections has -nothing- to do with PHP being compiled into Apache.
I find this terribly difficult to believe, but I'm willing to post my
cgi for review both here and in the DBI list
> We simply do -not- have the hardware to run mod_perl. With our level of
> traffic, we would need a load balanced cluster to handle this.
>
> We skewed nothing. We ran the same apache bench command for both
> scripts. Same number of concurrent requests, same number of times. Do not
> confuse ApacheBench with some useless little tool. This is for serious
> server benchmarking. The fact that we're using gemini table types and
> your database tables are indexed just further shows the limitations of
> Perl.
>
> You simply don't get it. PHP, in all of it's forms, blows perl out of the
> water. I've been writing both since early 1993, and in every case, in
> every instance, PHP crushes perl for speed. That's -why- it was created
> (build in interpreter). That's -why- Zend released the PHP4 engine.
> That's -why- we're running Zend Optimizer. If perl was the shit for doing
> CGI, why would anyone even bother creating things like PHP? That's like
> the Chewbacca website. It makes no sense.
>
> mod_perl is a resource pig. I refuse to install something on a server that
> will make life miserable for everyone else. I've seen GHz machines hauled
> off of their foundations because of mod_perl, while the same server
> running PHP code has no problems whatsoever.
I responded with certain information along these lines:
-=-
> > If you use CGI.pm in many of your mod_perl scripts, you may want to preload
> > CGI.pm and its methods at server startup time. To do this,
> > add the following line to httpd.conf:
> >
> > PerlScript /home/httpd/conf/startup.pl
> >
> > Create the file /home/httpd/conf/startup.pl and put in it all the modules
> > you
> > want to load. Include CGI.pm among them and call its
> > compile() method to precompile its autoloaded methods.
> >
> > #!/usr/local/bin/perl
> >
> > use CGI ();
> > CGI->compile(':all');
> >
> > Change the path to the startup script according to your preferences.
>
> if you're gonna benchmark at least do it right.
>
> don't flap statistics at my face when you've got sandbags tied around the
> feet of all my peasants, and shot each one in the foot as well, please.
>
> Also, Yoda's script is not performing (and from what I can see, can not
> perform ) the same query mine was (again skewing the 'benchmark')
>
> searching his script for 'c' does not return even the same list of maps mine
> does. I feel that a certain degree of *accuracy* is also important in a
> benchmark.
>
> I've also gone to the trouble of doing things such as this:
>
> my( $type, $id, $filename, $title, $size, $reviewfile, $rating, $rated,
> $oldtype );
> $sth->bind_columns(\$type, \$id, \$filename, \$title, \$size,
> \$reviewfile, \$rating);
>
> which binds the results of each return into the same variable reference to
> save on memory and processing while looping through the fetch, instead of
> thrashing the symbol table.
>
> and other things like this
>
> # die with status error if necessary if cgi itself got an error
> if (!param() && cgi_error()) {
> print header(-status=>cgi_error());
> goto FINISH;# don't call ex
status of DBD::mysql ?
I notice from the docs that come with DBD-mysql-2.0902, and in particular the Makefile.PL there is the warning: print <<"MSG"; This is an experimental version of DBD::mysql. For production environments you should prefer the Msql-Mysql-modules. MSG ...and I notice that the last update was in may of this year.. is this still a truism, and is Jochen still updating and maintaining the module? I haven't seen him posting anywhere recently, in the places I normally read up on Perl and DBI, so I'm not certain whether he is still actively working on the module. Can anyone clue me in? -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
FW: Re: FW: Commit - not working 1.20 (Proxy)
Just in case anybody needs this patch. This fixes the commit in DBD::Proxy
for DBI-1.20. Tim will have in the next DBI release.
-FW: <[EMAIL PROTECTED]>-
Date: Fri, 12 Oct 2001 10:43:07 +0200
From: Jochen Wiedmann <[EMAIL PROTECTED]>
To: "Scott T. Hildreth" <[EMAIL PROTECTED]>
Subject: Re: FW: Commit - not working 1.20 (Proxy)
Try the following patch.
--- Proxy.pmFri Aug 24 22:05:44 2001
+++ c:\Perl\site\lib\DBD\Proxy.pm Mon Sep 17 13:49:45 2001
@@ -175,7 +175,8 @@
'CachedKids' => 'local',
'PrintError' => 'local',
'RaiseError' => 'local',
-'RowCacheSize' => 'inherited'
+'RowCacheSize' => 'inherited',
+'AutoCommit' => 'cached'
);
sub AUTOLOAD {
@@ -237,9 +238,10 @@
return 1;
}
-if ($type eq 'remote') {
+if ($type eq 'remote' || $type eq 'cached') {
my $result = eval { $dbh->{'proxy_dbh'}->STORE($attr => $val) };
return DBI::set_err($dbh, 1, $@) if $@; # returns undef
+
$dbh->{$attr} = $val if $type eq 'cached';
return $result;
}
return $dbh->SUPER::STORE($attr => $val);
@@ -249,7 +251,8 @@
my($dbh, $attr) = @_;
my $type = $ATTR{$attr} || 'remote';
-if ($attr =~ /^proxy_/ || $type eq 'inherited') {
+if ($attr =~ /^proxy_/ || $type eq 'inherited' ||
+
$type eq 'cached') {
return $dbh->{$attr};
}
--End of forwarded message-
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 23-Oct-01
Time: 12:03:51
--
Elusive Syntax
Anyone know how I can do this? I can't seem to get past a syntax error
near the 'while'. (it worked up to the point I tried to put it into a
table, and the table works fine by itself)
use CGI qw/:standard *table start_ul/;
require DBI;
require HTTP::Date;
.
print table({-border=>undef},
caption('Contacts'),
Tr({-align=>CENTER,-valign=>TOP},
[
th(['First Name','Last Name','Title','Company','Work Ph.','Home Ph.',
'Fax Number','Other Ph.','Email Addr.','City','Prov.','Postal',
'Country','Custom 1','Custom 2','Custom 3','Custome 4','Notes',
'Category']),
while ( @columns = $cursor->fetchrow ) {
td([
'$columns[0]', '$columns[1]', '$columns[2]', '$columns[3]',
'$columns[4]', '$columns[5]', '$columns[6]', '$columns[7]',
'$columns[8]', '$columns[9]', '$columns[10]','$columns[11]',
'$columns[12]','$columns[13]','$columns[14]','$columns[15]',
'$columns[16]','$columns[17]','$columns[18]','$columns[19]',
'$columns[20]'])
}
]
)
);
--
Scott Taylor
Systems Administrator
DCT Chambers Trucking Ltd.
CSV-SQL convertion
Hello,
Does anyone have a routine to convert from a csv file to an sql database,
that works. I'm having a hard time with it.
here is what I have so far: (almost works)
#!/usr/bin/perl -w
#
use DBI;
require Text::CSV_XS;
my $csv = Text::CSV_XS->new;
# bunch of my$ stuff and dbi:InterBase:db connection stuff
# dialect=3
...
open(InFile, $infile) || die "Can not open text file: $!\n";
while ($line = ){
if ($csv->parse($line)) {
$newrec = '';
my @field = $csv->fields;
$SQL = qq[INSERT INTO rdx_data VALUES (
'$field[0]', '$field[1]', '$field[2]', '$field[3]',
'$field[4]', '$field[5]', '$field[6]', '$field[7]',
'$field[8]', '$field[9]', '$field[10]', '$field[11]',
'$field[12]', '$field[13]', '$field[14]', '$field[15]',
'$field[16]', '$field[17]', '$field[18]', '$field[19]',
'$field[20]')];
$cursor = $dbh->prepare($SQL) or die print $SQL, "\n";
$cursor->execute;
}
}
$cursor->finish;
$dbh->disconnect;
close(InFile);
#EOF
most of the data gets in the Interbase DB but some is missing, and I get a
bunch of these error messages:
DBD::InterBase::st execute failed: Arithmetic overflow or division by zero
has occurred.
-arithmetic exception, numeric overflow, or string truncation
Is my punctuation bad or what? I thought it would put text into text
fields. Why would it be doing any math or string manipulation at this point?
Flamage for being a lousy, newbie, Perl wannabe hacker, and posting in this
forum, is acceptable. But any help is really appreciated.
Oh, also having a hard time with the Text::CSV_XS module not quite
following all the rules, is there a better way, or another module to use
instead (it's most up-to-date from CPAN v0.23). What I can't get it to do
is recognize a multi-line field, ie:
field1_text, field2_text, "field3_line1^M
field3_line2^M
field3_line3",field4_text,...
I even tried stripping out the doze (^M). However, I'm not so
concerned about that part, just in case someone has some insight on that.
TIA
Scott
Re: CGIwrap?
I just love how some people's reply-ing to posted messages on the list forces their new topic under the old thread via the references, so if you get bored with a post, and mark the thread as read, it skips all the new posts with new subjects below it that got trapped by the thread references, Usually resulting in their never recieving replies to their missive. Far better to post to the list from a new message and add the mailing list address to your addressbook instead. just my $.02 -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
RE: Proxy Connect
Okay, I read DBI doc and saw that the DBI_AUTOPROXY is
of the form 'hostname=?;port=?' and not the way I had it.
I thought I had read before that the syntax was in the
form below, but then I was wrong wasn't I :-)
Sorry for wasting time,
STH
On 11-Oct-01 Scott T. Hildreth wrote:
>
> Tim,
>
> A couple months back I posted to dbi-users asking if anyone
> had the following error when using the below method for
> connecting to the Database.
>
> $ENV{DBI_AUTOPROXY} = 'dbi:Proxy:hostname=srv1;port=';
> my $dbh = DBI->connect( "dbi:Oracle:sid", "user", "passwd",
> {RaiseError => 1} ) or die $DBI::errstr;
>
>
> Proxy.pm would complain that
> that hostname was not defined. I debugged the code and
> saw that the $dsn going into Proxy.pm as
>
> dbi:Proxy:hostname=srv1;port=;dsn=dbi:Oracle:sid
>
> ..to fix this I put the line $dsn =~ s/dbi:Proxy://i;
>in Proxy.pm and it worked. Yet I was the only one
>that was having this problem. So I finally got tired
>of changing Proxy.pm everytime I installed DBI and
>RTFM for DBD::Proxy. When I tried the connect method,
>
> DBI->connect("dbi:Proxy:hostname=srv1;port=;dsn=dbi:Oracle:sid"..etc
>
> Proxy.pm recieves the dsn of
>
>hostname=srv1;port=;dsn=dbi:Oracle:sid
>
> ..which it expects. I prefer setting the DBI_AUTOPROXY variable,
> leaving my original connect alone. So my question is should
> the patch involve fixing DBI.pm, the Proxy.pm fix I had done
> already or either fix is okay?
>
> Possible DBI fix,
>
> if ($ENV{DBI_AUTOPROXY} && $driver ne 'Proxy' && $driver ne 'Switch') {
> $dsn = "$ENV{DBI_AUTOPROXY};dsn=dbi:$driver:$dsn";
> $dsn =~ s/dbi:proxy://i;
> $driver = 'Proxy';
> DBI->trace_msg(" DBI_AUTOPROXY: dbi:$driver:$dsn\n");
> }
>
>
>STH
>
>
>
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 11-Oct-01
> Time: 16:59:27
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 12-Oct-01
Time: 10:04:23
--
RE: Anyone using PostgreSQL?
> All - Just curious if anyone is successfully using DBI with > PostgreSQL under > Linux? I don't see a lot of traffic about it and may need to use > it in the > near future. Mark, I think the lack of traffic is a testament to how well it works :) I've used DBD::Pg on Linux (and more recently, W2K) for over 2 years. - Scott Scott Scecina In Mind, Inc.
Proxy Connect
Tim,
A couple months back I posted to dbi-users asking if anyone
had the following error when using the below method for
connecting to the Database.
$ENV{DBI_AUTOPROXY} = 'dbi:Proxy:hostname=srv1;port=';
my $dbh = DBI->connect( "dbi:Oracle:sid", "user", "passwd",
{RaiseError => 1} ) or die $DBI::errstr;
Proxy.pm would complain that
that hostname was not defined. I debugged the code and
saw that the $dsn going into Proxy.pm as
dbi:Proxy:hostname=srv1;port=;dsn=dbi:Oracle:sid
..to fix this I put the line $dsn =~ s/dbi:Proxy://i;
in Proxy.pm and it worked. Yet I was the only one
that was having this problem. So I finally got tired
of changing Proxy.pm everytime I installed DBI and
RTFM for DBD::Proxy. When I tried the connect method,
DBI->connect("dbi:Proxy:hostname=srv1;port=;dsn=dbi:Oracle:sid"..etc
Proxy.pm recieves the dsn of
hostname=srv1;port=;dsn=dbi:Oracle:sid
..which it expects. I prefer setting the DBI_AUTOPROXY variable,
leaving my original connect alone. So my question is should
the patch involve fixing DBI.pm, the Proxy.pm fix I had done
already or either fix is okay?
Possible DBI fix,
if ($ENV{DBI_AUTOPROXY} && $driver ne 'Proxy' && $driver ne 'Switch') {
$dsn = "$ENV{DBI_AUTOPROXY};dsn=dbi:$driver:$dsn";
$dsn =~ s/dbi:proxy://i;
$driver = 'Proxy';
DBI->trace_msg(" DBI_AUTOPROXY: dbi:$driver:$dsn\n");
}
STH
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 11-Oct-01
Time: 16:59:27
--
RE: Problems installing DBD::mysql
t; DIED. FAILED tests 1-35 > Failed 35/35 tests, 0.00% okay > t/50commit..install_driver(mysql) failed: Can't load > '../blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: Shared object > "libmysqlclient.so.10" not found at /usr/libdata/perl/5.00503/DynaLoader.pm > line 169. > > at (eval 1) line 3 > Perhaps a required shared library or dll isn't installed where expected > at t/50commit.t line 64 > dubious > Test returned status 255 (wstat 65280, 0xff00) > DIED. FAILED tests 1-16 > Failed 16/16 tests, 0.00% okay > t/60leaks...skipping test on this platform > t/ak-dbdinstall_driver(mysql) failed: Can't load > '../blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: Shared object > "libmysqlclient.so.10" not found at /usr/libdata/perl/5.00503/DynaLoader.pm > line 169. > > at (eval 1) line 3 > Perhaps a required shared library or dll isn't installed where expected > at t/ak-dbd.t line 59 > dubious > Test returned status 255 (wstat 65280, 0xff00) > DIED. FAILED tests 1-90 > Failed 90/90 tests, 0.00% okay > t/dbdadmin..install_driver(mysql) failed: Can't load > '../blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: Shared object > "libmysqlclient.so.10" not found at /usr/libdata/perl/5.00503/DynaLoader.pm > line 169. > > at (eval 1) line 3 > Perhaps a required shared library or dll isn't installed where expected > at t/dbdadmin.t line 60 > dubious > Test returned status 255 (wstat 65280, 0xff00) > DIED. FAILED tests 1-20 > Failed 20/20 tests, 0.00% okay > Failed Test Status Wstat Total Fail Failed List of failed > > --- > t/00base.t 255 65280 52 40.00% 4-5 > t/10dsnlist.t 255 65280 33 100.00% 1-3 > t/20createdrop. 255 65280 55 100.00% 1-5 > t/30insertfetch 255 6528011 11 100.00% 1-11 > t/40bindparam.t 255 6528028 28 100.00% 1-28 > t/40blobs.t 255 6528011 11 100.00% 1-11 > t/40listfields. 255 6528016 16 100.00% 1-16 > t/40nulls.t 255 6528011 11 100.00% 1-11 > t/40numrows.t 255 6528025 25 100.00% 1-25 > t/50chopblanks. 255 6528035 35 100.00% 1-35 > t/50commit.t255 6528016 16 100.00% 1-16 > t/ak-dbd.t 255 6528090 90 100.00% 1-90 > t/dbdadmin.t255 6528020 20 100.00% 1-20 > 1 test skipped. > Failed 13/14 test scripts, 7.14% okay. 273/276 subtests failed, 1.09% okay. > *** Error code 2 > > Stop in /usr/local/www/install/Msql-Mysql-modules-1.2215/mysql. > *** Error code 1 > > Stop in /usr/local/www/install/Msql-Mysql-modules-1.2215. > mysql3# make install > Installing > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/DBD/mysql/mysql.so > Installing > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/DBD/mysql/mysql.bs > Files found in blib/arch --> Installing files in blib/lib into architecture > dependend library tree! > Installing /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/DBD/mysql.pm > Installing > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/Bundle/DBD/mysql.pm > Installing > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/Mysql/Statement.pm > Installing /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/Mysql.pm > Installing /usr/local/man/man1/dbimon.1 > Installing /usr/local/lib/perl5/5.00503/man/man3/DBD::mysql.3 > Installing /usr/local/lib/perl5/5.00503/man/man3/Bundle::DBD::mysql.3 > Installing /usr/local/lib/perl5/5.00503/man/man3/Mysql.3 > Installing /usr/bin/dbimon > Writing > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/Msql-Mysql-modules/.p > acklist > Appending installation info to /usr/libdata/perl/5.00503/mach/perllocal.pod > > Have a nice day ... > > Sincerely, > > Mike Cherichetti, CTO > Advanced Resource Computing Services > Phone (256)828-8921 > ___ > > Internet Advertising Delivery Solutions > www.advertpro.com > ___ > > Web Design, Hosting, Promotion > www.arc-s.com > ___ > -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 08-Oct-01 Time: 06:52:48 --
Re: [repost] DBD::CSV and csv_eol=anything
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Jeff Zucker) wrote:
> "Scott R. Godin" wrote:
> >
> > unable to set ;csv_eol=\015, but saving the file via bbedit to DOS
> > instead of Macintosh, the code works?!? what the hell?
>
> AFAIK,
>
> 1. If you are on a MAC and have all MAC-formatted files, don't set
> csv_eol at all, DBD::CSV should do the right thing.
unfortunately this is not the case, as in CSV.pm there is the following
line:
$opts{'eol'} = $meta->{'eol'} || $dbh->{'csv_eol'} || "\015\012";
so, for a Mac file I need to set it explicitly.
> 2. If you are on a MAC and have all DOS formatted files, set csv_eol to
> \015\012.
Since this is the default (see above) it isn't necessary. As long as the
file has DOS linefeeds, setting to \015\012 or not setting this at all
results in usable data access.
> 3. If you are on a MAC and have mixed files
>
>a. convert them all to one format before processing (recommended)
>
>or
>
>b. for the files that you know are DOS formatted, set csv_eol to
> \015\012 and
> do not set it all for the MAC formatted files
>
> > All well and good, except that the file is generated by a whole suite of
> > perl scripts
>
> So use the scripts to put a MAC eol in the file and forget about csv_eol
> in the report generating scripts.
Well the issue here is one of independance between Mac and Unix -- if
the file is generated on the Mac, I should be able to set csv_eol to
\015 and have DBI 'do the right thing', likewise with unix setting
csv_eol to \012
now, if DBI is creating the table, all is well and good -- the present
setup creates files with \015\012 and as long as you DO NOT set csv_eol,
everything is fine. It's when you are creating the "database files" with
other applications and expect it (DBI and DBD::CSV) to do what it says
it's supposed to, that things go flooey.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBI Version Problem
Sounds like it is like FreeBSD, which has perl libs installed in /usr/libdata/perl. DBI probably installed into /usr/local/lib/perl5 or /usr/lib/perl5. You could upgrade perl to 5.6.1 and install it into your directory of choice and have a newer version of perl :-) On 03-Oct-01 Alex Kirk wrote: >> I vaguely recall that the directory DBI gets installed in changed >> slightly at some point, so you have to manually delete all traces >> of the old DBI, especially the parts in those 'auto' directories >> under the lib directory. > > Any idea what typically gets installed? It's kind of hard to weed out the > old without knowing where it is. > > Thanks, > Alex > -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 03-Oct-01 Time: 14:42:25 --
[repost] DBD::CSV and csv_eol=anything
the list mysteriously and completely disappeared for roughly 10 minutes,
and I wasn't sure if my post was received or not. *head-scratching*
-=-
unable to set ;csv_eol=\015, but saving the file via bbedit to DOS
instead of Macintosh, the code works?!? what the hell?
setting csv_eol=\015 when file format is saved as Macintosh results in
this error, searching for "test":
DBD::CSV::st execute failed: Missing first row at Primus
8.5GB:Applications:MacPerl 5.6.1a4 ü:site_perl:DBD:CSV.pm line 157,
line 1.
o if fileformat is DOS and csv_eol=\015, it searches the file but
finds 0 results
o if fileformat is DOS and I SET csv_eol=\015\012 it FINDS THE 7
RESULTS PROPERLY O_o
i.e. it's only confused SOMEtimes. Somewhere, it's assuming the file is
a DOS file, even though I'm setting csv_eol properly, the question is
WHERE?
my $dbh =
DBI->connect("dbi:CSV:f_dir=${dbi_connect_dir};csv_sep_char=\t;csv_eol=\0
15\012", '', '', { RaiseError => 2 })
or die "Can't connect to database: $DBI::errstr";
All well and good, except that the file is generated by a whole suite of
perl scripts running under MacPerl 5.6.1a*, (a mysql table output by SQL
embedded in an .asp page, formatted to an html table, parsed by
HTML::Parser, and tested for integrity by script # 3) and is read for
report-generation by something like 10 other scripts.
I don't want to go back through all the scripts and change the input and
output record separators unless I absolutely have to, considering one
single csv_eol=\015 is supposed to solve the problem :)
DBI 1.20
DBD::CSV 0.1027
SQL::Statement 0.1020
Text::CSV_XS 0.22
MacPerl 5.6.1a4
I can post the complete script and a smidge of sample data if you wish
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Select X number of rows
Hello, I am a Perl guy, not a DBA. Anyway, if I have a couple of DBs with X amount of records, can I select from three databases, acquire the first 20 records (sort by ASC), then (show them on the web) and when they hit next, show the next 20, eg. 21-40 and so on and so on. Also, If that is doable, how do I know how many pages. Is there some command in SQL that would tell me how many records are in the three DBS without me having to acquire and count all the records first? I hope this is not too much of a SQL question, but as I am building this in Perl. I hope I do not offend anyone with this morning off-perl question. Thanks you very much, Scott Purcell
Re: General Question DBI
Thanks
"Sterin, Ilya" wrote:
>
> Well it depends, though nothing will hand unless there is a bug or you make
> it hang.
>
> If you set RaiseError => 1, then the program will exit on error in any
> subroutine. You can catch your errors using eval and therefore do anything
> you want when an error is generated. I still don't see why you would think
> that this will hang.
>
> If your error checking is not turned on, the PrintError is turned on by
> default and will generate warnings and keep going, though it will finally
> fail, since if for example a prepare call fails it returns undef as a
> statement handle and therefore the next call to it, will generate a perl
> error stating that it can't call a method on an undefined object.
>
> Ilya
>
> > -Original Message-
> > From: Anthony Scott [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, September 24, 2001 5:49 PM
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: General Question DBI
> >
> >
> > No, not a diffrent process but will the current process hang due
> > to error in the
> > subroutine test1. Another example, if test1 had an infinite loop
> > I would expect
> > $dbh process to remain open, right? I'm just polling the group to
> > find out if
> > anyone writing code like this and what are the pit-fall :(
> >
> >
> > Anthony Scott
> >
> > "Sterin, Ilya" wrote:
> > >
> > > Not sure what you mean. You are reusing a global handle, if
> > you mean will
> > > there be a memory leak, no since everything is deallocated at end of
> > > execution. Otherwise I am not exactly sure, why you would think that a
> > > different process is created:-?
> > >
> > > Ilya
> > >
> > > > -Original Message-
> > > > From: Anthony Scott [mailto:[EMAIL PROTECTED]]
> > > > Sent: Monday, September 24, 2001 4:39 PM
> > > > Cc: [EMAIL PROTECTED]
> > > > Subject: General Question DBI
> > > >
> > > >
> > > >
> > > >
> > > > if I have the following code
> > > >
> > > >
> > > > sub test {
> > > >
> > > >
> > > > $dbh =get_dbh;
> > > >
> > > >
> > > >
> > > > test1($dbh,$somedata);
> > > >
> > > >
> > > > close($dbh);
> > > >
> > > >
> > > > }
> > > >
> > > >
> > > > sub test1 {
> > > >
> > > > my ($dbh,$data)=@_;
> > > >
> > > >
> > > > #sql code
> > > >
> > > >
> > > > }
> > > >
> > > >
> > > > if the $sql code fails in subroutine "test1" does it causes a
> > > > open process
> > > > since I defined $dbh as local?
> > > >
> > > >
> > > >
> > > > Anthony Scott
Re: General Question DBI
No, not a diffrent process but will the current process hang due to error in the
subroutine test1. Another example, if test1 had an infinite loop I would expect
$dbh process to remain open, right? I'm just polling the group to find out if
anyone writing code like this and what are the pit-fall :(
Anthony Scott
"Sterin, Ilya" wrote:
>
> Not sure what you mean. You are reusing a global handle, if you mean will
> there be a memory leak, no since everything is deallocated at end of
> execution. Otherwise I am not exactly sure, why you would think that a
> different process is created:-?
>
> Ilya
>
> > -Original Message-
> > From: Anthony Scott [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, September 24, 2001 4:39 PM
> > Cc: [EMAIL PROTECTED]
> > Subject: General Question DBI
> >
> >
> >
> >
> > if I have the following code
> >
> >
> > sub test {
> >
> >
> > $dbh =get_dbh;
> >
> >
> >
> > test1($dbh,$somedata);
> >
> >
> > close($dbh);
> >
> >
> > }
> >
> >
> > sub test1 {
> >
> > my ($dbh,$data)=@_;
> >
> >
> > #sql code
> >
> >
> > }
> >
> >
> > if the $sql code fails in subroutine "test1" does it causes a
> > open process
> > since I defined $dbh as local?
> >
> >
> >
> > Anthony Scott
General Question DBI
if I have the following code
sub test {
$dbh =get_dbh;
test1($dbh,$somedata);
close($dbh);
}
sub test1 {
my ($dbh,$data)=@_;
#sql code
}
if the $sql code fails in subroutine "test1" does it causes a open process
since I defined $dbh as local?
Anthony Scott
Oracle DBD stop working
Since today I have been getting the following error message. Software error: Can't load '/home/test/public_html/cgi-bin/perlmod//sun4-solaris/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: ld.so.1: /opt/exptools/bin/perl: fatal: relocation error: file /home/test/public_html/cgi-bin/perlmod//sun4-solaris/auto/DBD/Oracle/Oracle.so: symbol Perl_no_modify: referenced symbol not found at /opt/exp/perl/lib/5.6.1/sun4-solaris/DynaLoader.pm line 206. Can anyone tell me what it means? I have re-intalled DBD Oracle-1-07 and DBI 1-18 but the error remain from the web interface. Anthony Scott
RE: Strange error when doing insert into DB
Can we see some code? The error is, what it states, you are mixing placeholders. i.e. $dbh->prepare(select * from foo where bar = ? and foobar = :p1); ..check the code for :"string" and :number mixture in a statement. On 14-Sep-01 Riyaad Miller - MWeb wrote: > Hi ALL > > Has anyone come across the following error: > "Can't mix placeholder styles (:foo/:1) at > /usr/local/lib/perl5/site_perl/DBD/Oracle.pm line 293, chunk > 1085." > > Unix OS - SunOS 5.6 > Oracle - 7 > > The problem does multiple actions ranging from updates, insert and of course > selects from my DB. > Your help would be appreciated! > Thanks ... > Regards Riyaad. -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 14-Sep-01 Time: 08:29:30 --
RE: DBD::Oracle 1.09 - minor problems - needs DBI 1.20
>> Not Ok ... Oracle 8.1.5 - OSF
>> t/general...FAILED test 15
>> Failed 1/17 tests, 94.12% okay
>> t/ph_type...Can't set DBI::db=HASH(0x1400138a0)->{FetchHashKeyName}:
>> unrecognised attribute
>
> Upgrading the DBI to the latest will fix those.
> (I should have made it explicitly require DBI 1.20.)
As suspected, upgrading to DBI 1.20 fixed it, make test worked.
> I'll wait for some more feedback and put out a new release after that.
>
> Tim.
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 30-Aug-01
Time: 08:35:58
--
RE: Keeping a connection open accross a fork
look in the archives under Fork & InactiveDestroy.
On 30-Aug-01 Jay Strauss wrote:
> Hi,
>
> I'm trying to write a daemon that accesses my database. I thought I could
> create the connection in the parent, and use it in the child. But that
> doesn't seem to work. Below is the code. Any help would be appreciated
>
> Thanks
> Jay
>
>#!/usr/bin/perl -w
>
> use strict;
> use DBI;
> use POSIX qw(setsid);
>
> $|=1;
>
> my $service = "o817";
> my $userid = "jstrauss";
> my $passwd = "passwd";
>
> my $dbh = DBI->connect("dbi:Oracle:$service","$userid","$passwd",
>{ RaiseError => 1, AutoCommit => 0 }) or
>die "Can't connect to Oracle database: $DBI::errstr\n";
>
> my $sth = $dbh->prepare("select fromuser, touser from refresh");
>
> &daemonize;
>
> while(1) {
>print "Hello...\n";
>
>$sth->execute;
>
>while (my @row = $sth->fetchrow_array ) {
> print "$row[0] - $row[1]\n";
>}
>
>sleep(5);
> }
>
> sub daemonize {
>chdir '/' or die "Can't chdir to /: $!";
>open STDIN, '/dev/null' or die "Can't read /dev/null: $!";
>open STDERR, '>>/dev/null' or die "Can't write to /dev/null: $!";
> defined(my $pid = fork) or die "Can't fork: $!";
>exit if $pid;
>setsidor die "Can't start a new session: $!";
>umask 0;
> }
>
>
>
> _
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 30-Aug-01
Time: 08:27:49
--
RE: binding cursors
nsl -ldl -lm -lc -lcrypt > libc=/lib/libc-2.1.92.so, so=so, useshrplib=false, libperl=libperl.a > Dynamic Linking: > dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-rdynamic' > cccdlflags='-fPIC', lddlflags='-shared -L/usr/local/lib' > > > Characteristics of this binary (from libperl): > Compile-time options: > Built under linux > Compiled at Aug 7 2000 10:59:51 > @INC: > /usr/lib/perl5/5.6.0/i386-linux > /usr/lib/perl5/5.6.0 > /usr/lib/perl5/site_perl/5.6.0/i386-linux > /usr/lib/perl5/site_perl/5.6.0 > /usr/lib/perl5/site_perl >. > > DBI-1.15 > DBD-Oracle-1.06 > > Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production > JServer Release 8.1.7.1.0 - Production -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 24-Aug-01 Time: 11:47:17 --
Compiling DBI in Perl on SCO OSR5.0.5
Here is the error messages I get when trying to compile DBI on an SCO OSR5.0.5 compile of Perl5.005. Does anyone know what I'm missing or how I can get the DBI module into my Perl install? (Perl and cc info. at end of message) Thanks. Scott. Output of perl Makefile.PL: (information about optional modules from CPAN) Checking if your kit is complete... Looks good Writing Makefile for DBI Output of Make: mkdir blib mkdir blib/lib mkdir blib/arch mkdir blib/arch/auto mkdir blib/arch/auto/DBI mkdir blib/lib/auto mkdir blib/lib/auto/DBI mkdir blib/man1 mkdir blib/man3 cp lib/DBI/W32ODBC.pm blib/lib/DBI/W32ODBC.pm cp lib/DBD/ExampleP.pm blib/lib/DBD/ExampleP.pm cp lib/DBI/FAQ.pm blib/lib/DBI/FAQ.pm cp lib/DBI/Shell.pm blib/lib/DBI/Shell.pm cp lib/DBI/ProxyServer.pm blib/lib/DBI/ProxyServer.pm cp lib/Bundle/DBI.pm blib/lib/Bundle/DBI.pm cp lib/DBD/Proxy.pm blib/lib/DBD/Proxy.pm cp lib/DBD/Multiplex.pm blib/lib/DBD/Multiplex.pm cp DBIXS.h blib/arch/auto/DBI/DBIXS.h cp dbd_xsh.h blib/arch/auto/DBI/dbd_xsh.h cp dbi_sql.h blib/arch/auto/DBI/dbi_sql.h cp lib/DBD/NullP.pm blib/lib/DBD/NullP.pm cp lib/DBD/Sponge.pm blib/lib/DBD/Sponge.pm cp lib/DBI/Format.pm blib/lib/DBI/Format.pm cp Driver.xst blib/arch/auto/DBI/Driver.xst cp lib/DBI/DBD.pm blib/lib/DBI/DBD.pm cp dbipport.h blib/arch/auto/DBI/dbipport.h cp lib/Win32/DBIODBC.pm blib/lib/Win32/DBIODBC.pm cp DBI.pm blib/lib/DBI.pm cp lib/DBD/ADO.pm blib/lib/DBD/ADO.pm /usr/bin/perl -p -e "s/~DRIVER~/Perl/g" < blib/arch/auto/DBI/Driver.xst > Perl.x si /usr/bin/perl -I/usr/local/lib/perl5/5.00503/i386-sco -I/usr/local/lib/perl5/5.0 0503 /usr/local/lib/perl5/5.00503/ExtUtils/xsubpp -typemap /usr/local/lib/perl5 /5.00503/ExtUtils/typemap Perl.xs >xstmp.c && mv xstmp.c Perl.c cc -c -U M_XENIX -D PERL_SCO -D PERL_SCO5 -w0 -belf -I/usr/local/include -O0 -DVERSION=\"1.19\" -DXS_VERSION=\"1.19\" -Kpic -I/usr/local/lib/perl5/5.00503/i 386-sco/CORE -DDBI_NO_THREADS Perl.c cc: unrecognized option `-w0' cc: unrecognized option `-Kpic' cpp: -lang-c: linker input file unused since linking not done cc: installation problem, cannot exec `cc1': No such file or directory cc: file path prefix `/usr/local/lib/gcc-lib/elf/2.95.2/' never used make: *** [Perl.o] Error 1 Output of Perl -V: Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration: Platform: osname=sco, osvers=3.2v5.0.5, archname=i386-sco uname='sco_sv charmstr 3.2 5.0.5 i386 ' hint=recommended, useposix=true, d_sigaction=define usethreads=undef useperlio=undef d_sfio=undef Compiler: cc='cc', optimize='-O0', gccversion= cppflags='-U M_XENIX -D PERL_SCO -D PERL_SCO5 -w0 -belf -I/usr/local/include' ccflags ='-U M_XENIX -D PERL_SCO -D PERL_SCO5 -w0 -belf -I/usr/local/include' stdchar='unsigned char', d_stdstdio=undef, usevfork=false intsize=4, longsize=4, ptrsize=4, doublesize=8 d_longlong=undef, longlongsize=, d_longdbl=define, longdblsize=12 alignbytes=4, usemymalloc=y, prototype=define Linker and Libraries: ld='cc', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib libs=-lintl -lsocket -lnsl -lndbm -lgdbm -ldbm -ldb -lm -lcrypt -lPW -lx libc=/lib/libc.so, so=so, useshrplib=true, libperl=libperl5.so Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Bexport -L/usr/local/lib' cccdlflags='-Kpic', lddlflags='-G -L/usr/local/lib' Characteristics of this binary (from libperl): Built under sco Compiled at Jul 23 1999 18:02:29 @INC: /usr/local/lib/perl5/5.00503/i386-sco /usr/local/lib/perl5/5.00503 /usr/local/lib/perl5/site_perl/5.005/i386-sco /usr/local/lib/perl5/site_perl/5.005 Output from cc -v Reading specs from /usr/local/lib/gcc-lib/i386-pc-sco3.2v5.0.5/2.95.2/specs gcc version 2.95.2 19991024 (release)
RE: :Proxy and bind_param (with ORACLE)
I believe you want to send the patch to Tim, since DBD::Proxy is
bundled with DBI.
On 21-Aug-01 Oleg Mechtcheriakov wrote:
> OK,
>
> now I'm ready to answer my question om my own.
>
> The point was exactly that. DBI::Proxy worked well only for 'numbered'
> parameters. I've created small and dirty patch to work with named parameters
> as well. Who is the right person to talk about the patch for DBI::Proxy?
>
> Reg's
> Oleg
>
>> -Original Message-
>> From: Oleg Mechtcheriakov [mailto:[EMAIL PROTECTED]]
>> Sent: Monday, August 20, 2001 12:35 PM
>> To: [EMAIL PROTECTED]
>> Subject: DBD::Proxy and bind_param (with ORACLE)
>>
>>
>> Greetings,
>>
>> has someone experienced troubles with DBD::Proxy and
>> bind_param function?
>>
>> I'm trying to switch from DBD::Oracle to DBD::Proxy and
>> everything was OK
>> but bind_param gives me an exception.
>>
>> Here is the code snippet:
>>
>> my $addr_id;
>> my $sth = $dbh->prepare(q{BEGIN :id :=
>> first_req.check_req_sub_ok(:address);END;});
>> $sth->bind_param_inout(":id",\$addr_id,32,DBI::SQL_INTEGER);
>> $sth->bind_param(":address",$address);
>>
>> which gives me the following error message at the first
>> bind_param_inout
>>
>> Modification of non-creatable array value attempted, subscript -1 at
>> /usr/local/lib/perl5/site_perl/5.6.1/i86pc-solaris/DBD/Proxy.p
>> m line 536
>>
>> Any ideas?
>>
>> Many thanks in advance
>> Oleg
>>
>>
>
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 21-Aug-01
Time: 08:29:52
--
RE: DBI sql parsing - possible bug
Sorry I don't have a lot of time right this moment to investigate
further, taking a quick glance at the code,
if (in_literal) {
/* check if literal ends but keep quotes in literal */
if (*src == in_literal && *(src-1) != '\\') {
in_literal = 0;
}
*dest++ = *src++;
continue;
}
..I think this is where the problem is, the \\ escapes the '.
On 14-Aug-01 Scott T. Hildreth wrote:
>
> Actually the preparsing is done by the DBD's. Look at the
> dbd_preparse() function in dbdimp.c file under DBD::PG dist.
>
> On 15-Aug-01 Anar R Guliev wrote:
>> Dear Sir,
>>
>> I've found your email in the DBI's perldoc.
>>
>> So, what i found seems to be a bug of sql-parsing
>> Here is a fragment of tracing:
>>
>> dbd_st_preparse: statement = >select * from dirs_items1 where dirid like
>> ?||'\\_\%' escape '\\' and lang=?<
>> dbd_preparse scanned 1 distinct placeholders
>>
>> Of cause, there are 2 placeholders there, not 1.
>> If i write it this way:
>>
>> select * from dirs_items1 where lang=? and dirid like ?||'\\_\%' escape
>> '\\'
>>
>> it is ok. so, something is wrong when parsing this: ?||'\\_\%' escape '\\'
>> is not it?
>> (btw, i'm using postgres, but i think it is of DBI, not of DBD )
>>
>> I don't know really who deals with it, if you know - please forward
>> the mail to him.
>>I just wanted to notify developers of a possible problem in a code.
>>
>> --
>> Best regards,
>> Anar R Guliev mailto:[EMAIL PROTECTED]
>>
>>
>> PS: May be it will be usefull - here is a fragment of my program code
>> where i found a problem:
>>
>>#For Postgres:
>> $argument_undescore_something=' ?||\'_\%\' escape \'\' ';
>>
>> sub print_dirsbelow_n_return_number {#pass dirID,lang
>> my($passed_dirid,$passed_lang)=@_;
>> my $items_num=0;
>> my $row;
>> my $listingof_dirs='';
>> my $processed_subdirs='';
>> my $current_subdir_id='';
>> my $sth=$dbh->prepare("select * from $configs{'tablename'} where dirid like
>> $argument_undescore_something and lang=?");
>> $sth->execute($passed_lang,$passed_dirid);
>> while($row=$sth->fetchrow_arrayref)
>>
>>
>> }
>>
>> i've included $dbh->trace(7) - here is what it writes:
>>
>>
>> dbd_st_preparse: statement = >select * from dirs_items1 where dirid like
>> ?||'\\_\%' escape '\\' and lang=?<
>> dbd_preparse scanned 1 distinct placeholders
>>
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 14-Aug-01
> Time: 12:36:12
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 14-Aug-01
Time: 12:48:46
--
RE: DBI sql parsing - possible bug
Actually the preparsing is done by the DBD's. Look at the
dbd_preparse() function in dbdimp.c file under DBD::PG dist.
On 15-Aug-01 Anar R Guliev wrote:
> Dear Sir,
>
> I've found your email in the DBI's perldoc.
>
> So, what i found seems to be a bug of sql-parsing
> Here is a fragment of tracing:
>
> dbd_st_preparse: statement = >select * from dirs_items1 where dirid like
> ?||'\\_\%' escape '\\' and lang=?<
> dbd_preparse scanned 1 distinct placeholders
>
> Of cause, there are 2 placeholders there, not 1.
> If i write it this way:
>
> select * from dirs_items1 where lang=? and dirid like ?||'\\_\%' escape '\\'
>
> it is ok. so, something is wrong when parsing this: ?||'\\_\%' escape '\\'
> is not it?
> (btw, i'm using postgres, but i think it is of DBI, not of DBD )
>
> I don't know really who deals with it, if you know - please forward
> the mail to him.
>I just wanted to notify developers of a possible problem in a code.
>
> --
> Best regards,
> Anar R Guliev mailto:[EMAIL PROTECTED]
>
>
> PS: May be it will be usefull - here is a fragment of my program code
> where i found a problem:
>
>#For Postgres:
> $argument_undescore_something=' ?||\'_\%\' escape \'\' ';
>
> sub print_dirsbelow_n_return_number {#pass dirID,lang
> my($passed_dirid,$passed_lang)=@_;
> my $items_num=0;
> my $row;
> my $listingof_dirs='';
> my $processed_subdirs='';
> my $current_subdir_id='';
> my $sth=$dbh->prepare("select * from $configs{'tablename'} where dirid like
> $argument_undescore_something and lang=?");
> $sth->execute($passed_lang,$passed_dirid);
> while($row=$sth->fetchrow_arrayref)
>
>
> }
>
> i've included $dbh->trace(7) - here is what it writes:
>
>
> dbd_st_preparse: statement = >select * from dirs_items1 where dirid like
> ?||'\\_\%' escape '\\' and lang=?<
> dbd_preparse scanned 1 distinct placeholders
>
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 14-Aug-01
Time: 12:36:12
--
Re: Exporting Data
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] wrote:
> print FILE @rows . '\t;
why didn't you try
print FILE join("\t", @rows), "\n";
?
of course the dump method mentioned in the prev post is a good idea too
;-)
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Trigger Problem ??
I have a problem with the oracle dbi with trigger active. I have created some tables with triggers. When I do a simple select for the tables in my code I get nothing back however the tables without trigger display the output. I check my trace log and no errors where found just zero returns. I running DBI 1.18, DBD::Oracle version 1.07 and 8.1.7. Any help is appreciated. Anthony Scott
Re: Comparing Tables (MySQL) against arrays
Why are you making the %compare hash?
Why not,
foreach my $id (@compare_array) { print "Found\n" if exists $seen{$id}; }
or another way would be,
my $ar = $dbh->selectall_arrayref('select userid from subscriptions');
my %seen;
map { $seen{$_->[0]}++ } @$ar;
Remember to watch the memory if the returned rows gets big, although that
doesn't seem like a problem here. Also, DBD::mysql returns all the rows
at once so if the select bogs done the system use the
{ "mysql_use_result" =>1} in the connect.
> my $sth = $dbh->prepare('SELECT userid FROM subscriptions');
> my %seen;
>
> while (my ($id) = $sth->fetchrow) {
> $seen{$id}++;
> }
>
> my %compare = map { $_ => 1 } @compare_array;
>
> foreach my $id (keys %compare) {
> if (exists $seen{$id}) {
> # found this id
> }
> else {
> # didn't find this id
> }
> }
>
> Much more efficient. The basic idea would be, in this case, to ask
> the DB to just give you all IDs and you do the comparison in memory.
> Another option would be to create a temporary table, shove all your
> ids in it, and do an outer join on the tables, but I doubt that will
> be faster, unless you have more than 100 times as many id's in the DB
> as in your @compare_array table.
>
> Of course, WHERE you got those id's from is important; are they from
> another table? If so, you may be able to do it all in the DB and not
> transfer the IDs back and forth.
>
> Chip
>
> "oakbox" <[EMAIL PROTECTED]> writes:
>
>> I have about three work-arounds for this problem, but all of them are time
>> consuming and a big drain on resources. I was wondering if any of you have
>> come up with an easy way to compare the contents of a list (array) against a
>> table (Perl:DBI:MySQL).
>>
>> At the risk of looking like an idiot, here is one of my previous
>> work-arounds:
>>
>> I have a list of userid's (@compare_array) that i want to compare to my
>> subscription table.
>>
>> $primer=$dbh->prepare("SELECT count(*) FROM subscription where userid=? ");
>>
>> foreach $userid (@compare_array){
>> $primer->execute($userid);
>> if ($primer->err()){$message.="Problem on Select".$primer->errstr();
>> &shellout; exit;}
>>($count)=$primer->fetchrow_array();
>>
>>if($count > 0){#there is a match
>> }else{#there was no match
>> }
>> }
>>
>> The above system WORKS, and works pretty well for tables of <1000 records.
>> But when I'm dealing with 10,000 or 50,000 records, this search becomes and
>> incredible drain.
>>
>> Is there a way to perform this operation without running 25,000 SELECTs when
>> @compare_array has that many ID's?
>>
>> Thank you,
>> Richard Still
>> Oakbox.com
>
> --
> Chip Turner [EMAIL PROTECTED]
> RHN Web Engineer
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 18-Jul-01
Time: 18:10:12
--
RE: problem with DBD::Oracle and varchar
Curious Question,
Are you sure the value is not 50 characters long. It is a varchar
and will not return a 100 if the length is less.
On 11-Jul-01 Bill Goerlich wrote:
> I'm selecting from a table which has a varchar(100) ... but am only
> receiving the first 50 characters.
> In the same table/select I am also grabbing a field which is defined as a
> long, and not having any problems.
> ( I set $dbh->{'LongReadLen'} = 3; for the long field, which did the
> trick there)
>
> DBD::Oracle version is 1.03 I believe.
>
> Any advice would be much appreciated.
> Thanks,
> Bill.
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 11-Jul-01
Time: 09:11:36
--
RE: Problems Running from crontab.
How would this differ from when I set them in the code?
I do have $ENV{ORACLE_HOME}, $ENV{ORACLE_SID}, $ENV{LD_LIBRARY_PATH}
set in any code that runs from cron.
On 02-Jul-01 Steve Sapovits wrote:
>
> You can do this nice little Perl trick:
>
> BEGIN
> {
>if ($ENV{LD_LIBRARY_PATH} !~ /oracle.*lib/)
>{
> $ENV{LD_LIBRARY_PATH} = '/oracle/8.0.5/lib';
> exec($^X, $0, @ARGV);
>}
> }
>
> or something similar (e.g., we use a package that appends
> to LD_LIBRARY_PATH if the value isn't already in it).
>
> The exec piece executes your Perl script exactly as you did,
> using the same Perl interpreter.
>
>
> Steve Sapovits
> Global Sports Interactive
> Work Email: [EMAIL PROTECTED]
> Home Email: [EMAIL PROTECTED]
> Work Phone: 610-491-7087
> Cell: 610-574-7706
> Pager: 877-239-4003
>
>> -Original Message-
>> From: Scott T. Hildreth [SMTP:[EMAIL PROTECTED]]
>> Sent:Monday, July 02, 2001 2:44 PM
>> To: Scott T. Hildreth
>> Cc: [EMAIL PROTECTED]
>> Subject: RE: Problems Running from crontab.
>>
>>
>> I fixed it by setting the 'SHELL=/usr/local/bin/zsh' so
>> the LD_LIBRARY_PATH is getting set before Perl is executed.
>> I'm not sure why the behavior changed, but it works :-)
>>
>> On 02-Jul-01 Scott T. Hildreth wrote:
>> >
>> > I wonder if anyone has run into this problem. I upgrade on of our
>> > production servers to perl5.6.1, DBI-1.18, and using DBD::Oracle 1.06.
>> > I was using DBD::Oracle 1.07, but I went back to 1.06 to see if that
>> > is the problem. Anyway I have jobs that run in cron, in the past if
>> > I set the %ENV Vars, everything ran okay. since I update the Perl and
>> > DBI the following error occurs,
>> >
>> > install_driver(Oracle) failed: Can't load
>> >
>> '/usr/local/lib/perl5/site_perl/5.6.1/i686-linux/auto/DBD/Oracle/Oracle.so
>> '
>> > for
>> > module DBD::Oracle: libclntsh.so.1.0: cannot open shared object file: No
>> such
>> > file or directory at /usr/local/lib/perl5/5.6.1/i686-linux/DynaLoader.pm
>> line
>> > 206.
>> >
>> > ..It will run from command line, because it is getting the env from the
>> > shell,
>> > but not cron.
>> >
>> > The %ENV vars are set as,
>> >
>> > $ENV{ORACLE_SID} = $ARGV[0];
>> > $ENV{ORACLE_HOME} = '/oracle/8.0.5';
>> > $ENV{LD_LIBRARY_PATH} = '/oracle/8.0.5/lib';
>> >
>> > which worked until the updates. Does anyone know if this a problem with
>>
>> > Perl5.6.1 or DBI-1.18?
>> >
>> > Thanks,
>> > STH
>> >
>> > --
>> > E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
>> > Date: 02-Jul-01
>> > Time: 12:14:12
>> > --
>>
>> --
>> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
>> Date: 02-Jul-01
>> Time: 13:40:59
>> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 03-Jul-01
Time: 06:30:13
--
Re: Problems Running from crontab.
I just changed the shell because our .zshenv has the enviorment variables setup. On 03-Jul-01 Alexander Farber (EED) wrote: > "Scott T. Hildreth" wrote: >> >> I fixed it by setting the 'SHELL=/usr/local/bin/zsh' so >> the LD_LIBRARY_PATH is getting set before Perl is executed. > > Why not just set the LD_LIBRARY_PATH and then execute > the Perl-script instead of changing sh to zsh? > >> I'm not sure why the behavior changed, but it works :-) > > ;-) -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 03-Jul-01 Time: 06:24:08 --
RE: Problems Running from crontab.
I fixed it by setting the 'SHELL=/usr/local/bin/zsh' so
the LD_LIBRARY_PATH is getting set before Perl is executed.
I'm not sure why the behavior changed, but it works :-)
On 02-Jul-01 Scott T. Hildreth wrote:
>
> I wonder if anyone has run into this problem. I upgrade on of our
> production servers to perl5.6.1, DBI-1.18, and using DBD::Oracle 1.06.
> I was using DBD::Oracle 1.07, but I went back to 1.06 to see if that
> is the problem. Anyway I have jobs that run in cron, in the past if
> I set the %ENV Vars, everything ran okay. since I update the Perl and
> DBI the following error occurs,
>
> install_driver(Oracle) failed: Can't load
> '/usr/local/lib/perl5/site_perl/5.6.1/i686-linux/auto/DBD/Oracle/Oracle.so'
> for
> module DBD::Oracle: libclntsh.so.1.0: cannot open shared object file: No such
> file or directory at /usr/local/lib/perl5/5.6.1/i686-linux/DynaLoader.pm line
> 206.
>
> ..It will run from command line, because it is getting the env from the
> shell,
> but not cron.
>
> The %ENV vars are set as,
>
> $ENV{ORACLE_SID} = $ARGV[0];
> $ENV{ORACLE_HOME} = '/oracle/8.0.5';
> $ENV{LD_LIBRARY_PATH} = '/oracle/8.0.5/lib';
>
> which worked until the updates. Does anyone know if this a problem with
> Perl5.6.1 or DBI-1.18?
>
> Thanks,
> STH
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 02-Jul-01
> Time: 12:14:12
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 02-Jul-01
Time: 13:40:59
--
Problems Running from crontab.
I wonder if anyone has run into this problem. I upgrade on of our
production servers to perl5.6.1, DBI-1.18, and using DBD::Oracle 1.06.
I was using DBD::Oracle 1.07, but I went back to 1.06 to see if that
is the problem. Anyway I have jobs that run in cron, in the past if
I set the %ENV Vars, everything ran okay. since I update the Perl and
DBI the following error occurs,
install_driver(Oracle) failed: Can't load
'/usr/local/lib/perl5/site_perl/5.6.1/i686-linux/auto/DBD/Oracle/Oracle.so' for
module DBD::Oracle: libclntsh.so.1.0: cannot open shared object file: No such
file or directory at /usr/local/lib/perl5/5.6.1/i686-linux/DynaLoader.pm line
206.
..It will run from command line, because it is getting the env from the shell,
but not cron.
The %ENV vars are set as,
$ENV{ORACLE_SID} = $ARGV[0];
$ENV{ORACLE_HOME} = '/oracle/8.0.5';
$ENV{LD_LIBRARY_PATH} = '/oracle/8.0.5/lib';
which worked until the updates. Does anyone know if this a problem with
Perl5.6.1 or DBI-1.18?
Thanks,
STH
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 02-Jul-01
Time: 12:14:12
--
DBI to MS-SQL memory leak?
I recently wrote this script that pulls information from text files and adds
it to a MS-SQL database. When the script is finished running, the SQL server
is stuck using a large amount of memory. Each consecutive time I run the
script the memory usage for the SQL server goes up. I believe that I am
closing/finishing/committing correctly, but thought I would put it here for
review. Please excuse my lack of Perl skills, I usually close the book as
soon as I think I can figure it out for myself! This is one of 3 text files
that add/modify record in the database, I have posted the smallest example
here. The largest table has more than 160 fields, but is coded the same way.
chdir("D:\\CMLS\\download\\dailytemp") or error_report("could not change to
temp directory before inserting data: $!\n");
# open SQL database
my $dbh = DBI->connect( 'dbi:ODBC:CMLS', 'user', 'pass',
{RaiseError => 0,
AutoCommit => 0}
) or error_report("Database connection not made (update offices):
$DBI::errstr");
# open txt file of new data
open(OFFICES, "offices.txt") or error_report("could not open offices.txt
file to read data");
my($rcnt)=0;
my($sth) = $dbh->prepare
('SELECT officenumber FROM offices WHERE officenumber= ?');
my($sth1) = $dbh->prepare
('UPDATE offices SET OfficeName= ? WHERE OfficeNumber= ?') or
error_report("Could not prepare update statement for offices:
$DBI::errstr");
my($sth2) = $dbh->prepare
('INSERT INTO offices (OfficeNumber, OfficeName) VALUES (?,?)') or
error_report("Could not prepare insert statement for offices:
$DBI::errstr");
while(){
chomp($_);
if($rcnt>0){ # skip first line
my($officenumber,$officename) = split(/\|/, $_);
$sth->execute($officenumber);
my @row = $sth->fetchrow_array;
$sth->finish;
if(@row>0){
print "record exists - updating data for office $officenumber\n";
$sth1->execute($officename, $officenumber) or
error_report("Error updating office record: $DBI::errstr");
}else{
print "record does not exist - adding office $officenumber\n";
$sth2->execute($officenumber, $officename) or
error_report("Error inserting new office record:
$DBI::errstr");
}
}
$rcnt++;
}
$sth1->finish;
$sth2->finish;
$rcnt=$rcnt-1;
print "$rcnt records affected\n";
$dbh->commit or $dbh->rollback and error_report("Database changes to offices
table not commited: $$DBI::errstr");
$dbh->disconnect;
___
|
| Scott Phelps
| NT Systems Administrator
| WebKorner Internet Services
| [EMAIL PROTECTED]
| www.webkorner.com
| MCDTP (Microsoft Certified
| duct tape professional)
|___
FW: Re: 100 levels deep in subroutine calls!
It is updating data, the duplicates come from running again. Will get so far then it dies. When I took out the eval from the one function, it stopped dieing there. He has numerous code changes/fixes to make. He was reusing a global $sth for every handle, preparing everytime in a loop...etc. I have him changing all the code. I just wanted know where or why the recursive call was comming from, he is not making recursive calls, I checked that right away. Anyway, my quess is when he is done cleaning up the code the errors will disappearI hope :) Thanks, STH On 15-Jun-01 MikeBlezien wrote: > On Fri, 15 Jun 2001 14:20:28 -0500 (CDT), "Scott T. Hildreth" > <[EMAIL PROTECTED]> wrote: > > It appears that you are attempting to enter data to a column that has > possible > reached it's max size. What type of column type is the duplicate entry > referring > too?? > >>>Sorry I should have mentioned that I did Trace it, >>> >>>I traced one of the Statement Handles as well as the >>>Db Handle, it shows the Duplicate Error being returned >>>but nothing else, >>> >>> ERROR EVENT 5 'Duplicate entry '2067258104' for key 1' on >>>DBI::st=HASH(0x14037fa50) >>>Duplicate entry '2067258104' for key 1 error 5 recorded: Duplicate entry >>>'2067258104' for key 1 >>><- dbd_st_execute -2 rows >>>!! ERROR: 5 'Duplicate entry '2067258104' for key 1' >>><- execute= undef at qwl_newcust.pl line 374. >>>-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x14037fa50)~INNER) >>><- DESTROY= undef during global destruction. >>> >>>..the dups are there, that is why we catch with a eval. >>> >>>The $dbh trace shows the same thing, > > Mike(mickalo)Blezien > ==== > Thunder Rain Internet Publishing > Providing Internet Solutions that work! > http://www.thunder-rain.com > Tel: 1(225) 686-2002 > ===== > > > > > > > > > > > > > > > -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 15-Jun-01 Time: 14:42:22 -- --End of forwarded message- -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 15-Jun-01 Time: 14:53:14 --
RE: 100 levels deep in subroutine calls!
Sorry I should have mentioned that I did Trace it,
I traced one of the Statement Handles as well as the
Db Handle, it shows the Duplicate Error being returned
but nothing else,
ERROR EVENT 5 'Duplicate entry '2067258104' for key 1' on
DBI::st=HASH(0x14037fa50)
Duplicate entry '2067258104' for key 1 error 5 recorded: Duplicate entry
'2067258104' for key 1
<- dbd_st_execute -2 rows
!! ERROR: 5 'Duplicate entry '2067258104' for key 1'
<- execute= undef at qwl_newcust.pl line 374.
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x14037fa50)~INNER)
<- DESTROY= undef during global destruction.
..the dups are there, that is why we catch with a eval.
The $dbh trace shows the same thing,
Duplicate entry '2084592512' for key 1 error 5 recorded: Duplicate entry
'2084592512' for key 1
<- dbd_st_execute -2 rows
!! ERROR: 5 'Duplicate entry '2084592512' for key 1'
<- execute= undef at qwl_newcust.pl line 424.
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x14035fa90)~INNER)
<- DESTROY= undef during global destruction.
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x140379b00)~INNER)
<- DESTROY= undef during global destruction.
-> DESTROY for DBD::mysql::db (DBI::db=HASH(0x14035fa30)~INNER)
Rollback ineffective while AutoCommit is on error 15 recorded: Rollback
ineffective while AutoCommit is on
imp_dbh->svsock: 14030e350
<- DESTROY= undef during global destruction.
..I don't think it is DBI, because the Trace coninue to show the dup
errors for each record, then they call the DESTROY, and the other
error(100 levels deep in subroutine calls!) comes up.
On 15-Jun-01 Sterin, Ilya wrote:
> Well depending on why it is failing. You error message does not provide any
> help. Try using $DBI::errstr in it and also use trace() at level 2 (see
> docs). You can then submit both to us if you can't figure out yourself.
>
> Ilya
>
> -Original Message-
> From: Scott T. Hildreth
> To: [EMAIL PROTECTED]
> Sent: 06/15/2001 12:50 PM
> Subject: 100 levels deep in subroutine calls!
>
>
> I'm helping a co-worker debug some code, and I can't figure out
> what is going on. I hope someone can shed some light on it.
>
> This is perl, version 5.005_02 built for alpha-dec_osf
> DBI 1.14
> Msql-Mysql-modules-1.2217.tar.gz
>
>
> Basically, He calls a subroutine that evals the execute
> and checks $@. He keeps rerunning in debugger so there
> are duplicate keys that were already inserted. So he uses
> the eval to skip those errors. It actuall quits in multiple
> subroutines. I commented out the eval in one subroutine and
> added $dbh->{RaiseError} = 0, and it did not quit in this sub,
> but it did quit in another sub with,
>
> main::update_newcust(qwl_newcust.pl:430):
> 430:if ($@) {
> 100 levels deep in subroutine calls!
>
> Anybody know what I am missing here???
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 15-Jun-01
> Time: 13:33:13
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 15-Jun-01
Time: 14:12:07
--
100 levels deep in subroutine calls!
I'm helping a co-worker debug some code, and I can't figure out
what is going on. I hope someone can shed some light on it.
This is perl, version 5.005_02 built for alpha-dec_osf
DBI 1.14
Msql-Mysql-modules-1.2217.tar.gz
Basically, He calls a subroutine that evals the execute
and checks $@. He keeps rerunning in debugger so there
are duplicate keys that were already inserted. So he uses
the eval to skip those errors. It actuall quits in multiple
subroutines. I commented out the eval in one subroutine and
added $dbh->{RaiseError} = 0, and it did not quit in this sub,
but it did quit in another sub with,
main::update_newcust(qwl_newcust.pl:430):
430:if ($@) {
100 levels deep in subroutine calls!
Anybody know what I am missing here???
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 15-Jun-01
Time: 13:33:13
--
RE: DBI/DBD mysql freebsd4 __errno_location problem
For kicks, why don't you try upgrading to the lastest version, I am running Msql-Mysql-modules-1.2215 and I can't seem to find __errno_location. On 14-Jun-01 Rich Caller wrote: > I'm reinstalling DBI/DBD (Msql-Mysql-modules-1.2204) in an odd > location after a system rebuild and am having a problem that I am > having difficulty in finding a solution to. > > Everything seems to build fine but when I make test the tests I > get. > > > install_driver(mysql) failed: Can't load > '../blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: > ../blib/arch/auto/DBD/mysql/mysql.so: Undefined symbol > "__errno_location" at /usr/libdata/perl/5.00503/DynaLoader.pm line > 169. > Perhaps a required shared library or dll isn't installed where > expected > > I get the same error if I try using scripts that previously worked > > I've tried all the usual places and searches for information without > much luck. I would be grateful if someone could give me some pointers > to how to work out what I'm doing wrong.. > > I have also been advised that this may be due to certain mysql files not > being on path, I've made sure that the correct files are on path etc. but > nothing has changed. Any more ideas. > > > Heres perl -V for some platform and version information > > Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration: > Platform: > osname=freebsd, osvers=4.0-current, archname=i386-freebsd > uname='FreeBSD freefall.FreeBSD.org 4.0-current FreeBSD > 4.0-current #0: $Date$' > hint=recommended, useposix=true, d_sigaction=define > usethreads=undef useperlio=undef d_sfio=undef > Compiler: > cc='cc', optimize='undef', gccversion=2.95.2 19991024 (release) > cppflags='' > ccflags ='' > stdchar='char', d_stdstdio=undef, usevfork=true > intsize=4, longsize=4, ptrsize=4, doublesize=8 > d_longlong=define, longlongsize=8, d_longdbl=define, > longdblsize=12 > alignbytes=4, usemymalloc=n, prototype=define > Linker and Libraries: > ld='cc', ldflags ='-Wl,-E -lperl -lm ' > libpth=/usr/lib > libs=-lm -lc -lcrypt > libc=, so=so, useshrplib=true, libperl=libperl.so.3 > Dynamic Linking: > dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=' > -Wl,-R/usr/lib' > cccdlflags='-DPIC -fpic', lddlflags='-Wl,-E -shared -lperl -lm ' > > > Characteristics of this binary (from libperl): > Built under freebsd > Compiled at Apr 21 2001 08:25:58 > @INC: > /usr/libdata/perl/5.00503/mach > /usr/libdata/perl/5.00503 > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd > /usr/local/lib/perl5/site_perl/5.005 > . > > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > -- E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]> Date: 14-Jun-01 Time: 10:33:01 --
RE: Strange CHAR/Oracle/DBI issue
I'm not sure, but isn't a char(8) always '12345 ', in Oracle?
oh well,
$dbh->{ora_ph_type} = 96; or
$sth->bind_param(1, $fld, {ora_type =>96});
will work for sure..well they should :-)
On 12-Jun-01 Wilson, Doug wrote:
> Well, we know it SHOULD work, but ya never know till you try :)
>
> Just trying to narrow down the problem.
> or match '12345 %'?
> What if the value stored is '12345'
>
> -Original Message-
> From: Scott T. Hildreth [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 12, 2001 3:04 PM
> To: Doug
> Cc: [EMAIL PROTECTED]; Rick Osterberg
> Subject: RE: Strange CHAR/Oracle/DBI issue
>
>
>
> like would work, but it will match '123456' as well as '12345 '.
>
> On 12-Jun-01 Scott T. Hildreth wrote:
>>
>> DBD::Oracle trims the trailing spaces by default.
>> try this select * from bar where bar = rpad(?, 8).
>>
>> or
>>
>> use DBI;
>> use DBD::Oracle qw(:ora_types);
>>
>> my $dbh = DBI->connect( "dbi:Oracle:", "user/passwd", "",
>> {RaiseError => 1} ) or die $DBI::errstr;
>>
>> $dbh->{ora_ph_type} = 96;
>>
>>
>> On 12-Jun-01 Rick Osterberg wrote:
>>> Nope -- doesn't work either. (I had neglected to mention... we had tried
>>> that, too.)
>>>
>>> -Rick
>>>
>>> On Tue, 12 Jun 2001, Wilson, Doug wrote:
>>>
>>>> Maybe it is binding as a number. Try:
>>>> $sth->bind_param(1, $value, { TYPE => SQL_CHAR });
>>>> before the execute instead of putting $value in the execute;
>>>>
>>>> -Original Message-
>>>> From: Rick Osterberg [mailto:[EMAIL PROTECTED]]
>>>> Sent: Tuesday, June 12, 2001 2:04 PM
>>>> To: '[EMAIL PROTECTED]'
>>>> Subject: Strange CHAR/Oracle/DBI issue
>>>>
>>>>
>>>> Strange one here to pass by everyone:
>>>>
>>>> Oracle 8.0.5.1.1 server
>>>> Perl 5.00503 (on both alpha-dec_osf and Solaris)
>>>> DBI 1.14
>>>>
>>>> Take an Oracle table FOO, with a single field BAR which is a CHAR(8).
>>>> Most everything in that field BAR is 8-characters long, but there are a
>>>> couple that are 5 characters long.
>>>>
>>>> In SQLPLUS, I can do:
>>>>
>>>> SQL> SELECT COUNT(*) FROM FOO WHERE BAR = '12345';
>>>> or
>>>> SQL> SELECT COUNT(*) FROM FOO WHERE BAR = '12345 ';
>>>>
>>>> Both work, and give the correct value.
>>>>
>>>> However, from Perl/DBI:
>>>>
>>>> $sql = "SELECT COUNT(*) FROM FOO WHERE BAR = ?";
>>>> $sth = $dbh->prepare($sql);
>>>> $value = '12345';
>>>> $sth->execute($value);
>>>> ($result) = $sth->fetchrow_array();
>>>>
>>>> The $result is always zero, which is incorrect. Stays like that even if
> I
>>>> change
>>>>
>>>> $value = '12345';
>>>> to
>>>> $value = '12345 ';
>>>>
>>>> What am I missing?
>>>>
>>>> -Rick
>>>>
>>>>
> +------+
>>>> | Rick Osterberg [EMAIL PROTECTED]
>
>>>> | |
>>>> | Database Applications Specialist FAS Computer Services
>
>>>> | |
>>>>
> +--+
>>>>
>>>
>>>
> +--+
>>>| Rick Osterberg [EMAIL PROTECTED]
>|
>>>| Database Applications Specialist FAS Computer Services
>|
>>>
> +--+
>>>
>>
>> --
>> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
>> Date: 12-Jun-01
>> Time: 16:43:41
>> --
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 12-Jun-01
> Time: 17:03:14
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 12-Jun-01
Time: 17:19:48
--
RE: Strange CHAR/Oracle/DBI issue
DBD::Oracle trims the trailing spaces by default.
try this select * from bar where bar = rpad(?, 8).
or
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect( "dbi:Oracle:", "user/passwd", "",
{RaiseError => 1} ) or die $DBI::errstr;
$dbh->{ora_ph_type} = 96;
On 12-Jun-01 Rick Osterberg wrote:
> Nope -- doesn't work either. (I had neglected to mention... we had tried
> that, too.)
>
> -Rick
>
> On Tue, 12 Jun 2001, Wilson, Doug wrote:
>
>> Maybe it is binding as a number. Try:
>> $sth->bind_param(1, $value, { TYPE => SQL_CHAR });
>> before the execute instead of putting $value in the execute;
>>
>> -Original Message-
>> From: Rick Osterberg [mailto:[EMAIL PROTECTED]]
>> Sent: Tuesday, June 12, 2001 2:04 PM
>> To: '[EMAIL PROTECTED]'
>> Subject: Strange CHAR/Oracle/DBI issue
>>
>>
>> Strange one here to pass by everyone:
>>
>> Oracle 8.0.5.1.1 server
>> Perl 5.00503 (on both alpha-dec_osf and Solaris)
>> DBI 1.14
>>
>> Take an Oracle table FOO, with a single field BAR which is a CHAR(8).
>> Most everything in that field BAR is 8-characters long, but there are a
>> couple that are 5 characters long.
>>
>> In SQLPLUS, I can do:
>>
>> SQL> SELECT COUNT(*) FROM FOO WHERE BAR = '12345';
>> or
>> SQL> SELECT COUNT(*) FROM FOO WHERE BAR = '12345 ';
>>
>> Both work, and give the correct value.
>>
>> However, from Perl/DBI:
>>
>> $sql = "SELECT COUNT(*) FROM FOO WHERE BAR = ?";
>> $sth = $dbh->prepare($sql);
>> $value = '12345';
>> $sth->execute($value);
>> ($result) = $sth->fetchrow_array();
>>
>> The $result is always zero, which is incorrect. Stays like that even if I
>> change
>>
>> $value = '12345';
>> to
>> $value = '12345 ';
>>
>> What am I missing?
>>
>> -Rick
>>
>> +--+
>> | Rick Osterberg [EMAIL PROTECTED]|
>> | Database Applications Specialist FAS Computer Services |
>> +--+
>>
>
> +--+
>| Rick Osterberg [EMAIL PROTECTED]|
>| Database Applications Specialist FAS Computer Services |
> +--+
>
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 12-Jun-01
Time: 16:43:41
--
RE: Strange CHAR/Oracle/DBI issue
like would work, but it will match '123456' as well as '12345 '.
On 12-Jun-01 Scott T. Hildreth wrote:
>
> DBD::Oracle trims the trailing spaces by default.
> try this select * from bar where bar = rpad(?, 8).
>
> or
>
> use DBI;
> use DBD::Oracle qw(:ora_types);
>
> my $dbh = DBI->connect( "dbi:Oracle:", "user/passwd", "",
> {RaiseError => 1} ) or die $DBI::errstr;
>
> $dbh->{ora_ph_type} = 96;
>
>
> On 12-Jun-01 Rick Osterberg wrote:
>> Nope -- doesn't work either. (I had neglected to mention... we had tried
>> that, too.)
>>
>> -Rick
>>
>> On Tue, 12 Jun 2001, Wilson, Doug wrote:
>>
>>> Maybe it is binding as a number. Try:
>>> $sth->bind_param(1, $value, { TYPE => SQL_CHAR });
>>> before the execute instead of putting $value in the execute;
>>>
>>> -Original Message-
>>> From: Rick Osterberg [mailto:[EMAIL PROTECTED]]
>>> Sent: Tuesday, June 12, 2001 2:04 PM
>>> To: '[EMAIL PROTECTED]'
>>> Subject: Strange CHAR/Oracle/DBI issue
>>>
>>>
>>> Strange one here to pass by everyone:
>>>
>>> Oracle 8.0.5.1.1 server
>>> Perl 5.00503 (on both alpha-dec_osf and Solaris)
>>> DBI 1.14
>>>
>>> Take an Oracle table FOO, with a single field BAR which is a CHAR(8).
>>> Most everything in that field BAR is 8-characters long, but there are a
>>> couple that are 5 characters long.
>>>
>>> In SQLPLUS, I can do:
>>>
>>> SQL> SELECT COUNT(*) FROM FOO WHERE BAR = '12345';
>>> or
>>> SQL> SELECT COUNT(*) FROM FOO WHERE BAR = '12345 ';
>>>
>>> Both work, and give the correct value.
>>>
>>> However, from Perl/DBI:
>>>
>>> $sql = "SELECT COUNT(*) FROM FOO WHERE BAR = ?";
>>> $sth = $dbh->prepare($sql);
>>> $value = '12345';
>>> $sth->execute($value);
>>> ($result) = $sth->fetchrow_array();
>>>
>>> The $result is always zero, which is incorrect. Stays like that even if I
>>> change
>>>
>>> $value = '12345';
>>> to
>>> $value = '12345 ';
>>>
>>> What am I missing?
>>>
>>> -Rick
>>>
>>> +------+
>>> | Rick Osterberg [EMAIL PROTECTED]
>>> | |
>>> | Database Applications Specialist FAS Computer Services
>>> | |
>>> +--+
>>>
>>
>> +--+
>>| Rick Osterberg [EMAIL PROTECTED]|
>>| Database Applications Specialist FAS Computer Services |
>> +--+
>>
>
> --
> E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
> Date: 12-Jun-01
> Time: 16:43:41
> --
--
E-Mail: Scott T. Hildreth <[EMAIL PROTECTED]>
Date: 12-Jun-01
Time: 17:03:14
--
Re: DBI upgrade
Let me re-phrase it I'm currectly running Oracle 8.1.5 with the following env. Unix Platform DBD-Oracle-1.03 DBI-1.13 perl 5.00503 Do I need to upgrade the DBI/DBD software to support 8.1.7? Anthony Scott "Sterin, Ilya" wrote: > > Well it definitely wouldn't be bad move to upgrade to DBI 1.18, but for > Oracle 8+, compile DBD::Oracle with OCI8, for Win32 use ppm to install > DBD::Oracle8 > > Ilya Sterin > > -Original Message- > From: Anthony Scott > Cc: [EMAIL PROTECTED] > Sent: 6/11/01 1:11 PM > Subject: DBI upgrade > > Do I need to upgrade the DBI software for Oracle 8.1.7 support? > I'm current running DBI-1.13. > > Anthony Scott
DBI upgrade
Do I need to upgrade the DBI software for Oracle 8.1.7 support? I'm current running DBI-1.13. Anthony Scott
Re: DBI -> Access
D'Oh.
When I removed the permissions Access created a copy of the .mdb file called
'Secure ...' so I went to change the User DSN to point at that instead.
Then I noticed that the DSN setup had defaulted in 'ReadOnly'...
Works fine when I uncheck this :).
Thanks for your help.
Tim
- Original Message -
From: "Tim Scott" <[EMAIL PROTECTED]>
To: "DBI Users" <[EMAIL PROTECTED]>
Sent: Wednesday, May 30, 2001 9:43 AM
Subject: Re: DBI -> Access
>
> Some further fiddling found a problem with 'workdate'. I've sorted that,
but
> it still makes no difference. :((
>
> The values it's passing to '$sth->execute' are:
> 1531499623, -1516480662, 2001-5-29 00:00:00, 3, May-2001
>
> Help!
> Tim
>
> - Original Message -
> From: "Tim Scott" <[EMAIL PROTECTED]>
> To: "Trevor Webster" <[EMAIL PROTECTED]>
> Cc: "DBI Users" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 30, 2001 9:09 AM
> Subject: Re: DBI -> Access
>
>
> >
> > Thanks for your input on this guys. Here's some more information which
> might
> > help, but I think it precludes these problems...
> >
> > The access database file is a file on my local hard drive to which I
have
> > full and complete access. I can indeed add to this very table through
> Access
> > itself.
> >
> > The PERL script is running through the Apache web server as a CGI script
> and
> > is connecting to the Access database as 'Admin'.
> >
> > The operation I am doing is an insert into one table. I have enclosed
ALL
> of
> > the DBI calls from my script (it's not a complex one !)
> >
> > -
> > $dbh_eng = DBI->connect("dbi:ODBC:EngTest", "Admin", "admin");
> > # Also tried with "" password. No change.
> >
> > $sth = $dbh_eng->prepare(q(
> > insert into WorkDone (person_id, job_id, Workdate, hours, month)
> > values (?,?,?,?,?);
> > )) ||
> > splat "Failed to prepare insert";
> >
> > {{ snip - lines not referencing $dbh_eng or $sth }}
> >
> > $sth->execute($bod, $jobs[$i], $workdate, $hours[$i], $month)
> > || splat "Failed to insert\n";
> >
> > {{ snip - lines not referencing $dbh_eng or $sth }}
> >
> > eval { $sth->finish; };
> > eval { undef $$sth; }; # otherwise I get an error in the webserver log
> file
> >
> > $dbh_eng->disconnect();
> > -
> >
> > The WorkDone table has two referntial constraints enforced which are
> valid.
> > I have tried disabling them (and they still are) but it had no effect.
> >
> > The table is owned by Admin who has select, insert, update and delete
> > privileges.
> >
> > Please let me know if any more information would help.
> >
> > Thanks,
> > Tim
> >
> > >From: [EMAIL PROTECTED]
> > >Date: Tue, 29 May 2001 12:51:25 -0400
> > >
> > >This is an MS Access problem, and has nothing to do with DBI. Read the
> > Access
> > >documentation about updatable queries.
> > >
> > >In general, when a query results from certain types of joins, Access
> > doesn't
> > >know how to allow updates to the resultset; I think it has something to
> do
> > with
> > >how they do record locking. I'm sure Oracle supports row-level record
> > locks and
> > >generally handles the situation better, so it was able to support what
> > you're
> > >doing. DBI can't solve the problem. You can probably accomplish what
> you
> > need
> > >to while still using Access, if you break up your operation into
smaller
> > >queries, or only update one table at a time.
> > >
> > >HTH.
> > >
> > >Wes
> >
> > - Original Message -
> > From: "Trevor Webster" <[EMAIL PROTECTED]>
> > To: "Tim Scott" <[EMAIL PROTECTED]>
> > Sent: Wednesday, May 30, 2001 7:46 AM
> > Subject: Re: DBI -> Access
> >
> >
> > > Tim
> > >
> > > I had a similar problem when I first started using DBI-ODBC. After
> > > consulting with the DBI-users I found that the problem was associated
> not
> > > with DBI nor Access but with permissions on the sub-directory in which
> the
> > > database was located. Make sure that the directory structure has
> > read/write
> > > permissions throughout and this should, touch wood, solve your
problem.
> > >
> > > Cheers Trevor
> > >
> > > - Original Message -
> > > From: "Tim Scott" <[EMAIL PROTECTED]>
> > > To: "DBI Users" <[EMAIL PROTECTED]>
> > > Sent: Tuesday, May 29, 2001 5:16 PM
> > > Subject: DBI -> Access
> > >
> > >
> > > > All,
> > > >
> > > > New to Access I'm finding:-
> > > > [Microsoft][ODBC Microsoft Access Driver] Operation must use an
> > updateable
> > > > query. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
> > > >
> > > > And thinking "how do I tell it that it's an updateable query?"
> > > >
> > > > The same sort of thing worked fine with Oracle...
> > > >
> > > > Any help appreciated.
> > > >
> > > > Thanks,
> > > > Tim
> > > >
> > > > The information transmitted in this electronic mail message may
> contain
> > > > confidential and or privileged materials. For full details and
> > > restrictions
> > > > see http://www.fdgroup.com/emaildisclaimer.html
> > > >
> > >
> > >
> >
> >
>
>
Re: DBI -> Access
Some further fiddling found a problem with 'workdate'. I've sorted that, but
it still makes no difference. :((
The values it's passing to '$sth->execute' are:
1531499623, -1516480662, 2001-5-29 00:00:00, 3, May-2001
Help!
Tim
- Original Message -
From: "Tim Scott" <[EMAIL PROTECTED]>
To: "Trevor Webster" <[EMAIL PROTECTED]>
Cc: "DBI Users" <[EMAIL PROTECTED]>
Sent: Wednesday, May 30, 2001 9:09 AM
Subject: Re: DBI -> Access
>
> Thanks for your input on this guys. Here's some more information which
might
> help, but I think it precludes these problems...
>
> The access database file is a file on my local hard drive to which I have
> full and complete access. I can indeed add to this very table through
Access
> itself.
>
> The PERL script is running through the Apache web server as a CGI script
and
> is connecting to the Access database as 'Admin'.
>
> The operation I am doing is an insert into one table. I have enclosed ALL
of
> the DBI calls from my script (it's not a complex one !)
>
> -
> $dbh_eng = DBI->connect("dbi:ODBC:EngTest", "Admin", "admin");
> # Also tried with "" password. No change.
>
> $sth = $dbh_eng->prepare(q(
> insert into WorkDone (person_id, job_id, Workdate, hours, month)
> values (?,?,?,?,?);
> )) ||
> splat "Failed to prepare insert";
>
> {{ snip - lines not referencing $dbh_eng or $sth }}
>
> $sth->execute($bod, $jobs[$i], $workdate, $hours[$i], $month)
> || splat "Failed to insert\n";
>
> {{ snip - lines not referencing $dbh_eng or $sth }}
>
> eval { $sth->finish; };
> eval { undef $$sth; }; # otherwise I get an error in the webserver log
file
>
> $dbh_eng->disconnect();
> -
>
> The WorkDone table has two referntial constraints enforced which are
valid.
> I have tried disabling them (and they still are) but it had no effect.
>
> The table is owned by Admin who has select, insert, update and delete
> privileges.
>
> Please let me know if any more information would help.
>
> Thanks,
> Tim
>
> >From: [EMAIL PROTECTED]
> >Date: Tue, 29 May 2001 12:51:25 -0400
> >
> >This is an MS Access problem, and has nothing to do with DBI. Read the
> Access
> >documentation about updatable queries.
> >
> >In general, when a query results from certain types of joins, Access
> doesn't
> >know how to allow updates to the resultset; I think it has something to
do
> with
> >how they do record locking. I'm sure Oracle supports row-level record
> locks and
> >generally handles the situation better, so it was able to support what
> you're
> >doing. DBI can't solve the problem. You can probably accomplish what
you
> need
> >to while still using Access, if you break up your operation into smaller
> >queries, or only update one table at a time.
> >
> >HTH.
> >
> >Wes
>
> - Original Message -
> From: "Trevor Webster" <[EMAIL PROTECTED]>
> To: "Tim Scott" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 30, 2001 7:46 AM
> Subject: Re: DBI -> Access
>
>
> > Tim
> >
> > I had a similar problem when I first started using DBI-ODBC. After
> > consulting with the DBI-users I found that the problem was associated
not
> > with DBI nor Access but with permissions on the sub-directory in which
the
> > database was located. Make sure that the directory structure has
> read/write
> > permissions throughout and this should, touch wood, solve your problem.
> >
> > Cheers Trevor
> >
> > - Original Message -
> > From: "Tim Scott" <[EMAIL PROTECTED]>
> > To: "DBI Users" <[EMAIL PROTECTED]>
> > Sent: Tuesday, May 29, 2001 5:16 PM
> > Subject: DBI -> Access
> >
> >
> > > All,
> > >
> > > New to Access I'm finding:-
> > > [Microsoft][ODBC Microsoft Access Driver] Operation must use an
> updateable
> > > query. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
> > >
> > > And thinking "how do I tell it that it's an updateable query?"
> > >
> > > The same sort of thing worked fine with Oracle...
> > >
> > > Any help appreciated.
> > >
> > > Thanks,
> > > Tim
> > >
> > > The information transmitted in this electronic mail message may
contain
> > > confidential and or privileged materials. For full details and
> > restrictions
> > > see http://www.fdgroup.com/emaildisclaimer.html
> > >
> >
> >
>
>
Re: DBI -> Access
Thanks for your input on this guys. Here's some more information which might
help, but I think it precludes these problems...
The access database file is a file on my local hard drive to which I have
full and complete access. I can indeed add to this very table through Access
itself.
The PERL script is running through the Apache web server as a CGI script and
is connecting to the Access database as 'Admin'.
The operation I am doing is an insert into one table. I have enclosed ALL of
the DBI calls from my script (it's not a complex one !)
-
$dbh_eng = DBI->connect("dbi:ODBC:EngTest", "Admin", "admin");
# Also tried with "" password. No change.
$sth = $dbh_eng->prepare(q(
insert into WorkDone (person_id, job_id, Workdate, hours, month)
values (?,?,?,?,?);
)) ||
splat "Failed to prepare insert";
{{ snip - lines not referencing $dbh_eng or $sth }}
$sth->execute($bod, $jobs[$i], $workdate, $hours[$i], $month)
|| splat "Failed to insert\n";
{{ snip - lines not referencing $dbh_eng or $sth }}
eval { $sth->finish; };
eval { undef $$sth; }; # otherwise I get an error in the webserver log file
$dbh_eng->disconnect();
-
The WorkDone table has two referntial constraints enforced which are valid.
I have tried disabling them (and they still are) but it had no effect.
The table is owned by Admin who has select, insert, update and delete
privileges.
Please let me know if any more information would help.
Thanks,
Tim
>From: [EMAIL PROTECTED]
>Date: Tue, 29 May 2001 12:51:25 -0400
>
>This is an MS Access problem, and has nothing to do with DBI. Read the
Access
>documentation about updatable queries.
>
>In general, when a query results from certain types of joins, Access
doesn't
>know how to allow updates to the resultset; I think it has something to do
with
>how they do record locking. I'm sure Oracle supports row-level record
locks and
>generally handles the situation better, so it was able to support what
you're
>doing. DBI can't solve the problem. You can probably accomplish what you
need
>to while still using Access, if you break up your operation into smaller
>queries, or only update one table at a time.
>
>HTH.
>
>Wes
- Original Message -
From: "Trevor Webster" <[EMAIL PROTECTED]>
To: "Tim Scott" <[EMAIL PROTECTED]>
Sent: Wednesday, May 30, 2001 7:46 AM
Subject: Re: DBI -> Access
> Tim
>
> I had a similar problem when I first started using DBI-ODBC. After
> consulting with the DBI-users I found that the problem was associated not
> with DBI nor Access but with permissions on the sub-directory in which the
> database was located. Make sure that the directory structure has
read/write
> permissions throughout and this should, touch wood, solve your problem.
>
> Cheers Trevor
>
> - Original Message -
> From: "Tim Scott" <[EMAIL PROTECTED]>
> To: "DBI Users" <[EMAIL PROTECTED]>
> Sent: Tuesday, May 29, 2001 5:16 PM
> Subject: DBI -> Access
>
>
> > All,
> >
> > New to Access I'm finding:-
> > [Microsoft][ODBC Microsoft Access Driver] Operation must use an
updateable
> > query. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
> >
> > And thinking "how do I tell it that it's an updateable query?"
> >
> > The same sort of thing worked fine with Oracle...
> >
> > Any help appreciated.
> >
> > Thanks,
> > Tim
> >
> > The information transmitted in this electronic mail message may contain
> > confidential and or privileged materials. For full details and
> restrictions
> > see http://www.fdgroup.com/emaildisclaimer.html
> >
>
>
DBI -> Access
All, New to Access I'm finding:- [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1) And thinking "how do I tell it that it's an updateable query?" The same sort of thing worked fine with Oracle... Any help appreciated. Thanks, Tim The information transmitted in this electronic mail message may contain confidential and or privileged materials. For full details and restrictions see http://www.fdgroup.com/emaildisclaimer.html
Re: Connection pb with 2 oracle version! Please help!
Vincent,
To connect to the Oracle7 database using the Net8 tnsnames entry with Perl
linked to Oracle8 you will need ORACLE_HOME set to
'/export/home/oracle/app/oracle/product/8.1.6'.
ORACLE_SID is irrelevant for this type of connection as it is specified in
the tnsnames file.
The connect string (or TWO_TASK) value should be used as the first argument
to DBI->connect.
HTH,
Tim
- Original Message -
From: "Vincent Roquencourt" <[EMAIL PROTECTED]>
To: "Tim Scott" <[EMAIL PROTECTED]>
Cc: "Vincent Roquencourt" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, May 29, 2001 10:50 AM
Subject: Re: Connection pb with 2 oracle version! Please help!
> On Tue, May 29, 2001 at 10:28:01AM +0100, Tim Scott wrote:
> > Voncent,
> >
> > You'd be better to setup a tnsnames entry in your Oracle8.1.6 area to
point
> > at your Oracle 7.3.4 database and using this as a connect string.
> >
> > If you don't do this, you're using Oracle8 function calls from your
Perl/DBI
> > build to try to talk to an Oracle7 database. This is asking for
trouble -
> > even if you do eventually connect.
> >
> > Regards,
> > Tim
>
> Hi Tim,
> thanks for your quick reply, however i just tested it:
> $ENV{ORACLE_HOME}= '/export/home/oracle/app/oracle/product/7.3.4';
> $ENV{ORACLE_SID}='mydb';
> print "$ENV{ORACLE_HOME} $ENV{ORACLE_SID}\n";
> if( !( $dbh = DBI->connect($ENV{ORACLE_SID},$USER,$PASS,
'Oracle') ) ) {
> print "not connected\n $DBI::errstr";
> }
> else{
> print "connected\n"
> };
>
>
> #this is supposed to connect using tnsnames.ora (sqlplus $USER@mysid works
with both db)
> i get the same error with the 7.3.4:
>
> /export/home/oracle/app/oracle/product/7.3.4 mydb
> not connected
> ORA-01005: null password given; logon denied (DBD ERROR: OCISessionBegin)
>
> but not with the 8.1.6!
> /export/home/oracle/app/oracle/product/8.1.6 deuro
> connected
>
> user exists on both db (tested with sqlplus via sqlnet)
>
>
> HLp!!! :)
>
>
Re: Connection pb with 2 oracle version! Please help!
Voncent,
You'd be better to setup a tnsnames entry in your Oracle8.1.6 area to point
at your Oracle 7.3.4 database and using this as a connect string.
If you don't do this, you're using Oracle8 function calls from your Perl/DBI
build to try to talk to an Oracle7 database. This is asking for trouble -
even if you do eventually connect.
Regards,
Tim
- Original Message -
From: "Vincent Roquencourt" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 29, 2001 9:56 AM
Subject: Connection pb with 2 oracle version! Please help!
> Hi,
> using DBI, DBD::Oracle without any problem since 2 years i came
> acrossa weird behaviour:
> Environnement: Solaris 2.6
>Oracle 8.1.6 AND 7.3.4 on same machine
>DBI 1.15
>DBD 1.06
>
> Since yesterday i recompiled DBD,DBI with oracle 8.1.6 environnment.
> I can connect to the 8.1.6 db but now i can't connect to the 7.3.4!!
> here is the connection line of my script
> $ENV{ORACLE_HOME} is set to 7.3.4 ORACLE_HOME,
> $USER and $PASS are set in the script,
>
>if( !( $dbh =
DBI->connect("dbi:Oracle:host=mmyhost;sid=$sid",$USER,$PASS))) {
>push @dbs_down,$sid;
>}
>
> i get the following error when i tried to connect to the 7.3.4
> DBI->connect(host=myhost;sid=xxx) failed: ORA-01005: null password given;
logon
> denied (DBD ERROR: OCISessionBegin) at oracle-bb.pl line 59
>
> everything is fine with the 8.1.6 db..
>
> what is the problem? can t i connect to 2 version of oracle db?
>
> thanks
>
>
>
>
>
>
> --
> Vincent Roquencourt
> Business Objects
> DBA Team
Re: DBI - for Oracle 8i
Denis, When you did the 'perl Makefile.PL', did it find Oracle and everything correctly ? Is there any particular reason you're using 'Makefile.aperl', rather than simply 'make' ? Which version of DBI are you using ? Tim - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 25, 2001 11:11 AM Subject: DBI - for Oracle 8i Hi gurus, I have installed Oracle 8i enterprise edition on Linux and also successfully installed DBI module. When i installed DBD::Oracle module, it shows me this error --- You have a wait.ph file generated by perl h2ph utility. It does not define a WCOREDUMP function. That's probably an error. If a DBD::Oracle test fails then you will probably see a message from Test::Harness about WCOREDUMP being undefined. You can either ignore it or try to fix your wait.ph file. The message does not reflect the cause of the test failure, it's just a problem interpreting the failure. *** If you have problems, read the README and README.help files *** (Of course, you have read README by now anyway, haven't you?) make -f Makefile.aperl perl make[1]: Entering directory `/denis' Writing perlmain.c cd . && cc -c -I/usr/lib/perl5/5.00503/i386-linux/CORE -Dbool=char -DHAS_BOOL -I/u sr/local/include -O2 \ -DVERSION=\"1.06\" \ -DXS_VERSION=\"1.06\" -I/usr/lib/perl5/5.00503/i386-linux/CORE perlmain.c cat /usr/lib/perl5/5.00503/i386-linux/auto/DynaLoader/extralibs.ld >> blib/arch/auto/DBD/Oracle/extralibs.all cat blib/arch/auto/DBD/Oracle/extralibs.ld >> blib/arch/auto/DBD/Oracle/extralibs.all cc -L/usr/local/lib -rdynamic -o perl -O2 ./perlmain.o blib/arch/auto/DBD/Oracle/Oracle.a /usr/lib/perl5/5.00503/i386-linux/auto/DynaLoader/DynaLoader.a /usr/lib/perl5/5.00503/i386-linux/CORE/libperl.a `cat blib/arch/auto/DBD/Oracle/extralibs.all` -lnsl -ldl -lm -lc -lposix -lcrypt cc: /usr/oracle/rdbms/lib/libskgxpd..a: No such file or directory cc: /usr/oracle/rdbms/lib/libskgxpu..a: No such file or directory cc: /usr/oracle/rdbms/lib/libskgxpt..a: No such file or directory make[1]: *** [perl] Error 1 make[1]: Leaving directory `/denis' make: *** [perl] Error 2 [root@coral /denis]# - When i am testing this by "make test", it gives this error -- [root@coral /denis]# make test PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib -I/usr/lib/perl5/5.00503/i386-linux -I/ usr/lib/perl5/5.00503 -e 'use Test::Harness qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/base..FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/general...install_driver(Oracle) failed: Can't load 'blib/arch/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.00503/i386-linux/DynaLoader.pm line 169. at (eval 1) line 3 Perhaps a required shared library or dll isn't installed where expected at t/general.t line 20 dubious Test returned status 255 (wstat 65280, 0xff00) Undefined subroutine &Test::Harness::WCOREDUMP called at /usr/lib/perl5/5.00503/Test/Harness.pm line 288. make: *** [test_dynamic] Error 255 [root@coral /denis]# I have tried installation approx 15 times, but all the times same error.. Could any one help me ???? Pl note : I have installed MySql and it is working ok. Also, i can access MySql database using DBI:MySql module. I can also log into Oracle database using scott/tiger@oralin, so this is also ok. pl. pl. help me TIA Denis
