SQLP 2.2.6 and SQLEP 1.7.8 Tutorial - hudec/sql-processor GitHub Wiki

###The tutorial is updated for SQLP 2.2.8+ and SQLEP 1.7.8+

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

• references (REF) to another optional feature
• MAP based optional feature
• new REPLACE_LIKE_CHARS optional feature

The new features of the SQL Processor (SQLP) version 2.2.7 is the extended interface SqlSession.

The new features of the SQL Processor (SQLP) version 2.2.8 is the new REPLACE_LIKE_STRING optional feature.

###Sample All new features are presented on one sample. We want to search people utilizing the next SQL statement

SELECT_PEOPLE(QRY)=
  select p.ID @id, p.NAME_FIRST @name.first, p.NAME_LAST @name.last
  from PERSON p
  {= where
    {& p.id = :id}
    {& UPPER(p.NAME_FIRST) like :+name.first}
    {& UPPER(p.NAME_LAST) like :+name.last}
  }
;

So, for the names we can use the wildcards characters % and _. But users of our web application prefer to use another wildcards characters * and ? instead of the previous ones.

For this purpose we can utilize the next MAP based optional feature

REPLACE1(MOPT)=['*':'%', '?':'_'];

The MOPT is the indicator that this optional feature is in fact a Map. The supplied value is in Groovy syntax. Now we'll change the SELECT statement in the next way

SELECT_PEOPLE(QRY,OPT=REPLACE_LIKE_CHARS=REF=REPLACE1)=
  select p.ID @id, p.NAME_FIRST @name.first, p.NAME_LAST @name.last
  from PERSON p
  {= where
    {& p.id = :id}
    {& UPPER(p.NAME_FIRST) like :+name.first}
    {& UPPER(p.NAME_LAST) like :+name.last}
  }
;

We have here a new optional feature REPLACE_LIKE_CHARS, which is an indicator, that all values in input attributes (name.first and name.last), which are used for like SQL command, are transformed

• the character * is replaced with %
• the character ? is replaced with _

The optional feature value is REF=REPLACE1, which denotes, the REPLACE_LIKE_CHARS have in fact a value ['*':'%', '?':'_'].

###Informix Informix has a special SQL command matches, which is similar to like. To support it, SQLP has a possibility to define an optional feature

LIKE_STRING(OPT)=matches;

A standard value is of course like. Also SQLP can translate all matches to like in the runtime using the next optional feature

REAPLACE_LIKE_STRING(OPT)=like;