In this section: |
You can generate request document schemas using iWay Explorer, as described in Creating an SQL Statement and Generating Schemas. You can generate request document instances using a third party XML tool and submit those documents to the RDBMS or iWay agent.
The following topics include examples of schemas and instance documents for:
The following examples are based on schemas created for a regular SQL statement.
<?xml version="1.0" encoding="UTF-8"?> <!-- Generated by the iBSE 2005-04-28T22:18:27Z --> <xs:schema targetNamespace="urn:iwaysoftware:ibse:feb2004:Request" xmlns:m1="urn:iwaysoftware:ibse:feb2004:Request" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:element name="AdapterParams"> <xs:complexType> <xs:attribute name="location" type="xs:string" use="optional" default="RDBMS/Statements/test1"/> </xs:complexType> </xs:element> </xs:schema>
<!--Sample XML file generated by XMLSPY v5 rel. 4 U (http://www.xmlspy.com)--> <AdapterParams xmlns="urn:iwaysoftware:ibse:feb2004:Request" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iwaysoftware:ibse:feb2004:Request C:\temp\SP3\test1_request.xsd" location="RDBMS/Statements/test1"/>
<?xml version="1.0" encoding="UTF-8"?> <!-- Generated by the iBSE 2005-04-28T22:18:27Z --> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="RESULT"> <xsd:complexType> <xsd:sequence> <xsd:element name="test1"> <xsd:complexType> <xsd:sequence> <xsd:element name="RESULTSET_1" minOccurs="0"> <xsd:complexType> <xsd:sequence> <xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="CHAR_" type="xsd:string"/> <xsd:element name="VARCHAR2_5" type="xsd:string"/> <xsd:element name="DATE_TIME" type="xsd:dateTime"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<?xml version="1.0 encoding+"UT-8?> <RESULT xmlns:xsi="http://www.w3.org/2005/XMLSchema-instance" xsi:noNamespaceSchemaLocation="D:\iway\RDBMS\stock price select_response.xsd" <stock_price_select <RESULTSET_1> <ROW> <CHAR_>xxxxx</CHAR_> <VARCHAR2_5>1111</VARCHAR2_5> <DATE_TIME>1978-09-22T00:00:00Z</DATE_TIME> </ROW> <ROW> <CHAR_>bob</CHAR_> <VARCHAR2_5>hit</VARCHAR2_5> <DATE_TIME>2005-04-05T08:34:23Z</DATE_TIME> </ROW> <ROW> <CHAR_>NEW</CHAR_> <VARCHAR2_5>NEW</VARCHAR2_5> <DATE_TIME>1978-09-22T00:00:00Z</DATE_TIME> </ROW> <ROW> <CHAR_>NEW</CHAR_> <VARCHAR2_5>NEW</VARCHAR2_5> <DATE_TIME>1978-09-22T00:00:00Z</DATE_TIME> </ROW> </RESULTSET_1> </test1> </RESULT>
The following examples are based on schemas created for a parameterized SQL statement.
<?xml version="1.0" encoding="UTF-8"?> <!-- Generated by the iBSE 2005-04-28T22:35:12Z --> <xs:schema targetNamespace="urn:iwaysoftware:ibse:feb2004:Request" xmlns:m1="urn:iwaysoftware:ibse:feb2004:Request" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:element name="AdapterParams"> <xs:complexType> <xs:sequence> <xs:element name="param0" type="xs:string" minOccurs="0"/> </xs:sequence> <xs:attribute name="location" type="xs:string" use="optional" default="RDBMS/Statements/test4"/> </xs:complexType> </xs:element> </xs:schema>
<?xml version="1.0" encoding="UTF-8"?> <!--Sample XML file generated by XMLSPY v5 rel. 4 U (http://www.xmlspy.com)--> <AdapterParams xmlns="urn:iwaysoftware:ibse:feb2004:Request" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iwaysoftware:ibse:feb2004:Request \\g4d7001\temp\sp3\test4.xsd" location="RDBMS/Statements/test4"> <param0>john</param0> </AdapterParams>
<?xml version="1.0" encoding="UTF-8"?> <!-- Generated by the iBSE 2005-04-28T22:35:12Z --> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="RESULT"> <xsd:complexType> <xsd:sequence> <xsd:element name="test4"> <xsd:complexType> <xsd:sequence> <xsd:element name="RESULTSET_1" minOccurs="0"> <xsd:complexType> <xsd:sequence> <xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="FNAME" type="xsd:string"/> <xsd:element name="MINIT" type="xsd:string"/> <xsd:element name="LNAME" type="xsd:string"/> <xsd:element name="SSN" type="xsd:double"/> <xsd:element name="BDATE" type="xsd:dateTime"/> <xsd:element name="ADDRESS" type="xsd:string"/> <xsd:element name="SEX" type="xsd:string"/> <xsd:element name="SALARY" type="xsd:double"/> <xsd:element name="SUPERSSN" type="xsd:double"/> <xsd:element name="DNO" type="xsd:double"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<RESULT> <test4> <RESULTSET_1> <ROW> <FNAME>JOHN</FNAME> <MINIT>B</MINIT> <LNAME>SMITH</LNAME> <SSN>123456789</SSN> <BDATE>1955-01-09T00:00:00Z</BDATE> <ADDRESS>731 FONDREN, HOUSTON, TX</ADDRESS> <SEX>M</SEX> <SALARY>330000</SALARY> <SUPERSSN>333445555</SUPERSSN> <DNO>5</DNO> </ROW> <ROW> <FNAME>JOHN</FNAME> <MINIT>B</MINIT> <LNAME>SMITH</LNAME> <SSN>123456789</SSN> <BDATE>2005-01-09T00:00:00Z</BDATE> <ADDRESS>731 FONDREN, HOUSTON, TX</ADDRESS> <SEX>M</SEX> <SALARY>30000</SALARY> <SUPERSSN>333445555</SUPERSSN> <DNO>5</DNO> </ROW> <ROW> <FNAME>JOHN</FNAME> <MINIT>B</MINIT> <LNAME>SMITH</LNAME> <SSN>123456789</SSN> <BDATE>2005-01-09T00:00:00Z</BDATE> <ADDRESS>731 FONDREN, HOUSTON, TX</ADDRESS> <SEX>M</SEX> <SALARY>30000</SALARY> <SUPERSSN>333445555</SUPERSSN> <DNO>5</DNO> </ROW> </RESULTSET_1> </test4> </RESULT>
The iWay Technology Adapter for RDBMS includes extensive table functions that simplify the ability to update and query a table by creating commonly used prepared statements.
These are basic SQL statements that allow the user to execute basic SQL functionality. These table functions are predefined and cannot be edited. For more complex queries, you can write your own SQL under SQL statements. SQL update functions may not appear in the list of available functions if the table does not have a primary key.
When you create Web services from a table function, the adapter builds the SQL request and incorporates it into the Web service. You can also export the schema and use it to generate XML instance request documents.
Existing APIs into which these functions can fit are the J2EE design pattern data access object (DAO), Java data objects (JDOs), and J2EE entity beans, all of which abstract and encapsulate access to a data source.
The functions include the following:
CURSOR is a query function that allows you to scroll through a result set without having an open cursor within the adapter.
For detailed instructions on creating and testing iWay Business Services, see Understanding iWay Business Services. If you are using Swing iWay Explorer you can also Export the Schema by right-clicking the node in which you are interested and selecting Export Schemas. When using the JSP version of iWay Explorer, you can generate schemas by moving the mouse over Operations and selecting Generate Schema. You can use the schema to create instance XML request documents.
The Web service or schema that is created incorporates the SQL statement for the CURSOR function. This function requires five parameters. Only ROW_COUNT and ROW_REFERENCE require a value. The parameters are listed and defined in the table below.
Note: If ROW_COUNT is not provided in the input XML document, all records will be retrieved.
Parameter |
Description |
---|---|
ROW_COUNT (required) |
The number of rows that you want the function to return. If you a supply a value of -1, all rows will be returned. |
ORDERBY_COLUMN |
The list of columns for the table. It is an enumeration and can have only values in its enumeration list. The column name passed to this parameter sets the “order by” clause in the dynamic statement generated by this function. |
ROW_REFERENCE (required) |
The row and all of its values from which the returned result set starts or ends. The function dynamically creates a select statement and determines the next set of rows to be sent based on the parameters sent. |
ASCENDING |
The boolean input parameter that determines if the “order by” is ascending or descending. |
NEXT |
The boolean input parameter that determines if the reult returned is the next or previous set of rows. |
Using Test Run for the CURSOR function provides an opportunity to use the function. To use Test Run for the CURSOR function:
The Test Run information appears in the right pane.
The result appears in the right pane.
The additional table functions provided by the iWay Technology Adapter for RDBMS provide a standard way to store, update, and retrieve data from any database. The GET, INSERT, UPDATE, DELETE, and UPSERT functions operate on a single row at a time. The COUNT, AVERAGE, MIN, MAX, and SUM functions operate on a single table at a time.
Note: UPDATE functions are not displayed unless the table contains a primary key.
To use the additional table functions:
All the table functions can be used to create Web services and to export schemas to create instance XML request documents. You can review the SQL statement in the right pane when you select one of the functions. The following image shows the Count function selected and the SQL statement displayed in the right pane.
The following table lists and describes the available table functions.
Function |
Description |
---|---|
GET |
Retrieves one row at a time. |
INSERT |
Inserts one row into a table. |
UPDATE |
Updates non-primary keys and operates on a single row at a time. The primary keys sent in the request are used to populate the where clause in the SQL statement. Updating primary keys is logically equivalent to deleting the row by primary key and then inserting a new row with a new ID with the same non-primary key values. This logic fits the DAO, JDO, or EJB frameworks while simply updating the primary keys does not. |
DELETE |
Deletes one row at a time. The parameters are the primary keys. |
UPSERT |
Combines the INSERT and UPDATE functions. This function checks if a row already exists. If it does not exist, the request is forwarded to the INSERT function; this is determined by performing a count query with the table’s primary keys as part of the where clause, as shown in the properties in the right pane. |
COUNT |
The COUNT table function is used to return the number of entries in a table. The query does not accept any input parameters. |
AVERAGE |
This query executes an avg() function on a table and accepts an input parameter representing the column name. The JDBC API is not used to set the question mark in the statement. This process recreates the statement string replacing the question mark with the value in the input parameter in the request. This is performed before the statement is prepared. |
MAX |
This query executes a max() function on a table and accepts an input parameter representing the column name. |
MIN |
This query executes a min() function on a table and accepts an input parameter representing the column name. |
SUM |
This query executes a sum() function on a table and takes in an input parameter representing the column name. |
You can also use Search to locate data quickly.
The following is an example of schema for a batch statement.
<?xml version="1.0" encoding="UTF-8" ?> - <!-- Generated by the iBSE 2009-01-22T17:15:24Z --> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="RDBMS/Batches/Any:55012SP2:Request"> - <xsd:element name="RDBMS"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="BATCH"> - <xsd:complexType> - <xsd:sequence> <xsd:any namespace="##any" maxOccurs="unbounded" /> </xsd:sequence> <xsd:attribute type="xsd:string" use="required" fixed="RDBMS/Batches/Any:55012SP2" name="location" /> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<?xml version="1.0" encoding="UTF-8" ?> - <!-- Generated by the iBSE 2009-01-22T17:15:24Z --> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="RDBMS/Batches/Any:55012SP2:Response"> - <xsd:element name="RESULTS"> - <xsd:complexType> - <xsd:sequence> <xsd:any namespace="##any" maxOccurs="unbounded" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<?xml version="1.0" encoding="UTF-8" ?> - <!-- Generated by the iBSE 2009-01-09T20:27:51Z --> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="RDBMS/Batches/EditBatch:55012SP2:Request"> - <xsd:element name="RDBMS"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="BATCH"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="x_insert_char"> - <xsd:complexType> - <xsd:sequence> <xsd:element type="xsd:string" name="param0" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute type="xsd:string" use="required" fixed="RDBMS/Batches/EditBatch:55012SP2" name="location" /> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<?xml version="1.0" encoding="UTF-8" ?> - <!-- Generated by the iBSE 2009-01-09T20:27:51Z --> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="RDBMS/Batches/EditBatch:55012SP2:Response"> - <xsd:element name="RESULTS"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="x_insert_char"> - <xsd:complexType> - <xsd:sequence> <xsd:element type="xsd:integer" name="COUNT" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<?xml version="1.0" encoding="UTF-8" ?> - <!-- Generated by the iBSE 2009-01-09T20:29:01Z --> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="RDBMS/Batches/EditBatchIter:55012SP2:Request"> - <xsd:element name="RDBMS"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="BATCH"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="x_insert_char"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="PARAMS" maxOccurs="unbounded"> - <xsd:complexType> - <xsd:sequence> <xsd:element type="xsd:string" name="param0" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute type="xsd:string" use="required" fixed="RDBMS/Batches/EditBatchIter:55012SP2" name="location" /> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<?xml version="1.0" encoding="UTF-8" ?> - <!-- Generated by the iBSE 2009-01-09T20:29:01Z --> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="RDBMS/Batches/EditBatchIter:55012SP2:Response"> - <xsd:element name="RESULTS"> - <xsd:complexType> - <xsd:sequence> - <xsd:element name="x_insert_char" maxOccurs="unbounded"> - <xsd:complexType> - <xsd:sequence> <xsd:element type="xsd:integer" name="COUNT" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
<?xml version="1.0" encoding="UTF-8" ?> - <!-- Sample XML file generated by XMLSpy v2005 rel. 3 U (http://www.altova.com)--> - <RDBMS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\schemas\ORA015_request.xsd"> - <BATCH location="RDBMS/Batches/ORA015"> <ORA015A/> <ORA015B/> <ORA015C/> </BATCH> </RDBMS>
<?xml version="1.0" encoding="UTF-8"?> <!--Sample XML file generated by XMLSpy v2008 rel. 2 (http://www.altova.com)--> <RDBMS xmlns="RDBMS/Batches/Any:55012SP2:Request"> <BATCH xmlns="" location="RDBMS/Batches/TestAny:55012SP2"> <AdapterParams location="RDBMS/Statements/x_insert_char"> <param0>f</param0> </AdapterParams> <AdapterParams location="RDBMS/Statements/x_insert_char"> <param0>g</param0> </AdapterParams> <AdapterParams location="RDBMS/Schemas/SCOTT/Procedures/Packages/MY_PKG/raisePrice"> <prod>Soup</prod> <times>2</times> </AdapterParams> <AdapterParams location="RDBMS/Schemas/SCOTT/Tables/CHARS/INSERT"> <FLDA>a</FLDA> </AdapterParams> </BATCH> </RDBMS>
The following examples are based on schemas created for stored procedures.
Note: Only positional parameters are supported for Legacy Stored Procedures (FEX).
<?xml version="1.0" encoding="UTF-8"?> <!-- Generated by the iBSE 2004-01-13T22:05:56Z --> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:element name="RPCIn"> <xs:complexType> <xs:sequence> <xs:element name="1" type="xs:string"/> </xs:sequence> <xs:attribute name="name" type="xs:string" use="optional" default="RPCVSM"/> </xs:complexType> </xs:element> </xs:schema>
<?xml version="1.0" encoding="UTF-8"?> <RPCIn xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="D:\iway\RDBMS\RPCVSM_request.xsd" name="RPCVSM"> <1>String</1> </RPCIn>
<?xml version="1.0" encoding="UTF-8"?> <!-- Generated by the iBSE 2004-01-13T22:05:56Z --> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:element name="RPCOut"> <xs:complexType> <xs:sequence> <xs:element name="Row" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="COMP_NAME" type="xs:string"/> <xs:element name="EMP_ID" type="xs:string"/> <xs:element name="EMPID" type="xs:string"/> <xs:element name="FIRST_NAME" type="xs:string"/> <xs:element name="LAST_NAME" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="status" type="xs:string" use="required"/> <xs:attribute name="reason" type="xs:string" use="required"/> </xs:complexType> </xs:element> </xs:schema>
<?xml version="1.0" encoding="UTF-8"?> <RPCOut xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="D:\iway\RDBMS\RPCVSM_response.xsd" status="String" reason="String"> <Row> <COMP_NAME>String</COMP_NAME> <EMP_ID>String</EMP_ID> <EMPID>String</EMPID> <FIRST_NAME>String</FIRST_NAME> <LAST_NAME>String</LAST_NAME> </Row> <Row> <COMP_NAME>String</COMP_NAME> <EMP_ID>String</EMP_ID> <EMPID>String</EMPID> <FIRST_NAME>String</FIRST_NAME> <LAST_NAME>String</LAST_NAME> </Row> <Row> <COMP_NAME>String</COMP_NAME> <EMP_ID>String</EMP_ID> <EMPID>String</EMPID> <FIRST_NAME>String</FIRST_NAME> <LAST_NAME>String</LAST_NAME> </Row> </RPCOut>
iWay Software |