What are the benefits of using native executors?

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