How to execute Multiple Dynamic statements in Oracle / Teradata DB?

[akataria]

Hi ,

I have a workflow which is preparing some SQL statements based on transformations. 

Requirement is : I need to execute those statements in Database at the end of workflow. Currently I am taking the output in Excel but need to execute multiple SQL statements in Database.

Tried using multiple tools like Dynamic Input tool , in-DB tools but not much luck.

Any pointers please .. TIA.

[abe_ibanez]

Hello, 

Have not use native Alteryx tools for this situation before, by I would assume that you could use the Output Data tool to send your sql statement to the database. That said, I was always taught to use Python to send SQL commands to a DB. I believe that would give you more control and full visibility into what the script is actually doing. My process has always been a little different though. I would have Alteryx prepare the SQL statement, then save it to a directory as a .txt file (pretty sure it also works if you save it as a .sql file). Once there, I would run a Python script to read in the SQL statement and send it to the server. This allowed me to double check the SQL code before pushing it to the server, and also allows to have a copy of the statement for your records. But, if you want to do everything in one step, I would recommend adding a Python tool to your workflow, feed it your SQL code from the workflow, and have the Python tool execute the code on the DB. 

If you are only working with Oracle databases, I recommend that you use the oracledb Python library. 

The script would look something like this: 

import oracledb

# Connection parameters
username = "your_username"
password = "your_password"
host = "localhost"  # or your Oracle server IP/hostname
port = 1521  # default Oracle port
service_name = "TSTDB"  # your service name/SID

dsn = oracledb.makedsn(host, port, service_name=service_name)
connection = oracledb.connect(user=username, password=password, dsn=dsn)

cursor = connection.cursor()

# Execute SQL queries
try:
    sql = """
        UPDATE employees 
        SET salary = salary * 1.1 
        WHERE department_id = :dept
    """
    cursor.execute(sql, dept=20)
    connection.commit()

except oracledb.DatabaseError as e:
    error, = e.args
    print(f"Oracle Error: {error.message}")
    connection.rollback()
    
finally:
    cursor.close()
    connection.close()

For this to work within Alteryx, you would need to add a few tings: 

from ayx import Package
Package.installPackages(['oracledb'])

from ayx import Alteryx

import oracledb

sql_code = Alteryx.read("#1") # Bring in your code from the workflow

# Connection parameters
username = "your_username"
password = "your_password"
host = "localhost"  # or your Oracle server IP/hostname
port = 1521  # default Oracle port
service_name = "TSTDB"  # your service name/SID

dsn = oracledb.makedsn(host, port, service_name=service_name)
connection = oracledb.connect(user=username, password=password, dsn=dsn)

cursor = connection.cursor()

# Execute SQL queries
try:
    sql = sql_code # send your prepared sql code to the server
    cursor.execute(sql)
    connection.commit()

except oracledb.DatabaseError as e:
    error, = e.args
    print(f"Oracle Error: {error.message}")
    connection.rollback()
    
finally:
    cursor.close()
    connection.close()

I hope this helps :) 

[jrlindem · Accepted solution]

@akataria 

You don't need Python to do this.  You can do this in Alteryx:

Are you needing your final output to remain in Excel along with the database SQL statements?

If not:  Simply switch to the output too, configure to point to the data-table you want to output your data to, and then leverage the “Pre Create SQL Statement” or “Post Create SQL Statement” options within the output tool.  Respectively, the “Pre” performs those SQL statements before the output and the “Post” is after.

You can run multiple SQL statements by separating them with a semi-colon.

If you do still need to output to excel, then you can do the same as above, but with a few more steps.  Run an output to the database, to a dummy table.  Use the output tool to write to a stage table (one that you don’t intend to keep), then perform your Pre/Post SQL.  Part of your Post-SQL could be a drop table statement if you don’t want to keep the object.

Then you can use the Block-Until-Done Tool or just branch your workflow to the other output writing to Excel.  The Block-Until-Done tool is only necessary if you must sequence your outputs.

In this way (either option) you can definitely perform SQL statements or kick off procedures from the Pre/Post SQL options in the output tool when writing your output to database.

Hope this helps, -Jay