RE: MySQL upgrade caused Perl coredumps...

[NIPP, SCOTT V \(SBCSI\)]

I just stumbled across something providing a little more
information and not making anything any clearer.  Here is the database
related module information...

 perl -MDBI -e 'DBI->installed_versions'
  Perl: 5.008007(PA-RISC2.0)
  OS  : hpux(11.11)
  DBI : 1.48
  DBD::mysql  : 3.0008
  DBD::Sponge : 11.10
  DBD::Proxy  : install_driver(Proxy) failed: Can't locate
RPC/PlClient.pm in @INC
  DBD::Oracle : 1.16
  DBD::File   : 0.33
  DBD::ExampleP   : 11.12
  DBD::DBM: 0.02
  DBD::CSV: 0.22

Judging by this I am already running the latest DBD::MySQL,
though I am about 5 versions behind on the DBI module.  I hope all of
this helps.

-Original Message-
From: NIPP, SCOTT V (SBCSI) 
Sent: Saturday, November 04, 2006 12:02 PM
To: 'Darren Duncan'; dbi-users@perl.org
Subject: RE: MySQL upgrade caused Perl coredumps...


I'm kind of stuck with the MySQL version here.  This is the
pre-built HP version that we are required to use.  This is the latest
version available compiled by HP.  I am now attempting to build
DBD::MySQL-3.0008 and running into some make test errors.  Here is a
sample of the errors.

t/40nulls/usr/lib/dld.sl: Unresolved symbol: __udivdi3
(code)  from
/home/sadmin/sn4265/perl/DBD-mysql-3.0008/blib/arch/auto/DBD/mysql/mysql
.sl
/usr/lib/dld.sl: Unresolved symbol: __umoddi3 (code)  from
/home/sadmin/sn4265/perl/DBD-mysql-3.0008/blib/arch/auto/DBD/mysql/mysql
.sl
install_driver(mysql) failed: Can't load
'/home/sadmin/sn4265/perl/DBD-mysql-3.0008/blib/arch/auto/DBD/mysql/mysq
l.sl' for module DBD::mysql: Unresolved external at
/opt/perl/lib/5.8.7/PA-RISC2.0/DynaLoader.pm line 230.
 at (eval 1) line 3
Compilation failed in require at (eval 1) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at t/40nulls.t line 50
t/40nullsdubious
Test returned status 215 (wstat 55040, 0xd700)
DIED. FAILED tests 1-11
Failed 11/11 tests, 0.00% okay

I got a lot of these, in fact for just about every test.  The
DBD::MySQL module is the only one I am attempting to upgrade.  I'm not
sure if I need to update my DBI module or now.  One question I have is
how do I check the module versions?  Thanks in advance for any
assistance.

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Saturday, November 04, 2006 3:54 AM
To: dbi-users@perl.org
Subject: Re: MySQL upgrade caused Perl coredumps...


At 10:35 PM -0600 11/3/06, NIPP, SCOTT V \(SBCSI\) wrote:
>   I recently upgraded MySQL to version 5.0.4.  After the upgrade,
>several of my Perl scripts that access the MySQL database began to fail
>with "Memory fault coredump" messages.  Anyone have any suggestions?
>I'm hoping a simple DBI::MySQL upgrade will fix this issue.  Here is
the
>Perl information...

Before you go about with that, you should move up to a production 
release of MySQL, such as 5.0.2x ... MySQL 5.0.4 was an alpha or beta 
release, and is well over a year old already.  Unless you mistyped 
and meant to say 5.0.24.

Also, if you're using MySQL v4.1 or higher, you should be using the 
DBD::mysql v3.x series of drivers.  If nothing else, because the 2.9x 
series will fail to connect due to MySQL user password hashes having 
changed in length between 4.0 and 4.1, which affects database users 
created after the switch.

-- Darren Duncan

RE: MySQL upgrade caused Perl coredumps...

[NIPP, SCOTT V \(SBCSI\)]

I'm kind of stuck with the MySQL version here.  This is the
pre-built HP version that we are required to use.  This is the latest
version available compiled by HP.  I am now attempting to build
DBD::MySQL-3.0008 and running into some make test errors.  Here is a
sample of the errors.

t/40nulls/usr/lib/dld.sl: Unresolved symbol: __udivdi3
(code)  from
/home/sadmin/sn4265/perl/DBD-mysql-3.0008/blib/arch/auto/DBD/mysql/mysql
.sl
/usr/lib/dld.sl: Unresolved symbol: __umoddi3 (code)  from
/home/sadmin/sn4265/perl/DBD-mysql-3.0008/blib/arch/auto/DBD/mysql/mysql
.sl
install_driver(mysql) failed: Can't load
'/home/sadmin/sn4265/perl/DBD-mysql-3.0008/blib/arch/auto/DBD/mysql/mysq
l.sl' for module DBD::mysql: Unresolved external at
/opt/perl/lib/5.8.7/PA-RISC2.0/DynaLoader.pm line 230.
 at (eval 1) line 3
Compilation failed in require at (eval 1) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at t/40nulls.t line 50
t/40nullsdubious
Test returned status 215 (wstat 55040, 0xd700)
DIED. FAILED tests 1-11
Failed 11/11 tests, 0.00% okay

I got a lot of these, in fact for just about every test.  The
DBD::MySQL module is the only one I am attempting to upgrade.  I'm not
sure if I need to update my DBI module or now.  One question I have is
how do I check the module versions?  Thanks in advance for any
assistance.

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Saturday, November 04, 2006 3:54 AM
To: dbi-users@perl.org
Subject: Re: MySQL upgrade caused Perl coredumps...


At 10:35 PM -0600 11/3/06, NIPP, SCOTT V \(SBCSI\) wrote:
>   I recently upgraded MySQL to version 5.0.4.  After the upgrade,
>several of my Perl scripts that access the MySQL database began to fail
>with "Memory fault coredump" messages.  Anyone have any suggestions?
>I'm hoping a simple DBI::MySQL upgrade will fix this issue.  Here is
the
>Perl information...

Before you go about with that, you should move up to a production 
release of MySQL, such as 5.0.2x ... MySQL 5.0.4 was an alpha or beta 
release, and is well over a year old already.  Unless you mistyped 
and meant to say 5.0.24.

Also, if you're using MySQL v4.1 or higher, you should be using the 
DBD::mysql v3.x series of drivers.  If nothing else, because the 2.9x 
series will fail to connect due to MySQL user password hashes having 
changed in length between 4.0 and 4.1, which affects database users 
created after the switch.

-- Darren Duncan

MySQL upgrade caused Perl coredumps...

[NIPP, SCOTT V \(SBCSI\)]

I recently upgraded MySQL to version 5.0.4.  After the upgrade,
several of my Perl scripts that access the MySQL database began to fail
with "Memory fault coredump" messages.  Anyone have any suggestions?
I'm hoping a simple DBI::MySQL upgrade will fix this issue.  Here is the
Perl information...

Summary of my perl5 (revision 5 version 8 subversion 7) configuration:
  Platform:
osname=hpux, osvers=11.11, archname=PA-RISC2.0
uname='hp-ux r3 b.11.11 u 9000800 1909236376 unlimited-user license
'
config_args='-Dusedevel -Dcc=gcc -Uversiononly
-Dinc_version_list=none -des'
hint=recommended, useposix=true, d_sigaction=define
usethreads=undef use5005threads=undef useithreads=undef
usemultiplicity=undef
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
  Compiler:
cc='gcc', ccflags ='-mpa-risc-2-0 -D_HPUX_SOURCE -DDEBUGGING
-fno-strict-aliasing -pipe -I/pro/local/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64',
optimize='-g -O',
cppflags='-mpa-risc-2-0 -D_HPUX_SOURCE -mpa-risc-2-0 -D_HPUX_SOURCE
-DDEBUGGING -fno-strict-aliasing -pipe -I/pro/local/include'
ccversion='', gccversion='3.4.4', gccosandvers='hpux11.11'
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
lseeksize=8
alignbytes=8, prototype=define
  Linker and Libraries:
ld='/usr/bin/ld', ldflags ='-L/pro/local/lib'
libpth=/pro/local/lib /lib /usr/lib /usr/ccs/lib /usr/local/lib
libs=-lcl -lpthread -lnsl -lnm -lndbm -lgdbm -ldb -lmalloc -ldld -lm
-lcrypt -lsec -lc
perllibs=-lcl -lpthread -lnsl -lnm -lmalloc -ldld -lm -lcrypt -lsec
-lc
libc=/lib/libc.sl, so=sl, useshrplib=false, libperl=libperl.a
gnulibc_version=''
  Dynamic Linking:
dlsrc=dl_hpux.xs, dlext=sl, d_dlsymun=undef, ccdlflags='-Wl,-E
-Wl,-B,deferred '
cccdlflags='-fPIC', lddlflags='-b -L/pro/local/lib'


Characteristics of this binary (from libperl):
  Compile-time options: DEBUGGING USE_LARGE_FILES
  Locally applied patches:
defined-or
  Built under hpux
  Compiled at Jun  1 2005 13:42:32
  @INC:
/opt/perl/lib/5.8.7/PA-RISC2.0
/opt/perl/lib/5.8.7
/opt/perl/lib/site_perl/5.8.7/PA-RISC2.0
/opt/perl/lib/site_perl/5.8.7
/opt/perl/lib/site_perl
.

>   Scott Nipp
Sr. Systems Analyst
AT&T Unix Administration
>   (214) 858-1289
> 
For non-emergency HP UNIX work /support requests please open an RM
ticket online at: 
http://euss.sbc.com   or by sending an email
to: "unix sss email  " 
For outage or emergency HP UNIX issues, please refer to the oncall list
at: http://unixsss.sbc.com/oncall/mtsshpwest_oncall.html

RE: switching perl version

[NIPP, SCOTT V \(SBCSI\)]

I would definitely look to Oracle rather than Perl as your
culprit.  I don't have anything to back this up, just a suspicion.

