Advantage Developer Zone


Encryption and the Advantage TAdsQuery Component

Friday, July 14, 2006

Working with encrypted data is quite different with TAdsQuery than it is with TAdsTable. With the TAdsTable component, the TAdsTable.AdsEnableEncryption( strPassword: string ) is simply used after the table has been opened and the table is now ready for use. With TAdsQuery, because of the nature of SQL, encryption is not as straight forward.

SQL result sets come in two varieties, live cursors and static cursors. Live cursors are updateable result sets and static cursors are read-only result sets. With Advantage, simple Select statements can result in live cursors but more complex statements such as Joins result in static cursors. When a static cursor is created, it is physically a new temporary table. If the tables that were used in the SQL query to build the cursor are encrypted, the method TAdsQuery.AdsStmtSetTablePassword must be used to supply the password for each encrypted table in the query BEFORE the query is opened/executed. In this scenario, the resulting static cursor is not automatically encrypted. To force the resulting static cursor to be encrypted, the method TAdsQuery.AdsStmtEnableEncryption must also be used BEFORE the query is opened/executed.

The TAdsQuery component cannot be used to permanently encrypt or decrypt tables or records. TAdsTable methods must be used such as TAdsTable.AdsEncryptTable. Also, using TAdsQuery.AdsStmtEnableEncryption on ANY query makes its result set a static cursor. This means that even if the query is a simple select statement, using TAdsQuery.AdsStmtEnableEncryption will cause a temporary, encrypted, read-only result set to be created.

Static Cursor Example
In this example, the entire result set is encrypted, even though the EMPLOYEE_HISTORY table is not physically encrypted. This is because the method AdsStmtEnableEncryption was used to create an encrypted static cursor. In fact, none of the tables are required to be encrypted to use AdsStmtEnableEncryption. The result would be an encrypted result set while the source tables are not encrypted.

AdsQuery1.SQL.Add( ’SELECT a.company_name, b.lastname, c.history

FROM company a, employee b, employee_history c
WHERE ( AND (’ );

//Enable encryption on the static cursor result set.
AdsQuery1.AdsStmtEnableEncryption( ’result_pwd’ );

//Set the password for each table involved in the query.
AdsQuery1.AdsStmtSetTablePassword( ’company’, ’comp_pwd’ );
AdsQuery1.AdsStmtSetTablePassword( ’employee’, ’emp_pwd’ );


Live Cursor Example
AdsQuery1.SQL.Add( ’INSERT INTO company ( company_name )

VALUES(’ + ’’’Extended Systems Inc.’’’ +’)’ );

//Set the password for the table. Do not use AdsStmtEnableEncryption here
//or the result set will be read-only.
AdsQuery1.AdsStmtSetTablePassword( ’company’, ’comp_pwd’ );