User bio

Developer on the Language Interoperability Team at InterSystems.

Member since Nov 7, 2015
Replies:

I don't know anything about ALLOWIDENTITYINSERT - maybe someone has added that since I was last involved with the compiler? 

But - as a general rule - you should not allow IDENTITYINSERT all of the time as it introduces a responsibility to keep the ID counter updated. When I first ran my test, it failed not because identity insert didn't work but because my counter wasn't updated to be 101 (I had inserted 100 with ID set).

UPDATE:

I researched this and found that while the documentation implies that you can define this parameter in any persistent class, that isn't the truth. The documentation appears to be wrong. It only works for classes using SQL Storage. It won't work for your case. Unless I misread the source code changes. I did look at %Persistent and that parameter is not defined by that class.

With a system-assigned %ID value (the IDKEY is defined as "system assigned" internally), you cannot override the value as there is no settable property. The way to work around that restriction is to define a property as the IDENTITY. It has similar behavior in that its value is system assigned but you can also override that assignment by specifying the value yourself. You have to set a switch that allows you to do that but it is possible. This type of behavior is similar to using sequences in PostgreSQL where some number of inserts that specify the id are followed by an update of the sequence used for id auto-assignment to ensure uniqueness later. Of course, when using IRIS and you specify the identity value, you need to be certain that a future increment (uses $sequence most likely) won't produce a value that will not be unique. 

This is a simple example of a class using identity.

class com.intersystems.App extends %Library.Persistent {

property id as %Library.BigInt [ identity ];
property name as %String;
classmethod add(name as %String) as %Integer {
    set me = ..%New()
    set me.name = name
    do me.%Save()
    return me.%Id()
}
}

I then implemented a simple unit test:

method TestAddWithId() {
    set obj = ##class(com.intersystems.App).%New()
    set obj.name = "E2"
    set obj.id = 100
    try {
        $$$ThrowOnError(obj.%Save())
        set id = obj.%Id()
        do $$$AssertEquals(100, id, "TestAdd() - add 'E2' with Id = 100")
    } catch exc {
        do $$$AssertTrue(0, "TestAddWithId() - Failed, exception caught: "_exc.AsSQLMessage())
    }
}

And the result of executing that test is:

The solution to this problem is to turn on IDENTITY_INSERT. I updated the test to do that.

method TestAddWithId() {
    set old = $system.SQL.SetIdentityInsert(1)
    try {
        do $system.SQL.Execute("delete from com_intersystems.App")
        set obj = ##class(com.intersystems.App).%New()
        set obj.name = "E2"
        set obj.id = 100
        $$$ThrowOnError(obj.%Save())
        set id = obj.%Id()
        do $$$AssertEquals(100, id, "TestAdd() - add 'E2' with Id = 100")
    } catch exc {
        do $$$AssertTrue(0, "TestAddWithId() - Failed, exception caught: "_exc.AsSQLMessage())
    }
    do $system.SQL.SetIdentityInsert(old)
}

and now the test passes:

Depending on what you really want to do, there are a couple of options. First of all, you can (and should perhaps) define a property as an IDENTITY. Setting that property directly requires certain privileges. 

If you don't really care about this property being used as the IDKEY, you can define a %Counter and define it as a Primary Key. It has been a while since I've used %Counter but I can come up with an example if this interests you.

Open Exchange applications:
Certifications & Credly badges:
Dan has no Certifications & Credly badges yet.
Global Masters badges:
Followers:
Following: