Parameterized SQL queries in the Database Connector

[sean_tully]

Is it possible to parameterize a query in a Database Connector?

For example, say I want to run a SELECT query and have the ability to parameterize something in a WHERE clause, e.g.

SELECT time, user, volume FROM MyDB.Streams WHERE snapshot = $1

...where the parameter $1 could be linked to some variable in the model, such as a Parameter.

By analogy, in python, using pyodbc I can do something like:

import pyodbc

# this is my parameter that will be inserted into the query
snapshot = 123 

pyodbc.connect( [INSERT CONNECTION DETAILS HERE] )
cursor = connection.cursor()
query = "SELECT time, user, volume FROM Streams WHERE snapshot = ?"
cursor.execute(query, snapshot)

[nguyenthanhnam2310]

For more information: SQL Queries. You can take a parameterized SQL with a table in FlexScript

[FelixMoehlmann · Accepted solution]

You can bind parameters to a prepared statement.

[sean_tully]

Thanks - this is what I was looking for. 

So it seems I can use the existing Database Connector to make the connection and I can access the connection through something like:
Database.Connection con = Database.Connection("DBConnector1");

But I need to compose and run the query in FlexScript instead of entering it in the "Import" tab of the Database Connector menu.

[nguyenthanhnam2310]

You can also find it in the Database.connection. 

For example, here is my example code to import the data from the DatabaseConnector1 - new_table and place it into GlobalTable3.

Database.Connection con = Database.Connection("DatabaseConnector1");
con.connect();
//return con.isConnected;
con.query("SELECT * FROM new_table").cloneTo(Table("GlobalTable3"));
con.disconnect();