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=3D"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.=20

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/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

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. =20

Using your example below a select statement in Perl will fail if I =
do
the following

$sentby =3D "Peter Benac";
$count =3D $sth->do("select * from payment_trans where =
sent_by=3D$sentby");

or

$sth=3D$dbh->prepare(q{select * from payment_trans where sent_by=3D?});
$sth->execute($sentby);
$row =3D 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:david.logan@hp.com]=20
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 =3D $dbh->prepare("REPLACE INTO paymate_trans
SET Paymate_trans_id=3D?,
Reference=3D?,
Payment_date=3D?,
Trans_status=3D?,
Sent_by=3D?,
Email_address=3D?,
Payment_amount=3D?,
Payment_amt_currency=3D?,
Buyer_fee_absorption=3D?,
Buyer_fee_currency=3D?,
GST=3D?,
GST_currency=3D?,
Transaction_fee=3D?,
Trans_fee_currency=3D?,
GST_on_fee=3D?,
GST_on_fee_currency=3D?,
Payment_received=3D?,
Pay_recvd_currency=3D?,
Message=3D?");

$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=3D"this and that"; <------------------or whatever you wish

$sth =3D $dbh->prepare("SELECT * from TABLE where myfield=3D?"); $sth =
=3D
execute($variable);

while (my $table =3D $sth->fetchrow_hashref()) {
my $name =3D $table->{field1};
my $size =3D $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:ppbenac@emacolet.com]=20
Sent: Tuesday, 9 November 2004 4:19 PM
To: perl@lists.mysql.com
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=3D"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.=20

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!!!




--=20
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Ddavid.logan@hp.com


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

RE: Spaces in Data Fields

David,

You are not being overly simplistic, but I think you are missing my
problem. =20

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:david.logan@hp.com]=20
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 =3D $sth->Fetchrow(); rather than $row =
=3D
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 =3D "1.0";

my %err_handle =3D (
PrintError =3D> 1,
RaiseError =3D> 1
);

my %conf =3D (
help =3D> 0,
user =3D> '',
pass =3D> 'none',
);

GetOptions (
'h|help' =3D> \$conf{help},
'u|user=3Ds' =3D> \$conf{user},
'p|password=3Ds' =3D> \$conf{pass},
);

my $options =3D <<"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 =3D "DBI:mysql:host=3Dporkchop;database=3Dtest;";
my $dbh =3D DBI->connect($dsn, $conf{user}, $conf{pass},
\%err_handle);

my $selname=3D"David Logan";

my $sth =3D $dbh->prepare("SELECT * from mytest WHERE =
name=3D?");
$sth->execute($selname);

while (my $table =3D $sth->fetchrow_hashref()) {
my $name =3D $table->{name};
print "my name =3D $name\n";
}

}
detail_trans();=20

porkribs ~ $ ./mytest.pl -u root -p xxxxxxx
my name =3D 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:ppbenac@emacolet.com]=20
Sent: Tuesday, 9 November 2004 4:54 PM
To: Logan, David (SST - Adelaide)
Cc: perl@lists.mysql.com
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. =20

Using your example below a select statement in Perl will fail if I =
do
the following

