How can I access a SQL Server 2012 database sequence value in arcpy

3356
7
07-12-2016 03:46 PM
JamesFox1
Occasional Contributor

I had python script tool that accessed a sequence value and got the

value via arcpy.ArcSDESQLExecute call. We are porting our SDE environment

from Oracle 11g to SQL Server 2012 and as such changed the sequence select

from ALL_SEQUENCES to sys.sequences as shown:

        select_list = 'SELECT DISTINCT current_value ' #  SELECT LAST_NUMBER '

        from_clause = 'FROM sys.sequences ' # ' FROM ALL_SEQUENCES '

        where_clause = 'WHERE NAME = \''+seq_name+'\' ' # 'WHERE SEQUENCE_OWNER = \'' + schema + '\' AND SEQUENCE_NAME

This SQL works in SSMS or SQL_CMD but not in arcpy (it did work correctly in Oracle 11g)

is there now an arcpy call that will get a sequence value (CURRVAL) directly without

building SQL and using ArcSDESQLExecute?

-Jim

0 Kudos
7 Replies
JoshuaBixby
MVP Esteemed Contributor

Are you receiving any kind of error from arcpy.ArcSDESQLExecute ?  Looking in SQL Server just now, I notice that current_value is type sql_variant.  Have you tried casting the value to see if this is a data type mismatch or unsupported data type?

0 Kudos
JamesFox1
Occasional Contributor

I'm already casting the value:       

try:

           

            sde_return = connection.execute(sql_txt)

   

        ...

        # If the return value is a list (a list of lists), display

        # each list as a row from the table being queried.

        seq_val = int(sde_return)

Should it be done earlier?

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Yes.  Trying casting within the SQL itself so that a compatible SQL data type is being returned to arcpy.ArcSDESQLExecute by the DBMS.

0 Kudos
JamesFox1
Occasional Contributor

wrapping in T-SQL function?

0 Kudos
JamesFox1
Occasional Contributor

Hi Josh,

I tried casting in the sql as integer, int, bigint, smallint and tinyint - no luck. The code is pulling and using as python integer type.Capture.JPGCapture1.JPG

-Jim

0 Kudos
JamesFox1
Occasional Contributor

Convert doesn't seem to work either.

-JimCapture2.JPG

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

After doing a bit of testing and looking at your code, I am wondering whether your SQL is generating an error, which is then having sde_return get set to False, and False is getting cast to 0.  What if you call arcpy.ArcSDESQLExecute outside of the try statement, what do you get?

For me, I have to cast current_value in SQL in order to avoid generating an error:

>>> sde_file = # path to SDE connection file
>>> sde_conn = arcpy.ArcSDESQLExecute(sde_file)
>>> # Run SQL without casting
>>> sql_nocast = "SELECT current_value FROM sys.sequences WHERE name = 'CountBy1'"
>>> sde_conn.execute(sql_nocast)
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "c:\program files (x86)\arcgis\desktop10.4\arcpy\arcpy\arcobjects\arcobjects.py", line 27, in execute
    return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args)))
AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65 \ue45c
>>> 
>>> # Run SQL with casting
>>> sql_cast = "SELECT CAST(current_value AS INT) FROM sys.sequences WHERE name = 'CountBy1'"
>>> sde_conn.execute(sql_cast)
1
>>>