SQLP 2.1 and SQLEP 1.4 Tutorial

###The tutorial is updated for SQLP 2.1+ and SQLEP 1.4+

###Introduction The new features of the SQL Processor (SQLP) version 2.1.0 and the SQL Processor Eclipse Plugin (SQLEP) version 1.4.0 are

• the extended control directives
• the direct DatabaseMetaData methods invocation
• improved sequences and identities handling
• extended simple samples for all kinds of databases
• the 10 minutes Tutorial update

###The catalog filter Alongside the control directive database-active-schema there's a new control directive database-in-catalog. It enables the determination of the catalog as the filter for the database objects selection (for more info please see http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html).

###The database type The POJO/DAO/META SQL generation is influenced by the type of the database. It is derived from the database meta information. In any case, it can be manually defined using the new control directive, as in the following code snippet

database-is-of-type HSQLDB; 

Pressing the CTRL-Space after the control directive, the popup menu with the supported database is presented.

###The database meta info We can ask for the database meta info like in the following code

database-show-database-info HSQL Database Engine,2.2.9,2,2;

Simply put the cursor after the control control directive and press a Ctrl-Space.

###The driver meta info We can ask for the JDBC driver meta info like in the following code

database-show-driver-info HSQL Database Engine Driver,2.2.9,2,2;

Simply put the cursor after the control control directive and press a Ctrl-Space.

###The DatabaseMetaData methods invocation We can ask for the any DatabaseMetaData methods invocation output using the new control directive like in the following code snippet

database-show-driver-output getSystemFunctions->DATABASE,IFNULL,USER;

After the control directive there's a method name like getSystemFunctions in the previous example. Pressing the CTRL-Space after the -> the required information is presented.

###Improved sequences and identities handling The sequences and identities usage is described in https://github.com/hudec/sql-processor/wiki/CRUD-Tutorial#insert-statement. The updated SQLP is able to handle the sequences and identities automatically, without the necessity to specify the related statements. The main requirement is to define the database type in the runtime, like in the following example

JdbcEngineFactory factory = new JdbcEngineFactory();
factory.setMetaFilesNames("statements.qry");
factory.setFilter(SqlFeature.HSQLDB);

In this case it's enough to use the real sequence name in the META SQL INSERT statement, like in the next HSQLDB sample

INSERT_CONTACT(CRUD,final=,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %ADDRESS, %PHONE_NUMBER)
  {= values (:id(seq=SIMPLE_SEQUENCE), :person.id, :address, :phoneNumber) }
;

or the identity abbreviation IDSEL, like in the next MySQL sample

INSERT_CONTACT(CRUD,final=,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%PERSON_ID, %ADDRESS, %PHONE_NUMBER)
  {= values (:id(idsel), :person.id, :address, :phoneNumber) }
;

The supported databases are

• DB2
• HSQLDB
• Informix
• MS SQL (partially)
• MySQL
• Oracle
• PostgreSQL

The excellent examples presenting these features are located at https://github.com/hudec/sql-processor/tree/master/simple-samples.

###Improved sequences and identities statements generation The SQLEP is able to generate the sequences statements (described in https://github.com/hudec/sql-processor/wiki/SQLEP-1.2.x-META-SQL-generation#sequences) or identities statements (described in https://github.com/hudec/sql-processor/wiki/SQLEP-1.2.x-META-SQL-generation#identities). There's a new way, how to force these artifacts generation using the control directive

metagen-generate-sequences;

or

metagen-generate-identities;

In this case the SQLEP is trying do the best, and the output can be used as the first step for the next manual optimization.

###10 minutes Tutorial update The 10 minutes Tutorial is updated. The simple sample set-up is much simple. Also the transition from one database type to another one is very simple. At he same time at https://github.com/hudec/sql-processor/tree/master/simple-samples you can find the simple samples for the next databases

• DB2
• HSQLDB
• Informix
• MySQL
• Oracle
• PostgreSQ

###Improved META SQL generation At https://github.com/hudec/sql-processor/wiki/Optional-features#search-based-on-text-fragments there's described the usage of LIKE in the META SQL statements. The standard fashion, how the SQLEP generates the META SQL statements is the next one

• for all String base values the WHERE part of the generated statement contains the SQL fragment like in the next sample

  {& UPPER(%p.FIRST_NAME) like :+firstName }

• for all not-String base values the WHERE part of the generated statement contains the SQL fragment like in the next sample

  {& %p.DATE_OF_BIRTH = :dateOfBirth }

This behaviour can be changed using the control directives

metagen-like-columns PERSON DATE_OF_BIRTH;
metagen-not-like-columns PERSON FIRST_NAME;