So far, we have discussed data types that store data that is intended to be read by humans. Some types of data are not intended to be read directly by humans, but can still be stored in a database. For example, a picture from a digital camera, or a song from a CD, is stored as a series of numbers. These numbers are almost meaningless to a human. Digitized pictures and sounds can be stored as BINARY data, however. solidDB® supports three binary data types: BINARY, VARBINARY, and LONG VARBINARY (or BLOB).
In most cases, you will read and write binary data using the ODBC (Open DataBase Connectivity) API from a C program, or the JDBC API from a Java program. However, it is possible to insert data into a binary field using a utility that executes SQL statements. To insert a value into a binary field, you must represent the value as a series of hexadecimal numbers inside single quotation marks. For example, if you wanted to insert a series of bytes with the values 1, 9, 11, 255 into a binary field, you would execute:
INSERT INTO table1 (binary_col) VALUES (CAST('01090BFF' AS VARBINARY));
Because this command instructs the server to CAST the value to type VARBINARY, the server automatically interprets the string as a series of hexadecimal numbers, not as a string literal.
You may also insert a string literal directly, for example:
INSERT INTO table1 (binary_col) VALUES ('Thank you');
When you retrieve the data via solsql (solidDB® utility for executing SQL statements), the return value from a binary column is expressed in hexadecimal, whether or not you originally entered it as hexadecimal. Thus, after you insert the value “Thank you”, if you select this value from the table you will see:
5468616E6B20796F75
where 54 represents capital T, 68 represents lower-case h, 61 represents lower-case a, 6E represents lower-case n, and so on.
Note also that for long values only the first several digits are shown.