RE: Spaces in Data Fields
"Peter P. Benac" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.perl |
|---|---|
| Message-ID | <[email protected]> |
David,
You are not being overly simplistic, but I think you are missing my
problem.
The do statement returns a count of 0. If I do a search for records on
that field using data that does not contain a space it works.
For that matter the prepare/execute returns a "no record found" error
long before I hit the fetchrow.
I am glad yours works, because that means mine should. The problem is
it does not!!! It does not matter if I use a Fetchrow or a fetchrow_array
there is nothing there to fetch. Are you using the same version of Perl and
the DBI, DBD bundles I am using? As I said both systems where recently
updated from CPAN last week. I don't seem to recall this issue before, but
recent changes to the programming has required Zip Code verification and
it's tough to verify a Zip Code on Myrtle Beach when Perl/MySQL says Myrtle
Beach doesn't exist. For that matter either does San Francisco, Los Angeles,
New York, Palm Beach or any other two word city.
Regards,
Pete
----
Peter P. Benac, CCNA
Celtic Spirit Networking
Providing Network and Systems Project Management and Installation and Web
Hosting.
Phone: 919-618-2557
Web: http://www.emacolet.com
Need quick reliable Systems or Network Management advice visit
http://www.nmsusers.org
To have principles...
First have courage.. With principles comes integrity!!!
-----Original Message-----
From: Logan, David (SST - Adelaide) [mailto:[email protected]]
Sent: Tuesday, November 09, 2004 1:45 AM
To: Peter P. Benac
Subject: RE: Spaces in Data Fields
Hi Peter,
Works fine for me, one thing I noticed in your example was the fetchrow
statement, I've never used that. I use fetchrow_array or fetchrow_hash which
will put your record into an array or hash (sorry if I'm being a bit
simplistic) but the I can't see a fetchrow method for MySQL in the
Programming the Perl DBI book. If there is a method (I may be wrong) it
would probably be called $row = $sth->Fetchrow(); rather than $row =
fetchrow();
Try the fetchrow_hashref as below, that is my most used one as it is very
simple and works a treat.
porkribs ~ $ uname -a
SunOS porkribs 5.8 Generic_108528-27 sun4u sparc SUNW,Ultra-30
porkribs ~ $ cat mytest.pl
#!/usr/local/bin/perl -w
use strict;
use Getopt::Long qw(:config no_ignore_case);
use DBI;
my $VERSION = "1.0";
my %err_handle = (
PrintError => 1,
RaiseError => 1
);
my %conf = (
help => 0,
user => '',
pass => 'none',
);
GetOptions (
'h|help' => \$conf{help},
'u|user=s' => \$conf{user},
'p|password=s' => \$conf{pass},
);
my $options = <<"EOUSAGE";
$0 ver $VERSION
Usage : $0 options
--help -h This screen | help
--user -u User name to connect with
--password -p Password to connect with
EOUSAGE
sub usage {
die @_, $options;
}
sub detail_trans {
#
# Connect to the database on porkchop
#
my $dsn = "DBI:mysql:host=porkchop;database=test;";
my $dbh = DBI->connect($dsn, $conf{user}, $conf{pass},
\%err_handle);
my $selname="David Logan";
my $sth = $dbh->prepare("SELECT * from mytest WHERE name=?");
$sth->execute($selname);
while (my $table = $sth->fetchrow_hashref()) {
my $name = $table->{name};
print "my name = $name\n";
}
}
detail_trans();
porkribs ~ $ ./mytest.pl -u root -p xxxxxxx
my name = David Logan
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-----Original Message-----
From: Peter P. Benac [mailto:[email protected]]
Sent: Tuesday, 9 November 2004 4:54 PM
To: Logan, David (SST - Adelaide)
Cc: [email protected]
Subject: RE: Spaces in Data Fields
David,
Thanks, but you mis-understood my question. I don't have a problem
putting data with spaces into the table. I have a problem doing a select on
a field where the data could contain spaces.
Using your example below a select statement in Perl will fail if I do
the following
$sentby = "Peter Benac";
$count = $sth->do("select * from payment_trans where sent_by=$sentby");
or
$sth=$dbh->prepare(q{select * from payment_trans where sent_by=?});
$sth->execute($sentby); $row = fetchrow();
Nothing will be found by Perl, but if I do the same command string inside
the comman line mysql the records are found. I can't be the only one having
this problem so I am looking for what I am doing wrong. :)
Regards,
Pete
----
Peter P. Benac, CCNA
Celtic Spirit Networking
Providing Network and Systems Project Management and Installation and Web
Hosting.
Phone: 919-618-2557
Web: http://www.emacolet.com
Need quick reliable Systems or Network Management advice visit
http://www.nmsusers.org
To have principles...
First have courage.. With principles comes integrity!!!
-----Original Message-----
From: Logan, David (SST - Adelaide) [mailto:[email protected]]
Sent: Tuesday, November 09, 2004 12:57 AM
To: Peter P. Benac
Subject: RE: Spaces in Data Fields
Hi Peter,
I don't seem to have this issue, example code that I use is (a SELECT is not
much different)
my $sth = $dbh->prepare("REPLACE INTO paymate_trans
SET Paymate_trans_id=?,
Reference=?,
Payment_date=?,
Trans_status=?,
Sent_by=?,
Email_address=?,
Payment_amount=?,
Payment_amt_currency=?,
Buyer_fee_absorption=?,
Buyer_fee_currency=?,
GST=?,
GST_currency=?,
Transaction_fee=?,
Trans_fee_currency=?,
GST_on_fee=?,
GST_on_fee_currency=?,
Payment_received=?,
Pay_recvd_currency=?,
Message=?");
$sth->execute($trans_rec{Paymate_trans_id},
$trans_rec{Reference},
$trans_rec{Payment_date},
$trans_rec{Trans_status},
$trans_rec{Sent_by},
$trans_rec{Email_address},
$trans_rec{Payment_amount},
$trans_rec{Payment_amt_currency},
$trans_rec{Buyer_fee_absorption},
$trans_rec{Buyer_fee_currency},
$trans_rec{GST},
$trans_rec{GST_currency},
$trans_rec{Transaction_fee},
$trans_rec{Trans_fee_currency},
$trans_rec{GST_on_fee},
$trans_rec{GST_on_fee_currency},
$trans_rec{Payment_received},
$trans_rec{Pay_recvd_currency},
$trans_rec{Message});
Each of the variables replaces the appropriate placeholder and bingo there
it is.
Would you like to post your code and I'll have a look at it if you like
I would use
$variable="this and that"; <------------------or whatever you wish
$sth = $dbh->prepare("SELECT * from TABLE where myfield=?"); $sth =
execute($variable);
while (my $table = $sth->fetchrow_hashref()) {
my $name = $table->{field1};
my $size = $table->{field2};
}
This should return you each row selected and set $name to first field and
$size to second.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-----Original Message-----
From: Peter P. Benac [mailto:[email protected]]
Sent: Tuesday, 9 November 2004 4:19 PM
To: [email protected]
Subject: Spaces in Data Fields
Greetings,
My search of the archives is still in progress but I thought I would
throw this out there.
I am using Perl 5.8.0 I just installed the DBI and DBD modules directly
from CPAN last week. MySQL is version 4.0.14 running on two different
platforms Solaris 8 and Solaris 9.
If I do a select statement from with-in the MySQL command interface I can
do a search on a field where the data contains one or more spaces I.E select
* from mytable where myfield="this and that";
However, if I try the same thing inside a Perl script I get zero results,
and not errors other then no record found. It doesn't matter if I do this
inside a prepare(g{ }); or in a do("select * from"); statement. Even if I
escape the spaces it still fails to find the records that the command line
MySQL locates. I can use Perl to put the data in the database I just
can't
use Perl in this manner to locate the record. I have had to resort to using
a select * from mytable and extracting the data line by line and parse the
field as if this was a flat ASCII file.
Anyone have an idea how I can get this to work other then putting "this"
in one field and "that in a different field?
Thanks in advance
Regards,
Pete
----
Peter P. Benac, CCNA
Celtic Spirit Networking
Providing Network and Systems Project Management and Installation and Web
Hosting.
Phone: 919-618-2557
Web: http://www.emacolet.com
Need quick reliable Systems or Network Management advice visit
http://www.nmsusers.org
To have principles...
First have courage.. With principles comes integrity!!!
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/[email protected]
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/[email protected]