Autoinc in MSSQL Tables

questions
,
1

Hello,

i try to use serverside AUTOINC with MS SQL( EXPRESS) DB in C11.1, but with no success.

The Documentation about this topic seems to be not up to date. Or its me, who is not up to date…

The setting is a ABC Template generated Application from Dictionary.

Setting the Autoinc on the Primary Key in the Dictionary, i get the following Compiler-Error:
“An Identity Column can not belong to an Auto Number Key (Key:USER:IDKey - Column:USER:ID)
Set the auto Number option to false.”

But setting this to false, none of the suggested settings take affect on autoinc.

Should i keep it on client side?
Any experiences on this issue?

Thanks for any hints

best regards

Ralph

2

Autoins server side on mssql work for me this way:
The column that will be the id (LONG) shall have the attribute IsIdentity
image
This driver option shall be used
image
The primary key shall be without autonumber
image
When table is create check in sql if IDENTITY is set for the id column. If not set it manually. People usually use FM3 for sql table creation. I think if using FM3 you should add Autonumber attribute to the Id column, but not sure.
hth
Nenad

1 Like
3

ABProgram.tpw

#IF((EXTRACT(UPPER(%FieldUserOptions), UPPER(‘IsIdentity’),1) = ‘1’ OR UPPER(EXTRACT(UPPER(%FieldUserOptions), UPPER(‘IsIdentity’),1)) = ‘TRUE’) OR EXTRACT(UPPER(%FieldUserOptions), UPPER(‘ServerAutoIncColumn’),1) <> ‘’)
! EmulateAutoNumKey, this will force the FM to auto insert the record and
! recover the Identity column
IF UseType = UseType:Initialize AND ~SELF.Initialized
SELF.HasAutoInc = True
END
#BREAK
#ENDIF

#IF((EXTRACT(UPPER(%FieldUserOptions), UPPER(‘IsIdentity’),1) = ‘1’ OR UPPER(EXTRACT(UPPER(%FieldUserOptions), UPPER(‘IsIdentity’),1)) = ‘TRUE’) AND %FieldType<>‘GROUP’)
#IF(%FileIdentityFieldInstance=0)
#SET(%FileIdentityField,%Field)
#SET(%FileIdentityFieldInstance,%lFileCount)
#SET(%FileIdentityFieldColumn,1)
#ELSE
#ERROR(‘Only one Identity column per File is allowed - Second instance (File:’&%File&’ - Column:’&%Field&’)’)
#ENDIF
#ENDIF

Field User Option
IsIdentity = TRUE
is the same as
FileLabel{PROP:ServerAutoInc, FieldNumber} = 1
FileLabel{PROP:ServerAutoInc, FieldNumber} = True

You can only have one field which is autoincremented by the MS SQL table using this method.

Just dont forget to make the field in the MS SQL table, auto incrementing!

Now as a habit, with MS SQL I would create an auto incrementing field for each table. This becomes my entry order index because I never delete a record from MS SQL, I set other fields to indicate its been deleted and then its not shown in the app. Not GDPR compliant but…

I would then use Clarion to do the auto incrementing for other fields because something Clarion can do out the box which MS SQL cant do unless you write a stored procedure, is have a two or more field element key with the auto incrementing taking place on the last field. The first fields would be related to another file/table or prefilled with a value.

To do this, I edit
abFile.inc
HasAutoInc BYTE,PROTECTED ! Auto-increment or not flag
changed to
HasAutoInc BYTE !,PROTECTED ! Auto-increment or not flag

I then have a template which sets HasAutoInc to zero, so that autoinc fields are only incremented at the time the record is saved. They are not primed before then.

To do this in code just add the following to the embed Brwx.Ask Priority 2501

IF Request = InsertRecord
Access:%AutoIncFile.HasAutoInc = 0      !generated by IS_HasAutoIncBrowse procedure extension template
END
4

Hi nehad,

Yes, You are right:

I changed the id Column to “Identity” manually in the SSMS and that works.

Looks like a bug in Clarion, that should create the table with its primary field set to Identity by default.

Thanks Ralph

5

Hi RichClaCode,

Thank You for pointing to this section of the template and this approach;

I am trying to keep the things simple, but even with manually written code to set USER{PROP:ServerAutoInc, 1} = 1 in the Insert Section of a Update-Procedure it did not work because the Identity Property of the column (id field) had not been set on table creation.

Setting this manually in the MS SQL Management Studio worked fine, as nehad mentioned above.

regards
Ralph

6

Did this bit not come out at your end then?

Edit.

The reason I ask is I know I get fake content from the DailyMail website and news.ycombinator.com so I wouldnt be surprised if I’m getting fake content from this website as well!
I know this computer I’m working on is hacked but I dont have the resources to secure it, because I’ve been done over by criminals.

Edit 2

Maybe its gaslighting. I’ve had plenty of that since I was born, even the neighbours engage it. Of course any I say will land me straight back in the mental hospital, so I cant even say that I’ve been shot at whilst walking the dog on Childerhouse’s Farm!

7

Definitely not a bug. I could see that possibly defining a primary key field in Clarion as “IsIdentity” might be useful, but should not be the default.

Reasons:

  1. You can have multiple columns in a primary key
  2. The column you define as the primary key in Clarion is not necessarily the autoincremented column in the database: in many cases Clarion doesn’t even need to know anything about the actual auto-incremented column. For example, a transaction has a unique ID created by the database, but Clarion uses customer and a datetime as its primary key
  3. One of the advantages for MSSQL is that an Identity column is treated as read-only. MSSQL doesn’t like you trying to write to an Identity column. That’s not the case in other databases.
  4. You may have gathered from #2 that I don’t always expect Clarion to know about all columns in database tables. You can probably guess from that that I do not rely on Clarion to create tables.
  5. Although the use of surrogate, auto-numbered keys is incredibly common, some entities really do have natural keys, and you want to treat the columns in those keys normally, not marked as IsIdentity.
1 Like
8

Youre right, there are some scenarios where MSSQL Identity column may not be handled as a primary field with auto inc
It depends on the setup.
The scenarios i use with Clarion is creating tables as defined in dct.
So lets call this behavior a feature leading to more flexibilty with using less of Clarion :wink: