Wednesday, June 30, 2010

ADS 10 Tip #23 – Create a Database from an Entity Data Model

Visual Studio 2010 includes many improvements to the entity framework most notably the ability to create a database based upon a data model. This allows developers to use the Entity Designer to create the model for the database and then generate the appropriate SQL script to create the tables in a database. This is referred to as a model first design.

EDM_Properties Version 10 of the Advantage .NET Data Provider fully supports the Visual Studio 2010 development environment along with support for model first. The .NET data provider includes a Text Transformation Template (SSDLToADS.tt) which can translate the model definition into appropriate SQL statements.

The data definition language (DDL) script that is generated can be saved and the opened with Advantage Data Architect. After creating or connecting to an existing data dictionary the generated script can be run which will create tables and referential integrity rules as defined in the data model.

You can view a demonstration of the VS2010 support in this screencast available on the DevZone.

Tuesday, June 29, 2010

ADS 10 Tip #22 – New TAdsConnection Properties and Methods

Version 10 of the TAdsConnection component includes two new properties; ApplicationID and SQLTimeout and two new methods; CreateFromHandle and GetApplicationID.

The ApplicationID sets the application id to the specified string value. This information can be seen in the Management Utility in Advantage Data Architect (ARC). It is also returned with the sp_mgGetConnectedUsers system procedure. If the property is not set then the application id will be the name of the executable file.

The SQLTimeout property is the amount of time in seconds an SQL query should run before being canceled. This setting applies to all TAdsQuery components that are associated with the connection. Any query that exceeds the SQL Timeout value will be canceled and return a 7209 error.

The CreateFromHandle method creates a new connection to Advantage using the same properties as the connection associated with the handle. This is different than the CreateWithHandle which is used primarily for triggers.

The GetApplicationID method retrieves the application id for the current connection from the server. This is the value specified in the ApplicationID property or set using the sp_SetApplicationID. The application id can also be retrieved using the sp_GetApplicationID system procedure.

Monday, June 28, 2010

ADS 10 Tip #21 – TAdsEvent Component

Version 10 of the Advantage Components for Delphi include a new component TAdsEvent. This component makes it very simple for Delphi developers to add notifications to their applications. Advantage notifications are asynchronous and generally require the use of threading for implementation. The TAdsEvent component creates a secondary thread that can notify the main application thread whenever a notification is received.

Using the TAdsEvent component is quite simple you first assign an AdsConnecton which will be used to create another connection with the same properties. Next you can assign one or more events you want to be notified of in the Events property.

// Setup event component
evtAdmin.AdsConnection = cnAds;
evtAdmin.Events.Add('AdminMsg');
evtAdmin.Events.Add('OrderProcessed');
evtAdmin.EventTimeout = -1;

// Start listening for events
evtAdmin.Active := true;

The TAdsEvent component has two events which are used to interact with the main thread. When a notification is received the OnNotification event is raised. If an error occurs the OnLog event is raised. The OnNotification event passes back the event name, event count and any event data. The OnLog event passes back an error string. The code below demonstrates parsing the notification data to determine if it should be displayed to the user in a statusbar control. 

procedure TfOrders.evtAdminNotification(Sender: TObject; event: string;
  count: Integer; eventdata: string);
var
  bShow: boolean;
  sMsg: string;
begin
  bShow := True;

  if Event = 'AdminMsg' then
    sMsg := 'Admin Message: ' + eventdata
  else if Event = 'OrderProcessed' then
  begin
    GetInvoices;
    sMsg := 'Order list updated';
  end
  else
    bShow := False;

  // Update the StatusBar
  if bShow then
  begin
    StatusBar1.Panels[0].Text := sMsg;
    Application.ProcessMessages;
  end;
end;

You can see a demonstration of the TAdsEvent component in this screencast on the DevZone.

Friday, June 25, 2010

ADS 10 Tip #20 – Editing SQL Statements in Delphi

