RE: need some advice
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
DMuey
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
On 08 Mar 2003 16:06:35 -0600 Rob Benton
[EMAIL PROTECTED] wrote:
I know there's probably a better way to do what I'm trying to
accomplish so I'd like some opinions.
I have 2 cgi programs. One has a form full of text boxes
sending data
to the 2nd program. What I need is an easy way to add the
single-quotes to each variable that is not an integer
before sending
the statement to my Oracle database.
So say I had -
$name
$address
$zip
NAME and ADDRESS being character and ZIP being integer
variables, and
I wanted to make sure the went in as
Using an NUMBER column for zipcodes is just begging for
problems. For example, what about Canadian addresses?
$statement = qw/select * from otable where name='$name' and
address='$address' and zip=$zip/; $sth = $dbh-prepare($statement);
I need a way (hopefully not messy) to interpolate those variables
into the sql statement but also have them single-quoted. I could
probably work something out but I usually find that there's
an easier
way to do things after I have made a mess of them.
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate mail list. ** Ask Smarter:
http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
RE: need some advice
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
[EMAIL PROTECTED] wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values were
properly quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it will take care of
any charcaters that could casue problems and not just single quotes ( IE (), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values.
Oddly enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including
DBD::Oracle), placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate
mail list. ** Ask Smarter:
http://www.catb.org/~esr/faqs/smart- questions.html
Give a
hobbit a fish and he eats fish for a
day.
Give a hobbit a ring and he eats fish for an age.
RE: need some advice
OK I see what you're doing. What I would need to do is just
tweak that a little. I would need something like:
foearch ($cgi-param() ) {
foreach $p(@params) {
if(param($p) ...) {
$dbh-(param($p));
To do param() instead of $cgi-param() you may need to do
use CGI qw/:standard/;
Not sure if
if($cgi-param($p) ...) would work or not, it may though.
DMuey
if ( some test on the type here ) {
$dbh-quote($_);
}
$sql .= $_,;
$val .= ?,;
push(@{$array_ref}, $cgi-param($_));
}
Does that make any sense?
On Mon, 2003-03-10 at 12:45, Ian Harisay wrote:
Here, I'll try and be more helpful than berating. I'm
sorry I'm having
a bad day. The code below is not totally complete. Some error
handling needs to be added for sure. I would consider this to be
psuedo
code simply because I did not check my work for syntax. I think it
fairly accruate though.
## this assumes you want to insert all form elements
my $cgi = CGI-new();
my $dbh = DBI-connect(connect info goes here);
my $sql = INSERT INTO table (;
my $val = values(;
my $array_ref = [];
## build your statement assuming the form element names are ## the
same as your column names. foreach ( $cgi-param() ){
$sql .= $_,;
$val .= ?,;
push(@{$array_ref}, $cgi-param($_));
}
$sql =~ s/,$/) /; ## strip the last comma and add a closing paren.
$val =~ s/,$/)/;
my $sth = $dbh-prepare($sql.$val);
my $result = $sth-execute($array_ref);
$dbh-commit(); ## if autocommit is not on.
Ian Harisay wrote:
Are you not listening to these people giving you helpful advice?
Use
the placeholders. I gaurantee you will be glad you did.
C'mon man!!!
Embrace the change.
Rob Benton wrote:
There won't be any ['] (read that as reg. expression)
inside the
fields so that's not a problem. All I need to do is
decide whether
to single-quote the variable based on its data-type. Also this
will just be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
[EMAIL PROTECTED]
wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values
were properly
quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it
will take care of any charcaters that could casue
problems and not
just single quotes ( IE (), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting
values. Oddly
enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including DBD::Oracle),
placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in
the fine DBI
and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the
appropriate mail
list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-
questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
RE: apache config problems
On 06 Mar 2003 11:11:15 -0600, Rob Benton
[EMAIL PROTECTED] wrote:
INSTALLED:
apache 1.3.27
perl 5.6.1
redhat 7.3
For some reason perl is mangling the environment variables
set in the
httpd.conf file:
-httpd.conf-
SetEnv
LD_LIBRARY_PATH=/usr/local/lib:/usr/local/atf/lib:/usr/local/hydra/lib
:/opt/oracle/lib
SetEnv JAVA_HOME=/usr/java/j2sdk1.4.0_03
SetEnv ORACLE_HOME=/opt/oracle
Then I use this loop inside a cgi script to test the vars:
foreach $k
(keys %ENV) {
Try it with out the printf :: print -$k-$ENV{$k}- br\n;
printf(%s:\t%sbr, $k, $ENV{$k});
}
and it produces this output:
LD_LIBRARY_PATH:
/usr/local/lib:/usr/local/atf/lib:/usr/local/hydra/lib:/opt/oracle/lib
=
JAVA_HOME: /usr/java/j2sdk1.4.0_03=
ORACLE_HOME: /opt/oracle=
Why is that '=' getting appended to the end of the variables?
A very good question why it is on the end, but it appears
that your syntax is not correct in the config file. There
should be no '=', ??
http://httpd.apache.org/docs/mod/mod_env.html
http://danconia.org
RE: DBD::CSV
Hello, I have changed from MySQL DB to CSV File (DBD::CSV). And also from Linux to Windows There's the first two mistakes! :) A) But now I discovered few problems: On the same hardware system the same select for the same table 1) Linux the select takes 0.4 seconds 2) Windows the select takes 2.1 seconds Using Apache 1.3.27 and under Windows ActivePerl. Is it a problem of Apache, Perl or the OS? THE OS !! WINDOWS is an expensive resource cow, designed to stop working every 2.5 years And make you think you can't live with out it. B) I have a select like this: ...WHERE ((field1 like ?) OR (field2 like ?) OR (field3 like ?)) AND ((field1 like ?) OR (field2 like ?) OR (field3 like ?)) AND ((field1 like ?) OR (field2 like ?) OR (field3 like ?)) values1,values1,values1,values2,values2,values2,values3,values 3,values3 with Linux this is not a problem, but under Windows I get no data and also no error. I removed the field3 only then I got data with Windows, but the result looks like WHERE ((field1 like ?) OR (field2 like ?)) has been used for the search. On this one are the tables *exactly* the same? C) There is a different in the order of a table when I use it under Linux or Windows. Under Windows the order is fine but under Linux it looks mixed up. That depends again on if the tables are *exactly* the same and *exactly* what query you use and *exactly* how the software is installed on the OS, and how you assemble the results. Windows and unix behave differently. If you can hack it I'd highly recommend sticking to a flavor of unix. Too many variables to figure out why they are coming back different. Thanks for any help in advance! Peter
RE: Strange security problem...
Not sure with windows, probably should see apache.org for details, but it seems That perhaps There is a 'ScriptAlias' directive for CGI-BIN but not cgi-bin. I'd try adding that in your httpd.conf file or making some kind of symlink or alias with each other for both versions. Also it may be an ExecCGI directive needs to be added for the cgi-bin version. Whatever the case it seems to be an apache configuration isssue since the script works right. It's just that that version of the directory isn't allowed/doesn't know how to execute a script. A better solution would be to install any flavor of unix and use your Windows cd as a coaster and use the manual to make the table legs even. To many Winders issues to make anythign work relyably. DMuey I was messing around at home with a test web server (Windows 2000 Professional, Apache and ActivePerl (all the latest versions of each)) which resides in my office and I typed in the url of a perl script. I purposely mistyped the url with 'cgi-bin' rather than what it actually is, 'CGI-BIN' just to see what would happen. I ended up getting the entire *code* of the perl script in my web browser (latest version of Safari on Mac OS X 10.2.3)! This worries me because my code contains passwords to our database. Is it a well known problem to capitalize the CGI-BIN directory? Should I make it lower case and change my Apache config file as well as any paths to it in my perl scripts? Or is it just a permissions problem? If I re-type the url using upper case CGI-BIN, it works as expected. I'm relatively new to Perl and Apache so forgive me if this is a well know faux pas. I was just wondering if others are aware of this issue. Regards, Cory Rau
RE: DBD::CSV
Hello, thanks for your answers. I would also like to stick to MySQL, but I have to change it, since there is no DB-Server available. Use a remote one, your isp's perhaps. Anywho... Dan, yes the data of the tables are *exactly* the same!!! How about posting the first few lines form the linux one and form the windows one? When ftping the CSV files up did you do it as binary. ( I've found once when using CSV that they look identicle if you FTP them in ASCII and binary, but if I didn't do them in binary everythingn was screwy ) The querry I use for C) is like: where (field1=?) and (field2=?) order by field3 again the same data and the same query! For showing the data I just use a while loop and display record by record. Again using the same script here and there. Regards - Original Message - From: Dan Muey [EMAIL PROTECTED] To: Peter Schuberth [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 03, 2003 3:35 PM Subject: RE: DBD::CSV Hello, I have changed from MySQL DB to CSV File (DBD::CSV). And also from Linux to Windows There's the first two mistakes! :) A) But now I discovered few problems: On the same hardware system the same select for the same table 1) Linux the select takes 0.4 seconds 2) Windows the select takes 2.1 seconds Using Apache 1.3.27 and under Windows ActivePerl. Is it a problem of Apache, Perl or the OS? THE OS !! WINDOWS is an expensive resource cow, designed to stop working every 2.5 years And make you think you can't live with out it. B) I have a select like this: ...WHERE ((field1 like ?) OR (field2 like ?) OR (field3 like ?)) AND ((field1 like ?) OR (field2 like ?) OR (field3 like ?)) AND ((field1 like ?) OR (field2 like ?) OR (field3 like ?)) values1,values1,values1,values2,values2,values2,values3,values 3,values3 with Linux this is not a problem, but under Windows I get no data and also no error. I removed the field3 only then I got data with Windows, but the result looks like WHERE ((field1 like ?) OR (field2 like ?)) has been used for the search. On this one are the tables *exactly* the same? C) There is a different in the order of a table when I use it under Linux or Windows. Under Windows the order is fine but under Linux it looks mixed up. That depends again on if the tables are *exactly* the same and *exactly* what query you use and *exactly* how the software is installed on the OS, and how you assemble the results. Windows and unix behave differently. If you can hack it I'd highly recommend sticking to a flavor of unix. Too many variables to figure out why they are coming back different. Thanks for any help in advance! Peter
RE: Trying to build a new perl with DBI statically linked to it
use DBI; perldoc -m DBI or search.cpan.org If 'use DBI;' is not what they're talking about ask them exactly what they mean by 'statically linked' Hi Folks, I've encountered an error when configuring some open-source software (Bugzilla) that has asked me to build a new perl executable which either supports dynamic loading or has the DBI module statically linked into it. I don't know how to do this and I can't find any documentation to help me out. Can you point me in the right direction? Thanks! Chris Bucchere Bucchere Development Group [EMAIL PROTECTED] 415.516.3941
RE: question on views!!
What I've done before with mysql is do a 'DESCRIBE TABLE joemama' query and assign the
results to a hash withh the keys being a number starting with 0
0 = ID,
1 = name,
2 = etc,
Then I do my select and just do
@row = fetch_rowarray
print %column_name{$num} is $row[$num] \n;
Or create the table with column names as it's own TR.
Dan
Hi
I am working on a perl script that runs a sql which extracts
all data from a view and prints them on a web page.
But the problem is I am not able to print the field names as
headings of the respective columns.
Does anyone have any idea?
Thanks
-
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, and more
RE: prepare with fetchrow_array
If it won't be exactly 8 characters every time you need to change it to VARCHAR(8)
That will allow up to 8 characters but doesn't have to be exactly 8.
Dan
Hello all,
I am having a problem with prepared sth that uses
fetchrow_array with two parameters passed in.
The problem is as follows:
in one of my tables I have a column STAT_DEF_ID ( CHAR(8) )
which is also one of the four unique constraints for that
table. I am able to select my rows into an array only if
value passed to STAT_DEF_ID is exactly 8 char. if its less
then 8 char I don't get any results back. Now I tried to
leave trailing spaces and pass a value with blanks at the end
of a string to constitute for 8 chars. No effect. However if
I do execute that sql from SQLPLUS I get the desired results.
Also I created another statement that selects everything from
that table with no reference to STAT_DEF_ID but the other
unique key. In that case I get all the data I want. Here are
some perl code.
# the following code works just fine since I don't have
STAT_DEF_ID=? in a WHERE close my $sth_test =
$dbh-prepare(q{ SELECT STAT_ID, STAT_DEF_ID, nvl(INST_MNEM,'N.A.'),
nvl(FINSRL_TYP,'N.A.'), DENOM_CURR_CDE, ORG_ID, SUBDIV_ID,
STAT_VAL_DTE, STAT_VAL_TMS, STAT_VAL_AMT,
STAT_DEC_VAL_AMT,
STAT_INT_VAL_NUM, STAT_CHAR_VAL_TXT
FROM FT_T_ISST WHERE INSTR_ID=?
ORDER BY STAT_DEF_ID,LAST_CHG_TMS DESC}) or die $dbh-errstr;
$sth_test-execute($instr_id);
while (my @row=$sth_test-fetchrow_array) {
print join(',',@row).\n;
}
###
# However this code don't return those records that have
STAT_DEF_ID than 8 chars long my $sth_test =
$dbh-prepare(q{ SELECT STAT_ID, STAT_DEF_ID, nvl(INST_MNEM,'N.A.'),
nvl(FINSRL_TYP,'N.A.'), DENOM_CURR_CDE, ORG_ID, SUBDIV_ID,
STAT_VAL_DTE, STAT_VAL_TMS, STAT_VAL_AMT,
STAT_DEC_VAL_AMT,
STAT_INT_VAL_NUM, STAT_CHAR_VAL_TXT
FROM FT_T_ISST WHERE INSTR_ID=? AND STAT_DEF_ID=?
ORDER BY STAT_DEF_ID,LAST_CHG_TMS DESC}) or die $dbh-errstr;
# $instr_id and $stat_def_id are obtained from a file
$sth_test-execute($instr_id, $stat_def_id);
while (my @row=$sth_test-fetchrow_array) {
print join(',',@row).\n;
}
NOTE: there are two prepared statements in a trace.txt I am
executing the second one. As you can see the fetchrow_array
works for 'FD12BFEE' and doesnt for 'FDPHONE'
Thanx much for your help
trace.txt
Daniel Rozengurtel
Analyst II - Data Mining/WHSE
IT Clearing-Settlements
tell: (646) 733-4242
[EMAIL PROTECTED]
_
IMPORTANT NOTICES:
This message is intended only for the addressee.
Please notify the sender by e-mail if you are not the
intended recipient. If you are not the intended recipient,
you may not copy, disclose, or distribute this message or its
contents to any other person and any such actions may be unlawful.
Banc of America Securities LLC(BAS) does not
accept time sensitive, action-oriented messages or
transaction orders, including orders to purchase or sell
securities, via e-mail.
BAS reserves the right to monitor and review the
content of all messages sent to or from this e-mail address.
Messages sent to or from this e-mail address may be stored on
the BAS e-mail system.
RE: retrieving column names from a table using DBI module
If you're using mysql try a 'DESCRIBE TABLE' query first. If not mysql find a similar command in your database and use that first, Once you have the name and position of the column names you can do what you want with them While associating them to the data in the subsequent select statement quite easily. I usually accomplish this by putting my describe statement results in has that has an index number and Column name and using the index nuumber of the select results to associate them however I need. Dan Thanks Dave But i made the mistake of caling it a table but actually it is a view and table_info ethod does not work for that. Also i tried to run table_info for a table but it does not return the column names. There is a column info method for tables to do this. But it does not work for views. Please help manisha Dave K [EMAIL PROTECTED] wrote: Manisha Hello Hi I am working with Perl CGi. There is a query where i have to select all columns in a table and display the data on the web page. the query is something like select * from But the problem is I am not able to get the column names. I am using the DBI module. Is there a way to get column names? I read the responses from Dan, Timothy and Brian, and they all have sound advice. If you would like to see another approach you can visit: http://www.geocities.com/k2001evad/pindex.html and cut and paste scripts for Oracle, MySql and Postgres DBMS that demonstrate another (not nessecarily better) way to get meta data from those RDBMs. If your driver supports the table_info method that may be the most direct way to get to the column names. Check the DBI docs for more information. HTH - Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, and more
RE: warnings reletive to my $sql content
Try changing all of your $ that are not variables to \$
perl 5.6.1
With use warnings, dependant upon the query I execute, I will
recive the
following: Use of uninitialized value in join or string.
I see this when I am selecting against v$ views and not when
I use a table/view
without a $ in the name. I'm assuming at this point that perl
is parsing $ references as variables within the my $sql=q{ };
block that I create. Whats the a method of working around
this as opposed to turning off warnings?
RE: passing more than one ARGV value
This really isn't a DBI question and might be better suited to the [EMAIL PROTECTED]
Mailing list.
Hello ,
I am writing a user registration script which once the
user has filled in the form it emails them a simple html
email with a link for them to click to validate that there
email address is real.
i.e
Print 'a
Href=http://www.mydomain.com/cgi-bin/script.pl?confirm?you@yo
urdomain.com
First off this link is bad it should dbe script.pl?[EMAIL PROTECTED]
Click to validate/A href;
Ok so far but when the link is clicked the script will not
pick up the second variable I am using
Second you only have one key value pair
$process = $ARGV[0];
$somevariable = $ARGV[1];
Third ARGV is best suited for the command line.
Use CGI.pm to parse input instead.
Fomr what it looks like from your script you nedd to make your link
Script.pl?proc=confirm[EMAIL PROTECTED]
Then your script would be like
use CGI;
$cgi = new CGI;
$process = $cgi-param('proc');
$email = $cgi-param('email');
...
if ($process EQ confirm)
{
do_something;
}
else
{
do_somethingelse
}
I am relatively new to perl so I may be going about it in
completely the wrong way can anyone help me out here please.
Thanks
James
These disclaimers don't do anything and are anoying.
***DISCLAIMER***
This Email and any files transmitted with it are confidential
and intended solely for the use of the individual to whom or
the entity to which they are addressed. If you have received
this email in error please notify the sender immediately.
Please note that any views or opinions presented in this
email are those of the author and do not necessarily
represent those of Letterbox Mail Order Limited. The
recipient should check this email and any attachments for the
presence of viruses. Letterbox Mail Order Limited accepts no
liability for any damage caused by any virus transmitted via
this email.
RE: MySQL question
Those are three different modules. You'll only need one and if any of the others are needed for it to work it will tel you on install. I use DBI, it's pretty cool. -Original Message- From: Barone, Philip [mailto:[EMAIL PROTECTED]] Sent: Friday, February 07, 2003 11:09 AM To: [EMAIL PROTECTED] Subject: MySQL question Can someone tell me the necessary modules needed to access a MySQl database using DBD/DBI? Looking in CPAN my guess is from what I find there is: DBI-1.32.tar.gz DBD-mysql-2.1020.tar.gz Msql-Mysql-modules-1.2219.tar.gz Are these the modules I need? Is this the correct order? Thanks.
RE: MySQL question
How do you do that, using DBI without mysql.pm ? I don't actually, it's just that it all came as one big happy setup on my server so I didn't have to install a bunch of different things . In fact I think on every server I've set up it is installed with perl or the os or something as I've never had to install it myself. It was just magically there evry time. I do use mysql.pm I just do Use DBI; $dh-connect(...:mysql:); Sorry if I was confusing, didn't mean to be. :) Dan Best regards, Stephan Am Freitag, 7. Februar 2003 18:12 schrieb Dan Muey: Those are three different modules. You'll only need one and if any of the others are needed for it to work it will tel you on install. I use DBI, it's pretty cool. -Original Message- From: Barone, Philip [mailto:[EMAIL PROTECTED]] Sent: Friday, February 07, 2003 11:09 AM To: [EMAIL PROTECTED] Subject: MySQL question Can someone tell me the necessary modules needed to access a MySQl database using DBD/DBI? Looking in CPAN my guess is from what I find there is: DBI-1.32.tar.gz DBD-mysql-2.1020.tar.gz Msql-Mysql-modules-1.2219.tar.gz Are these the modules I need? Is this the correct order? Thanks. -- Stephan Harren Manager Site Operations MFN-IS --- Phone +49 69 90554 153 Fax +49 69 90554 111 Cell +49 173 7011126
prepare problem w/ trace
Hello, I have a script that does several queries and I have trouble with one : I can't seem to get a particular query to prepare(). $queryqq = SELECT * FROM $root_table WHERE ID=\'$Root_ID_DB\'; Print 1; print $queryqq; DBI-trace(2,trace.txt); ($tmp = $queryqq) =~ s/([^\x20-\x7E])/'\x' . sprintf %x20, ord $1/ge;print DEBUG : NON PRINT ACHAR -$tmp- br \n; # Shows us any unprintable character my $sthqq = $dbhqq-prepare($queryqq) or die $dbhqq-errstr; print 2; outputs :: 1SELECT * FROM root WHERE ID='5'DEBUG : NON PRINT ACHAR -SELECT * FROM root WHERE ID='5'- No unprintable charcters ( I had that happen before, I couldn't see it but to mysql it was a period which made the query bad ) Never gets to 'print 2' I can run the generated query mysqlSELECT * FROM root WHERE ID='5'; in the mysql shell and get a record back Couldn't make to much sense from trace() I can't run this script with -w currently and I have many other queries that go through fine with the same set up. Can't seem to see what's happening. The user has all but grant privileges on this database and table. trace.txt :: DBI 1.21-nothread dispatch trace level set to 2 Note: perl is running without the recommended perl -w option - DESTROY for DBD::mysql::st (DBI::st=HASH(0x82315ec)~INNER) - DESTROY= undef - DESTROY for DBD::mysql::st (DBI::st=HASH(0x82317cc)~INNER) - DESTROY= undef -- DBI::END - disconnect_all for DBD::mysql::dr (DBI::dr=HASH(0x80eb5d4)~0x822a8cc) - disconnect_all= '' at DBI.pm line 533 - DESTROY for DBD::mysql::st (DBI::st=HASH(0x805b360)~INNER) - DESTROY= undef during global destruction - DESTROY for DBD::mysql::st (DBI::st=HASH(0x82315b0)~INNER) - DESTROY= undef during global destruction - DESTROY for DBD::mysql::db (DBI::db=HASH(0x822a9bc)~INNER) imp_dbh-svsock: 8228644 - DESTROY= undef during global destruction - DESTROY in DBD::_::common for DBD::mysql::dr (DBI::dr=HASH(0x822a8cc)~INNER) - DESTROY= undef during global destruction DBI 1.21-nothread dispatch trace level set to 1 Note: perl is running without the recommended perl -w option - DESTROY= undef - DESTROY= undef - disconnect_all= '' at DBI.pm line 533 - DESTROY= undef during global destruction - DESTROY= undef during global destruction - DESTROY= undef during global destruction - DESTROY= undef during global destruction Any assistance is very appreciated!!Thanks! Dan
RE: Why can't use SQL GROUP BY...?
I apologize if this has already been covered to but... Can you run that command by hand and does it work? Do this to see is both $Table has a value and if the generated code is actaully a valid sql command. $query = SELECT page FROM $Table GROUP BY page; print P QUERY -$query- P \n $value2 = $dbh-prepare($query) || die Couldn't add record, .$dbh-errstr(); Then paste the query into your program and see if it takes it. IE Mysql SELECT page FROM monkey GROUP BY page; You could also you use DBI-trace. Again if this has already been covered sorry, I've been away. Dan -Original Message- From: Philip Newton [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 11:34 PM To: Gary fung Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Why can't use SQL GROUP BY...? On Thu, 9 Jan 2003 22:59:03 +0800 (CST), [EMAIL PROTECTED] (Gary fung) wrote: My coding is similar as: $value2 = $dbh-prepare(SELECT page FROM $Table GROUP BY page) || die Couldn't add record, .$dbh-errstr(); Whenever I use GROUP BY.. , an error statement will go out : SQL ERROR: Can't find table names in FROM clause! Others have pointed out the possibility that $Table may be empty. I'd like to add that I think there's another error -- as far as I know, GROUP BY can only be used when you have aggregate functions such as SUM, MAX, COUNT(...) etc. (For example, SELECT custno, count(ordernum) FROM orders GROUP BY custno ORDER BY 2 DESC to select the customers together with the number of orders, grouped by customer but sorted by number of orders.) Did you mean ORDER BY, perhaps? Cheers, Philip -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: query only partially done
[EMAIL PROTECTED]
Add $dbh - trace( 4, file ); before the do() to see what DBI thinks
is
happening.
Mac :})
Thanks good idea!
Ok here's my modified perl : ( I did 2 instead of 4 becaus e4 didn't do
anything )
print $query;
$dbh-trace(2,trace.txt);
$dbh-do($query) or die Can not execute $query : . $dbh-errstr .
\n;
print $query;
Here is pasted in the output from the above two print $query bits :
DELETE FROM customer WHERE ID IN ('23','18')
DELETE FROM customer WHERE ID IN ('23','18')
Here is the content of the trace.txt
DBI::db=HASH(0x81bd0b4) trace level set to 2 in DBI 1.21-nothread
Note: perl is running without the recommended perl -w option
- do for DBD::mysql::db (DBI::db=HASH(0x81bd03c)~0x81bd0b4
'DELETE FROM customer WHERE ID IN ('23','.18')')
- do= 1 at mysql_man.cgi line 404
- disconnect for DBD::mysql::db (DBI::db=HASH(0x81bd03c)~0x81bd0b4)
imp_dbh-svsock: 8141a44
- disconnect= 1 at mysql_man.cgi line 47
- DESTROY for DBD::mysql::db (DBI::db=HASH(0x81bd0b4)~INNER)
- DESTROY= undef during global destruction
So for some reason the do() seems to be adding a '.' in front of all
ids except for the first one. No wonder it doesn't match the ids!
I tried a regex to remove periods - =~ s/\.//g; before the
print statements and $query doesn't seem to have it when
printed out with or without the regex.
A few more answers anyway. But why the period added in the do()?
RE: query only partially done
I think you will need to show us more of your code. Where are the ids
coming from?
First the html generated by the script :
input type=checkbox name=IDS value=26,
input type=checkbox name=IDS value=18,
I get this html by doing :
while(row = $sth-fetchrow_array) {
if($tmp_bgcolor eq $bgcolor_a) { $tmp_bgcolor
= $bgcolor_b; }
else { $tmp_bgcolor = $bgcolor_a; }
$code = '';
($tmpa = $row[0]) =~ s/([^\x20-\x7E])/'\x' . sprintf %x, ord $1/ge;
$code = tr bgcolor=$tmp_bgcolortd -$tmpa- input type=checkbox
name=\IDS\ value=\$row[0],\ /tdtd;
$code =$code a target=\_blank\
href=\$mysql_man_script_name?req_lib=$req_libreq_id=$row[0]action=see
_rec\ View Rec
ord /a Deleted By : $row[1] On : $row[2] ::
$row[$menu_option_index_num];
$code = $code /td/tr \n;
print $code;
$code = '';
}
With the altered code it outputs :
-28- input type=checkbox name=IDS value=28,
-18- input type=checkbox name=IDS value=18,
If you check them both and submit, it does this script :
$ids = $in{'IDS'};
$ids =~ s/\,$//;
print -$ids-br;# which out put - -27,18-
($tmpa = $ids) =~ s/([^\x20-\x7E])/'\x' . sprintf %x, ord $1/ge;
print $tmpa; # which out put - 27,\x018
print br;
recs = split(/,/, $ids);
Perhaps the later ids include a non-printable character, which trace()
is
outputting as a period. Try printing your query like this:
($tmp = $query) =~ s/([^\x20-\x7E])/'\x' . sprintf %x, ord $1/ge;
print $tmp;
$tmp prints out as : DELETE FROM customer WHERE ID IN ('25','\x018')
sure enough odd char
It seems to get there somewhere in between pressing submit and after
parse
( I use the ReadParse subroutine in cgi-lib )
I just did $query =~ s/([^\x20-\x7E])//ge;
to remove any nonprintable chars right before do() and all is well.
Thanks for the idea! I still wonder where it's coming from.
Any ideas how a non printable character would get ther ewould be good to
know.
Thanks for your help everyone!
Dan
Ronald
query only partially done
Here is the deal : I have a script that does quite a bit mysql
manipulation everything works super except one simple delete statement
print $query;
This is copied and pasted from the above print statement:
DELETE FROM customer WHERE ID='10' OR ID='9' OR ID='8' OR ID='7'
$dbh-do($query) or die Can not execute $query : . $dbh-errstr .
\n;
It does delete the first record specified ( in this case ID='10' )
it acts as if the query where just : DELETE FROM customer WHERE ID='10'
I get no error messages, it goes on and finishes displaying the page.
When I've had bad queries before it would just stop at the 'do'
statement and not finish printing the html out for the rest of the page.
If I paste that query into the mysqlprompt it works like a charm and
deletes all. ( with an added semi colon of course )
i've usd the prepare/execue method, I've had it do execute a query for
each id :
foreach $ID(ids) {
$query = DELETE FROM customer WHERE ID=\'$ID\';
print $query \n;
$dbh-do($query) or die Can not execute $query : .
$dbh-errstr .\n;
$query = '';
}
results in this output :
DELETE FROM customer WHERE ID='8'
DELETE FROM customer WHERE ID='9'
DELETE FROM customer WHERE ID='10'
It acts as if only 'DELETE FROM customer WHERE ID='8'' is being executed
or executed each time but $query is different each time in the print
statement.
all have the exact same result - only the first record specified gets
deleted,( ID='8' above )the script behaves as if the query went ok, but
they are still
there and if I copy and paste the query into the command line it works
fine.
Here's what I got :
perl, version 5.005_03 built for i386-freebsd
mysql 323
DBI 1.21
Why isn't it doing the entire query from my script?
Thanks
Dan
RE: query only partially done
1) is the id field a character? in other words, do you need the
quotes? id='10' ??
It is - ID INT(11) NOT NULL AUTO_INCREMENT and - PRIMARY KEY(ID)
2) why not just use
DELETE FROM customer WHERE ID in ('7','8','9','10')
??
Good question I'll try that way. I also have the same prob for and
update
statement. I'll try it on both. Thanks
Dan
-Joe
--- Dan Muey [EMAIL PROTECTED] wrote:
Here is the deal : I have a script that does quite a bit mysql
manipulation everything works super except one simple delete
statement
print $query;
This is copied and pasted from the above print statement :
DELETE FROM customer WHERE ID='10' OR ID='9' OR ID='8' OR ID='7'
$dbh-do($query) or die Can not execute $query : . $dbh-errstr .
\n;
It does delete the first record specified ( in this case ID='10' )
it acts as if the query where just : DELETE FROM customer WHERE
ID='10'
I get no error messages, it goes on and finishes displaying the
page.
When I've had bad queries before it would just stop at the 'do'
statement and not finish printing the html out for the rest of the
page.
If I paste that query into the mysqlprompt it works like a charm
and
deletes all. ( with an added semi colon of course )
i've usd the prepare/execue method, I've had it do execute a query
for
each id :
foreach $ID(@ids) {
$query = DELETE FROM customer WHERE ID=\'$ID\';
print $query \n;
$dbh-do($query) or die Can not execute $query : .
$dbh-errstr .\n;
$query = '';
}
results in this output :
DELETE FROM customer WHERE ID='8'
DELETE FROM customer WHERE ID='9'
DELETE FROM customer WHERE ID='10'
It acts as if only 'DELETE FROM customer WHERE ID='8'' is being
executed
or executed each time but $query is different each time in the
print
statement.
all have the exact same result - only the first record specified
gets
deleted,( ID='8' above )the script behaves as if the query went ok,
but
they are still
there and if I copy and paste the query into the command line it
works
fine.
Here's what I got :
perl, version 5.005_03 built for i386-freebsd
mysql 323
DBI 1.21
Why isn't it doing the entire query from my script?
Thanks
Dan
Re: query only partially done
1) is the id field a character? in other words, do you need the
quotes? id='10' ??
It is - ID INT(11) NOT NULL AUTO_INCREMENT and - PRIMARY KEY(ID)
2) why not just use
DELETE FROM customer WHERE ID in ('7','8','9','10')
??
...I'll try that...
I tried the above method and same thing. It would only delete record '7'
I tried the above and the old with out the single quotes and then it
wouldn't do anything except die.
Dan
-Joe
--- Dan Muey [EMAIL PROTECTED] wrote:
Here is the deal : I have a script that does quite a bit mysql
manipulation everything works super except one simple delete
statement
print $query;
This is copied and pasted from the above print statement :
DELETE FROM customer WHERE ID='10' OR ID='9' OR ID='8' OR ID='7'
$dbh-do($query) or die Can not execute $query : . $dbh-errstr .
\n;
It does delete the first record specified ( in this case ID='10' )
it acts as if the query where just : DELETE FROM customer WHERE
ID='10'
I get no error messages, it goes on and finishes displaying the
page.
When I've had bad queries before it would just stop at the 'do'
statement and not finish printing the html out for the rest of the
page.
If I paste that query into the mysqlprompt it works like a charm
and
deletes all. ( with an added semi colon of course )
i've usd the prepare/execue method, I've had it do execute a query
for
each id :
foreach $ID(@ids) {
$query = DELETE FROM customer WHERE ID=\'$ID\';
print $query \n;
$dbh-do($query) or die Can not execute $query : .
$dbh-errstr .\n;
$query = '';
}
results in this output :
DELETE FROM customer WHERE ID='8'
DELETE FROM customer WHERE ID='9'
DELETE FROM customer WHERE ID='10'
It acts as if only 'DELETE FROM customer WHERE ID='8'' is being
executed
or executed each time but $query is different each time in the
print
statement.
all have the exact same result - only the first record specified
gets
deleted,( ID='8' above )the script behaves as if the query went ok,
but
they are still
there and if I copy and paste the query into the command line it
works
fine.
Here's what I got :
perl, version 5.005_03 built for i386-freebsd
mysql 323
DBI 1.21
Why isn't it doing the entire query from my script?
Thanks
Dan
