Conditional Processing
Conditional processing lets a database profile adapt its content to conditions, for example:
- The version of the database
- The format of the database URL
- The client environment, such as the Java version or vendor
- User properties
- Database connection properties
Conditional processing is useful to adapt a profile to different versions of the database or JDBC driver. You can also use it to replace generic error messages with clearer ones.
You express conditions using the If, ElseIf and Else elements. The test attribute holds the condition, as an OGNL expression.
Conditions are processed in two phases:
- When the database connection is established: you can use
If,ElseIfandElseelements almost anywhere in the profile. - When a command runs: the
OnErrorelement defines a message that DbVisualizer shows if a command fails. Conditions control which message is shown.
The context attribute of the If and ElseIf elements controls the phase. If it is set to runtime, the condition is processed when a command runs. If it is not set, or set to load, the condition is processed when the connection is established.
Conditional processing when database connection is established
These are the utility functions that you can use in conditions:
boolean #util.isNull(String string)
boolean #util.isNullOrWhitespace(String string)
boolean #util.isDatabaseVersionLT(Integer major)
boolean #util.isDatabaseVersionLT(Integer major, Integer minor)
boolean #util.isDatabaseVersionLT(Integer major, Integer minor, Integer bugfix)
boolean #util.isDatabaseVersionLTE(Integer major)
boolean #util.isDatabaseVersionLTE(Integer major, Integer minor)
boolean #util.isDatabaseVersionEQ(Integer major)
boolean #util.isDatabaseVersionEQ(Integer major, Integer minor)
boolean #util.isDatabaseVersionGTE(Integer major)
boolean #util.isDatabaseVersionGTE(Integer major, Integer minor)
boolean #util.isDatabaseVersionGTE(Integer major, Integer minor, Integer bugfix)
boolean #util.isDatabaseVersionGT(Integer major)
boolean #util.isDatabaseVersionGT(Integer major, Integer minor)
boolean #util.isDatabaseType(String type)
boolean #util.isNotDatabaseType(String type)
The following example uses conditions that are processed when the connection is established:
<Command id="sqlserver.getLogins">
<If test="#util.isDatabaseVersionLTE(13)">
<SQL>
<![CDATA[
select name from master.dbo.syslogins
]]>
</SQL>
</If>
<ElseIf test="#util.isDatabaseVersionEQ(14)">
<SQL>
<![CDATA[
select name, suser_id(name) "SUID" from master.dbo.syslogins
]]>
</SQL>
</ElseIf>
<Else>
<SQL>
<![CDATA[
select name, suser_id(name) "SUID", dbname from master.dbo.syslogins
]]>
</SQL>
</Else>
</Command>
If the major version of the database is 13 or lower, the first SQL is used. If it is 14, the second SQL is used. For all other versions, the last SQL is used. A test attribute can combine several conditions using and and or, grouped using parentheses.
The next example controls whether nodes appear in the database objects tree. It contains a nested If:
<!-- Getting Table Engines was added in MySQL 4.1 -->
<If test="#util.isDatabaseVersionGTE(4, 1)">
<GroupNode type="TableEngines" label="Table Engines" isLeaf="true"/>
<!-- "Errors" was added in MySQL 5 -->
<If test="#util.isDatabaseVersionGTE(5)">
<GroupNode type="Errors" label="Errors" isLeaf="true"/>
</If>
</If>
Conditional processing during command execution
You can use conditions on the errors from a command, for example to show a clearer error message.
<Commands>
<OnError>
<!-- The ORA-942 error means "the table or view doesn't exist" -->
<!-- It is caught here since these errors typically indicate -->
<!-- that the user doesn't have privileges to access the SYS and/or -->
<!-- V$ tables. -->
<If test="#result.getErrorCode() eq 942" context="runtime">
<Message>
<![CDATA[
You don't have the required privileges to view this object.
]]>
</Message>
</If>
<ElseIf test="#result.getErrorCode() eq 17008" context="runtime">
<Message>
<![CDATA[
Your connection with the database server has been interrupted!
Please <a href="connect" action="connect">reconnect</a> to re-establish the connection.
]]>
</Message>
</ElseIf>
</OnError>
...
</Commands>
You can use the OnError element in Commands and Command elements. In a Commands element, its conditions apply to all commands. In a Command element, they apply only to that command.
Conditions using connection properties
Connection properties are the settings in the Properties tab of a connection. You refer to them in the database profile using the #cp. prefix. This example is from the vertica.xml profile:
<If test="#cp.getBool('SinglePhysicalConnection')">
<Action id="vertica-local-temp-tables-create-table" label="Create Table"
icon="add" reload="true"
class="tableCreate"
classargs="create"
doclink="dbvis.url.ug.create-table"/>
</If>
<Else>
<Action id="vertica-local-temp-tables-create-table-INFO" label="Create Table" icon="add">
<Confirm>
<![CDATA[
Creating <b>Temporary Table</b> is only supported with <b>Single Shared Physical Connection</b> enabled.
]]>
</Confirm>
</Action>
</Else>
The names of the properties are not documented. Contact DbVisualizer support if you need the name of a property.
Conditions using JDBC "driver properties"
Driver properties are the settings in the Driver Properties category in the Properties tab of a connection. You refer to them in the database profile using the #dp. prefix.
<If test="#dp.useSSL eq true">
...
</If>
drop-on-condition attribute
The drop-on-condition attribute is processed when the profile is loaded. Its value is either always or a condition. If the value is always or the condition is true, DbVisualizer removes the element and its child elements. You can use the attribute in these elements:
ActionActionGroupGroupNodeDataNodeObjectViewDataView
This removes the element if the database version is lower than 4.1:
<GroupNode type="TableEngines" label="Storage Engines"
drop-on-condition="#util.isDatabaseVersionLT(4,1)">
This removes the element unconditionally. This is useful in a profile that extends another profile, for example to remove a DataView from the parent profile in the same ObjectView:
<DataView id="generic-catalog-tables" drop-on-condition="always"/>
To replace an ObjectView from the parent profile in the extending profile, drop each of its DataView elements. Don't drop the ObjectView and then define it again: the extending profile's definition is merged into the parent's ObjectView, so both are removed. This example doesn't work, since both ObjectView type="Procedures" definitions are removed:
<ObjectView type="Procedures" drop-on-condition="always"/>
<ObjectView type="Procedures">
<DataView id="redshift-procedures-Procedures" icon="Procedures" label="Procedures" viewer="grid">
...
</DataView>
</ObjectView>
Instead, define the ObjectView and drop the DataView in it:
<ObjectView type="Procedures">
<DataView id="generic-procedures-procedures" drop-on-condition="always"/>
<DataView id="redshift-procedures-Procedures" icon="Procedures" label="Procedures" viewer="grid">
...
</DataView>
</ObjectView>
The same applies to all elements that support the drop-on-condition attribute.