Retrieve data with EGL SQLRecord

 

Requirements
IBM EGL Community Edition
http://www.ibm.com/developerworks/downloads/r/eglcommunity/index.html?S_TACT=105AGX28&S_CMP=DLMAIN
Mysql Community Edition
http://dev.mysql.com/downloads/mysql/
 
 
Creating a MySQL schema
To set up and configure MySQL
http://www.programmingsystems.eu/content/installation-mysql-51-windows-server-2003

We start by creating a new schema in MySQL i will call this myegldata.
In this schema i will create a table:
customer
containing fields:
cID
cNumber
cName


SQL:
CREATE TABLE `myegldata`.`customer` (
  `cID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `cNumber` INTEGER UNSIGNED NOT NULL,
  `cName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`cID`)
)
ENGINE = InnoDB;

INSERT INTO customer (cNumber,cName) VALUES (1,"Microsoft");
INSERT INTO customer (cNumber,cName) VALUES (2,"Oracle");

Set up the EGL eclipse environment to connect to MySQL
In the Eclipse Project Explorer click with the right mousebutton on your project and select select New and select SQL File.
- Enter a Filename MyDataConnection
- Select the database type MySQL_51
- Select a connection profile if it does not exist create one to the myegldata schema.
- Select the database myegldata.
- Test the connection
 
Create a Rich UI and XML webservice project
We want to make use of the Rich UI which EGL has to offer in combination with a XML webservice.
I will refer to a previously published article how to set up and consumer XML webservices with EGL:
http://www.programmingsystems.eu/content/create-simple-web-service-ibm-egl
 
Add the SQLRecord
I will make use of the MyFirstWebService code.
First we need to create a SQLRecord statement and add it to the webservice.

// service
record  customer type  SQLRecord record {
  tableNames = [[customer type SQLRecord "customer"]], keyItems=[cid]}
  cid string {column="cID"};
  cnumber string {column="cNumber"};
  cname string {column="cName"};
end

service MyFirstWebService
...

 
Add a function to the Web Service to retrieve the data
Now we can add GetCustomerNameFromID function to the webservice which will retrieve the customer name froma the data if you enter a customer number..

// service

record customer type SQLRecord 
 {tableNames = [["customer"]], keyItems=[cid]}
 cid string     {column="cID"};
 cnumber string     {column="cNumber"};
 cname string      {column="cName"};
end

service MyFirstWebService
 
 // Variable Declarations
 variableName string;
 
  function GetCustomerNameFromID(cid int in) returns (string)
    result string;
     result = "not found";
    myCustomer customer;
   
    myCustomer.cid = cid;
       
   get myCustomer with
   #sql{    
    select
     cID, cNumber, cName
    from Customer
    where
     Customer.cid = :myCustomer.cid
  }
  into myCustomer.cid, myCustomer.cnumber, myCustomer.cname;
  
       
    result = myCustomer.cname;
      return(result);
   end
 
 // Function Declarations
 function functionName()
 end

end

 
Publish the Web Service

In order to use the Web Service it must be published first.
Open the MyFirstProject in the Project Explorer
Open the EGLSource folder.
Open the (default package) folder.
Locate the MyFirstWebService and click on it with the right mouse button.
Select EGL Services and choose Create EGL Service Client Binding.
Keep the names default and click on Finish.
The Web Service is now published.

 
Calling the functions from the Button_onClick Event
myService MyFirstWebService{};
call myService.GetCustomerNameFromID(TextField.text)
returning to myCallback onException myExceptionHandler;


*Where TextField.text is a customer number 
 
Running the app
Now you can go to the preview tab and enter a customer number in the TextField and click on the Button.
The Web Service should now return a customer name.
 

// RUI Handler

import com.ibm.egl.rui.widgets.Box;
import com.ibm.egl.rui.widgets.TextField;
import com.ibm.egl.rui.widgets.Button;

//
//
handler MyFirstRichUI type RUIhandler {initialUI = [ ui ],onConstructionFunction = start, cssFile="css/MyFirstProject.css"}
 
 ui Box{ columns = 2, width = "400", height = "400", children = [ TextField, Button ] };
 TextField TextField{};
 Button Button{ text="Button", onClick ::= Button_onClick };
 
 function start()
 end
 
 Function myExceptionHandler(exp AnyException in)
   case
    when (exp isa ServiceBindingException)
     TextField.Text = exp.message;
    ;
    when (exp isa ServiceInvocationException)
     TextField.Text = exp.message;
    ;
    otherwise
     TextField.Text = exp.message;
    ;
   end
 end
 
 Function myCallBack(result string IN)
  TextField.Text = result;
 end

 
 function Button_onClick(event Event in)
  myService MyFirstWebService{};
  call myService.GetCustomerNameFromID(TextField.text)
  returning to myCallback onException myExceptionHandler;

 end 
end

 
www.programmingsystems.eu