Previous Next
Opalis Integration Server > Foundation Objects > Utilities > Query Database

Query Database
The Query Database object will query a database and return the resulting rows as Published Data. The Query Database object supports the following database types:
Click on one of the links above to view instructions for that database type.
Note: Opalis 6.2.2 disables native MySQL support in the Query Database object and Write to Database object. You will be required to change those objects to use ODBC if you were using the native MySQL support prior to 6.2.2.
How it is used
The Query Database object can be used to query a database for the detailed description of an error code that has appeared on one of the systems in the data center and then that description is sent to an administrator via email.
Configuration
To configure the Query Database object you will need to know the database that you are connecting to as well as the SQL query that you are running.
To use a Microsoft Access database:
1.
Double-click the Query Database object. The Query Database Properties dialog appears.
2.
Click the Details tab and type the SQL query in the Query field.
3.
Click the Connection tab. From the Database type drop-down list, select the Access option.
4.
In the File field, type the name of the Microsoft Access database file that you want to access.
5.
In the Workgroup file field, type the name of the Microsoft Access Workgroup file that is associated with this database.
6.
In the User name field, type the user name for the Workgroup file.
7.
In the Password field, type the password for the Workgroup file.
8.
In the DB password field, type the password for the Microsoft Access database.
9.
Click the Timeout tab and specify the amount of time that the Query Database object will wait for the database operations to complete. Set this value to 0 to wait indefinitely.
10.
Click the Security Credentials tab. Do one of the following:
To run this object using the same account that the Action Server uses, select the Use the security of the account assigned to the service option.
To specify a different account to run this object, select the This account option and complete the User name and Password fields.
Note: If you specify an invalid user name or password, the account assigned to the Action Server will be used to run the object instead.
11.
Click the Finish button.
To use an ODBC-compatible database:
1.
Double-click the Query Database object. The Query Database Properties dialog appears.
2.
Click the Details tab and type the SQL query in the Query field.
3.
Click the Connection tab. From the Database type drop-down list, select the ODBC option.
4.
In the DSN field, type the data source name.
5.
In the User name field, type the user name for this database.
6.
In the Password field, type the password for this database.
7.
Click the Timeout tab and specify the amount of time that the Query Database object will wait for the database operations to complete. Set this value to 0 to wait indefinitely.
8.
Click the Security Credentials tab. Do one of the following:
To run this object using the same account that the Action Server uses, select the Use the security of the account assigned to the service option.
To specify a different account to run this object, select the This account option and complete the User name and Password fields.
Note: If you specify an invalid user name or password, the account assigned to the Action Server will be used to run the object instead.
9.
Click the Finish button.
To use an Oracle database:
1.
Double-click the Query Database object. The Query Database Properties dialog appears.
2.
Click the Details tab and type the SQL query in the Query field.
3.
Click the Connection tab. From the Database type drop-down list, select the Oracle option.
4.
In the Service Name field, type the name of the service that you want to access.
5.
In the User name field, type the user name for this service.
6.
In the Password field, type the password for this service.
7.
Click the Timeout tab and specify the amount of time that the Query Database object will wait for the database operations to complete. Set this value to 0 to wait indefinitely.
8.
Click the Security Credentials tab. Do one of the following:
To run this object using the same account that the Action Server uses, select the Use the security of the account assigned to the service option.
To specify a different account to run this object, select the This account option and complete the User name and Password fields.
Note: If you specify an invalid user name or password, the account assigned to the Action Server will be used to run the object instead.
9.
Click the Finish button.
To use a SQL Server database:
1.
Double-click the Query Database object. The Query Database Properties dialog appears.
2.
Click the Details tab and type the SQL query in the Query field.
3.
Click the Connection tab. From the Database type drop-down list, select the SQL Server option.
4.
From the Authentication options, select either Windows Authentication or SQL Server Authentication.
5.
In the Server field, type the name of the SQL Server that you want to access.
6.
In the Initial catalog field, type the name of the initial catalog.
7.
If you selected the SQL Server Authentication option in step 5, type the user name and password used to access the SQL Server in the User name and Password fields.
8.
Click the Timeout tab and specify the amount of time that the Query Database object will wait for the database operations to complete. Set this value to 0 to wait indefinitely.
9.
Click the Security Credentials tab. Do one of the following:
To run this object using the same account that the Action Server uses, select the Use the security of the account assigned to the service option.
To specify a different account to run this object, select the This account option and complete the User name and Password fields.
Note: If you specify an invalid user name or password, the account assigned to the Action Server will be used to run the object instead.
10.
Click the Finish button.
Alternate icon
You can change the icon that appears in the workspace for this object. To change the icon, follow the instructions in Changing the object icon.
Available Published Data
 
Name
Description
Numeric return value of the query
When a query that returns a numeric value is used, this will be the value. For example, "Select COUNT(*) where FirstName=John"
Database query
The database query that was sent to the database.
Initial Catalog
The initial catalog that was used when connecting to the database. This Published Data will only be available when SQL Server is selected on the Connection tab.
Database server
The name of the database server. This Published Data will only be available when SQL Server is selected on the Connection tab.
Database user
The name of the user used to connect to the database server.
Oracle service name
The Oracle service name. This Published Data will only be available when Oracle is selected on the Connection tab.
ODBC DSN
The name of the ODBC DSN. This Published Data will only be available when ODBC is selected on the Connection tab.
Access file
The Access database file that was queried. This Published Data will only be available when Access is selected on the Connection tab.
Access workgroup information file
The Access workgroup file that is associated with the Access database file. This Published Data will only be available when Access is selected on the Connection tab.
For each row returned
Full line as a string with fields separated by ';'
The entire the row that was returned with each field in the row separated by a semi-colon (;). Use the Field data manipulation function to obtain the values of a field within the row.
Common Published Data

Previous Next