$sentby =3D "Peter Benac";
$count =3D $sth->do("select * from payment_trans where =
sent_by=3D$sentby");

or

$sth=3D$dbh->prepare(q{select * from payment_trans where sent_by=3D?});
$sth->execute($sentby); $row =3D 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:david.logan@hp.com]=20
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 =3D $dbh->prepare("REPLACE INTO paymate_trans
SET Paymate_trans_id=3D?,
Reference=3D?,
Payment_date=3D?,
Trans_status=3D?,
Sent_by=3D?,
Email_address=3D?,
Payment_amount=3D?,
Payment_amt_currency=3D?,
Buyer_fee_absorption=3D?,
Buyer_fee_currency=3D?,
GST=3D?,
GST_currency=3D?,
Transaction_fee=3D?,
Trans_fee_currency=3D?,
GST_on_fee=3D?,
GST_on_fee_currency=3D?,
Payment_received=3D?,
Pay_recvd_currency=3D?,
Message=3D?");

$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=3D"this and that"; <------------------or whatever you wish

$sth =3D $dbh->prepare("SELECT * from TABLE where myfield=3D?"); $sth =
=3D
execute($variable);

while (my $table =3D $sth->fetchrow_hashref()) {
my $name =3D $table->{field1};
my $size =3D $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:ppbenac@emacolet.com]=20
Sent: Tuesday, 9 November 2004 4:19 PM
To: perl@lists.mysql.com
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=3D"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.=20

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!!!




--=20
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Ddavid.logan@hp.com


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

RE: Spaces in Data Fields

Hi Peter,

porkribs ~ $ perl -V
Summary of my perl5 (revision 5.0 version 8 subversion 0) configuration:
Platform:
osname=3Dsolaris, osvers=3D2.8, archname=3Dsun4-solaris
uname=3D'sunos porkribs 5.8 generic_108528-11 sun4u sparc
sunw,ultra-30 '
config_args=3D''
hint=3Dprevious, useposix=3Dtrue, d_sigaction=3Ddefine
usethreads=3Dundef use5005threads=3Dundef useithreads=3Dundef
usemultiplicity=3Dundef
useperlio=3Ddefine d_sfio=3Dundef uselargefiles=3Ddefine =
usesocks=3Dundef
use64bitint=3Dundef use64bitall=3Dundef uselongdouble=3Dundef
usemymalloc=3Dn, bincompat5005=3Dundef
Built under solaris
Compiled at Sep 1 2003 21:06:42
@INC:
/usr/local/lib/perl5/5.8.0/sun4-solaris
/usr/local/lib/perl5/5.8.0
/usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris
/usr/local/lib/perl5/site_perl/5.8.0
/usr/local/lib/perl5/site_perl

DBI (1.42) - Database independent interface for Perl
DBD::mysql (2.9003) - MySQL driver for the Perl5 Database Interface
(DBI)

I suppose this also means that "United States of America" doesn't exist
either 8-) Australia is definitely there!

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:ppbenac@emacolet.com]=20
Sent: Tuesday, 9 November 2004 5:35 PM
To: Logan, David (SST - Adelaide)
Cc: perl@lists.mysql.com
Subject: RE: Spaces in Data Fields

David,

You are not being overly simplistic, but I think you are missing my
problem. =20

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!!!


much snippage

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

[m_p] SSI after CGIs...

Okay, I'm going crazy here...

I've tried both Apache::Filter AND Apache::OutputChain to no avail (or
rather to bad avail).

The former (Filter), if I enable loading PerlModule Apache::RegistryFilter
and restart, for some reason when I hit a mod_perl Registry script I get:

[Mon Nov 8 23:04:27 2004] [error] Undefined subroutine
&Apache::Registry::handler called.

Every time I hit *any* mod_perl CGI. And even if I don't have anyting
actually USING Filter in the conf, at all.

So this seems to simply be not working, though I am mystified as to how it
creates the illusion in the webserver that *Registry* suddenly loses its
hander subroutine (as much as I hated to do it, I even looked and double
checked that the method was, in fact, there).



The latter (OutputChain with SSIChain) is even WEIRDER.

It DOES parse the SSI as long asd there's no call to another mod_perl script
lower down (like, when I tried to 'include virtual' a SHTML page that in
turn 'include virtual's a mod_perl CGI, the whole thing just hung and left
me with a lot of zombies from my repeated hits on it before I realised what
was not going on -- namely, it was never finishing and things were left dead
from hitting the 20 layer recursion limit and then somehow also not dying
off -- note that when I hit the SHTML page *by itself* it is fine, and the
mod_perl script did nothing but print out an include virtual pointing at
it... That doen't seem right anyway...

But it's worse... it's out of order...

Here, look:

http://www.xfx-3d.com/include1.shtml
http://www.xfx-3d.com/include2.shtml

Those are (despite the 's' in the extension) static HTML files with nothing
but what you see when you hit them in them.

This: http://www.xfx-3d.com/include_both.shtml
Is an SSI page with the following source:


CONTENT


Now I also have a little apache registry script, right here:
http://www.xfx-3d.com/include_both.mp

The source for this is:
#!/usr/bin/perl

use strict;
use CGI;
my $cgi = new CGI;
print $cgi->header;

print <<"EOF";

CONTENT

EOF


The two should have the EXACT same output, except maybe a carriage return or
two....

But when you see them on screen, nope...

Now, as DUMB as it was, I actually DID test to see if something was coming
goofy out of Perl itself, by running the script from the command line and
piping it into an SHTML file.

http://www.xfx-3d.com/include_both_from_mp.shtml

So you see, coming out of Perl everything is in the correct order.

For some reason, when the SSIChain parses through the SSI, it deposits
everything it interprets and includes at the TOP of the output not in the
place where it actually belongs.

This is bad. What the hell is going on here???


All this made me took at Apache::Registry a little too much and I think I
may now be clinically insane, because of all the things it seems to be doing
that don't make any sense at all... like calling methods on $r that are
defined in Apache::RegistryLoader, which Apache::Registry never actually
calls... After that I think I'll stick to 'safe' reading material, like the
Necronomicon.

The reason I was looking at Apache::Registry is I was trying to find a place
I could hook a call to Apache::SSI to parse the SSI right in there, maybe. I
couldn't find it. But Cthulhu ate my brain after that. Yaaaagh!


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: Spaces in Data Fields

Well I see two differences.

My DBI version is 1.45 and the DBD::mysql version is 2.9004

Don't upgrade till I find out if this is me or the modules?

Lucky me the United States of America doesn't have it's own zip code. =
While
Australia would exist Botany Bay would not.

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:david.logan@hp.com]=20
Sent: Tuesday, November 09, 2004 2:28 AM
To: Peter P. Benac
Cc: perl@lists.mysql.com
Subject: RE: Spaces in Data Fields


