There are many ways to approach this. How are you receiving the IP data? Are
you reading a file or other stream or are you trying to process the table
creation by reading a column from a previously populated table through a select
statement?
The functions, inet_ntoa() and inet_addr(), are part of most networking
libraries. These are the common functions to convert dotted quad notation. If
you wanted to write you own function, an IPv4 address is broken down as follows:
AAA.BBB.CCC.DDD
\ \ \ \
\ \ \ DDD
\ \ CCC x CCC
\ BBB x BBB x BBB
AAA x AAA x AAA x AAA
Add the results and you have your unique 32bit number.
eg. 10.10.10.1 = 10,000 + 1,000 + 100 + 1
= 11,101
If you are not able to pre-process (scrub) the incoming data programmatically,
you would need to create a UDF in MySQL to perform the conversion, or,
alternatively, if you want to use MySQL SELECT statement as-is could replace
the 'period' with an 'underscore' using MySQL's built-in string functions like
so:
Assumptions: Reading IP address from an existing table named IP_Addresses with
a column named ip_address and a column named status.
SELECT "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert create
options here]" FROM IP_Addresses WHERE status ="ACTIVE"
You would obviously add your "CREATE TABLE" options and "INTO OUTFILE" options
as needed.
This would be an alternative to converting IPv4 to 32bit Integer.
I hope this helps...
If at all possible, it is probably best to continue in the MySQL list, there
are some pretty clever people out there
Pat...
[EMAIL PROTECTED]
CocoNet Corporation
SW Florida's First ISP
825 SE 47th Terrace
Cape Coral, FL 33904
----- Original Message -----
From: Chance Ellis
To: Patrick
Sent: Monday, October 03, 2005 2:22 PM
Subject: Re: Table names with periods
Patrick,
I have been trying to figure out how I can convert an IP address to a 32bit
integer within a SQL create statement.
Is this possible or am I thinking about this all wrong? The input I am given
is a straight IP address. I have no way of modifying it other than some option
in the SQL create statement is possible. I want to create a new table for each
IP address. Without getting too much into the details, these are my
requirements and I have been wasting alot of time trying to figure out how to
change this string in the create statement.
Any help you can provide is greatly appreciated.
Thanks!
Chance
On 9/28/05, Patrick <[EMAIL PROTECTED]> wrote:
Historically any form of punctuation, parameter delimiter, or filepath
delimiter in either a database name, field or column name, file or table
name would not be recommended; even if the RDBMS or File Handler allows it.
If you are able to stick to alphanumeric characters using underscores
characters if needed for clarity, you go a long way for portability across
various operating systems. Also, IPv4 addresses are readily converted to
single 32bit integers that minimize the need for dotted quartets.
Early versions of MySQL allowed periods. This caused OS incompatibility
issues. To my knowledge this was fixed prior to version 3 and you are no
longer allowed periods in database or table names. This, in my thinking, is
a good thing by assuring greater portability and easier migration.
I hope this helps.
Pat...
[EMAIL PROTECTED]
CocoNet Corporation
SW Florida's First ISP
825 SE 47th Terrace
Cape Coral, FL 33904
