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]