Wednesday, June 23, 2010

ADS 10 Tip #18 – Handling Binary Data with SQL

Two new binary scalar functions have been added to the SQL engine,Char2Hex and Hex2Char . These functions allow you to encode text as binary and convert binary into a string respectively. The functions can help save space in your tables. For example a standard GUID is a 32 byte hexadecimal string value. Using Char2Hex you can store this in a RAW 16 field saving 16 bytes of space.

You can then use the Hex2Char function to retrieve the raw bytes as a readable string value. Indexes can be created on the raw field allowing you to perform searches or use the field as a primary key. As an alternative to using Char2Hex you can precede a hexadecimal string with an x. The following SQL example demonstrates using these functions.

// GUID table has a RAW 16 field type that stores bytes
SELECT * FROM GUID

// Insert a value into the table
INSERT INTO GUID VALUES ( Char2Hex('d65c98afbdc1b949b4c559d367a474d5'),
  'Test One')
  
// Alternate method for inserting binary values  
INSERT INTO GUID VALUES ( x'096fb317bd33f049b2ed889f234b222b', 
  'Test Two')

While storing the data in a binary format can save space, it isn't very useful for you users. Below is an example of selecting the data from the table.

SELECT * FROM Guid

 SQL_Binary1

You an use the Hex2Char function to convert the data from binary format back into a readable string, as shown below.

SELECT Hex2Char(ID) AS Id, Name FROM Guid

SQL_Binary2

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

No comments: