SQL Anywhere Functions On The Client Side

Question: How do I use SQL Anywhere's builtin functions like COMPRESS() and ENCRYPT() on the client side of an application?

Answer: The short answer is "You can't!", according to this response.

The long answer is,

• if you're willing to start an empty local database using the SQL Anywhere personal server on the same machine that's running your client application

• and you're willing to wave your arms and declare "This embedded database shall henceforth be regarded as part of the client side!"

• then yes, you can invoke COMPRESS() and ENCRYPT() without passing the data back and forth across the network to your "real" database.

First, here's an all-SQL demonstration of the four functions that are going to be invoked on the client side:

BEGIN
DECLARE @input         LONG VARCHAR;
DECLARE @compressed    LONG BINARY;
DECLARE @encrypted     LONG BINARY;
DECLARE @decrypted     LONG BINARY;
DECLARE @output        LONG VARCHAR;
DECLARE @key           LONG VARCHAR; 

SET @key = '4wlPnLAs34cHUlgSVLUUbIzany3G3RkXQOjouGo';

SET @input      = 'Hello, World!';
SET @compressed = COMPRESS ( @input, 'zip' );
SET @encrypted  = ENCRYPT ( @compressed, @key, 'AES256' );
SET @decrypted  = DECRYPT ( @encrypted, @key, 'AES256' );
SET @output     = CAST ( DECOMPRESS ( @decrypted, 'zip' ) AS LONG VARCHAR );

SELECT @input, @compressed, @encrypted, @decrypted, @output;
END;

The code on lines 11 through 17 compresses a string, then encrypts it, decrypts it and decompresses it to show the final output is the same as the original input:

@input,@compressed,@encrypted,@decrypted,@output
Hello, World!,0x789cf348cdc9c9d75108cf2fca495104001f9e046a,0x652c6bb5bf26f146592932352d63e0976ee8ed4b5f59f19682664d42c3a0a5d8543b4aceda8e98ae5875e0f2eef8f029,0x789cf348cdc9c9d75108cf2fca495104001f9e046a,Hello, World!

Here's the output from a PowerBuilder application that does the same thing:



The client-side PowerBuilder code is quite a bit more verbose than the SQL. First of all, the local database must be started and a connection established; in this example the Window Open event is used to do that:

// Auto-start local database with auto-stop enabled.

gtr_local = CREATE u_tr_sqlca

gtr_local.DBMS = 'ODB'

gtr_local.DBParm &
   = "ConnectString='Driver=SQL Anywhere 12;" &
   + "UID=dba;PWD=sql;" &
   + "START=C:\Program Files\SQL Anywhere 12\Bin64\dbeng12.exe -o C:\data\dbeng12_log_local.txt -os 10M -x none -zl -zp -zt;" &
   + "DBF=C:\data\local.db'," &
   + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"
   
CONNECT USING gtr_local;

IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', 'CONNECT USING gtr_local failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

// Create a temporary table for holding blob data.

EXECUTE IMMEDIATE 'CREATE LOCAL TEMPORARY TABLE blob ( pkey INTEGER PRIMARY KEY, data LONG BINARY )'
   USING gtr_local;
 
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'CREATE failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

// Insert a row for use by subsequent UPDATEBLOB statements.

INSERT INTO blob VALUES ( 1, '' )
   USING gtr_local;
 
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'INSERT failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

The code on lines 1 through 25 uses a DSN-less connection to auto-start the local database C:\data\local.db.

The code on lines 27 through 59 creates and initializes a local temporary table used to pass a single LONG BINARY value from step to step in the PowerBuilder application; this table is used to get around PowerBuilder's inability to handle blob values as easily as other data types.

The application uses two global variables: gtr_local maintains a separate connection to the local database and gs_key holds the key used by the ENCRYPT and DECRYPT funtions.

u_tr_sqlca gtr_local 
string gs_key = '4wlPnLAs34cHUlgSVLUUbIzany3G3RkXQOjouGo'

The u_tr_sqlca data type used to define gtr_local is a user-defined data type based on the standard PowerBuilder transaction object; in simple terms, gtr_local is a separate copy of the SQLCA communication area.

COMPRESS()

Here's the code for the "Compress" button clicked event:

blob lblob_compressed

// Erase the output fields.

sle_compressed.text = ''
sle_encrypted.text = ''
sle_decrypted.text = ''
sle_output.text = ''

// COMPRESS() the input into a local blob variable.

SELECTBLOB COMPRESS ( :sle_input.text, 'zip' )
   INTO :lblob_compressed
   FROM dummy
   USING gtr_local;
  
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'SELECT COMPRESS failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

sle_compressed.text = "Compressed OK: length = " +  String ( len ( lblob_compressed ) )

// Save the local blob variable in the local blob table.

UPDATEBLOB blob 
   SET blob.data = :lblob_compressed 
   WHERE blob.pkey = 1
   USING gtr_local;
  
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'UPDATEBLOB failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

The PowerBuilder SELECTBLOB statement on lines 12 through 15 returns the compressed value to the local variable lblob_compressed.

The UPDATEBLOB statement on lines 33 through 36 is used to store that value in the local temporary table for use in the next step... if there is no "next step", if all you need is the compressed value, then the UPDATEBLOB is not required.

Neither the local table nor the UPDATEBLOB statement should ever be necessary, but it appears that the SELECTBLOB statement in the next PowerBuilder script can't handle a blob being passed to it as a host variable in the SELECT list as well being returned to another host variable in the INTO clause.

ENCRYPT()

The code for the "Encrypt" button clicked event is quite similar except that the SELECTBLOB statement works on the blob.data column passed from the previous script via the local temporary table rather than a host variable like :sle_input.text in the previous script:

blob lblob_encrypted

// ENCRYPT() the saved blob column into a local blob variable.

SELECTBLOB ENCRYPT ( blob.data, '4wlPnLAs34cHUlgSVLUUbIzany3G3RkXQOjouGo', 'AES256' )
   INTO :lblob_encrypted
   FROM blob
   WHERE blob.pkey = 1
   USING gtr_local;
 
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'SELECTBLOB ENCRYPT failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

sle_encrypted.text = "Encrypted OK: length = " +  String ( len ( lblob_encrypted ) )

// Save the local blob variable in the local blob table.

UPDATEBLOB blob 
   SET blob.data = :lblob_encrypted 
   WHERE blob.pkey = 1
   USING gtr_local;
  
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'UPDATEBLOB failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

DECRYPT()

The code for the "Decrypt" button clicked event is almost identical, SELECTBLOB followed by UPDATEBLOB, except that it calls DECRYPT instead of ENCRYPT:

blob lblob_decrypted

// DECRYPT() the saved blob column into a local blob variable.

SELECTBLOB DECRYPT ( blob.data, :gs_key, 'AES256' )
   INTO :lblob_decrypted
   FROM blob
   WHERE blob.pkey = 1
   USING gtr_local;
 
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'SELECTBLOB DECRYPT failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

sle_decrypted.text = "Decrypted OK: length = " +  String ( len ( lblob_decrypted ) )

// Save the local blob variable in the local blob table.

UPDATEBLOB blob 
   SET blob.data = :lblob_decrypted 
   WHERE pkey = 1
   USING gtr_local;
  
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'UPDATEBLOB failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

DECOMPRESS()

The code for the "Decompress" button clicked event is simpler because there is no "next step" so there's no need for a local blob variable:

// DECOMPRESS() the saved blob column into into the display field.

SELECT CAST ( DECOMPRESS ( blob.data, 'zip' ) AS LONG VARCHAR )
   INTO :sle_output.text
   FROM blob
   WHERE blob.pkey = 1
   USING gtr_local;
 
IF gtr_local.SQLCODE <> 0 THEN
   MessageBox ( 'Error', &
      'SELECT DECOMPRESS failed:' &
      + '~r~nSQLCode = ' &
      + String ( gtr_local.SQLCode ) &
      + '~r~nSQLDBCode = ' &
      + String ( gtr_local.SQLDBCode ) &
      + '~r~n' &
      + gtr_local.SQLErrText )
   RETURN
END IF

Many other client-side development environments suffer from similar limitations when it comes to handling blobs, different from PowerBuilder's but just as annoying. However, SQL Anywhere builtin functions that don't involve LONG VARCHAR and LONG BINARY arguments and return values (and there are lots of those) shouldn't bump up against these annoyances, and they should be easier to implement on the client side.