Along with the enhanced table editor for the TAdsTable component the SQL editor for the TAdsQuery component has been enhanced. You now get the full functionality of the SQL editor from Advantage Data Architect (ARC). This editor includes all of the following features

  • Syntax Highlighting
  • Code Templates
  • SQL Execution Plan
  • Index creation for query optimization
  • Exporting of query results
  • Find and Replace functionality
  • Preview query results
  • Verifying SQL syntax
  • Debugging of SQL scripts

You open the new enhanced SQL editor by clicking the build button on the SQL property of the TAdsQuery component. You can still edit your SQL statement in the Delphi Code Editor by clicking on the Code Editor button in the bottom left corner of the SQL window.

Delphi_SQLEditor

Thursday, June 24, 2010

ADS 10 Tip # 19 – Creating Tables Within the Delphi IDE

Delphi_CreateTable Version 10 of the Advantage Components for Delphi include some new enhanced property editors. Version 9 included support for modifying existing tables within the Delphi IDE, with version 10 you can also create a new table as well. An option has been added to the context menu of the TAdsTable component called "Create Table". Clicking on this option will bring up a Table Designer allowing you to create a new table. You must set the AdsConnection or DatabaseName property before creating a new table.

The Table Designer is the same one used in Advantage Data Architect (ARC) and allows you to create any of the supported table types. You can use the designer to add order, binary and full text search indexes. With dictionary bound tables you can add any of the additional table properties such as constraints. You can also load a pre-defined table schema using the Schema menu.

Delphi_TblDesigner

Wednesday, June 23, 2010

ADS 10 Tip #18 – Handling Binary Data with SQL

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

 SQL_Binary1

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

SQL_Binary2

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

Tuesday, June 22, 2010

ADS 10 Tip #17 – Scalar Functions

Advantage includes two distinct data engines an expression engine and an SQL engine. This gives developers the flexibility to choose the access method which provides the best speed and functionality. The Advantage SQL engine uses traditional filters in order to optimize performance. These filters are based on indexes which are created with the expression engine. Therefore for the best performance you should use SQL functions that have complimentary expression engine functions.

We have added many of the SQL functions to the expression engine which can make many queries more efficient. Many of these new functions involve date/time functionality for example: WEEK, QUARTER and MONTH. The image below shows a comparison of the SQL execution plans from version 9 and version 10. With version 10 I was able to create an index on MONTH( OrderDate ) which optimizes the query.

SQL Execution Plan

For a list of all the functions added to the expression engine refer to the What's New in Advantage 10.

Monday, June 21, 2010

ADS 10 Tip #16 – Bitwise Operators

Bitwise operators have been added to the Advantage SQL engine. The full list of supported operators is below. I included some example values demonstrating the result of each of the operators.

