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

// 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.



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


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

No comments: