Retrieve data with EGL SQLRecord

Submitted by Administrator on Mon, 02/01/2010 - 14:38

Tags:

EGL
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