Python teradatasql: Only an ET or null statement is legal after a DDL Statement error

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Highlighted
Enthusiast

Python teradatasql: Only an ET or null statement is legal after a DDL Statement error

Hi there,

 

I am trying to run a .sql file eg: script.sql that has the following lines:

CREATE MULTISET TABLE DB.TABLENAME
(
  COL1 INTEGER NOT NULL
)
PRIMARY INDEX COL1_PI
(
      COL1
);

COLLECT STATISTICS USING THRESHOLD 10 PERCENT AND THRESHOLD 60 DAYS COLUMN COL1 ON DB.TABLENAME;

I open and read the file into a python string and pass the string to the cursor.execute() function.

So basically I do:

conn = teradatasql.connect('connection details')
cursor = conn.cursor()
sql_str = 'CREATE MULTISET TABLE DB.TABLENAME ' \
'( COL1 INTEGER NOT NULL) ' \
'PRIMARY INDEX COL1_PI ' \
'(COL1);' \
'COLLECT STATISTICS USING THRESHOLD 10 PERCENT AND THRESHOLD 60 DAYS COLUMN COL1 ON DB.TABLENAME;'

cursor.execute(sql_str) <--Exception is thrown here

which results in the "Only an ET or null statement is legal after a DDL Statement"

I'm guessing it doesn't like the fact that I have 2 statements in the one sql string.

How would I go about achieving my desired outcome of just reading in a sql file and running the contents (whether multi statement or not) in the database?
I don't want users to have to decorate every sql file with BT; and ET; everywhere.

 

Thanks. 

 

2 REPLIES 2
Enthusiast

Re: Python teradatasql: Only an ET or null statement is legal after a DDL Statement error

Just noting that this seems to work seemlessly in Teradata Assistant - just pressing F5 will run multi statements in the 1 session without any BT or ETs.
Can the same not be done for the teradatasql module?

Teradata Employee

Re: Python teradatasql: Only an ET or null statement is legal after a DDL Statement error

First, please make sure that you are using teradatasql 16.20.0.43 or later, which includes auto-commit and transaction management.

 

>>> I'm guessing it doesn't like the fact that I have 2 statements in the one sql string.

Correct. That error is from the Teradata Database.

 

Submit your SQL statements one at a time to avoid Teradata Database Error 3932 "Only an ET or null statement is legal after a DDL Statement".

 

cur = conn.cursor()
cur.execute("CREATE MULTISET TABLE DB.TABLENAME (COL1 INTEGER NOT NULL) PRIMARY INDEX COL1_PI (COL1)")
cur.execute("COLLECT STATISTICS USING THRESHOLD 10 PERCENT AND THRESHOLD 60 DAYS COLUMN COL1 ON DB.TABLENAME")

 

 

 

>>> How would I go about achieving my desired outcome of just reading in a sql file and running the contents (whether multi statement or not) in the database?

 

If you want to submit the SQL statements individually then your app will need to parse the SQL statements, separate them at semicolon boundaries, and execute each SQL statement individually.

 

 

>>> seems to work seemlessly in Teradata Assistant - just pressing F5 will run multi statements in the 1 session without any BT or ETs. Can the same not be done for the teradatasql module?

 

Teradata SQL Assistant is an app, not a driver. SQL Assistant, like BTEQ and other apps, parse SQL requests and identify the semicolon boundaries.

 

On the other hand, our drivers -- JDBC Driver, ODBC Driver, Python driver, R driver, etc. -- do not separate multi-statement requests and execute the statements individually. The job of the driver is to simply execute the SQL request from the app exactly as the app specified.

 

If you want your app to submit the SQL statements individually then your app will need to parse the SQL statements, separate them at semicolon boundaries, and execute each SQL statement individually.