You can use an SQL transformation to call a stored procedure in a MySQL database or to process SQL queries midstream in a pipeline that runs against a MySQL database.
You can configure an SQL transformation to process the following types of SQL statements:
Stored procedure
Stored procedures reside in the database and run within the database. When you configure the SQL transformation to process a stored procedure, it passes input parameters to the stored procedure. The stored procedure passes the return value or values to the output fields of the transformation.
Before you configure an SQL transformation to call a stored procedure, make sure to specify the following property in the Runtime Advanced Connection Properties of the MySQL connection for the stored procedures to function correctly: NO_SSPS=0.
Informatica recommends that you create a dedicated MySQL connection specifically configured with this property for stored procedure configurations. Avoid using this connection for mappings that do not involve calling a stored procedure.
SQL Query
You can configure an SQL transformation to process a saved query that you create in Data Integration or you can enter a query in the SQL editor.
For more information about SQL transformations, see Transformations.
Rules and guidelines for calling a stored procedure
Consider the following rules and guidelines for calling a stored procedure:
•You can't define a stored procedure that contains Unicode characters.
•You can't configure an unconnected stored procedure using the SQL transformation.
•You can't process a stored function in an SQL transformation.
•You can't configure the input or in-out parameter in an entered query that you define in the SQL editor.