SQL Query Parameter

SQL Parameter executes an SQL statement to a database and retrieves values from the dataset.

Parameter Details

Query :It is the SQL statement i.e. The syntax is database-dependent. SQLStatement can be parameterized.
Parameter Name :Unique name for the resulting dataset from the SQL query. It is automatically derived from the column names used in the query.

e.g. if given query is SELECT balance,creditcardbalance from acc_balance where account_id=’{AccountID}’ then parameter names are auto define as balance,creditcardbalance

Occurrence Count :indicates the ordinal position or instance of the match.
- Any :Randomly one of the occurrences is captured.
- All :All occurrences are captured. Here parameter values are saved in an array
- Specified :specified instance is captured. The default instance is 1.
Existing DSN : It is the DSN which is already present in {active correlation} correlation.conf.
New DSN : You can create a new DSN by giving below details
- Data Source Name : specifies the name of the datasource
- Host : IP/name of the database server.
- Port : listening port of the database server.
- Username : It is the username connect to database.
- Password : It is the password to connect to database.
- Database : indicates the type of database. Supported database are Postgres,MySQL,Oracle,SQL.
- DB Schema : it is the schema of the database to which to connect.
- Test DB Connection : It is used to test connection with the database.

Example :
In the following example, it connects to dbsource1 database using postgres. Also parameter AccountID is used in query to retrieve info corresponding to the value of AccountID param.

nsl_dbquery_var(balance; creditcardbalance, DSN="dbsource1" Query=" SELECT balance,creditcardbalance from acc_balance where account_id=’{AccountID}’ ", ORD=All);

nsl_search_var (AccountID, LB="<accountno>", RB="</accountno>", ORD=1, SaveOffset=0, SaveLen=0, Method=NONE, IgnoreCase=No, Search=BODY);

Steps for Oracle ODBC Driver installation

Download ODBC Driver for Oracle 4.3 DEB x64 from
https ://www.devart.com/odbc/oracle/download.html
Install the downloaded package(step1) using

dpkg -i <path to downloaded package>
Download Oracle instant client dependency zip from
https ://download.oracle.com/otn_software/linux/instantclient/218000/instantclient-basic-linux.x64-21.8.0.0.0dbru.zip
Unzip file downloaded in step2 using

unzip <zip file path>
Once unzipped, folder 'instantclient_21_8' will be created. Go inside it and run below command

rm $(ls | grep -v -e "libclntsh*" -e "libnnz*") network/ -rf
Enter the path of the folder from step4 in
/etc/ld.so.conf.d/oracle.conf.
Run command

ldconfig