EDT:Default SQL Generation

Overview

EDT 0.7.0 release supports the following EGL action statements to generate default SQL statements:

1. ADD Statement
2. DELETE Statement
3. GET Statement
4. OPEN Statement
5. REPLACE Statement

The following sections show how to use the feature in EDT.

EGL Records Definition

record Product{@Table {name="Product"}}
   id bigint{@Id};
   name string;
   price float; 
end 

In that Record type, the Id annotation specifies which record field is used as the primary key.  The optional @Table annotation specifies the table to which the record is mapped.

record OrderItem type Entity {@table{name = "OrderItem"}}
   ITEM_ID int{@Id}; 
   NAME string{ @Column { insertable=true } }; 
   IMAGE string?{ @Column { updateable=true } }; 
   PRICE decimal(7, 2)?; 
   DESCRIPTION string?; 
end 

The previous definition was created from a database table schema, by use of the EGL retrieve feature.

EGL Variables Declaration

basicRec Product;     
itemEntity OrderItem; 
rs SQLResultSet?; 
ds SQLDataSource? = new SQLDataSource("jdbc:derby:C:/databases/EGLDerbyR7;create=true");    
 

Default SQL Generation

ADD Statement 

Click anywhere in the EGL SQL statement, press ctrl-1, and click Add SQL Statement.

Here is an example:

• From this:
  

   add basicRec to ds; 

• To this:

   add basicRec to ds 
      with #sql{ insert into Product (id, name, price) values (?, ?, ?)}; 

            (2) add itemEntity to ds; //for entity record
Change To:
             add itemEntity to ds with
                             #sql{ insert into OrderItem (ITEM_ID, Name, IMAGE, price, description) values (?, ?, ?, ?, ?) }; 

DELETE Statement

        (3) delete basicRec from ds; // for basic record
Change To:
          delete basicRec from ds with
                  #sql{ delete from Product where id = ? };

      (4) delete itemEntity from ds; //for entity record
Change To:
         delete itemEntity from ds with
                 #sql{ delete from OrderItem where ITEM_ID = ? };

GET Statement

     (5) GET basicRec from ds; //for basic record
Change To:
         GET basicRec from ds using basicRec.id with
                   #sql{ select id, rtrim(name), price from Product where id = ? };

     (6) GET itemEntity from ds; //for entity record
Change To:
         GET itemEntity from ds using itemEntity.ITEM_ID with
                   #sql{ select ITEM_ID, rtrim(Name), rtrim(IMAGE), price, rtrim(description) from OrderItem where ITEM_ID = ? };

OPEN Statement

     (7) rows OrderItem; open rs from ds for rows;
Change To:
        open rs from ds using rows.ITEM_ID with
                  #sql{ select ITEM_ID, rtrim(Name), rtrim(IMAGE), price, rtrim(description) from OrderItem where ITEM_ID =  ? };

REPLACE(Update) Statement 

    (8) replace itemEntity to ds ;
Change To:
       replace itemEntity to ds with
                #sql{ update OrderItem set Name = ?,IMAGE = ?, price = ?,description = ? where ITEM_ID = ? } ;

Current Limitation

  Note the feature can only generate default SQL statements for basic record or entity record that does not contain any entity relationship field.