0

As you see in my code I iterate and execute all the sql statements in an sql file using ArcSDESQLExecute object.

It does work but when all statements have been executed the object returns 'None' (and thus the elif sde_return == False: part) indicating a failure and I don't know why.

What am I doing wrong?

I've used the first example in Executing SQL using an ArcSDE connection to build my code.

My code:

import arcpy

try:

  sqlFileSt = arcpy.GetParameterAsText(0)
  destSDE = arcpy.GetParameterAsText(1)

  arcpy.env.workspace = destSDE
  sde_conn = arcpy.ArcSDESQLExecute(destSDE)

  fd = open(sqlFileSt, 'r')
  sqlFile = fd.read()
  fd.close()
  sqlFile = "".join([s for s in sqlFile.strip().splitlines(True) if s.strip("\r\n").strip()])
  sql_llist = sqlFile.split(";")
  for sql in sql_llist:
    try:
      sde_return = sde_conn.execute(sql)
    except Exception as err:
      arcpy.AddMessage(err)
      sde_return = False

    if sde_return == True:
      arcpy.AddMessage("SQL: {0} executed.".format(sql))
    elif sde_return == False:
      arcpy.AddMessage(arcpy.GetMessages(2))
      arcpy.AddMessage("SQL: {0} failed.".format(sql))

except Exception as err:
    print(err)
    arcpy.AddMessage(arcpy.GetMessages(2))

And the contents of in my sql file:

CREATE TABLE "APP_UPDATES"
  (
    "OBJECTID" NUMBER(38,0) NOT NULL ENABLE,
    "DATE_ACT" DATE,
    "NAME_TABLE" NVARCHAR2(50),
    "ELEMENTS_DEL" NUMBER(*,0),
    "ELEMENTS_ADD"   NUMBER(*,0),
    "RESULT" NVARCHAR2(15),
    "ERRORS" NCLOB
    );

CREATE TABLE "APP_CONFIG" 
   (    
    "C_KEY" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "C_VALUE" VARCHAR2(2048 BYTE) NOT NULL ENABLE, 
    "COMMENTS" VARCHAR2(255 BYTE)
    );


CREATE TABLE "APP_QUERIES" 
   (    
    "NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
    "QUERY" VARCHAR2(2048 BYTE) NOT NULL ENABLE, 
    "NOTES" VARCHAR2(255 BYTE)
    );
4
  • Please edit your question to contain both the contents of the file, and the value of 'sql' before it is executed. – Vince Jun 30 '15 at 11:55
  • @Vince Done! Forgot to include the sql statements. Thanks! – DMS02 Jun 30 '15 at 13:55
  • CREATE TABLE statements do not return results, which is a case covered by the sample code you've referenced, but which you've removed from the code to ask this question. – Vince Jun 30 '15 at 15:40
  • @Vince No. I didn't remove the sql statements from the code. They are in an sql file and the reason is that they may be modified with time and it's easier for the user to modify a sql file than the tool script. In fact I'm using 4 different sql files with different statement that remove, create and populate tables and also create views. And you're right. None of them return values but do return 'True'/ 'None' as explained in the documentation: "for statements that do not return rows, it will return an indication of the success or failure of the statement (True for success; None for failure)". – DMS02 Jul 1 '15 at 6:39
0

So it was as simple as checking the list resulting from the split. I hadn't noticed the empty string at the end of the list. That's my 'non existant SQL statement'.

Changed sql_llist = sqlFile.split(";") to sql_llist= sqlFile.rstrip(';').split(';') and voilà!

Everyday we learn something new. This is what I learned today:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.