How-to Guides‎ > ‎

Setting binary attributes

Polyhedra allows database administrators to define tables with binary attributes; a binary field can hold a arbitrary sequence of bytes, with the only length limit being down to memory constraints. Thus, you can define a table to hold numbered BLOBs as follows:

create table test
( id integer primary key
, data binary
)

Using SQLC, it is possible to define a binary field value by means of a literal, expressed in hexadecimal notation:

SQL> insert into test values (1, x'12345678');
SQL> insert into test values (2, x'abcdef1234567890abcdef1234567890abcdef1234567890');
SQL> commit;
SQL> Transaction Done

Note however if you query this table using ‘select *’, for historic reasons the SQLC program will truncate the string representation of the binary values:

SQL> select * from test;
+------+---------------------+
| id   | data                |
+------+---------------------+
|    1 | X'12345678'         |
|    2 | X'abcdef1234567890' |
+------+---------------------+
Query Done: 2 records selected
SQL>

You can get round this limitation by a query such as ‘select id, binarytohexstring(data) from test’, which instructs the SQL engine to return the binary value as a hexadecimal string.

SQL> select id, binarytohexstring(data) data from test;
+------+----------------------------------------------------+
| id   | data                                               |
+------+----------------------------------------------------+
|   1  | '12345678'                                         |
|   2  | 'abcdef1234567890abcdef1234567890abcdef1234567890' |
+------+----------------------------------------------------+
SQL>

Note that there is no leading ‘X’ in the above output, as SQLC is being given a string (which it displays in quotes) rather than a binary value (which it truncates, then displays as a quoted sequence of hexadecimal pairs preceded by the X character). SQLC does NOT truncate the representation of binary values when it is writing them to a file, as shown by the following sequence:

SQL> writing 'temp.dat' select id+2,data from test;
Writing to file 'temp.dat'
Query Done: 2 records selected
SQL> reading 'temp.dat' insert into test; commit;
Reading from file 'temp.dat'
Reading Done (type COMMIT to send)
SQL> Transaction Done
SQL> select id, binarytohexstring(data) data from test;
+------+----------------------------------------------------+
| id   | data                                               |
+------+----------------------------------------------------+
|    1 | '12345678'                                         |
|    2 | 'abcdef1234567890abcdef1234567890abcdef1234567890' |
|    3 | '12345678'                                         |
|    4 | 'abcdef1234567890abcdef1234567890abcdef1234567890' |
+------+----------------------------------------------------+
Query Done: 4 records selected
SQL>

After this operation, the file temp.dat will contain two lines:

3,X'12345678'
4,X'abcdef1234567890abcdef1234567890abcdef1234567890'

Of course, representing everything as hexadecimal strings is awkward! At present, though, SQLC does not have a mechanism to, say, read a binary file and put its contents directly into a binary attribute of a database table, so to achieve this effect you have to write a small program. Using ODBC, for example, the easiest way is probably to open a connection as normal (using code based any of the example applications in the Polyhedra release kits if you wish), prepare a statement such as insert or update test(id,data) values (?,?), bind some variable to the parameters, and execute the statement. ('insert or update' is a Polyhedra-specific extension to SQL that will update a record if one exists with the indicated primary key; if not, then it creates one with the supplied values.) So, assuming hdbc is the SQLHANDLE for a connection, id is an SQLINTEGER holding the ID of the record we want to create or update, binsize is an int holding a byte count, and buffer is an unsigned char* pointing at the data we want to upload, then (with the addition of some lines to test the values returned by the ODBC functions) you could use the following code snippet:

SQLRETURN      ret;
SQLHANDLE      hstmt  = SQL_NULL_HANDLE;
SQLINTEGER     cols[2];
SQLLEN         indicators[2];
// prepare a parameterised SQL statement
ret = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, hstmt);
ret = SQLPrepare (hstmt, (SQLCHAR*)"insert or update test values (?, ?)", SQL_NTS);
// bind the two parameters to id and buffer.
indicators[0] = 0;
ret = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
                        SQL_INTEGER, 0, 0, &id, sizeof(SQLINTEGER), &indicators[0]);
indicators[1] = binsize;
ret = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
                        SQL_VARBINARY, 0, 0, buffer, binsize, &indicators[1]);
// execute the statement, using the parameter values in the bound locations
ret = SQLExecute (hstmt);

(If you are going to do this just once in your program, you could use SQLExecDirect instead of SQLPrepare + SQLExecute, but the form above is more efficient for repeated execution with different parameter values.)