Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Conversion of VARCHAR to VARBINARY

Help
Anonymous
2012-04-11
2014-01-19

  • Anonymous
    2012-04-11

    Hello,
    does anybody know if it is possible to convert a VARCHAR to VARBINARY in a INSERT statement?

    I have following table:

    CREATE TABLE "Flashpoint$CM Job Management" (
        ID int NOT NULL,
        Version varchar(10) NOT NULL,
        "Service Name" varchar(245) NOT NULL,
        Command varchar(250) NOT NULL,
        Parameter VARBINARY(1000) NULL,
        Status int NOT NULL,
        "Exit Code" int NOT NULL,
        "Update DateTime" datetime NOT NULL,
        "Unique ID" VARCHAR(40) NOT NULL,
        CONSTRAINT "Flashpoint$CM Job Management$0" PRIMARY KEY (ID)
    );
    

    In order to initialize my JUnit tests fixtures I would like to execute a bunch of  INSERT statments like this:

    INSERT INTO "Flashpoint$CM Job Management"
          (ID, Version, Status, "Exit Code", "Update DateTime", Parameter, "Service Name", Command, "Unique ID")
    VALUES
          (1, '1.0.0', 0, 3, '2012-04-09 00:22:00', 
          CAST('8717418266882,4030521721746,4013549874632,4006680054445,012345678' AS VARBINARY),
          'TEST_SERVICE_A', 'TEST_TASK_A', '36447280-81d8-11e1-b0c4-0800200c9a66');
    

    This is not working and I have following error message:

    org.hsqldb.HsqlException: data exception: invalid character value for cast
    

    Perhaps someone could help me or show me another approach to do that ;)

    Thanks!

    Wagner

     
  • Fred Toussi
    Fred Toussi
    2012-04-11

    Your string is not a hex string, therefore no built-in function or cast can be used to convert it.

    It is easy to write your own converter function. Something like this:

    public static byte[] stringToByte(String s) {
        byte[] b = new byte[s.length()];
        // loop and convert charaters to bytes according to your requirements
       return b;
    }
    CREATE FUNCTION STRING_TO_BINARY(S VARCHAR(10000)) RETURNS VARBINARY(1000) LANGUAGE JAVA ...
         EXTERNAL NAME 'full-class-and-function-name'