| Navigation: How To's and Troubleshooting > How to… >====== How to Execute Auto Incrementing on the Server using SQL ====== |    |
Clarion generated code by default uses generic methods to access all supported drivers and databases, but for SQL server access, Clarion allows you to specify a particular query to optimize access and Auto Incrementing for a particular column.
The generic code for executing the auto incrementing in MyField looks like this:
CLEAR(ByMyFieldKey)
SET(ByMyFieldKey,ByMyFieldKey)
PREVIOUS(MyFile)
MyField = Myfield + 1
The code shown above is similar for both Clarion and ABC Templates, and will work reliably. However, with in some SQL systems this code will not generate an optimized query, resulting in slow response. Performance is dependant on the SQL platform and the database configuration.
Depending on the particular SQL engine, the translate code will look simiar to the following:
SELECT (FIELDS) FROM FILE ORDER BY KEYFIELD DESC
This usage above requires the database to have an optimization on the key in place when a descending order is requested, and that is not the case for every database.
What we would will like to be executed is something that is specific for the database that we are using. For example, depending on the back end:
SELECT FIRST FROM FILE
or:
SELECT LAST_INSERT_ID()
or:
SELECT Myseq.nextVal
or:
SELECT MAX(Col1),MAX(Col2)'.FROM ACCOUNTS
Beginning with Clarion 6.3, the SQL drivers and templates now support the use of native SQL to be specified to execute the auto incrementing of a field directly in the database engine when clarion executes the ADD or APPEND command that translates to an SQL insert.
To support this, three new things were added to all SQL drivers.
Two new Driver String Options, \AUTOINC and \PREAUTOINC, and the PROP:ServerAutoInc file property
The \AUTOINC Driver String option allows you to specify the query that the database should execute to get the new value for our auto incrementing column.
The \PREAUTOINC Driver String option is used to set if the query should be executed before or after the insert.
The PROP:ServerAutoInc file property is used to specify which column of the table will receive the auto incremented query value.
Those options and properties are used at the driver level. To allow the application templates to use them, two user options were also added.
IsIdentity and ServerAutoIncColumn are used at the FILE user option level in the Dictionary.
For more information regarding any of these options, refer to the Server Side Auto incrementing for Clarion SQL file drivers help topic.
See Also: