SQLEP 1.7.3 Tutorial

###The tutorial is updated for SQLP 2.2.3+ and SQLEP 1.7.3+

###Introduction The new features of the SQL Processor Eclipse Plugin (SQLEP) version 1.7.3 are

• types conversions for procedures and functions
• POJO assignment for procedures and functions
• new control directives for general string substitutions

Types modification

As is true for POJOs types modification presented in POJOs types modification, the same can be done for procedures and functions using the directives pojogen-types-for-procedure and pojogen-types-for-function.

In the next sample we'd like to change the default type for the input parameter T from java.sql.Timestamp to java.util.Date

pojogen-types-for-procedure AN_HOUR_BEFORE T->:java.util.Date;

We can also ask for the SQL type in procedures and functions using the directives pojogen-show-type-for-procedure and pojogen-show-type-for-function. For example in

pojogen-show-type-for-function AN_HOUR_BEFORE T->

put the cursor after the control sequence -> and press a Ctrl-Space.

POJO assignment for procedures and functions

The standard behaviour of the pojogen generator is to generate one new POJO for every procedure or function. Now we can reuse the existing POJO for them using the control directives pojogen-pojos-for-procedures and pojogen-pojos-for-functions.

For example for the procedure NEW_PERSON the standard POJO NewPerson is generated. To carry input parameters for this procedure reusing the POJO Person, we can use

pojogen-pojos-for-procedures NEW_PERSON->::Person;

or

pojogen-pojos-for-procedures NEW_PERSON->:org.sample.model.Person;

In this case we are responsible the reused POJO have all required attributes (related to the input/output parameters).

###General string substitutions SQLEP is able to generate several kinds of artifacts (POJO, DAO, META SQL) based on DB model. This generation follows basic ORM approach. Of course, sometimes it's rather difficult to utilize this transformation for all cases. This is the reason, why general string substitution can help.

Suppose the procedure NEW_PERSON is in the runtime located in (Oracle) package PKGS. In standard fashion the next META SQL statement is generated for this procedure invocation

PROC_NEW_PERSON(CALL,inx=NewPerson)=
  call NEW_PERSON(:<newid, :dateOfBirth, :ssn, :firstName, :lastName, :gender)
;

We want to call this procedure inside the package PKGS

PROC_NEW_PERSON_RET_RS(CALL,inx=NewPersonRetRs)=
  call PKGS.NEW_PERSON_RET_RS(:dateOfBirth, :ssn, :firstName, :lastName, :gender)
;

The next control directives usage can help

replace-all-regex REP1 call NEW_PERSON;
replace-all-replacement REP1 call PKGS.NEW_PERSON;

The REP1 is simply the identifier, which is used to relate both control directives. call NEW_PERSON is the string, which has to be replaced. call PKGS.NEW_PERSON is the substitution string.