User Tools

Site Tools


server_side_auto_incrementing_in_clarion_6_3_sql_file_drivers.htm
Navigation:  SQL Drivers >====== Server Side Auto incrementing ====== Previous pageReturn to chapter overviewNext 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 (/AUTOINC and /PREAUTOINC).

See also (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 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 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

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

AUTOINCUSESSCOPEIDENTITY

server_side_auto_incrementing_in_clarion_6_3_sql_file_drivers.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1