Dropping Trailing Whitespace

am 17.03.2003 15:40:10 von Gabriel Weinberg

------=_NextPart_000_00DF_01C2EC69.33C7AB80
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I am using DBD::mysql w/ perl v5.8.0 on a system running FreeBSD
5.0-RELEASE #0 and mysql Ver 11.18 Distrib 3.23.55, for
portbld-freebsd5.0 (i386).

I have a table with a column defined as the following.

hash CHAR(16) BINARY NOT NULL

I am inserting binary md5 hashes into this column using the Digest::MD5
module.

Most data works fine. However, if the hash has a trailing white space
(ascii character 32), it seems to be getting truncated on the way to
MySQL, such that subsequent queries to find the hash fail. Of course,
if I do the truncation myself, everything works fine, but the character
really does matter.

I am inserting and comparing via binding the values.

I prepare the queries, and pass the binary hash to the query via the
execute function.

Any ideas?

Attached is an example script that illustrates the problem.

Thank you,

Gabriel

_________________
Gabriel Weinberg
yegg@alum.mit.edu

------=_NextPart_000_00DF_01C2EC69.33C7AB80
Content-Type: text/plain; charset=us-ascii

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2082@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.
------=_NextPart_000_00DF_01C2EC69.33C7AB80--

Re: Dropping Trailing Whitespace

am 17.03.2003 16:17:45 von Jochen Wiedmann

Zitiere Gabriel Weinberg :

> Most data works fine. However, if the hash has a trailing white space

Did you try using the ChopBlanks attribute?

Jochen

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2083@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Dropping Trailing Whitespace

am 17.03.2003 16:17:45 von Jochen Wiedmann

RE: Dropping Trailing Whitespace

am 17.03.2003 16:19:16 von Gabriel Weinberg

Yes, I did.

Gabriel

_________________
Gabriel Weinberg
yegg@alum.mit.edu

-----Original Message-----
From: Jochen Wiedmann [mailto:joe@ispsoft.de]
Sent: Monday, March 17, 2003 10:18 AM
To: Gabriel Weinberg
Cc: msql-mysql-modules@lists.mysql.com
Subject: Re: Dropping Trailing Whitespace

Zitiere Gabriel Weinberg :

> Most data works fine. However, if the hash has a trailing white space

Did you try using the ChopBlanks attribute?

Jochen

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2084@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

RE: Dropping Trailing Whitespace

am 17.03.2003 16:19:16 von Gabriel Weinberg

Re: Dropping Trailing Whitespace

am 19.03.2003 15:19:39 von Jochen Wiedmann

Zitiere Gabriel Weinberg :

> Attached is an example script that illustrates the problem.

The example script is missing?

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2099@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Dropping Trailing Whitespace

am 19.03.2003 15:19:39 von Jochen Wiedmann

RE: Dropping Trailing Whitespace

am 20.03.2003 00:05:29 von Gabriel Weinberg

------=_NextPart_000_0061_01C2EE42.20641F80
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

> The example script is missing?

That's weird since it was attached to the email.

I have attached it again and included it below.

Gabriel

_________________
Gabriel Weinberg
yegg@alum.mit.edu

#!/usr/bin/perl -w

use strict;

# For hashing.
use Digest::MD5 qw(md5);

# Use the database.
use DBI;

# MySQL setup.
my $mysql_host='localhost';
my $mysql_db='';
my $mysql_user='';
my $mysql_passwd='';
my $data_source="DBI:mysql:database=" . $mysql_db . ":host=" .
$mysql_host;

# Connect to the database.
my $dbh = DBI->connect($data_source,$mysql_user,$mysql_passwd,{
PrintError => 0 });

# Create table.
$dbh->do("CREATE TABLE test (address_id INT NOT NULL AUTO_INCREMENT,
hash CHAR(16) BINARY NOT NULL, PRIMARY KEY (address_id), UNIQUE INDEX
(hash));");

# Define hash.
#my $hash = &md5("4702 STEARNS HILL RD WALTHAM MA 02451"); # This one
works fine.
my $hash = &md5("4003 MINNESOTA DR ANCHORAGE AK 99503"); # This one has
a trailing space and doesn't work.

# If you uncomment this block, the one that doesn't work will then work.
# Escape trailing space.
#if ($hash =~ / $/) {
# my @characters = split(//,$hash);
# my $pos1 = scalar(@characters) - 2;
# my $pos2 = scalar(@characters) - 1;
# $hash = join('',@characters[0..$pos1]);
#}

# Insert hash.
my $sth_insert_hash = $dbh->prepare("INSERT INTO test (hash) VALUES
(?)");
$sth_insert_hash->execute($hash);
$sth_insert_hash->finish;

# Select hash.
my $sth_select_hash = $dbh->prepare("SELECT address_id FROM test WHERE
hash=?");
$sth_select_hash->execute($hash);
while (my $row = $sth_select_hash->fetchrow_hashref) {
print "Found\n";
}
$sth_select_hash->finish;

# Cleanup.
$dbh->disconnect;

------=_NextPart_000_0061_01C2EE42.20641F80
Content-Type: text/plain; charset=us-ascii

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2105@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.
------=_NextPart_000_0061_01C2EE42.20641F80--

RE: Dropping Trailing Whitespace

am 20.03.2003 00:05:29 von Gabriel Weinberg

RE: Dropping Trailing Whitespace

am 20.03.2003 08:00:32 von Jochen Wiedmann

---MOQ1048143632368d95dcc16b2e1d7d6f886dba02e54a
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hi, Gabriel,

I tried the following from the "mysql" command line prompt:

mysql> insert into test (hash) values ('abcdefghiklmnopq');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (hash) values ('abcdefghiklmnop');
Query OK, 1 row affected (0.00 sec) mysql> insert into test (hash) values
('abcdefghiklmnop ');
ERROR 1062: Duplicate entry 'abcdefghiklmnop' for key 2

In other words: The database itself is cutting the trailing blank
from the command line, for whatever reason.

I would personally consider this a bug and would suggest that
you post your CREATE TABLE statement and the above statements
into the MySQL users mailing list. Please put me on the cc list.

Regards,

Jochen

---MOQ1048143632368d95dcc16b2e1d7d6f886dba02e54a
Content-Type: text/plain; charset=us-ascii

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread2107@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.
---MOQ1048143632368d95dcc16b2e1d7d6f886dba02e54a--

RE: Dropping Trailing Whitespace

am 20.03.2003 08:00:32 von Jochen Wiedmann