7 Replies Latest reply: Nov 16, 2006 8:55 PM by 807607

I am importing from a flat file to a mysql db. I am having trouble with the primary keys. In the delimited file I read "x1234567887654321123456788765a321". This is a hexadecimal. I am trying to insert this value in a field of CHAR(16). Any ideas on how to solve this or where I might find the solution would be greatly appreciated.
I am importing from a flat file to a mysql db. I am
having trouble with the primary keys. In the
"x1234567887654321123456788765a321". This is a
No, this is a string representation of a hexadecimal number, that is 32 characters in length (minus the hex designator).
I am trying to insert this value in a
field of CHAR(16). Any ideas on how to solve this or
where I might find the solution would be greatly
appreciated.
Do you have to store these intact? (as strings like they are given or can you convert them and store them as numbes)

If your goal is to save them in strings just make the fields char(32) or char(33) if you want to store the designator. If you must put them as the string values given, but fit into char(16), then you can do a couple of things:

1 - strip off the designator and cut the string in half and use to of your char(16) fields, half in one and half in the other.

or

2 - come up with some packing algorithm that will allow you to store the characters in a smaller space. Convert to hex each pair of characters and store the result as a character in 1 of the character cells of your 16 byte data type. You'll have to make a decoder for it to make any sense.

or

3 - convert it to a number and treat it as such from that point on.
Thank you for the response.
Do you have to store these intact? (as strings like
they are given or can you convert them and store them
as numbes)
Yes, I actualy do want to store them as a number. I would like to store the original hex value. Basically I want to take this string x12345678876af321123456788765a321" and turn it back into its original form. Unfortunately I am limited, I have to store this value in a field of Char(16)
Yes, I actualy do want to store them as a number.
I would like to store the original hex value.
Basically I want to take this string
x12345678876af321123456788765a321" and turn it back
into its original form.
Do you want to store a number or a string? If it's a number, there is no "hex value." The hex-ness of it comes when you go to represent it as a string.

If you want to store it as a number, why are you storing it in a char field? If you want to store it as a string, then the column needs to be defined to be wide enough to hold any possible valid string.

At the moment, your requirements sound not well specified and rather wack.

Unfortunately I am limited,
I have to store this value in a field of Char(16)
Let me back up a step.
We are moving our database from Frontbase to mysql. I have dumped the frontbase into a flat file. In our old DB the primary keys were of type BIT(128). I beleive the equivalent(suitable equivalent anyways) to this in mysql is CHAR(16). I have been able to enter enter data into the Database using:

insert into foo (d) values (x'7330f08c0d02bf0091796dbac0a80006');

It is displayed in the DB as: 8u�����%�DLc��
(This is just an example the sql statement did not produce this row in the DB.)

the field is defined as CHAR(16). Now I am trying to enter data similar to this ie"x7330f08c0d02bf0091796dbac0a80006" from within a small java app. I beleive I need to convert this string. I am unsure as to why I can enter the data through sql but not through the java app. I am currently getting this error

com.webobjects.foundation.NSValidation\$ValidationException: The uuid property of TableName exceeds maximum length of 16 characters
Again thanks for the responses.
Ah, now we're getting somewhere.

It seems that in mysql's SQL, x'whatever' is a special notation that does some translation of that string.

I don't know what that translation is or how to do it through JDBC (if you even can).

You might try just putting it literally in there:
``String insertStr = "insert into blah values(x'whatever')";``
string. I am unsure as to why I can enter the data
through sql but not through the java app.
Because the mysql client is taking advantage of a mysql-specific thing (at least I assume it's mysql-specific) that Java doesn't know about. Not sure if that special thing is in mysql's sql or in the client app itself.
insert into foo (d) values
(x'7330f08c0d02bf0091796dbac0a80006');

It is displayed in the DB as: 8u�����%�DLc��
(This is just an example the sql statement did not
produce this row in the DB.)
When you output the char(16) to your screen do you want it to look like a hex value, because the garbage you are showing is what you will get if you put numbers in the individual characters:

example: you want to display FF so you put 255 in a character value, what will be displayed is the character represented by 255 (basically garbage, not FF).

If you want the TEXT type of representation to display, then you are going to have to change to a CHAR(32) and store the TEXT values.
the field is defined as CHAR(16). Now I am trying to
enter data similar to this
ie"x7330f08c0d02bf0091796dbac0a80006" from within a
small java app. I beleive I need to convert this
string. I am unsure as to why I can enter the data
through sql but not through the java app. I am
currently getting this error

com.webobjects.foundation.NSValidation\$ValidationExcep
tion: The uuid property of TableName exceeds maximum
length of 16 characters
Unless something is gravely wrong, then the error is just saying that your table name is longer than 16 characters and the interface/sql will not support it.
Again thanks for the responses.
You are welcome.