topic Re: How can I access a SQL Server 2012 database sequence value in arcpy in Python Questions

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

JamesFox1 — Tue, 12 Jul 2016 22:46:28 GMT

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

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

JoshuaBixby — Wed, 13 Jul 2016 01:11:51 GMT

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?

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

JamesFox1 — Wed, 13 Jul 2016 16:00:58 GMT

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?

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

JoshuaBixby — Wed, 13 Jul 2016 16:41:05 GMT

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

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

JamesFox1 — Wed, 13 Jul 2016 19:22:21 GMT

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.

-Jim

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

JamesFox1 — Wed, 13 Jul 2016 20:56:05 GMT

Convert doesn't seem to work either.

-Jim

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

JamesFox1 — Thu, 14 Jul 2016 14:42:31 GMT

wrapping in T-SQL function?

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

JoshuaBixby — Sat, 11 Dec 2021 18:07:51 GMT

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
>>>