Hi Peter,

porkribs ~ $ perl -V
Summary of my perl5 (revision 5.0 version 8 subversion 0) configuration:
Platform:
osname=3Dsolaris, osvers=3D2.8, archname=3Dsun4-solaris
uname=3D'sunos porkribs 5.8 generic_108528-11 sun4u sparc =
sunw,ultra-30 '
config_args=3D''
hint=3Dprevious, useposix=3Dtrue, d_sigaction=3Ddefine
usethreads=3Dundef use5005threads=3Dundef useithreads=3Dundef
usemultiplicity=3Dundef
useperlio=3Ddefine d_sfio=3Dundef uselargefiles=3Ddefine =
usesocks=3Dundef
use64bitint=3Dundef use64bitall=3Dundef uselongdouble=3Dundef
usemymalloc=3Dn, bincompat5005=3Dundef
Built under solaris
Compiled at Sep 1 2003 21:06:42
@INC:
/usr/local/lib/perl5/5.8.0/sun4-solaris
/usr/local/lib/perl5/5.8.0
/usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris
/usr/local/lib/perl5/site_perl/5.8.0
/usr/local/lib/perl5/site_perl

DBI (1.42) - Database independent interface for Perl
DBD::mysql (2.9003) - MySQL driver for the Perl5 Database Interface
(DBI)

I suppose this also means that "United States of America" doesn't exist
either 8-) Australia is definitely there!

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:ppbenac@emacolet.com]=20
Sent: Tuesday, 9 November 2004 5:35 PM
To: Logan, David (SST - Adelaide)
Cc: perl@lists.mysql.com
Subject: RE: Spaces in Data Fields

David,

You are not being overly simplistic, but I think you are missing my
problem. =20

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!!!


much snippage


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Spaces in Data Fields

> 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");
>
> 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.

This fails because you have not quoted the value in the select
statement. You need to do something like:

$count = $sth->do("select * from payment_trans where sent_by='$sentby'");


> $sth=$dbh->prepare(q{select * from payment_trans where sent_by=?});
> $sth->execute($sentby); $row = fetchrow();
>
> For that matter the prepare/execute returns a "no record found" error
> long before I hit the fetchrow.

Are you sure you have posted the exact code that you have run?
On my machine, it doesn't run at all, it fails at compile time because
there is no fetchrow(). If I take out that error and put in
fetchall_arrayref then it runs fine.

Your error message confuses me. It isn't an error for the select to
return no rows, I simply get 0 rows returned and if I use a string with
spaces that does exist in the database I get the correct number of rows.
So I don't see why you would see an error message like that.
Furthermore, I can't find the string 'no record found' in either DBI or
DBD::MySQL. Where do you believe that error is coming from?

If you need more help, please post the actual running code that fails
and the actual output from your program, and please provide the version
numbers and environment details.

Cheers, Dave


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: Spaces in Data Fields

Use DBI
Use Mysql;

