All Forums Teradata Studio
kmiesse 7 posts Joined 04/14
20 Aug 2015
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!

tomnolan 594 posts Joined 01/08
20 Aug 2015

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.
 

kmiesse 7 posts Joined 04/14
20 Aug 2015

Thank you very much for the detailed info!

MikeDempsey 94 posts Joined 10/06
26 Oct 2015

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.)

You must sign in to leave a comment.