|TechTip: Cast Binary Data to Built-in Data Types|
|Tips & Techniques - Database|
|Written by Michael Sansoterra|
|Friday, 22 July 2011 00:00|
Give DB2 new functionality with the ability to CAST from binary data.
Whether it be a hash value, raw data from another system, or something else, every once in a while, I find myself manipulating binary data within an application. However, in SQL, it's a royal pain to convert binary data to another built-in data type.
DB2 for i supports the casting of many built-in data types to binary. However, it does not support the reverse scenario of changing binary data to a built-in type. The casting of binary data to a built-in data type such as an integer will earn your application an SQL0461 error: "CAST from BINARY to INTEGER not supported." The good news is that user-defined functions can aid in the casting of binary data to another data type.
(The DB2 for i cast specification that lists the supported casting of the various built-in data types can be found here within the DB2 for i SQL Reference.)
Since, as of i5/OS 7.1, there is little intrinsic support for casting binary data to a built-in type, this is a programming task we'll need to tackle. A scalar user-defined function is the appropriate vehicle that will enable SQL to receive binary data and translate it to the appropriate built-in data type. Shown here is the use of a hypothetical user-defined function called Bin2Int that receives a 4-byte binary input and returns a standard 4-byte integer:
DECLARE @IntegerVariable INTEGER;
So how do we write this function? There are two basic options available when writing scalar functions: SQL and external. An SQL function is written entirely in the SQL procedural language, and an external function is written in a high-level language such as RPG, COBOL, or Java. Because the SQL language doesn't have great support for interpreting and manipulating binary data (the original problem we started with), this is probably not the best option.
In contrast, RPG is a great language that can be used to convert binary data to a built-in numeric type. For example, using an RPG data structure, four bytes of contiguous data (assumed to be binary) can easily be interpreted as a 32-bit integer:
D BinData 4
D IntData 10I 0 Overlay(BinData)
As a result, the functions to convert binary data to a built-in type will be written as external function in RPG. How will DB2 know to use the RPG logic to do the conversion? The CREATE FUNCTION statement will instruct DB2 how to use the RPG code when the function is used. The attached RPG ILE service program DB2BINARYR contains several subprocedures that DB2 can use for casting binary data to another built-in type.
The subprocedures within the service program and a description of their function are listed here:
Each of these subprocedures has a corresponding CREATE FUNCTION statement in the RPG source. Each of these CREATE FUNCTION SQL statements will need to be executed using System i Navigator or STRSQL in order to register the subprocedures for use by SQL.
In case you're unfamiliar with using RPG code with SQL, here is a sample CREATE FUNCTION statement:
CREATE FUNCTION xxxx/BIN2INT
PARAMETER STYLE GENERAL
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'xxxx/DB2BINARYR(BIN2INT)'
This CREATE FUNCTION is used to register function code with DB2; it tells DB2 about the parameters passed to the function and identifies what the function will return when it's completed. It also gives additional information, such as the language of the function and the location of the program (or service program). When SQL attempts to process a statement that invokes the BIN2INT scalar function, DB2 uses the information from this CREATE FUNCTION statement to locate and invoke the RPG code in service program DB2BINARY2 subprocedure BIN2INT.
To find out more about using CREATE FUNCTION, including the keywords, refer to the CREATE FUNCTION section of the DB2 for i SQL Reference manual.
If your eyes can read it, here is a sample of how the functions operate and what they'll return (using hex constants for illustrative purposes):
SELECT BIN2REAL(BINARY(X'41BC51EC')), /* 2.354 */
BIN2DOUBLE(BINARY(x'40378A3D70A3D70A')), /* 2.354 */
BIN2INT(BINARY(x'00000100')), /* 256 */
BIN2SMALL(BINARY(x'7FFF')), /* 32767 */
BIN2BIGINT(BINARY(x'000000000000001F')), /* 31 */
BIN2DECIMAL(BINARY(x'000000000512345F')),/* 5.12345 */
/* 5.12345 */
CHAR(BIN2CHAR(BINARY(x'C1C2C3')),3), /* ABC */
CHAR(BIN2CHAR_1208(BINARY(x'414243')),3) /* ABC */
All of these functions dealing with numeric data are "strongly" typed, meaning that they expect an input of the appropriate data type and length. These rules can be relaxed (say, passing only three bytes of an integer value instead of four) at the expense of more coding.
The BIN2NUMERIC and BIN2DECIMAL functions arbitrarily convert only numeric data defined with a precision of fifteen and a scale of five (15,5). Technically, the scale is not carried in the binary representation of these formats, so these functions can in fact convert any binary representation of numeric or decimal data with a precision of 15. However, the converted binary value will always be returned from the function with a scale of 5.
The BIN2CHAR function works with only single-byte character data and assumes that the binary data represents character data from the job's default Coded Character Set Identifier (CCSID). So if the SQL job's default CCSID is 37 (U.S. EBCDIC), SQL will simply assume that the binary data represents CCSID 37.
Additional variations of this function can be made by changing the CCSID of the VARCHAR return parameter. In this example, a new function is created that will interpret the binary data as CCSID 1208 (UTF-8).
CREATE FUNCTION xxxx/BIN2CHAR_1208
RETURNS VARCHAR(16384) CCSID 1208
PARAMETER STYLE GENERAL
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'xxxx/DB2BINARYR(BIN2CHAR)'
This function will assume that the binary data is stored as 8-bit Unicode. Since the subprocedure BIN2CHAR doesn't really do anything, the character data translation relies on some hocus-pocus magic that operates automatically when data is passed between RPG and DB2. To perform a true text conversion from binary data to any supported character set, the BIN2CHAR character conversion function would need to implement the technique shown in Converting Between Character Sets.
External user-defined functions provide the perfect way to add functionality to DB2 for i. Working with binary data in SQL no longer has to be a burdensome chore.
as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1
|Last Updated on Friday, 22 July 2011 00:00|