-Original Message-
From: Oscar Gomez [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 08, 2006 10:04 AM
To: dbi-users@perl.org
Subject: switching perl version


A perl program executing in linux Redhat 7.0, perl version 5.6, oracle
8i was
using 30% cpu approx. Now I'm running the same program in linux
enterprise ES 4,
perl v. 5.8.5, Oracle 10g. uses 60% cpu. I'd like to know what's
happening
because the performance is slower and the difference is wide big.
Do you think this perl new version (5.8.5) takes more cpu ?
I appreciate any idea you could give me to improve my performance.

--
Open WebMail Project (http://openwebmail.org)

RE: NULLs in Text::CSV_XS and DBD::CSV

[NIPP, SCOTT V \(SBCSI\)]

Still banging my head into a wall...  Now I am getting NULLs
inserted as expected into the database, but I am getting errors on the
compare.

my @old = $test->fetchrow_array ();
foreach $n (0..20) {
  chomp($file_val = $data[$n]);
  $file_val =~ s/\s*$//;
  #chomp($db_val = $old[$n+1]);
  #if ($file_val eq "") {
  #  print "NULL found in $n value. $file_val\n";
  #  $file_val = 0;
  #}
  # print "Comparing $file_val to $db_val. \n";  # Testing line
  if (defined $old[$n+1]) {
if ($file_val eq $$old[$n+1]) {
  $update = 1;
} else {
  $update = 0;
  # print "Comparing $file_val to $db_val. \n";  # Testing line
  last;
}
  }
}

Here are the errors.

Name "main::old" used only once: possible typo at ./host_tbl_update2.pl
line 65.
Use of uninitialized value in string eq at ./host_tbl_update2.pl line
65,  line 1.
Use of uninitialized value in string eq at ./host_tbl_update2.pl line
65,  line 2.
DBD::mysql::st execute failed: Column 'Vantive_HW' cannot be null at
./host_tbl_update2.pl line 80,  line 2.

I am pretty much confused by all of this.  That is until I was
typing this up and noticed I had typo'd an extra '$' on the comparison
line.  Maybe now all will be good.  Thanks again.



Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Jeff Zucker [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 16, 2005 11:49 AM
Cc: dbi-users@perl.org
Subject: Re: NULLs in Text::CSV_XS and DBD::CSV


Jeff Zucker wrote:

> 1,,2
> 1,"",2
>
In case I was unclear: the first is three fields with the second field 
NULL, the second is three fields with the second field an empty string.

-- 
Jeff

RE: NULL field matching problem...

[NIPP, SCOTT V \(SBCSI\)]

That sounds good, but I am a little confused on exactly how to
achieve that.  I'll start poking around, but...  Thanks for the pointer
at least.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: CAMPBELL, BRIAN D (BRIAN) [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 16, 2005 10:54 AM
To: NIPP, SCOTT V (SBCSI); Ronald J Kimball; dbi-users@perl.org
Subject: RE: NULL field matching problem...


I'm guessing that when a field is empty in your CSV file, then the
method $csv->fields puts an empty string value (e.g. "") in the
corresponding element in your @data array (not an undef value).

If so, you are going to have to detect for this condition and change it
to an undef before binding with bind_param.  Undef will cause a null to
be inserted.  In general, you would need to do this to all fields that
you want to represent as null, when they are represented as empty fields
in the CSV input.

 


-Original Message-----
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 16, 2005 9:18 AM
To: Ronald J Kimball; dbi-users@perl.org
Subject: RE: NULL field matching problem...


I thought about that after I hit the send button.  Here is the
INSERT code...

my $sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x
21).")")
  or print "Error with INSERT _prepare_ $DBI::errstr\n";
my $test = $dbh->prepare(qq{SELECT * FROM AllMid_Data WHERE CPU_DNS = ?
AND CPU_Hostname = ?});
while (my $file = ) {
  # Next two lines parse the CSV data from AllMid
  my $line = $csv->parse($file);
  my @data = $csv->fields($file);
  $test->execute ($data[4], $data[14]);
  my $rows = $test->rows;
  if ($rows == 0) {
# print "Entry not found.  Inserting into database. \n";  # Testing
line
$sth->bind_param(1, undef);
$sth->bind_param($_+2, $data[$_]) foreach 0..20;
$sth->execute() or print "Data insert failed.";

RE: NULL field matching problem...

[NIPP, SCOTT V \(SBCSI\)]

I thought about that after I hit the send button.  Here is the
INSERT code...

my $sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x
21).")")
  or print "Error with INSERT _prepare_ $DBI::errstr\n";
my $test = $dbh->prepare(qq{SELECT * FROM AllMid_Data WHERE CPU_DNS = ?
AND CPU_Hostname = ?});
while (my $file = ) {
  # Next two lines parse the CSV data from AllMid
  my $line = $csv->parse($file);
  my @data = $csv->fields($file);
  $test->execute ($data[4], $data[14]);
  my $rows = $test->rows;
  if ($rows == 0) {
# print "Entry not found.  Inserting into database. \n";  # Testing
line
$sth->bind_param(1, undef);
$sth->bind_param($_+2, $data[$_]) foreach 0..20;
$sth->execute() or print "Data insert failed.";

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Ronald J Kimball [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 16, 2005 10:14 AM
To: NIPP, SCOTT V (SBCSI); dbi-users@perl.org
Subject: RE: NULL field matching problem...




NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED] wrote:
 
>   I am working on a script that parses a csv file.  The actual
> field delimiters in the file are pipes |.  The problem I am running
into
> is that if the field is empty, then the database is getting a '0'
rather
> than 'NULL' as it should.  The column is setup properly with NULLs
> allowed and the default value of NULL.  Here is a sample of the file
> being parsed...
> 
>
SANDBOX|0|750|0.0|junk|N|0.0|9000/800/RP7410|Unknown|B.11.11|junk|junk|s
> erial number|Sandbox|hostname|0||CHTSAN|UNKNOWN|ROBERT C|JONES SR.|
> 
>   The problem on this line is the field between '0' and 'CHTSAN'.
> This ends up as a zero in the database.  When I parse the file again
to
> compare the values, this comes up as a mismatch when it should
actually
> be a match.  How do I fix this?  Thanks in advance for any help.  Here
> is the pertinent code.

You need to figure out how the value in the database is getting set to 0
rather than to NULL.  You should be looking at the code that parses the
file
and inserts into the database, not the code that compares the values
later.

Ronald

NULL field matching problem...

[NIPP, SCOTT V \(SBCSI\)]

I am working on a script that parses a csv file.  The actual
field delimiters in the file are pipes |.  The problem I am running into
is that if the field is empty, then the database is getting a '0' rather
than 'NULL' as it should.  The column is setup properly with NULLs
allowed and the default value of NULL.  Here is a sample of the file
being parsed...

SANDBOX|0|750|0.0|junk|N|0.0|9000/800/RP7410|Unknown|B.11.11|junk|junk|s
erial number|Sandbox|hostname|0||CHTSAN|UNKNOWN|ROBERT C|JONES SR.|

The problem on this line is the field between '0' and 'CHTSAN'.
This ends up as a zero in the database.  When I parse the file again to
compare the values, this comes up as a mismatch when it should actually
be a match.  How do I fix this?  Thanks in advance for any help.  Here
is the pertinent code.

foreach $n (0..20) {#
$file_val is the data from the parsed file.
  chomp($file_val = $data[$n]);
  $file_val =~ s/\s*$//;
  chomp($db_val = $old[$n+1]);  #
$db_val is the data coming from the DB.
  # print "Comparing $file_val to $db_val. \n";  # Testing line
  if ($file_val eq $db_val) {
$update = 1;
  } else {
$update = 0;
# print "Comparing $file_val to $db_val. \n";  # Testing line
last;
  }
Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Insert help...

[NIPP, SCOTT V \(SBCSI\)]

OK...  I have been able to solve this problem, and it was no big
deal.  However, the problem I am currently facing is really hosing me
up...  Basically, a "NULL" field is getting converted to a 0 in the
database.  This causes a comparison of the same file and the database to
yield a lot of differences.  Please help.  I think this has got to be
related to the INSERT.  The table column is setup as 'smallint' allowing
NULLS with the default value set to NULL.  This column however is
getting a '0' if the input file is empty for that field.  
Here is the pertinent code...

<...  Snipped  ...>
my $sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x
21).")")
  or print "Error with INSERT _prepare_ $DBI::errstr\n";
<...  Snipped  ...>
$sth->bind_param(1, undef);
$sth->bind_param($_+2, $data[$_]) foreach 0..20;
$sth->execute() or print "Data insert failed.";
<... Remainder snipped  ...>

Please help as I am almost there.  Thanks in advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-----Original Message-----
From: Reidy, Ron [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 15, 2005 1:34 PM
To: NIPP, SCOTT V (SBCSI); dbi-users@perl.org
Subject: RE: Insert help...


Doesn't MySQL have the concept of a unique or primary key constraint?
If it does, why not ...

1.  INSERT
2.  If insert fails due to PK violation, perform archiving and update
the row

This saves the cost of an extra insert (which is what the PK check sort
of performs ;)

I also would like to suggest that you put all of your prepare()
statements outside of your while { }.  This will save the time to parse
the statement for each line from the file.

---------
Ron Reidy
Lead DBA
Array BioPharma, Inc.
303.386.1480


-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 15, 2005 12:25 PM
To: dbi-users@perl.org
Subject: Insert help...


I am working on a Perl MySQL problem.  Basically, the script
parses a text file and then checks a database table to see if it needs
to be updated.  If the entry is NOT in the table, it inserts the entry.
If the entry does exist, it compares the data.  Assuming these data is
identical nothing happens, if however, the data is different then the
entry is archived off to a history table and the new data is inserted.
Here is basically what I have...

while (my $file = ) {
  my $line = $csv->parse($file);
  my @data = $csv->fields($file);
  my $test = $dbh->prepare(qq{SELECT * FROM AllMid_Data WHERE
CPU_Hostname = ?});
  $test->execute ($data[14]);
  my $rows = $test->rows;
  if ($rows == 0) {
print "Entry not found.  Inserting into database. \n";
my $sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x
21).")")  # This is the INSERT if the data is new.
  or print "Error with INSERT _prepare_ $DBI::errstr\n";
$sth->bind_param(1, undef);
$sth->bind_param($_+2, $data[$_]) foreach 0..20;
$sth->execute() or print "Data insert failed.";
  } else {
# print "Found entry and checking if updating is needed. \n";
my @old = $test->fetchrow_array ();
foreach $n (0..20) {
  chomp($file_val = $data[$n]);
  $file_val =~ s/\s*$//;
  chomp($db_val = $old[$n+1]);
  # print "Comparing $file_val to $db_val. \n";  Testing line
  if ($file_val eq $db_val) {
$update = 1;
  } else {
$update = 0;
print "Comparing $file_val to $db_val. \n";
last;
  }
}
if ($update == 0) {
  print "$data[14] requires updating in database.  Updating entry
now.\n";
  #  Insert existing data into AllMid_Hist.
  shift(@old);
  $dbh->do(qq{
  INSERT INTO AllMid_Hist VALUES (?".(",?" x 22)."))},  #
This is where I run into issues.
  undef,@old,NOW());# I am not sure how to
structure this syntax and not having luck finding the answer.
 }
  }
}

Thanks in advance for any help.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Insert help...

[NIPP, SCOTT V \(SBCSI\)]

I am working on a Perl MySQL problem.  Basically, the script
parses a text file and then checks a database table to see if it needs
to be updated.  If the entry is NOT in the table, it inserts the entry.
If the entry does exist, it compares the data.  Assuming these data is
identical nothing happens, if however, the data is different then the
entry is archived off to a history table and the new data is inserted.
Here is basically what I have...

while (my $file = ) {
  my $line = $csv->parse($file);
  my @data = $csv->fields($file);
  my $test = $dbh->prepare(qq{SELECT * FROM AllMid_Data WHERE
CPU_Hostname = ?});
  $test->execute ($data[14]);
  my $rows = $test->rows;
  if ($rows == 0) {
print "Entry not found.  Inserting into database. \n";
my $sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x
21).")")  # This is the INSERT if the data is new.
  or print "Error with INSERT _prepare_ $DBI::errstr\n";
$sth->bind_param(1, undef);
$sth->bind_param($_+2, $data[$_]) foreach 0..20;
$sth->execute() or print "Data insert failed.";
  } else {
# print "Found entry and checking if updating is needed. \n";
my @old = $test->fetchrow_array ();
foreach $n (0..20) {
  chomp($file_val = $data[$n]);
  $file_val =~ s/\s*$//;
  chomp($db_val = $old[$n+1]);
  # print "Comparing $file_val to $db_val. \n";  Testing line
  if ($file_val eq $db_val) {
$update = 1;
  } else {
$update = 0;
print "Comparing $file_val to $db_val. \n";
last;
  }
}
if ($update == 0) {
  print "$data[14] requires updating in database.  Updating entry
now.\n";
  #  Insert existing data into AllMid_Hist.
  shift(@old);
  $dbh->do(qq{
  INSERT INTO AllMid_Hist VALUES (?".(",?" x 22)."))},  #
This is where I run into issues.
  undef,@old,NOW());# I am not sure how to
structure this syntax and not having luck finding the answer.
 }
  }
}

Thanks in advance for any help.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Large SQL statements...

[NIPP, SCOTT V \(SBCSI\)]

I have finally had a chance to get back to this.  This is
working very well for me on the basic insert.  The challenge now is to
figure out how to compare the new data to the existing data to find out
if I need to update the table or just discard the data.  I want to do
this so I can create a second table to hold the changes in providing us
with a tracking mechanism for changes.
I am working on this now, but wanted to throw this out there
seeing as how I am probably going to get stuck shortly.  Thanks in
advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Wieland Pusch [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 08, 2004 3:17 AM
To: CAMPBELL, BRIAN D (BRIAN)
Cc: NIPP, SCOTT V (SBCSI); dbi-users@perl.org
Subject: Re: Large SQL statements...


Hallo BRIAN,

I like your solution with foreach and bind_param. Just add the prepare
like this:
$sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x 21).")")
 or print "Error with INSERT _prepare_ $DBI::errstr\n";

Tuesday, September 7, 2004, 9:31:15 PM, you wrote:
CBDB> This should work:
$sth->>bind_param(1, undef);
$sth->>bind_param($_+2, $data[$_]) foreach 0..20;
$sth->>execute() or print "something";

cu
 Wielandmailto:[EMAIL PROTECTED]

RE: MySQL data matching error...

[NIPP, SCOTT V \(SBCSI\)]

