SQLP 2.0 and SQLEP 1.3 Tutorial

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

• the unified grammar
• the simplified terminology

Unfortunately, the side effect is that the backward compatibility is broken.

###Control directives The control directives for POJOs and DB tables identification have been renamed

• in for dynamic input values identification - it refers POJO class
• inx for dynamic input values identification - it refers POJO entity
• const for static input values identification - it refers POJO class
• constx for static input values identification - it refers POJO entity
• out for output values identification - it refers POJO class
• outx for output values identification - it refers POJO entity
• tab for database tables/columns identification

This can be seen in the following snippet in statements.qry

GET_BANK_ACCOUNT(CRUD,final=,inx=BankAccount,outx=BankAccount,tab=billingDetails=b,tab=subscriber=s)=
  select %b.BA_ACCOUNT @baAccount, %b.ID @id(id), %b.SUBSCRIBER @subscriber.id(id), %b.TYPE @type, %b.VERSION @version
         {? :subscriber(call=toInit) | , %s.LIBRARY @subscriber.library.id, %s.CONTACT @subscriber.contact.id, %s.NAME @subscriber.name, %s.VERSION @subscriber.version }
  from %%BILLING_DETAILS b
  {? :subscriber(call=toInit) | left join %%SUBSCRIBER s on %b.SUBSCRIBER = %s.ID }
  {= where
    {& %b.BA_ACCOUNT = :baAccount }
    {& %b.ID = :id }
    {& %b.SUBSCRIBER = :subscriber.id }
    {& %b.TYPE = :type }
  }
;

All these control directives in turn switch on the validation and content assist. The content assist can also help to establish the control directives itself (using Ctrl+Space).

###Modifiers Up to the previous release of the SQLP the META types and other supplement values were used like in the following example presenting optimistic locking

UPDATE_PERSON(CRUD,final=,identx=Person,colx=Person,dbcol=person)=
  update %%PERSON
  {= set
    { ,%FIRST_NAME = :firstName^^call=isDef }
    { ,%LAST_NAME = :lastName^^call=isDef }
    { ,%DATE_OF_BIRTH = :dateOfBirth^^call=isDef }
    { ,%SSN = :ssn^^call=isDef }
    { ,%VERSION = %VERSION + 1 }
  }
  {= where
    {& %ID = :id^^notnull }
    {& %VERSION = :version^^notnull }
  }
;

In the new grammar for any input/output value there can be a list of modifiers enclosed in parenthesis, like in the following META SQL statement

UPDATE_PERSON(CRUD,final=,inx=Person,outx=Person,tab=person)=
  update %%PERSON
  {= set
    { ,%FIRST_NAME = :firstName(call=isDef) }
    { ,%LAST_NAME = :lastName(call=isDef) }
    { ,%DATE_OF_BIRTH = :dateOfBirth(call=isDef) }
    { ,%SSN = :ssn(call=isDef) }
    { ,%VERSION = %VERSION + 1 }
  }
  {= where
    {& %ID = :id(notnull) }
    {& %VERSION = :version(notnull) }
  }
;

Also the syntax colouring is utilized