Retrieve next value from SQL Server sequence in Pro SDK

Question

I am looking for a way to retrieve next value from a SQL Server sequence using Pro SDK. In Oracle a QueryDef can be used (see below), but in SQL Server the "dual" table does not exist. I do not think I can execute "select next value for " using QueryDef without table name. DatabaseClient.ExecuteStatement does not return any data. Any suggestions? Thanks! private Task GetNextNumberFromSequence(ArcGIS.Core.Data.Geodatabase geodatabase, string seqName) { int numFromSequence = 0; return QueuedTask.Run(() => { QueryDef seqQueryDef = new QueryDef { SubFields = seqName + ".NEXTVAL", Tables = "SYS.DUAL" }; try { using (RowCursor rowCursor = geodatabase.Evaluate(seqQueryDef, false)) { rowCursor.MoveNext(); using (Row row = rowCursor.Current) numFromSequence = Convert.ToInt32(row[0]); } return numFromSequence.ToString(); } catch (Exception) { return null; } }); }

azlotin · Answer

Thanks Rich! My concern is that if the global temporary table ##temptbl is used, two users may end up getting the same sequence value from the table if they execute the code at the same time (though this may be hard to prove).

I have solved the issue by including a Python script in my addin and calling it to generate values from the sequence.

import arcpy

connFile = sys.argv[1]
sequenceName = str(sys.argv[2])
numberOfValues = int(sys.argv[3])

sqlConn = arcpy.ArcSDESQLExecute(connFile)
sql = f"SELECT NEXT VALUE FOR {sequenceName}"
valueList = [sqlConn.execute(sql) for i in range(numberOfValues)]

print(",".join(valueList))

RichRuh · Answer

Hi,

If you are taking this sequence value and writing it into a database field, you might think about using attribute rules. Attribute Rules are short scripts that run during editing can be used to fill in a value.  They are written using Arcade, which provides a built-in cross-platform sequence function.

That said, if this doesn't work or you want to continue with your current technique here's how.

First, use DatabaseClient.Execute statement to generate the next sequence value and write it into a temporary table.

SELECT NEXT VALUE FOR sequencename as VALUE INTO ##temptbl;

Second, use Geodatabase.Evaluate to read the value from the temporary table and return it back to your application

SELECT value from ##temptbl

Please let me know if this works.

Thanks,

--Rich