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.
|