SQLP 2.2.2 and SQLEP 1.7.1 Tutorial - hudec/sql-processor GitHub Wiki

###The tutorial is updated for SQLP 2.2.2+ and SQLEP 1.7.1+

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

• new control directive metagen-optional-features
• optional features in the statement's execution scope
• improved INSERT statements generation for not required cloumns
  • new control directive metagen-optimize-insert
  • new SQLP {= columns (...) } for INSERT

###Control directive metagen-optional-features Using this control directive, for example

metagen-optional-features SELECT_COUNTRY IOPT=SURROUND_QUERY_MIN_LEN=1;

we can inject into generated META SQL statement this optional feature

SELECT_COUNTRY(QRY,IOPT=SURROUND_QUERY_MIN_LEN=1,inx=Country,outx=Country,tab=country)=
... 

###Optional features in the runtime The optional feature can be defined in the statement's execution (=runtime) scope. For more info please see Optional features scope.

###Improved INSERT statements generation The standard generated META SQL statement is the next one

INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %CTYPE, %ADDRESS, %PHONE_NUMBER, %VERSION, %COUNTRY_CODE)
  {= values (:id(seq=SIMPLE_SEQUENCE), :personId, :ctype, :address, :phoneNumber, :version, :country.code) }
;

Using the control directive

metagen-optimize-insert CONTACT;

the generated META SQL statement will become

INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT {= columns (
  , %ID
  , %PERSON_ID
  , %CTYPE
  , %ADDRESS
  {? :phoneNumber | ,%PHONE_NUMBER}
  , %VERSION
  , %COUNTRY_CODE
) }
{= values (
  ,:id(seq=SIMPLE_SEQUENCE)
  ,:personId
  ,:ctype
  ,:address
  {? :phoneNumber | ,:phoneNumber}
  ,:version
  ,:country.code
) }
;

For all optional (not required) DB columns (for this case only phoneNumber) special code is generated. The result effect is, if the related attribute value is null, this value won't be seeded into DB column (so the default value can be applied for example).