I am using phpMyAdmin to simplify the DB administration, but yes
I have setup the table as you are suggesting.  The problem as I see it
is that the value of the incoming data is ' ', and when this is inserted
into the database it becomes '0'.  The problem is if I test against this
value now, the input value is still ' ' and the database is '0'.  
The goal is a change log on this table.  I am accomplishing this
by reading each line of the incoming file and doing a select on the key
field to grab the database entry as an array.  Then I work through the
database array comparing it's contents to the incoming file data array.
If a difference is discovered, then the database array is populated into
the change log table and the incoming file data array is populated into
the live table.  Obviously this strange behavior is disrupting this
change log testing.
Thanks again for any feedback.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: S P [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 16, 2004 2:27 PM
To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: Re: MySQL data matching error...


Hi,
Not sure what you're trying to achieve,

Are you trying to have a blank(' ') instead of '0'?
or are you trying to actually store as 'NULL'?

Have you tried 
create table table_name (
field datatype(size) default as 'NULL',
...
)
?


--- "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> wrote:

>   I am having a problem in that if a field is blank,
> it is getting
> put into the database as a '0' rather than 'NULL'. 
> The table is setup
> to both allow NULL values in this field as well as
> NULL being the
> default value.  The problem is that on a few entries
> the field is
> actually empty, but the database is entering the
> value into the table as
> a '0' as mentioned above.  Does this problem sound
> familiar to anyone?
> 
> Scott Nipp
> Phone:  (214) 858-1289
> E-mail:  [EMAIL PROTECTED]
> Web:  http:\\ldsa.sbcld.sbc.com
> 
> 
> 

MySQL data matching error...

[NIPP, SCOTT V \(SBCSI\)]

I am having a problem in that if a field is blank, it is getting
put into the database as a '0' rather than 'NULL'.  The table is setup
to both allow NULL values in this field as well as NULL being the
default value.  The problem is that on a few entries the field is
actually empty, but the database is entering the value into the table as
a '0' as mentioned above.  Does this problem sound familiar to anyone?

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

MySQL INSERT problem...

[NIPP, SCOTT V \(SBCSI\)]

I have a database that I am working on a Perl script for to
manage.  Basically, this is a database of user accounts on about 80 Unix
boxes.  Most of it is working great, but I have one problem with the two
of the INSERTs.  The INSERTs are putting data into a history table such
that we can maintain when changes to user accounts occurred.  One field
of the INSERTs is not populating a column.  The column is an ENUM type
with either "D" (for deleted account) or "U" (for update to account) as
the valid input.  The INSERT actually populates all the other columns
but this one.  What am I doing wrong?  
This is a MySQL database running on HP-UX 11i.  This is running
Perl 5.6.0, and I do not have easily available the DBD and DBI versions.
Here is the section of code with the problem INSERT"

<---code snipped above--->
  if ($old[0]) {
print "$key1 requires updating in database.  Updating entry
now.\n";
$type = "U";
#  Insert existing data into acct_hist.
$dbh->do(qq{
 INSERT INTO acct_hist (key1, uid, gid, gcos, home,
shell, e
nt_time, update_type, arc_time)
 VALUES (?,?,?,?,?,?,?,?,NOW())},
 undef,@old,$type);
#  Delete existing data from acct_db.
$dbh->do(qq{DELETE FROM acct_db WHERE key1 = ?},
undef,$key1);
#  Insert new entry into acct_db.
$dbh->do(qq{
   INSERT INTO acct_db VALUES(?,?,?,?,?,?,NOW(),NOW())},
   undef, $key1, $uid, $gid, $gcos, $home, $shell)
or print "Error updating database:  ", $dbh->errstr,
"\n";
<---code snipped below--->

This first INSERT is the problem one.  The second INSERT works
fine.  Thanks in advance for any suggestions.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: SQL question: Find next unused number...

[NIPP, SCOTT V \(SBCSI\)]

Well...  The database is actually MySQL.  In answer to a couple
of other questions...  The reason for the number beginning at 3000 is
that this if for a Unix user account system, and we are reserving all
numeric UIDs below 3000.  Thanks again for the feedback.  Still trying
to figure this out.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Steve Baldwin [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 14, 2004 4:02 PM
To: 'Reidy, Ron'; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...


Another good point Ron.  This strategy allows update and delete activity
on
the table concurrently with the insert. 

-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 15 September 2004 6:53 AM
To: Steve Baldwin; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...

Or ...

SELECT MIN(id)
FROM   t
FOR UPDATE;

Would only cause a row lock.

-
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-Original Message-
From: Steve Baldwin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 14, 2004 2:50 PM
To: Reidy, Ron; 'NIPP, SCOTT V (SBCSI)'; [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...


Good point Ron.  However, if the requirement were to 'recycle' deleted
ID's,
you could obtain an exclusive lock on the table before issuing the
SELECT.
However, if you expect a large number of concurrent executions of this
code,
you would want to ensure the INSERT and subsequent COMMIT happens
immediately after the SELECT.  So it would go something along the lines
of

LOCK TABLE user_table IN EXCLUSIVE MODE ;
SELECT MIN ...
INSERT INTO user_table ...
COMMIT ;

There may be performance issues with this strategy, but if your
requirement
is definitely to 'fill in the gaps', you'll just have to take the hit.

Steve

-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 15 September 2004 6:43 AM
To: Steve Baldwin; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...

With Oracle, this will not work with many users executing the code at
the
same time.  Better to use q sequence.

-
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-Original Message-
From: Steve Baldwin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 14, 2004 2:41 PM
To: 'NIPP, SCOTT V (SBCSI)'; [EMAIL PROTECTED]
Subject: RE: SQL question: Find next unused number...


You didn't mention what DB you are using, but if it were Oracle, I would
do
something like this ...

SELECT MIN (user_id) + 1
FROM   user_table a
WHERE  userid >= 3000
ANDNOT EXISTS (
   SELECT 0
   FROM   user_table b
   WHERE  b.user_id = a.user_id + 1)

You would obviously want to ensure there is an index on the user_id
column
if the table has a lot of rows.

HTH,

Steve

-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 15 September 2004 5:31 AM
To: [EMAIL PROTECTED]
Subject: SQL question: Find next unused number...

I have a table that has a list of users with numeric user IDs.
The user IDs are not sequential.  There are large gaps in the list of
numeric IDs.  I want to pick the next unused number beginning at 3000.
How do I do this?
Thanks in advance.  

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not
the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.


This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not
the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

SQL question: Find next unused number...

[NIPP, SCOTT V \(SBCSI\)]

I have a table that has a list of users with numeric user IDs.
The user IDs are not sequential.  There are large gaps in the list of
numeric IDs.  I want to pick the next unused number beginning at 3000.
How do I do this?
Thanks in advance.  

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Database update module???

[NIPP, SCOTT V (SBCSI)]

I am using MySQL.  I must confess that I don't believe I have ever
even heard of 'triggers' before.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Ian Harisay [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 09, 2004 12:51 PM
To: [EMAIL PROTECTED]
Subject: Re: Database update module???


What database are you using.  A database that supports triggers
will handle this quite nicely. 

>>>NIPP, SCOTT V (SBCSI) <[EMAIL PROTECTED]> 09/08 11:47 am >>>
I have created a database that has a table for the active data,
and a table that basically captures the changes to that table. The
second table has all of the same fields as the first with one extra
and
that being to capture a date of the change. I know that I can write
a
piece of code to compare the incoming data to the existing data and
then
update accordingly. This seems like it would be an extremely common
thing to do though. I was wondering if someone might know of a module
to do this? Thanks in advance.

Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com


  

Database update module???

[NIPP, SCOTT V \(SBCSI\)]

I have created a database that has a table for the active data,
and a table that basically captures the changes to that table.  The
second table has all of the same fields as the first with one extra and
that being to capture a date of the change.  I know that I can write a
piece of code to compare the incoming data to the existing data and then
update accordingly.  This seems like it would be an extremely common
thing to do though.  I was wondering if someone might know of a module
to do this?  Thanks in advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Large SQL statements...

[NIPP, SCOTT V \(SBCSI\)]

Just a curiosity at this point...  Is there a way to do this
using the quick quotes method?

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Wieland Pusch [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 08, 2004 3:17 AM
To: CAMPBELL, BRIAN D (BRIAN)
Cc: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: Re: Large SQL statements...


Hallo BRIAN,

I like your solution with foreach and bind_param. Just add the prepare
like this:
$sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x 21).")")
 or print "Error with INSERT _prepare_ $DBI::errstr\n";

Tuesday, September 7, 2004, 9:31:15 PM, you wrote:
CBDB> This should work:
$sth->>bind_param(1, undef);
$sth->>bind_param($_+2, $data[$_]) foreach 0..20;
$sth->>execute() or print "something";

cu
 Wielandmailto:[EMAIL PROTECTED]

Large SQL statements...

[NIPP, SCOTT V \(SBCSI\)]

I have a table with 22 columns, and my INSERT on this table is
actually putting in data to possibly all 22 fields.  My question is, is
there a better way to write this INSERT?  Here's what I have:

while ($line = ) {
  $csv->parse($line);
  @data = $csv->fields;
  $sth = $dbh->prepare(qq{INSERT INTO AllMid_Data
VALUES(?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?)}) or print "Error with INSERT _prepare_:
$DBI::errstr\n";

 
$sth->execute(undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5
],$dat
a[6],$data[7],$data[8],$data[9],$data[10],$data[11],$data[12],$data[13],
$data[14
],$data[15],$data[16],$data[17],$data[18],$data[19],$data[20]) or print
"Error with 
INSERT _execute_: $DBI::errstr\n";

}

The 'execute' statement is quite long, and almost all of the
data comes from a single array.  I don't think this is a big deal in and
of itself, just looking for a better way to do this.  Thanks.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Parsing question...

[NIPP, SCOTT V \(SBCSI\)]

The suggestion of using Text::CSV_XS was the winner.  This is a
very simple module to use.  Thanks for the feedback though.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Ravi Kongara [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 07, 2004 2:05 PM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: Parsing question...


What i understood from your question is , you want to split strings that

have comma within
quotes. Forgive me if i'm wrong.

If elements of @data are re-split  , you can get multiple fields from
it.

<  ...snip...  >
my @new_data;
while () {
  @data = split(/,\s*/);
   foreach my $string ( @data ) {
 @temp = split /,\s*/, $string;
 push @new_data, @temp;
   }
  print "$data[1]\n";
}
<   ...snip...  >

there may be better solutions, though, as i've never dealt with CSV
files.

regs,
Ravi

NIPP, SCOTT V (SBCSI) wrote:

>   This isn't specifically a DBI question, but it is a part of a
>database script I am writing.  I am parsing a CSV file and populating a
>MySQL database table.  The problem I have run into is that a couple of
>lines of data have a comma in a filed that is in quotes, such as "First
>part, second part".  This makes it difficult to parse the CSV file on
>commas like my intention was.  Could someone help me figure out how to
>parse this?  Thanks.  
>   Basically, I am reading in the file in a 'while' loop.  I am
>then splitting the line into an array using the 'split' function.  The
>next step is to simply populate the database table with the fields.
>Here is the 'split' I am currently using.
>
><  ...snip...  >
>while () {
>  @data = split(/,\s*/);
>  print "$data[1]\n";
>}
><   ...snip...  >
>
>   Thanks in advance for the help.
>
>Scott Nipp
>Phone:  (214) 858-1289
>E-mail:  [EMAIL PROTECTED]
>Web:  http:\\ldsa.sbcld.sbc.com
>
>
>  
>

RE: Parsing question...

[NIPP, SCOTT V \(SBCSI\)]

Actually, yes.  There is a VERY good reason that I am not using
this module...

I had no idea that it existed.  Now that I know, I'll take a
peak and see if this will indeed do the trick for me.  Thanks a lot for
pointing me in the direction of the easy solution.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Jeff Zucker [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 07, 2004 1:18 PM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: Parsing question...


Is there some reason you aren't using DBD::CSV or its parser 
(Text::CSV_XS), which handle this and a number of other problem issues 
with parsing CSV?  Why reinvent the wheel?

-- 
Jeff

NIPP, SCOTT V (SBCSI) wrote:
>   This isn't specifically a DBI question, but it is a part of a
> database script I am writing.  I am parsing a CSV file and populating
a
> MySQL database table.  The problem I have run into is that a couple of
> lines of data have a comma in a filed that is in quotes, such as
"First
> part, second part".  This makes it difficult to parse the CSV file on
> commas like my intention was.  Could someone help me figure out how to
> parse this?  Thanks.  
>   Basically, I am reading in the file in a 'while' loop.  I am
> then splitting the line into an array using the 'split' function.  The
> next step is to simply populate the database table with the fields.
> Here is the 'split' I am currently using.
> 
> <  ...snip...  >
> while () {
>   @data = split(/,\s*/);
>   print "$data[1]\n";
> }
> <   ...snip...  >
> 
>   Thanks in advance for the help.
> 
> Scott Nipp
> Phone:  (214) 858-1289
> E-mail:  [EMAIL PROTECTED]
> Web:  http:\\ldsa.sbcld.sbc.com
> 
> 
> 
> 

Parsing question...

[NIPP, SCOTT V \(SBCSI\)]

This isn't specifically a DBI question, but it is a part of a
database script I am writing.  I am parsing a CSV file and populating a
MySQL database table.  The problem I have run into is that a couple of
lines of data have a comma in a filed that is in quotes, such as "First
part, second part".  This makes it difficult to parse the CSV file on
commas like my intention was.  Could someone help me figure out how to
parse this?  Thanks.  
Basically, I am reading in the file in a 'while' loop.  I am
then splitting the line into an array using the 'split' function.  The
next step is to simply populate the database table with the fields.
Here is the 'split' I am currently using.

<  ...snip...  >
while () {
  @data = split(/,\s*/);
  print "$data[1]\n";
}
<   ...snip...  >

Thanks in advance for the help.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: DBD driver compile on HP...

[NIPP, SCOTT V \(SBCSI\)]

No parameters, no modifications as of yet.  Just a lot of reading
and researching.
 
Basically...
 
perl Makefile.PL
make
 
ERRORS...

Scott Nipp 
Phone:  (214) 858-1289 
E-mail:  [EMAIL PROTECTED] 
Web:  http:\\ldsa.sbcld.sbc.com <http://ldsa.sbcld.sbc.com/>  

-Original Message-
From: Vinnie Lima [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 20, 2004 12:18 PM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: DBD driver compile on HP...



Hmm.dont think this is going to help you at all, but i'm getting all
kinds of error on my HPUX 11.00 platform.  What (if any) parameters did
you use for your make command? 

 
nanokov:/home/vlima/DBD-mysql-2.9004> perl Makefile.PL
--cflags=-I/opt/mysql/mysql \ 
> "--libs=-L/opt/mysql/mysql -lmysqlclient" 


Can't exec "mysql_config": No such file or directory at Makefile.PL line
174. 
readline() on closed filehandle PIPE at Makefile.PL line 176. 
Can't exec "mysql_config": No such file or directory at Makefile.PL line
174. 
readline() on closed filehandle PIPE at Makefile.PL line 176. 
Can't exec "mysql_config": No such file or directory at Makefile.PL line
174. 
readline() on closed filehandle PIPE at Makefile.PL line 176. 
Can't exec "mysql_config": No such file or directory at Makefile.PL line
174. 
readline() on closed filehandle PIPE at Makefile.PL line 176. 
Can't exec "mysql_config": No such file or directory at Makefile.PL line
174. 
readline() on closed filehandle PIPE at Makefile.PL line 176. 
Can't exec "mysql_config": No such file or directory at Makefile.PL line
174. 
readline() on closed filehandle PIPE at Makefile.PL line 176. 
Can't exec "mysql_config": No such file or directory at Makefile.PL line
174. 
readline() on closed filehandle PIPE at Makefile.PL line 176. 
I will use the following settings for compiling and testing: 

  cflags(Users choice) = -I/opt/mysql/mysql 
  libs  (Users choice) = -L/opt/mysql/mysql -lmysqlclient 
  nocatchstderr (default ) = 0 
  nofoundrows   (default ) = 0 
  ssl   (guessed ) = 0 
  testdb(default ) = test 
  testhost  (default ) = 
  testpassword  (default ) = 
  testuser  (default ) = 

To change these settings, see 'perl Makefile.PL --help' and 
'perldoc INSTALL'. 

Checking if your kit is complete... 
Looks good 
Note (probably harmless): No library found for -lmysqlclient 
Using DBI 1.21 installed in
/opt/perl/lib/site_perl/5.6.1/9000/800-hpux/auto/DBI 
Writing Makefile for DBD::mysql 


nanokov:/home/vlima/DBD-mysql-2.9004> make 

cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm 
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod 
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm 
cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm 
cp lib/Mysql/Statement.pm blib/lib/Mysql/Statement.pm 
cp lib/Mysql.pm blib/lib/Mysql.pm 
gcc -c -I/opt/perl/lib/site_perl/5.6.1/9000/800-hpux/auto/DBI
-I/opt/mysql/mysql -fno-strict-aliasing -O-DVERSION=\"2.9004\"
-DXS_VERSION=\"2.9004\" -fpic -I/opt/perl/lib/5.6.1/9000/800-hpux/CORE
dbdimp.c 
In file included from /usr/include/locale.h:157, 
 from /opt/perl/lib/5.6.1/9000/800-hpux/CORE/perl.h:465,

 from
/opt/perl/lib/site_perl/5.6.1/9000/800-hpux/auto/DBI/DBIXS.h:19, 
 from dbdimp.h:20, 
 from dbdimp.c:19: 
/opt/gcc/lib/gcc-lib/hppa2.0n-hp-hpux11.00/2.95.3/include/stdio.h:30:
warning: `__va__list' redefined 
/usr/include/locale.h:26: warning: this is the location of the previous
definition 
In file included from /opt/perl/lib/5.6.1/9000/800-hpux/CORE/perl.h:482,

 from
/opt/perl/lib/site_perl/5.6.1/9000/800-hpux/auto/DBI/DBIXS.h:19, 
 from dbdimp.h:20, 
 from dbdimp.c:19: 
/usr/include/setjmp.h:26: warning: `__va__list' redefined 
/opt/gcc/lib/gcc-lib/hppa2.0n-hp-hpux11.00/2.95.3/include/stdio.h:30:
warning: this is the location of the previous definition 
In file included from dbdimp.c:19: 
dbdimp.h:21: mysql.h: No such file or directory 
dbdimp.h:22: errmsg.h: No such file or directory 
*** Error exit code 1 

Stop. 

nanokov:/home/vlima/DBD-mysql-2.9004> perl -V 
Summary of my perl5 (revision 5.0 version 6 subversion 1) configuration:

  Platform: 
osname=hpux, osvers=11.00, archname=9000/800-hpux 
uname='hp-ux nanokov b.11.00 a 9000800 130931527 two-user license ' 
config_args='-Dcc=gcc -Dprefix=/opt/perl' 
hint=recommended, useposix=true, d_sigaction=define 
usethreads=undef use5005threads=undef useithreads=undef
usemultiplicity=undef 
useperlio=undef d_sfio=undef uselargefiles=define usesocks=undef 
use64bitint=undef use64bitall=undef uselongdouble=undef 
  Compiler: 
cc='gcc', ccfla

DBD driver compile on HP...

[NIPP, SCOTT V \(SBCSI\)]

I know that this question has come up before.  I think I have
even asked it long, long ago.  Unfortunately, this is not something I do
even infrequently, more like once in a blue moon.  The problem is once
again getting DBD::MySQL to compile and play nice on HP-UX.  Here are
the errors I am seeing:

system(root):/home/user/perl/DBD-mysql-2.9004# make
gcc -c
-I/opt/perl/lib/site_perl/5.6.1/PA-RISC1.1-thread-multi/auto/DBI
-I'/usr/include/mysql' -DDBD_MYSQL_WITH_SSL -D_POSIX_C_SOURCE=199506L
-D_HPUX_SOURCE -L/lib/pa1.1 -DUINT32_MAX_BROKEN -mpa-risc-1-1 -fPIC
-fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64 -O-DVERSION=\"2.9004\"
-DXS_VERSION=\"2.9004\" -fPIC
-I/opt/perl/lib/5.6.1/PA-RISC1.1-thread-multi/CORE  dbdimp.c
cc1: warning: changing search order for system directory
"/usr/local/include"
cc1: warning:   as it has already been specified as a non-system
directory
In file included from
/opt/perl/lib/5.6.1/PA-RISC1.1-thread-multi/CORE/perl.h:713,
 from
/opt/perl/lib/site_perl/5.6.1/PA-RISC1.1-thread-multi/auto/DBI/DBIXS.h:1
9,
 from dbdimp.h:20,
 from dbdimp.c:19:
/usr/include/sys/socket.h:484: parse error before "sendfile"
/usr/include/sys/socket.h:485: parse error before "bsize_t"
/usr/include/sys/socket.h:486: parse error before "sendpath"
/usr/include/sys/socket.h:487: parse error before "bsize_t"
/usr/include/sys/socket.h:501: parse error before "__sendfile64"
/usr/include/sys/socket.h:501: parse error before "bsize_t"
/usr/include/sys/socket.h:502: parse error before "__sendpath64"
/usr/include/sys/socket.h:502: parse error before "bsize_t"
/usr/include/sys/socket.h:504: parse error before "sendfile"
/usr/include/sys/socket.h: In function `sendfile':
/usr/include/sys/socket.h:504: parse error before "bsize_t"
/usr/include/sys/socket.h: At top level:
/usr/include/sys/socket.h:505: parse error before "sendpath"
/usr/include/sys/socket.h: In function `sendpath':
/usr/include/sys/socket.h:505: parse error before "bsize_t"
*** Error exit code 1

Stop.

Here is the Perl information...

system(root):/home/user/perl/DBD-mysql-2.9004# perl -V
Summary of my perl5 (revision 5.0 version 6 subversion 1) configuration:
  Platform:
osname=hpux, osvers=11.00, archname=PA-RISC1.1-thread-multi
uname='hp-ux llbertha b.11.00 u 9000800 2002402864 unlimited-user
license '
config_args='-des -Dcf_by=ActiveState
[EMAIL PROTECTED] -Uinstallusrbinperl -Dusethreads
-Duseithreads -Duselargefiles -Dinc_version_list=5.6.0/$archname 5.6.0
-Dcc=gcc -Accflags=-mpa-risc-1-1 -fPIC -Dcccdlflags=-fPIC
-Dd_attribut=undef -Darchname=PA-RISC1.1 -Dprefix=/opt/perl'
hint=recommended, useposix=true, d_sigaction=define
usethreads=define use5005threads=undef useithreads=define
usemultiplicity=define
useperlio=undef d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
  Compiler:
cc='gcc', ccflags ='-D_POSIX_C_SOURCE=199506L -D_HPUX_SOURCE
-L/lib/pa1.1 -DUINT32_MAX_BROKEN -mpa-risc-1-1 -fPIC
-fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64',
optimize='-O',
cppflags='-D_POSIX_C_SOURCE=199506L -D_HPUX_SOURCE -L/lib/pa1.1
-DUINT32_MAX_BROKEN -mpa-risc-1-1 -fPIC -fno-strict-aliasing
-I/usr/local/include'
ccversion='', gccversion='2.9-hppa-991112', gccosandvers='hpux11.00'
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
lseeksize=8
alignbytes=8, usemymalloc=n, prototype=define
  Linker and Libraries:
ld='ld', ldflags =' -L/usr/local/lib'
libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib
libs=-lnsl -lnm -lndbm -lmalloc -ldld -lm -lpthread -lc -lndir
-lcrypt -lsec
perllibs=-lnsl -lnm -lmalloc -ldld -lm -lpthread -lc -lndir -lcrypt
-lsec
libc=/lib/libc.sl, so=sl, useshrplib=false, libperl=libperl.a
  Dynamic Linking:
dlsrc=dl_hpux.xs, dlext=sl, d_dlsymun=undef, ccdlflags='-Wl,-E '
cccdlflags='-fPIC', lddlflags='-b -L/usr/local/lib'


Characteristics of this binary (from libperl):
  Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES
PERL_IMPLICIT_CONTEXT
  Locally applied patches:
ActivePerl Build 627
  Built under hpux
  Compiled at Jun 20 2001 21:42:53
  @INC:
/opt/perl/lib/5.6.1/PA-RISC1.1-thread-multi
/opt/perl/lib/5.6.1
/opt/perl/lib/site_perl/5.6.1/PA-RISC1.1-thread-multi
/opt/perl/lib/site_perl/5.6.1
/opt/perl/lib/site_perl
.

Thanks for the help.  Sorry for asking the same question yet
again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Basic symlink question...

[NIPP, SCOTT V (SBCSI)]

Thanks for the PerlMonks suggestion.  I found a solution out there.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Ronald Kimball [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 09, 2004 12:51 PM
To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: RE: Basic symlink question...


NIPP, SCOTT V (SBCSI) <[EMAIL PROTECTED]> wrote:
> 
>   I know this is not the correct forum for this question, but I
figure
> someone will know easily enough...  I am attempting to use the Perl
> symlink
> command to create/update a link on the Unix server.  Below is the
syntax I
> am using, and not having any luck with.  Any help would be most
> appreciated.
> 
> symlink("$loc[($push -1)]/bin","/tlgld/bin")
> 

I recommend adding error checking, rewriting your question to include a
description of what actually happened (including the error message you
get, if any), and then asking on an APPROPRIATE FORUM, such as
www.perlmonks.com, since this question has absolutely nothing to do with
DBI.

Ronald

Basic symlink question...

[NIPP, SCOTT V (SBCSI)]

I know this is not the correct forum for this question, but I figure
someone will know easily enough...  I am attempting to use the Perl symlink
command to create/update a link on the Unix server.  Below is the syntax I
am using, and not having any luck with.  Any help would be most appreciated.

symlink("$loc[($push -1)]/bin","/tlgld/bin")

I am wondering if there is a problem with the variable expansion or
something.  Thanks in advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Strange matching problem...

[NIPP, SCOTT V (SBCSI)]

OK...  From what I have read, the "prepare...execute" method is no
more acceptable than the "do" method.  My understanding is that if you are
going to be inserting multiple instances, without looping, then you are
better served with using the "prepare...execute" method.  If you are looping
through data, then using the "do" method is no worse.
I am going to create a copy using the "prepare...execute" method
exclusively, and time that against the existing version.  This will give me
very real world comparison.
Thanks for the feedback again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: David N Murray [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 22, 2004 2:07 PM
To: NIPP, SCOTT V (SBCSI)
Cc: Hardy Merrill; [EMAIL PROTECTED]
Subject: RE: Strange matching problem...


On Jan 22, NIPP, SCOTT V (SBCSI) scribed:

>   OK.  I am working on converting this to use placeholders and the
> "qq" quoting option.  I am obviously very new to placeholders, so this is
> probably a stupid question, but here goes.  I execute the script and
receive
> the following error:
>
> [EMAIL PROTECTED]:/home/sadmin/sn4265/perl> timex ./passwd2db.pl
> Name "main::passwd" used only once: possible typo at ./passwd2db.pl line
25.
> Password file for argon found.  Now processing...
> Undefined subroutine &main::NOW called at ./passwd2db.pl line 33, 
> line 11.
>
>   The following is the section of code that includes the "NOW" on line
> 33.  Obviously there is a problem with the way I am doing the placeholders
> here.
>
>   $dbh->do(qq{
>INSERT INTO acct_db VALUES(?,?,?,?,?,?,?)},
>undef,$key1,$uid,$gid,$gcos,$home,$shell,NOW())
> or print "Error updating database:  ", $dbh->errstr, "\n";

That's not how its done:
my $ih = $dbh->prepare("insert into acct_db values (?,?,?,?,?,?,NOW())");
# presuming NOW() is a function in your database; its not a perl function
$ih->execute(undef,$key1,$uid,$gid,$gcos,$home,$shell);
(and the prepare() goes outside the loop).

Look at the section on Performance for an example in perldoc DBI.
You probably wouldn't be hurt by buying Tim's book.

HTH,
Dave

RE: Strange matching problem...

[NIPP, SCOTT V (SBCSI)]

Thanks to everyone for the feedback.  I have modified the script to
fix the NOW function.  Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 22, 2004 2:11 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: RE: Strange matching problem...


You really need to read 'perldoc DBI' (at a command prompt) and pay
attention to how placeholders are used. See below.  As Dave Murray
suggested, the Programming the Perl DBI book would be a good
investment.

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 02:52PM >>>
OK.  I am working on converting this to use placeholders and
the
"qq" quoting option.  I am obviously very new to placeholders, so this
is
probably a stupid question, but here goes.  I execute the script and
receive
the following error:

[EMAIL PROTECTED]:/home/sadmin/sn4265/perl> timex ./passwd2db.pl
Name "main::passwd" used only once: possible typo at ./passwd2db.pl
line 25.
Password file for argon found.  Now processing...
Undefined subroutine &main::NOW called at ./passwd2db.pl line 33,

line 11.

The following is the section of code that includes the "NOW" on
line
33.  Obviously there is a problem with the way I am doing the
placeholders
here.

  $dbh->do(qq{
   INSERT INTO acct_db VALUES(?,?,?,?,?,?,?)},
   undef,$key1,$uid,$gid,$gcos,$home,$shell,NOW())
or print "Error updating database:  ", $dbh->errstr,
"\n";

HM@@ I don't think(?) you can use placeholders with the "do" command,
but I'm really not sure.  I think you have to put the "?" placeholders
in the "prepare", and then do an "execute" and it's in the execute where
you specify the values for those placeholders, like this:

  $sth = $dbh->prepare(qq{
   INSERT INTO acct_db
  VALUES(?,?,?,?,?,?,NOW())
  }) or print "Error with INSERT _prepare_: $DBI::errstr\n";

  $sth->execute(undef,$key1,$uid,$gid,$gcos,$home,$shell)
   or print "Error with INSERT _execute_: $DBI::errstr\n";

See if that works.

Hardy Merrill

Thanks again for the help.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED] 
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 22, 2004 12:46 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: RE: Strange matching problem...


This time HM### - I hate Groupwise!  Can't properly quote anything.

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:32PM >>>
OK...  First of all, today, this afternoon, I am going to learn
how
to use placeholders and get that fixed.  I promise.  :)

HM### Your "non-placeholder" code is not wrong - it can just be done
better with placeholders :)

HM>> I don't see any matching logic in your code below, but here's a
general thought - instead of a string equality check ( if ($acct1 eq
$acct2) ), you could use a regular expression with the "i" (ignore
case
flag) something like ( if ($acct1 =~ /$acct2/i )

The matching logic is actually a part of the MySQL query.  This
part
works great, but it was case sensitivity that was giving me the fits. 
I
discovered that VARCHAR and CHAR data types in MySQL are not case
sensitive
by default.  You have to set the column to have a BINARY column
modifier to
make these data types case sensitive.  This seems to have fixed at
least
that problem.

HM### Sorry, I missed that the 1st time around.  Here is one of your
selects:

my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'");

HM### you should be able to use something like this (I'm not sure
exactly what MySQL's syntax is for this):
my $test = $dbh->prepare(qq{
  SELECT *
  FROM acct_db
  WHERE UPPER(key1) = UPPER('$key1')
 });

or, which placeholders, like this:

my $sth = $dbh->prepare(qq{
  SELECT *
  FROM acct_db
  WHERE UPPER(key1) = UPPER(?)
 });
 $sth->execute($key1);

the idea is to make sure the case on both sides is the same.  MySQL
may
also have some sort of regular expression matching where you can
ignore
case, but I'm not familiar enough with it to know definitely.

HTH.

Hardy Merrill

Now, I'll get to work on the placeholders, and see about the
other
issue.  If the BINARY column modifier doesn't fix this, hopefully I
can
come
back with some more useful information.  Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED] 
Web:  

RE: Strange matching problem...

[NIPP, SCOTT V (SBCSI)]

OK.  I am working on converting this to use placeholders and the
"qq" quoting option.  I am obviously very new to placeholders, so this is
probably a stupid question, but here goes.  I execute the script and receive
the following error:

[EMAIL PROTECTED]:/home/sadmin/sn4265/perl> timex ./passwd2db.pl
Name "main::passwd" used only once: possible typo at ./passwd2db.pl line 25.
Password file for argon found.  Now processing...
Undefined subroutine &main::NOW called at ./passwd2db.pl line 33, 
line 11.

The following is the section of code that includes the "NOW" on line
33.  Obviously there is a problem with the way I am doing the placeholders
here.

  $dbh->do(qq{
   INSERT INTO acct_db VALUES(?,?,?,?,?,?,?)},
   undef,$key1,$uid,$gid,$gcos,$home,$shell,NOW())
or print "Error updating database:  ", $dbh->errstr, "\n";

Thanks again for the help.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 22, 2004 12:46 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: RE: Strange matching problem...


This time HM### - I hate Groupwise!  Can't properly quote anything.

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:32PM >>>
OK...  First of all, today, this afternoon, I am going to learn
how
to use placeholders and get that fixed.  I promise.  :)

HM### Your "non-placeholder" code is not wrong - it can just be done
better with placeholders :)

HM>> I don't see any matching logic in your code below, but here's a
general thought - instead of a string equality check ( if ($acct1 eq
$acct2) ), you could use a regular expression with the "i" (ignore
case
flag) something like ( if ($acct1 =~ /$acct2/i )

The matching logic is actually a part of the MySQL query.  This
part
works great, but it was case sensitivity that was giving me the fits. 
I
discovered that VARCHAR and CHAR data types in MySQL are not case
sensitive
by default.  You have to set the column to have a BINARY column
modifier to
make these data types case sensitive.  This seems to have fixed at
least
that problem.

HM### Sorry, I missed that the 1st time around.  Here is one of your
selects:

my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'");

HM### you should be able to use something like this (I'm not sure
exactly what MySQL's syntax is for this):
my $test = $dbh->prepare(qq{
  SELECT *
  FROM acct_db
  WHERE UPPER(key1) = UPPER('$key1')
 });

or, which placeholders, like this:

my $sth = $dbh->prepare(qq{
  SELECT *
  FROM acct_db
  WHERE UPPER(key1) = UPPER(?)
 });
 $sth->execute($key1);

the idea is to make sure the case on both sides is the same.  MySQL may
also have some sort of regular expression matching where you can ignore
case, but I'm not familiar enough with it to know definitely.

HTH.

Hardy Merrill

Now, I'll get to work on the placeholders, and see about the
other
issue.  If the BINARY column modifier doesn't fix this, hopefully I can
come
back with some more useful information.  Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED] 
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 22, 2004 12:22 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: Re: Strange matching problem...


My comments below as HM>>.  I'll repeat one general suggestion made
previously - use placeholders :)

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:05PM >>>
I am working on a script to gather data about Unix user
accounts.
This is going well so far and has gotten us some initial data such as
almost
27000 user accounts across about 80 servers.  I have built into the
script
and database some checking to track changes basically capture these
changes
in a historical table.  I am running into at least two problems I am
currently unable to explain...
The first problem appears to be a matching problem with case
sensitivity.  There are several instances on a couple of systems that
have
an account twice, once with the account name in all upper case and the
other
in all lower case.  I am not sure exactly what/why this is occurring. 
Any
help on this would be appreciated.

HM>> I don't see any matching logic in your code below, but here's a
general thought - instead of a string equality check ( if ($acct1 eq
$acct2) ), you could use a regular expression with the "i" (ignore
case
flag) something like ( if ($acc

RE: Strange matching problem...

[NIPP, SCOTT V (SBCSI)]

OK...  First of all, today, this afternoon, I am going to learn how
to use placeholders and get that fixed.  I promise.  :)

HM>> I don't see any matching logic in your code below, but here's a
general thought - instead of a string equality check ( if ($acct1 eq
$acct2) ), you could use a regular expression with the "i" (ignore case
flag) something like ( if ($acct1 =~ /$acct2/i )

The matching logic is actually a part of the MySQL query.  This part
works great, but it was case sensitivity that was giving me the fits.  I
discovered that VARCHAR and CHAR data types in MySQL are not case sensitive
by default.  You have to set the column to have a BINARY column modifier to
make these data types case sensitive.  This seems to have fixed at least
that problem.
Now, I'll get to work on the placeholders, and see about the other
issue.  If the BINARY column modifier doesn't fix this, hopefully I can come
back with some more useful information.  Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 22, 2004 12:22 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: Re: Strange matching problem...


My comments below as HM>>.  I'll repeat one general suggestion made
previously - use placeholders :)

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:05PM >>>
I am working on a script to gather data about Unix user
accounts.
This is going well so far and has gotten us some initial data such as
almost
27000 user accounts across about 80 servers.  I have built into the
script
and database some checking to track changes basically capture these
changes
in a historical table.  I am running into at least two problems I am
currently unable to explain...
The first problem appears to be a matching problem with case
sensitivity.  There are several instances on a couple of systems that
have
an account twice, once with the account name in all upper case and the
other
in all lower case.  I am not sure exactly what/why this is occurring. 
Any
help on this would be appreciated.

HM>> I don't see any matching logic in your code below, but here's a
general thought - instead of a string equality check ( if ($acct1 eq
$acct2) ), you could use a regular expression with the "i" (ignore case
flag) something like ( if ($acct1 =~ /$acct2/i )

The other problem is an account with the same name on two
servers
that is being captured as changed every time.  This is strange as I am
simply running the script repeatedly on the same dataset.  Any ideas on
this
would be most appreciated also.

HM>> Can't help you with this one - not enough info.

HTH.

Hardy Merrill

Below is the relevant sections of code...

while ($entry = ) {
  ($name, $passwd, $uid, $gid, $gcos, $home, $shell) =
split(/:/,$entry);
  if ($uid > 100) {
$key1 = "$name"."-"."$host";
my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'");
$test->execute ();
$rows = $test->rows;
#print "Return value:  $rows\n";
if ($rows == 0) {
  $dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database:  ", $dbh->errstr, "\n";
  print "Adding $key1 to password database. \n";
} elsif ($rows == 1) {
  my $test1 = $dbh->prepare("SELECT * FROM acct_db WHERE key1
=
'$key1' AND (uid != '$uid' OR gid != '$gid' OR gcos != '$gcos' OR home
!=
'$home' OR shell != '$shell')");
  $test1->execute();
  @old = $test1->fetchrow_array ();
  if ($old[0]) {
print "$key1 requires updating in database.  Updating
entry
now.\n";
#  Insert existing data into acct_hist.
$dbh->do("INSERT INTO acct_hist (key1, uid, gid, gcos,
home,
shell, ent_time, arc_time)
VALUES
('$old[0]','$old[1]','$old[2]','$old[3]','$old[4]','$old[5]','$old[6]',NOW()
)");
#  Delete existing data from acct_db.
$dbh->do("DELETE FROM acct_db WHERE key1 = '$key1'");
#  Insert new entry into acct_db.
$dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database:  ", $dbh->errstr, "\n";
  } e

Strange matching problem...

[NIPP, SCOTT V (SBCSI)]

I am working on a script to gather data about Unix user accounts.
This is going well so far and has gotten us some initial data such as almost
27000 user accounts across about 80 servers.  I have built into the script
and database some checking to track changes basically capture these changes
in a historical table.  I am running into at least two problems I am
currently unable to explain...
The first problem appears to be a matching problem with case
sensitivity.  There are several instances on a couple of systems that have
an account twice, once with the account name in all upper case and the other
in all lower case.  I am not sure exactly what/why this is occurring.  Any
help on this would be appreciated.
The other problem is an account with the same name on two servers
that is being captured as changed every time.  This is strange as I am
simply running the script repeatedly on the same dataset.  Any ideas on this
would be most appreciated also.
Below is the relevant sections of code...

while ($entry = ) {
  ($name, $passwd, $uid, $gid, $gcos, $home, $shell) =
split(/:/,$entry);
  if ($uid > 100) {
$key1 = "$name"."-"."$host";
my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'");
$test->execute ();
$rows = $test->rows;
#print "Return value:  $rows\n";
if ($rows == 0) {
  $dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database:  ", $dbh->errstr, "\n";
  print "Adding $key1 to password database. \n";
} elsif ($rows == 1) {
  my $test1 = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1' AND (uid != '$uid' OR gid != '$gid' OR gcos != '$gcos' OR home !=
'$home' OR shell != '$shell')");
  $test1->execute();
  @old = $test1->fetchrow_array ();
  if ($old[0]) {
print "$key1 requires updating in database.  Updating entry
now.\n";
#  Insert existing data into acct_hist.
$dbh->do("INSERT INTO acct_hist (key1, uid, gid, gcos, home,
shell, ent_time, arc_time)
VALUES
('$old[0]','$old[1]','$old[2]','$old[3]','$old[4]','$old[5]','$old[6]',NOW()
)");
#  Delete existing data from acct_db.
$dbh->do("DELETE FROM acct_db WHERE key1 = '$key1'");
#  Insert new entry into acct_db.
$dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database:  ", $dbh->errstr, "\n";
  } else {
#print "$key1 is up to date in database.  No update
necessary.\n";
  }
}  else {
  print "Error. \n";
}
  }
}

The basic functionality of this script is working great.  Data is
definitely making it into the database, and testing changes works as
intended.  The problems mentioned above are what I am trying to rectify at
this point.  Any other suggestions on improvements or better methods for
doing some of this stuff are most welcome too.  Thanks in advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Need some MySQL query help...

[NIPP, SCOTT V (SBCSI)]

Thanks for the feedback, this did the trick for me.

The next question I have is regarding a good way to archive changes.
I have already created a separate archive table.  I am mainly curious as to
if there is a standard method of moving a row from one table to another.  If
I were to script this manually, I would assume that I select the data and
capture the fields as variable and then have an insert statement to put this
data into the archive table.  At this point, I would then delete the
existing row from the current table and then insert the new data into the
current table.  I am just curious as to if there is a better way than doing
all of this.
The archive table has two additional fields.  Not sure if this makes
any difference or not.  The first new field is the Primary Key, which is a
simple autoincrement field.  The other extra field is a date field for when
this change was archived off.  I know that this is actually redundant,
however, I think it will make some of the other tools easier to write later.
Thanks again for the help.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 20, 2004 10:39 AM
To: [EMAIL PROTECTED]
Subject: Fw: Need some MySQL query help...






You can try a != instead of the <> for not equal.

- Forwarded by Patricia A Markiewicz/US/DNY on 01/20/2004 10:38 AM
-
   
 "NIPP, SCOTT V
     (SBCSI)"  
 <[EMAIL PROTECTED]>   To 
   Hardy Merrill   
 01/20/2004 10:23  <[EMAIL PROTECTED]>,
 AM[EMAIL PROTECTED]  
cc 
   
   Subject 
   RE: Need some MySQL query help...   
   
   
   
   
   
   




 Thanks for the feedback.  I am getting an error during the
compile.
I think my problem is in the structure of the SELECT statement.  I was
taking a guess on how to test for inequality.  My guess now is that the
"<>"
is simply invalid, and this is where my problem is coming from.  Here again
is the SELECT statement that I think is the problem, along with output from
attempting to run the script:

my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 = '$key1' AND
(uid <> '$uid' OR gid <> '$gid' OR gcos <> '$gcos' OR home <> '$home' OR
shell <> '$shell')");
$test->execute ();

***  OUTPUT  ***
syntax error at ./passwd2db.pl line 34, near ""Adding $key1 to password
database
. \n";"
Execution of ./passwd2db.pl aborted due to compilation errors.

 In the mean time, I'll start reading up on using placeholders
too.
Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 20, 2004 10:13 AM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: Re: Need some MySQL query help...


You're saying that you have a problem and that you think the problem is
in the matching logic in the SELECT, but you don't say exactly _what_ is
happening.  Put some prints in so you can see the values of the
variables you are plugging into the sql - make sure those values match
what you are expecting.  If that doesn't tell you enough, you can try
using DBI's "trace" method to help you see exactly what values are being
passed to the database.

I also suggest using placeholders - if you have quoting issues,
placeholders will solve those for you.  And they'll make things run
faster.  Read about placeholders by doing

   perldoc DBI

at a command prompt and searching(using the forward slash "/") for
"Placeholder".  Those perldocs also describe using "trace".

HTH.

Hardy Merrill

>>&

RE: Need some MySQL query help...

[NIPP, SCOTT V (SBCSI)]

Thanks for the feedback.  I am getting an error during the compile.
I think my problem is in the structure of the SELECT statement.  I was
taking a guess on how to test for inequality.  My guess now is that the "<>"
is simply invalid, and this is where my problem is coming from.  Here again
is the SELECT statement that I think is the problem, along with output from
attempting to run the script:

my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 = '$key1' AND
(uid <> '$uid' OR gid <> '$gid' OR gcos <> '$gcos' OR home <> '$home' OR
shell <> '$shell')");
$test->execute ();

***  OUTPUT  ***
syntax error at ./passwd2db.pl line 34, near ""Adding $key1 to password
database
. \n";"
Execution of ./passwd2db.pl aborted due to compilation errors.

In the mean time, I'll start reading up on using placeholders too.
Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-----Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 20, 2004 10:13 AM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: Re: Need some MySQL query help...


You're saying that you have a problem and that you think the problem is
in the matching logic in the SELECT, but you don't say exactly _what_ is
happening.  Put some prints in so you can see the values of the
variables you are plugging into the sql - make sure those values match
what you are expecting.  If that doesn't tell you enough, you can try
using DBI's "trace" method to help you see exactly what values are being
passed to the database.

I also suggest using placeholders - if you have quoting issues,
placeholders will solve those for you.  And they'll make things run
faster.  Read about placeholders by doing

   perldoc DBI

at a command prompt and searching(using the forward slash "/") for
"Placeholder".  Those perldocs also describe using "trace".

HTH.

Hardy Merrill

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/20/04 10:48AM >>>
I am working on a set of Perl scripts, along with some PHP web
pages, to help organize and automate user account creation in a large
HP-UX
environment.  I am currently writing a few scripts to gather all of
the
existing user account data from every system and populate a couple of
database tables.  One feature I am working on is to have a history
table to
track all changes to user accounts.  
I am currently working on the logic of the script that populates
the
database tables and what I am attempting to do is compare the existing
passwd entry to the current database entry.  The Primary Key on the
current
table (acct_db) is a combination of userID and hostname.  I want the
query
to match the Primary Key, and compare all of the data in the passwd
file to
the data in this table.  Assuming the data is all a match, nothing
happens,
the script simply proceeds to the next entry.  If however there is a
difference, the script should delete the entry and populate this same
entry
into the history table (acct_hist) and also insert the new data into
the
current table.
This is where I am currently running into problems.  I am not
sure
exactly how to test for inequality of all the variables in the passwd
file.
I was hoping to have a single MySQL query do the test in order to use
it to
help simplify the Perl code.  The SELECT statement is where I think I
am
going wrong here.  Here is the code loop that processes the passwd
file
data:

while ($file = readdir(DATA)) {
  if ($file =~ /passwd/) {
($host) = split /\./, $file, 2;
print "Password file for $host found.  Now processing...\n";
open(FILE, "/usr/local/mysql/tmp_data/$file");
while ($entry = ) {
  ($name, $passwd, $uid, $gid, $gcos, $home, $shell) =
split(/:/,$entry);
  if ($uid > 100) {
$key1 = "$name"."-"."$host";
my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'
AND (uid <> '$uid' OR gid <> '$gid' OR gcos <> '$gcos' OR home <>
'$home' OR
shell <> '$shell')");
$test->execute ();
$rows = $test->rows;
if ($rows == 0) {
  $dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database:  ", $dbh->errstr, "\n";(
  print "Adding $key1 to password database. \n";
} elsif ($rows == 1) {
  print "$key1 already in database.  Updating entry now.\n";
}  else {
  print "Error. \n";
}
  }
}
  }
}

Thanks in advance for any help.  

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED] 
Web:  http:\\ldsa.sbcld.sbc.com

Need some MySQL query help...

[NIPP, SCOTT V (SBCSI)]

I am working on a set of Perl scripts, along with some PHP web
pages, to help organize and automate user account creation in a large HP-UX
environment.  I am currently writing a few scripts to gather all of the
existing user account data from every system and populate a couple of
database tables.  One feature I am working on is to have a history table to
track all changes to user accounts.  
I am currently working on the logic of the script that populates the
database tables and what I am attempting to do is compare the existing
passwd entry to the current database entry.  The Primary Key on the current
table (acct_db) is a combination of userID and hostname.  I want the query
to match the Primary Key, and compare all of the data in the passwd file to
the data in this table.  Assuming the data is all a match, nothing happens,
the script simply proceeds to the next entry.  If however there is a
difference, the script should delete the entry and populate this same entry
into the history table (acct_hist) and also insert the new data into the
current table.
This is where I am currently running into problems.  I am not sure
exactly how to test for inequality of all the variables in the passwd file.
I was hoping to have a single MySQL query do the test in order to use it to
help simplify the Perl code.  The SELECT statement is where I think I am
going wrong here.  Here is the code loop that processes the passwd file
data:

while ($file = readdir(DATA)) {
  if ($file =~ /passwd/) {
($host) = split /\./, $file, 2;
print "Password file for $host found.  Now processing...\n";
open(FILE, "/usr/local/mysql/tmp_data/$file");
while ($entry = ) {
  ($name, $passwd, $uid, $gid, $gcos, $home, $shell) =
split(/:/,$entry);
  if ($uid > 100) {
$key1 = "$name"."-"."$host";
my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 = '$key1'
AND (uid <> '$uid' OR gid <> '$gid' OR gcos <> '$gcos' OR home <> '$home' OR
shell <> '$shell')");
$test->execute ();
$rows = $test->rows;
if ($rows == 0) {
  $dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database:  ", $dbh->errstr, "\n";(
  print "Adding $key1 to password database. \n";
} elsif ($rows == 1) {
  print "$key1 already in database.  Updating entry now.\n";
}  else {
  print "Error. \n";
}
  }
}
  }
}

Thanks in advance for any help.  

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

DBD Oracle compile error...

[NIPP, SCOTT V (SBCSI)]

I was hoping for a little help in troubleshooting a problem I am
having with the DBD-Oracle module (1.14).  I have set the ORACLE_HOME
variable and the Makefile creation completes.  Once I try to run the make to
actually build the module it dies on me with the following output:

ld: Can't find library or mismatched ABI for -lclntsh

I am building this again HP-UX 11i, running Perl 5.6.1 in 64 bit
mode with DBI 1.37.  Any help would be most appreciated.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

MySQL UPDATE Question...

[NIPP, SCOTT V (SBCSI)]

I am working on an application that has a database table with two
repeating fields.  The table has several fields that are uniq, and the there
are two fields that occur 6 times i.e. a1, b1, a2, b2, ., a6, b6.  This
table is populated and updated from a Perl script that parses a file.  All
of the uniq fields remain the same, but these last two fields can have up to
6 different values and I want to associate all 6 possibilities with this
single entry.  The original INSERT statement doesn't have to worry, it
simply enters the data into a1 & b1.  The problem arises when there is
another entry that needs to be made.
Assuming that the file is processed for the first time, and the
first entry places a row of data into the table, all of the uniq fields are
populated along with a1 & b1.  The next match of the Primary Key, needs to
now place the non-uniq field data into a2 & b2.  Likewise, the subsequent
Primary Key match needs to place this data into fields a3 & b3.  This is the
problem I am having.  I am not really sure on how to script the logic to get
the data to be inserted into the next available a# & b# filed combination.
I could test each of the a# fields, but this seems like a lot of code.
Any ideas on how to get the data into the next available field would
be most appreciated.  Unfortunately, I also have to figure out how to move
data around in the event that say all 6 non-uniq fields pairs are full, and
then the 3 & 4 field pairs are removed.  Maybe I don't even need to worry
about moving this data around.  Once again, any suggestions would be most
appreciated.  Thanks in advance.


Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Fetch without Execute...

[NIPP, SCOTT V (SBCSI)]

Thanks for the feedback.  Your guess about 'key' being a MySQL
reserved word was correct.  Now to take a stab at the next error, and do a
little reading on placeholders.  Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-Original Message-
From: Ronald J Kimball [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 14, 2003 8:25 AM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: Fetch without Execute...


On Tue, Oct 14, 2003 at 08:06:30AM -0500, NIPP, SCOTT V (SBCSI) wrote:
>   I have run into this problem before, but unfortunately, I can't seem
> to figure out what I am doing wrong again this time...  I am working on a
> script to parse a data file and INSERT/UPDATE this data into a MySQL
> database table.  I am able to verify that the data I am dealing with is
what
> I expect, and the simple logic in the script is performing as desired.
The
> problem I am running into at the moment is the following pair of error
> messages:
> 
> DBD::mysql::st execute failed: You have an error in your SQL syntax near
> 'key ='
> 1281-02F'' at line 1 at ./emc_data2.pl line 35,  line 134.
> DBD::mysql::st fetch failed: fetch() without execute() at ./emc_data2.pl
> line 36
> ,  line 134.

You're focusing on the wrong error message.  The "fetch without error"
occurs because the execute failed.  If you fix the execute, the fetch will
work also.

The execute fails because there's a syntax error in the SQL, as the first
message says.  You should print out the constructed SQL statement and make
sure it's valid.  Without seeing the entire statement, I'm not sure what
the syntax error is.  Is key a reserved word in MySQL?


> while ($data = ) {
>   @fields = split(/\s+/, $data);  
>   my $chk = $dbh->prepare("SELECT * FROM emc_usage WHERE key
> ='$fields[5]'");

Also, you should be using placeholders, or at least DBI->quote(), instead
of interpolating external data directly into an SQL statement.


Ronald

Fetch without Execute...

[NIPP, SCOTT V (SBCSI)]

I have run into this problem before, but unfortunately, I can't seem
to figure out what I am doing wrong again this time...  I am working on a
script to parse a data file and INSERT/UPDATE this data into a MySQL
database table.  I am able to verify that the data I am dealing with is what
I expect, and the simple logic in the script is performing as desired.  The
problem I am running into at the moment is the following pair of error
messages:

DBD::mysql::st execute failed: You have an error in your SQL syntax near
'key ='
1281-02F'' at line 1 at ./emc_data2.pl line 35,  line 134.
DBD::mysql::st fetch failed: fetch() without execute() at ./emc_data2.pl
line 36
,  line 134.

Here is the pertinent section of script:

opendir(DATA, "$file_source");
while ($file = readdir(DATA)) {
  if ($file =~ /^emc/) {
print "Processing $file. \n";
open(FILE, "$file_source$file");
($x, $host) = split(/./, $file);
while ($data = ) {
  @fields = split(/\s+/, $data);  
  my $chk = $dbh->prepare("SELECT * FROM emc_usage WHERE key
='$fields[5]'");
  $chk->execute ();
  my $test = $chk->fetch ();
  if ($test[8]) {
$used = "Yes";
  } else {
$used = "No";
  }
  my $cnt = $chk->rows;
  if ($cnt == 0) {

my $insert = $dbh->prepare("INSERT INTO emc_usage (key, emc_id,
size, prot, host, initial, vg, ctd_num1, used1)
 VALUES
('$fields[5]','$fields[0]','$fields[2]','$fields[4]','$host',NOW,'$fields[6]
','$ctd','$used')")
 or print "Error adding entry to database: ",
$dbh->errstr, "\n";

  }
}
close(FILE);
  }
}
closedir(DATA);

Thanks in advance for the help.  I am sure the answers will most
likely generate more questions, but hopefully this is a start.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

Help with some DBI errors...

[NIPP, SCOTT V (SBCSI)]

I am working on a script to parse a series of text files and insert
or update this data as appropriate into a couple of database tables.  I am
stuck at a point with a couple of errors that are just confusing the hell
out of me.  I keep going around in circles and I must be missing something
here.  Please let me know if you can spot anything in this piece of code.
This is a pair of subroutines from the script, and the place where the
problems exist are in the fs_db_pop subroutine.  Here is the code with line
numbers, and then the output/error messages.

87  sub fs_db_pop {
88$server = $name;
89my $test2 = $dbh2->prepare("SELECT id FROM fsref WHERE server
='$serve
r' AND fs_name = '$fs_name'");
90$test2->execute();
91
92$rows2 = $test2->rows();
93print "Number of matching rows:  $rows2 \n";
94if ($rows2 == 1) {
95  my $vals = $test2->fetchrow_hashref ('NAME_lc');
96  my $id = $vals->{id};
97  my $query = $dbh2->prepare("SELECT * FROM fsdata WHERE id
='$id'");
98  $query->execute();
99  while (my $ref2 = $query->fetchrow_hashref ('NAME_lc')) {
   100undef $set2;
   101foreach my $key2 (keys %$ref2) {
   102  unless ($$key2 eq $ref2->{$key2}) {
   103$set2 .= " , " if $set2;
   104$set2 .= $key2 . "=\'$$key2\'";
   105  }
   106  $query->finish();
   107}
   108if ($set2) {
   109  print "Entry found in database.  Updating information for
$id. \
n";
   110  my $sql2 = "UPDATE fsdata SET $set2 WHERE id = '$id'";
   111  my $sth2 = $dbh2->prepare(qq{$sql2});
   112  $sth2->execute();
   113  $sth2->finish();
   114}
   115  }
   116} else {
   117undef $id;
   118$dbh2->do("INSERT INTO fsref VALUES('$id','$server','$fs_name')")
or p
rint "Error updating fsref table:  ", $dbh2->errstr, "\n";
   119$id = $dbh2->{mysql_insertid};
   120$dbh2->do("INSERT INTO fsdata
VALUES('$id','$lvol','$stripe_num','$str
ipe_size','$size','$owner','$group','$export')") or print "Error updating
fsdata
 table: ", $dbh2->errstr, "\n";
   121print "Entry not found in database.  Adding information for $id.
\n";
   122}
   123$test2->finish();
   124  }
   125
   126  sub filesystems {
   127# $fs_index = (split /\[|\]/)[1];
   128@fs_data = split(/:\s+/);
   129chomp($fs_data[1]);
   130# print "Test:  $fs_data[0] \n";
   131if ($fs_data[0] =~ /^Filesystem/) {
   132  $fs_name = $fs_data[1];
   133} elsif ($fs_data[0] =~ /^Owner/) {
   134  ($owner, $group) = split /\s+/, $fs_data[1], 2;
   135} elsif ($fs_data[0] =~ /^LvolN/) {
   136  $lvol = $fs_data[1];
   137} elsif ($fs_data[0] =~ /^Exported/) {
   138  $export = $fs_data[1];
   139} elsif ($fs_data[0] =~ /^LvolS\[/) {
   140  $size = $fs_data[1];
   141} elsif ($fs_data[0] =~ /^LvolStN/) {
   142  $stripe_num = $fs_data[1];
   143  print "$stripe_num \n";
   144} elsif ($fs_data[0] =~ /^LvolStS/) {
   145  $stripe_size = $fs_data[1];
   146  fs_db_pop();
   147  undef @fs_data;
   148}
   149  }

I mainly include the second subroutine because that is where the
parsing of the data from the text file occurs.  It is working fine, but I
thought it might be helpful to see it also.  The rest of the script mainly
sets up the database connection strings, and handles adding/updating a
seperate database table with other information.  Here is the output of the
script:

Number of matching rows:  1 
Entry found in database.  Updating information for 116. 
DBD::mysql::st execute failed: You have an error in your SQL syntax near
'group=
'sys ' , lvol='/dev/vg02/lvol2 ' , stripe_num='4 ' , id='377140311' , size'
at l
ine 1 at ./sys_db_update-dev.pl line 112,  line 1145.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
./sys_db_up
date-dev.pl line 99,  line 1145.
0  
Number of matching rows:  1 
Entry found in database.  Updating information for 117. 
DBD::mysql::st execute failed: You have an error in your SQL syntax near
'group=
'root ' , lvol='/dev/vg00/home_tmp ' , stripe_num='0 ' , id='377140311' , '
at l
ine 1 at ./sys_db_update-dev.pl line 112,  line 1153.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
./sys_db_up
date-dev.pl line 99,  line 1153.
pyro found in database.  Updating information for pyro. 

Hopefully this is enough information for someone to help figure out
what I am doing wrong here.  Thanks in advance for the help.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: fetch( ) without execute( ) Error...

[NIPP, SCOTT V (SBCSI)]

OK.  The other execute is further up.  I am actually receiving this
error for each fetchrow_hashref.  Here is a bit more of the code:
 
  my $test2 = $dbh2->prepare("SELECT id FROM fsref WHERE server ='$server'
AND f
s_name = '$fs_name'");
  $test2->execute();
 
  $rows2 = $test2->rows();
  print "Number of matching rows:  $rows2 \n";
  $test2->finish();
  if ($rows2 == 1) {
my $vals = $test2->fetchrow_hashref ('NAME_lc');
my $id = $vals->{id};
my $query = $dbh2->prepare("SELECT * FROM fsdata WHERE id ='$id'");
$query->execute();
$query->finish();
while (my $ref2 = $query->fetchrow_hashref ('NAME_lc')) {
  undef $set2;
 
This now includes the other execute.  Thanks again.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 03, 2003 11:54 AM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: fetch( ) without execute( ) Error...



Which fetchrow_hashref line? I see two of them, but only one execute.  You
need to include more code for anyone to know what's really wrong here.


________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]





"NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 


09/03/2003 12:43 PM 



To:[EMAIL PROTECTED] 
cc: 
Subject:fetch( ) without execute( ) Error...



 I keep coming up with this error, but I AM performing an
execute
prior to the fetch.  I have seen some other threads on this, and have
upgraded the DBD:Mysql to the latest version on CPAN.  Any ideas?  Here is a
small portion of the code:

if ($rows2 == 1) {
   my $vals = $test2->fetchrow_hashref ('NAME_lc');
   my $id = $vals->{id};
   my $query = $dbh2->prepare("SELECT * FROM fsdata WHERE id ='$id'");
   $query->execute();
   $query->finish();
   while (my $ref2 = $query->fetchrow_hashref ('NAME_lc')) {
 undef $set2;

I am getting an error on the fetchrow_hashref line.  Thanks
in
advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

fetch( ) without execute( ) Error...

[NIPP, SCOTT V (SBCSI)]

I keep coming up with this error, but I AM performing an execute
prior to the fetch.  I have seen some other threads on this, and have
upgraded the DBD:Mysql to the latest version on CPAN.  Any ideas?  Here is a
small portion of the code:

if ($rows2 == 1) {
my $vals = $test2->fetchrow_hashref ('NAME_lc');
my $id = $vals->{id};
my $query = $dbh2->prepare("SELECT * FROM fsdata WHERE id ='$id'");
$query->execute();
$query->finish();
while (my $ref2 = $query->fetchrow_hashref ('NAME_lc')) {
  undef $set2;

I am getting an error on the fetchrow_hashref line.  Thanks in
advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

MySQL keys problem...

[NIPP, SCOTT V (SBCSI)]

I am working on a script to collect system information, and I have
run into a problem I cannot quite figure out.  Here is the pertinent section
of code:

  my $test = $dbh->prepare("SELECT id FROM fsref WHERE server ='$server' AND
fs_
name = '$fs_name'");
  $test->execute ();

  $rows = $test->rows;
  print "Server name:  $server   Filesystem:  $fs_name  \n";
  print "Number of matching rows:  $rows \n";
  if ($rows == 1) {
my $vals = $test->fetchrow_hashref ('NAME_lc');
my $id = $vals->{id};
my $query = $dbh->prepare("SELECT * FROM fsdata WHERE id ='$id'");
$query->execute ();
while (my $ref = $query->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 "Entry found in database.  Updating information for $id. \n";
my $sql = "UPDATE fsdata SET $set WHERE id = '$id'";
my $sth = $dbh->prepare(qq{$sql});
$sth->execute;
  }
}
  } else {
$dbh->do("INSERT INTO fsref VALUES('$id','$server','$fs_name')") or
print "E
rror updating fsref table:  ", $dbh->errstr, "\n";
$id = $dbh->{mysql_insertid};
$dbh->do("INSERT INTO fsdata
VALUES('$id','$lvol','$stripe_num','$stripe_siz
e','$size','$owner','$group','$export')") or print "Error updating fsdata
table:
  ", $dbh->errstr, "\n";
  print "Entry not found in database.  Adding information for $id. \n";
  }
}

The code before this appears to be functioning properly, and is the
actual data collection commands i.e. bdf, vgdisplay, etc.  The problem is
that I am getting some error messages that I do not understand what is
causing them.  Any help would be greatly appreciated.  Here is a portion of
the output errors.

Server name:  stanley   Filesystem:  /var  
Number of matching rows:  1 
Use of uninitialized value in string eq at ./space_data.pl line 53.
Use of uninitialized value in concatenation (.) at ./space_data.pl line 55.
Entry found in database.  Updating information for 23. 
DBD::mysql::st execute failed: Duplicate entry '0' for key 1 at
./space_data.pl 
line 62.

Note:  The first line of the included code is line 39.  Thanks.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Perl/MySQL script...

[NIPP, SCOTT V (SBCSI)]

Making progress on the DBI:Proxy path.  I am currently getting the
following errors from the output...

DBI handle cleared whilst still active at ./space_data.pl line 20.
dbih_clearcom (sth 0x4025172c 0x40251d20, com 0x40137878, imp
DBD::Proxy::st
):
   FLAGS 0x115: COMSET Active Warn PrintError 
   PARENT DBI::db=HASH(0x401363b8)
   KIDS 0 (0 Active)
   IMP_DATA undef
   NUM_OF_FIELDS 1
   NUM_OF_PARAMS 0
panic: DBI active kids (2) > kids (1) at
/usr/local/lib/perl5/site_perl/5.6.0/PA
-RISC2.0/DBD/Proxy.pm line 526.
DBD::Proxy::db disconnect failed: Server returned error: Failed to execute
metho
d CallMethod: Not permitted for method disconnect of class
DBI::ProxyServer::db 
at /usr/local//lib/perl5/site_perl/5.6.0/RPC/PlServer.pm line 328.

Anyone recognize this???  I can post the code also if necessary,
just wanted to keep the post short.  Thanks.

-Original Message-
From: William Goedicke [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 29, 2003 3:10 PM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: Perl/MySQL script...


Dear Scott et al - 

>>>>> "SCOTT" == SCOTT V NIPP  writes:

SCOTT> I am developing a series of scripts to collect system
SCOTT> information on a fairly large number of HP-UX servers.

Use SNMP to gather the information remotely and then send the information
to a remote MySQL.  Then your script and the associated perl modules can 
be placed on whatever machine you want.

As an aside you should probably check out www.nagios.org

 Yours -  Billy


 William Goedicke [EMAIL PROTECTED]
  http://www.goedsole.com:8080  


  Lest we forget:

There may be no stupid questions, but there's no shortage of morons
asking them.

- William Goedicke

Perl/MySQL script...

[NIPP, SCOTT V (SBCSI)]

Hey all...  I am developing a series of scripts to collect system
information on a fairly large number of HP-UX servers.  Initially, this
system information is fairly limited to filesystem information but will be
expanded over time to include other things like kernel parameter settings,
user account information, etc.  I am wanting to capture this information
into a MySQL database on one of these servers.  Currently I have a setup
that captures some of this basic information and ftps it over to the
database server.  The database server then has a cron job that kicks off a
separate script that processes these data files.  I would prefer to have
each server simply update the database directly.  The problem with this is
that I cannot seem to compile the DBI module for MySQL on each server
without having some MySQL libraries installed on each server.  I really
don't want to have to screw around with installing or copying MySQL
libraries on 80+ servers.
Does anyone have any suggestions on how to address this issue?
Thanks in advance for the feedback.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

Perl/MySQL question...

[NIPP, SCOTT V (SBCSI)]

I am not sure if this is a Perl/DBI question specifically or not,
but here goes...  I seem to remember reading somewhere that you could return
the value of an "auto-increment" field during an INSERT operation on a MySQL
database.  Is this just wishful thinking on my part or does this sound
right?
Basically, I have two tables, one is a reference table where the
other is an actual data table.  The reference table is a simple three column
table with one of the columns being an ID number that is the primary key and
is auto-increment.  The other table uses this same ID number as it's primary
key and contains pertinent data.  What I need to do is upon a "new" database
entry, create the new reference entry and immediately retrieve the new ID
number such that I can then insert the actual data into the other table with
the new reference ID.  If I can retrieve the ID number as some kind of
output from the reference table insert, I can avoid a second query to
retrieve this number.  Make sense?
Thanks in advance for the help, and sorry again if this is off topic
for this group.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

DBD MySQL Installation Help...

[NIPP, SCOTT V (SBCSI)]

Is it possible to install the Perl DBD for MySQL on a system that
does not have MySQL installed?  Basically, I am wanting for numerous servers
to be able to connect and interact with a MySQL database on another server.
Only 2 servers have MySQL installed, none of the others do.  how do I get
the DBD driver to install on systems without MySQL installed?  Thanks in
advance for the help.  If I missed this in the docs, I apologize in advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Database layout and query questions...

[NIPP, SCOTT V (SBCSI)]

Thanks for the great feedback.  This is exactly what I was hoping
for.  Unfortunately, I think I am still missing some key database
fundamentals to completely understanding some of your suggestions.  I guess
where I am still missing something is how I can layout the table(s) to
handle the following example...

Let's say that I have users Mary, Joe, Frank, and Dan.  I also have
servers panther, cheetah, jaguar and lion.  The data for each account that I
want to maintain is UID, GID, home directory, and default shell.
In designing a table or tables to handle this example what can I
make as a primary key?  My idea was to have a table named mary, with a row
for each server, and each column would hold the data such as UID, GID, etc.
This would mean that the primary key for each row would simply be the server
name.
By holding all of the data, including server name, in a single
table, I am not sure how I would define a primary key.  I couldn't use the
user name or server name as there would be duplication.  I suppose I could
use a dummy numeric field that is auto-incrementing, but I am not sure how
good an idea this is.  I think I have read somewhere that you can actually
use a combination of multiple columns as a primary key or index, but this is
something I am obviously not familiar with.
One other concern I have is regarding performance.  The database
work I have done so far has been dealing with relatively miniscule amounts
of data.  This database table however is going to contain information for
about 80 servers with somewhere around 300 users per server on average.
This is quite a large number of rows from my very limited experience.  I
don't want to come up with a poor table design that ends up causing problems
down the line.

Well, that's about all I can think of at the moment.  I am sure that
I will have plenty more questions as this progresses.  Thanks again for the
feedback.

-Original Message-
From: andy law (RI) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 18, 2003 3:33 AM
To: NIPP, SCOTT V (SBCSI)
Subject: RE: Database layout and query questions...


Scott,

Define your details table to have a column that refers to the server.

e.g.

Table definition.


user_name   char(20)
server_name char(60)
shell
etc.
etc.

Then you have everything in one table and you can pull out details as you
need.

Primary key should be on user_name, server_name with a secondary index
defined the other way around (server_name, user_name) although the
efficiencies of that differ between DBMS implementations.

To illustrate, give me all the account details for user 'xyz'

select * from table where user_name = 'xyz'

(you can do this on your design, but it is very inefficient if you want to
query more than one user)





Give me all the accounts on machine 'abc'

select * from table where server_name = 'abc'
(you couldn't do this in any efficient manner on your design)



Give me all the accounts who use bash

select * from table where shell = '/bin/bash'
(you couldn't do this in any efficient manner on your design)



Give me all the users who use different shells on different machines...

select distinct a.* from table a, table b
where a.user_name = b.user_name
and a.shell != b.shell

(OK, that's a bit more complicated for a newbie...)
(you can do this on your design, but it is very inefficient if you want to
query more than one user)


Later,

Andy

-
Yada, yada, yada...

The information contained in this e-mail (including any attachments) is
confidential and is intended for the use of the addressee only.   The
opinions expressed within this e-mail (including any attachments) are the
opinions of the sender and do not necessarily constitute those of Roslin
Institute (Edinburgh) ("the Institute") unless specifically stated by a
sender who is duly authorised to do so on behalf of the Institute.


> -Original Message-
> From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]
> Sent: 17 June 2003 21:35
> To: 'David N Murray'
> Cc: '[EMAIL PROTECTED]'
> Subject: RE: Database layout and query questions...
> 
> 
>   The logic in having a table per user is basically as follows...
> Each user would have a table with columns such as UID, GID, 
> home directory,
> default shell, date created, etc.  Each row would then be an 
> entry with all
> of this pertinent information for each server that the user 
> has an account
> on.  This seems to make the most logical sense to me as it 
> seems that it
> would be very organized.  Please bear in mind that I am an SA playing
> DBA/Web Developer here.  I am guessing that there are 
> actually a whole host
> of reasons why this is a bad idea.  I am eager to learn, but 
> please keep it
> basic as I still a database newbie

RE: Database layout and query questions...

[NIPP, SCOTT V (SBCSI)]

I have this book, however, it doesn't really have any good
information that I have found specifically regarding database design.  The
one area where I am really in need of help is in database design.  I can
work through the scripting to access the database both for queries and data
insertion, but designing the layout and relationships of the tables is where
I am truly lost.  Thanks again.

-Original Message-
From: Ron Savage [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 17, 2003 6:05 PM
To: DBI users
Subject: RE: Database layout and query questions...


On Tue, 17 Jun 2003 15:35:20 -0500, NIPP, SCOTT V (SBCSI) wrote:

Hi Scott

>Thanks again for all the good feedback.  If anyone can recommend 
a
>good book that could help me out on this I would be most
>appreciative.  I
>have some MySQL reference books, but nothing that really goes into
>database
>design.  I am planning on taking some database courses at a local

http://www.kitebird.com/mysql-perl/
-- 
Cheers
Ron Savage, [EMAIL PROTECTED] on 18/06/2003
http://savage.net.au/index.html

RE: Database layout and query questions...

[NIPP, SCOTT V (SBCSI)]

The logic in having a table per user is basically as follows...
Each user would have a table with columns such as UID, GID, home directory,
default shell, date created, etc.  Each row would then be an entry with all
of this pertinent information for each server that the user has an account
on.  This seems to make the most logical sense to me as it seems that it
would be very organized.  Please bear in mind that I am an SA playing
DBA/Web Developer here.  I am guessing that there are actually a whole host
of reasons why this is a bad idea.  I am eager to learn, but please keep it
basic as I still a database newbie.
Additionally, I am going to be developing a similar system to use
for filesystem information on our servers.  We have about 85 servers, and
most of our servers have well over 25 independent filesystems.  The
structure I described above would be similar for this project with each
server having a table, columns being things like size, mount point UID,
mount point GID, volume group, etc.  The rows would then be the individual
filesystems mount points themselves.
Thanks again for all the good feedback.  If anyone can recommend a
good book that could help me out on this I would be most appreciative.  I
have some MySQL reference books, but nothing that really goes into database
design.  I am planning on taking some database courses at a local community
college starting this fall, but I also need something to help me learn and
limp along in the mean time.

-Original Message-
From: David N Murray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 10, 2003 3:21 PM
To: NIPP, SCOTT V (SBCSI)
Cc: '[EMAIL PROTECTED]'
Subject: Re: Database layout and query questions...


Some questions, that might help you answer yours:

What's the advantage of having a table per user, vs. a 'users' table with
one row per user?
How many rows do you envision in each user table, and what are their
columns?
If you are looking to create a per_user table that just contains 'key',
'value' pairs for each row, why not just a table with 'user','key','row'?
If you collect stats at the user level, and store these in the per_user
table, how do you summarize? select sum(cpu_time) from joe union select
sum(cpu_time) from alice union select sum(cpu_time) from sam... Do you see
my point?
How much code do you have to rewrite if you have to change DB platforms,
in the future?  SHOW TABLES works nice from MySQL, but that's the only
place it works (AFAIK).  You're moving away from standard SQL with a
per_user design, since DDL isn't very standard.
Which rule of normalization are you using to decide to use a separate
table for each user?  (In my experience, most production systems struggle
when you go beyond 3NF.)
Are you going to use any reporting tools (e.g. Access or Crystal) and how
are they going to fare with per_user tables?

Just some thoughts.  Feel free to respond or just digest the questions
yourself.  The answer may jump out at you.

HTH,
Dave

On Jun 10, NIPP, SCOTT V (SBCSI) scribed:

>   I am currently working on developing a User Account Management
> system.  The environment I support currently has about 80 servers, and a
> user community of several hundred.  I currently have a Account Request
> system that I developed running on one of my webservers and this is about
to
> roll into production supporting our environment.  I am now working on a
> database and scripting that will inventory every existing user account on
> all of the systems.  In designing the DB layout I am thinking of creating
a
> table for each user with all of the passwd file information as well as a
few
> other tidbits.  This will allow us much better account management than we
> have ever had in the past, the past being faxed in request forms.
>   My questions are many, but the immediate questions are as follows...
> First of all, is it a bad idea to structure the database as described?
> Basically, the database will eventually contain hundreds of tables, each
> with maybe 10 or so fields.  This is what makes the most sense to me
> thinking about this.  Assuming that creating the database as such is not a
> bad idea, I am now trying to figure out how to query the DB to determine
if
> a new table needs to be built, for a new user, or an existing table needs
to
> be updated.  Below is roughly what I was planning...
>
> 
> open (PASSWD, "/etc/passwd");
> while () {
>   @fields = split(/:/, $_);
>   $user = $field[0];
>   my $tblqry = $dbh->prepare("SHOW TABLES FROM Users LIKE '$user'");
>   $tblqry->execute();
>
>   Here is where I am getting a little confused...  I am not sure how I
> get a return value or data from the 'Show' statement.  I need at the very
> least a return value at this point to 

Database layout and query questions...

[NIPP, SCOTT V (SBCSI)]

I am currently working on developing a User Account Management
system.  The environment I support currently has about 80 servers, and a
user community of several hundred.  I currently have a Account Request
system that I developed running on one of my webservers and this is about to
roll into production supporting our environment.  I am now working on a
database and scripting that will inventory every existing user account on
all of the systems.  In designing the DB layout I am thinking of creating a
table for each user with all of the passwd file information as well as a few
other tidbits.  This will allow us much better account management than we
have ever had in the past, the past being faxed in request forms.
My questions are many, but the immediate questions are as follows...
First of all, is it a bad idea to structure the database as described?
Basically, the database will eventually contain hundreds of tables, each
with maybe 10 or so fields.  This is what makes the most sense to me
thinking about this.  Assuming that creating the database as such is not a
bad idea, I am now trying to figure out how to query the DB to determine if
a new table needs to be built, for a new user, or an existing table needs to
be updated.  Below is roughly what I was planning...


open (PASSWD, "/etc/passwd");
while () {
  @fields = split(/:/, $_);
  $user = $field[0];
  my $tblqry = $dbh->prepare("SHOW TABLES FROM Users LIKE '$user'");
  $tblqry->execute();

Here is where I am getting a little confused...  I am not sure how I
get a return value or data from the 'Show' statement.  I need at the very
least a return value at this point to test against so I know whether to
update an existing table or create a brand new table altogether.
Any help would be most appreciated.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: DBD on HPUX

[NIPP, SCOTT V (SBCSI)]

This may or may not be obvious...  Did you add the -lcl and
-lpthreads as the first in the list of libraries?  I have learned from
experience they absolutely have to be the first two in the list in that
order.  Also, maybe obvious, you were compiling Perl with the -lcl and
-lpthreads, right?  Not only DBI?

-Original Message-
From: Brian D. Silverio [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 12, 2002 3:35 PM
To: [EMAIL PROTECTED]
Subject: DBD on HPUX 


Greetings,
I have spent about two weeks going over the archives and reading
everything I can find out the DBD on hpux.  I still can't get it to work

Short form:
HPUX 11.11 64 bit
Oracle 8.1.7 32 bit
HP extra cost compiler
Perl 5.8.0 and 5.6.1 used
DBI 1.18, 1,25 and 1.30 all tried
DBD 1.07 and 1.12 tried.
Non threading, -lcl and lpthreads added,  +z added,
+Z also tried alone and in combo with +z
perl Makefile.PL -l tried

Can not shl_load()etc

Static builds also tried they will not link properly
used the symbol finder script to find the missing symbols, add the
libraries and they are still not found.

At this point I have tried each and every thing I can find on google.  I
still can't make it work.

Can anyone offer additional suggestions?

I am new to HPUX but have been working with unix since 1985.

Thanks
Brian

RE: Database to Excel script...

[NIPP, SCOTT V (SBCSI)]

I am trying to do the cell formatting now and I am running into an error
message:
 
Can't locate object method "set_column" via package
"Spreadsheet::WriteExcel" at
 ./oncall_report.pl line 23.
 
Here is the line of the script...
 
$xls->set_column(6, 7, 50);
 
I am not sure what is wrong with this...  I pretty much copied this code
from one of the examples, but I am definitely having a problem.  Thanks
again.

-Original Message-
From: Sterin, Ilya [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 02, 2002 11:12 PM
To: NIPP, SCOTT V (SBCSI); ''Jeff Zucker' '; 'dbi-users '
Subject: RE: Database to Excel script...



Actually I can set up some event handlers in DBIx::Dump, which will allow
you to assign an even handler to handle transformations.

Give me until tomorrow noon. 

Ilya 

-Original Message- 
From: NIPP, SCOTT V (SBCSI) 
To: 'Jeff Zucker'; Sterin, Ilya; dbi-users 
Sent: 10/2/02 2:45 PM 
Subject: RE: Database to Excel script... 

OK...  I am stuck once again.  I am to pull the data from the 
database and export it directly to an Excel file.  Now, I am trying to 
figure out the formatting...  The first problem I am running into is 
reformatting dates from the output of MySQL to the desired Excel format. 
Here is a same of what I am attempting: 

MySQL date:  2002-09-02 13:13:00 

Desired Excel date:  9/2/2002 13:13 

I know that there is a set_num_format function in the WriteExcel 
module, but this works with epoch numbers.  I am not sure about 
transforming 
one date format to another date format.  Any help would be greatly 
appreciated. 

-Original Message- 
From: Jeff Zucker [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
] 
Sent: Tuesday, October 01, 2002 6:51 PM 
To: Sterin, Ilya; dbi-users 
Subject: Re: Database to Excel script... 


Sterin, Ilya wrote: 

> Jeff, that's helpful:-) 
> 
> but that does not pupulat the excel spreadsheet with data from a 
select 
> query, rather you have to select and loop while do()ing it. 
> 
> There is lot of overhead for certain things, I just though a simple 
mod 
> would help to just dump data, and nothing else. 


How much simpler can you get than this (requires only AnyData, not 
DBD::AnyData or any of the SQL* modules): 

   use DBI; 
   use AnyData; 
   my $dbh = DBI->connect($mysql_or_other_dsn); 
   my $sth = $dbh->prepare($query); 
   for my $new_format(qw(CSV XML Ini Tab Pipe Fixed HTMLTable)) { 
 adConvert( 'DBI', $sth, $new_format, "newfile.$new_format"); 
   } 
   __END__ 

-- 
Jeff 

RE: Database to Excel script...

[NIPP, SCOTT V (SBCSI)]

OK...  I am stuck once again.  I am to pull the data from the
database and export it directly to an Excel file.  Now, I am trying to
figure out the formatting...  The first problem I am running into is
reformatting dates from the output of MySQL to the desired Excel format.
Here is a same of what I am attempting:

MySQL date:  2002-09-02 13:13:00

Desired Excel date:  9/2/2002 13:13

I know that there is a set_num_format function in the WriteExcel
module, but this works with epoch numbers.  I am not sure about transforming
one date format to another date format.  Any help would be greatly
appreciated.

-Original Message-
From: Jeff Zucker [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 6:51 PM
To: Sterin, Ilya; dbi-users
Subject: Re: Database to Excel script...


Sterin, Ilya wrote:

> Jeff, that's helpful:-)
> 
> but that does not pupulat the excel spreadsheet with data from a select
> query, rather you have to select and loop while do()ing it.
> 
> There is lot of overhead for certain things, I just though a simple mod
> would help to just dump data, and nothing else.


How much simpler can you get than this (requires only AnyData, not 
DBD::AnyData or any of the SQL* modules):

   use DBI;
   use AnyData;
   my $dbh = DBI->connect($mysql_or_other_dsn);
   my $sth = $dbh->prepare($query);
   for my $new_format(qw(CSV XML Ini Tab Pipe Fixed HTMLTable)) {
 adConvert( 'DBI', $sth, $new_format, "newfile.$new_format");
   }
   __END__

-- 
Jeff

RE: Database to Excel script...

[NIPP, SCOTT V (SBCSI)]

Thanks.  This is definitely helpful, but the one thing I find a bit
funny is the comments in the code...  I think I recognize it as German, but
being a stupid American, English is the only language I actually speak.  :)

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 11:14 AM
To: NIPP, SCOTT V (SBCSI)
Cc: '[EMAIL PROTECTED]'
Subject: Re: Database to Excel script...


Here we go:
> 
> I have a MySQL database that I have a few Perl scripts working on.
> I am now looking into doing some reporting on these database tables.  I
> would really like to find a script that queries the database and the uses
> the Spreadsheet::WriteExcel module to output the data directly to an Excel
> file.  

use diagnostics;
use strict;
use DBI;
use Spreadsheet::WriteExcel;

my $filename ="abfrage.xls";
my $user = "root";
my $passwort = "";
my $dsn = "DBI:mysql:database=medialsoft;host=localhost";
unlink 'dbitrace.log' if -e 'dbitrace.log';

print "Content-type: application/vnd.ms-excel\n";
print "Content-Disposition: attachment; filename=$filename\n";
print "\n";
my ($model_id, $vorname, $name);
my @entries = ();
my %font= (
font  => 'Arial',
size  => 12,
color => 'black',
bold  => 1,
  );
my %shading = (
fg_color => 'red',
pattern  => 1,
  );


  my $dbh = DBI->connect($dsn, $user, $passwort,{RaiseError
=> 1});
 $dbh->trace( 2, "dbitrace.log" );
# trace verfolgung
 my $sth1 = $dbh->prepare( "SELECT Count(*) FROM
modelle" );
 $sth1->execute();
 # alle Daten durchzählen, Gesamtzahl in
Variable $count schreiben
 my $count = $sth1->fetchrow_array();
 my $sth = $dbh->prepare( "SELECT model_id, vorname, name
FROM modelle" );

 $sth->execute();
 # alle Daten aus der Datenbank holen
 while (my $ref = $sth->fetchrow_hashref ()) {
push @entries, [ $ref->{model_id}, $ref->{vorname},
$ref->{name} ];
 # pack alles in den HASH
}
   my $workbook  = Spreadsheet::WriteExcel->new("-");
   my $worksheet = $workbook->addworksheet("Adressen");
 $worksheet->set_column(0, 0, 10);
 $worksheet->set_column(1, 4, 20);
[..snip..]
 my $format = $workbook->addformat();
my $format1 = $workbook->addformat(%font);
   my $format2 = $workbook->addformat(%font, %shading);
   my $format3 = $workbook->addformat();
$format3->set_text_wrap();
$format3->set_align('justify');
   $format->set_bold();
   # das war die Exceltabellenformatierung
   # jetzt kommt das schleifen der Daten durch Zeilen und
Spalten
for my $col ( 0..7 ) {
  for my $row ( 1..$count ) {
 my $e = shift @entries;
# HASH entpacken
last unless $e;
# bis nichts mehr an Daten da ist
   my ($id, $vor, $nach) = @$e;

  $worksheet->write($id, 0, $id);
  $worksheet->write($id, 1, $vor);
 $worksheet->write($id, 2, $nach);
$worksheet->write(($count+2), 0,
"Programmiert als Open Source Software mit Perl5, DBD::MySQL,
Spreadsheet::WriteExcel by Andreas Schmitz 3. September 2002",
$format1);
   # Varaiblen in Worksheets schreiben
 

}
}
 for my $col ( 0..7 ) {
$worksheet->write(0, 0, "ID-Nummer", $format);
 $worksheet->write(0, 1, "Vorname", $format1);
 $worksheet->write(0, 2, "Name", $format1);
 $worksheet->write(0, 34, "Gesamtanzahl",$format);
   }
  #Ueberschriften schreiben


-- 
Andreas Schmitz http://www.medialsoft.de
 _ _  _  _ ° _ _ _  _ _
| | ||_ | \|| || _| (_)|- |
| | ||_ |_/||-||__

RE: Database to Excel script...

[NIPP, SCOTT V (SBCSI)]

Yes.  I also am using phpMyAdmin for some queries, but I am actually
looking to create a cron job that will run a monthly report and e-mail the
resulting Excel file to a couple of individuals.  I am able to do all of
this manually at this time, but I would really like to automate the process
if possible.  This is also a great learning opportunity and I think that I
will have more requests in the future for generating Excel reports of this
nature.

-Original Message-
From: Sterin, Ilya [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 10:11 AM
To: NIPP, SCOTT V (SBCSI); ''[EMAIL PROTECTED]' '
Subject: RE: Database to Excel script...



You can easily accomplish that by dumpin into a CSV (comma separated file)
with .csv extension which will open in excel as you want.

Ilya 

-Original Message- 
From: NIPP, SCOTT V (SBCSI) 
To: '[EMAIL PROTECTED]' 
Sent: 10/1/02 9:04 AM 
Subject: Database to Excel script... 

I have a MySQL database that I have a few Perl scripts working 
on. 
I am now looking into doing some reporting on these database tables.  I 
would really like to find a script that queries the database and the 
uses 
the Spreadsheet::WriteExcel module to output the data directly to an 
Excel 
file.  Does anyone out there have something like this that I could 
cannibalize?  Thanks in advance. 

Scott Nipp 
Phone:  (214) 858-1289 
E-mail:  [EMAIL PROTECTED] 
Web:  http:\\ldsa.sbcld.sbc.com   

Database to Excel script...

[NIPP, SCOTT V (SBCSI)]

I have a MySQL database that I have a few Perl scripts working on.
I am now looking into doing some reporting on these database tables.  I
would really like to find a script that queries the database and the uses
the Spreadsheet::WriteExcel module to output the data directly to an Excel
file.  Does anyone out there have something like this that I could
cannibalize?  Thanks in advance.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

RE: Connecting to Oracle through Perl.

[NIPP, SCOTT V (SBCSI)]

I am pretty sure this is supposed to reside in /etc.  I know that on
our HP-UX boxes the tnsnames.ora is in /etc and we use Perl for some
database access.

-Original Message-
From: Sridhar Saragu [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 8:24 AM
To: [EMAIL PROTECTED]
Subject: Connecting to Oracle through Perl.


Hi,

Can  somebody tell me  where the Perl  exactly  look for tnsnames.ora while
connecting to Oracle ? (like home, oracle_home, tns_admin etc ...).  I want
to

know the order in which it looks & the directories ?

Thanks & Regards

Sridhar

Need a little help...

[NIPP, SCOTT V (SBCSI)]

I have a question about connecting to one of two different FTP
servers.  This is a libnet question that I know if off topic, but PLEASE
help me.  I have a MySQL database running and I use Perl/DBI to populate the
database.  I am using libnet to FTP the data to the server, and then post
data from the local server.  I know that I could post data from the remote
machines with DBI, but I feel that limiting the account that posts to the
database to the local system provides a bit of security.  Anyway, a group of
my systems are on the other side of a firewall that does not allow FTP
traffic.  I am trying to figure out a way to somehow test the FTP
connection, and if the connection is unsuccessful then I want to FTP to a
different server instead.  I have been handling this by having a different
version of the script that simply transfers to the alternate, but I don't
want to have to maintain two different scripts.
Any help would be appreciated.  Thanks in advance, and sorry for
posting off topic.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com

Off Topic Question...

[NIPP, SCOTT V (SBCSI)]

Sorry for posting a slightly off topic question, but I am having
trouble figuring out how to do something basic.  I have a MySQL database
that has a UNIQUE column in a table.  My problem is that I need to change
the column to non-UNIQUE.  I cannot figure out how to do this through the
mysql monitor.  I know this should be a simple ALTER TABLE statement, or
something similar but I can't figure out exactly how to do this.  Thanks in
advance, and sorry again for posting off topic.

>   Scott Nipp
>   Systems Analyst
>   SBC Long Distance
>   (214) 858-1289
> 
> 

RE: DBD on HPUX 11i

[NIPP, SCOTT V (SBCSI)]

I was able to install DBI, and DBD for MySQL under 11.0, and I will
be doing this under 11i.  I am quite suprised that you are having trouble.
Please let us know of your results.

-Original Message-
From: Waldemar Zurowski [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 18, 2002 5:48 AM
To: Alex Green
Cc: [EMAIL PROTECTED]
Subject: Re: DBD on HPUX 11i


On Thu, 18 Apr 2002 12:12:03 +0200
"Alex Green" <[EMAIL PROTECTED]> wrote:

> Does anyone have a link of info on how to build DBD on HPUX 11i...

You mean DBI, or some specific driver?
Instalation of DBI was quite easy, and went witout problems.

Waldemar

-- 
o Powiadam Wam, iż straszny czas nastał na ulicach po owym pamiętnym
  zaćmieniu Słońca. Albowiem wtedy właśnie uzyskałem prawo jazdy.


-- 
Encyklopedia multimedialna w prezencie!
http://www.e-mail.onet.pl

RE: UPDATE Statement Problem...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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...

[NIPP, SCOTT V (SBCSI)]

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