Comment above REPLACE PROCEDURE causes error

Teradata Studio
Enthusiast

Comment above REPLACE PROCEDURE causes error

In Teradata Studio, when executing a REPLACE PROCEDURE statement with a comment (block /* */or single-line --) above the statement, I receive the error "REPLACE  failed.  Failed [3706 : 42000] Syntax error: Invalid  SQL Statement." Below is an example that fails but if you remove the "-- comment" line it is successful. I am able to put comments above a REPLACE VIEW statement and successfully execute it. Anyone else have this issue or am I missing something? I am using version 15.10.00.01.201504291700.

-- comment 

REPLACE PROCEDURE  db_name.proc_name ( )

BEGIN

END;

;

Thanks!

3 REPLIES
Teradata Employee

Re: Comment above REPLACE PROCEDURE causes error

The Teradata Database requires a special wire protocol to be used for the CREATE PROCEDURE and REPLACE PROCEDURE statements, that is slightly different than the wire protocol used for all other SQL statements.

Because of the need to use a different wire protocol, the Teradata JDBC Driver must examine the SQL request text and determine whether or not each SQL statement is CREATE/REPLACE PROCEDURE.

If you place a comment before the CREATE/REPLACE PROCEDURE keywords, then you will confuse the Teradata JDBC Driver, it won't recognize the SQL statement as CREATE/REPLACE PROCEDURE, and then it will send the SQL request to the Teradata Database using the normal wire protocol, and you will get the 3706 syntax error.

For comparison, BTEQ deals with this issue by forcing the user to put a CREATE/REPLACE PROCEDURE in a separate file, and use the special .COMPILE command to submit the CREATE/REPLACE PROCEDURE command to the Teradata Database. That is also a clunky workaround.

There is an outstanding RFC 94094 against the Teradata Database to remove the need for the special wire protocol for CREATE PROCEDURE and REPLACE PROCEDURE statements.

Enthusiast

Re: Comment above REPLACE PROCEDURE causes error

Thank you very much for the detailed info!

Teradata Employee

Re: Comment above REPLACE PROCEDURE causes error

Since it may be years before the database removes the requirement for a different wire protocol you should handle the issue within TD Studio - as SQLA does.

(It looks for this situation and strips off the comments before passing it to the .Net provider.)