SQLExecute Command
 |
 |
 |
Contents |
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
