CODEWITHSUNDEEP
sqljdbcteradatateradatasql
Ofir Cohen
Ofir Cohen

Reputation: 1

Teradata create volatile doesn't work on JDBC

When running the following Python code and SQL query on a Teradata Vantage Express server:

#!/usr/bin/env python3
import teradatasql

query = """CREATE VOLATILE TABLE target_table AS (
    select * FROM MY_DB.MY_TABLE
  )
    WITH DATA ON COMMIT PRESERVE ROWS;
SELECT * FROM target_table;"""

con = teradatasql.connect(host="localhost", user="dbc", password="dbc")
cur = con.cursor()
cur.execute(query)

I get the following error:

teradatasql.OperationalError: [Version 17.20.0.7] [Session 2988] [Teradata Database] [Error 3932] Only an ET or null statement is legal after a DDL Statement.

However, when using bteq (Teradata's CLIv2 db connector), and running the same query it works like a charm and doesn't throw any error:

 BTEQ -- Enter your SQL request or BTEQ command:
CREATE VOLATILE TABLE target_table AS (
    select * FROM MY_DB.MY_TABLE
  )
    WITH DATA ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE target_table AS (
    select * FROM MY_DB.MY_TABLE
  )
    WITH DATA ON COMMIT PRESERVE ROWS;

 *** Table has been created.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
SELECT TOP 1 * FROM target_table;

SELECT TOP 1 * FROM target_table;

 *** Query completed. One row found. 9 columns returned.
 *** Total elapsed time was 1 second.

customer_id     customer_token                customer_branch customer_num
-------------- ------------------------------ --------------- ------------
          8585      452004                                 83       808038

 BTEQ -- Enter your SQL request or BTEQ command:

Any idea?

Note that no useful Google entries were found for either Python based JDBC drivers (e.g. teradatasql) or Node.js based drivers.

Upvotes: 0

Views: 244

Answers (1)

NickW
NickW

Reputation: 9788

In the bteq examples you’ve given there are individual queries being executed; each query is separated by a “;”. However, in the Python code you have combined 2 queries into a single string and are trying to execute that string as a single query - which won’t work.

You need to write the Python code to run each query separately, in the same way that the bteq code does. For example:

query = """CREATE VOLATILE TABLE target_table AS (
    select * FROM MY_DB.MY_TABLE
  )
    WITH DATA ON COMMIT PRESERVE ROWS;”””

con = teradatasql.connect(host="localhost", user="dbc", password="dbc")
cur = con.cursor()
cur.execute(query)

query = “””SELECT * FROM target_table;"""
cur.execute(query)

Upvotes: 1

Related Questions