Thanks very much Paul. My day has just improved.
On Fri, 2004-12-03 at 16:53, Paul DuBois wrote:
> At 16:34 -0800 12/3/04, Mark Maunder wrote:
> >It looks like when mysql coerces character strings into integers, it
> >turns them into signed int's. Obviously if the column is unsigned, this
> >is a problem. Don't use quotes you say. Problem is that the perl DBI API
> >seems to put quotes around everything. So when I grab a really really
> >large integer from the db using the perl api, and then try to get a
> >child record referencing the same large integer ID, the DB doesn't give
> >me anything because it's coercing a large chunk of text into a signed
> >integer and truncating it.
>
> You don't indicate when it is that DBI is putting "quotes around
> everything", but if what you mean is that values bound to placeholders
> get quoted, you can suppress that. perldoc DBI shows this information:
>
> Data Types for Placeholders
>
> The "\%attr" parameter can be used to hint at the data type the
> placeholder should have. Typically, the driver is only interested
> in knowing if the placeholder should be bound as a number or a
> string.
>
> $sth->bind_param(1, $value, { TYPE => SQL_INTEGER });
>
> As a short-cut for the common case, the data type can be passed
> directly, in place of the "\%attr" hash reference. This example is
> equivalent to the one above:
>
> $sth->bind_param(1, $value, SQL_INTEGER);
>
> The "TYPE" value indicates the standard (non-driver-specific) type
> for this parameter. To specify the driver-specific type, the driver
> may support a driver-specific attribute, such as "{ ora_type => 97
> }".
>
> The SQL_INTEGER and other related constants can be imported using
> use DBI qw(:sql_types);
>
> See "DBI Constants" for more information.
>
>
> >
> >Another not-really-a-bug but definitely a pitfall. And it sucks because
> >after not being able to use md5 hashes to index my records using
> >BINARY(16) because binary isn't really binary because it cuts off
> >spaces, I'm losing a digit of my next-best-thing thanks to unsigned
> >integers which are actually signed.
> >
> >Don't make me go spend my life savings on Oracle!
> >
> >Here's an example in case you're really bored. The problem below exists
> >because 9358082631434058695 > 2^63
> >
> >##First with no quotes around the large integer:
> >mysql> select job_id from wordbarrel_9a where
> >job_id=9358082631434058695;
> >+---------------------+
> >| job_id |
> >+---------------------+
> >| 9358082631434058695 |
> >+---------------------+
> >1 row in set (0.00 sec)
> >
> >##Then with quotes:
> >mysql> select job_id from wordbarrel_9a where
> >job_id='9358082631434058695';
> >Empty set (0.00 sec)
> >
> >mysql> desc wordbarrel_9a;
> >+----------+---------------------+------+-----+---------+-------+
> >| Field | Type | Null | Key | Default | Extra |
> >+----------+---------------------+------+-----+---------+-------+
> >| job_id | bigint(20) unsigned | | PRI | 0 | |
> >+----------+---------------------+------+-----+---------+-------+
--
Mark D. Maunder <[EMAIL PROTECTED]>
http://www.workzoo.com/
"The Best jobs from the Best Job Sites."
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
