On Wednesday, August 15, 2012 2:16:44 PM UTC-7, g_f wrote:
>
> I have to store IPv4 and IPv6 values in a MySQL table. I'm using
> varbinary(16) as the type and size because it'll hold both values as
> integers.
>
> The values being returned in the row hash are strings, instead of
> integers, so I want to cast them back to ints. I'm doing that using:
>
> ips = DB[:ips].select(:id, :ip, :mask, :ip_type, :ip_int.cast_numeric,
> :min_subnet_ip_int.cast_numeric, :max_subnet_ip_int.cast_numeric)
>
> That part of the magic is working, mostly, but I'm seeing the values
> returned as signed ints, instead of unsigned:
>
> SELECT `id`, `ip`, `mask`, `ip_type`, CAST(`ip_int` AS SIGNED),
> CAST(`min_subnet_ip_int` AS SIGNED), CAST(`max_subnet_ip_int` AS SIGNED)
> FROM `ips` LIMIT 1
>
> and
>
> {:id=>1, :ip=>"10.255.2.77", :mask=>"255.255.255.248",
> :ip_type=>"network", :"CAST(`ip_int` AS SIGNED)"=>184484424,
> :"CAST(`min_subnet_ip_int` AS SIGNED)"=>184484424,
> :"CAST(`max_subnet_ip_int` AS SIGNED)"=>184484431}
>
> That's gonna bust stuff left and right in my code because the original
> hash keys are gone.
>
> Right now I think I'd be better off to add some code to convert strings to
> integers when they'll pass a simple Integer("1") cast, something like:
>
> class Hash
> def v_to_int
> Hash[
> self.map{ |k,v|
> v = Integer(v) rescue v
> [k,v]
> }
> ]
> end
> end
>
> I realize this is probably a knee-jerk reaction to something with a simple
> fix. Is there a better way?
>
An unsigned->signed conversion shouldn't produce problems except in the
case of overflow. If the problem is an overflow issue, you can use
.cast_numeric(:unsigned) to cast to unsigned instead of signed.
You are getting entries such as :"CAST(`max_subnet_ip_int` AS SIGNED)" in
your result hash because you aren't aliasing the casts and those are the
default column names used by MySQL. You probably want:
:ip_int.cast_numeric.as(:ip_int)
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/PYyQ0iS_wloJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
