How To: Create Database Web Services for Exposing DML Statements

Contents

Overview

This example shows you how to publish a DML statement into Web Services. The DML statement, as shown in service-config.xml, is the following:
update emp SET sal=sal+500 where  ename=:{myname VARCHAR}
The expression, :{<variable>  <variable_type>} defines a host variable of a particular SQL type. The expression is used in service-config.xml file to define variables in a DML statement, similarly to the symbol ? used in JDBC SQL statements.

To run this demo you need to ensure that you have installed the required software and set up your environment. In addition, an installation of the Oracle database is required.

Step By Step

Step 1. Prepare the Database

The demo requires the present of the EMP table in the database. The table is created during installtion for the SCOTT schema in Oracle databases. To create the EMP table in a database, run the script ${ORACLE_HOME}/bin/demobld in SQLPLUS.

Step 2. Prepare the OC4J Instance

If the database is not running locally, modify the
J2EE_HOME/data-sources.xml file so that the data-source
"jdbc/OracleDS" points to the running database, for instance,
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="OracleDS"
location="jdbc/OracleCoreDS"
xa-location="jdbc/xa/OracleXADS"
ejb-location="jdbc/OracleDS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="scott"
password="tiger"
url="jdbc:oracle:thin:@<host>:<port>:<sid>"
inactivity-timeout="30"
/>

Step 3. Start OC4J

Go to the J2EE_HOME directory and start the OC4J instance standalone.
java -jar oc4j.jar

Step 4. Prepare service-config.xml

If the database is not running locally, modify the file
service-config.xml, so that the <db-conn/> element points
to the running database, in the format of
jdbc:oracle:thin:@<host>:<port>:<sid>

Step 5. Run the Demo with ANT

Run the command
ant
in the current directory. The last target "run-client" should give the following result:
[java] *** DML Update Return: 1
This step accomplish several tasks including assembling, deploying, binding the application and then running the client. In the next section, Step 5a (Alternative to Step 5), we will detail how this step is implemented with separate commands.

Step 5a (Alternative to Step 5). Run the Demo with Separate Commands

The section provides a detaiedl view of the previous section. One should run either Step 5 or 5a but not both.

Step 5a(1) Assembly

Run the WebServices assembler as following.
java -jar $ORACLE_HOME/webservices/wsa.jar -config service-config.xml
This step publishes queries defined in service-config.xml into Webservices.  The following files will be generated.

Step 5a(2) Deploy

Deploy the generated WebServices application.
java -jar $J2EE_HOME/admin.jar ormi://localhost admin welcome -deploy -file dist/dml.ear -deploymentName dml

Step 5a(3) Bind

Bind the application to http-web-site.
java -jar $J2EE_HOME/admin.jar ormi://localhost admin welcome -bindWebApp dml dml-web http-web-site /dml

Step 5a(4) Generate the Client Proxy

Run the WebServices assembler to generate the client proxy.
java -jar $ORACLE_HOME/webservices/wsa.jar -config client-config.xml
The client proxy will be generated in the subdirectory build/classes/client.

Step 5a(5) Run the Client

The client program, src/client/oracle/demo/db/dml/DMLClient.java, uses the client proxy, generated in Step 5a(4), to invoke the WebServices operations.

Compile and run the client program using the ANT commands:

%ant compile-client
%ant run-client
The target "run-client" will give the result described in Section 5.

Step 6. Access the Services via Web Browser

The services can also be accessed via the URL
http://localhost:8888/dml/dml