Description
Execute a SQL statement or a batch of SQL statements that modify the data or the schema (DML or DDL).
MySQL, PostgreSQL, Microsoft SQL Server and Amazon Redshift connections:
Syntax
ExecuteSqlNonQuery(connection_id, sql_statement[, param1] [,…paramN])
Arguments
connection_id (string) - The unique identifier of the connection to use to execute the query. You can find it in the connection's page.
sql_query - String expression that evaluates to a query that returns a scalar value.
param11 ... paramN - optional parameters to pass to the query. Use ? as placeholder in the query. Make sure to use arguments of the data types that are compatible with the columns' data types. Do not enclose the placeholders in quotes.
Google BigQuery connections:
Syntax
ExecuteSqlNonQuery(connection_id, sql_query[, configuration_json])
Arguments
connection_id (string) - The unique identifier of the connection to use to execute the query. You can find it in the connection's page.
sql_query - String expression that evaluates to a query that returns a scalar value.
configuration_json - optional string:
{
"useQueryCache" : boolean
,"useLegacySql" : boolean
}
- useQueryCache (optional) - boolean expression. Specifies whether to look for the result in the query cache (default value is true).
- useLegacySql (optional) - boolean expression. Specifies whether to use BigQuery's legacy SQL dialect for the query (default value is false).
Examples
ExecuteSqlNonQuery('mysql_55','INSERT INTO events (description) VALUES (\'Started processing data\'); UPDATE Products SET Price = Price * 2;')
ExecuteSqlNonQuery('mysql_55','INSERT INTO events (description) VALUES (?)',event_data)
ExecuteSqlNonQuery('bq_58', 'INSERT INTO events_stg (id, ts, edata) SELECT * FROM events_old WHERE ts < \'2017-01-01\';','{"useQueryCache" : false}')
Notes
This function only executes in job runtime. When validating a package with variables that use the function or in X-console, the function returns null.
Return value datatype
The function returns a map with the following keys:
- status - true if the statement executed successfully.
- rowsAffected - numbers of rows affected (depends on database platform)
- errorCode - returned error code if status is false (statement failed)
- exception - returned exception string if status is false (statement failed)
Impact of null value
If the connection_id or sql_query are null, null is returned.