RE: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6
I'm using: DBD::mysql 3.007 DBI 1.52 Perl 5.8.8 That output looks like missing shared library dependencies to me, but maybe someone else has a better idea. Try: Ldconfig -p | grep mysql And see if you see something like: libmysqlclient.so (libc6,x86-64) => /usr/lib64/libmysqlclient.so in the output. If not you may have to find it and make sure ldconfig knows about it. You should also be able to use ldd to check shared lib dependencies. Regards, Gavin Towey -Original Message- From: Hagen Finley [mailto:finha...@comcast.net] Sent: Monday, October 05, 2009 1:30 PM To: Gavin Towey Cc: mysql@lists.mysql.com Subject: Re: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6 Here is the error I am receiving ( I posted this issue in the recent past): dyld: lazy symbol binding failed: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace dyld: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace Trace/BPT trap Could you send me the versions of Perl, DBD & DBI you are using? Hagen On 10/5/09 1:16 PM, "Gavin Towey" wrote: > MySQL Server can be 32 or 64 bit and it shouldn't make a difference to PERL. > However, if you can run 64 bit, you should. > > PERL and it's modules all need to be the same architecture. It doesn't matter > if they're 32 bit or 64 bit, as long as it's consistent with itself. > > I see no reason why those versions should have a problem interacting; what are > you trying, and what is the error you get? > > Regards > Gavin Towey > > -Original Message- > From: Hagen [mailto:finha...@comcast.net] > Sent: Monday, October 05, 2009 11:58 AM > To: mysql@lists.mysql.com > Cc: finha...@comcast.net > Subject: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6 > > After installing Apple's MAC Snow Leopard OS 10.6 I haven't been able to get > my DBD:mysql module to work. I am kind of beyond trying to fix that issue > directly so I am requesting assistance defining a compatible version set which > is known to work. Between various releases and 32/64 bit options I have to > confess there are too many choices and not enough information on how to choose > for me to trust my own judgment anymore. > > Currently, I am running: > > 1. mysql Server Version 5.4.1-beta MySQL Community Server > > 2. perl version 5.8.9 built for Darwin 2level > > 3. perl DBI version 1.609 > > 4. perl DBD:mysql 4.012 > > I am not positive which of the above are 32 or 64 bit but help discerning > which 32/64 versions are required would also be appreciated. I am happy ;-( to > uninstall and reinstall any of the above to get to a working configuration. > Thanks in advance. > > Hagen Finley > Boulder, CO > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com > > > The information contained in this transmission may contain privileged and > confidential information. It is intended only for the use of the person(s) > named above. If you are not the intended recipient, you are hereby notified > that any review, dissemination, distribution or duplication of this > communication is strictly prohibited. If you are not the intended recipient, > please contact the sender by reply email and destroy all copies of the > original message. The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6
Here is the error I am receiving ( I posted this issue in the recent past): dyld: lazy symbol binding failed: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace dyld: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace Trace/BPT trap Could you send me the versions of Perl, DBD & DBI you are using? Hagen On 10/5/09 1:16 PM, "Gavin Towey" wrote: > MySQL Server can be 32 or 64 bit and it shouldn't make a difference to PERL. > However, if you can run 64 bit, you should. > > PERL and it's modules all need to be the same architecture. It doesn't matter > if they're 32 bit or 64 bit, as long as it's consistent with itself. > > I see no reason why those versions should have a problem interacting; what are > you trying, and what is the error you get? > > Regards > Gavin Towey > > -Original Message- > From: Hagen [mailto:finha...@comcast.net] > Sent: Monday, October 05, 2009 11:58 AM > To: mysql@lists.mysql.com > Cc: finha...@comcast.net > Subject: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6 > > After installing Apple's MAC Snow Leopard OS 10.6 I haven't been able to get > my DBD:mysql module to work. I am kind of beyond trying to fix that issue > directly so I am requesting assistance defining a compatible version set which > is known to work. Between various releases and 32/64 bit options I have to > confess there are too many choices and not enough information on how to choose > for me to trust my own judgment anymore. > > Currently, I am running: > > 1. mysql Server Version 5.4.1-beta MySQL Community Server > > 2. perl version 5.8.9 built for Darwin 2level > > 3. perl DBI version 1.609 > > 4. perl DBD:mysql 4.012 > > I am not positive which of the above are 32 or 64 bit but help discerning > which 32/64 versions are required would also be appreciated. I am happy ;-( to > uninstall and reinstall any of the above to get to a working configuration. > Thanks in advance. > > Hagen Finley > Boulder, CO > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com > > > The information contained in this transmission may contain privileged and > confidential information. It is intended only for the use of the person(s) > named above. If you are not the intended recipient, you are hereby notified > that any review, dissemination, distribution or duplication of this > communication is strictly prohibited. If you are not the intended recipient, > please contact the sender by reply email and destroy all copies of the > original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6
MySQL Server can be 32 or 64 bit and it shouldn't make a difference to PERL. However, if you can run 64 bit, you should. PERL and it's modules all need to be the same architecture. It doesn't matter if they're 32 bit or 64 bit, as long as it's consistent with itself. I see no reason why those versions should have a problem interacting; what are you trying, and what is the error you get? Regards Gavin Towey -Original Message- From: Hagen [mailto:finha...@comcast.net] Sent: Monday, October 05, 2009 11:58 AM To: mysql@lists.mysql.com Cc: finha...@comcast.net Subject: Mysql Perl DBI DBD Version Compatability for MAC OS 10.6 After installing Apple's MAC Snow Leopard OS 10.6 I haven't been able to get my DBD:mysql module to work. I am kind of beyond trying to fix that issue directly so I am requesting assistance defining a compatible version set which is known to work. Between various releases and 32/64 bit options I have to confess there are too many choices and not enough information on how to choose for me to trust my own judgment anymore. Currently, I am running: 1. mysql Server Version 5.4.1-beta MySQL Community Server 2. perl version 5.8.9 built for Darwin 2level 3. perl DBI version 1.609 4. perl DBD:mysql 4.012 I am not positive which of the above are 32 or 64 bit but help discerning which 32/64 versions are required would also be appreciated. I am happy ;-( to uninstall and reinstall any of the above to get to a working configuration. Thanks in advance. Hagen Finley Boulder, CO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Mysql Perl DBI DBD Version Compatability for MAC OS 10.6
After installing Apple's MAC Snow Leopard OS 10.6 I haven't been able to get my DBD:mysql module to work. I am kind of beyond trying to fix that issue directly so I am requesting assistance defining a compatible version set which is known to work. Between various releases and 32/64 bit options I have to confess there are too many choices and not enough information on how to choose for me to trust my own judgment anymore. Currently, I am running: 1. mysql Server Version 5.4.1-beta MySQL Community Server 2. perl version 5.8.9 built for Darwin 2level 3. perl DBI version 1.609 4. perl DBD:mysql 4.012 I am not positive which of the above are 32 or 64 bit but help discerning which 32/64 versions are required would also be appreciated. I am happy ;-( to uninstall and reinstall any of the above to get to a working configuration. Thanks in advance. Hagen Finley Boulder, CO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: perl DBI does not do execute mysql statement
Baron Schwartz wrote: Kemin Zhou wrote: Hi All, I have one of the toughest problem to solve now. I have not been able to sleep well in the last three days. There is a simple query insert into table foo (column1, column2, column5) select $col from bar If I run this query by hand whatever the value of $col, it works fine. The insertion was done. If I run this query with other values of $col this sql works fine if $col is not name. If $col eq "name" then the insertion does not happen and no error message is given. (Of course, if I run the same query by hand, then this works fine). I would first get some sleep, then enable DBI tracing and turn on the MySQL general query log and see what query is really getting sent to the server. Baron Hi Baron, Best advice. I forgot about this one. I fixed the bug but I don't know how. I randomly changed form feature of the connection of the DBI object. Before this I experimented the ` quote symbol it did not help. To figure out what had caused the problem I need to ask the sysadmin for this. I was working on a database that I don't have easy access of the log files. Thanks for the help. Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl DBI does not do execute mysql statement
Kemin Zhou wrote: Hi All, I have one of the toughest problem to solve now. I have not been able to sleep well in the last three days. There is a simple query insert into table foo (column1, column2, column5) select $col from bar If I run this query by hand whatever the value of $col, it works fine. The insertion was done. If I run this query with other values of $col this sql works fine if $col is not name. If $col eq "name" then the insertion does not happen and no error message is given. (Of course, if I run the same query by hand, then this works fine). I would first get some sleep, then enable DBI tracing and turn on the MySQL general query log and see what query is really getting sent to the server. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl DBI does not do execute mysql statement
At 6:54 PM -0700 6/9/07, Kemin Zhou wrote: Hi All, I have one of the toughest problem to solve now. I have not been able to sleep well in the last three days. There is a simple query insert into table foo (column1, column2, column5) select $col from bar If I run this query by hand whatever the value of $col, it works fine. The insertion was done. If I run this query with other values of $col this sql works fine if $col is not name. If $col eq "name" then the insertion does not happen and no error message is given. (Of course, if I run the same query by hand, then this works fine). Not sure how crazy this could be. Any clue? No, because you're describing the problem in English without showing any of the code that doesn't work. How can anyone help you? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
perl DBI does not do execute mysql statement
Hi All, I have one of the toughest problem to solve now. I have not been able to sleep well in the last three days. There is a simple query insert into table foo (column1, column2, column5) select $col from bar If I run this query by hand whatever the value of $col, it works fine. The insertion was done. If I run this query with other values of $col this sql works fine if $col is not name. If $col eq "name" then the insertion does not happen and no error message is given. (Of course, if I run the same query by hand, then this works fine). Not sure how crazy this could be. Any clue? Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl/dbi - insert into server2.db2.table2 select * from server1.db1.table1
| Hi All, | | I have 2 separate mysql servers and need to import data from a table on | sever1 to | a table on server2. This would need to be done in Perl. | | The script in question already has open handles to both servers, so I | know I can | select all the rows from server1 and process them one by one and insert | into the table on server2, but I was wondering if there was a more | simple way to do this which would allow me to transfer the data from one | database handle directly to another? | | Looking on google, the closest example I can find is something like : | | #!/usr/bin/perl | use DBI; | | $dbh1=.; | $dbh2=.; | ... | | $statement = "select a,b,c,d,... from table1 where condition='$value'"; | $sth=$dbh1->prepare($sql); | my @results; | while (@results = $sth->fetchrow_array) { | | # build placeholders based on num of fields | my $placeholders; | $placeholders .= ($placeholders ? ",?" : "?") for (@results); | | my $sth2 = $dbh2->prepare("INSERT INTO table2 values | ($placeholders);"); | $sth2->execute(@results); | $sth2->finish; | } | | $sth1->finish; | $dbh1->disconnect(); | $dbh2->disconnect(); | | George Law | [EMAIL PROTECTED] | MSN: [EMAIL PROTECTED] | Phone: 864-678-3161 George, Did you try to "INSERT INTO db2.tablename SELECT fields from db1.tablename where field='Somevalue'" across different servers? Don't know if it's possible but it would certainly be more efficient. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl/dbi - insert into server2.db2.table2 select * from server1.db1.table1
George, that's probably about the easiest way you could do it in perl. If you want every column transferred you could do a "SELECT *" instead of enumerating columns I think. Dan On 9/29/06, George Law <[EMAIL PROTECTED]> wrote: Hi All, I have 2 separate mysql servers and need to import data from a table on sever1 to a table on server2. This would need to be done in Perl. The script in question already has open handles to both servers, so I know I can select all the rows from server1 and process them one by one and insert into the table on server2, but I was wondering if there was a more simple way to do this which would allow me to transfer the data from one database handle directly to another? Looking on google, the closest example I can find is something like : #!/usr/bin/perl use DBI; $dbh1=.; $dbh2=.; ... $statement = "select a,b,c,d,... from table1 where condition='$value'"; $sth=$dbh1->prepare($sql); my @results; while (@results = $sth->fetchrow_array) { # build placeholders based on num of fields my $placeholders; $placeholders .= ($placeholders ? ",?" : "?") for (@results); my $sth2 = $dbh2->prepare("INSERT INTO table2 values ($placeholders);"); $sth2->execute(@results); $sth2->finish; } $sth1->finish; $dbh1->disconnect(); $dbh2->disconnect(); George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
perl/dbi - insert into server2.db2.table2 select * from server1.db1.table1
Hi All, I have 2 separate mysql servers and need to import data from a table on sever1 to a table on server2. This would need to be done in Perl. The script in question already has open handles to both servers, so I know I can select all the rows from server1 and process them one by one and insert into the table on server2, but I was wondering if there was a more simple way to do this which would allow me to transfer the data from one database handle directly to another? Looking on google, the closest example I can find is something like : #!/usr/bin/perl use DBI; $dbh1=.; $dbh2=.; ... $statement = "select a,b,c,d,... from table1 where condition='$value'"; $sth=$dbh1->prepare($sql); my @results; while (@results = $sth->fetchrow_array) { # build placeholders based on num of fields my $placeholders; $placeholders .= ($placeholders ? ",?" : "?") for (@results); my $sth2 = $dbh2->prepare("INSERT INTO table2 values ($placeholders);"); $sth2->execute(@results); $sth2->finish; } $sth1->finish; $dbh1->disconnect(); $dbh2->disconnect(); George Law [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] Phone: 864-678-3161 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dependency issues: coreutils and perl(DBI)
David Blake wrote: I am attempting to install MySQL-server-5.0.18-0.glibc23 via rpm. I am getting this message: warning: MySQL-server-5.0.18-0.glibc23.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: coreutils is needed by MySQL-server-5.0.18-0.glibc23 perl(DBI) is needed by MySQL-server-5.0.18-0.glibc23 perl(DBI) is needed by MySQL-client-5.0.18-0.glibc23 I have downloaded coreutils-5.92.tar, but its contents sould be in /bin already and I tried to verify DBI: [prompt] perl -M'DBI 999' -e1 DBI version 999 required--this is only version 1.50. BEGIN failed--compilation aborted. so I have DBI installed. Any suggestions? Maybe you've installed coreutils and DBI via a non-rpm method, and haven't told the rpm database what's happened. You can either tell the rpm database that you have these packages already, or you can force an install without checking for dependancies. I haven't used rpm in over 5 years, so I can't really give you any specifics, but 'man rpm' will help. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dependency issues: coreutils and perl(DBI)
I am attempting to install MySQL-server-5.0.18-0.glibc23 via rpm. I am getting this message: warning: MySQL-server-5.0.18-0.glibc23.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: coreutils is needed by MySQL-server-5.0.18-0.glibc23 perl(DBI) is needed by MySQL-server-5.0.18-0.glibc23 perl(DBI) is needed by MySQL-client-5.0.18-0.glibc23 I have downloaded coreutils-5.92.tar, but its contents sould be in /bin already and I tried to verify DBI: [prompt] perl -M'DBI 999' -e1 DBI version 999 required--this is only version 1.50. BEGIN failed--compilation aborted. so I have DBI installed. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install trouble, perl DBI
For the t/40 error, you can install the missing module which appears to be missing. There is also a Bundle::DBI module from CPAN which will install the DBI and dependent modules. Alfred Lewis Ashley Foster wrote: Ok, i had a look at the perl DBI installations and tried "make test"i think this explains a thing or two! Both 1.32 and 1.49 fail massively it seems. I'll go for version 1.32 so the output for the make test on 1.32 is below. I seriously have no idea where to start with this lot, t/40 seems to be the first error, cant find Time/HiRes.pmthen later on 56/56 tests failed...how can it go so wrong? Any ideas how I can begin to sort this out? Please let me know if i should post this on a perl mailing list instead. Thanks for any pointers, Lewis. [EMAIL PROTECTED] DBI-1.32]# make test PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/01basics.ok t/02dbidrv.ok t/03hleak..ok t/04mods...ok t/05thrclone...ok t/06attrs..ok t/07kids...ok t/10examp..ok t/15array..ok t/20meta...ok t/30subclass...ok t/40profileCan't locate Time/HiRes.pm in @INC (@INC contains: /opt/www0/src/DBI-1.32/blib/lib /opt/www0/src/DBI-1.32/blib/arch /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 . /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 .) at t/40profile.t line 183. BEGIN failed--compilation aborted at t/40profile.t line 183. Use of uninitialized value in unlink at t/40profile.t line 35. t/40profiledubious Test returned status 2 (wstat 512, 0x200) DIED. FAILED tests 1-56 Failed 56/56 tests, 0.00% okay t/41prof_dump..ok t/42prof_data..ok t/60preparse...ok t/70shell..ok t/80proxy..skipped all skipped: modules required for proxy are probably not installed t/zz_01basics_pp... Using DBI::PurePerl (DBI_PUREPERL=2) t/zz_01basics_pp...ok t/zz_02dbidrv_pp...ok t/zz_03hleak_ppok t/zz_04mods_pp.ok t/zz_05thrclone_pp.ok t/zz_06attrs_ppok t/zz_07kids_pp.skipped all skipped: $h->{Kids} attribute not supported for DBI::PurePerl t/zz_10examp_ppok 48/245 Taint mode switching tests skipped t/zz_10examp_ppok 64/245 Taint attribute tests skipped t/zz_10examp_ppok t/zz_15array_ppok t/zz_20meta_pp.ok t/zz_30subclass_pp.ok t/zz_40profile_pp..skipped all skipped: profiling not supported for DBI::PurePerl t/zz_41prof_dump_ppskipped all skipped: profiling not supported for DBI::PurePerl t/zz_42prof_data_ppskipped all skipped: profiling not supported for DBI::PurePerl t/zz_60preparse_pp.skipped all skipped: preparse not supported for DBI::PurePerl t/zz_70shell_ppok t/zz_80proxy_ppskipped all skipped: modules required for proxy are probably not installed Failed Test Stat Wstat Total Fail Failed List of Failed --- t/40profile.t2 51256 56 100.00% 1-56 7 tests skipped. Failed 1/34 test scripts, 97.06% okay. 56/1148 subtests failed, 95.12% okay. make: *** [test_dynamic] Error 29 At 18:06 12/12/2005 +0200, Octavian Rasnita wrote: From: "Lewis Ashley Foster" <[EMAIL PROTECTED]> > > I'm trying to get mySQL installed on my machine but im having a bit of > trouble, obviously :) > > When i come to install mySQL server as below: > > rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm > > I get this result: > > warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: > NOKEY, key ID 5072e1f5 > error: Failed dependen
Re: install trouble, perl DBI
Hello. > the first error, cant find Time/HiRes.pmthen later on 56/56 tests Now it is rather a pure Perl problem than MySQL. However, have you installed Time/HiRes.pm? As far as I know, you can use 'force install ...' instead of just 'install' command in the cpan shell. This should continue the install even if few tests failed. Lewis Ashley Foster wrote: > > Ok, i had a look at the perl DBI installations and tried "make > test"i think this explains a thing or two! > > Both 1.32 and 1.49 fail massively it seems. I'll go for version 1.32 so > the output for the make test on 1.32 is below. > > I seriously have no idea where to start with this lot, t/40 seems to be > the first error, cant find Time/HiRes.pmthen later on 56/56 tests > failed...how can it go so wrong? > > Any ideas how I can begin to sort this out? Please let me know if i > should post this on a perl mailing list instead. > > Thanks for any pointers, > > Lewis. > > > [EMAIL PROTECTED] DBI-1.32]# make test > PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" > "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t > t/01basics.ok > t/02dbidrv.ok > t/03hleak..ok > t/04mods...ok > t/05thrclone...ok > t/06attrs..ok > t/07kids...ok > t/10examp..ok > t/15array..ok > t/20meta...ok > t/30subclass...ok > t/40profileCan't locate Time/HiRes.pm in @INC (@INC > contains: /opt/www0/src/DBI-1.32/blib/lib > /opt/www0/src/DBI-1.32/blib/arch > /usr/lib/perl5/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 > /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/site_perl/5.8.0 > /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl > /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/vendor_perl/5.8.0 > /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl > /usr/lib/perl5/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 . > /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 > /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl > /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi > /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl > /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 .) at > t/40profile.t line 183. > BEGIN failed--compilation aborted at t/40profile.t line 183. > Use of uninitialized value in unlink at t/40profile.t line 35. > t/40profiledubious > Test returned status 2 (wstat 512, 0x200) > DIED. FAILED tests 1-56 > Failed 56/56 tests, 0.00% okay > t/41prof_dump..ok > t/42prof_data..ok > t/60preparse...ok > t/70shell..ok > t/80proxy..skipped > all skipped: modules required for proxy are probably not installed > t/zz_01basics_pp... Using DBI::PurePerl (DBI_PUREPERL=2) > t/zz_01basics_pp...ok > t/zz_02dbidrv_pp...ok > t/zz_03hleak_ppok > t/zz_04mods_pp.ok > t/zz_05thrclone_pp.ok > t/zz_06attrs_ppok > t/zz_07kids_pp.skipped > all skipped: $h->{Kids} attribute not supported for DBI::PurePerl > t/zz_10examp_ppok 48/245 Taint mode switching tests skipped > t/zz_10examp_ppok 64/245 Taint attribute tests skipped > t/zz_10examp_ppok > t/zz_15array_ppok > t/zz_20meta_pp.ok > t/zz_30subclass_pp.ok > t/zz_40profile_pp..skipped > all skipped: profiling not supported for DBI::PurePerl > t/zz_41prof_dump_ppskipped > all skipped: profiling not supported for DBI::PurePerl > t/zz_42prof_data_ppskipped > all skipped: profiling not supported for DBI::PurePerl > t/zz_60preparse_pp.skipped > all skipped: preparse not supported for DBI::PurePerl > t/zz_70shell_ppok > t/zz_80proxy_ppskipped > all skipped: modules required for proxy are probably not installed > Failed Test Stat Wstat Total Fail Failed List of Failed > ---
Re: install trouble, perl DBI
Ok, i had a look at the perl DBI installations and tried "make test"i think this explains a thing or two! Both 1.32 and 1.49 fail massively it seems. I'll go for version 1.32 so the output for the make test on 1.32 is below. I seriously have no idea where to start with this lot, t/40 seems to be the first error, cant find Time/HiRes.pmthen later on 56/56 tests failed...how can it go so wrong? Any ideas how I can begin to sort this out? Please let me know if i should post this on a perl mailing list instead. Thanks for any pointers, Lewis. [EMAIL PROTECTED] DBI-1.32]# make test PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/01basics.ok t/02dbidrv.ok t/03hleak..ok t/04mods...ok t/05thrclone...ok t/06attrs..ok t/07kids...ok t/10examp..ok t/15array..ok t/20meta...ok t/30subclass...ok t/40profileCan't locate Time/HiRes.pm in @INC (@INC contains: /opt/www0/src/DBI-1.32/blib/lib /opt/www0/src/DBI-1.32/blib/arch /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 . /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 .) at t/40profile.t line 183. BEGIN failed--compilation aborted at t/40profile.t line 183. Use of uninitialized value in unlink at t/40profile.t line 35. t/40profiledubious Test returned status 2 (wstat 512, 0x200) DIED. FAILED tests 1-56 Failed 56/56 tests, 0.00% okay t/41prof_dump..ok t/42prof_data..ok t/60preparse...ok t/70shell..ok t/80proxy..skipped all skipped: modules required for proxy are probably not installed t/zz_01basics_pp... Using DBI::PurePerl (DBI_PUREPERL=2) t/zz_01basics_pp...ok t/zz_02dbidrv_pp...ok t/zz_03hleak_ppok t/zz_04mods_pp.ok t/zz_05thrclone_pp.ok t/zz_06attrs_ppok t/zz_07kids_pp.skipped all skipped: $h->{Kids} attribute not supported for DBI::PurePerl t/zz_10examp_ppok 48/245 Taint mode switching tests skipped t/zz_10examp_ppok 64/245 Taint attribute tests skipped t/zz_10examp_ppok t/zz_15array_ppok t/zz_20meta_pp.ok t/zz_30subclass_pp.ok t/zz_40profile_pp..skipped all skipped: profiling not supported for DBI::PurePerl t/zz_41prof_dump_ppskipped all skipped: profiling not supported for DBI::PurePerl t/zz_42prof_data_ppskipped all skipped: profiling not supported for DBI::PurePerl t/zz_60preparse_pp.skipped all skipped: preparse not supported for DBI::PurePerl t/zz_70shell_ppok t/zz_80proxy_ppskipped all skipped: modules required for proxy are probably not installed Failed Test Stat Wstat Total Fail Failed List of Failed --- t/40profile.t2 51256 56 100.00% 1-56 7 tests skipped. Failed 1/34 test scripts, 97.06% okay. 56/1148 subtests failed, 95.12% okay. make: *** [test_dynamic] Error 29 At 18:06 12/12/2005 +0200, Octavian Rasnita wrote: From: "Lewis Ashley Foster" <[EMAIL PROTECTED]> > > I'm trying to get mySQL installed on my machine but im having a bit of > trouble, obviously :) > > When i come to install mySQL server as below: > > rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm > > I get this result: > > warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: > NOKEY, key ID 5072e1f5 > error: Failed dependencies: > perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3 > Suggested resolutions: > perl-DBI-1.32-5.i386.rpm > > But I have already installed perl dbi 1.40 like this with no errors:
Re: install trouble, perl DBI
Lewis, As you already have perl installed, an easier way of getting modules is by way of CPAN (www.cpan.org). Give this a try, as superuser: cpan install DBI Regards, Imran -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail & More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is the perl DBI for version 5 mysql
Hello. Follow instructions from: http://dev.mysql.com/doc/refman/5.0/en/perl-support.html Logg, Connie A. wrote: > I have done various searches, and cannot locate the perl dbi for mysql = > 5.0.16. > > I assume that the old one for mysql 4 will not work. > > Can someone please provide me the url for mysql 5's perl dbd/dbi bundle? > > Thanks, Connie > Connie Logg, Network Analyst > Stanford Linear Accelerator Center > ph: 650-926-2879=20 > "Happiness is found along the way, not at the end of the road, and 'IF' = > is the middle word in life." > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where is the perl DBI for version 5 mysql
I have done various searches, and cannot locate the perl dbi for mysql 5.0.16. I assume that the old one for mysql 4 will not work. Can someone please provide me the url for mysql 5's perl dbd/dbi bundle? Thanks, Connie Connie Logg, Network Analyst Stanford Linear Accelerator Center ph: 650-926-2879 "Happiness is found along the way, not at the end of the road, and 'IF' is the middle word in life." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install trouble, perl DBI
Hello. > rpm -ivh perl-DBI-1.40-5.src.rpm This rpm installs only the source of perl-DBI, install a binary rpm for your architecture. You might need to use --force option for rpm command in case you have a fresher working version of perl-DBI. Lewis Ashley Foster wrote: > > I'm trying to get mySQL installed on my machine but im having a bit of > trouble, obviously :) > > When i come to install mySQL server as below: > > rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm > > I get this result: > > warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA > signature: NOKEY, key ID 5072e1f5 > error: Failed dependencies: > perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3 > Suggested resolutions: > perl-DBI-1.32-5.i386.rpm > > But I have already installed perl dbi 1.40 like this with no errors: > > rpm -ivh perl-DBI-1.40-5.src.rpm > > I really cant think how to get round this, unless i downgrade the dbi to > 1.32 but that seems daft. > > Any ideas would be greatly appreciated. > > Lewis. > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install trouble, perl DBI
On Mon, 2005-12-12 at 15:20 +, Lewis Ashley Foster wrote: > > warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: > NOKEY, key ID 5072e1f5 > error: Failed dependencies: > perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3 > Suggested resolutions: > perl-DBI-1.32-5.i386.rpm > > But I have already installed perl dbi 1.40 like this with no errors: > > rpm -ivh perl-DBI-1.40-5.src.rpm You're installing the source RPM, which you would then have to build and install yourself. Try installing the perl-DBI RPM without src in the filename. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install trouble, perl DBI
From: "Lewis Ashley Foster" <[EMAIL PROTECTED]> > > I'm trying to get mySQL installed on my machine but im having a bit of > trouble, obviously :) > > When i come to install mySQL server as below: > > rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm > > I get this result: > > warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: > NOKEY, key ID 5072e1f5 > error: Failed dependencies: > perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3 > Suggested resolutions: > perl-DBI-1.32-5.i386.rpm > > But I have already installed perl dbi 1.40 like this with no errors: > > rpm -ivh perl-DBI-1.40-5.src.rpm > > I really cant think how to get round this, unless i downgrade the dbi to > 1.32 but that seems daft. > Hi, Try this command: perl -MDBI -e1 If it will tell you that it can't find the DBI module, this means that DBI was not properly installed. If it will give no results, this means that you have the DBI module installed, so try the following command: perl -M'DBI 999' -e1 It will give an error telling that you don't have DBI version 999 installed but only the version ... and here it will tell the version number. If it is a newer version that the version required by MySQL RPM, then perhaps there is an error in the format of the version of DBI, or most probably in the way MySQL checks that version and don't like a newer one. You have some solutions: 1. Manually edit the file DBI.pm after it is installed on the computer and write the older version instead. Then install MySQL and then change the version back in DBI.pm file. The newer DBI will surely work with MySQL. 2. Install the older version of DBI, install MySQL, then install again the latest version of DBI module. If rpm doesn't allow you to do this because you might need to remove the older version of DBI, you can download the module DBI from search.cpan.org, and compile and install it using: perl Makefile.pl make make test make install or use the command: cpan (But in this case you will need to answer to some configuration settings) Then do: cpan> install DBI This will download and install the latest version of DBI. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
install trouble, perl DBI
I'm trying to get mySQL installed on my machine but im having a bit of trouble, obviously :) When i come to install mySQL server as below: rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm I get this result: warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3 Suggested resolutions: perl-DBI-1.32-5.i386.rpm But I have already installed perl dbi 1.40 like this with no errors: rpm -ivh perl-DBI-1.40-5.src.rpm I really cant think how to get round this, unless i downgrade the dbi to 1.32 but that seems daft. Any ideas would be greatly appreciated. Lewis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Perl DBI does not print error.
The print string you show is: "prepare: $insert_sql: $DBI::errstr" but your error message is Unable to execute query: DBI::db=HASH(0x8647df0)->errstr I think your problem is with a print line like: print "Unable to execute query: $dbh->errstr" and Perl is interpolating $dbh, but not the full method call $dbh->errstr. You want instead: print "Unable to execute query: ", $dbh->errstr or print "Unable to execute query: @{[ $dbh->errstr ]}" You will probably have better luck with your Perl questions on Usenet newsgroup comp.lang.perl.misc, or on the Perl Monks site www.perlmonks.org. > -Original Message- > From: sam [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 10, 2005 7:56 AM > To: Mysql > Subject: Perl DBI does not print error. > > Hi, > > The perl DBI does not prints error message: > $sth = $dbh->prepare($insert_sql) >|| print ERRFILE_OUT "prepare: $insert_sql: $DBI::errstr"; > > The out of this error only does not print why the error was occurred. > eg. > Unable to execute query: DBI::db=HASH(0x8647df0)->errstr: > > Dan anyone please tell me how to prints how a description of > the error > from DBI when sql executed failed? > > Thanks > Sam > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl DBI does not print error.
Hi! Am Do, den 10.03.2005 schrieb sam um 14:55: > Hi, > > The perl DBI does not prints error message: > $sth = $dbh->prepare($insert_sql) >|| print ERRFILE_OUT "prepare: $insert_sql: $DBI::errstr"; > > The out of this error only does not print why the error was occurred. > eg. > Unable to execute query: DBI::db=HASH(0x8647df0)->errstr: "Works as designed" (but not as intended ... ) > > Dan anyone please tell me how to prints how a description of the error > from DBI when sql executed failed? By using "or" where you used "||". Both do an "or" operation, but have different priorities relative to the "=" assignment. See the example in "Programming Perl" (Wall, Christiansen, Schwartz) on page 89 (and other Perl references). HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Perl DBI does not print error.
Hi, The perl DBI does not prints error message: $sth = $dbh->prepare($insert_sql) || print ERRFILE_OUT "prepare: $insert_sql: $DBI::errstr"; The out of this error only does not print why the error was occurred. eg. Unable to execute query: DBI::db=HASH(0x8647df0)->errstr: Dan anyone please tell me how to prints how a description of the error from DBI when sql executed failed? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One question about Perl DBI interface.
Good morning! Important: SQL Server used: MySQL (version 4.0.17) on UNIX FreeBSD. Answer this question please: Can I make a request, consisting of several SQL commands at one step, like this: use DBI; my $db = DBI->new(); $db = DBI->connect(...) or die "Can't connect to DataBase:", "error code", "\n"; $db->do("CREATE TEMPORARY TABLE tmptable (field1 INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL,field2 DOUBLE(16,2) DEFAULT '0.00' NOT NULL); LOCK TABLES table1 read; INSERT INTO tmptable SELECT field1, MAX(field2) FROM table1 GROUP BY field1;"); I can do it by sending these commands one by one in series, like this: use DBI; my $db = DBI->new(); $db = DBI->connect(...) or die "Can't connect to DataBase:", "error code", "\n"; $db->do("CREATE TEMPORARY TABLE tmptable (field1 INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL,field2 DOUBLE(16,2) DEFAULT '0.00' NOT NULL);"); $db->do("LOCK TABLES table1 read;"); $db->do("INSERT INTO tmptable SELECT field1, MAX(field2) FROM table1 GROUP BY field1;"); I think last solution is more time expensive. # Truly yours Sviridov Vladislav. [EMAIL PROTECTED]
Performance question with perl DBI
Hi all, i have a MySQL driven web system that searches a pretty big DB of property info. (10 data tables, about 50,000,000 total rows, average 300 concurrent users) There are actually 2 databases that feed this site, one is meta info, and isnt hit that heavily. Lets call those DB's 'data' and 'metadata'. So my perl cgi handler creates a MySQL DBI connection to the 'data' db. When i need info from the 'metadata', i see a couple options. 1) issue query 'use metadata'. Do whatever. issue query 'use data' 2) dont change DB's, but have all queries into 'metadata' explicitly name the tables, like '... metadata.some_table...' I was wondering if either method has better performance. They seem about the same to me. Oh, im using InnoDB with the innodb_file_per_table setting. The 'data' DB is on a separate physical drive from 'metadata' Any thoughts? need more info, let me know? am i being too anal? thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table locking using perl DBI
Hi Andy, The best way to accomplish this is at an application level, setup a lock table and put your locks in there. There are many methods for handling application locks. Choose one that work for you. With a database level lock, when the connection terminates the lock is released. Persistant connections might be an option, but you can't guarantee your CGI will get the same connection and be able to release the lock! Hope this helps, Ken - Original Message - From: "Andy Ford" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, April 26, 2004 10:01 PM Subject: table locking using perl DBI > I have a perl cgi script that needs to lock the tables. Unfortunately, > as HTTP is a one shot protocol, once the cgi script completes execution, > the tables are unlocked. > > I need it to stay locked until another cgi script unlocks them! > > Is there any other way of achieving this!? > > Thanks > > Andy > > > -- > > perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^' > in:control developer, Telindus, RG27 9HY > DDI: +44 1256 709211, GSM: +44 7810 636652 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table locking using perl DBI
Andy Ford wrote: I have a perl cgi script that needs to lock the tables. Unfortunately, as HTTP is a one shot protocol, once the cgi script completes execution, the tables are unlocked. I need it to stay locked until another cgi script unlocks them! Is there any other way of achieving this!? You cannot unlock tables that have been locked by another cgi script. If you could, you could easily steal the locks that were set by someone else (not a good idea). If your scripts access the tables exclusively, that is, no scripts from someone else or other clients have access to the tables you want to lock, you could use the GET_LOCK(str,timeout) and RELEASE_LOCK(str) functions to perform cooperative advisory locking on a global/server (not table) level. This will protect you from yourself, but requires consistent use by all your scripts and it doesn't protect you from other clients/scripts accessing the tables, something which you can easily forget sooner or later. Hans-Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table locking using perl DBI
I have a perl cgi script that needs to lock the tables. Unfortunately, as HTTP is a one shot protocol, once the cgi script completes execution, the tables are unlocked. I need it to stay locked until another cgi script unlocks them! Is there any other way of achieving this!? Thanks Andy -- perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^' in:control developer, Telindus, RG27 9HY DDI: +44 1256 709211, GSM: +44 7810 636652 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query fast via mysql CLI, Same query slow via Perl DBI
Daniel J. Conlon wrote: Hi, When executing this query through the 'mysql' command line utility, the result is returned from the database server immediately and the database server does not create a temporary file. (SELECT domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor ds.type FROM domains LEFT JOIN accounts USING(username) LEFT JOIN zone_records ON zone_records.zone = accounts.username AND zone_records.type = 'username' WHERE domains.host = 1) UNION (SELECT domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor ds.type FROM domains LEFT JOIN accounts USING(username) LEFT JOIN zone_records ON zone_records.zone = domains.domain AND zone_records.type = 'domain' WHERE domains.host = 1) ORDER BY domains.domain,zone_records.type LIMIT 871; When executing the exact same query in a Perl script via the DBI interface, the database server takes minutes to return the results, using large amounts of CPU whilst computing the results and creating a temporary file in /var/tmp. Perhaps perl is taking up so much memory that a temporary file is needed to hold the results. I have turned on query logging and verified that the query logged by the server is the same when executed with both the mysql and perl clients so it does not seem that the mysql command line is optimising the query in any way. I have restarted the database server between queries to ensure that the results are not being cached. The two clients are connecting from the same server using the same username and password to login. The structure of the tables concerned is as follows: CREATE TABLE `accounts` ( `username` varchar(20) NOT NULL default '', `owner` varchar(20) NOT NULL default '', `type` varchar(10) default NULL, `server` tinyint(3) default NULL, `winserver` tinyint(3) default NULL, PRIMARY KEY (`username`), KEY `owner` (`owner`), ) TYPE=InnoDB CREATE TABLE `domains` ( `domain` varchar(255) NOT NULL default '', `username` varchar(20) NOT NULL default '', `host` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`domain`), KEY `username` (`username`) ) TYPE=InnoDB CREATE TABLE `zone_records` ( `zone` varchar(255) NOT NULL default '', `type` enum('username','domain','component','default') NOT NULL default 'username', `record` enum('A','MX','CNAME','PTR','NS','SOA') NOT NULL default 'A', `ttl` smallint(5) unsigned default NULL, `data` varchar(255) NOT NULL default '', KEY `zone` (`zone`), KEY `zone_type` (`zone`,`type`) ) TYPE=InnoDB Software versions: mysql-4.0.12-standard-log DBI-1.30 DBD-mysql-2.1018 I am really at a loss as to what could be causing this and what to do to correct the problem. Any assistance or advice you can offer is greatly appretiated. Thanks in advance. Dan Conlon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query fast via mysql CLI, Same query slow via Perl DBI
Hi, When executing this query through the 'mysql' command line utility, the result is returned from the database server immediately and the database server does not create a temporary file. (SELECT domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor ds.type FROM domains LEFT JOIN accounts USING(username) LEFT JOIN zone_records ON zone_records.zone = accounts.username AND zone_records.type = 'username' WHERE domains.host = 1) UNION (SELECT domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor ds.type FROM domains LEFT JOIN accounts USING(username) LEFT JOIN zone_records ON zone_records.zone = domains.domain AND zone_records.type = 'domain' WHERE domains.host = 1) ORDER BY domains.domain,zone_records.type LIMIT 871; When executing the exact same query in a Perl script via the DBI interface, the database server takes minutes to return the results, using large amounts of CPU whilst computing the results and creating a temporary file in /var/tmp. I have turned on query logging and verified that the query logged by the server is the same when executed with both the mysql and perl clients so it does not seem that the mysql command line is optimising the query in any way. I have restarted the database server between queries to ensure that the results are not being cached. The two clients are connecting from the same server using the same username and password to login. The structure of the tables concerned is as follows: CREATE TABLE `accounts` ( `username` varchar(20) NOT NULL default '', `owner` varchar(20) NOT NULL default '', `type` varchar(10) default NULL, `server` tinyint(3) default NULL, `winserver` tinyint(3) default NULL, PRIMARY KEY (`username`), KEY `owner` (`owner`), ) TYPE=InnoDB CREATE TABLE `domains` ( `domain` varchar(255) NOT NULL default '', `username` varchar(20) NOT NULL default '', `host` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`domain`), KEY `username` (`username`) ) TYPE=InnoDB CREATE TABLE `zone_records` ( `zone` varchar(255) NOT NULL default '', `type` enum('username','domain','component','default') NOT NULL default 'username', `record` enum('A','MX','CNAME','PTR','NS','SOA') NOT NULL default 'A', `ttl` smallint(5) unsigned default NULL, `data` varchar(255) NOT NULL default '', KEY `zone` (`zone`), KEY `zone_type` (`zone`,`type`) ) TYPE=InnoDB Software versions: mysql-4.0.12-standard-log DBI-1.30 DBD-mysql-2.1018 I am really at a loss as to what could be causing this and what to do to correct the problem. Any assistance or advice you can offer is greatly appretiated. Thanks in advance. Dan Conlon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Perl DBI Persistent Connection
On Fri, 16 Jan 2004 17:54:06 -0500 "Syed Ali" <[EMAIL PROTECTED]> wrote: > I have a Perl CGI script which uses DBI to access MySQL. > I noticed that each query through the Perl CGI script using DBI has a > connect, query and then quit. > I was wondering if there is a way of keeping the connection persistent > to Mysql through the Perl CGI script. > > In my Apache httpd.conf I inserted PerlModule Apache::DBI before all > LoadModule lines, but that does not solve the problem. > I also downloaded the Apache::DBI module from CPAN and installed it. > Perhaps I need to do something else? Is your script running through mod_perl or plain CGI? If it is running through CGI, you can't make persistent connection to MySQL. You should check: http://perl.apache.org/docs/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and Perl DBI Persistent Connection
Hello, I have a Perl CGI script which uses DBI to access MySQL. I noticed that each query through the Perl CGI script using DBI has a connect, query and then quit. I was wondering if there is a way of keeping the connection persistent to Mysql through the Perl CGI script. In my Apache httpd.conf I inserted PerlModule Apache::DBI before all LoadModule lines, but that does not solve the problem. I also downloaded the Apache::DBI module from CPAN and installed it. Perhaps I need to do something else? I also removed any disconnect statements in my Perl CGI script, but I still see the quit in mysql.query.log. Thank you... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl DBI vs. prepare and execute do
On 12/6/03 8:52 AM Dan Anderson wrote: I am using the Perl DBI to connect to a mySQL database. I am using prepare and execute statements to send the query to the database and then execute it. Is there any benefit to doing this versus using do? The O'Reilly book "Programming the Perl DBI" says: ...the do() method supplied by the DBI makes executing non-SELECT statements much simpler than repeatedly preparing and executing statements. ... There is a drawback to doing this, however: performance. If you invoked do() repeatedly to insert a huge number of rows into a table, you could be preparing a statement handle many times more than is required, especially if the statement contained placeholder variables. HTH. --Jo smime.p7s Description: S/MIME Cryptographic Signature
perl DBI vs. prepare and execute do
I am using the Perl DBI to connect to a mySQL database. I am using prepare and execute statements to send the query to the database and then execute it. Is there any benefit to doing this versus using do? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PERL DBI DBD mysql / REMOTE ACCESS
"nm" <[EMAIL PROTECTED]> wrote: > > I can login on console. I can telnet 192.168.1.20 3306 from a remote host > but when I try to connect using perl DBI i get this error: > > DBI connect('database=test;hostname=192.168.1.20','root',...) failed: Access > denied for user: '[EMAIL PROTECTED]' (Using password: YES) > > I added Host 192.168.1.10 user root with passowrd in mysql user table. > > Ideas? What else? If you edit privilege tables explicitly you should execute FLUSH PRIVILEGES. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PERL DBI DBD mysql / REMOTE ACCESS
> I added Host 192.168.1.10 user root with passowrd in mysql user table. Did you flush privileges? did you hash the password using the 'password' function? did you type the password correctly ? > > Ideas? What else? > > Thanks. -- -- Johannes Ullrich [EMAIL PROTECTED] pgp key: http://johannes.homepc.org/PGPKEYS -- "We regret to inform you that we do not enable any of the security functions within the routers that we install." [EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PERL DBI DBD mysql / REMOTE ACCESS
I can login on console. I can telnet 192.168.1.20 3306 from a remote host but when I try to connect using perl DBI i get this error: DBI connect('database=test;hostname=192.168.1.20','root',...) failed: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I added Host 192.168.1.10 user root with passowrd in mysql user table. Ideas? What else? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting records while parsing query results (in Perl DBI)?
ExpiryDate On 28-Feb-2003 Jeff Snoxell wrote: > Hi, > > If I run a mysql query then work through the results of that query one at a > time, deleting the record sometimes eg: > > while (my $href = $sth->fetchrow_hashref()) > { >if ($href->{'ExpiryDate'} eq '2003-02-22 00:00:00') { > # Do something > # then... > $db->do("DELETE FROM MyTable WHERE ID=$href->{'ID'}"); >} >else { > # Do something else >} > } > > Does the DELETE cause a change in the results of the surrounding $sth > parsing loop? > > I know it would be best to do a: > > DELETE FROM MyTable WHERE ExpiryDate = whatever > > But I need to use the data to do some other stuff, only from those records > that are up for deletion. > > Any ideas? > Fetch all the IDs of interest into an array and loop on that. In my libsql.pl I wrote a sub: sub GetArray { my $qry= shift; my @na=(); my $res=SQLQuery($qry); if ($res) { while (my(@row) = $res->fetchrow()) { push( @na, $row[0]) ; } } return(@na); } $tbl='mytable'; $qry="SELECT id FROM $tbl WHERE ExpiryDate='2003-02-22'"; @ids=GetArray($qry); while ( my(@id)= splice (@ids,0,64)) { $deleteqry="DELETE FROM $tbl WHERE id IN (" .join(',',@id) .")"; SQLQuery($deleteqry); } $qry="SELECT id FROM $tbl WHERE ExpiryDate < DATE_ADD(CURRENT_DATE, INTERVAL 1 WEEK)"; @ids=GetArray($qry); while ( @id= splice (@ids,0,64)) { $weekqry="UPDATE $tbl SET foo='bar' WHERE id IN (" .join(',',@id) .")"; SQLQuery($weekqry); } Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Deleting records while parsing query results (in Perl DBI)?
Hi, If I run a mysql query then work through the results of that query one at a time, deleting the record sometimes eg: while (my $href = $sth->fetchrow_hashref()) { if ($href->{'ExpiryDate'} eq '2003-02-22 00:00:00') { # Do something # then... $db->do("DELETE FROM MyTable WHERE ID=$href->{'ID'}"); } else { # Do something else } } Does the DELETE cause a change in the results of the surrounding $sth parsing loop? I know it would be best to do a: DELETE FROM MyTable WHERE ExpiryDate = whatever But I need to use the data to do some other stuff, only from those records that are up for deletion. Any ideas? Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI secret command?
hi pls look into "man DBI" and find there this command. kr mr >>> Jeff Snoxell <[EMAIL PROTECTED]> 01/18/03 10:45 AM >>> Hi, just stumbled across the following piece of code: my $rowcount = $sth->rows(); And I checked it out compared to a count of the fetched rows from an SQL QUERY. AND It matched exactly! BUT, I can't find the command "rows()" in the DBI documentation. What's the story? Have I unearthed a conspiracy? Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI secret command?
At 9:39 + 1/18/03, Jeff Snoxell wrote: Hi, just stumbled across the following piece of code: my $rowcount = $sth->rows(); And I checked it out compared to a count of the fetched rows from an SQL QUERY. AND It matched exactly! BUT, I can't find the command "rows()" in the DBI documentation. What's the story? Have I unearthed a conspiracy? You probably looked for it with parentheses. When you do find it, you'll note that it discourages use of the rows method for SELECT statements, and recommends that you count the rows yourself as you fetch them. Jeff sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI secret command?
No conspiracy, read it again ! ;) http://www.perldoc.com/cpan/DBI.html#rows Joseph Bueno Jeff Snoxell wrote: > Hi, > > just stumbled across the following piece of code: > > my $rowcount = $sth->rows(); > > And I checked it out compared to a count of the fetched rows from an SQL > QUERY. AND It matched exactly! > > BUT, I can't find the command "rows()" in the DBI documentation. > > What's the story? Have I unearthed a conspiracy? > > > Jeff > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI secret command?
Hi, just stumbled across the following piece of code: my $rowcount = $sth->rows(); And I checked it out compared to a count of the fetched rows from an SQL QUERY. AND It matched exactly! BUT, I can't find the command "rows()" in the DBI documentation. What's the story? Have I unearthed a conspiracy? Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Perl DBI $sth->fetchrow_hashref() persistence?
I'm not sure how Perl works on variable assignment. If $sth is passing a reference to $record finish() may kill the variable $record holds a reference to during garbage collection. If it *assigns* the row to $record (meaning $record holds a copy of the record data and not a reference to where the data is actually stored) then you should be able to use it all you want. Maybe one of the many perl monks will know more. --Joe -- Joe Stump <[EMAIL PROTECTED]> http://www.joestump.net -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 6:22 AM To: [EMAIL PROTECTED] Subject: Perl DBI $sth->fetchrow_hashref() persistence? Hi, if I call: my $record = $sth->fetchrow_hashref(); then: $sth->finish(); How valid is it for me to keep referring to my $record hash pointer? Thanks, Jeff Snoxell Aetherweb Ltd http://www.aetherweb.co.uk [SPAM FILTER FODDER: MySQL, QUERY] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI $sth->fetchrow_hashref() persistence?
Hi, if I call: my $record = $sth->fetchrow_hashref(); then: $sth->finish(); How valid is it for me to keep referring to my $record hash pointer? Thanks, Jeff Snoxell Aetherweb Ltd http://www.aetherweb.co.uk [SPAM FILTER FODDER: MySQL, QUERY] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating MySQL databases and tables via Perl/DBI
the example will connect to the local mySQL server and create a new database named 'dbname" #!/usr/bin/perl -w use DBI; $dbh = DBI->connect( "dbi:mysql:", 'user', 'password', { PrintError => 0 } ) or die "Can\"t connect to database: $DBI::errstr\n"; $dbh->do("create database dbname"); $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; On Wednesday, January 8, 2003, at 12:04 PM, Ken Hilliard wrote: I'd like to be able to create a new database using Perl/DBI instead of using the MySQL command line (and other) utilities. It looks like you can create a new database with the driver handle but I can't seem to make it work. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating MySQL databases and tables via Perl/DBI
I'd like to be able to create a new database using Perl/DBI instead of using the MySQL command line (and other) utilities. It looks like you can create a new database with the driver handle but I can't seem to make it work. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using Perl DBI quote() method with NULL values?
Hi Jeff, Try the magic undef as it is with no '' or "" $dbh->do('insert into yourtab values (' . join (',', map($dbh->quote($_), $your_stuff, undef, $more_stuff)) . ')') ; Regards Salam Jeff Snoxell wrote: Hello, I've got a mysql table with a lot of fields and I'm using a map statement to pass each of my values for a new record through the quote system so-as to have everything nicely wrapped up. Problem is that I can't find any way of writing a null value to my DB when the value has first passed through quote(). I've tried: my $err = $MyDatabase->do('INSERT INTO MyTable VALUES(' . join(",",map($MyDatabase->quote($_), 0, # REF 0, # PARENT REF $FormData{'Title'} . " " . $FormData{'Name'} . "", $FormData{'Email'} . "", "\N", # <-- I REALLY WANT THIS TO BE A NULL VALUE! etc., etc., etc. )) . ')'); Which works fine except the "\N" isn't entered into my database as a null value. I've also tried '\N' and '' and "". Any ideas how I can solve this easily? Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using Perl DBI quote() method with NULL values?
From: Jeff Snoxell <[EMAIL PROTECTED]> > ...I'm using a map statement... > ...through the quote system... Short answer: Don't do that. The longer answer: # Prepare the SQL using 'bind' operators. $sth = $MyDatabase->prepare(qq{ INSERT INTO MyTable (col1, col2, col3) VALUES (?, ?, ?) }); $sth->execute(@values); None of the other silly quoting stuff we're talking about applies to what you are doing. Just use bind variables and ALL of your problems will go away. Hollar if you need a hand with this, it can be a bit strange the first time. --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Using Perl DBI quote() method with NULL values?
Date: Wed, 8 Jan 2003 01:56:39 +0100 From: [EMAIL PROTECTED] To: Paul DuBois <[EMAIL PROTECTED]> Subject: Re: Re: Using Perl DBI quote() method with NULL values? At 17:16 -0500 1/7/03, walt wrote: Jeff, I'm not sure if this will help or not, but we ended up adding our own version of quote to perl cgis. Someone told me when I ask a similar question to check the value before calling quote, but it seemed like more of a headache... You may have to use perl's ord & chr to look for \n walt sub nea_quote { my ($input) = @_; if (length($input) == 0) { $return_string = "NULL"; return $return_string; } else { $return_string = $dbh->quote($input); return $return_string; } } This seems unnecessary, and (unless I'm missing something), it seems to treat empty strings as NULL, which isn't necessarily correct. Just use DBI's quote() method, and pass it undef when you want to produce a NULL. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using Perl DBI quote() method with NULL values?
On 7 Jan 2003, at 18:32, Jeff Snoxell wrote: > Which works fine except the "\N" isn't entered into my database as a null > value. I've also tried '\N' and '' and "". Null values are represented as undef in DBI. [Filter fodder: SQL] -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using Perl DBI quote() method with NULL values?
Jeff Snoxell wrote: > Hello, > > I've got a mysql table with a lot of fields and I'm using a map statement > to pass each of my values for a new record through the quote system so-as > to have everything nicely wrapped up. Problem is that I can't find any way > of writing a null value to my DB when the value has first passed through > quote(). > > I've tried: > > my $err = $MyDatabase->do('INSERT INTO MyTable VALUES(' . >join(",",map($MyDatabase->quote($_), >0, # REF >0, # PARENT REF >$FormData{'Title'} . " " . $FormData{'Name'} . "", >$FormData{'Email'} . "", >"\N", # <-- I REALLY WANT THIS TO BE A NULL VALUE! >etc., >etc., >etc. > )) . > ')'); > > Which works fine except the "\N" isn't entered into my database as a null > value. I've also tried '\N' and '' and "". > > Any ideas how I can solve this easily? > > Thanks, > > Jeff > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Jeff, I'm not sure if this will help or not, but we ended up adding our own version of quote to perl cgis. Someone told me when I ask a similar question to check the value before calling quote, but it seemed like more of a headache... You may have to use perl's ord & chr to look for \n walt sub nea_quote { my ($input) = @_; if (length($input) == 0) { $return_string = "NULL"; return $return_string; } else { $return_string = $dbh->quote($input); return $return_string; } } - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using Perl DBI quote() method with NULL values?
At 18:32 + 1/7/03, Jeff Snoxell wrote: Hello, I've got a mysql table with a lot of fields and I'm using a map statement to pass each of my values for a new record through the quote system so-as to have everything nicely wrapped up. Problem is that I can't find any way of writing a null value to my DB when the value has first passed through quote(). I've tried: my $err = $MyDatabase->do('INSERT INTO MyTable VALUES(' . join(",",map($MyDatabase->quote($_), 0, # REF 0, # PARENT REF $FormData{'Title'} . " " . $FormData{'Name'} . "", $FormData{'Email'} . "", "\N", # <-- I REALLY WANT THIS TO BE A NULL VALUE! Use undef to get a NULL value. etc., etc., etc. )) . ')'); Which works fine except the "\N" isn't entered into my database as a null value. I've also tried '\N' and '' and "". Any ideas how I can solve this easily? Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using Perl DBI quote() method with NULL values?
Hello, I've got a mysql table with a lot of fields and I'm using a map statement to pass each of my values for a new record through the quote system so-as to have everything nicely wrapped up. Problem is that I can't find any way of writing a null value to my DB when the value has first passed through quote(). I've tried: my $err = $MyDatabase->do('INSERT INTO MyTable VALUES(' . join(",",map($MyDatabase->quote($_), 0, # REF 0, # PARENT REF $FormData{'Title'} . " " . $FormData{'Name'} . "", $FormData{'Email'} . "", "\N", # <-- I REALLY WANT THIS TO BE A NULL VALUE! etc., etc., etc. )) . ')'); Which works fine except the "\N" isn't entered into my database as a null value. I've also tried '\N' and '' and "". Any ideas how I can solve this easily? Thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Perl DBI or C++ API Help!
Q&D answer in PERL! my ($file); $file = 'database_names'; $cmd = qq!show databases;!; open O, ">cmd" or die "could open cmd - $!"; print O "$cmd\n"; close O; system ("mysql -uusername -ppassword < cmd > $file") == 0 or die "could not do system - $!"; open I, "<$file" or die "could not open $file - $!"; while (){ print "$_"; } close I; -Original Message- From: Bernd Prager [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 2:33 PM To: Sarah Killcoyne; [EMAIL PROTECTED] Subject: Re: Perl DBI or C++ API Help! - Original Message - From: "Sarah Killcoyne" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, December 03, 2002 11:14 AM > I need to be able to connect to mysql through a perl or C++ script without > knowing the name of the database to connect to. ,,, I'm not an expert om the C++ API. But if the host is a UNIX system you can do a RSH command in C++ as well as in Perl. "mysql -u username 'show databases;'" Maybe there's a better solution, but in worst case this would do it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI or C++ API Help!
Sarah Killcoyne wrote: I need to be able to connect to mysql through a perl or C++ script without knowing the name of the database to connect to. I can't seem to find a way to do this. Is it possible to either: look up database names so I can pick one before connecting or connect without a database name so I can "show databases;" and pick one then? The reason for this is, this script will be creating the database we need new each time with a different name depending on how the user wants it so it's possible that there are no databases set up at all or a differnt named one each time. My book only shows me how to do it if I know the name. One way could be to connect to the test database and then run the SQL "show databases". - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI or C++ API Help!
- Original Message - From: "Sarah Killcoyne" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, December 03, 2002 11:14 AM > I need to be able to connect to mysql through a perl or C++ script without > knowing the name of the database to connect to. ,,, I'm not an expert om the C++ API. But if the host is a UNIX system you can do a RSH command in C++ as well as in Perl. "mysql -u username 'show databases;'" Maybe there's a better solution, but in worst case this would do it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Perl DBI or C++ API Help!
For Perl... Take a look at the data_sources DBI class method http://search.cpan.org/author/TIMB/DBI-1.32/DBI.pm#DBI_Class_Methods or the ListDBs MetaData Method for DBD::mysql http://search.cpan.org/author/JWIED/DBD-mysql-2.1020/lib/DBD/mysql.pod#Priva te_MetaData_Methods Duncan --- Duncan Salada Titan Systems Corporation 301-925-3222 x375 -Original Message- From: Sarah Killcoyne [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 11:14 AM To: [EMAIL PROTECTED] Subject: Perl DBI or C++ API Help! I need to be able to connect to mysql through a perl or C++ script without knowing the name of the database to connect to. I can't seem to find a way to do this. Is it possible to either: look up database names so I can pick one before connecting or connect without a database name so I can "show databases;" and pick one then? The reason for this is, this script will be creating the database we need new each time with a different name depending on how the user wants it so it's possible that there are no databases set up at all or a differnt named one each time. My book only shows me how to do it if I know the name. Thanks!! Sarah - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI or C++ API Help!
I need to be able to connect to mysql through a perl or C++ script without knowing the name of the database to connect to. I can't seem to find a way to do this. Is it possible to either: look up database names so I can pick one before connecting or connect without a database name so I can "show databases;" and pick one then? The reason for this is, this script will be creating the database we need new each time with a different name depending on how the user wants it so it's possible that there are no databases set up at all or a differnt named one each time. My book only shows me how to do it if I know the name. Thanks!! Sarah - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: perl dbi and mysql question
yeah, but i was looking for a function. mostly just to see if a matching function to dbh->tables() existed. -Original Message- From: Grigor, Peter [mailto:pgrigor@;aseedge.com] Sent: Friday, November 08, 2002 10:07 AM To: 'Shon Stephens'; '[EMAIL PROTECTED]' Subject: RE: perl dbi and mysql question Of course, you could have also used: $stmt = $db->prepare("show databases"); Peter <^_^> -Original Message- From: Shon Stephens [mailto:sstephens@;corp.goamerica.net] Sent: Friday, November 08, 2002 9:50 AM To: Shon Stephens; '[EMAIL PROTECTED]' Subject: RE: perl dbi and mysql question eureka! i found it! @dbs = $dbh->func('_ListDBs'); -Original Message- From: Shon Stephens [mailto:sstephens@;corp.goamerica.net] Sent: Friday, November 08, 2002 9:45 AM To: '[EMAIL PROTECTED]' Subject: perl dbi and mysql question i am using perl dbi with dbd::mysql. i know that using the dbi function, $dbh->tables (), will list all the tables in a database. is there a function that will show all the databases on a server? thanks, shon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: perl dbi and mysql question
Of course, you could have also used: $stmt = $db->prepare("show databases"); Peter <^_^> -Original Message- From: Shon Stephens [mailto:sstephens@;corp.goamerica.net] Sent: Friday, November 08, 2002 9:50 AM To: Shon Stephens; '[EMAIL PROTECTED]' Subject: RE: perl dbi and mysql question eureka! i found it! @dbs = $dbh->func('_ListDBs'); -Original Message- From: Shon Stephens [mailto:sstephens@;corp.goamerica.net] Sent: Friday, November 08, 2002 9:45 AM To: '[EMAIL PROTECTED]' Subject: perl dbi and mysql question i am using perl dbi with dbd::mysql. i know that using the dbi function, $dbh->tables (), will list all the tables in a database. is there a function that will show all the databases on a server? thanks, shon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: perl dbi and mysql question
No clue of dbi/dbd but the query SHOW DATABASES does what it tells. STIBS - Original Message - From: "Shon Stephens" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 08, 2002 3:44 PM Subject: perl dbi and mysql question > i am using perl dbi with dbd::mysql. i know that using the dbi function, > $dbh->tables (), will list all the tables in a database. is there a function > that will show all the databases on a server? > > thanks, > shon > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: perl dbi and mysql question
eureka! i found it! @dbs = $dbh->func('_ListDBs'); -Original Message- From: Shon Stephens [mailto:sstephens@;corp.goamerica.net] Sent: Friday, November 08, 2002 9:45 AM To: '[EMAIL PROTECTED]' Subject: perl dbi and mysql question i am using perl dbi with dbd::mysql. i know that using the dbi function, $dbh->tables (), will list all the tables in a database. is there a function that will show all the databases on a server? thanks, shon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
perl dbi and mysql question
i am using perl dbi with dbd::mysql. i know that using the dbi function, $dbh->tables (), will list all the tables in a database. is there a function that will show all the databases on a server? thanks, shon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI Interfacer
On Sat, Sep 21, 2002 at 02:06:01AM -0500, Kevin wrote: > Hello, I was wondering if anyone knows PERL & DBI, if so please respond to >[EMAIL PROTECTED] personally, or to the list > > > My question is, I'm trying to build a 'custom' querier for a survey program... based >on the information submitted, the program will generate an SQL Query statement. The >command that my program works absolutely find if I copy/paste it into MySQL, however, >DBI won't pass it... if I pass the QUERY Along to DBI typed in manually it works... I'm not in the mood to unpeel this code, just to see output. As I'm curious, could you set the DBI trace (to 2 or better), run your query, and show us the logs? That we can see what DBI is getting, and what it's actually complaining about (since you didn't tell us...) > Thanks > Kevin -- Brian 'you Bastard' Reichert<[EMAIL PROTECTED]> 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI Interfacer
Hello, I was wondering if anyone knows PERL & DBI, if so please respond to [EMAIL PROTECTED] personally, or to the list My question is, I'm trying to build a 'custom' querier for a survey program... based on the information submitted, the program will generate an SQL Query statement. The command that my program works absolutely find if I copy/paste it into MySQL, however, DBI won't pass it... if I pass the QUERY Along to DBI typed in manually it works... Any Idea's? Here is a copy of the block of code #Attempt to build an artificial SQL Query # $i=0; if($vars{'f_name'} ne "") { @query[0] = "AND `f_name` = \'$vars{'f_name'}\'"; $i++; } if($vars{'l_name'} ne "") { @query[$i] = "AND `l_name` = \'$vars{'l_name'}\'"; $i++; } if($vars{'stud_num'} ne "") { @query[$i] = "AND `stud_num` = \'$vars{'stud_num'}\'"; $i++; } if($vars{'grade'} ne "") { @query[$i] = "AND `grade` = \'$vars{'grade'}\'"; $i++; } foreach $item(@query) { $dastring = join(" ",$dastring,$item); #$end = "\""; #$dastring = join("",$dastring,$end); } #print "Dum Dum Dum... The query is $dastring\n"; #Build the remaining query string $first_part = "SELECT `level`,`f_name`,`l_name`,`stud_num`,`grade`,`last_survey` FROM `users` WHERE 1"; $finalstring = join(" ", $first_part,$dastring); print "The Final Product $finalstring\n"; # #End of Artificial Query #Connect to the SQL Server specified in the %sql hash $dbh = DBI->connect("dbi:mysql:$sql{'database'}", "$sql{'username'}", "$sql{'password'}"); #Pepare an SQL Query for execution $sth = $dbh->prepare($finalstring); #Executes the SQL Command in the previous statement $rc = $sth->execute; #executes the search #my $row_hash; #defines $row_hash as a local variable. #Grab and breakup the row hash #while($row_hash = $sth->fetchrow_hashref) { $vars{'f_name'}=$row_hash->{f_name}; #Loads the Username $vars{'l_name'}=$row_hash->{l_name}; #Loads the password $vars{'stud_num'}=$row_hash->{stud_num}; #Loads the Student # $vars{'grade'}=$row_hash->{grade}; #loads the grade $vars{'l_survey'}=$row_hash->{last_survey}; #loads the date of the last $vars{'level'}=$row_hash->{level}; #Grabs the user level print < $vars{'f_name'} $vars{'l_name'} $vars{'stud_num'} $vars{'grade'} $vars{'l_survey'} $vars{'level'} endofhtml } $sth->finish; #finishes the SQL Query $dbh->disconnect(); #Disconnect the database to save connections Any assistance that can be provided is much appriated as this code is for my school's survey program Thanks Kevin
perl-DBI Question
Hello, I need some advice regarding a scrolling list that I've built from a query. I can generated the scrolling list just fine, but when I try to put the subroutine within a table (cell), all I get is a hash reference: -+-+ Date | column | -+-+ date_calc(DBI::db=HASH(0xfdd40)) +2| -+-+ Code for above output: print start_form (-method => "post", -action => url ()); print ""; print "Datecolumn"; print "date_calc($dbh)2"; print ""; print end_form (); Now, if I break apart the line that generates the above, I get the scrolling list to appear properly, but it doesn't appear in the first cell, it appears above the table. I get a '1' which indicates I believe that the subroutine returned a true value: (scrolling list appears here) ---+-+ Date | column | ---+-+ 1 +2| ---+-+ Code for above output: print start_form (-method => "post", -action => url ()); print ""; print "Datecolumn"; print "" . date_calc($dbh) . "2"; print ""; print end_form (); Please advise what I should do to correct this. BTW, the subroutine I'm using for the above is as follows: sub date_calc { $dbh = shift; ($sth, $stmt); $stmt = qq { select distinct When from rCam order by When desc }; $sth = $dbh->prepare ($stmt); $sth->execute (); print ""; while ($row = $sth->fetchrow_hashref ()){ push (@date_ary,$row->{When}); } $sth->finish (); foreach $elt (@date_ary){ print "$elt\n"; } print "\n"; } Thanks, Brian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl/DBI & load data local
At 10:23 -0800 6/21/02, nellA hciR wrote: >iH > >running mySQL 3.23.51, i have a my.cnf file with local-infile=1 and >am able to use the "load data local file" command when running run >the interactive mysql app (command line). > >my problem is that i can not use the "load data local" command from >a Perl script. i am connecting as the same user and i do with the >command line. other command such as connect, disconnect, insert, >select do work > >what am i missing to allow the same access via Perl as with the command line Add: ;mysql_read_default_group=client to the end of your DSN string. > >thanks >- hcir >[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl/DBI & load data local
iH running mySQL 3.23.51, i have a my.cnf file with local-infile=1 and am able to use the "load data local file" command when running run the interactive mysql app (command line). my problem is that i can not use the "load data local" command from a Perl script. i am connecting as the same user and i do with the command line. other command such as connect, disconnect, insert, select do work what am i missing to allow the same access via Perl as with the command line thanks - hcir [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI
Yes. Denny said: > Hi, > > How can i use Perl DBI to create tables in mysql? > > Denny > > __ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> To > unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI
Hi Denny, >How can i use Perl DBI to create tables in mysql? I am not an expert in CPAN modules for Perl but I am using the DBD module that needs DBI module for access the MySQL. Please see the man pages for DBI module. I am using the: DBI-1.22 DBD-mysql-2.1017 You can installing all mentioned and all dependant modules with perl -MCPAN -e 'install Bundle::DBD::mysql' Before rushing execute the perl command mentioned above you need to have compiled MySQL client library and source files (especially header files) otherwise you are not able to install the DBD:mysql module on your platform. Please read INSTALL.htm file that is delivered with the DBD::mysql module. After successful instalation of the DBD::mysql module I have used example from DBD::mysql module documentation. Please see the mentioned EXAMPLE below: #!/usr/bin/perl use strict; use DBI(); # Connect to the database. my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "joe", "joe's password", {'RaiseError' => 1}); # Drop table 'foo'. This may fail, if 'foo' doesn't exist. # Thus we put an eval around it. eval { $dbh->do("DROP TABLE foo") }; print "Dropping foo failed: $@\n" if $@; # Create a new table 'foo'. This must not fail, thus we don't # catch errors. $dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))"); # INSERT some data into 'foo'. We are using $dbh->quote() for # quoting the name. $dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")"); # Same thing, but using placeholders $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen"); # Now retrieve data from the table. my $sth = $dbh->prepare("SELECT * FROM foo"); $sth->execute(); while (my $ref = $sth->fetchrow_hashref()) { print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n"; } $sth->finish(); # Disconnect from the database. $dbh->disconnect(); I hope that it helps. Viliam Batka - Original Message - From: "Denny" <[EMAIL PROTECTED]> To: "Mysql maillist" <[EMAIL PROTECTED]> Sent: Thursday, May 23, 2002 8:02 PM Subject: Perl DBI > Hi, > > How can i use Perl DBI to create tables in mysql? > > Denny > > __ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI
Hi, How can i use Perl DBI to create tables in mysql? Denny __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to load binary data with Perl DBI?
On Sun, Mar 10, 2002 at 09:59:09AM +0200, Toomas Vendelin wrote: > It doesn't look "same way" :(. Normally I've read data from a file > on a client's machine using Perl's read function like this > > while(read($file, $data, 1024)) > { > print TO $data; > } > > to write it from $file on client's computer to a file associated > with file handle TO. > > Now I obviously need to store the data in a variable. Just pushing > $data strings into array and then joining (join('',@array) doesn't > work, because it's not ASCII (file gets corrupted). So, what I do not > understand, is how to open a filehandle to a database field and then > "print" to it? :). Start with this: ---snip--- my $data; open FILE, "<$filename" or die; { local($/) = undef; $data = ; } close FILE; ---snip--- Then just create a prepared statement handle with a place holder for the data and execute it, passing $data along the way. The "Placeholders and Bind Values" section of the DBI documentation explains how to do that. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 31 days, processed 1,033,853,920 queries (382/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: How to load binary data with Perl DBI?
Hello Paul, Thank you for a quick reply. It doesn't look "same way" :(. Normally I've read data from a file on a client's machine using Perl's read function like this while(read($file, $data, 1024)) { print TO $data; } to write it from $file on client's computer to a file associated with file handle TO. Now I obviously need to store the data in a variable. Just pushing $data strings into array and then joining (join('',@array) doesn't work, because it's not ASCII (file gets corrupted). So, what I do not understand, is how to open a filehandle to a database field and then "print" to it? :). Regards, Tom Saturday, March 09, 2002, 9:00:08 PM, you wrote: PD> At 16:39 +0200 3/9/02, [EMAIL PROTECTED] wrote: >>Hello. >> >>How to load binary data into a table using >>Perl DBI module (the data is uploaded from a HTML >>form)? PD> Same way you load any other kind of data. Use placeholders or the quote() PD> function. >> >>I failed to find any information on MySQL site. >> >>Thanks in advance. PD> - PD> Before posting, please check: PD>http://www.mysql.com/manual.php (the manual) PD>http://lists.mysql.com/ (the list archive) PD> To request this thread, e-mail <[EMAIL PROTECTED]> PD> To unsubscribe, e-mail <[EMAIL PROTECTED]> PD> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Best regards, Toomasmailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to load binary data with Perl DBI?
At 16:39 +0200 3/9/02, [EMAIL PROTECTED] wrote: >Hello. > >How to load binary data into a table using >Perl DBI module (the data is uploaded from a HTML >form)? Same way you load any other kind of data. Use placeholders or the quote() function. > >I failed to find any information on MySQL site. > >Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to load binary data with Perl DBI?
Hello. How to load binary data into a table using Perl DBI module (the data is uploaded from a HTML form)? I failed to find any information on MySQL site. Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI installation. Help!
Hello all, I am new to linux. I was told that I need DBI::DBD to connect to Mysql using perl. So I go to http://www.mysql.com/downloads/api-dbi.html and downloaded DBI-1.18.tar.gz, Msql-Mysql-modules-1.2216.tar.gz. Using gunzip, I unziped the two files. Now I have DBI-1.18.tar and Msql-Mysql-modules-1.2216.tar. What should I do next to install them and test them? Thanks for the help _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: explain table within perl DBI
At 15:51 -0800 2/14/02, Brian Warn wrote: >When I run the code below, information about the first column in each >table in my list is excluded. Any ideas why? Why are you reading a row into @row_ary? I bet you're thinking that row will correspond to the row of column headers that you'd see were you to run the query in the mysql client. But it doesn't. So you're reading the first row of information (i.e., the information for the first table column), but discarding it. > >@table_listing=`cat /usr/local/file.txt`; # contains table list used >below > >$dbh = >DBI->connect("dbi:mysql:database=dbname;host=hostIP;port=port_number", >"user", "password") or dienice("Can't connect: $DBI:errstr"); >foreach $table (@table_listing){ > chomp $table; > print "~~ $table Table ~\n"; > > $statement="explain $table"; > $sth = $dbh->prepare($statement) or dienice("Can't prepare >statement: ",$dbh-errstr); > $dbh->errstr; > $sth->execute; > @row_ary = $sth->fetchrow_array; > while (($expl0,$expl1,$expl2) = $sth->fetchrow_array){ > print "$expl0 ... $expl1 ... $expl2\n"; > } > $sth->finish; > print "\n"; >} >$dbh->disconnect; > >-Brian >sql, query, mysql, database > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: explain table within perl DBI
On 14 Feb 2002, at 15:51, Brian Warn wrote: > When I run the code below, information about the first column in each > table in my list is excluded. Any ideas why? [snip] > @row_ary = $sth->fetchrow_array; You've read that information into @row_ary, but then you never do anything with it. > while (($expl0,$expl1,$expl2) = $sth->fetchrow_array){ > print "$expl0 ... $expl1 ... $expl2\n"; > } Now you're printing the rest of the information. > sql, query, mysql, database -- Keith C. Ivey <[EMAIL PROTECTED]> Washington, DC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
explain table within perl DBI
When I run the code below, information about the first column in each table in my list is excluded. Any ideas why? @table_listing=`cat /usr/local/file.txt`; # contains table list used below $dbh = DBI->connect("dbi:mysql:database=dbname;host=hostIP;port=port_number", "user", "password") or dienice("Can't connect: $DBI:errstr"); foreach $table (@table_listing){ chomp $table; print "~~ $table Table ~\n"; $statement="explain $table"; $sth = $dbh->prepare($statement) or dienice("Can't prepare statement: ",$dbh-errstr); $dbh->errstr; $sth->execute; @row_ary = $sth->fetchrow_array; while (($expl0,$expl1,$expl2) = $sth->fetchrow_array){ print "$expl0 ... $expl1 ... $expl2\n"; } $sth->finish; print "\n"; } $dbh->disconnect; -Brian sql, query, mysql, database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error with code utilizing perl DBI & mysql ??
It should be PrintError (capital 'E'). ie. --- $dbh = DBI->connect($dsn, $user, $pwd,{PrintError => 0, RaiseError => 1}); Nicholas -Original Message- From: Investor [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 2:43 PM To: [EMAIL PROTECTED] Subject: Error with code utilizing perl DBI & mysql ?? This is a bit strange, but I was writing code and adding some select statements that seemed to be working fine until about the 5 th one, ... then i get errors : I am a bit of a newbie at mysql & dbi. any help much appreciated Error: Can't set DBI::db-HASH(0x1b73b70->{Printerror}: unrecongnised attribute at C:/indogoperl/site/lib/DBI.pm line 441. Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). use DBI; my $dir = "C:/indigoperl/bin"; my $file1 = "out1.txt"; my $file2 = "out2.txt"; my $file3 = "out3.txt"; my $file4 = "out4.txt"; my $file5 = "out5.txt"; open(F1, ">$dir/$file1") or warn "Did not open $file1: $!\n"; open(F2, ">$dir/$file2") or warn "Did not open $file1: $!\n"; open(F3, ">$dir/$file3") or warn "Did not open $file1: $!\n"; open(F4, ">$dir/$file4") or warn "Did not open $file1: $!\n"; open(F5, ">$dir/$file5") or warn "Did not open $file1: $!\n"; my ($dbh, $sth, $count, $dsn, $user, $pwd); my ($flag, $sic, $sic2, $sic3); my ($cite, $reg); $dsn = "DBI:mysql:host=localhost;database=air"; $user = "root"; $pwd = "wick"; $dbh = DBI->connect($dsn, $user, $pwd,{Printerror => 0, RaiseError => 1}); # Table sicc $flag =Y; print " Enter data for sicc table? 'y' or 'n'\n"; chomp($flag = ); while ( uc($flag) eq 'Y') { print " Enter values for sic, sic2, sic3 separate with commas \n"; chomp($dat = ); ($sic, $sic2, $sic3) = split (/,/, $dat); $dbh->do (" INSERT INTO sicc (sic, sic2, sic3) values (?,?,?) " , undef, $sic, $sic2, $sic3); print F1 "Enter new row of data: 'y' or 'n' \n"; chomp($flag = ); undef $sic, $sic2, $sic3, $dat; } # end of while for table sicc # Table permitno $f2=Y; print " Enter data for permitno table? 'y' or 'n'\n"; chomp($f2 = ); while ( uc($f2) eq 'Y') { print " Enter values for pn, datep separate with commas \n"; chomp($dat = ); ($pn, $datep) = split (/,/, $dat); $dbh->do (" INSERT INTO permitno (pn,datep) values (?,?) " , undef, $pn, $datep ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f2 = ); undef $pn, $datep, $dat; } # End of while for table permitno # Table equip $f3=Y; while ( uc($f3) eq 'Y') { print " Enter value for type \n"; chomp($dat = ); ($type) = split (/,/, $dat); $dbh->do (" INSERT INTO equip (type) values (?) " , undef, $type ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f3 = ); undef $type, $dat; } # End of while for table permitno # Table regs $f4 = Y; while ( uc($f4) eq 'Y') { print " Enter values cite and reg \n"; chomp($dat = ); ($cite, $reg) = split (/,/, $dat); $dbh->do (" INSERT INTO regs (cite, reg) values (?,?) " , undef, $cite, $reg ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f4 = ); undef $cite, $reg, $dat; } # End of while for table regs # Table refs $f5 = Y; while ( uc($f5) eq 'Y') { print " Enter value for ref \n"; chomp($dat = ); ($ref) = split (/,/, $dat); $dbh->do (" INSERT INTO refs (ref) values (?) " , undef, $ref ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f5 = ); undef $ref, $dat; } # End of while for table refs $sth=$dbh->prepare ("SELECT * FROM sicc"); $sth->execute (); $count = 0; while (my $ref = $sth ->fetchrow_hashref () ) { print F3 "Found a row: sic_id =$ref->{'sic_id'}, sic =$ref->{'sic'}, sic2= $ref->{'sic2'}, sic3=$ref->{'sic3'}\n"; ++$count; } $sth->finish(); print "$count rows total\n"; $sth=$dbh->prepare ("SELECT * FROM permitno"); $sth->execute (); $count = 0; while (my $ref = $sth ->fetchrow_hashref () ) { print F2 "Found a row: pn_id =$ref->{'pn_id'}, datep =$ref->{'datep'}, pn= $ref->{'pn'}\n"; ++$count; } $sth->finish(); print "$count rows total\n"; $sth=$dbh->prepare ("SELECT * FROM equip"); $sth->execute (); $count = 0
Error with code utilizing perl DBI & mysql ??
This is a bit strange, but I was writing code and adding some select statements that seemed to be working fine until about the 5 th one, ... then i get errors : I am a bit of a newbie at mysql & dbi. any help much appreciated Error: Cant set DBI::db-HASH(0x1b73b70->{Printerror}: unrecongnised attribute at C:/indogoperl/site/lib/DBI.pm line 441. Issuing rollback() for database handle being DESTROYd without explicit disconnect(). use DBI; my $dir = "C:/indigoperl/bin"; my $file1 = "out1.txt"; my $file2 = "out2.txt"; my $file3 = "out3.txt"; my $file4 = "out4.txt"; my $file5 = "out5.txt"; open(F1, ">$dir/$file1") or warn "Did not open $file1: $!\n"; open(F2, ">$dir/$file2") or warn "Did not open $file1: $!\n"; open(F3, ">$dir/$file3") or warn "Did not open $file1: $!\n"; open(F4, ">$dir/$file4") or warn "Did not open $file1: $!\n"; open(F5, ">$dir/$file5") or warn "Did not open $file1: $!\n"; my ($dbh, $sth, $count, $dsn, $user, $pwd); my ($flag, $sic, $sic2, $sic3); my ($cite, $reg); $dsn = "DBI:mysql:host=localhost;database=air"; $user = "root"; $pwd = "wick"; $dbh = DBI->connect($dsn, $user, $pwd,{Printerror => 0, RaiseError => 1}); # Table sicc $flag =Y; print " Enter data for sicc table? 'y' or 'n'\n"; chomp($flag = ); while ( uc($flag) eq 'Y') { print " Enter values for sic, sic2, sic3 separate with commas \n"; chomp($dat = ); ($sic, $sic2, $sic3) = split (/,/, $dat); $dbh->do (" INSERT INTO sicc (sic, sic2, sic3) values (?,?,?) " , undef, $sic, $sic2, $sic3); print F1 "Enter new row of data: 'y' or 'n' \n"; chomp($flag = ); undef $sic, $sic2, $sic3, $dat; } # end of while for table sicc # Table permitno $f2=Y; print " Enter data for permitno table? 'y' or 'n'\n"; chomp($f2 = ); while ( uc($f2) eq 'Y') { print " Enter values for pn, datep separate with commas \n"; chomp($dat = ); ($pn, $datep) = split (/,/, $dat); $dbh->do (" INSERT INTO permitno (pn,datep) values (?,?) " , undef, $pn, $datep ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f2 = ); undef $pn, $datep, $dat; } # End of while for table permitno # Table equip $f3=Y; while ( uc($f3) eq 'Y') { print " Enter value for type \n"; chomp($dat = ); ($type) = split (/,/, $dat); $dbh->do (" INSERT INTO equip (type) values (?) " , undef, $type ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f3 = ); undef $type, $dat; } # End of while for table permitno # Table regs $f4 = Y; while ( uc($f4) eq 'Y') { print " Enter values cite and reg \n"; chomp($dat = ); ($cite, $reg) = split (/,/, $dat); $dbh->do (" INSERT INTO regs (cite, reg) values (?,?) " , undef, $cite, $reg ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f4 = ); undef $cite, $reg, $dat; } # End of while for table regs # Table refs $f5 = Y; while ( uc($f5) eq 'Y') { print " Enter value for ref \n"; chomp($dat = ); ($ref) = split (/,/, $dat); $dbh->do (" INSERT INTO refs (ref) values (?) " , undef, $ref ); print "Enter new row of data: 'y' or 'n' \n"; chomp($f5 = ); undef $ref, $dat; } # End of while for table refs $sth=$dbh->prepare ("SELECT * FROM sicc"); $sth->execute (); $count = 0; while (my $ref = $sth ->fetchrow_hashref () ) { print F3 "Found a row: sic_id =$ref->{'sic_id'}, sic =$ref->{'sic'}, sic2= $ref->{'sic2'}, sic3=$ref->{'sic3'}\n"; ++$count; } $sth->finish(); print "$count rows total\n"; $sth=$dbh->prepare ("SELECT * FROM permitno"); $sth->execute (); $count = 0; while (my $ref = $sth ->fetchrow_hashref () ) { print F2 "Found a row: pn_id =$ref->{'pn_id'}, datep =$ref->{'datep'}, pn= $ref->{'pn'}\n"; ++$count; } $sth->finish(); print "$count rows total\n"; $sth=$dbh->prepare ("SELECT * FROM equip"); $sth->execute (); $count = 0; while (my $ref = $sth ->fetchrow_hashref () ) { print F3 "Row $count: type_id =$ref->{'type_id'}, type =$ref->{'type'}\n"; ++$count; } $sth->finish(); print "$count rows total\n"; $sth=$dbh->prepare ("SELECT * FROM regs"); $sth->execute (); $count = 0; while (my $ref = $sth ->fetchrow_hashref () ) { print F4 "Row $count: reg_id =$ref->{'reg_id'}, cite =$ref->{'cite'}, reg =$ref->{'reg'}\n"; ++$count; } $sth->finish(); print "$count rows total\n"; $sth=$dbh->prepare ("SELECT * FROM refs"); $sth->execute (); $count = 0; while (my $ref = $sth ->fetchrow_hashref () ) { print F5 "Row $count: ref_id =$ref->{'ref_id'}, ref =$ref->{'ref'}\n"; ++$count; } $sth->finish(); print "$count rows total\n"; close (F1); close (F2); close (F3); close (F4); close (F5); $dbh->disconnect (); $dbh->disconnect (); $dbh->disconnect (); $dbh->disconnect (); $dbh->disconnect (); $dbh->disconnect (); exit (0); = Regards, Investorclb __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ ---
RE: Perl DBI & DBD & Show Table Modules Best Dir Install Choices!
Bob, As soon as you compiled and installed perl itself, the Config module of perl will tell any Makefile.PL driven package where to install itself: under the directory structure of perl. I don't know for sure, but I suppose DBI DBD installs via Makefile.pl script... If not, do a perl -V to see where @INC is pointing to. somewhere in that structure your DBI should be installed, so that all programs in Perl can find it without messing with @INC. Cheers, Guus PS: database,sql,query,table for the antispam rules... > -Original Message- > From: bobby [mailto:bobby]On Behalf Of rjtalbo > Sent: Saturday, September 15, 2001 9:08 PM [snipped] > Where is the most common and preferred Location for the Install?? [omitted] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI & DBD & Show Table Modules Best Dir Install Choices!
Now that I have the correct Install procedure for the Perl DBI and MySQL DBD, Where is the most common and preferred Location for the Install?? I was considering the following. /usr/local/ /usr/local/share/ /usr/share/mysql /usr/share/perl5/ Sometimes a script or program will expect to see these modules in a particular Dir.. I am just thinking ahead to make my progamming a bit easier I had made changes and reinstalled java JDK for just such a reason. Please replly with suggestions and personal setup prefrences. If you would include your reasoning that would alson be appeciatied.. Bob T. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI: Same column name in different tables problem
Or the perl way: my %row; @row{'f_handle','a_handle'} = $sth->fetchrow_array(); $row{f_handle} or $row{a_handle}are now set properly. ( if you really want a reference, you can say 'my $row = {}; @{$row}{'f_handle. ) dpk - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Philip Mak" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, September 07, 2001 10:48 PM Subject: Re: Perl DBI: Same column name in different tables problem > On Fri, Sep 07, 2001 at 10:03:03PM -0700, Philip Mak wrote: > > Let's say I performed the following query using Perl DBI: > > > > $row = $dbh->selectrow_hashref(<<"~"); > > SELECT fanfics.handle, authors.handle > > FROM fanfics, authors > > WHERE fanfics.aid = authors.aid > > ~ > > > > I won't be able to access both fanfics.handle and authors.handle > > this way, because they're called "handle". I would like to be able > > to access them e.g. by doing $row->{fanfics.handle} and > > $row->{authors.handle}, or something like that. > > > > What workarounds have people found for this problem? > > Use a column alias: > > SELECT fanfics.handle as f_handle, authors.handle as a_handle > FROM fanfics, authors >WHERE fanfics.aid = authors.aid > > Then refer to them as $row->{a_handle} or $row->{f_handle}. > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.41-max: up 2 days, processed 42,300,526 queries (237/sec. avg) > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI: Same column name in different tables problem
On Fri, Sep 07, 2001 at 10:03:03PM -0700, Philip Mak wrote: > Let's say I performed the following query using Perl DBI: > > $row = $dbh->selectrow_hashref(<<"~"); > SELECT fanfics.handle, authors.handle > FROM fanfics, authors > WHERE fanfics.aid = authors.aid > ~ > > I won't be able to access both fanfics.handle and authors.handle > this way, because they're called "handle". I would like to be able > to access them e.g. by doing $row->{fanfics.handle} and > $row->{authors.handle}, or something like that. > > What workarounds have people found for this problem? Use a column alias: SELECT fanfics.handle as f_handle, authors.handle as a_handle FROM fanfics, authors WHERE fanfics.aid = authors.aid Then refer to them as $row->{a_handle} or $row->{f_handle}. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 2 days, processed 42,300,526 queries (237/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI: Same column name in different tables problem
Let's say I performed the following query using Perl DBI: $row = $dbh->selectrow_hashref(<<"~"); SELECT fanfics.handle, authors.handle FROM fanfics, authors WHERE fanfics.aid = authors.aid ~ I won't be able to access both fanfics.handle and authors.handle this way, because they're called "handle". I would like to be able to access them e.g. by doing $row->{fanfics.handle} and $row->{authors.handle}, or something like that. What workarounds have people found for this problem? __ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI -- How to select all returned values into an array
On Sat, Aug 25, 2001 at 09:24:25PM -0500, Paul DuBois wrote: > At 10:07 AM -0700 8/24/01, Katherine Porter wrote: > >For single values I usually use this DBI function and query: > > > > my $val =3D $dbh->selectrow_array("SELECT value FROM tab1 WHERE test=3D= > >2"); > > > >However, what if I want to store a bunch of values into an array? > > > > my @vals =3D $dbh->?("SELECT value FROM tab1 WHERE test > 10"); > > > >What's the syntax I'm missing above? Any help appreciated! > > my $ref = $dbh->selectcol_arrayref (single-column query); > my @val = (defined ($ref) ? @{$ref} : () ); Or just: my @val = @{ $dbh->selectcol_arrayref(single-column query) || []}; And if you have RaiseError set then you don't need the '|| []' part. Also, from DBI 1.20 onwards you can now say $dbh->selectcol_arrayref("select f1, f2 from ...", { Columns=>[1,2] }); and have all the fields flattened into a list. Very handy when building a hash. But then for building a hash DBI 1.20 also has selectall_hashref() :-) "The Perl DBI - One call does it all" - almost :-) Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI -- How to select all returned values into an array
On Fri, Aug 24, 2001 at 10:07:59AM -0700, Katherine Porter wrote: > For single values I usually use this DBI function and query: > > my $val =3D $dbh->selectrow_array("SELECT value FROM tab1 WHERE test=3D= > 2"); > > However, what if I want to store a bunch of values into an array? > > my @vals =3D $dbh->?("SELECT value FROM tab1 WHERE test > 10"); > > What's the syntax I'm missing above? Any help appreciated! Glance over this for a minute (stolen from an example in perl cookbook): my $sth = $dbh->prepare( qq{select * from invoice} ); $sth-> execute; while( (my @row) = $sth->fetchrow_array ) { print join(qq{, }, map {defined $_ ? $_ : qq{(null)} } @row, qq{\n}; } -- Any technology distinguishable from magic is insufficiently advanced. TopQuark Software & Serv. Contract programmer, server bum. [EMAIL PROTECTED]Give up Spammers; I use procmail. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI -- How to select all returned values into an array
At 10:07 AM -0700 8/24/01, Katherine Porter wrote: >For single values I usually use this DBI function and query: > > my $val =3D $dbh->selectrow_array("SELECT value FROM tab1 WHERE test=3D= >2"); > >However, what if I want to store a bunch of values into an array? > > my @vals =3D $dbh->?("SELECT value FROM tab1 WHERE test > 10"); > >What's the syntax I'm missing above? Any help appreciated! > >[.kate] my $ref = $dbh->selectcol_arrayref (single-column query); my @val = (defined ($ref) ? @{$ref} : () ); -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI -- How to select all returned values into an array
For single values I usually use this DBI function and query: my $val =3D $dbh->selectrow_array("SELECT value FROM tab1 WHERE test=3D= 2"); However, what if I want to store a bunch of values into an array? my @vals =3D $dbh->?("SELECT value FROM tab1 WHERE test > 10"); What's the syntax I'm missing above? Any help appreciated! [.kate] ___ Visit http://www.visto.com. Find out how companies are linking mobile users to the enterprise with Visto. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql - Perl -DBI cannot work??
I just installed DBI module and try to run a cgi script from browser to access Mysql Database. The browser responsed with "Internal Error" >From the /var/httpd/error_log it says Caught SIGTERM, shutting down Warning: user directive in requires SUEXEC wrapper. What is that means?? Can anyone give me a hint? Thanks in Advnace - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Writing 100+ fields with Perl DBI
You are correct, of course, Werner. I was thinking along the wrong path - of trying to write AS I looped (as I was doing with the text file), rather than using the loop to build the INSERT statement and THEN writing to the database. Christopher Jones also provided me with a sample of very basic code that built the INSERT statement using simple concatenation - and using the loop that I'd already built. One important bit was using 'chop' to remove the final comma in each of the arrays that I built. Thank you (and Christopher), Nelson >I don't see the problem. If you have a table with 120 columns and >need to populate it, do you not want to do it with one insert >statement? What else? > -- === Nelson Goforth Lighting for Moving Pictures phone: 01.303.322.5042 pager: 01.303.634.9733 resume: http://www.earthnet.net/~ngoforth/film - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Writing 100+ fields with Perl DBI
I don't see the problem. If you have a table with 120 columns and need to populate it, do you not want to do it with one insert statement? What else? I am not very familiar with perl, but in php it would be easy for me to construct the insert statement: You can read the columns names into one array; the values are in the array $HTTP_POST_VARS; so you loop over the arrays and build parts of your insert statement -- you only have to make sure you get the correspondence correct. If you name the columns accordingly, you don't even have to ask mysql about them. So you say: $q = 'INSERT INTO table ('; for ($i = 1; $i < 120; $i++) { $q .= 'Q' . $i . ', '; } $q = substr($q, 0, -2); //take off the last ', ' $q .= ') VALUES ('; while(list(, $val) = each($HTTP_POST_VARS)) { $q .= "'$val', "; } $q = substr($q, 0, -2); //take off the last ', ' $q .= ')'; //you are done, hoopefully, with something like //INSERT INTO table (..., ...) VALUES ('...', ...) Nelson Goforth schrieb am Dienstag, 31. Juli 2001, 20:47:31: > In my application I'm writing up to 120 fields of data in one swoop > (the results of a sort of survey). I'm using Perl DBI with MySQL. > Each survey has several fields of data like 'name' (stored as > parameters in the CGI object $asmt) and then up to 100 actual survey > questions (Q1, Q2, etc). Until now I've just written one line of > data to a text file, like: > # Write basic data > print FILE asmt -> param('name') . "\t"; > print FILE asmt -> param('city') . "\t"; > $v = 1; > #write response to each question (Q1, Q2 etc) > while ($v <= $questions) { > $x = "Q" . $v; > print FILE $asmt -> param($x). "\t"; > $v++; > } > And this works fine for the text file - but from what I've seen I > probably can't carry over the iteration through the (up to) 100 > actual survey questions. I named the MySQL fields 'q1', 'q2', > anticipating some way to do a loop, but I can't see it. > Does anyone have any suggestions of how best to do this? The thought > of one huge INSERT statement makes my eyes glaze over - I'm hoping > there is a more elegant alternative. > Thanks, > Nelson -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Writing 100+ fields with Perl DBI
On Tue, 31 Jul 2001, Nelson Goforth wrote: > > And this works fine for the text file - but from what I've seen I > probably can't carry over the iteration through the (up to) 100 > actual survey questions. I named the MySQL fields 'q1', 'q2', > anticipating some way to do a loop, but I can't see it. > > Does anyone have any suggestions of how best to do this? The thought > of one huge INSERT statement makes my eyes glaze over - I'm hoping > there is a more elegant alternative. > More elegant, and more fun. Here's one way. It assumes the order of your columns is name. city, Q1, Q2, Q3, ... Q100. If you have an auto_incrementing id column in there, you'll want to push a 'NULL' into your @values array in the appropriate place. We're also assuming your survey's Q1..Q100 data type is numeric rather than string. ### my @values; for (qw(name city ) ) { push @values, $dbh->quote($asmt->param($_)); } for (1..100) { push @values, $asmt->param("Q$_"); } my $values = join (",",@values); $dbh->do("insert into your_table values ($values)") or die("D'oh! " . $DBI::errstr; ### HTH David Mintz Spanish Interpreter US District Court, Southern District of New York Web Design & Hosting http://www.dmintzweb.com/ Personal http://www.panix.com/~dmintz/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Writing 100+ fields with Perl DBI
In my application I'm writing up to 120 fields of data in one swoop (the results of a sort of survey). I'm using Perl DBI with MySQL. Each survey has several fields of data like 'name' (stored as parameters in the CGI object $asmt) and then up to 100 actual survey questions (Q1, Q2, etc). Until now I've just written one line of data to a text file, like: # Write basic data print FILE asmt -> param('name') . "\t"; print FILE asmt -> param('city') . "\t"; $v = 1; #write response to each question (Q1, Q2 etc) while ($v <= $questions) { $x = "Q" . $v; print FILE $asmt -> param($x). "\t"; $v++; } And this works fine for the text file - but from what I've seen I probably can't carry over the iteration through the (up to) 100 actual survey questions. I named the MySQL fields 'q1', 'q2', anticipating some way to do a loop, but I can't see it. Does anyone have any suggestions of how best to do this? The thought of one huge INSERT statement makes my eyes glaze over - I'm hoping there is a more elegant alternative. Thanks, Nelson -- === Nelson Goforth Lighting for Moving Pictures phone: 01.303.322.5042 pager: 01.303.634.9733 resume: http://www.earthnet.net/~ngoforth/film - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI to MySQL -- Passing info to query
r> I was not aware of placeholders, and the benifits of using them instead of r> using $dbh->quote(). It doesnt make sence that the DBI version of quote isnt r> as thorough as having the code behind placeholding do it. I'm not DBI guru but I've seen that quote() doesn't quote some data correctly. Replacing it with placeholders fixed a problem. BTW fact that quote is not releable is mentioned in DBI docs (perldoc DBI). This is quote from there: Quote will probably not be able to deal with all pos- sible input (such as binary data or data containing newlines), and is not related in any way with escaping or quoting shell meta-characters. There is no need to quote values being used with the Placeholders and Bind Values entry elsewhere in this document. r> But anyhow, I have r> a few questions as to how this works. Here is an example from the Perl r> DBH::mysql docs: r> my @names = ['Flaherty', 'Jones', 'Smith']; r> my $sth = $dbh->prepare("UPDATE contact r> SET phone = '555-1212' r> WHERE last_name = ?"); r> $sth->execute(@names); r> $sth->finish; Where did you found this example? I've not found it neither in man DBI nor in man DBD::mysql. AFAIK this example is just incorrect. For each placeholder you pass to 'execute' only one single scalar. Check man DBI chapter (Placeholders and Bind Values) for info on placeholders usage. It has some examples. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.com/) | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php