I am using MySQL database.
I have one table having column with datatype binary(16).
I need help with the insert statement for this table.
Example:
CREATE TABLE `assignedresource` (
`distid` binary(16) NOT NULL
)
insert into assignedresource values ('9fad5e9e-efdf-b449');
Error : Lookup Error - MySQL Database Error: Data too long for column 'distid' at row 1
How to resolve this issue?
You should remove the hyphens to make the value match the length of the field...
Example:
CREATE TABLE `assignedresource` (
`distid` binary(16) NOT NULL
)
insert into assignedresource values ('9fad5e9eefdfb449');
Also, MySQL standard is to use this notation to denote the string as binary... X'9fad5e9eefdfb449', i.e.
insert into assignedresource values (X'9fad5e9eefdfb449');
Well, assuming that you want to strictly insert a hexadecimal string, first you need to remove the dashes and then "unhex" your string before inserting it into a binary(16) data type column, the code would go like this:
INSERT INTO `assignedresource` VALUES(UNHEX(REPLACE('9fad5e9e-efdf-b449','-','')));
Also... the "usable" data you are inserting is actually 8 bytes after undashing it, so binary(8) would do fine if you plan on not storing the dashes.
You can strip the hyphens and perpend 0x to the value unquoted, like this:
insert into assignedresource values (0x9fad5e9eefdfb449);
As well as, as this (mentioned in other answers):
insert into assignedresource values (X'9fad5e9eefdfb449');
Both are valid notation for a hexadecimal literal.
Your string is 18 char long, change the database
CREATE TABLE `assignedresource` (
`distid` binary(18) NOT NULL
)
