SQLExecute Command

SQLExecute

Employing the SQLExecute command makes possible running an arbitraty SQL query against a database. It is necessary to provide the following information:

• The data to establish a connection against the database, such as host, name, user, password, etc.
• The SQL Query which we want to run
• The destination variable or Datapool in which the results will be saved (if any results are returned at all)

An example:

Data for the connection

The information required to establish a connection to a database depends on its engine. A few examples are displayed below, where the database engine and the rest of data needed for the connection are shown (Oracle, SQLServer, MySQL, DB2 and PostgreSQL):

SQLServer

sqlserver=Data Source=myServer;Initial Catalog=myDatabase;Integrated Security=False;User Id=myUsername;Password=myPassword

DB2

db2=Provider=DB2OLEDB;Database=SAMPLE;UserID=myUsername;Password=myPassword;Server=xxx.xxx.xxx.xxx:50000

• Note: the DB2 connection PROVIDER installed on the machine must be specified in order to succesfully connect to the DB2 database

Oracle

oracle=Data Source=TORCL;User Id=myUsername;Password=myPassword;

Note 1: If you don’t have a Data Source defined you should indicate this parameters on the string connection: Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST = xxx.xx.xx.xx)(PORT = xxxx))(CONNECT_DATA=(SERVICE_NAME = name)))

Nota 2: You should install on the local machine which will connect to the data base an Oracle client, for example the one you could download with the following link[1]

MySQL

mysql=Server=myServerAddress;Port=3306;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

PostgreSQL

postgresql=Host=myServerAddress;Username=myUsername;Password=mypassword;Database=myDataBase

SQL Query

The SQL query to be used can be of three types:

• Performs a change in the database, nonetheless, it does not return a value.
• It returns a unique value. In that case, either a variable to save the returned value may be specified, or a datapool/column.
• Returns a set of columns and rows. In that case, it is possible to specify a datapool with the same amount of columns as the returned data. In that way, all of the returned rows are saved in the datapool, deleting the data which was previously available in it.

Supported Connectors

• Oracle
• SQLServer
• DB2
• Mysql (Installing the .Net Client for MySQL is required, and it's available for downloading it from: http://dev.mysql.com/downloads/connector/net/)
• PostgreSQL