Difference between revisions of "SQLExecute Command"
(→Data for the connection) |
(→PostgreSQL) |
||
| (7 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
| − | {{Idiomas|Comando SQLExecute|SQLExecute Command}} | + | {{Idiomas|Comando SQLExecute|SQLExecute Command | SQL 実行コマンド}} |
| − | [[category: | + | [[category:GXtest Commands]] |
== SQLExecute == | == SQLExecute == | ||
| Line 8: | Line 8: | ||
* The data to establish a connection against the database, such as host, name, user, password, etc. | * 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 SQL Query which we want to run | ||
| − | * The destination | + | * The destination variable or Datapool in which the results will be saved (if any results are returned at all) |
| Line 18: | Line 18: | ||
== Data for the connection == | == 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 ( | + | 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 === | ||
| Line 24: | Line 24: | ||
=== DB2 === | === DB2 === | ||
| − | db2=Database=SAMPLE;UserID=myUsername;Password=myPassword;Server=xxx.xxx.xxx.xxx:50000 | + | 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 === | ||
oracle=Data Source=TORCL;User Id=myUsername;Password=myPassword; | 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[http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html] | ||
=== MySQL === | === MySQL === | ||
mysql=Server=myServerAddress;Port=3306;Database=myDataBase;Uid=myUsername;Pwd=myPassword; | mysql=Server=myServerAddress;Port=3306;Database=myDataBase;Uid=myUsername;Pwd=myPassword; | ||
| + | |||
| + | === PostgreSQL === | ||
| + | postgresql=Host=myServerAddress;Username=myUsername;Password=mypassword;Database=myDataBase | ||
== SQL Query == | == SQL Query == | ||
| Line 45: | Line 54: | ||
*DB2 | *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/) | *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 | ||
Latest revision as of 14:52, 7 February 2018
 |
 |
 |
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
