topic Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle? in Python Questions

ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

GeoffreyWest — Fri, 20 Jan 2017 22:55:53 GMT

I have a Python script that has thrown this error. The script has remained functional and working properly until I have changed the paths of the data that is being read in it. Initially the application read 3 different tables which were all in a SQL Server DB. I made a very small subset of each table(3 records) and exported these tables to an Oracle DB, when I am reading the data from the Oracle environment this error is thrown. Here is where I make my ArcSDESQLExecute connection. What is the solution for this?

ticket_sql = "select {0} from {1} where {2} >= '{3}' and CONVERT(time, {4}) > '{5}' and jobid NOT LIKE '%ZZ%' ".format(self.ticket_id_field, self.ticket_audit_table_name, self.modified_date_field, last_date, self.modified_time_field, last_time)
self.logger.debug(ticket_sql)

conn = arcpy.ArcSDESQLExecute(self.korterra_sde_path)
results = conn.execute(ticket_sql)

Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

ChristianWells — Fri, 20 Jan 2017 23:08:00 GMT

If you print ticket_sql with the formatted text, does that SQL run in Oracle? I've noticed this error happening on occasion when there is an error in the text string.

Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

GeoffreyWest — Sat, 21 Jan 2017 00:01:44 GMT

Hi Christian,

When I print ticket_sql here is what is returned. Is the CONVERT function incorrect for Oracle? When I run this statement with the SQL Server table, it is correct.

select jobid from SEU_GIS_GAS.actionaudit where actiondtdate >= '2017-01-17' and CONVERT(time,actiondttime) > '14:28:55' and jobid NOT LIKE '%ZZ%'

Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

ChristianWells — Sat, 21 Jan 2017 00:07:47 GMT

Hi Geoffrey, what is your goal of the convert? Your inputs look like your inputting two field names, based on the SQL above.

Oracle uses convert character strings from one character set to another.

CONVERT

Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

GeoffreyWest — Sat, 21 Jan 2017 00:13:20 GMT

CONVERT in the above statements converts from a string to TIME field from the SQL table.

Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

ChristianWells — Sat, 21 Jan 2017 00:22:59 GMT

So if we are sending this to Oracle and we need to change the string to a time field, then CONVERT cannot be used.

Instead for Oracle, we must use CAST or a TO_TIMESTAMP function.

Can you clarify what "time" and "actiondttime" datatypes are in Oracle? In addition, which field are you attempting to convert, time or actiondttime?

Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

GeoffreyWest — Sat, 21 Jan 2017 00:33:17 GMT

"actiondttime" is a string in oracle, the value time above was used in the SQL expression to note to convert a SQL table field from string to time.

Re: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

JoshuaBixby — Sat, 21 Jan 2017 14:58:59 GMT

Just for reference, in case you hadn't looked up the codes already: Return codes.

SE_ATTR_NOEXIST (-38)
Returned from functions that attempt to access a column that does not exist. Sometimes returned after misspelling the name of the column.