· Apr 18

Does Embedded SQL support Table Hints?

Hi everyone.

I have a function that may end up being called from a number of transformations at the same time, and within the function there's some Embedded SQL to first check if a local table has an entry, and then adds the entry if it doesn't exist.

To prevent a race condition where the function is called by two transformations and they both end up attempting to insert the same value, I'm looking to use the table hint "WITH TABLOCK" on the insert, but this seems to be failing the syntax checks within vscode.

Are table hints supported with embedded sql?

If not, is there a way to prevent the scenario from unfolding? The code I'm working with for the function is:

Class Example.Functions Extends Ens.Rule.FunctionSet

ClassMethod VisitIDShortener(inVisitID As %String, SourceSystem As %String) As %String
    //Firstly,  check to see if the table we're working with exists | This should only be an issue on first deployment to a new environment
    Set tSC = $SYSTEM.SQL.Schema.TableExists("Example.VisitIDLookup")
    If tSC'=1{
        //Buid table if it doesn't exist
            CREATE TABLE Example.VisitIDLookup
            (SourceSystem varchar(10), SourceVisitID varchar(250), TargetVisitID varchar(15))
    //Secondly, check to see if the Global we're using for our increment exist, and create if it doesn't exist
    Set GBLCHK = $DATA(^$GLOBAL("^EMPVisitID"))
	If GBLCHK = 0{
		Set ^EMPVisitID = 0

    //Check table for entry
        Select TargetVisitID into :pTargetID
        FROM Example.VisitIDLookup
        Where SourceVisitID = :inVisitID And SourceSystem = :SourceSystem
    //If SQLCODE is 0, we have an entry and we wnat to simply return the result
    If SQLCODE=0 {
        Set outVisitID = pTargetID
        //Increment the Global
        Do $INCREMENT(^EMPVisitID)
        //Use incremented value for output (with a prefix)
        Set outVisitID = "TIEGEN"_^EMPVisitID
        //Add new entry to table
            Insert INTO Example.VisitIDLookup
            Set SourceSystem=:SourceSystem, SourceVisitID = :inVisitID, TargetVisitID = :outVisitID

    Quit outVisitID

Discussion (4)2
Log in or sign up to continue

I'm not sure about TABLOCK specifically, but there are a couple other features that might help you solve this:

First, there is the LOCK TABLE statement:

There's also the objectscript LOCK command:

You could use either of these to block the method while other instances of it are running and prevent the race condition. I'm not sure how efficient this method would be compared to TABLOCK, but it would fix the bug.

I hope this helps!