| **Navigation:**  SQL Drivers >====== Server Side Auto incrementing ====== | [[sql driver behavior.htm|{{btn_prev_n.gif|Previous page}}]][[coming future.htm|{{btn_home_n.gif|Return to chapter overview}}]][[checkfornull.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

All Clarion SQL drivers support the ability to automatically retrieve values set by the server when an INSERT is issued or to set values via server calls before an INSERT is issued. Normally, this usage is designed for auto incrementing columns that are not set up as auto number key columns on the client side.

These techniques are designed so that the value in columns that are auto incremented by actions that are performed by the Database Server (as opposed to auto incrementing on the client side) are available to the client  application right after the insert without having to perform a requery of the database.

This feature is achieved by the use of the file property and two driver strings (/[[how to execute auto incrementing on the server using sql.htm|AUTOINC and /PREAUTOINC]]).

See also ([[autoinc.htm|AUTOINC)]]

__**Language Support**__

If you are using the application/dictionary approach to development, you do not need to handcode these properties (they are built-in to the template support via dictionary driver string and field option settings ' see [[server side auto incrementing in clarion 6 3 sql file drivers.htm#application dictionary implementation|below]])

** **

PROP:ServerAutoInc

This property is READ/WRITE. To make the driver automatically update fields after an ADD or APPEND call (or before in some cases) you need to issue the following statement

**//file//****{PROP:ServerAutoInc,n}= m**

before issuing the ADD(//file//) or APPEND(//file//).

**This property is a flag that is reset by ADD and APPEND. So it must be reissued each time an ADD or APPEND is done. If you always want this flag set, you could attach a [[callback  register or unregister a filecallbackinterface.htm|driver callback]] to the file and set the property before the ADD or APPEND statements are executed.**

To specify which field is set on the server, you issue the following statement:

//file//{PROP:ServerAutoInc, //n//} = //m//

where //n// is the field number and //m// is the column number of the returning SQL code.

\\  Thwe m value is almost always 1. However, in the following SELECT statement:

SELECT MAX(Col1),MAX(Col2)'.FROM ACCOUNTS

and your file structure had //Col1// as the first field and //Col2// as the second field then you would issue:, you would issue:

//Accounts//{PROP:ServerAutoInc, 1} = 1

//Accounts//{PROP:ServerAutoInc, 2} = 2

You only need to set the value of {PROP:ServerAutoInc, n} once for non-threaded files and once per thread for threaded files.

If no fields are specified to receive the results for the auto incrementing method and //file//{PROP:ServerAutoInc} has been issued, ADD() will return error code 80 (Function not supported).

You can query **PROP:ServerAutoInc** to see if the next ADD or APPEND will do a server side auto increment or to verify that a field is set to receive the results of the auto increment.

**/AUTOINC AND /PREAUTOINC**

**For MSSQL, SQLAnywhere and Pervasive.SQL drivers, this is all the work you normally need to do. For Oracle and the ODBC driver for other drivers not mentioned above (and in special cases for the other drivers) you will also need to apply the following driver string:**

**/AUTOINC = ****//your SQL SELECT statement//**

with the necessary SQL statement you want executed for retrieving the auto-incrementing fields and

**/PREAUTOINC=[TRUE|FALSE]**

to indicate that the auto-incrementing code should be executed before or after the INSERT statement. For all the drivers (except Oracle), if PREAUTOINC is not specified, then the SQL code is issued after the SELECT. For Oracle, it is issued before the SELECT.

These properties can easily be set through the Dictionary //Driver String// Options.

**Example:**

**!ORACLE Example**

**OracleFile FILE,DRIVER('Oracle', '/AUTOINC=****SELECT Myseq.nextVal****')**

__**            RECORD**__

__**SomeData     STRING**__

**autoIncFld   LONG**

**            END**

**           END**

**  CODE**

**   OPEN(OracleFile)**

**   OracleFile{PROP:ServerAutoInc}**

**   ADD(OracleFile)  !will return error code 80**

**   OracleFile{PROP:ServerAutoInc, 2} = 2**

**   ADD(OracleFile)  !will return error code 80 as no field has been set to receive the result of the SELECT statement**

**   OracleFile{PROP:ServerAutoInc, 2} = 1**

**   OracleFile{PROP:ServerAutoInc}**

**   ADD(OracleFile)**

**   ! This will first put the nextval sequence value into **

**   ! OracleFile.autoIncFld and then do a normal ADD call.**

**!**********************************************************

**!MySQL Example**

**Pet FILE,DRIVER('ODBC','****/AUTOINC=SELECT LAST_INSERT_ID()****'),|**

**              OWNER('menagerie,root'),NAME('pet'),PRE(pet),BINDABLE,THREAD**

**Primary KEY(pet:PetID),PRIMARY**

**Record  RECORD,PRE()**

**Name     CSTRING(21)**

**Owner    CSTRING(21)**

**Species  CSTRING(21)**

**Sex      STRING(1)**

**Birth    DATE**

**Death    DATE**

**PetID    LONG             ****!Server side auto inc**

**        END**

**   END**

**  CODE**

**  OPEN(Pet)**

**  Pet{****PROP:ServerAutoInc****, 7} = 1  ! indicate which field receives which value**

**  Pet:Name = 'test autoinc'**

**  Pet:Owner = 'Pierre'**

**  Pet:Species = 'Dog'**

**  Pet:Sex = 'M'**

**  Pet:Birth = DEFORMAT('2005-12-12', @d10)**

**  Pet{****PROP:ServerAutoInc****}         ! arms automatic updating**

**  ADD(Pet)  ! PetID now has the Auto incremented value**

**  Pet:PetID = 0**

**  Pet:Name = 'test no autoinc'**

**  Pet:Owner = 'Pierre'**

**  Pet:Species = 'Dog'**

**  Pet:Sex = 'M'**

**  Pet:Birth = DEFORMAT('2005-12-12', @d10)**

**  ADD(Pet)  ! PetID will ****//not//**** have the auto incremented value.**

** **

__**Application/Dictionary Implementation**__

There are essentially two steps involved to activate the retrieval of a server side auto-incrementing value.

{{ssautoinc.jpg|SSautoinc.jpg}}

1. In the Dictionary identify the column in the Column Properties //User Options//, with one of two option types:

To identify a column in the dictionary as a server side Identity column, add the following user option:

**IsIdentity = TRUE**

This results in the following statement that will be generated by the template:

**//FileLabel//****{PROP:ServerAutoInc, ****//FieldNumber//****} = 1**

The supported values for **IsIdentity** are TRUE,True or 1 (it is not case sensitive).

Remember, the column you identify must exist on the server as one that possesses the "Identity" characteristic.

If you are returning more than one column in your own SQL code, use the ServerAutoIncColumn user option as follows:

**ServerAutoIncColumn=****//m//**

where //m// is the SQL query column.

This results in the following statement that will be generated by the template:

**//FileLabel//****{PROP:ServerAutoInc, ****//FieldNumber//****} = m**

This associates a column in the dictionary with a Server Side Auto-Incrementing Column. You will also need to add the needed SQL SELECT statement in the /AUTOINC driver string. For example:

**/AUTOINC=SELECT LAST_INSERT_ID()**

An //Identity// or //ServerAutoIncColumn// column cannot be used in a key with the auto number feature active. The template will display an error g message, if you do this.

In addition, server side auto incrementing columns are NOT supported for BLOB, MEMO, GROUP data types, and any data element using the OVER attribute.

__**Template Considerations**__

When using this new feature with Clarion template chain applications, the trigger support needs to be enabled in order to use the //IsIdentity// and //ServerAutoIncColumn// field options. This option is found in the //Global Properties File Options//.

This is necessary because all of the above template support is generated in the FileManager's PreInsert method.

__**Client Side AutoNumber Emulation**__

Once you have set up the support for Server Side auto-incrementing, you may still need to emulate the behavior of a Client side auto-numbering column.

To do this, there is a File level user-option: **EMULATEAUTONUMKEY**

To activate this option, set **EMULATEAUTONUMKEY** to 1 or True, any other value deactivates it (e.g., turns it off).

This option is only valid with any file using the **IsIdentity** Column Option or the **ServerAutoInc** property.

When **EMULATEAUTONUMKEY** is active, the Form and Edit-in-Place templates will work as if the file had a client-side AutoNumber Key. The parent record will be inserted before calling the update Form. This support will allow the user to properly add child records, while maintaining referential integrity.

**See Also:**

[[how to execute auto incrementing on the server using sql.htm|How to Execute Auto Incrementing on the Server using SQL]]

[[autoincusesscopeidentity.htm| AUTOINCUSESSCOPEIDENTITY]]