Using SQL ICE for Web Services Integration to Enterprise Relational Databases

Business Integration Technology has just released SQL ICE, the Internet Computing Environment for SQL Experts. Although SQL ICE can do a lot of things that may not be of interest to EDI/Integration developers, SQL ICE makes it extremely easy to expose data in relational databases as a Web service for integration purposes. All you need to do is add a Connection to the target database and write a query in SQL. The data is exposed as an XML document accessed by a REST Web service. For example, a simple query like:

Select
  max(Customer_Code) as "Customer Code",
  max(Company_Name) as "Company Name",
  max(Invoice_Number) as "Invoice Number",
  max(Invoice_Date) as "Invoice Date",
  max(Due_Date) as "Due Date",
  sum(Charge.Total_Cost) as "Amount"
from Customer, Invoice
left outer join Charge on Charge.Invoice_ID = Invoice.ID
and Charge.Customer_ID = Invoice.Customer_ID
where Customer.ID = Invoice.Customer_ID
and Customer.Customer_Code = :code
group by Customer.Customer_Code, Invoice.Invoice_Number 

may be accessed from a URL like:

http://demo.sqlice.com/sqlice/app/query/GetCustomerInvoices/output/XML?code=BIT

(note: this URL actually works. This query is live and runs on our demo Web site. The (non-privileged) user login is username:notadmin, password Notadmin1. Please contact us if you would like an Admin login.)

It returns an XML doc like:

<?xml version="1.0" encoding="UTF-8" ?>
-   <query name="DEMO GET CUSTOMER INVOICES" resultSize="2">
-     <row number="1">
        <column name="Amount" value="2051.90" type="BigDecimal" />
        <column name="Company_Name" value="Business Integration Technology" type="String" />
        <column name="Customer_Code" value="BIT" type="String" />
        <column name="Due_Date" value="2011-01-04" type="Date" />
        <column name="Invoice_Date" value="2011-01-04" type="Date" />
        <column name="Invoice_Number" value="1" type="String" />
      </row>
-     <row number="2">
        <column name="Amount" value="1006.45" type="BigDecimal" />
        <column name="Company_Name" value="Business Integration Technology" type="String" />
        <column name="Customer_Code" value="BIT" type="String" />
        <column name="Due_Date" value="2011-02-18" type="Date" />
        <column name="Invoice_Date" value="2011-01-04" type="Date" />
        <column name="Invoice_Number" value="2" type="String" />
      </row>
   </query>

Hook up your favorite EDI translator or BIT's EME, and you can get data from enterprise databases very easily.

SQL ICE is available at http://SQLICE.com. There is no charge and no restrictions on usage. A very simple name/email registration is required.