Conditional Processing

Introduction

Conditional processing simply means that a profile can adjust its content based on certain conditions. A few examples:

Which version of the database is being accessed
The format of the database URL
The client environment i.e Java version, vendor, etc.
User properties
Database connection properties

Conditional processing is especially useful when adapting the profile for different versions of the database (and/or JDBC driver). Another use is to replace generic error messages with more user friendly messages.

If you have some programming skills conditions are expressed using If, Elseif and Else statements.

There are two phases when conditions are processed:

Conditional processing when database connection is establishedIf, ElseIf and Else elements can be specified almost everywhere in the profile
Conditional processing during command execution The OnError element is used to define a message that will appear in DbVisualizer if a command fails. Conditions are used to control what message should appear

DbVisualizer uses the type attribute to determine which If elements should be executed in which of the two phases. If this attribute is set to the value runtime, it will be processed in the second phase. If it is not specified, it will be processed in the first phase.

Conditional processing when database connection is established

These are the call signatures for the utilities used when processing 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.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.isDatabaseVersionGT(Integer major)
boolean #util.isDatabaseVersionGT(Integer major, Integer minor)
boolean #util.isDatabaseType(String type)
boolean #util.isNotDatabaseType(String type)

The following example shows the use of conditions that are processed during connect of the database connection.

<Command id="sybase-ase.getLogins">
   <If test="#util.isDatabaseVersionLTE(5)">
      <SQL>
         <![CDATA[
select name from master.dbo.syslogins
         ]]>
     </SQL>
   </If>
   <ElseIf test="#util.isDatabaseVersionEQ(9)">
      <SQL>
         <![CDATA[
select name, suid from master.dbo.syslogins
         ]]>
      </SQL>
   </ElseIf>
   <Else>
      <SQL>
         <![CDATA[
select name, suid, dbname from master.dbo.syslogins
        ]]>
      </SQL>
    </Else>
</Command>

The above means that if the major version of the database being accessed is less than or equal to 5, the first SQL is used. If the major version is equal to 9, the second SQL is used, and the last SQL is used for all other versions. The test attribute may contain conditions that are ANDed or ORed. Conditions can contain multiple evaluations, combined using parenthesis. The If, ElseIf and Else elements may be placed anywhere in the XML file.

Here is another example that controls whether certain nodes will appear in the database objects tree or not.

<!-- 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>
<Commands> 
   <OnError> 
      <!-- The ORA-942 error means "the table or view doesn't exist" --> 
      <!-- It is catched here since these errors typically indicates -->  
      <!-- that the user don'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>

As you can see, this example contains nested uses of If.

Conditional processing during command execution

Using conditional processing to evaluate any errors from a Command may be useful to rephrase error messages to be more user friendly.

<Commands> 
   <OnError> 
      <!-- The ORA-942 error means "the table or view doesn't exist" --> 
      <!-- It is catched here since these errors typically indicates -->  
      <!-- that the user don'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>

The OnError element can be used in Commands and Command elements. If used in Commands element, its conditions are processed for all its commands. If it's part of a specific Command, it is processed only for that command.

Conditions using connection properties

Connection properties are all settings made in the Properties tab for a connection. These can be referred in the database profile using the #cp. prefix. Check this for a sample 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="/doclink/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 for the properties are unfortunately not publicly available. Just send us an email and we will let you know.

Conditions using JDBC "driver properties"

Driver properties are all settings made in the Properties tab and in the Driver Properties category. These can be referred 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 during profile load and may have the constant value "always" or a boolean statement that is evaluated. This attribute is valid for the following XML elements:

Action
ActionGroup
GroupNode
DataNode
ObjectView
DataView

This will drop the element if the database version is less than 4.1:

<GroupNode type="TableEngines" label="Storage Engines" 
   drop-on-condition="#util.isDatabaseVersionLT(4,1)">

This will drop the element unconditionally which is useful in a sub profile needing to for example drop a parent DataView defined in the same ObjectView:

<DataView id="generic-catalog-tables" drop-on-condition="always"/>

If you are looking to drop for example the parent ObjectView and all its DataView elements, and then add a replacement for the ObjectView in the sub profile, you need to drop each of the DataView elements in the sub profile rather than first doing a drop of the parent ObjectView and then re-define it in the sub profile. Here is an example that will not work resulting that both ObjectView="Procedures" definitions will be 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 you need to override the ObjectView and in it, drop the DataView:

<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 apply for all elements supporting the drop-on-condition attribute.

<- PreviousIcons Next ->Database Profile Utilities