$igotzip=3D$dbh->do("select * from ZipCode where city=3D'$members[8]'
and state =3D '$members[9]'");
=09
$igotzip will be zero if the city has a space in it like Myrtle =
Beach.
No space it works just fine.

$sth=3D$dbh->prepare(q{select * from ZipCode where city=3D? and
state=3D?} );
$sth->execute ($members[8], $members[9]);

@Row =3D $sth->fetchrow();

The array will be empty if the city conatain as space.


As for my operating environment please read the other messages

----
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: Dave Howorth [mailto:dhoworth@mrc-lmb.cam.ac.uk]=20
Sent: Tuesday, November 09, 2004 5:42 AM
To: perl@lists.mysql.com
Subject: Re: Spaces in Data Fields


> Using your example below a select statement in Perl will fail if
> I do the following
>
> $sentby =3D "Peter Benac";
> $count =3D $sth->do("select * from payment_trans where =
sent_by=3D$sentby");
>
> The do statement returns a count of 0. If I do a search for=20
> records on that field using data that does not contain a space it=20
> works.

This fails because you have not quoted the value in the select=20
statement. You need to do something like:

$count =3D $sth->do("select * from payment_trans where =
sent_by=3D'$sentby'");


> $sth=3D$dbh->prepare(q{select * from payment_trans where =
sent_by=3D?}); >
$sth->execute($sentby); $row =3D fetchrow(); >
> For that matter the prepare/execute returns a "no record found"=20
> error long before I hit the fetchrow.

Are you sure you have posted the exact code that you have run? On my
machine, it doesn't run at all, it fails at compile time because=20
there is no fetchrow(). If I take out that error and put in=20
fetchall_arrayref then it runs fine.

Your error message confuses me. It isn't an error for the select to=20
return no rows, I simply get 0 rows returned and if I use a string with=20
spaces that does exist in the database I get the correct number of rows. =

So I don't see why you would see an error message like that.=20
Furthermore, I can't find the string 'no record found' in either DBI or=20
DBD::MySQL. Where do you believe that error is coming from?

If you need more help, please post the actual running code that fails=20
and the actual output from your program, and please provide the version=20
numbers and environment details.

Cheers, Dave


--=20
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Dppbenac@emacolet.com


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

RE: FIXED Spaces in Data Fields

Fixed it. Turns out it was a loose nut between the keyboard and the =
chair.
Sorry for the not necessary posts

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: Peter P. Benac [mailto:ppbenac@emacolet.com]=20
Sent: Tuesday, November 09, 2004 5:58 AM
To: dhoworth@mrc-lmb.cam.ac.uk; perl@lists.mysql.com
Subject: RE: Spaces in Data Fields


Use DBI
Use Mysql;

$igotzip=3D$dbh->do("select * from ZipCode where city=3D'$members[8]'
and state =3D '$members[9]'");
=09
$igotzip will be zero if the city has a space in it like Myrtle =
Beach.
No space it works just fine.

$sth=3D$dbh->prepare(q{select * from ZipCode where city=3D? and
state=3D?} );
$sth->execute ($members[8], $members[9]);

@Row =3D $sth->fetchrow();

The array will be empty if the city conatain as space.


As for my operating environment please read the other messages

----
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: Dave Howorth [mailto:dhoworth@mrc-lmb.cam.ac.uk]=20
Sent: Tuesday, November 09, 2004 5:42 AM
To: perl@lists.mysql.com
Subject: Re: Spaces in Data Fields


> Using your example below a select statement in Perl will fail if
> I do the following
>
> $sentby =3D "Peter Benac";
> $count =3D $sth->do("select * from payment_trans where =
sent_by=3D$sentby");
>
> 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=20
> works.

This fails because you have not quoted the value in the select=20
statement. You need to do something like:

$count =3D $sth->do("select * from payment_trans where =
sent_by=3D'$sentby'");


> $sth=3D$dbh->prepare(q{select * from payment_trans where =
sent_by=3D?}); >
$sth->execute($sentby); $row =3D fetchrow(); >
> For that matter the prepare/execute returns a "no record found"
> error long before I hit the fetchrow.

Are you sure you have posted the exact code that you have run? On my
machine, it doesn't run at all, it fails at compile time because=20
there is no fetchrow(). If I take out that error and put in=20
fetchall_arrayref then it runs fine.

Your error message confuses me. It isn't an error for the select to=20
return no rows, I simply get 0 rows returned and if I use a string with=20
spaces that does exist in the database I get the correct number of rows. =

So I don't see why you would see an error message like that.=20
Furthermore, I can't find the string 'no record found' in either DBI or=20
DBD::MySQL. Where do you believe that error is coming from?

If you need more help, please post the actual running code that fails=20
and the actual output from your program, and please provide the version=20
numbers and environment details.

Cheers, Dave


--=20
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Dppbenac@emacolet.com


--=20
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Dppbenac@emacolet.com


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org