Benefits of Using Native Executors

Using native executors can provide distinct advantages including the ability to process database client-specific settings, commands, and environment variables within the SQL script.

Special Script Handling

A business analyst requests a data change that includes special characters that may interfere with the default script deployment behavior. Native database clients offer flexibility when deploying these types of scripts or when SQL statements need special handling.

For example, Oracle's native client "sqlplus" offers a wide range of commands such as SET, that can be intermixed with the script to modify the script deployment behavior at runtime.

  • Using these commands may be necessary when data containing special characters are loaded into tables.
  • Using these commands consistently ensures that all changes to the database follow a pattern such as an Oracle client's SPOOL command to indicate a spool output file.
  • Additional commands can be added for special script handling such as Oracle client's WHENEVER command to indicate how to handle errors during script execution.

Connection Attributes

Native database clients can be used to toggle various attributes:

  • Microsoft SQL Server client's SQLCMDLOGINTIMEOUT environment variable configures login timeout for the client.
  • The client's SQLCMDINI environment variable configures the init script to run when the client is started.

A DBA may want all deployments to run an init script prior to any deployments to their SQL Server database.

Logging

Native database clients typically provide more insight into database deployments than JDBC drivers. Stakeholders are familiar with native database clients and are accustomed to browsing log files to quickly identify issues, spot patterns, and expected output.

One example is when a SQL developer authored a script to update a large number of rows. They would be interested in reviewing the native database client log to verify the exact number of rows that were updated.

Native Database Responses

Below are examples of different responses from a native database client and JDBC driver.

Sample SQL:

--liquibase formatted sql

--changeset Adeel:person_table
create table person (
id int primary key,
name varchar(50) not null,
address1 varchar(50)
);
--rollback drop table person;

--changeset Adeel:person_insert
INSERT INTO PERSON (ID,NAME,ADDRESS1) VALUES (1,'John Smith','101 State St');
INSERT INTO person (id, name, address1) VALUES (2, 'Henry Blair', '812 Michigan Ave');
INSERT INTO person (id, name, address1) VALUES (3, 'Amy Long', '25 Waldorf Way');
INSERT INTO person (id, name, address1) VALUES (100, 'John Doe', '100 Liquibase Ave');
INSERT INTO person (id, name, address1) VALUES (1001, 'John Doe', '100 Liquibase Ave');
--rollback truncate table person;

--changeset Adeel:person_update
UPDATE person
SET address1=NULL
WHERE ID >= '100'
--rollback update person set address1='100 Liquibase Ave' WHERE ID >= '100'

Below are three changesets that were deployed. Successful output from the CREATE TABLE statement with the JDBC driver does not reveal what operation was performed on the database:

Running Changeset: release1/1.person.sql::1::Adeel
INFO [liquibase.changelog] Custom SQL executed
INFO [liquibase.changelog] ChangeSet release1/1.person.sql::person_table::Adeel ran successfully in 22ms
Running Changeset: release1/1.person.sql::2::Adeel
INFO [liquibase.changelog] Custom SQL executed
INFO [liquibase.changelog] ChangeSet release1/1.person.sql::person_insert::Adeel ran successfully in 83ms
Running Changeset: release1/1.person.sql::3::Adeel
INFO [liquibase.changelog] Custom SQL executed
INFO [liquibase.changelog] ChangeSet release1/1.person.sql::person_update::Adeel ran successfully in 8ms

The successful output from the same statement with the native database client provides more valuable information. It indicates that: 1) a table was created, 2) several rows were inserted and 3) a couple of rows were updated.

Related Links