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:
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'
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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:
Perhaps someone could help me or show me another approach to do that ;)
Thanks!
Wagner
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'