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

Question

I am able to read data. However, when writing data to Teradata database, I get the following error:

   Error in .verify.JDBC.result(s, "Unable to create JDBC prepared 
   statement ",  : 
   Unable to create JDBC prepared statement INSERT INTO 
   dl_nbu.alex_test2 VALUES(?,?) ([Teradata Database] 
   [TeraJDBC 16.10.00.07] [Error 3932] [SQLState 25000] Only an ET or 
   null statement is legal after a DDL Statement.)

Here is my code:

     dbWriteTable(tdConnection, "dl_nbu.alex_test2", all_files4)

Post a sample of your data. Also: what package are you using to connect to SQL? — Carlos Eduardo Lagosta, Jun 26 '18 at 05:20
Hi Carlos. I am using RJDBC. The data I have is just two character columns. — alexsmith2, Jun 26 '18 at 05:22
The error message indicates there was an uncommited DDL statement before. `Create Table`? `DATABASE xxx`? Can you check Teradata's QueryLog what was actually send? — dnoeth, Jun 26 '18 at 06:48

Carlos Eduardo Lagosta · Answer 1 · 2018-06-30T17:28:32.470

Hard to give an answer without details. But check the RJDBC help page:

Due to the fact that JDBC can talk to a wide variety of databases, the SQL dialect understood by the database is not known in advance. Therefore the RJDBC implementation tries to adhere to the SQL92 standard, but not all databases are compliant. This affects mainly functions such as dbWriteTable that have to automatically generate SQL code. One major ability is the support for quoted identifiers. The SQL92 standard uses double-quotes, but many database engines either don’t support it or use other character. The identifier.quote parameter allows you to set the proper quote character for the database used. For example MySQL would require identifier.quote="`". If set to NA, the ability to quote identifiers is disabled, which poses restrictions on the names that can be used for tables and fields. Other functionality is not affected.

Try this and see what happens:

allfiles4 -> allFilesFour
dbWriteTable(tdConnection, "testTwo", allFilesFour)

If works, then the problem is in the quote identifier. Change the line where you make the connection to something like:

tdConnection <- dbConnect( JDBC( identifier.quote = "`" ), ...

Check this post: Do different databases use different name quote?

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

1 Answers1