The JDBC plugin provides the ability to deploy SQL scripts to supported databases using JDBC.
Any JDBC driver which is supported by the database vendor for the version you have installed is likely to be compatible with the FlexDeploy JDBC plugin. Supported databases include following.
- Oracle 10g+
- Using statement delimiter of ;
- PL/SQL blocks starting with DECLARE or BEGIN and finishing with END; /
- MySql 5.1+
- PostgreSql 9.0+
- Derby 10.8.2.2+
- SAP HANA
- SQL Server 2008+
- MariaDB 10.0+
- Vertica 6.5+
- H2 1.2.137+
- solidDB 6.5+
- SQL Azure
- DB2 9.7+
- AWS Redshift
- Hsql 1.8+
- Sybase ASE12.5+
- SQLite 3.7.2+
- DB2 z/OS 9.1+
- EnterpriseDB 9.4+
- Phoenix 4.2.2+
- Greenplum 4.3+
- Vendor neutral implementation using JDBC
- Oracle specific extensions for PL/SQL
JDBC plugin connects to database using JDBC driver and executes SQL statements. See blog entry https://flexagon.com/make-database-changes-with-the-jdbc-plugin/ for examples on how to organize SQL files for deployment using partial deployment operations.
Additionally, you need to consider which user to use for execution. There are few options :
- system or admin user
- Oracle has user called system which will be able to perform all operations. You should be careful in this situation as this user has access to do anything.
- Object in SQL files must be schema qualified.
- schema owner
- This is safer option than system user, but you need to grant additional permissions to schema user.
- Objects in SQL files need not be qualified, but it is always good idea to do so, hence that would allow you to change user that executes files.
- deployment specific user, let's say FLEXDEPLOY
- This is also safer option than system user and may offer less maintenance of permissions as one user is used for managing all schema objects. For Oracle database, you can grant DBA role to such user or you can grant specific permissions to this user as well (CREATE ANY TABLE, CREATE ANY INDEX, ALTER ANY TABLE, ALTER ANY INDEX, DROP ANY TABLE, DROP ANY INDEX etc.)
- Objects in SQL files must be schema qualified.
Partial Deployment - Supported Object Types
JDBC Plugin supports partial deployment, see below for details on supported object types.
The JDBC plugin partial deploy support allows for deploying File and Folder type objects. These links explain the three types and their attributes. This functionality became available in the 4.5.1 release.
- DB Objects
- Data Fixes
- Other Objects