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

    adding hexadecimal values

    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.
        • 1. Re: adding hexadecimal values
          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.
          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.
          • 2. Re: adding hexadecimal values
            807607
            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)
            • 3. Re: adding hexadecimal values
              796440
              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)
              • 4. Re: adding hexadecimal values
                807607
                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.
                • 5. Re: adding hexadecimal values
                  796440
                  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')";
                  • 6. Re: adding hexadecimal values
                    796440
                    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.
                    • 7. Re: adding hexadecimal values
                      807607
                      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.