Re: Checking for the existence of a certain row.
James, James Kufrovich wrote: Hi. I'd like to check for the existence of data in a row of a database (MySQL), if given the value of a primary key. (Is there already a row in the database that has this value as a key?) I don't care what data is in the row, or if more than one row (!!) is found. I'd hope that whatever method I use will stop searching the database after it finds the first match. If it is a Primary Key then there there will be at most one row with that value in the table. What would be the best way to go about this? Is there a special method that can do this, or would I have to use selectrow_array (or fetchrow_array or one of those) and then see if it finds anything? I suppose I can call 'finish' as soon as a match is found, if the method (or whatever) doesn't stop by itself. I'd appreciate any tips. Thanks. Try select count(*) from fred where primaryKey = 'bert' as the query I'm don't know how MySQL handles primary keys but other databases put a Unique index on the column. The SQL above _should_ just look at the index (or you could find the index name and use that in the query). --MarkT
Re: Installing dbi on windows 98
Hy Michael,
The MSDOS shell ignores the #! line and Perl doesn't worry about the part
before 'perl', so it's better to leave it as #!/usr/bin/perl ... in case
you decide later to move to a system with a real shell. The -wT is a good
idea.
Your right! Do not code the perlscript in your DOS-Shell. Put it into
your cgi-bin directory and run it from html, right?
--
#!C:\perl\bin\perl.exe -wT
print map {chr(ord($_)-3)} split //, Dqguhdv0Vfkplw}Cw0rqolqh1gh;
# http://www.medialsoft.de
#! line parsing
- Original Message - From: Andreas-Schmitz [EMAIL PROTECTED] To: Michael A. Chase [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 06, 2001 16:10 Subject: Re: Installing dbi on windows 98 The MSDOS shell ignores the #! line and Perl doesn't worry about the part before 'perl', so it's better to leave it as #!/usr/bin/perl ... in case you decide later to move to a system with a real shell. The -wT is a good idea. Your right! Do not code the perlscript in your DOS-Shell. Put it into your cgi-bin directory and run it from html, right? -- #!C:\perl\bin\perl.exe -wT I don't know why so many have so much trouble with this. I am not talking about the physical location of the Perl script. I am talking about the content of the first line of the Perl script file. 1. The shell that comes with MSDOS derivatives (including all versions of Windows) _DO_NOT_ understand the #! line so putting a MSDOS path there has no effect. 2. Most UNIX shells do recognize the #! line and will call whatever program is given there to interpret the file. This works the same whether you are using Perl, Bourne Shell, C Shell, Bash, env, or some other program known only to you. 3. The perl interpreter will parse the #! line starting after the word 'perl' for switches so switch parsing by the perl interpreter will be consistent across platforms. So it follows that putting c:\perl\bin\perl.exe in the #! line provides no advantage in MSDOS derived systems and will interfere with running the script in UNIX systems. Any script that might be run in both environments should use the UNIX location (usually /usr/bin/perl) on the #! line. I have many scripts that run _completely_unchanged_ in Win95, Win98, WinMe, WinNT, Win2000, Dynix, HPUX, Solaris, an Linux. They all have #!/usr/bin/perl -w as the first line. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: Installing dbi on windows 98
-Original Message-
From: Andreas-Schmitz [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 06, 2001 7:10 PM
To: Michael A. Chase
Cc: [EMAIL PROTECTED]
Subject: Re: Installing dbi on windows 98
Hy Michael,
The MSDOS shell ignores the #! line and Perl doesn't worry
about the part
before 'perl', so it's better to leave it as #!/usr/bin/perl
... in case
you decide later to move to a system with a real shell. The
-wT is a good
idea.
Your right! Do not code the perlscript in your DOS-Shell.
Why not?
Put it into
your cgi-bin directory and run it from html, right?
From the browser you mean, right?
--
#!C:\perl\bin\perl.exe -wT
print map {chr(ord($_)-3)} split //, Dqguhdv0Vfkplw}Cw0rqolqh1gh;
# http://www.medialsoft.de
(Fwd) .MDX
- Forwarded message from Greg Jackson [EMAIL PROTECTED] - From: Greg Jackson [EMAIL PROTECTED] To: Tim.Bunce [EMAIL PROTECTED] Subject: .MDX Date: Sat, 7 Jul 2001 19:22:52 +0100 Dear Tim Apologies for unsolicited email, but I have been searching for any information that will tell me what database I'm using - with .mdx files in there and found a message from you to a message board asking a similar quiestion Did you ever find an answer? If so, I'd be really grateful if you could tell me anything! Best regards, and apologies for the disturbance, Greg Jackson - End forwarded message -
Re: #! line parsing
2. Most UNIX shells do recognize the #! line and will call whatever program is given there to interpret the file. This works the same whether you are using Perl, Bourne Shell, C Shell, Bash, env, or some other program known only to you. Apache on Win32 platforms does the same thing. Given the usefulness of perl+DBI for CGI applications, and the prevalence of Apache on even Windows boxes, having a correctly formed shebang line is important on any platform. I don't think that having #!e:\perl\bin\perl.exe -w as the first line in my scripts is anywhere near as foolish as thinking it's safe to assume that /usr/bin/perl is the location of the perl interpreter on a *NIX box. Jason..
RE: Checking for the existence of a certain row.
do a SELECT COUNT(*) FROM tablename WHERE ... A count is almost always faster than actually returning that row, and requires even less network bandwidth to return the result. Of course, it a result of 1 is returned - the row exists. Steve H. -Original Message- From: Steven Lembark [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 07, 2001 11:54 AM To: [EMAIL PROTECTED] Subject: Re: Checking for the existence of a certain row. - James Kufrovich [EMAIL PROTECTED] on 07/06/01 17:08:56 -0400: I'd like to check for the existence of data in a row of a database (MySQL), if given the value of a primary key. (Is there already a row in the database that has this value as a key?) I don't care what data is in the row, or if more than one row (!!) is found. I'd hope that whatever method I use will stop searching the database after it finds the first match. What would be the best way to go about this? Is there a special method that can do this, or would I have to use selectrow_array (or fetchrow_array or one of those) and then see if it finds anything? I suppose I can call 'finish' as soon as a match is found, if the method (or whatever) doesn't stop by itself. I'd appreciate any tips. Thanks. select keyfield1, keyfield2,... keyfieldN from tablename; If the list of keyfield's is your PK then this should return rather quickly.
RE: Checking for the existence of a certain row.
- Steve Howard [EMAIL PROTECTED] on 07/07/01 17:54:18 -0500: do a SELECT COUNT(*) FROM tablename WHERE ... A count is almost always faster than actually returning that row, and requires even less network bandwidth to return the result. Of course, it a result of 1 is returned - the row exists. Don't try this in Oracle, it immediately degenerates into a table scan. MySql may handle this differently but selecting the key (or the first field with restriction on the PK) will be equally fast w/o fewer risks. sl
Comparison not producing what is expected.
I have put together a script that was intended to move all existing
non-clustered indexes in a SQL 7 database to the secondary filegroup.
However, I am getting unexpected results in a comparison. Here is a snippet
from what I am doing that contains my print statement (To verify that I'm
getting the values for comparison that I need) and the output that shows it
is not acting as expected. Any help is appreciated:
foreach (@table) {
print outfile \n\n\n--$_ :\n\n;
$selecth = $dbh-prepare(qq{sp_helpindex $_}) || die can't prepare
sp_helpindex\n$dbh::errstr\n;
$selecth-execute() || die Can't execute sp_helpindex\n$dbh::errstr\n;
my ($row, $name, $descr, $keynames, $create, $drop);
eval{
$selecth-bind_columns(undef, \($name, $descr, $keynames));
while ($row = $selecth-fetchrow_arrayref) {
my @keyarray = split /, /, $keynames;
$keynames = '['. join('], [', @keyarray).']';
$countone = grep /^nonclustered/i, $descr;
$counttwo = grep /unique/i, $descr;
$countthree = grep /primary key/i, $descr;
$create = qq{CREATE NONCLUSTERED INDEX \[$name\] \n\tON
$_($keynames) ON SECONDARY\n}
if ($countone == 1 and $counttwo == 0 and
$countthree == 0);
$create = qq{CREATE UNIQUE NONCLUSTERED INDEX \[$name\]
\n\tON $_($keynames) ON SECONDARY\n}
if ($countone == 1 and $counttwo == 1 and
$countthree == 0);
$create = qq{ALTER TABLE $_ \n\tadd constraint
$name PRIMARY KEY NONCLUSTERED($keynames) on SECONDARY\n}
if ($countone == 1 and
$countthree == 1);
$drop = qq{DROP INDEX $_.\[$name\]\n} if ($countone == 1
and $countthree == 0);
$drop = qq{alter table $_ drop constraint
$name\n}
if ($countone == 1 and $counttwo ==
1 and $countthree == 1);
print
$name,\t$countone\t$counttwo\t$countthree\n$drop$create
if ($countone == 1 and $counttwo == 1 and
$countthree == 1) ;
print outfile $drop.$create;
}
}
}
I see my indent format went bad when I pasted that. However that's the
comparison. I need to handle the nonclustered primary keys different from a
nonclustered unique index. That's why I go through the pains of the
comparisons. Here is an example of two different results that are printed
showing something behaving different from what I expect:
one output:
CMS_FTEXT_COLUM0, 1 1 1
alter table dbo.[CMS_FTEXT_COLUM] drop constraint CMS_FTEXT_COLUM0
ALTER TABLE dbo.[CMS_FTEXT_COLUM]
add constraint CMS_FTEXT_COLUM0 PRIMARY KEY
NONCLUSTERED([COLUMN_ID]) on SECONDARY
A second output:
MKA_FEVENT_MAIL0, 1 1 1
alter table dbo.[MKA_FEVENT_MAIL] drop constraint MKA_FEVENT_MAIL0
ALTER TABLE dbo.[MKA_FEVENT_MAIL]
add constraint MKA_FEVENT_MAIL0 PRIMARY KEY
NONCLUSTERED([FIRM_EVENT_UNO], [MAIL_UNO]) on SECONDARY
another output:
GLT_JRNL_Z0,1 1 1
alter table dbo.[GLT_JRNL_Z] drop constraint GLT_JRNL_Z0
CREATE UNIQUE NONCLUSTERED INDEX [GLT_JRNL_Z0]
ON dbo.[GLT_JRNL_Z]([STATUS], [JE_NUMBER]) ON SECONDARY
Perousing the outfile (over 800 tables and over 1500 nonclustered indexes, I
haven't been through every one yet) it looks as if it always get the right
statement for $drop, but although about 90% of the $create lines that are
printed are the right one, there are some like the third example which
should use an alter table to add back a primary key constraint that instead
just create a unique nonclustered index. You can see from the output that
all three values are 1, so how can it be doing this? What am I missing? how
can it make the right evaluation most of the time, but not all of the time?
Steve H.
Re: Checking for the existence of a certain row.
What I do is:
my $statement = q{select 1 from x where xyz = ?};
Prepare:
my $exist_sth = $dbh-prepare( $statement );
Then call selectrow_array with a scalar:
$does_exists = $dbh-selectrow_array($statement, undef, $key_to_check );
Tom
On Fri, Jul 06, 2001 at 05:08:56PM -0400, James Kufrovich wrote:
Hi.
I'd like to check for the existence of data in a row of a database
(MySQL), if given the value of a primary key. (Is there already a row in
the database that has this value as a key?) I don't care what data is in
the row, or if more than one row (!!) is found. I'd hope that whatever
method I use will stop searching the database after it finds the first
match.
What would be the best way to go about this? Is there a special
method that can do this, or would I have to use selectrow_array (or
fetchrow_array or one of those) and then see if it finds anything? I
suppose I can call 'finish' as soon as a match is found, if the method (or
whatever) doesn't stop by itself. I'd appreciate any tips. Thanks.
Jamie Kufrovich
Egg, eggie (at) sunlink (dot) net
FMSp3am/MSp3am A- C+ D H+ M+ P+++ R+ T W Z+
Sp#/p++ RL-CT a+ cl++ d? e++ f h* i+ j p+ sm+
--
Thomas A. Lowery
See DBI/FAQ http://tlowery.hypermart.net
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
RE: Checking for the existence of a certain row.
OK, one more yes-no. I tried this for performance and as expected, it is faster than count on MS and Sybase (and count is very fast on MS). You might see if there is a variation of it you can use with the Oracle Decode functionI can't answer if there is or not, however in either case even though this is probably faster, it won't be portable between some dbms's. SELECT CASE WHEN EXISTS(SELECT somecolumn FROM customers WHERE columnname ='thisvalue') then 1 ELSE 0 END (I'm installing Oracle on Solaris right now. I'm anxious to get my hands on it. I've only been off of mainframes for a couple of years, and haven't used it, but hear some interesting things, like trim() in the where clause causes indexes to not be used, and count causes a table scan. I'm sure it's all accurate, but it certainly poses some challenges I've never thought about having to deal with). Anyway, just add that one to the possibilities of testing for the existence from within a Perl script. Steve H. -Original Message- From: Steven Lembark [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 07, 2001 6:16 PM To: [EMAIL PROTECTED] Subject: RE: Checking for the existence of a certain row. - Steve Howard [EMAIL PROTECTED] on 07/07/01 17:54:18 -0500: do a SELECT COUNT(*) FROM tablename WHERE ... A count is almost always faster than actually returning that row, and requires even less network bandwidth to return the result. Of course, it a result of 1 is returned - the row exists. Don't try this in Oracle, it immediately degenerates into a table scan. MySql may handle this differently but selecting the key (or the first field with restriction on the PK) will be equally fast w/o fewer risks. sl
CPAN Upload: T/TI/TIMB/Oracle-OCI-0.03.tar.gz
The uploaded file Oracle-OCI-0.03.tar.gz has entered CPAN as file: $CPAN/authors/id/T/TI/TIMB/Oracle-OCI-0.03.tar.gz size: 27651 bytes md5: f51da2b5d44ba4e0b4f58e7c2d1e6378 No action is required on your part Request entered by: TIMB (Tim Bunce) Request entered on: Sun, 08 Jul 2001 02:07:35 GMT Request completed: Sun, 08 Jul 2001 02:09:02 GMT Virtually Yours, Id: paused,v 1.74 2001/05/20 14:59:52 k Exp k
