SQLP 2.3.0 and SQLEP 1.8.0 Tutorial

###The tutorial is updated for SQLP 2.3.0+ and SQLEP 1.8.0+

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

• ID generator is a new concept for uniform handling of sequences and identities. As a consequence the INSERT statement is written in the same way regardless which technology is used to establish the ID value of a database record.
• SQLEP is now able to generate META SQL using ID generators
• MSSQL is the next database fully supported in the runtime (SQLP) and design process (SQLEP). The simple sample for the MSSQL DB can be seen at
• rewritten all simple samples to reflect new features

###ID generator The usage of sequences and identities in INSERT statement is described here. Now, instead of a basic sequence (presented on HSQLDB sample)

SIMPLE_SEQUENCE(OPT)=call next value for SIMPLE_SEQUENCE;
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(seq=SIMPLE_SEQUENCE), :person.id, :type, :address, :phoneNumber, :xNote) }
;

we can use an identity generator

SEQ_COMMON(OPT)=call next value for SIMPLE_SEQUENCE;
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=COMMON,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;

The sequence SIMPLE_SEQUENCE doesn't need to be exactly defined (it can be a default sequence based on SqlFeature). In this case we can use an identity generator in this way

IDGEN_COMMON(OPT)=seq=SIMPLE_SEQUENCE;
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=COMMON,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;

Instead of an identity (presented on HSQLDB sample)

IDSEL(OPT)=call identity();
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idsel=IDSEL), :person.id, :type, :address, :phoneNumber, :xNote) }
;

we can use an identity generator

IDSEL_COMMON(OPT)=call identity();
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=COMMON,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;

The identity doesn't need to be exactly defined (it can be a default identity based on SqlFeature). In this case we can use an identity generator in this way

IDGEN_COMMON(OPT)=idsel;
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=COMMON,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;

The advantage is in the case of an identity generator all INSERT statements are the same. So for example we can use the same statements for different databases

SEQ_COMMON(OPT,HSQLDB)=seq=SIMPLE_SEQUENCE;
IDSEL_COMMON(OPT,MYSQL)=select last_insert_id();

INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=COMMON,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;

###SQLEP and ID generators In the following snippets the simple sample based on HSQLDB is used. The advantage is this database supports both sequences and identities. So we are going to establish general identity COMMON and for table PERSON the sequence SIMPLE_SEQUENCE:

###Using the explicitly defined sequence and identity Control directives in definitions.qry:

metagen-global-identity COMMON;
metagen-table-sequence CONTACT SIMPLE_SEQUENCE;
metagen-generate-identities;
metagen-generate-sequences;

Generated META SQL in statements.qry:

SIMPLE_SEQUENCE(OPT)=call next value for SIMPLE_SEQUENCE;
COMMON(OPT)=call identity();
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(seq=SIMPLE_SEQUENCE), :person.id, :type, :address, :phoneNumber, :xNote) }
;
INSERT_PERSON(CRUD,inx=Person,outx=Person,tab=person)=
  insert into %%PERSON (%FIRST_NAME, %LAST_NAME, %DATE_OF_BIRTH, %GENDER, %SSN)
  {= values (:id(idsel=COMMON), :firstName, :lastName, :dateOfBirth, :gender, :ssn) }
;

###Using the explicitly defined ID generators Control directives in definitions.qry:

metagen-global-identity COMMON;
metagen-table-sequence CONTACT SIMPLE_SEQUENCE;
metagen-generate-idgenerators;

Generated META SQL in statements.qry:

SEQ_SIMPLE_SEQUENCE(OPT)=call next value for SIMPLE_SEQUENCE;
IDSEL_COMMON(OPT)=call identity();
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=SIMPLE_SEQUENCE,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;
INSERT_PERSON(CRUD,inx=Person,outx=Person,tab=person)=
  insert into %%PERSON (%ID, %FIRST_NAME, %LAST_NAME, %DATE_OF_BIRTH, %GENDER, %SSN)
  {= values (:id(idgen=COMMON,id=ID), :firstName, :lastName, :dateOfBirth, :gender, :ssn) }
;

###Using the explicitly defined default ID generators Control directives in definitions.qry:

metagen-global-identity COMMON;
metagen-table-sequence CONTACT SIMPLE_SEQUENCE;
metagen-generate-default-idgenerators;

Generated META SQL in statements.qry:

IDGEN_SIMPLE_SEQUENCE(OPT)=seq=SIMPLE_SEQUENCE;
IDGEN_COMMON(OPT)=idsel;
INSERT_CONTACT(CRUD,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=SIMPLE_SEQUENCE,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;
INSERT_PERSON(CRUD,inx=Person,outx=Person,tab=person)=
  insert into %%PERSON (%ID, %FIRST_NAME, %LAST_NAME, %DATE_OF_BIRTH, %GENDER, %SSN)
  {= values (:id(idgen=COMMON,id=ID), :firstName, :lastName, :dateOfBirth, :gender, :ssn) }
;