Operator Value 1 Value 2 Result
& ( AND ) 10101010 11001011 10001010
| ( OR ) 10101010 11001011 11101111
^ ( XOR ) 10101010 11001011 01100001
` ( NOT ) 10101010 NA 01010101
<< ( Shift Left ) 11011010 NA 10110100
>> ( Shift Right ) 11011010 NA 01101101

Option Value
Unique 1
Compound 2
Custom 4
Descending 8
Candidate 2048
Binary 4096
These operators can be very useful in resolving a bitmask. For example the Advantage Index Options are defined as a bitmask. The table to the right lists the index options and their associated values. Each of which corresponds to a particular bit value. These options can be added together to define the index type. For example a primary (candidate) key index would have the option 2051 ( Unique + Compound + Candidate ).

Since each of the option values are represented by a specific bit we can use the binary & operator to determine if a particular bit is set. Using our example value for a primary key 2051, we can determine which options have been selected using the & operator. For example IF ( 2051 & 2048 = 2048 ) we know one of the options is Candidate.

The user defined function (UDF) shown below accepts an index option value and returns a string description of the options specified.

CREATE FUNCTION IndexOptions ( Options INTEGER ) RETURNS CHAR ( 100 )
BEGIN
  DECLARE sReturn STRING;
  sReturn = '';

  IF ( Options & 1) = 1 THEN 
    sReturn = 'Unique, ';
  END IF;

  IF ( Options & 2) = 2 THEN 
    sReturn = sReturn + 'Compound, ';
  END IF;

  IF ( Options & 4) = 4 THEN 
    sReturn = sReturn + 'Custom, ';
  END IF;

  IF ( Options & 8) = 8 THEN 
    sReturn = sReturn + 'Descending, ';
  END IF;

  IF ( Options & 2048) = 2048 THEN 
    sReturn = sReturn + 'Candidate, ';
  END IF;

  IF ( Options & 4096) = 4096 THEN 
    sReturn = sReturn + 'Binary, ';
  END IF;

  // Remove the trailing comma
  return LEFT( sReturn, LENGTH(sReturn) - 1);
END;

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

Friday, June 18, 2010

ADS 10 Tip # 15 - ROWNUM

Another mechanism for doing paging with SQL is to use the ROWNUM function. ROWNUM assigns a unique number to each row as it is added to the resultset. See the example below.

SELECT ROWNUM() as ID, LastName, FirstName, CompanyName FROM CustomerList
WHERE State = 'CA'

Rownum1 

As you can see we get a unique number for each row that is returned. It is important to remember that the row number is assigned as the rows are added to the resultset. If you are using an ORDER BY clause the row numbers might not be in numerical order since the order may be applied after the resultset is populated. If there is an index that matches the ORDER BY then the results can be ordered prior to being added to the resultset. If no index exists then the order will be applied after the results have been added to the resultset as demonstrated below.

SELECT ROWNUM() as ID, LastName, FirstName, CompanyName FROM CustomerList
WHERE State = 'CA' ORDER BY LastName

Rownum2

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

Thursday, June 17, 2010

ADS 10 Tip #14 – Paging with SQL

The ability to divide the results of a query into smaller pieces is a very common need for disconnected or partially connected applications. These smaller resultsets can be much more efficient than passing back the entire result and allow for paging. For example; It is very common to see  controls like the ones shown below after a web search.

Paging

You can use the TOP function to limit the results of a query to a specific number of records or a percentage of the resultset. This command has been available since version 8. With version 10 we have added the START AT functionality which allows for paging. The basic syntax is TOP x START AT y, where x is the number of records to return and y is the first record in the result.

// Get the first 10 customers
SELECT TOP 10 
  CustNum, LastName, FirstName
  FROM Customer
  ORDER BY LastName
 
// Get the next 10 customers
SELECT TOP 10 START AT 11
  CustNum, LastName, FirstName
  FROM Customer
  ORDER BY LastName;

If you set the START AT value greater than the number of records an empty resultset will be returned. You cannot use parameters as the values, your logic must build the appropriate statement as you get additional pages.

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

Wednesday, June 16, 2010

ADS 10 Tip #13 – Boolean Expressions in the Select List

You can now put Boolean expressions in the SELECT list of any SQL statement. This allows you to see if a condition is true without having to filter the entire resultset. You can use any expression that returns a Boolean result.

// Using Boolean expression in Select List
SELECT Name, State, (State = 'ID') AS GemState FROM Company Order by 3 desc;

// Show true if the employee has worked at the company for 15 or more years
SELECT LastName, FirstName, ( TIMESTAMPDIFF( SQL_TSI_YEAR, DateOfHire, NOW()) > 14) 
  As "15+ Years" FROM Employee

The result of the second query is shown below

BooleanExpr

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

Tuesday, June 15, 2010

ADS 10 Tip #12 – Results of a Stored Procedure in a SELECT

Advantage now allows you to use the results of a stored procedure or system procedure as a table in a SELECT clause. This allows you to select specific columns, filter the results and even join the results to other tables or the results of other procedures.

// Using stored procedure with select clause
SELECT Name, Description, CodePage FROM (execute procedure sp_getcollations(NULL)) gc
WHERE CodePage = 1202

In an earlier tip I discussed the express queue and how it is used to prioritize worker threads. You can get information about the express queue using system procedures. In the example below I am using the results from two of these system procedures to get a list of which applications are currently running below the express queue threshold.

// Get a list of connections below the EQThreshold
SELECT UserName, ApplicationID, AverageCost FROM 
  (EXECUTE PROCEDURE sp_mgGetConnectedUsers()) u
WHERE AverageCost < 
  ( SELECT EQThreshold FROM (EXECUTE PROCEDURE sp_mgGetActivityInfo()) a )
ORDER BY AverageCost DESC

Here is a snapshot of the query results

AppsBelowEQThreshold

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

Monday, June 14, 2010

ADS 10 Tip #11 – Windows Side-By-Side Installs

Advantage 10 can be installed alongside a previous version of Advantage. You can also install multiple instances of Advantage 10 on the same machine. For example; when multiple Advantage-enabled applications are using the same physical server, but are shipped using different versions of Advantage. It is also very useful for testing and support since the same physical server can be used.

In order to install another instance of Advantage on the same machine you must make a few modifications to the setup files. First you modify the Advantage Database Server for Windows x86_64 v10.0.msi file using setup.exe. You do this by running setup.exe with the sidebyside option as shown below.

setup.exe sidebyside  <your custom install name> [UpgradeCode] [ProductCode]

You will get a screen displaying a new upgrade code and product code. The upgrade code is used to identify the product for upgrade purposes and should be reused whenever you apply updates to Advantage. The product code is unique to every version of Advantage that is installed. When creating your first install have the setup program generate both codes. When an update is released create the side-by-side install again using the upgrade code you received the first time you created a side-by-side install.

SideBySide_new

After you have run setup.exe to modify the MSI file you need to make a few changes to the setup.ini file. First you must add SideBySide=1 to the [Bootstrap] section.

You must change the INSTALLDIR property to avoid overwriting an existing Advantage installation. If this property is not set then the default directory will be used. The location for program file shortcuts is specified with the STARTMENUNAME property.

Since the current instance of Advantage is using the default log file path you need to specify a different path for the new instance. These paths are configured with the CONFIG_ERROR_ASSERT_LOGS and CONFIG_TPS_LOGS. The specified path must exist prior to running the setup, the specified folder will not be created.

The additional instance must run on a different port than any other instance of Advantage. This port is configured with the CONFIG_RECEIVE_IP_PORT property. When setting this property you must precede the value with a hash, or pound sign. The log paths and IP port can all be changed using the Advantage Configuration Utility after the installation.

Finally you must specify a unique service name for the instance with the SERVICE_NAME property. This name will also be used for creating an additional registry key for storing configuration settings. The SERVICE_DISPALAY_NAME and optional SERVICE_DESCRIPTION will be displayed in the Service Control Manager.

For more information refer to Installing Multiple Instances on a Single Server in Windows in the help file.

Friday, June 11, 2010

ADS 10 Tip #10 – Binary Indexes

A binary index is a very compact index which contains a bitmask which can be used to optimize some operations. There are significant performance gains when creating a binary index on deleted records, using the DELETED() expression. This index can be used by the Advantage Optimized Filter (AOF) engine to improve SQL performance when there are many deleted records in a table.

DeletedIndex You create a binary index by specifying the ADS_BINARY_INDEX (4096) option when using sp_CreateIndex or AdsCreateIndex90. You can also create a binary index using the Table Designer in Advantage Data Architect. Clicking on the Add DELETED() Index on the Additional Index Definitions tab will add the index to the table. DELETED() indexes can be created on ADTs and DBF table types.

Binary indexes can also be created on any logical field when using DBF files. This can improve SQL operations when the value of a logical field is used in the WHERE clause. Binary indexes are most efficient when the balance between true and false values is nearly equal. If the values are unbalanced a traditional index may be more efficient.

Binary indexes have a few limitations.

  • The result of the index expression must be logical
  • The result of the index expression cannot be NULL
  • The index cannot be conditional (it cannot have a FOR clause)
  • The index cannot be descending or unique

Because binary indexes cannot be built upon an expression that will evaluate to NULL you cannot create a binary index on a logical field in an ADT file.

Thursday, June 10, 2010

ADS 10 Tip #9 – Notifications with Data

Advantage added Notifications(Events) in version 9, you can read an overview here. Notifications have been enhanced in version 10 by adding the ability to pass back a string with a notification. This can save a round trip to the server since all the information the client needs can be provided in the notification.

As a quick review using notifications with Advantage is a three step process. First the client must register for the event(s) it wants. Second the client listens for the event and finally the server must signal the event. This is all done with the use of system procedures as outlined below.

/* Client-side setup */
// Create an event with data
EXECUTE PROCEDURE sp_CreateEvent( 'AdminMsg', 2 );

// Wait for the event this is a blocking call
EXECUTE PROCEDURE sp_WaitForEvent( 'AdminMsg', -1, 0, 2 ); 

/* Server-side setup */
// Signal an event this must be done in a trigger or stored procedure
EXECUTE PROCEDURE sp_SignalEvent( 'AdminMsg', true, 2, 'test message' );

In the example the client "registers" for an event called AdminMsg using the ADS_WITH_DATA option. Next the client calls sp_WaitForEvent which sets up an efficient wait with the Advantage server. In this case it will wait until the query is cancelled or a notification is received. Since this is a blocking call we recommend using a second thread.

Signaling is the responsibility of the server and is usually done in response to an action performed on the server. For example a table being updated. The sp_SignalEvent system procedure can only be called within a trigger or stored procedure. A client cannot call this system procedure directly. The data returned when the event is signaled can be any string value. The return type is a memo field which allows for large amounts of data to be passed back with the notification.

There is a screencast available that demonstrates notifications with version 10.

Wednesday, June 9, 2010

ADS 10 Tip #8 – Table Data Caching

The Advantage cache system introduced in version 8.1 and has continued to improve. In version 10 all temporary tables are created in the cache instead of on disk. This improves performance by reducing the amount of time it takes to create the temporary tables as well as improving read and write speed. Temporary tables may still be written to disk when there is not enough space available within the cache.
Version 10 also added the ability to specify tables to be loaded into the cache system. This is primarily intended for relatively small tables that are accessed frequently. By loading these physical tables into memory reads and writes to the table can be much faster. However, changes made to tables stored in cache may not be persisted to disk if the server is improperly shutdown. If the server was improperly shutdown you may receive a 7155 "Lost Cached Updates" error the first time you open the table after the improper shutdown.  For more information refer to Table Data Caching in the help file.
TableDataCachingTables can be set to cache either reads or writes but not both. You can set a table for caching of reads only or for both reads and writes. You can specify the caching option when creating a table or when opening a table. Two new options ADS_CACHE_READS and ADS_CACHE_WRITES   have been added which can be specified when using AdsCreateTable90 or AdsOpenTable90. Additionally the TAdsTable.AdsTableOptions includes an AdsCachingOption allowing you to specify read or write caching.
Dictionary bound tables include a Table Caching property which you can modify in the Table Designer on the Table Properties tab in Advantage Data Architect. This property can also be modified using the sp_ModifyTableProperty or AdsDDSetTableProperty API. You can get the current setting using AdsDDGetTableProperty. An example of using the sp_ModifyTableProperty system procedure is below.

// Turn off table caching
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_CACHING', '0' , 'NO_VALIDATE', NULL );

// Set the Table to always cache reads
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_CACHING', '1' , 'NO_VALIDATE', NULL );

// Set the Table to always cache writes
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_CACHING', '2' , 'NO_VALIDATE', NULL );

Tuesday, June 8, 2010

ADS 10 Tip #7 – Excluding Tables from a Transaction

Along with nested transactions version 10 also allows tables to be excluded from a transaction. This can be very useful when all users need to see changes to a table immediately. Advantage uses read-committed isolation while within a transaction. This means that only the user within the transaction can see the changes. Additionally all records that are modified within a transaction are locked until the transaction is committed or rolled back.

Changes to Transaction-Free tables within a transaction are immediately visible and will not be restored to their original values if the transaction is rolled back. This also means that records within a transaction-free table are not locked until the transaction is committed or rolled back either. This can be very useful if you are using a table to store key values. If the table was not transaction-free a bottleneck could occur if multiple users needed to update the key table.

TxnFreeTableYou can set tables to be transaction free when creating a table. This setting can be modified on an existing table using the  AdsSetTableTransactionFree API. There are also two system procedures  sp_IgnoreTableTransactions (for free tables or dictionary tables) and sp_ModifyTableProperty (for dictionary bound tables) that can modify this property. This property can also be set in Advantage Data Architect with the Table Designer on the Table Properties tab, pictured right. All of these functions require exclusive access to the tables and the setting is persisted.

A new column TABLE_TRANS_FREE has been added to system.tables which indicates if the table is transaction free or not. An example of the various SQL statements pertaining to transaction free tables is below.

// Set the table to be excluded from a transaction
EXECUTE PROCEDURE sp_ModifyTableProperty
  ( 'tblname', 'TABLE_TRANS_FREE', 'true' , 'NO_VALIDATE', NULL );

// Ignore transactions on a table requires exclusive access
EXECUTE PROCEDURE sp_IgnoreTableTransactions( 'tblname', true );

// Get a list of tables that are transaction free in the database
SELECT * FROM system.tables WHERE Table_Trans_Free = true;

You can view a screencast on the new Transaction Processing features on the DevZone.

Monday, June 7, 2010

ADS 10 Tip #6 – Nested Transactions

Nesting transactions is the ability to start a transaction within a transaction. This allows for simpler encapsulation since you can start a transaction within your function or procedure even if the function or procedure was called within an active transaction.

Starting with version 9 you could create transaction save points which provided similar functionality. However, this required you to check to see if you were in a transaction prior to starting a transaction. Version 10 supports nested transactions allowing you to begin a transaction within an active transaction. Each new transaction will increment the transaction nesting level. The entire transaction will be committed when the nesting level reaches zero.

Calling rollback within any nesting level will rollback the entire transaction. If you need to provide partial rollback functionality you will need to use savepoints. Below is an SQL script example.

DECLARE cOrder CURSOR AS EXECUTE PROCEDURE CreateInvoice( 141, 10010 );
DECLARE @OrdID CHAR(36);
DECLARE @Price MONEY;

BEGIN TRANSACTION;  // Nesting level = 1

// Opening the cursor creates the order
OPEN cOrder;
FETCH cOrder;
@OrdID = cOrder.OrderID;

  // Add some items to the order
  BEGIN TRANSACTION; // Nesting level = 2
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP080-50', 25 );
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP060-43', 15 );
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP075-30', 20 );
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP040-15', 30 );
  COMMIT WORK; // Nesting level = 1

// The ProcessOrder stored procedure uses a transaction
EXECUTE PROCEDURE ProcessOrder( @OrdID );

COMMIT WORK; // Nesting level = 0

You can view a screencast on the new Transaction Processing features on the DevZone.

Friday, June 4, 2010

ADS 10 Tip #5 – System Variables

Several new system variables have been added to the SQL engine allowing developers to get more metadata within SQL scripts. System variables can be divided into four categories: Server Level, Connection Level, Statement Level and Error handling.

Many system variables such as __errorclass, ::conn.name and ::stmt.trigrecno were available in version 9. With version 10 several new system variables have been added.

Server Variables  
::server.OldestActiveTxn Oldest active transaction as a timestamp. Returns NULL if no transactions are active on the server.

Connection Variables  
::conn.TransactionCount Nesting level of the current transaction 0 if there is no active transaction on the connection.
::conn.OperationCount The number of operations performed by the server for the current connection

Statement Variables  
::stmt.TrigRowID Returns the ROWID of the record that fired the trigger
::stmt.TrigName Name of the trigger being fired
::stmt.TrigTableName Name of the table that fired the trigger
::stmt.TrigEventType Type of event that caused the trigger to be fired: INSERT, UPDATE or DELETE
::stmt.TrigType Type of trigger: BEFORE, INSTEAD OF, AFTER or CONFLICT

All of these system variables can be accessed within an SQL statement. See the examples below.

// If we are in a nested transaction rollback to a savepoint
IF ::conn.TransactionCount > 1 THEN
  ROLLBACK TO SAVEPOINT MySavepoint;
ELSE
  ROLLBACK;
END IF;

/* A generic INSERT that writes to an audit table
 * System Variables allow the same script to be used 
 * as a trigger for any table */
INSERT INTO AuditTbl ( TableName, Operation, UserID, DateTime )
  VALUES ( ::stmt.TrigTableName, ::stmt.TrigEventType, USER(), NOW() )

Thursday, June 3, 2010

ADS 10 Tip #4 – Unicode Support

Unicode support has been added to Advantage. This includes three new field types nChar, nVarChar and nMemo which can be used to store Unicode characters. Unicode characters are stored in UTF-16 encoding which uses two bytes per character. Over 200 Unicode collations are available allowing creation of indexes on the Unicode field types.

Unicode1

The sp_GetCollations system procedure returns a list of available collations. You can also use Unicode string literals in your SQL statements. This allows you to filter the results as well as insert Unicode data into the field. Several example SQL statements are below.

// Get a list of Collations
EXECUTE PROCEDURE sp_GetCollations( NULL );

// Get a list of Arabic collations 
EXECUTE PROCEDURE sp_GetCollations( 'ar%' );

// Support for Unicode string literals 
SELECT * FROM PhraseBook WHERE JP = 'どうぞ';

// Insert Unicode values using SQL 
INSERT INTO PhraseBook (ENG, JP, Phonetic)
  VALUES ('Happy Birthday', 'お誕生日おめでとう', 'o tanjoubi omedeto'); 

// Create an index using a Unicode collation
EXECUTE PROCEDURE sp_CreateIndex90( 'StringTbl', 'StringTbl.adi', 'Arabic', 
				    'ARA', '', 0, 0, ':ar_IQ');

When using Unicode field types you must distribute some additional files to your clients. The Unicode functions are stored in aciu32.dll (aciu64.dll for x64) and Unicode collations are stored in icudt40l.dat. Any application that attempts to open a table containing Unicode field types must have these files available.

There is a Unicode Demonstration available on the DevZone.

Wednesday, June 2, 2010

ADS 10 Tip #3 – Worker Threads

Worker threads are used by Advantage to process all database requests, the number of worker threads indicates how many simultaneous database operations can be performed. The default setting prior to version 10 was eight. The default for version 10 is eight per CPU/Core, so if you have a dual core CPU Advantage you will have sixteen threads configured.

This calculation is done when Advantage starts and when the configuration parameter for worker threads is set to zero. You can still specify a specific number of worker threads, we recommend between eight and sixteen threads per CPU/Core.

When all of the configured worker threads are busy new requests are placed in a queue for processing. In previous versions of Advantage these threads were processed in the order they were received. In version 10 the queue has been modified to allow for prioritization of the requests. Advantage automatically calculates the average cost of each connection. When the request queue is full a threshold is calculated and connections with costs below the threshold are placed in the express queue. These requests will be processed with the next available worker thread.

You can get information about the express queue using the sp_MgGetConnectedUsers and sp_MgGetActivityInfo which both have new fields that report express queue activity. The AverageCost is displayed for each user when you call sp_MgGetConnectedUsers. The express queue threshold, express queue active threads and express queue operations are all displayed when calling sp_MgGetActivityInfo. Screenshots of the results of these procedures when running multiple clients are shown below. Only the pertinent fields are displayed.

sp_MgGetConnectedUsers

ExpressQueue1

sp_MgGetActivityInfo

ExpressQueue2

Tuesday, June 1, 2010

ADS 10 Tip #2 – Rights Checking Behavior

Advantage 10 changes the default behavior for rights checking. The default in version 9 and earlier is to check rights when making connections and opening tables. When using a version 10 client, rights are always ignored regardless of the rights checking setting.

CheckRightsThis leads to better performance since it avoids having the client check if it has rights to a file prior to opening it. Since the Advantage Service is opening the file for the client this check is unnecessary.

The diagram illustrates the steps taken when rights checking is enabled. Step one is the request to the network/domain controller checking for rights to the table. Step two is the response from the server. Step three is the open request to Advantage. Steps one and two are not performed when rights are ignored which can save time especially on multiple file opens.

If you rely on the network to provide security to your tables you can set the Advantage client to respect the check rights setting. This is done with the AdsSetRightsChecking API. Make a call to this API using the ADS_RESPECT_RIGHTS_CHECKING (2) option in your application startup code. This setting is global and will be respected until the ace.dll is unloaded.