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
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:
Post a Comment