Re: DBD::CSV and skip_first_line
On 11/26/2012 11:56 AM, H.Merijn Brand wrote: >> I still think it would be easier if skip_first_line were not presumed >> > (forced to) false if col_names is set, but rather presumed false only if >> > not set explicitly true. > > We agree, investigating what is actually required (and should be > documented) Just curious whether any progress had been made on this front since we last spoke about it. -- (please respond to the list as opposed to my email box directly, unless you are supplying private information you don't want public on the list)
Re: DBD::CSV and skip_first_line
On 11/26/2012 11:56 AM, H.Merijn Brand wrote:
> Can you attach the first 4 lines of your csv datafile?
Here is some randomized data that closely resembles the data in the csv
if this is any help in working with variations on
$dbh->prepare(q{Select key, PHM_ID, DAW_CD, GENBRND_CD from clms limit 10});
(bearing in mind the csv contains 44, not 4 columns and this is just a sample)
key|PHM_ID|DAW_CD|GENBRND_CD
667291120KNM4728|P1951532|2|0
858525298EEA3248|P8697017|5|0
286424010HTG2644|P8607393|3|1
344987842DYH2950|P8662248|3|0
225509049XEU3393|P1222508|1|0
061473729SFZ1183|P2785408|6|0
370501125YPF2594|P1534462|2|0
620354050CRF3119|P4438944|3|1
901228431AUF5822|P5315769|1|0
969358370QPO9757|P1523687|8|0
543692286WTA5861|P5993819|1|0
591327753QVR5452|P1013462|4|0
159204117LXL0308|P5358769|8|1
352853355KYT5615|P2810873|3|1
195099617GNE7056|P1306424|6|0
--
Scott R. Godin, Senior Programmer
MAD House Graphics
302.468.6230 - main office
302.722.5623 - home office
Re: DBD::CSV and skip_first_line
On 11/26/2012 11:56 AM, H.Merijn Brand wrote:
> On Mon, 26 Nov 2012 11:49:49 -0500, "Scott R. Godin"
> wrote:
>
> On 11/25/2012 04:16 AM, Jens Rehsack wrote:
>>> On 25.11.12 10:00, H.Merijn Brand wrote:
>>>> On Fri, 23 Nov 2012 17:43:50 -0500, "Scott R. Godin"
>>>> wrote:
>>>>> I've run into an issue where I need both col_names set and
>>>>> skip_first_line still set to TRUE, because of malformed colnames in the
>>>>> original dumpfiles that conflict with SQL Reserved Words (such as
>>>>> 'key')
>>>>> that I am unable to find any other acceptable workaround short of
>>>> Why not automate the hacking using Text::CSV_XS and rewrite the header
>>>> before using DBD::CSV?
>>> Or simply quote the column names in your SQL statement?
>> I tried various quoting mechanisms up to and including attempting to use
>> backticks, but all result in errors of one kind or another
> Can you attach the first 4 lines of your csv datafile?
Unfortunately, no, as I am under HIPAA restrictions.
key consists of seemingly random alphanumeric [A-Z0-9] sequences that
may or may not contain one dash (at about position 11), of 16-char length
PHM_ID consists of P\d{7} and may repeat across records
of the other two fields DAW_CD is numeric(1) and GENBRND_CD is boolean
all records are pipe-delimited
The actual csv contains 44 columns; in the interest of brevity I limited
the sample to the below four. :)
>> $dbh->prepare(q{Select 'key', PHM_ID, DAW_CD, GENBRND_CD from clms limit
>> 10})
>> results in every record having the literal value "key" for the column `key`
>> same if I try select 'key' as PKEY
>>
>> if I switch to double-quotes rather than single quotes around "key" in
>> the above, I get the following error:
>> Execution ERROR: No such column '"key"' called from clms_test.pl at 23.
>>
>> I'll look into playing with Text::CSV_XS, and see what I can come up with.
>>
>> I still think it would be easier if skip_first_line were not presumed
>> (forced to) false if col_names is set, but rather presumed false only if
>> not set explicitly true.
> We agree, investigating what is actually required (and should be
> documented)
>
--
Scott R. Godin, Senior Programmer
MAD House Graphics
302.468.6230 - main office
302.722.5623 - home office
Re: DBD::CSV and skip_first_line
On 11/25/2012 04:16 AM, Jens Rehsack wrote:
> On 25.11.12 10:00, H.Merijn Brand wrote:
>> On Fri, 23 Nov 2012 17:43:50 -0500, "Scott R. Godin"
>> wrote:
>>
>>> I've run into an issue where I need both col_names set and
>>> skip_first_line still set to TRUE, because of malformed colnames in the
>>> original dumpfiles that conflict with SQL Reserved Words (such as
>>> 'key')
>>> that I am unable to find any other acceptable workaround short of
>>
>> Why not automate the hacking using Text::CSV_XS and rewrite the header
>> before using DBD::CSV?
>
> Or simply quote the column names in your SQL statement?
I tried various quoting mechanisms up to and including attempting to use
backticks, but all result in errors of one kind or another
$dbh->prepare(q{Select 'key', PHM_ID, DAW_CD, GENBRND_CD from clms limit
10})
results in every record having the literal value "key" for the column `key`
same if I try select 'key' as PKEY
if I switch to double-quotes rather than single quotes around "key" in
the above, I get the following error:
Execution ERROR: No such column '"key"' called from clms_test.pl at 23.
I'll look into playing with Text::CSV_XS, and see what I can come up with.
I still think it would be easier if skip_first_line were not presumed
(forced to) false if col_names is set, but rather presumed false only if
not set explicitly true.
--
Scott R. Godin, Senior Programmer
MAD House Graphics
302.468.6230 - main office
302.722.5623 - home office
Re: Perl DBI / SQL Question
Vance M. Allen wrote:
Sorry if the cross-posting wasn't appropriate, but I need help with this and
am not sure if it's more appropriate to post under CGI or DBI since it
involves both...I want to be sure that I can get help from the best source.
My question is probably a simple answer, but I am not sure what I have to do
and the books I have here are either not answering the question, or I'm not
finding the answer.
I need to know how to retrieve through Perl DBI a listing of possible ENUM
elements from a field for processing under a CGI script. If all I need for
this is some form of SELECT statement, please provide a code snippet of this
so I can do it.
I want to make my code so I'm not having to edit hard-coded Perl CGI scripts
if/when I add new elements to the ENUM field. Any help you can provide
would be greatly appreciated.
Thanks!
Vance
I did this exact same thing at some point in the past.. I should be able to dig
it up.. half a moment...
ahh here it is (I think). Let me know if this doesn't work for you. SET and ENUM
should be similar enough in this regard
# obtain values of SET columns live, rather than hard-code them into the script
# existing dbhandle object (not a further reference to one), table, and col name
sub get_column_values ($$$) {
my ($dbh1, $table, $column, @vals) = @_;
my $sth = $dbh1->column_info( undef, undef, $table, '%');
$sth->execute() or safe_error($dbh1->errstr); # errorsub defined elsewhere
while ( my $ref = $sth->fetchrow_hashref() )
{
#skip unless this is the column we want info for
next unless $ref->{COLUMN_NAME} eq $column;
@vals = @{$ref->{mysql_values}}; #save the column data
last; #there can be only one
}
$sth->finish();
return undef unless @vals;
# return either the data or a count of how much data
return wantarray ? @vals : scalar(@vals);
}
Also, Tim, you have my permission to fuse this or a cleaned up version of it
into the DBI docs somewhere if you feel it might be useful therein.
Re: DBI::Shell still failing to install under Fedora Core 1
Jeff Zucker wrote: >>>t/coredubious >>> >>Not one response since Feb 9th? what's going on here? >> > You mean the Feb. 9th that happend less than two weeks ago? Did you > submit a patch? If not, what is going on is probably that the module's > author a) has a life and/or b) is busy. :-) *chuckle* Well, I was basically hoping along the lines of someone else having had this problem and knowing what to do to fix it... >>Am I invisible? >> > Not to me. And good thing too. It's nice of you to report problems, I > know because you've helped me by doing so with several of my modules. Well that's good to know. I was hoping it wasn't the knode newsreader I've been using under linux (I'm too used to MT-Newswatcher on the Mac :) somehow karking up my news postings. At least I know now that I've gotten through. Hows everything going by the way, with AnyData, and the book related stuff ? >> How could I tell? :) >> > Look in a mirror? (note: may not provide conclusive evidence if you are > a witch). LOL .. wait, isn't that 'vampire' ?
Re: DBI::Shell still failing to install under Fedora Core 1
Scott R. Godin wrote: > t/coredubious > Test returned status 0 (wstat 139, 0x8b) > Scalar found where operator expected at (eval 153) line 1, near "'int' > $__val" > (Missing operator before $__val?) > after all the subtests completed successfully > t/funcskipped > all skipped: Function tests not completed > t/shell...ok > t/spool...ok > t/sqlminusok 106/109DBD::ExampleP::db prepare failed: Unknown field > names: yuck_error. > t/sqlminusok > t/timing..ok > Failed Test Stat Wstat Total Fail Failed List of Failed > - > t/core.t 0 139 90 0.00% ?? > 1 test skipped. > Failed 1/7 test scripts, 85.71% okay. 0/261 subtests failed, 100.00% okay. > make: *** [test_dynamic] Error 255 > /usr/bin/make test -- NOT OK > > This was previously reported here by me, failing under Red Hat 9, but no > followups were ever really made on this. I was hoping that upgrading would > resolve the issue, but that appears to have not been the case. Not one response since Feb 9th? what's going on here? Am I invisible? How could I tell? :)
DBI::Shell still failing to install under Fedora Core 1
cpan> test DBI::Shell Running test for module DBI::Shell Running make for T/TL/TLOWERY/DBI-Shell-11.93.tar.gz Fetching with LWP: http://cpan.develooper.com/authors/id/T/TL/TLOWERY/DBI-Shell-11.93.tar.gz Fetching with LWP: http://cpan.develooper.com/authors/id/T/TL/TLOWERY/CHECKSUMS Checksum for /root/.cpan/sources/authors/id/T/TL/TLOWERY/DBI-Shell-11.93.tar.gz ok DBI-Shell-11.93/ DBI-Shell-11.93/Changes DBI-Shell-11.93/MANIFEST DBI-Shell-11.93/META.yml DBI-Shell-11.93/Makefile.PL DBI-Shell-11.93/README DBI-Shell-11.93/ToDo DBI-Shell-11.93/dbish.PL DBI-Shell-11.93/dbish_config DBI-Shell-11.93/lib/ DBI-Shell-11.93/lib/DBI/ DBI-Shell-11.93/lib/DBI/Format/ DBI-Shell-11.93/lib/DBI/Format/SQLMinus.pm DBI-Shell-11.93/lib/DBI/Format.pm DBI-Shell-11.93/lib/DBI/Shell/ DBI-Shell-11.93/lib/DBI/Shell/Completion.pm DBI-Shell-11.93/lib/DBI/Shell/FindSqlFile.pm DBI-Shell-11.93/lib/DBI/Shell/SQLMinus.pm DBI-Shell-11.93/lib/DBI/Shell/Spool.pm DBI-Shell-11.93/lib/DBI/Shell/Timing.pm DBI-Shell-11.93/lib/DBI/Shell.pm DBI-Shell-11.93/t/ DBI-Shell-11.93/t/batch.t DBI-Shell-11.93/t/core.t DBI-Shell-11.93/t/func.t DBI-Shell-11.93/t/shell.t DBI-Shell-11.93/t/spool.t DBI-Shell-11.93/t/sqlminus.t DBI-Shell-11.93/t/timing.t DBI-Shell-11.93/testsql.sql DBI-Shell-11.93/testsqlminus.sql DBI-Shell-11.93/testtiming.sql CPAN.pm: Going to build T/TL/TLOWERY/DBI-Shell-11.93.tar.gz Checking if your kit is complete... Looks good Writing Makefile for DBI::Shell cp lib/DBI/Shell/FindSqlFile.pm blib/lib/DBI/Shell/FindSqlFile.pm cp lib/DBI/Shell/Timing.pm blib/lib/DBI/Shell/Timing.pm cp lib/DBI/Shell/Completion.pm blib/lib/DBI/Shell/Completion.pm cp lib/DBI/Shell/Spool.pm blib/lib/DBI/Shell/Spool.pm cp lib/DBI/Format.pm blib/lib/DBI/Format.pm cp lib/DBI/Format/SQLMinus.pm blib/lib/DBI/Format/SQLMinus.pm cp lib/DBI/Shell/SQLMinus.pm blib/lib/DBI/Shell/SQLMinus.pm cp lib/DBI/Shell.pm blib/lib/DBI/Shell.pm /usr/bin/perl "-Iblib/arch" "-Iblib/lib" dbish.PL dbish Extracted dbish from dbish.PL with variable substitutions. cp dbish blib/script/dbish /usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/dbish Manifying blib/man1/dbish.1 Manifying blib/man3/DBI::Shell.3pm Manifying blib/man3/DBI::Format.3pm Manifying blib/man3/DBI::Format::SQLMinus.3pm /usr/bin/make -- OK Running make test cp dbish blib/script/dbish /usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/dbish PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/batch...ok t/coredubious Test returned status 0 (wstat 139, 0x8b) Scalar found where operator expected at (eval 153) line 1, near "'int' $__val" (Missing operator before $__val?) after all the subtests completed successfully t/funcskipped all skipped: Function tests not completed t/shell...ok t/spool...ok t/sqlminusok 106/109DBD::ExampleP::db prepare failed: Unknown field names: yuck_error. t/sqlminusok t/timing..ok Failed Test Stat Wstat Total Fail Failed List of Failed - t/core.t 0 139 90 0.00% ?? 1 test skipped. Failed 1/7 test scripts, 85.71% okay. 0/261 subtests failed, 100.00% okay. make: *** [test_dynamic] Error 255 /usr/bin/make test -- NOT OK This was previously reported here by me, failing under Red Hat 9, but no followups were ever really made on this. I was hoping that upgrading would resolve the issue, but that appears to have not been the case.
Re: DBD module
Tim Bunce wrote: > On Thu, Nov 06, 2003 at 02:07:46PM -0500, [EMAIL PROTECTED] wrote: >> How do I determine what version of the DBD module is installed? > > With DBI 1.38 you can now do > > perl -MDBI -e 'DBI->installed_versions' > > and it'll tell you. > > Tim. Sweet! Thanks a lot, Tim, this is nice.
Re: DBD module
Michael A Chase wrote:
> On Thu, 6 Nov 2003 14:07:46 -0500 [EMAIL PROTECTED] wrote:
>
>> How do I determine what version of the DBD module is installed?
>
> There are several DBD::xyz modules. DBI has a method for finding them:
>
>perl -MDBI -e "print join qq(\n), DBI->available_drivers"
>
> You can usually find the version of a module with something like:
>
>perl -MDBD::xyz -e "print $DBD::xyz::VERSION" # UNIX
>perl -MDBD::xyz -e 'print $DBD::xyz::VERSION' # MSWin
>
I keep these in my ~/.bashrc (and something similar in my .tcshrc) for just
this purpose:
# User specific aliases and functions
pv () { perl -M$1 -le "print q{$1}->VERSION";}
m () { perl -MCPAN -e "print CPAN::Shell->format_result(q{Module}, q{$1})";}
so you can do
$> pv CGI
3.00
$> m CGI
CPAN: Storable loaded ok
Going to read /home/webdragon/.cpan/Metadata
Database was generated on Fri, 14 Nov 2003 15:51:31 GMT
Module id = CGI
CPAN_USERID LDS (Lincoln D. Stein <[EMAIL PROTECTED]>)
CPAN_VERSION 3.00
CPAN_FILEL/LD/LDS/CGI.pm-3.00.tar.gz
MANPAGE CGI - Simple Common Gateway Interface Class
INST_FILE/usr/lib/perl5/5.8.0/CGI.pm
INST_VERSION 3.00
$>
Hope people find these useful. :)
DBI::Shell producing errors during test on RedHat 9
cpan> install DBI::Shell Running install for module DBI::Shell Running make for T/TL/TLOWERY/DBI-Shell-11.93.tar.gz Is already unwrapped into directory /root/.cpan/build/DBI-Shell-11.93 CPAN.pm: Going to build T/TL/TLOWERY/DBI-Shell-11.93.tar.gz Checking if your kit is complete... Looks good Writing Makefile for DBI::Shell cp lib/DBI/Shell.pm blib/lib/DBI/Shell.pm cp lib/DBI/Format.pm blib/lib/DBI/Format.pm cp lib/DBI/Shell/Timing.pm blib/lib/DBI/Shell/Timing.pm cp lib/DBI/Shell/SQLMinus.pm blib/lib/DBI/Shell/SQLMinus.pm cp lib/DBI/Format/SQLMinus.pm blib/lib/DBI/Format/SQLMinus.pm cp lib/DBI/Shell/FindSqlFile.pm blib/lib/DBI/Shell/FindSqlFile.pm cp lib/DBI/Shell/Completion.pm blib/lib/DBI/Shell/Completion.pm cp lib/DBI/Shell/Spool.pm blib/lib/DBI/Shell/Spool.pm /usr/bin/perl "-Iblib/arch" "-Iblib/lib" dbish.PL dbish Extracted dbish from dbish.PL with variable substitutions. cp dbish blib/script/dbish /usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/dbish Manifying blib/man1/dbish.1 Manifying blib/man3/DBI::Shell.3pm Manifying blib/man3/DBI::Format.3pm Manifying blib/man3/DBI::Format::SQLMinus.3pm /usr/bin/make -- OK Running make test PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/batch...ok t/coredubious Test returned status 0 (wstat 139, 0x8b) Executing /usr/bin/gdb "/usr/bin/perl" "core" (bt)... GNU gdb Red Hat Linux (5.3post-0.20021129.18rh) Copyright 2003 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-redhat-linux-gnu"...(no debugging symbols found)... /root/.cpan/build/DBI-Shell-11.93/core: No such file or directory. (gdb) Hangup detected on fd 0 error detected on stdin after all the subtests completed successfully t/funcskipped all skipped: Function tests not completed t/shell...ok 32/51Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 361. t/shell...ok 35/51Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 361. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 364. t/shell...ok 38/51Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 361. t/shell...ok t/spool...ok t/sqlminusok 16/109Use of uninitialized value in array dereference at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/ExampleP.pm line 361. t/sqlminusok 20/109Use of uninitialized value in array dereference at /usr/lib/pe
Re: column_info, mysql, and SET/ENUM values
Tim Bunce wrote:
>> $ perl col_info.pl
>> Undefined subroutine &DBD::mysql::db::SQL_VARCHAR called at
>> /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/mysql.pm line
>> 337.
>>
>> which I don't quite understand. I tried adding :sql_types to the DBI
>> declaration, but no go.
>
> It's a minor bug in DBD::mysql. Try adding "use DBI qw(:sql_types);" just
> after "package DBD::mysql::db;" in DBD/mysql.pm
>
> Looks like it's not fixed in the DBD-mysql-2.9003_1 release either.
>
After doing the above.. =>
I haven't abstracted these away into object methods yet, as I'm not yet
fully comfortable with the whole object-oriented thing, and am not quite
sure of the best way to do this, however as a subroutine in my scripts this
should work fine.
Took a bit of doing, and fooling around with it to come up with these, and I
hope the documentation for these is updated soon. It wasn't immediately
obvious to me that column_info returns a DBI::st object that I had to
further handle with other additional DBI methods, for example. My first
attempt was just to read the values returned, thinking that $sth would be a
simple hashref. I was rapidly disabused of this notion, and came up with
the following. :)
(If anyone is willing to show me how I would take these subroutines and
convert them to subclassed DBI methods, I'd be interested in seeing how it
should be done properly.) :-)
#!/usr/bin/perl
# col_info.plx
# methods for obtaining values from SET or ENUM columns.
#
use warnings;
use strict;
use DBI;
my $dbh = DBI->connect(
'DBI:mysql:allclassics',
'webdragon',
'PASSWORD',
{RaiseError => 1}
)
or die $DBI::errstr;
END {undef $dbh if $dbh }
sub get_column_values ($$$) {# dbhandle, table name, and column name
my ($dbh1, $table, $column, @vals) = @_;
my $sth = $dbh1->column_info( undef, undef, $table, '%');
$sth->execute();
while ( my $ref = $sth->fetchrow_hashref() )
{
next unless $ref->{COLUMN_NAME} eq $column;
@vals = @{$ref->{mysql_values}};
}
$sth->finish();
return wantarray ? @vals : scalar(@vals);
}
sub get_value_hashref ($$) {# dbhandle, table name
my ($dbh1, $table, %hash) = @_;
my $sth = $dbh1->column_info(undef, undef, $table, '%');
$sth->execute();
while ( my $ref = $sth->fetchrow_hashref() )
{
next unless $ref->{TYPE_NAME} =~ /SET|ENUM/i;
$hash{ $ref->{COLUMN_NAME} }{type} = $ref->{TYPE_NAME};
$hash{ $ref->{COLUMN_NAME} }{'values'}
= [ @{$ref->{mysql_values}} ];
}
return \%hash;
}
## -=- Example one
my @default_keywords = sort(
get_column_values($dbh, 'products', 'keywords')
);
print join ", ", map { "'$_'" } @default_keywords;
print "\n\n";
## -=- Example two
my $ref = get_value_hashref($dbh, 'products');
foreach my $key (keys %$ref)
{
print "Column Name: $key - Column Type: $ref->{$key}{type}\n";
print join ", ", @{ $ref->{$key}{'values'} }, "\n\n";
}
## -=-
# other stuff that was just for testing purposes
# and to help me figure out how this column_info thing worked.
#
# next unless $ref->{COLUMN_NAME} eq $column;
# print all info returned by column_info() for a particular column
# foreach (sort keys %$ref)
# {
# print "$_ => ";
# print defined $ref->{$_} ? "$ref->{$_}\n" : "\n";
# }
Re: column_info, mysql, and SET/ENUM values
Tim Bunce wrote:
> I think the latest DBD::mysql contains my column info code and
> thus does return set & enum values.
>
> Tim.
>
> On Tue, Oct 14, 2003 at 09:01:14AM +1000, Ron Savage wrote:
>> On Mon, 13 Oct 2003 13:54:46 -0400, Scott R. Godin wrote:
>>
>> Hi Scott
>>
>> >?A while back in February, Tim and Ron were discussing DBD::mysql
>> >?and column_info..
>>
>> I'm still interested in info in this area.
>>
>> >?This is the only thing I could find that *may* relate to my
>> >?question, so I'm asking here for further clarification.
>>
>> This is the place.
>>
>> >?It's not clear in the docs (at least not to me) whether I can have
>> >?column_info return the possible values of a SET or ENUM column,
>> >?without hard-coding them into my scripts.
>>
>> I too would like to know that.
>>
Well I've tried several iterations of this, and the DBD::mysql and DBI
current releases don't accept a column value on
my $sth = $dbh->column_info( undef, $schema, $table, $column);
but instead require me to use '%'.
However this results in:
$ perl col_info.pl
Undefined subroutine &DBD::mysql::db::SQL_VARCHAR called at
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/mysql.pm line
337.
which I don't quite understand. I tried adding :sql_types to the DBI
declaration, but no go.
here's my test program: maybe you can point out what I'm doing wrong.
$ cat col_info.pl
#!/usr/bin/perl
use warnings;
use strict;
use DBI qw/:sql_types/;
my $dbh = DBI->connect('DBI:mysql:allclassics', 'webdragon', 'PASSWORD',
{RaiseError => 1})
or die $DBI::errstr;
my $sth = $dbh->column_info( undef, 'allclassics', 'products', '%') or die;
__END__
now I have managed to otherwise glean this info using the following program,
but I'd prefer to have a preset DBI method to do this with.
$ cat allclassics.pl
#!/usr/bin/perl
use warnings;
use strict;
use DBI qw/:sql_types/;
my $dbh = DBI->connect('DBI:mysql:allclassics', 'webdragon', 'PASSWORD',
{RaiseError => 1}) or die $DBI::errstr;
my $sth = $dbh->prepare('SHOW COLUMNS FROM products LIKE ?');
$sth->execute('keywords');
my $ref = $sth->fetchrow_hashref('NAME_lc');
my $value = $ref->{type};
$value =~ m#\((.*)\)#;
my @values = map { s/'//g; $_ } split /,/, $1;
$sth->finish();
use CGI qw/:standard/;
use CGI::Pretty qw/:html3/;
print start_html(),
scrolling_list(
-name=> "keywords",
-values=> [ sort @values ],
-size=>10,
-multiple=>'true',
),
end_html();
I'd be interested to know what I'm doing wrong in the earlier program, and
how I would go about asking for the values otherwise, as it's still not
very clear in the DBI docs.
just so it's easier to test, the SET declaration in the products table looks
like this:
keywords SET('', 'carving', 'fountain', 'children', 'child',
'animal', 'statue', 'statuette', 'tiki', 'mermaid', 'furniture',
'bookends', 'planter', 'coatrack', 'unbrella stand', 'totem pole', 'toy',
'rocker', 'plane', 'mounted head', 'buddha', 'standing', 'sitting',
'lying', 'trophy', 'desktop', 'horse', 'dolphin', 'indian', 'cigar',
'butler', 'waiter', 'cat', 'rooster', 'pig', 'dog', 'chicken', 'cow',
'small', 'medium', 'large', 'huge', 'man', 'woman', 'male', 'female',
'girl', 'boy')DEFAULT ''NOT NULL,
--scott
Re: MYSQL where clause case sensitive?
[EMAIL PROTECTED] wrote: > is MYSQL where clause case sensitive? > it looks like it is not > > redhat 9 > mysql 3.23.56 > > name='Bob' same as name='bob' > For case-sensitivity, use the BINARY cast operator: select * from $table WHERE name = BINARY 'Bob'; will only match if it's an exact case match.
column_info, mysql, and SET/ENUM values
A while back in February, Tim and Ron were discussing DBD::mysql and column_info.. This is the only thing I could find that *may* relate to my question, so I'm asking here for further clarification. It's not clear in the docs (at least not to me) whether I can have column_info return the possible values of a SET or ENUM column, without hard-coding them into my scripts. In other words I'd like my script to be able to tell when the values of the SET column have changed for the purposes of building an input form via CGI.pm to allow multiple selects from the input form, and to correctly display the current selection of SET items. Before I hare off and write a bunch of testing scripts, I'd just like to know if I'm on the right track. Will I be able to obtain this information, and is column_info the right way to do it?
Re: Odd behavior from DBD::CSV
Unknown Sender wrote:
> W li?cie z wto, 26-08-2003, godz. 18:33, Jeff Zucker pisze:
>> Well, I thought so, and that's why AnyData works that way. But CSV
>> worked with the "\015\012" default when I inherited it and there are too
>> many scripts out there based on that behaviour for me to contemplate
>> breaking backward compatibility.
>
> Maybe
>
> chomp $line;
> { local $/ = "\r"; chomp $line }
>
> Would help to remove both \015 and \012, or only \012, or only \015 from
> the end of a line?
>
> I use such construction in my scripts, because they can be 'fed' with
> files created in MS Win environment, as well as in Unix environment.
normally I use something along the lines of this:
s#\015\012|\015|\012#\n#
This ensures that whatever linefeeds the file contains are converted to
whatever your local system thinks of as 'normal' for \n.
Then you can simply set csv_eol to "\n", and worry less about what OS the
original .csv came from.
RE: make DBI-1.35 Fails on HP-UX 11i and Perl 5.8.0
Mkb wrote:
> #include
> int main() { exit(0); };
>
> gcc -o t.o -c t.c
> gives
> t.c:1:18: socket: No such file or directory. Not
> being a C programmer, I'm not sure how I'd get the
> compiler to include this header. Any ideas?
>
> The socket.h files are located here:
> /usr/conf/sys/socket.h
> /usr/include/sys/socket.h
>
> And BTW, gcc -v gives the following:
>
> Reading specs from
> /usr/local/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.2/specs
> Configured with: ./configure : (reconfigured)
> ./configure : (reconfigured) ./configure :
> (reconfigured) ./configure : (reconfigured)
> ./configure : (reconfigured) ./configure :
> (reconfigured) ./configure : (reconfigured)
> ./configure : (reconfigured) ./configure :
> (reconfigured) ./configure : (reconfigured)
> ./configure
> Thread model: posix
> gcc version 3.2
>
> thanks
>
> mohammed
>
I get this here:
2:27pm {29} pcp02404936pcs:/home/webdragon>$ gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix
--disable-checking --host=i386-redhat-linux --with-system-zlib
--enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
That whole (reconfigured) thing looks very bizarre and may indicate
something being wrong with your gcc. I don't know enough about it to say
what might be the case however.
RE: make DBI-1.35 Fails on HP-UX 11i and Perl 5.8.0
[EMAIL PROTECTED] wrote:
> This looks like your GCC can not parse /usr/include/socket.h
>
> Can you test that?
>
> cat > t.c < #include
> int main() { exit(0); }
> EOF
> gcc -o t.o -c t.c
>
posting this just for comparison purposes...
my copy of redhat 8 here doesn't have socket.h in /usr/include, however
there IS a socket.h in
/usr/include/sys/socket.h
/usr/include/asm/socket.h
/usr/include/linux/socket.h
/usr/include/bits/socket.h
I did a quick list of /usr/include and also discovered that my version of
gcc wouldn't compile it if I did
#include
(not even if I added -I/usr/include)
but WOULD work if I did
#include
Re: MySQL Select question.
Ron Stephan wrote: > The way I understand it, it will select all 100,000 and then try and > display them all. Between the server and the browser something is bound > to break or get stupefying slow. Am I wrong? Most of the time people > are going to have filter criteria in the display - maybe it doesn't > matter. But how do I mimic the "show all" logic but only actually show > the first 200 records (and at the end allow a browse to continue through > the next 200 records? > > > > Is this a stupid question? not at all try the LIMIT clause of MySQL :)
Re: how to install DBI
Ron Savage wrote: >>>The other thing is, Tom Phoenix's script (not module), inside, from >>>CPAN, finds quite a few other modules not found by your script. >> >>Oh? Such as? This method is similar to ones used as example material >>(but expanded upon) in CPAN.pm. I'd be surprised that it doesn't >>find all the installed modules. Perhaps this is a bug in CPAN.pm ? >> >>Do you have a URL for this script ? I'd like to see what it does and >>how. You say it's on CPAN? If so I can probably find it. > > http://search.cpan.org/author/PHOENIX/ > I'll take a look at it and test the results and report back to you on what's apparently going on here. Everything installed on my system perl-wise is either part of the redhat 8 distribution, or has been installed/updated via CPAN. so, CPAN should technically know about all the modules currently installed. I don't suppose you could provide a list of which modules inside found that modulereport didn't, on your system? Thanks for the heads-up. I'll let you know what I can find out.
Re: how to install DBI
Ron Savage wrote:
> On Sun, 23 Mar 2003 16:37:43 -0500, Scott R. Godin wrote:
>>Ron Savage wrote:
>
> Hi Scott
>
>>Yes, unfortunately there's no default $ENV{TEMP} on unix..
>>
>>I've been considering using File::Temp, File::MkTemp, or IO::File
>>instead,
>>but just haven't gotten around to futzing with it. :-)
>
> OK
>
>>File::Spec would be the most portable way to specify the directory
>>path, but
>>that's as far as I've gotten. Originally I ported this from the
>>MacPerl
>>version I started playing with the idea on, and then added the unix
>>/tmp
>>conventions to make certain things easier for myself.
>
> OK.
>
> The other thing is, Tom Phoenix's script (not module), inside, from
> CPAN, finds quite a few other modules not found by your script.
Oh? Such as? This method is similar to ones used as example material (but
expanded upon) in CPAN.pm. I'd be surprised that it doesn't find all the
installed modules. Perhaps this is a bug in CPAN.pm ?
Do you have a URL for this script ? I'd like to see what it does and how.
You say it's on CPAN? If so I can probably find it.
Re: how to install DBI
Ron Savage wrote:
> On Sat, 08 Mar 2003 22:28:09 -0500, Scott R. Godin wrote:
>
> Hi Scott
>
>>I've written a very nice module-reporting script that helps me to
>>stay on
>>top of installed modules and easily see when they require updates
>>from CPAN
>>for bugfixes.
>>
>>http://www.webdragon.net/mr/
>
> Great program!
>
> I made a small change to get it working under MS Windows:
> --- modulereport.pl Sun Mar 23 18:57:36 2003
> +++ \scripts\bin\modulereport.plSun Mar 23 18:57:32 2003
> @@ -46,9 +46,8 @@
> );
>
> my $storefile = File::Spec->catfile(
> -File::Spec->rootdir(),
> -'tmp',
> -"$ENV{USER}_modules.stor"
> +$ENV{'TEMP'},
> +"perl_modules.stor"
> );
>
> In other words:
> o I don't have $USER defined
> o I don't have /tmp
> o File::Spec -> rootdir() leads to /C:/tmp/..., which doesn't work
>
Yes, unfortunately there's no default $ENV{TEMP} on unix..
I've been considering using File::Temp, File::MkTemp, or IO::File instead,
but just haven't gotten around to futzing with it. :-)
File::Spec would be the most portable way to specify the directory path, but
that's as far as I've gotten. Originally I ported this from the MacPerl
version I started playing with the idea on, and then added the unix /tmp
conventions to make certain things easier for myself.
Re: Error installing DBD::mysql
Snowber Khan wrote: > [EMAIL PROTECTED] bugzilla]# perl -MCPAN -e 'force install "DBD::mysql"' > CPAN: Storable loaded ok > Going to read /root/.cpan/Metadata > Database was generated on Wed, 12 Mar 2003 15:42:32 GMT > Running install for module DBD::mysql > Running make for J/JW/JWIED/DBD-mysql-2.1026.tar.gz > > CPAN: MD5 security checks disabled because MD5 not installed. > Please consider installing the MD5 module. > Highly recommend cpan> install Digest::MD5 cpan> install MIME::Base64 before you go any further. [snip] > > CPAN.pm: Going to build J/JW/JWIED/DBD-mysql-2.1026.tar.gz > > I will use the following settings for compiling and testing: > > cflags(mysql_config) = -I'/usr/include/mysql' > libs (mysql_config) = -L/usr/lib/mysql -lmysqlclient -lz > -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss > _files -lnss_dns -lresolv > nocatchstderr (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'. > Here's your clue #1 To edit these settings and build manually, do a look DBD::mysql and follow the instructions in the commands given above. [snip] > /usr/bin/make -- OK This is good > Running make test > PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib > -I/usr/lib/perl5/5.6.1/i386-linux -I/usr/lib/perl5/5.6.1 -e 'use > Test::Harn ess qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t > t/00baseok t/10dsnlist.DBI->connect(test) failed: > Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) at > t/10dsnlist.t line > 45 > Cannot connect: Access denied for user: '[EMAIL PROTECTED]' (Using password: > NO) > Either your server is not up and running or you have no Here's your clue #2 > permissions for acessing the DSN DBI:mysql:test. > This test requires a running server and write permissions. > Please make sure your server is running and you have > permissions, then retry. > t/10dsnlist.dubious > Test returned status 10 (wstat 2560, 0xa00) > DIED. FAILED tests 1-9 > Failed 9/9 tests, 0.00% okay [snip] > make test had returned bad status, won't install without force This is bad. > [EMAIL PROTECTED] bugzilla]# Recommend making sure the mysql server is running before installing this module. If it is, then I recommend that you edit the parameters of the install process and run it manually while doing 'look DBD::mysql'.
Re: ls: *.jpg: No such file or directory
Mel Awaisi wrote:
> Hi
>
> My error is as the subject says, i have a script that i am trying in it to
> locate where a directory with images are.
>
> the part in the script that the error i think is arising from is
>
> my $dir = '/home/me/images/';
> my @jpegs = `ls *.jpg`;
> foreach (@jpegs) {
> print"$_";# dont forget that a newline is still at the end of each
> element in the array...
> }
>
>
> ls: *.jpg: No such file or directory
> ---
>
chdir '/home/me/images/' or die "Unable to chdir to /home/me/images: $!";
my @jpegs = glob "*.jpg";
print "$_\n" for @jpegs;
Re: how to install DBI
Mel Awaisi wrote:
> Hi
>
> i am using Red Hat Linux 8 as a server for a project i am working on.
> i have installed MySQL automatically with the install of the OS. and then
> i installed Apache Server.
>
> i would like to install DBI in order for me to be able to use a script
> that i have in perl to insert data into MySQL databse.
>
> What is the easiest and most understandable way to install it?
>
> Regards,
>
> Mel
Two ways. Start with the DBI provided by Red Hat.
(rpm -qa |grep perl)
You will need the following:
perl-5.8.0-55
perl-DBD-MySQL-2.1017-3
perl-DBI-1.30-1
You could also add:
perl-DB_File-1.804-55
...if you were interested in working with DB_File type databases as well.
I'd also recommend installing:
perl-CPAN-1.61-55
...as then you can upgrade the installed modules to more recent counterparts
install all of the above with the following
(up2date -i perl-DBD-MySQL perl-DBI perl-DB_File perl-CPAN)
{
Additionally I'd recommend making sure you have all the mysql stuff
installed..
(rpm -qa |grep -i mysql) should yield at least the following:
mysql-3.23.54a-4
mysql-devel-3.23.54a-4
mysql-server-3.23.54a-4
}
With CPAN installed, then you can do simple things like
#> perl -MCPAN -e shell
cpan> m DBI
{results}
cpan> install DBI
{many results}
cpan> m DBD::mysql
{results}
cpan> install DBD::mysql
{many results}
cpan>
etc
I've written a very nice module-reporting script that helps me to stay on
top of installed modules and easily see when they require updates from CPAN
for bugfixes.
http://www.webdragon.net/mr/
perldoc CPAN for more info on the CPAN shell.
Re: DBD::CSV
Jeff Zucker wrote:
> Tim Bunce wrote:
>
>>On Mon, Mar 03, 2003 at 10:07:29AM -0800, Jeff Zucker wrote:
>>
>>>use SQL::Statement;
>>> print $SQL::Statement::VERSION;
>>>
>>
>>Or run this command
>>
>>perl -MSQL::Statement=
>>
>
> Hmm, what am I missing? That doesn't work for me with SQL::Statement.
> It also doesn't work for me with DBD::ODBC, DBD::ADO, DBD::CSV although
> it does work with DBI and with DBD::Pg. What do I need to do in the
> module to get this to work?
>
I also occasionally use the syntax
perl -MModule::Name -le 'print Module::Name->VERSION'
2:35pm {20} pcp02404936pcs:/home/webdragon>$ perl -MCGI -MSQL::Statement -le
'print CGI->VERSION; print SQL::Statement->VERSION'
2.91
1.005
usually this works very well.
Re: Installation problems on OS X 10.2.3
Curt Russell Crandall wrote: > In case anyone cares. > > It appears the threaded version of Perl 5.8.0 on OSX is incompatible with > DBI. This really bites since you need the threaded version for Apache > 2.0.43 and mod_perl 1.99_07. So, on OSX, you are probably better off > using FastCGI or, I hate to say, Java Servlets/JSP for dynamic web pages > since not having DBI available pretty much makes mod_perl worthless... at > least for me. > > The distro of Perl I'm using on my OSX box is from serverlogistics.com > (aaronfaby.com... there's a link next to Rendezvous on Safari). Since > Perl 5.8 is binary incompatible with 5.6.x, you have to recompile all of > the XS mods (i.e. Storable)... but with the pkg from serverlogistics, > that's all taken care off. > > I'm unsure if there are similar incompatibilities with threaded Perl5.8 > and DBI on Linux... I'll try it out later today. My main Linux box runs > Perl5.8 non-threaded and DBI-1.31 works fine. > > Hopefully, this incompatiblity with threaded Perl 5.8 and DBI gets > resolved soon... otherwise Java is looking more like the route to go on > OSX... and I really really hate Java. > > Curt RedHat 8.0 uses threaded perl 5.8.0 as well, and the current DBI installed just fine here..
Re: problems building AnyData.pm on Red Hat 7.2/Perl 5.6.1
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Jeff Zucker) wrote: > "Scott R. Godin" wrote: > > > > The weird thing is, the same problem occurs even if I push some other > > test in front of the XML test. =:o (tried that last night) > > Scott, there are problems between XML::Twig's file locking and AnyData's > file locking so nothing you do will get the tests to work on Linux. > Ignore the tests and install anyway. If you need XML support, follow my > advice in the previous mail about changing the vaule of HAS_FLOCK in > AnyData::Format::File. perhaps I wasn't clear -- the same problem occurred at the same place in the test regardless of which test it was that came first.. in the recent case I got : AnyData HTMLtable ... ... and that was it... and in this case it was NOT XML, that was failing. I did install anyway, FWIW. print pack "H*", "4a75737420416e6f74686572204d61635065726c204861636b65722c0d"; -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/ It is not necessary to cc: me via e-mail unless you mean to speak off-group. I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
SQL::Statement
Jeff Zucker's newer versions of SQL::Statement do not show up when you use CPAN.pm's shell to m SQL::Statement if one doesn't know of the newer version, one might be inclined to believe it doesn't exist. just FYI print pack "H*", "4a75737420416e6f74686572204d61635065726c204861636b65722c0d"; -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/ It is not necessary to cc: me via e-mail unless you mean to speak off-group. I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
Re: DBI newbie
In article <061601c1f511$cf6ab2d0$05c3eed4@asarianhost>, [EMAIL PROTECTED] (Mark) wrote: > Hello, > > Just branching into the whole MySQL thing. :) > > Does anyone know a good place for examples on how to use DBI.pm? > > Thanks. > > - Mark > > I found having this handy to be VERY useful: http://www.mysql.com/Downloads/Manual/manual.pdf also used the excellent Pod::Pdf to convert the pod docs from DBD::mysql to the lovely output format of Pod::Pdf and didn't regret having THAT handy either :) print pack "H*", "4a75737420416e6f74686572204d61635065726c204861636b65722c0d"; -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/ It is not necessary to cc: me via e-mail unless you mean to speak off-group. I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
Re: problems building AnyData.pm on Red Hat 7.2/Perl 5.6.1
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Jeff Zucker) wrote: > "Scott R. Godin" wrote: > > > > my e-mail below isn't working, and Jeff's mailserver is rejecting mail > > from my comcast.net domain (my ISP) *sigh* > > Strange. I have some other email addresses but am traveling at the > moment and can't get to them. I guess use the list for now. > > > At any rate, I've been trying to build AnyData.pm (and then move on to > > DBD::AnyData from there) on this Red Hat Linux box I have here.. I've > > updated to Perl 5.6.1, and DO have XML::Parser, XML::Twig, Text::Iconv, > > HTML::TableExtract, HTML::Parser, HTML::Tree, and WeakRef installed. > > > > when running make test, I simply get the following: > > > > AnyData > > > >XML ... > > Ignore the test problem and install it. Everything but XML should > work. If you need XML, change the line in AnyData::Storage::File that > sets the constant for USE_FLOCK and change it to 0. This will mean that > file locking won't work for you so you'll either have to do it manually > or wait for the next release. > > > Jeff I'd welcome some dialog with you on this, via the list. I LOVE > > AnyData (particularly useful on MacPerl :) and am frustrated that I > > can't also use it on the Linux box at the moment. > > If you still have problems after my suggestion, let me know. The weird thing is, the same problem occurs even if I push some other test in front of the XML test. =:o (tried that last night) changed unshift @formats, 'XML' unless $@; to push ... and then 'unshifted' HTMLtable in front. same problem. test just comes to a complete halt, cpu-wise with no explanations. print pack "H*", "4a75737420416e6f74686572204d61635065726c204861636b65722c0d"; -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/ It is not necessary to cc: me via e-mail unless you mean to speak off-group. I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
problems building AnyData.pm on Red Hat 7.2/Perl 5.6.1
my e-mail below isn't working, and Jeff's mailserver is rejecting mail from my comcast.net domain (my ISP) *sigh* At any rate, I've been trying to build AnyData.pm (and then move on to DBD::AnyData from there) on this Red Hat Linux box I have here.. I've updated to Perl 5.6.1, and DO have XML::Parser, XML::Twig, Text::Iconv, HTML::TableExtract, HTML::Parser, HTML::Tree, and WeakRef installed. when running make test, I simply get the following: AnyData XML ... and it just sits there.. top reports that it's using 0% cpu at this point, so I don't quite know what's holding things up. Jeff I'd welcome some dialog with you on this, via the list. I LOVE AnyData (particularly useful on MacPerl :) and am frustrated that I can't also use it on the Linux box at the moment. print pack "H*", "4a75737420416e6f74686572204d61635065726c204861636b65722c0d"; -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/ It is not necessary to cc: me via e-mail unless you mean to speak off-group. I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
Re: inserting into CLOB field
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Wolfgang Weisselberg) wrote: > > Hi, I am new to this newsgroup, so I hope this is not a bad question. > > It's a good question, but I thought it was a mailing list :-) actually it's both.. point your news reader at nntp://nntp.perl.org :) -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/ It is not necessary to cc: me via e-mail unless you mean to speak off-group. I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
Re: bind_param question
In article
<[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Brad Watts) wrote:
> print header;
> start_html("ATT Canada - (NCMRS) Network Capacity Management Report
> Server");
that should be
print header(),
start_html("ATT Canada - (NCMRS) Network Capacity Management
Report Server");
> #
> # Define and Call Select Statement For First Screen #
> #
>
> my $dbh =
> DBI->connect("DBI:mysql:password=bash111:user=stats;database=CAPACITY",
> {'RaiseError' => 1});
>
> my $sth_1 = $dbh->prepare("SELECT FORE_ADDRESS_T.CITY CITY
> ,FORE_ADDRESS_T.REAL_LOCATION ADDRESS
> ,COUNT(DISTINCT FORE_HARD_WARE_T.IP_ADDRESS) TOTAL_BOXES
> ,COUNT(DISTINCT FORE_HARD_WARE_T.SLOT_NUM) TOTAL_SLOTS
> ,COUNT(FORE_HARD_WARE_T.PORT_NUM) TOTAL_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'up\',1,0)) AS UP_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'down\',1,0)) AS DOWN_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'standby\',1,0)) AS
> STANDBY_PORTS
> ,SUM(IF(FORE_HARD_WARE_T.STATE = \'\',1,0)) AS NO_STATUS_PORTS
> FROM FORE_ADDRESS_T INNER JOIN FORE_HARD_WARE_T USING (IP_ADDRESS)
> WHERE FORE_ADDRESS_T.IP_ADDRESS = FORE_HARD_WARE_T.IP_ADDRESS
> AND FORE_ADDRESS_T.CITY <> \'UNKNOWN\'
> AND FORE_HARD_WARE_T.DATE = " . $dbh->quote( $sql_date ) . "
> AND FORE_ADDRESS_T.CITY = " . $dbh->quote( $sql_city ) . "
> # AND FORE_ADDRESS_T.PROVINCE = ?
> AND FORE_ADDRESS_T.REAL_LOCATION LIKE " . $dbh->quote( "%$sql_location%" )
> . "
> GROUP BY FORE_ADDRESS_T.CITY, FORE_ADDRESS_T.REAL_LOCATION") or die
> "Couldn't prepare statement: " . $dbh->errstr;
each of those $dbh->quote( ) parts can be replaced with a ? thusly,
(although I haven't parsed the rest of your SQL for correctness)
my $sth_1 = $dbh->prepare("SELECT FORE_ADDRESS_T.CITY CITY
,FORE_ADDRESS_T.REAL_LOCATION ADDRESS
,COUNT(DISTINCT FORE_HARD_WARE_T.IP_ADDRESS) TOTAL_BOXES
,COUNT(DISTINCT FORE_HARD_WARE_T.SLOT_NUM) TOTAL_SLOTS
,COUNT(FORE_HARD_WARE_T.PORT_NUM) TOTAL_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'up\',1,0)) AS UP_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'down\',1,0)) AS DOWN_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'standby\',1,0)) AS
STANDBY_PORTS
,SUM(IF(FORE_HARD_WARE_T.STATE = \'\',1,0)) AS NO_STATUS_PORTS
FROM FORE_ADDRESS_T INNER JOIN FORE_HARD_WARE_T USING (IP_ADDRESS)
WHERE FORE_ADDRESS_T.IP_ADDRESS = FORE_HARD_WARE_T.IP_ADDRESS
AND FORE_ADDRESS_T.CITY <> \'UNKNOWN\'
AND FORE_HARD_WARE_T.DATE = ?
AND FORE_ADDRESS_T.CITY = ?
AND FORE_ADDRESS_T.REAL_LOCATION LIKE '%?%'
GROUP BY FORE_ADDRESS_T.CITY, FORE_ADDRESS_T.REAL_LOCATION")
or die("Couldn't prepare statement: ", $dbh->errstr);
and then do
> my @data;
>
> #
> # Decide Whether or not to Execute Select Statement #
> #
>
> if ( defined $sql_date && $sql_date =~ /\d+\-\d+\-\d+/ ) {
> my @data;
# it wil call quote() on these automatically if you do it this way :)
$sth_1->execute($sql_date, $sql_city, $sql_location)
or die "Couldn't execute statement:" . $sth_1->errstr;
> }
>
> if ( defined $sql_date and ! defined $sth_1->fetchrow_array()) {
> print "Sorry, I was unable to
> process your request. Please try again.. \n"
> ;
> }
I don't believe you can use a block-level header like H3 in a table
caption. (and you also never close the Center tag, which has been
deprecated anyway, since html 3.2 years ago)
You might be better off setting this with a stylesheet and importing it
in your start_html() like this
# in CSS never set color without also setting background color --
# user's stylesheets may override otherwise.
my $stylesheet=<<"EOS";
caption {background: white; color: #3366ff; font-size: large ;}
EOS
my $pagetitle = 'ATT Canada - (NCMRS) Network Capacity Management Report
Server';
print header(),
start_html({-'style'=>{-'code'=>$style}, -title=>$pagetitle});
and later...
print caption("Sorry, I was unable to process your request. Please try
again.. "), "\n";
Much neater eh?
:-)
if you want to have a caption that's differently colored than normal for
the error you can do
caption.error { background: white; color: #3366ff; font-size: large ; }
in the stylesheet and then
print caption({-class=>'error'}, "Sorry, I was unable to process your
request. Please try again.. "), "\n";
HTH
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
It is not necessary to cc: me via e-mail unless you mean to speak off-group.
I read these via nntp.perl.org, so as to get the stuff OUT of my mailbox. :-)
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Michael Peppler) wrote:
> > my $rows = $sth->rows;
> > # only expecting one row for a unique ID . this should NEVER happen.
> >safe_error("invalid number of rows returned from database ($rows) for
> > ID $id")
> > if $rows > 1;
> > # although this might...
> > safe_error("no match in database for ID $id")
> > if $rows < 1;
>
> Be careful here!
>
> Most DBI drivers will return -1 for $sth->rows() for a SELECT query.
=:o
> In the case of DBD::Sybase $h->rows() will return the correct number
> of rows only *after* all the rows have been fetched.
I guess I'm fortunate that DBD::'s CSV, AnyData, and mysql all work this
way.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Tim Bunce) wrote:
> On Fri, Nov 02, 2001 at 02:18:15PM +0100, Bart Lateur wrote:
> > On Fri, 02 Nov 2001 07:27:49 -0500, Scott R. Godin wrote:
> >
> > >my %db;
> > >$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
> > >
> > >while ($sth->fetch)
> > >{
> > >#... and no worries about which order the columns get returned in
> > >#... since you access them via the $db{ColumnName} method :)
> >
> > What's the advantage of this approach over
> >
> > while(my $db = fetchrow_hashref) {
> > ...
> > }
> >
> > and accessing the datae through $db->{ColumnName}?
>
> Speed! It's many times faster (assuming the loop is empty :)
>
> (But use $sth->{NAME_lc} or $sth->{NAME_uc} for portability.
>
> Tim.
with the exception of my case where neither mod_perl nor Apache::DBI is
compiled in..
in the php vs perl thread earlier this (last?) month, I posted some
"benchmarks" done by the site admin on a search of 5100 rows for "c" by
their ph script and my perl script.. the results were staggeringly
different, even with the help of this (see the script I posted in that
thread for details on what I was doing) the thread issues are posted
here: <[EMAIL PROTECTED]>
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBI 1.15+ establishes multiple connections under parent mod_perl process
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Tim Bunce) wrote: > > There were connect() changes made between DBI 1.14 and 1.15 but I'd need > people to look into it for me. Should be trivial to debug by enabling > DBI tracing and Apache::DBi debug. Unless your admin refuses to run any of the mod_perl and Apache::DBI stuff compiled in, because he's a php freak and thinks mod_perl is a resource pig. :\ -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBI 1.15+ establishes multiple connections under parent mod_perl process
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Eric Kolve) wrote:
> I have traced it back to prepare_cached() (at least that is what I
> notice).
>
> Scott, try replacing your calls on startup with prepare() instead of
> prepare_cached().
no, I'm using prepare(). an earlier post thread of mine (php vs perl)
has a copy of the script I'm running in it.
> I was also able to eliminate the problem if I commented out the
> following line in DBI.pm
>
> # $dbh->STORE('CachedKids', $cache = {}) unless $cache; # line 1021
> sub prepare_cached
I haven't tried this though
> Of course this is not a solution, but it may give someone else with more
> knowledge enough to fix the problem. I will keep digging for answers.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBI 1.15+ establishes multiple connections under parent mod_perl process
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Eric Kolve) wrote: > I think I have found a curious bug in DBI. It seems that since DBI 1.15 > - 1.20, when you bring up apache/mod_perl and execute queries against > the database handle in the parent process (startup.pl), multiple > connections result against the database. If I switch to DBI 1.14, no > such problem occurs. I have found this problem occurs with: > > DBI 1.20 + DBD::Oracle 1.12 > DBI 1.15 + DBD::Oracle 1.07 > DBI 1.16 + DBD::Oracle 1.07 > > > I have turned on Apache::DBI::DEBUG and trace(2) in DBI. Could someone > tell me what I should be looking for or can someone else shed any light > on this? I am not sure if this is necessarily a mod_perl issue or if > mod_perl is just eliciting a bug in DBI. > > thanks, > > --eric I've noticed this too, and it has *seriosly* damaged any credibility I might have gained with the admin I'm up against who is a major PHP proponent, and who refused to even think about installing mod_perl to help the script along after he saw this. :/ -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: Column Names
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Bart Lateur) wrote:
> On Thu, 01 Nov 2001 18:56:18 -0800, Venkataramana Mokkapati wrote:
>
> >How do I get column names and order of column names
> >for a "select * from ..." query.
>
> If you have
>
> $sth = $dbh->prepare("select * from ...");
>
> then try
>
> @column names = @{$sth->{NAME}};
>
> You may have to do an "execute" first, for this to return anything of
> value.
>
> It's in the DBI docs under the heading "Statement Handle Attributes", in
> the DBI POD formatted as text around line 2284.
the absolute neatest trick I've seen with this, that is so totally
perlish it defies description.. you stare at it for a bit and suddenly
all becomes clear.
$sth->execute
or die("Cannot Execute SQL Statement: ", $sth->errstr(), "\n");
my $rows = $sth->rows;
# only expecting one row for a unique ID . this should NEVER happen.
safe_error("invalid number of rows returned from database ($rows) for
ID $id")
if $rows > 1;
# although this might...
safe_error("no match in database for ID $id")
if $rows < 1;
my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic
while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: DBD::CSV incorrect detection of numeric fields, patch?
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Sam Roberts) wrote:
> I'm sorry its taken me so long to get back to you. I lost your
> patch, hacked something that worked for my particular problem,
> and then got pulled into other things.
>
> However, I'm back. I'm having the problem with DBD:CSV, wherein things
> like DISTINCT and SORT don't work:
>
[snip]
> ~/w/svv/tools/dbtools $ perl db-tool export "select distinct date from
> svv_bug_count"
> 2001/09/18 11:25
I know that in MySQL you can do something along the lines of
SELECT date FROM svv_bug_count ORDER BY date DESC LIMIT 1
which will return a descending list of dates from the most recent on
down, and limit the return to a single value :)
I don't know if DBD::CSV will do it, but you *could* try Jeff Zucker's
DBD::AnyData.pm and AnyData.pm modules, which are the replacement for
the old DBD::CSV/DBD::RAM modules. I DO know that there were some things
that DBD::CSV was choking on that DBD::AnyData was able to do admirably
on MacPerl (setting a different csv_eol for example :)
> Can you send me what I need (a newer SQL::Statement, at least) to try
> your newest stuff, and I'll do so Monday, and get back to you right away
> (this time) on how it works.
>
> Thanks a lot, sorry I appeared to drop off the earth.
>
> Sam
>
> p.s. Totally offtopic: I'm an SQL newbie, do you happen to know an SQL
> query that will get me the latest date? I was going to do a
> sort|uniq|head on "select date" (the perl equiv, that is) to get the
> latest date for which data exists, but was wondering if there was a
> purely SQL way. The sort and uniq is supported by SQL, but getting only
> the single greates valued item?
see above :)
> p.p.s. Is there any kind of special date handling in SQL::Statement?
> I've chosen "/mm/dd hh:mm" (with 24 hour hh) because it a textual
> sort is equivalent to a date sort, but it would be nice if there was
> a way to treat a field as a date, and do things like:
this is *very* close to the formatting used by MySQL for it's DATETIME
field, which is "-mm-dd HH:MM:SS"...
> select distinct year-month-day(date) from a_table
>
> so that the SQL engine new that the data field was date data, and just
> returned the day-month-year part, stripping out the timeof day part.
>
> Not SQLs problem, or just not supported by DBD::CSV?
with the proper formatting (and yours should suffice even if you didn't
want to convert it to the standard MySQL DATETIME format) you should be
able to extract it with a simple regex, since you KNOW the format that
you can expect to be returned.
With DBD::mysql I'd do: (I don't offhand know whether it supports an
extraction but I suppose it does.. for the sake of argument lets assume
it doesn't (cuz I'm too tired to check, and it's likely that CSV or
AnyData don't anyway) and that we want a regex to extract the return
value (and that RaiseError => 1 is set))
my $sth = $dbh->prepare("SELECT date FROM svv_bug_count ORDER BY date
DESC LIMIT 1");
$sth->execute();
$sth->bind_columns( \($date) );
while ( $sth->fetch() )
{
my($yyymmdd, $hhmmss) = split / /, $date;
my($, $mm, $dd) = split m|/|, $mmdd; # or split /-/, etc.
print "Year: $, Month: $mm, Day, $dd\n";
}
$sth->finish if $sth;
$dbh->disconnect();
does that help?
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: php vs perl again (reposted - original post was incomplete)
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Scott R. Godin) wrote: > > Well post the script and we can look. Please eliminate the parts that are > > not relevant if you script is big. > > it's about 255 lines of code incuding comments.. I'll remove the > comments from the file to shorten it a bit. The linewrapping will suck > but I'll try and clean it up a bit. just following up to my previous reply since I haven't seen any further follow-up regarding the code I posted, and wondering if it got misplaced... ? -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: php vs perl again (reposted - original post was incomplete)
r( td({-colspan=>3, -align=>'center', }, b("No Match
Found") ));
return '';
}
print Tr( td({-colspan=>3, -align=>'center', }, b("Found $rowcount
matches") ));
my( $type, $id, $filename, $title, $size, $reviewfile, $rating,
$rated, $oldtype );
$sth->bind_columns(\$type, \$id, \$filename, \$title, \$size,
\$reviewfile, \$rating);
while ( $sth->fetch )
{
# in english: If oldrating is empty, or different from the
# previous rating AND the rating is now < 0
if ( !defined($rated) or ($rated != $rating and $rating < 0) )
{
# then check to see whether we're rated or unrated and print
# an appropriate header for that section
if ($rating < 0)
{
print Tr( td({-colspan=>3, -align=>'center', }, b(
u("Unrated Maps")) )),
Tr(
th({-align=>"center"}, "Map Name"),
th({-align=>"right"}, "Size"),
th({-align=>"center"}, "Rating"),
);
$oldtype = -1; #re-set oldtype ;)
}
else
{
print Tr( td({-colspan=>3, -align=>'center', }, b(
u("Rated Maps")) )),
Tr(
th({-align=>"center"}, "Map Name"),
th({-align=>"right"}, "Size"),
th({-align=>"center"}, "Rating"),
);
};
};
# okay so we have nice section headers..
# how about some type section headers for the unrated section?
if ( $rating == -1 and $type != $oldtype )
{
print Tr( td({-colspan=>3, -align=>'center'},
$files_list{$type} ) );
}
$filename= unescapeHTML($filename);
print Tr(
td({-align=>"left", -valign=>"top"},
a({-href=>"${download_url}$types[${type}]/${filename}.zip",
-target=>"_new"}, $title ),
),
td({-align=>"right", -valign=>"top"},
size_calc($size),
),
td({-align=>"center", -valign=>"top",},
checkrating($rating, $reviewfile, $id),
),
), "\n";
# adjust loop vars for prettyprint
$rated = $rating;
$oldtype = $type;
};
die $sth->errstr if $sth->err;
}
my $search_obj = escapeHTML( param('searchfor') ) || '';
# un-taint the search object
$search_obj =~ m/([ a-zA-Z-_\[\]\{\}0-9]+)/;
$search_obj = $1;
if (!param() && cgi_error()) {
print header(-status=>cgi_error());
goto FINISH;# don't call exit 0; !!! (unless you LIKE killing your
perl process over and over, ass-hat) :P
}
my $expires = (localtime(time + 30));
print header({'head'=>meta( {-http_equiv=>'Expires', -content=>$expires
} )}),
start_html({-Title=>"FuzzBuster's NaliCity Quick Search!",
-Style=>{-Code=>$newStyle},
-bgcolor=>'#003366',
-text=>'white',
-"link"=>'#99',
-vlink=>'yellow'});
print start_form,
div({-align=>'center'},
h3("Map Search"),
p("Enter the name of a map title or file to search for:"),
textfield(-name=>'searchfor',
-default=>'',
-size=>30,
-maxlength=>68,
-override=>1),
br,
submit(-name=>'Submit', -value=>'Submit'),
),
end_form, hr;
if ( $search_obj eq '' )
{
# skip the database query
print end_html;
goto FINISH;
}
my $query = "SELECT Type, ID, FileName, Title, Size, ReviewFile,
ROUND(Rating, 2)
FROM $map_db
WHERE FileName LIKE '%$search_obj%' OR Title LIKE
'%$search_obj%'
ORDER BY Rating DESC, Type, FileName";
print start_div({-align=>"center"}),
start_table({-border=>"0", -cellpadding=>"0", -cellspacing=>"2",
-width=>"300"});
create_dbi_table($query);
print end_table,
end_div,
hr,
end_html;
FINISH:
# end of code
If anyone is interested I can provide particulars on the maps table in
the MySQL database as well.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: php vs perl again (reposted - original post was incomplete)
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Stephen Clouse) wrote: > Having said all that, is this really on topic for dbi-users? indeed it is, since the script in question is banking heavily upon DBI and DBD::mysql to do the work. -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: Unwanted error message with DBD::CSV
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Ilya Sterin) wrote: > As much as I shouldn't say this, but run the script without the -w to get > rid of this message, or define a __WARN__ handler. > > Ilya or use local $^W; within the loop -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
php vs perl again (reposted - original post was incomplete)
cess and CGI process (and DBI process) like you DO have a > persistent php and php-with-mysql process, causing perl and CGI and DBI to > re-execute and recompile themselves each time. (what kind of results DID you > expect doing something like this? =:P) > > C> The accuracy of the returned result of the query as performed by each > script is also in question. (look at the result count reported by each script > as to how many maps it returned from the query, and tell me something's not > wrong with one of them. =:P) > > try again. -=- here's the "benchmark" result he returned to me We benched your script using ApacheBench. We let it run with 100 concurrent connections. A couple of things happened. 1. MySQL died with a "too many connections" error. Our forums, with 150 users on them, can't even do that. 2. The load average on the box jumped to 11. Not 1 or 2. 11. 3. Yoda has written a PHP search engine which already incorporates all of the advanced features for NC. His script ran 229 times faster than yours, and the load average never moved above 0.5. MySQL was also perfectly fine, after being benched under the same conditions. I strongly recommend at this point that you do not use Perl for Nalicity. I have included the results of our benchmarks (performed by Chris), so you can see for yourself. This sort of load would be unacceptible in the BU environment. Begin ab log: Yoda's version: [root@beyondunreal bin]# ./ab -n100 -c10 -k http://nalicity.beyondunreal.com/testbed/news2.php?executesearch=1&search by_titles=on&tSearchText=c&sortby=2&sorttype=1 This is ApacheBench, Version 1.3c <$Revision: 1.45 $> apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2000 The Apache Group, http://www.apache.org/ Server Software:Apache/1.3.22 Server Hostname:nalicity.beyondunreal.com Server Port:80 Document Path: /testbed/news2.php?executesearch=1 Document Length:769 bytes Concurrency Level: 10 Time taken for tests: 0.574 seconds Complete requests: 100 Failed requests:0 Keep-Alive requests:0 Total transferred: 97206 bytes HTML transferred: 78438 bytes Requests per second:174.22 Transfer rate: 169.35 kb/s received Connnection Times (ms) min avg max Connect:1 517 Processing:2044 244 Total: 2149261 Fuzzbuster's version: [root@beyondunreal bin]# ./ab -n100 -c10 -k http://nalicity.beyondunreal.com/cgi-bin/simplesearch.cgi?searchfor=c This is ApacheBench, Version 1.3c <$Revision: 1.45 $> apache-1.3 Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ Copyright (c) 1998-2000 The Apache Group, http://www.apache.org/ Server Software:Apache/1.3.22 Server Hostname:nalicity.beyondunreal.com Server Port:80 Document Path: /cgi-bin/simplesearch.cgi?searchfor=c Document Length:698276 bytes Concurrency Level: 10 Time taken for tests: 115.382 seconds Complete requests: 100 Failed requests:0 Keep-Alive requests:0 Total transferred: 71242100 bytes HTML transferred: 71215208 bytes Requests per second:0.87 Transfer rate: 617.45 kb/s received Connnection Times (ms) min avg max Connect:1 122 3017 Processing: 9771 11087 11502 Total: 9772 11209 14519 I'm willing to post my script here to see if any of you individuals can tell me what, if anything, I did wrong with MY script that could have caused MySQL to die with "too many connections" or whether this is a problem with DBI and DBD::MySQL in its present form. I await your response. (with heavy sighs and a great deal of frustration) -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
php vs perl again
here's a missive fired off by the site admin after he "benchmarked" two
scripts, one written in php and one written in perl/cgi
>
> First of all.
>
> Dude. you're out of your mind. Im serious.
>
> The WHOLE point about why PHP is faster than Perl is because the
> interpreter is compiled into Apache.
he's not running a perl interpreter compiled into Apache.
> Also. the fact that your script caused MySQL to use up all of it's
> connections has -nothing- to do with PHP being compiled into Apache.
I find this terribly difficult to believe, but I'm willing to post my
cgi for review both here and in the DBI list
> We simply do -not- have the hardware to run mod_perl. With our level of
> traffic, we would need a load balanced cluster to handle this.
>
> We skewed nothing. We ran the same apache bench command for both
> scripts. Same number of concurrent requests, same number of times. Do not
> confuse ApacheBench with some useless little tool. This is for serious
> server benchmarking. The fact that we're using gemini table types and
> your database tables are indexed just further shows the limitations of
> Perl.
>
> You simply don't get it. PHP, in all of it's forms, blows perl out of the
> water. I've been writing both since early 1993, and in every case, in
> every instance, PHP crushes perl for speed. That's -why- it was created
> (build in interpreter). That's -why- Zend released the PHP4 engine.
> That's -why- we're running Zend Optimizer. If perl was the shit for doing
> CGI, why would anyone even bother creating things like PHP? That's like
> the Chewbacca website. It makes no sense.
>
> mod_perl is a resource pig. I refuse to install something on a server that
> will make life miserable for everyone else. I've seen GHz machines hauled
> off of their foundations because of mod_perl, while the same server
> running PHP code has no problems whatsoever.
I responded with certain information along these lines:
-=-
> > If you use CGI.pm in many of your mod_perl scripts, you may want to preload
> > CGI.pm and its methods at server startup time. To do this,
> > add the following line to httpd.conf:
> >
> > PerlScript /home/httpd/conf/startup.pl
> >
> > Create the file /home/httpd/conf/startup.pl and put in it all the modules
> > you
> > want to load. Include CGI.pm among them and call its
> > compile() method to precompile its autoloaded methods.
> >
> > #!/usr/local/bin/perl
> >
> > use CGI ();
> > CGI->compile(':all');
> >
> > Change the path to the startup script according to your preferences.
>
> if you're gonna benchmark at least do it right.
>
> don't flap statistics at my face when you've got sandbags tied around the
> feet of all my peasants, and shot each one in the foot as well, please.
>
> Also, Yoda's script is not performing (and from what I can see, can not
> perform ) the same query mine was (again skewing the 'benchmark')
>
> searching his script for 'c' does not return even the same list of maps mine
> does. I feel that a certain degree of *accuracy* is also important in a
> benchmark.
>
> I've also gone to the trouble of doing things such as this:
>
> my( $type, $id, $filename, $title, $size, $reviewfile, $rating, $rated,
> $oldtype );
> $sth->bind_columns(\$type, \$id, \$filename, \$title, \$size,
> \$reviewfile, \$rating);
>
> which binds the results of each return into the same variable reference to
> save on memory and processing while looping through the fetch, instead of
> thrashing the symbol table.
>
> and other things like this
>
> # die with status error if necessary if cgi itself got an error
> if (!param() && cgi_error()) {
> print header(-status=>cgi_error());
> goto FINISH;# don't call ex
status of DBD::mysql ?
I notice from the docs that come with DBD-mysql-2.0902, and in particular the Makefile.PL there is the warning: print <<"MSG"; This is an experimental version of DBD::mysql. For production environments you should prefer the Msql-Mysql-modules. MSG ...and I notice that the last update was in may of this year.. is this still a truism, and is Jochen still updating and maintaining the module? I haven't seen him posting anywhere recently, in the places I normally read up on Perl and DBI, so I'm not certain whether he is still actively working on the module. Can anyone clue me in? -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: CGIwrap?
I just love how some people's reply-ing to posted messages on the list forces their new topic under the old thread via the references, so if you get bored with a post, and mark the thread as read, it skips all the new posts with new subjects below it that got trapped by the thread references, Usually resulting in their never recieving replies to their missive. Far better to post to the list from a new message and add the mailing list address to your addressbook instead. just my $.02 -- Scott R. Godin| e-mail : [EMAIL PROTECTED] Laughing Dragon Services |web : http://www.webdragon.net/
Re: [repost] DBD::CSV and csv_eol=anything
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Jeff Zucker) wrote:
> "Scott R. Godin" wrote:
> >
> > unable to set ;csv_eol=\015, but saving the file via bbedit to DOS
> > instead of Macintosh, the code works?!? what the hell?
>
> AFAIK,
>
> 1. If you are on a MAC and have all MAC-formatted files, don't set
> csv_eol at all, DBD::CSV should do the right thing.
unfortunately this is not the case, as in CSV.pm there is the following
line:
$opts{'eol'} = $meta->{'eol'} || $dbh->{'csv_eol'} || "\015\012";
so, for a Mac file I need to set it explicitly.
> 2. If you are on a MAC and have all DOS formatted files, set csv_eol to
> \015\012.
Since this is the default (see above) it isn't necessary. As long as the
file has DOS linefeeds, setting to \015\012 or not setting this at all
results in usable data access.
> 3. If you are on a MAC and have mixed files
>
>a. convert them all to one format before processing (recommended)
>
>or
>
>b. for the files that you know are DOS formatted, set csv_eol to
> \015\012 and
> do not set it all for the MAC formatted files
>
> > All well and good, except that the file is generated by a whole suite of
> > perl scripts
>
> So use the scripts to put a MAC eol in the file and forget about csv_eol
> in the report generating scripts.
Well the issue here is one of independance between Mac and Unix -- if
the file is generated on the Mac, I should be able to set csv_eol to
\015 and have DBI 'do the right thing', likewise with unix setting
csv_eol to \012
now, if DBI is creating the table, all is well and good -- the present
setup creates files with \015\012 and as long as you DO NOT set csv_eol,
everything is fine. It's when you are creating the "database files" with
other applications and expect it (DBI and DBD::CSV) to do what it says
it's supposed to, that things go flooey.
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
[repost] DBD::CSV and csv_eol=anything
the list mysteriously and completely disappeared for roughly 10 minutes,
and I wasn't sure if my post was received or not. *head-scratching*
-=-
unable to set ;csv_eol=\015, but saving the file via bbedit to DOS
instead of Macintosh, the code works?!? what the hell?
setting csv_eol=\015 when file format is saved as Macintosh results in
this error, searching for "test":
DBD::CSV::st execute failed: Missing first row at Primus
8.5GB:Applications:MacPerl 5.6.1a4 ΓΌ:site_perl:DBD:CSV.pm line 157,
line 1.
o if fileformat is DOS and csv_eol=\015, it searches the file but
finds 0 results
o if fileformat is DOS and I SET csv_eol=\015\012 it FINDS THE 7
RESULTS PROPERLY O_o
i.e. it's only confused SOMEtimes. Somewhere, it's assuming the file is
a DOS file, even though I'm setting csv_eol properly, the question is
WHERE?
my $dbh =
DBI->connect("dbi:CSV:f_dir=${dbi_connect_dir};csv_sep_char=\t;csv_eol=\0
15\012", '', '', { RaiseError => 2 })
or die "Can't connect to database: $DBI::errstr";
All well and good, except that the file is generated by a whole suite of
perl scripts running under MacPerl 5.6.1a*, (a mysql table output by SQL
embedded in an .asp page, formatted to an html table, parsed by
HTML::Parser, and tested for integrity by script # 3) and is read for
report-generation by something like 10 other scripts.
I don't want to go back through all the scripts and change the input and
output record separators unless I absolutely have to, considering one
single csv_eol=\015 is supposed to solve the problem :)
DBI 1.20
DBD::CSV 0.1027
SQL::Statement 0.1020
Text::CSV_XS 0.22
MacPerl 5.6.1a4
I can post the complete script and a smidge of sample data if you wish
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: Exporting Data
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] wrote:
> print FILE @rows . '\t;
why didn't you try
print FILE join("\t", @rows), "\n";
?
of course the dump method mentioned in the prev post is a good idea too
;-)
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
