Importing Data from a Database
Data can be imported directly from many different databases, including SQL, Oracle, IBM and Microsoft Access.
Prerequisites
You must have created a process in Rulex.
You must have installed the OBDC drivers (64-bit) corresponding to the database you want to import from. Rulex supplies the required drivers for SQLite, SQL Server and Oracle, but other databases require specific drivers, such as Microsoft Access Driver (*.mdb, *.accdb) for importing from MS Access.
Additional tabs
The following additional tabs are provided:
Query tab, where the query created in the Options tab is displayed. If required a pre-existing query can be copied and pasted to this tab, instead of building the query in the Rulex interface.
Documentation tab where you can document your task,
Parametric options tab where you can configure process variables instead of fixed values. Parametric equivalents are expressed in italics in this page (PO).
Procedure
Drag and drop the Import from Database task onto the central stage.
Double click the task.
In the Database parameters section, configure the parameters required for each database, as described in the table below. Parameters that are not required will be greyed out.
Click Connect: a connection is established with the database and the Table list box is filled with the list of all the available tables which are presented in the database.
Configure the general options as described in the table below.
Select the tables you want to load from the Table list, then click Load.
Save and compute the task.
Import from database options | ||
Database parameters | ||
Parameter | PO | Description |
Platform | type | The database platform type. The following platforms are currently supported:
Unicode is also supported, if its corresponding driver is installed. Rulex's technical documentation does not and cannot provide comprehensive guidelines on the use of third-party software, beyond how Rulex integrates with this software. Please consult the technical documentation of the third-party software itself for up to date information. |
Server | server | The target database server IP address. |
Port | port | The number of the specific process to connect to. If this parameter is left empty, the default port used is 1521. |
Database name | name | The name of the database you want to use (or the database file name if using SQLite or Microsoft Access). |
Authentication | winauth | The method of authentication used to access the database. Possible values are:
|
User name | usr | The login user name. This parameter is mandatory only when User Name/Password has been selected as the authentication mode |
Password | pwd | The login password. This parameter is mandatory only when User Name/Password has been selected as the authentication mode. |
Schema | schema | The database schema. |
Driver | driver | ODBC driver installed on your machine. For example, Microsoft Access Driver (*.mdb, *.accdb) if you want to import from Microsoft Access. |
Service name | service | Database service name of the Oracle database. The Service Name and the Database Name are not interchangeable. Their use is defined by the DB manager, which decides to use either the DB Name (SSID) or the Service Name. If the Service Name is not specified, its name corresponds with the Database Name. |
Bulk size for prefetch | bulksize | Retrieves blocks of data of the size specified. This option must be set to 1 when importing from Microsoft Access. |
Additional options | connparam | Additional parameters which may be required by ODBC drivers (according to database used) can be expressed through this edit box with the following format: option1=value1;option2=value2. The DSN value can also be inserted here, if present, with the following format: DSN=value. |
General options | ||
Repeat query execution every | checkdata, sleeptime, nres | Select to repeat the query until the required number of records are retrieved (checkdata). Subsequently set the frequency in seconds (sleeptime) with which the query must be performed and the number of records (nres) that must be retrieved. |
Strip spaces | stripspaces | Select to remove spaces from strings. For example, the string ” age ” is then imported as “age”. |
Compress white spaces | onlyonespace | Select this option to remove extra consecutive spaces from within strings. For example the string "university program" would be imported as "university program". |
Use old computation data if the source table is not available | useolddata | Select to use data from the previous computation if the source table is not available. |
Table list | N/A | Select the tables you want to load from this list: they will be displayed in the Load data from table box. The table selected corresponds to the parametric option tablename. |
Search table | N/A | If your selected database contains many tables, you can use the search function to find the required table, by entering its name in the edit box. |
Case sensitive | N/A | If selected, the search operation will be performed in case sensitive mode. |
Load data from table | tablename | The name of the table from which want to select data. You can either select the table from the table list, or manually enter the name. |
Extract query | N/A | Click to view the query that you have constructed in the interface in the Query tab, where you can easily modify it if required. The SQL query itself corresponds to the parametric option Query. |
Number of records in preview | nrecord | Specify how many records you want to display in the Table preview. |
Load | N/A | Click to load the selected tables according to your specific configuration, which will then be displayed in the Table preview window. |