Re: Bareword DBI::SQL_INTEGER not allowed while strict subs
If you are using DBI::SQL_INTEGER, ':sql_types' needs to be imported, i.e. use DBI qw(:sql_types); On Sun, 2005-12-11 at 18:45 -0500, Patrick Danley wrote: I am new to DBI, Perl, and SQL and I am currently trying to piece together a group of bioinformatic programs. In running the database element of the package, I recieve a number of : Bareword DBI::SQL_INTEGER not allowed while strict subs in use errors (a full listing of the error is pasted below). Does anyone have experience with this error and/or suggestions on how i might resolve it? I am truly a novice, 3 days in, so simple explanations would be especially appreciated. I'm running : Perl 5.8.6 dbi-pm 5.8.6 PostgreSQL -perl-586 Mac OS 10.4.2 Bareword DBI::SQL_INTEGER not allowed while strict subs in use at /sw/lib/perl5/5.8.6/darwin-thread-multi-2level/DBD/Pg.pm line 1168. Bareword DBI::SQL_SMALLINT not allowed while strict subs in use at /sw/lib/perl5/5.8.6/darwin-thread-multi-2level/DBD/Pg.pm line 1168. Bareword DBI::SQL_DECIMAL not allowed while strict subs in use at /sw/lib/perl5/5.8.6/darwin-thread-multi-2level/DBD/Pg.pm line 1168. Bareword DBI::SQL_FLOAT not allowed while strict subs in use at / sw/lib/perl5/5.8.6/darwin-thread-multi-2level/DBD/Pg.pm line 1168. Bareword DBI::SQL_REAL not allowed while strict subs in use at / sw/lib/perl5/5.8.6/darwin-thread-multi-2level/DBD/Pg.pm line 1168. Bareword DBI::SQL_DOUBLE not allowed while strict subs in use at / sw/lib/perl5/5.8.6/darwin-thread-multi-2level/DBD/Pg.pm line 1168. Bareword DBI::SQL_NUMERIC not allowed while strict subs in use at /sw/lib/perl5/5.8.6/darwin-thread-multi-2level/DBD/Pg.pm line 1168. Compilation failed in require at /Users/TheBucket/lib/Perl/PartiGene/ PG_db.pm line 6. BEGIN failed--compilation aborted at /Users/TheBucket/lib/Perl/ PartiGene/PG_db.pm line 6. Compilation failed in require at /Users/TheBucket/genome/bin/ PartiGene_db.pl line 4. BEGIN failed--compilation aborted at /Users/TheBucket/genome/bin/ PartiGene_db.pl line 4. Patrick Danley, Ph.D. Postdoctoral Researcher Department of Biology University of Maryland phone 301.405.8303 fax 301.314.9358 email [EMAIL PROTECTED] http://www.life.umd.edu/biology/shawlab/patrickdanley -- Scott T. Hildreth [EMAIL PROTECTED]
Re: DBI:Oracle failure ORA-12154
Jeffrey == Jeffrey Seger [EMAIL PROTECTED] writes: Jeffrey Is $ORACLE_HOME set to the correct path? Now it is. Jeffrey If the tnsnames.ora is not in $ORACLE_HOME/network/admin/, is Jeffrey $TNS_ADMIN set to the actual location of the file? Jeffrey On 09 Dec 2005 15:37:33 +0200, Claude [EMAIL PROTECTED] wrote: I run locally a client script which fails with the following message: ORA-12154: TNS:could not resolve service name (DBD ERROR: OCIServerAttach) at ... DBI::connect('DBI','dbi:Oracle:abc','batman','secret','HASH(0x82d7910)') called at ... The datasource seems ok to me, and I checked from 'tnsnames.ora' that the port is set up to the default 1521 and the name is 'abc'. That was not enough, and the ds syntax above was not correct. I managed to get the connection working by setting the ds to 'dbi:Oracle:host=127.0.0.1;port=1521;sid=abc'. Tx for your time, Jeffrey! -- Claude
Re: Oracle failure ORA-12154
Ron == Ron Reidy [EMAIL PROTECTED] writes: Ron Can you connect to this database using SQL*Plus in the same manner? Ron From the error docs: Ron $ oerr ora 12154 Ron 12154, 0, TNS:could not resolve service name Ron // *Cause: The service name specified is not defined correctly in the Ron // TNSNAMES.ORA file. Ron // *Action: Make the following checks and correct the error: Ron // - Verify that a TNSNAMES.ORA file exists and is in the Ron proper Ron // place and accessible. See the operating system specific Ron manual Ron // for details on the required name and location. Ron // - Check to see that the service name exists in one of the Ron // TNSNAMES.ORA files and add it if necessary. Ron // - Make sure there are no syntax errors anywhere in the Ron file. Ron // Particularly look for unmatched parentheses or stray Ron characters. Ron // Any error in a TNSNAMES.ORA file makes it unusable. See Ron // Chapter 4 in the SQL*Net V2 Administrator's Guide. If Ron // possible, regenerate the configuration files using the Ron Oracle Ron // Network Manager. That helped, tx, Ron! [...] Ron ORA-12154: TNS:could not resolve service name (DBD ERROR: Ron OCIServerAttach) at ... Ron DBI::connect('DBI','dbi:Oracle:abc','batman','secret','HASH(0x82d7910)') Ron called at ... [...] -- Claude
RE: DBD-ODBC-1.13 --FOXPRO fetchrow_array question
I'm guessing not. I see an error and I'm guessing that it detects the column type is not correct. Can you set DBI-trace to 9 and send me the output? Jeff -Original Message- From: yongming [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 9:36 PM To: [EMAIL PROTECTED] Subject: DBD-ODBC-1.13 --FOXPRO fetchrow_array question Hello Jeff, I am using the ODBC DBI to access FOXPRO (visual foxpro 6) tables. Somehow in one table some numberical fields mix numerical data with string like ***,***,***. The fetchrow_array method will fail on that row and I won't be able to fetch the next row with the same select statement. When I try to browse the data using WINSQL with the same ODBC driver, I will get the offending row with those fields filled with error togather with the rest of the result set. I am just wondering if the DBD-OBDC module can allow me to do the same thing as the WINSQL does: still return the offending rows and allow me to fetch the next row with the same sql statement. Thank you for your help. Cheers, Yongming -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 12/2/2005
RE: select placeholder
John wrote: Can anyone see the error in the following code. It has to do with the placeholder in the select statement. Substitution fails to occur at the $sth1-execute... statement script dies at while (my $rowref If I replace the placeholder with a literal it works as expected. [code] snipped I'm not sure if this is relevant because of differences in the database type, but here goes... We had a similar problem with an Oracle database, and from an item on this list I found the following which fixed our issue. CHAR column values are padded with spaces to the length of the column. When compared with a literal, the SQL engine automatically adds the padding to the literal. When compared with a placeholder, the blank padded semantics are not available unless the placeholder type is CHAR. Evidently this is not unique to the Perl DBI, it was documented in the Oracle OCI manual. Is it possible that SQLite is similar? If so you may need something similar to this: use DBD::Oracle qw(:ora_types); ... # after connect but before prepare $dbh-{ora_ph_type} = ORA_CHAR; FWIW, Hope it helps. Paula
DBD::mysql and profiling
Hi, I'm writing a program that uses DBI/Mysql and does a *lot* of selects and inserts/updates. (One short run did 2809 and 5714 respectively). I've used Devel::DProf with it - in an attempt to get an idea of any performance problems - and am getting some interesting data on test runs. One thing that looks odd is: Total Elapsed Time = 362 seconds User+System Time = 3.2 seconds Now I'm not running anything special apart from the program here. Is it likely to be the MySQL engines themselves, with all those SQL statements, taking all that time? I did have a MySQL GUI running for this but running with this turned off brought the Total elapsed time down to 334 seconds. Not a great improvement. The MySQL table being used is near 3 records. (Am running ActivePerl 5.8.6 on XP) TIA for any help, Henry ___ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
Apache::DBI::Cache
Has anyone got any experience with Apache::DBI::Cache that they might like to share? Tim. http://search.cpan.org/~opi/Apache-DBI-Cache/
Re: DBD::mysql and profiling
http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/sld022.htm http://search.cpan.org/~timb/DBI/lib/DBI/Profile.pm Tim. On Mon, Dec 12, 2005 at 03:03:09PM +, Henry McGuinness wrote: Hi, I'm writing a program that uses DBI/Mysql and does a *lot* of selects and inserts/updates. (One short run did 2809 and 5714 respectively). I've used Devel::DProf with it - in an attempt to get an idea of any performance problems - and am getting some interesting data on test runs. One thing that looks odd is: Total Elapsed Time = 362 seconds User+System Time = 3.2 seconds Now I'm not running anything special apart from the program here. Is it likely to be the MySQL engines themselves, with all those SQL statements, taking all that time? I did have a MySQL GUI running for this but running with this turned off brought the Total elapsed time down to 334 seconds. Not a great improvement. The MySQL table being used is near 3 records. (Am running ActivePerl 5.8.6 on XP) TIA for any help, Henry ___ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
unstable proxy
Hi, I start a dbiproxy with the following command. I checked that is free. My questions are: - Where does the intriguing port 3209 value below comes from? It is incremented by 1 at every call of the client. Is it relevant or is that a well-known problem? - I could not find the log file anywhere. Would its creation be skipped for some reason? - Would these be fixed in later versions? Running a remote client executing no or a single SQL statement at port is accepted by the proxy, as the printout below says (although the port is wrong). It sometimes crashes without warning. I tested the same code code earlier locally with DBI:Oracle and it runs fine. ---clip-- [EMAIL PROTECTED] proxy]# dbiproxy --localport= --logfile=log.txt --pidfile=pid --debug [1] 12516 [EMAIL PROTECTED] proxy]# Mon Dec 12 16:09:32 2005 debug, 0, Server starting in operation mode threads Mon Dec 12 16:09:32 2005 notice, 0, Server starting Mon Dec 12 16:09:32 2005 debug, 0, Writing PID to pid Mon Dec 12 16:09:47 2005 debug, 0, Connection from 172.21.190.29, port 3209 Mon Dec 12 16:09:47 2005 debug, 0, Child clone: DBI::ProxyServer=HASH(0x8357034) Mon Dec 12 16:09:47 2005 debug, 1, New child starting (DBI::ProxyServer=HASH(0x836c408)). Mon Dec 12 16:09:47 2005 debug, 1, Accepting client from 1.2.3.4, port 3209 Mon Dec 12 16:09:47 2005 debug, 1, Client logs in: Application dbi:Oracle:host=127.0.0.1;port=1521;sid=abc, version 0.2004, user batman Mon Dec 12 16:09:47 2005 debug, 1, Connecting to dbi:Oracle:host=127.0.0.1;port=1521;sid=abc as batman Mon Dec 12 16:09:47 2005 debug, 1, Accepting client Mon Dec 12 16:09:47 2005 debug, 1, Client executes method Version Mon Dec 12 16:09:47 2005 debug, 1, Client executes method CallMethod Mon Dec 12 16:09:47 2005 debug, 1, CallMethod: = DBI::ProxyServer::db=HASH(0x857f848),STORE,AutoCommit,0 Mon Dec 12 16:09:47 2005 debug, 1, CallMethod: = 1 Mon Dec 12 16:09:47 2005 debug, 1, Client executes method DestroyHandle Mon Dec 12 16:09:47 2005 debug, 1, Child terminating. [EMAIL PROTECTED] proxy]# [EMAIL PROTECTED] proxy]# dbiproxy --ver DBI::ProxyServer 0.3005, Copyright (C) 1998, Jochen Wiedmann [EMAIL PROTECTED] proxy]# ls pid ---clip-- -- Claude
Re: select placeholder
Capacio, Paula J wrote: John wrote: Can anyone see the error in the following code. It has to do with the placeholder in the select statement. Substitution fails to occur at the $sth1-execute... statement script dies at while (my $rowref If I replace the placeholder with a literal it works as expected. [code] snipped I'm not sure if this is relevant because of differences in the database type, but here goes... We had a similar problem with an Oracle database, and from an item on this list I found the following which fixed our issue. CHAR column values are padded with spaces to the length of the column. When compared with a literal, the SQL engine automatically adds the padding to the literal. When compared with a placeholder, the blank padded semantics are not available unless the placeholder type is CHAR. Evidently this is not unique to the Perl DBI, it was documented in the Oracle OCI manual. Is it possible that SQLite is similar? If so you may need something similar to this: use DBD::Oracle qw(:ora_types); ... # after connect but before prepare $dbh-{ora_ph_type} = ORA_CHAR; FWIW, Hope it helps. Paula Thanks Paula I don't think that is the issue, in fact I don't think it is a DBI/SQL issue at all because when I do this: [code] { #$custnum = shift @duplist; #redo if int($custnum) == 0; $custnum = 000281610; ===### $sth1-execute( $custnum ) or die couldn't execute... . DBI-errstr; while (my $rowref = $sth1-fetchrow_arrayref) { ($asset, $custnum, $branch, $customer, $address, $town, $postcode) = @$rowref; [/code] it works. I will look into how I load @duplist and if I have further problems I will take them elsewhere. -- Regards John McMahon ([EMAIL PROTECTED]) -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/198 - Release Date: 12/12/2005
RE: select placeholder
I take it the real problem is that your query is not returning any data. Could it be that you're loading @duplist from a file, and it ends with a newline? Try adding: chomp $custnum; before your execute() call. -Original Message- From: John [mailto:[EMAIL PROTECTED] Sent: Monday, December 12, 2005 6:14 PM Cc: dbi-users@perl.org Subject: Re: select placeholder I don't think that is the issue, in fact I don't think it is a DBI/SQL issue at all because when I do this: [code] { #$custnum = shift @duplist; #redo if int($custnum) == 0; $custnum = 000281610; ===### $sth1-execute( $custnum ) or die couldn't execute... . DBI-errstr; while (my $rowref = $sth1-fetchrow_arrayref) { ($asset, $custnum, $branch, $customer, $address, $town, $postcode) = @$rowref; [/code] it works. I will look into how I load @duplist and if I have further problems I will take them elsewhere. -- Regards John McMahon ([EMAIL PROTECTED])
RE: DBD-ODBC-1.13 --FOXPRO fetchrow_array question
Please try issuing another fetchrow_array, if the $sth-err is SQL-01S01, just to see. It could be that you can safely 'ignore' this error and use it as a warning. Jeff From: Yongming Xu [mailto:[EMAIL PROTECTED] Sent: Monday, December 12, 2005 4:41 PM To: Jeff Urlwin Subject: Re: DBD-ODBC-1.13 --FOXPRO fetchrow_array question Jeff, Here is the error spooled on the screen: Z:\pl\dndperl odbctest.pl DBD::ODBC::st fetchrow_array failed: [Microsoft][ODBC Visual FoxPro Driver]Error in row (SQL-01S01) [Microsoft][ODBC Visual FoxPro Driver]Error: Column 3 ( convertednotional ) (SQL-22003)(DBD: st_fetch/SQLFetch err=-1) a t odbctest.pl line 61. I attached the test perl DBI script and trace level 9 file for your review. In the test script, I didn't try to issue fatchrow_array after the fail as I posted in my original question. thanks very much for your help. yongming On 12/12/05, Jeff Urlwin [EMAIL PROTECTED] wrote: I'm guessing not. I see an error and I'm guessing that it detects the column type is not correct. Can you set DBI-trace to 9 and send me the output? Jeff -Original Message- From: yongming [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 9:36 PM To: [EMAIL PROTECTED] Subject: DBD-ODBC-1.13 --FOXPRO fetchrow_array question Hello Jeff, I am using the ODBC DBI to access FOXPRO (visual foxpro 6) tables. Somehow in one table some numberical fields mix numerical data with string like ***,***,***. The fetchrow_array method will fail on that row and I won't be able to fetch the next row with the same select statement. When I try to browse the data using WINSQL with the same ODBC driver, I will get the offending row with those fields filled with error togather with the rest of the result set. I am just wondering if the DBD-OBDC module can allow me to do the same thing as the WINSQL does: still return the offending rows and allow me to fetch the next row with the same sql statement. Thank you for your help. Cheers, Yongming -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 12/2/2005