3932: Only an ET or null statement is legal after a DDL Statement.

Database
Enthusiast

3932: Only an ET or null statement is legal after a DDL Statement.

Hi All,

I am writing a code to change PI of an existing table.

  1. I am creating a backup table with new PI.
  2. Copying data from existing table
  3. Drop Old Table
  4. Rename backup table to original table.

Following is the code snippet : 

create table person_bck (person_id bigint not null , person_name varchar(100)) primary index (person_id);

insert into person_bck (person_id , person_name ) select person_id , person_name from person;

drop table person;

rename table person_bck to person;

This code executes fine but fails with the following error when i surround it with bt - et.

Statement 2: INSERT Failed. 3932:  Only an ET or null statement is legal after a DDL Statement. 

I want to write this code as part of a single transaction; if any of the statement fails, it rolls back everything.

Some posts suggested to commit after the create table command and end transaction. then do rest work in another transaction.

However, in that case the rollback of create table will not happend because its a separate unit altogether.

How can i achieve this in a single atomic transaction unit?

8 REPLIES
Enthusiast

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

In BTET mode each individual statement is treated as a transaction, to combine multiple statements in transaction you need to use BTET. In case of DDL it is a requirement that each DDL statement must be followed by a COMMIT in case of ANSI mode. but in Teradata Mode I didn't find any such restriction. Might be changing the transaction mode will work for you. Else you will have to use the ET after each DDL to accomplish this. Or you can use some procedure to accomlish the task in entirety. 

Khurram
Teradata Employee

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

Put

BT;

<UR LOGIC>

ET;

IF any thing fails in between.it rolls back

Enthusiast

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

Saeed, i got the error in TD mode.

But, i got the point.

Thanks.

New Member

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

After adding BT and ET , am facing the same error, Can u pls help me!

Enthusiast

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

I'm using RazorSQL and getting this error. I cannot run multiple DDL's at the same time. Any suggestions?

 

Screen Shot 2018-05-29 at 9.23.57 AM.png

Highlighted
Junior Contributor

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

RazorSQL seems to submit all statements as a Multi Statement Request (i.e. all at once), check if there's a way/setting to submit them individually (like F5/F9 in SQL Assistant/Studio).

Enthusiast

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

Hi dnoeth,

 

I was able to figure it out by using TMODE=TERA,CHARSET=ASCII in my JDBC URL. However, now I am getting the following error: 

 

ERROR: [Teradata Database] [TeraJDBC 16.10.00.07] [Error 3807]
[SQLState 42S02] Object 'midlife_a' does not exist. Error Code:
3807

 

I have tried deselecting the option that ignores error but it doesn't seem to be working...

Enthusiast

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

Dear dnoeth,

 

It is interesting that another ODBC tool is giving out quite similar error messages to what is syntactically correct SQL statements and statement blocks.  In another post, I mentioned that I was having the same issues.  I see some similar posts from earlier in the past too.

 

You mentioned Single vs. Multiple statements.  Another user mentioned TMODE=TERA option. 

 

How can I control these options?  What options are available from an ODBC tool to change these settings?  Do we need to set something up in the database server once, or each time after establishing a session, or something completely different?

 

Thanks for your help.