Design

Back Next

SQL Binding Tab

For each action in a data access service connector, use the

SQL Binding

tab to specify multiple SQL queries to access the data from the data source. After the SQL entries are made, the tab displays details such as the SQL name, SQL text, column case of the expression, query type, maximum rows returned by the SQL query, and maximum wait time in seconds.

When you add multiple queries, the statements are executed in the order in which the queries are listed in the table. You can use the arrow buttons to move a query up or down. You can also delete SQL queries.

To add a row of an SQL entry, click

on the

SQL Binding

tab. Enter the following details for each SQL query:

SQL Name: Name of the SQL query.

SQL Text: A valid SQL statement that returns at least one row.

Column Case: Specify whether the characters in the expression must be converted into lowercase or uppercase, or remain unchanged. Default is
Lowercase
.

Type: Select one of the following values:
Data Manipulation
. Select this value to query information and apply logic to it to generate a completely different set of data.
Stored Procedure
. Select this value to use a prepared SQL code that you can save and reuse.
Has Result Set: Reserved for future use.

Maximum Rows: The maximum number of rows returned by the SQL query that can be displayed in the SQL response payload. Default is 100 rows. The maximum number of rows can't exceed 10,000 rows.

Max Wait (Seconds): The maximum length of time in seconds that a database client can wait for a response from the database before timing out the session.

Application Integration does not perform a commit after it executes every SQL query. To help with a rollback in case of an SQL query failure, the commit is done only at the end after all the SQL queries are executed. In a few databases, implicit commit occurs for DDL statements such as CREATE TABLE, DROP TABLE, and so on. These executions can't be rolled back.

Defining Actions

Download Guide

Watch

Comments

Cloud Application Integration Homepage

0 COMMENTS

We’d like to hear from you! Log in to comment.

Adam Derenbecher - September 06, 2022

Can you please add that the input variables cannot have white spaces? We accidently had one with a white space and it would just tell us that the sql statement was incorrect.

Informatica Documentation Team - September 08, 2022

Hi Adam,

We’re working to address your comments and will get back to you.

Thanks,

Informatica Documentation team

Informatica Documentation Team - September 09, 2022

Hi Adam,

We’re happy to let you know that we addressed your feedback in the documentation. Please see the following link: https://docs.informatica.com/integration-cloud/cloud-application-integration/current-version/design/designing-service-connectors/creating-a-data-access-service-connector/defining-actions/input-tab.html

Thanks,

Informatica Documentation team

Edius Maicol Vargas Paniagua - August 11, 2023

Could you please add what is the syntax to include tha input variables on the SQL Statement?

It is not clear, and there is no available on examples or documentation. It should be some way to put an input variable as a where criteria in order to be a parametric query.

Informatica Documentation Team - August 14, 2023

Hi Edius,

We’re working to address your comments and will get back to you.

Thanks,

Informatica Documentation team

Eshangani Gunaratne - October 04, 2023

We are also facing the same issue. We were able to all Oracle procedures with hardcoded values. But when we pass the input parameter as it is we get error

ORA-06576: not a valid function or procedure name

Informatica Documentation Team - October 05, 2023

Hi Edius and Eshangani,

The correct syntax is: select * from <table_name> where <field_name>='{$<parameter_name>}'

The parameter phrase must be enclosed within single quotation marks.

We will also publish the updated documentation soon.

Thanks,

Informatica Documentation team

Eshangani Gunaratne - October 05, 2023

Hi Team

Thank you so much for the update. With the given information, we were able to pass input parameters to an Oracle procedure and execute the procedure. As below

CALL apps.TESTDUMMY_RT_PRICE_CALL.eg_test4('{$p_account_number}')

But when we have the output parameters we are getting the error below. Could you please help with this?

CALL apps. TESTDUMMY_RT_PRICE_CALL.eg_test5'{$p_account_number}', '{$x_rtn_status}')

Error executing cloud process [dasConnectionAutomatedStep] with id [347201]: Error executing expression (



           { concat(abx:getFaultString(),': ',abx:getFaultDetail()) }


               { "false" }

               {
               bconnutil:serialize( $multiDataAccessRequest )
               }

{ concat(abx:getFaultString(),': ',abx:getFaultDetail()) }

{ concat(abx:getFaultString(),': ',abx:getFaultDetail()) }


   ). Reason: Internal error evaluating function aexqo:f1000 at line 51

thanks

Eshangani

Informatica Documentation Team - October 13, 2023

HI Eshangani Gunaratne,

You can add output parameters on the Output tab and define the return values using the Get From field. If you select Column in the Get From field, you must also provide the column name.

If you are facing issues with using the Expression Editor to define the output parameters, you can contact Informatica Global Customer Support.

Thanks,

Informatica Documentation team

Scott Hughes - January 08, 2024

Any update from Informatica on providing syntax on how to use the binding variables? Based on these comments I am able to now pass input parameters into a stored proc, but I have the exact same error mentioned below for output parameters:

Reason: Internal error evaluating function aexqo:f1000 at line 51

It would be great to have an example and proper documentation instead of trying to figure this out via the comments section.

Thanks.

Informatica Documentation Team - January 10, 2024

Hi Scott, Eshangani, and Edius,

We're glad to let you know that we addressed your feedback on this page:

https://docs.informatica.com/integration-cloud/application-integration/current-version/design/designing-service-connectors/creating-a-data-access-service-connector/defining-actions/configuring-multiple-sql-queries.html

We've documented the syntax to use input variables.

Regarding the output variable error, we request you to contact Informatica Global Customer Support for a resolution. The team can verify your query or stored procedure and assist you further.

Thanks,

Informatica Documentation team

Rename Saved Search

Table of Contents

Design

Design

SQL Binding Tab

SQL Binding Tab