Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Using XSQL Servlet, 24 of 24

## Frequently Asked Questions (FAQs) - XSQL Servlet

### NoClassDefFoundError When Running XSQL Servlet Demos

#### Question

I downloaded the lastest version of the XSQL Servlet and the XML Parser for Java off of Technet and configured per the instructions. On both Java Web Server and JRun from Allaire, I get the following error (this one happens to be from JRun). As far as I know, I have the CLASSPATH set correctly, the xsql extension mapped to the servlet, etc.

    (Running servlet) java.lang.NoClassDefFoundError:
    oracle/xml/parser/v2/XSLException at
    oracle.xml.xsql.XSQLServlet.doGet(XSQLServlet.java:118) at
    javax.servlet.http.HttpServlet.service(HttpServlet.java:715)  at
    javax.servlet.http.HttpServlet.service(HttpServlet.java:840) at
    com.livesoftware.jrun.JRun.runServlet(JRun.java, Compiled Code) at
    com.livesoftware.jrun.JRunGeneric.handleConnection(JRunGeneric.java:116) at
    com.livesoftware.jrun.service.web.JRunWebServiceHandler.handleOutput(JRunWeb
    ServiceHandler.java:266) at
    com.livesoftware.jrun.service.web.JRunWebServiceHandler.handleRequest(JRunWebSer
    viceHandler.java,
    Compiled Code) at
    com.livesoftware.jrun.service.ThreadConfigHandler.run(ThreadConfigHandler.java,
    Compiled Code)

#### Answer

This is an error that the xmlparserv2.jar is not in your server side classpath. Double check that you've edited the CLASSPATH info in the right place. With JRun I do this from the JRun Admin application by...

1. On the (General) tab...

2. Click on the (Java) "sub"tab...

3. Enter the following into the "Java ClassPath" box (all on one line...)

       E:/xsql/lib/xmlparserv2.jar;
       E:/xsql/lib/oraclexmlsql.jar;
       E:/xsql/lib/oraclexsql.jar;
       E:/xsql/lib/classes111.zip;
       E:/xsql/lib;... etc ...

### Specifying a DTD While Transforming XSQL Output to a WML Document

#### Question

I am trying to create a demo using XML's XSQL demo (downloaded from OTN). It works fine with Apache server. Now I am trying to write my own stylesheet for transforming XSQL output to WML and VML format. These programs (mobile phone simulators) need a WML document with a specific DTD assigned.

Is there any way, I can specify a particular DTD while transforming XSQL's output to a WML document.

#### Answer

Sure. The way you do it is using a built-in facility of the XSLT Stylesheet called <xsl:output/>

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output type="xml" doctype-system="your.dtd"/>
      <xsl:template match="/">
      </xsl:template>
         :
         :
    </xsl:stylesheet>

This will produce an XML result with a:

<!DOCTYPE xxxx SYSTEM "your.dtd">

in the result. "your.dtd" can be any valid absolute or relative URL.

### XSQL: Using the CURSOR Operator for Nested Structure

#### Question

1. The generated code has a top-level Element "<EMPLOYEES>". I want to get rid of it as per out requirements.

2. How can I specify id_attribute and id-attribute-column for the inner nested query e.g. <myEmployee myEmployee_id="1001">

Currently I am using "include-xsql" and manual deletion as a work-around. I believe most of the XSDs getting out of Oracle will be complex having nested structures. Just to give you an idea, I am working on an XSD such as the following:

      Budget Worksheet
        Positions
          Elements
            Element Distributions
          FTE Distributions
        Accounts

Though work-arounds are available using XSLT, include-xsql, and so on, they involve additional work. Will XSQL support it?

#### Answer

1. The generated code has a top-level Element "<EMPLOYEES>". I want to get rid of it as per out requirements.

   Unfortunately, today we do not support renaming columns or attributes or hiding them directly with the utility. The only way to do it is to use an XSLT stylesheet (simple stylesheet which simply removes the EMPLOYEES tag).

2. How can I specify id_attribute and id-attribute-column for the inner nested query e.g. <myEmployee myEmployee_id="1001">

Again, we do not support this yet. You can do it with XSLT.

### XSQL Servlet Conditional Statements

#### Question

Is it possible to write conditional statements in a .xsql file, and if yes what is the syntax to do that?

For example:

    <xsql:choose>
        <xsql:when test="@security='admin'">
          <xsql:query>
              SELECT ....
          </xsql:query>
       </xsq:when>
       <xsql:when test="@security='user'">
          <xsql:query>
              SELECT ....
          </xsql:query>
       </xsql:when>
    ...
    </xsql:if>

#### Answer

- I'd rather use <xsql:dml> to call a PLSQL procedure that would make the test. The statement you're describing look like xsl statement, used to process an already completed XML document. As far as I understand your problem, I would use a PLSQL procedure calling dbms_sql to generate the right statement, and perhaps go for a ref cursor.

- Right now, no, these are not supported. The plan for post-1.0.0.0 release is to integrate XSQL action handlers with XSLT so that you will be able to mix and match <xsql:query>, for example with XSLT tags like <xsl:choose> instead of copying all of XSLT's functionality with XSQL equivalents like <xsql:choose>.

We want to blend the best of both worlds and it will happen in a forthcoming release. For now, you can use the <xsql:ref-cursor-function> tag and make the conditional decision in the PL/SQL that returns the REF CURSOR.

### Multiple Query in an .xsql File: Page Parameters

#### Question

I have two queries in an .xsql file.

    <xsql:query>
       select col1,col2
       from table1
    </xsql:query>
    <xsql:query>
       select col3,col4 from table2
       where col3 = {@col1}    => the value of col1 in the previous query
    </xsql:query>

How can I use, in the second query, the value of a select list item of the first query?

#### Answer

You do this with page parameters.

    <page xmlns:xsql="urn:oracle-xsql" connection="demo">
      <!-- Value of page param "xxx" will be first column of first row -->
      <xsql:set-page-param name="xxx">
        select one from table1 where ...
      </xsl:set-param-param>
      <xsql:query>
         select col3,col4 from table2
        where col3 = {@xxx}
      </xsql:query>
    </page>

### XSQL, URN, xsl:script

#### Question

Using XSQL Servlet, I receive parameters, and I need to keep them safe for further use, in a few pages to be reached later. My idea is to keep them as hidden fields or the equivalent. I want to build an original XML document that looks like the following:

    <root xmlns:xsql blah blah blah>
      <toBeTurnedAsForm action="doThis" method="POST">
        <aHiddenField name="secret" value="{@IGotYou}"/>
        ....
      </toBeTurnedAsForm>
    </root>

As the <aHiddenField> tag does not belong to the XSQL name space, the parsing of the variable is not done. Is there a simple way to do it? I would not like to have a select '{@IGotYou}' from dual to make.

What does "urn" mean in the xmlns:xsql?

#### Answer

Use <xsql:include-param name="IGotYou"/> to include the value of a parameter in your datapage, then your XSL stylesheet can transform the:

<IGotYou>ValueOfIGotYou</IGotYou>

into wherever it needs to go in your HTML page.

urn stands for "Uniform Resource Name".

### XSQL Servlet: Connecting to Any Database With JDBC Support

#### Question

Can the XSQL Servlet connect to any DB that has JDBC support.

#### Answer

Currently the XSQL Servlet is hardcoded for a connection to use OracleConnection class. We are testing a new version which would connect to any database.

### XSQL Demos: Using the Correct Version JDK

#### Question

I'm trying to run the XSQL demo on Java WebServer. I have the classpath set and have mapped the .xsql extension to the XSQLServlet. When I go to the demo page and click on any of the links that point to an XSQL file, I get the following error message:

    XSQL-001: Cannot locate requested XSQL file. Check the name.

I guess it is a mapping problem but I have mapped the extension. Also the Java WebServer console has this message: It can find the servlet)oracle.xml.xsql.XSQLServlet: init

#### Answer

Your problem may the location of the XSQL files. On my Apache install they are in ..htdocs/xsql/demo/ and the fully qualified path is properly registered.

I've set my classpath for Java Webserver 2.0 by creating a simple batch file which I used to start up the server. I've also aliased *.xsql to oracle.xml.xsql.XSQLServlet in the Servlet Aliases settings page.

Java Webserver also by default uses port 8080. Can you run any other servlet?

#### Comment

This happens to me too using Apache on Solaris. Some investigation reveals that this happens also when using xsqlcommandLine. Notice that the problem occurs Apache default web user) the command works perfectly under Oracle user or root.

It happens with JDK 1.1.5. JDK 1.1.7 and JDK 1.1.8 seem to work better. Now I have part of the solution but it does not resolve the demo connection, but I'll surely solve this too.

### From XML To Table Creation?

#### Question

I created UNIV table as below. Whenever a new XML template file is created, I should create the new table schema as below:

    CREATE TYPE student_type as object(
       id int,name varchar(20),
       math int,english int);/
    CREATE TYPE department_type as object(
       name varchar(10),
       student student_type);/
    CREATE TYPE college_type AS OBJECT(
       name VARCHAR(50),
       dept department_type);/
    CREATE TABLE univ (college college_type);

Can the tables above be automatically created from the aproprietry XML file? In my project, the XML, DTD file is created by DTD Factory, which is a part of my project, dynamically. I need a solution that creates tables automatically from XML files.

#### Answer

You could use the XSLT processor in our XML Parser to generate the required SQL file for your table creation based upon the XML document.

#### Question 2

I need to create tables to import existing XML document. How can the table be automatically generated based on XML? I have download XML-SQL Utility, so I think I already have the XML Parser. How do I trigger this wonderful magic into action?

#### Answer 2

Download the XSQL Servelet archive as that has a number of demos that include the SQL scripts that map to XML documents. INSCLAIM.sql may be the most applicable as it use object views and several tables and should serve as a good template.

#### Question 3

Insclaim.sql is a far cry from the message which said, "You could use the XSLT processor in our XML Parser to generate the required SQL file for your table creation based upon the XML document."

If Insclaim.sql were created by hand, then it requires someone to create a structure upon structure to mimic the DTD. Are there alternatives?

I am running Microsoft IIS server 4.0 which does not support Java Servlet. Installing an Apache is not an option at this moment due to the time constraint to get a simple prototype done. For example, to import a set of valid XML into a repository and be able to search and apply a stylesheet upon retrieval. What do you suggest?

#### Answer 3

The previous reply about XSLT was based on the assumption that you were looking for something on an on-going basis, not simply something quick. The way to approach an automatic XSLT solution is the following:

- Have an idea of the mapping, hence my insclaim.sql response

- Create a simple SQL INSERT script based upon that mapping, by hand, with dummy data.

- After that works, substitute the XSLT instructions that retrieve your document data for the dummy data, thereby creating the stylesheet.

There is no quick automatic solution at this point because DTD's do not adequately describe the schema. XML Schema support however is forthcoming.

### XSQL Servlet: Access to JServ Process

#### Question

I am running the demo helloworld.xsql. Initially I was getting the following error:

    XSQL-007 cannot aquire a database connection to process page

Now my request times out and I see the following message in the jserv/log/jserv.log file:

    Connections from Localhost/127.0.0.1 are not allowed

Is this a security issue? Do we have to give explicit permission to process an .xsql page. If so, how do we do that? I am using Apache web server and Apache jserver and Oracle8i as database. I have Oracle client installed and Tnsnames.ora file configured to get database connection. My XSQconnections.xml file is configured correctly.

#### Answer

This looks like a generic JServ problem. You have to make sure that your security.allowedAddresses=property in jserv.properties allows your current host access to the JServ process where Java runs. Can you successfully run *any* JServ Servlet?

#### Comment

It works now. The JVM was not cleaning up properly when I stop Apache serv. After restarting every thing the XSQL servlet worked fine.

### Calling xmlgen via XSQL Servlet on Java Web Server

#### Question

Now I want to call the xmlgen utility through my servlet which resides on Java Web Server 2.0. Can this be possible? Can I directly call the stored procedure executing the query which returns the XML document and directly host it on the browser through the servlet. If this is to be done should the browser be IE5.0

#### Answer

This is precisely what the XSQL servlet does. It is a servlet which uses the XML Parser for Java,V2 and the XML- SQL Utility to generate XML pages for SQL queries. Try using the XSQL servlet.

You can directly call the client side version of the XML-SQL Utility and the XML parser directly as well. If you are executing stored procedures which return the XML as a string, that would work as well.

The browser need not be IE5.0. The difference is that in IE5.0 if you supply a .xml file it would apply a default stylesheet and display it nicely. In other browsers this would only appear as a text file. If you apply XSL processing to the XML document and transform it in to HTML, then you can display it correctly on any browser.

### Supporting Other Databases

#### Question

Does XSQL Servlet support SQL Server database? How about the other databases besides Oracle?

#### Answer

XSQL Servlet supports any database with a JDBC driver.

### XSQL Servlet: Connecting to Remote Database

#### Question

I have successfully got the XSQL Servlet running on my NT system (Oracle8i, Apache Web Server, IE5) but I cannot run it under Solaris. My Solaris system does not as yet have Oracle8i installed and therefore the XSQL Servlet needs to connect to the remote Oracle8i on my NT machine - and I think this is where the problem lies.

I have installed on the Solaris system Oracle 8.0.5, Apache 1.6.3, ApacheJserv1.0, and the UNIX installation of XSQL Servlet.

The test of the ApacheJserv works correctly. The error messages I get when trying to access the xsql_servlet demo pages are:

    In the err_log file...[Wed Aug  4 11:21:20 1999] [notice] Apache/1.3.6 (Unix)
    ApacheJServ/1.0 configured -- resuming normal operationsCould not establish the
    JDBC connection:java.sql.SQLException: No more data to read from socketand in
    the mod_jserv.log file....[04/08/1999 12:03:55:536] (ERROR) ajp11: Servlet
    Error: java.lang.NullPointerException: null[04/08/1999 12:03:55:536] (ERROR) an
    error returned handling request via protocol "ajpv11"

My XSQLConnections.xml file contains the following:

    ...  scott    tiger    jdbc:oracle:thin:@pc2929:1521:peter        xmldemo
    xmldemo    jdbc:oracle:thin:@pc2929:1521:peter

and I have also tried setting the dburl to:

    jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=pc2929)(port=1521))(
    source_route=yes)(connect_data=(sid=peter)))

The connection to the remote database (peter) functions correctly if I use SQL*PLUS from the Sun. What could be wrong? Is it simply that the database must be on the same machine as the WEB server?

#### Answer

It is designed to work anywhere JDBC can connect to. It definitely does not require the database to be on the same machine as the web server. But, it *does* require that you can successfully establish a JDBC connection using the JDBC driver that it finds in the Apache JServ's CLASSPATH environment, given the connect information in the XSQLConnections.xml file.

Most problems crop up because the CLASSPATH for Jserv does not properly include the JDBC classes111.zip.

I'd suggest the following:

1. Try a JDBC sample program with the connect string you have in XSQLConnections.xml to make sure you can connect at all

2. Check the JServ classpath for the correct inclusion of the latest Oracle JDBC driver archive file.

#### Comment

The problem was solved by installing the newest 8.05 JDBC libraries on the Solaris machine.

### Apache JServ

#### Question

I've set up Apache (1.3.9) + JServ + XSQL Servlet as explained in the XSQL servlet release notes. When I try to run: http://127.0.0.1/xsql/demo/helloworld.xsqlI see the following entry in mod_jserv.log :[11/10/1999 20:25:47:383] (ERROR) ajp12: Servlet Error: ClassNotFoundException: oracle.xml.xsql.XSQLServlet

Does anybody know why JServ cannot find the class above? 'IsItWorking' example seems to work ok, so Apache is talking to JServ.

    relevant (?) stuff from jserv.properties:wrapper.bin=c:\jdk1.2.2\bin\java.exe
    wrapper.classpath=C:\Program Files\Apache Group\Apache
    JServ\ApacheJServ.jarwrapper.classpath=c:\jsdk2.0\lib\jsdk.jarwrapper.classpath=
    C:\xsql\lib\classes111.zipwrapper.classpath=C:\xsql\lib\xmlparser.jarwrapper.cla
    sspath=C:\xsql\lib\oraclexmlsql.jarrelevant (?) stuff from mod_
    jserv.conf:ApJServMount /servlets /rootApJServAction .xsql
    /servlets/oracle.xml.xsql.XSQLServlet

#### Answer

    wrapper.classpath=c:\jsdk2.0\lib\jsdk.jar: :
    wrapper.classpath=C:\xsql\lib\classes111.zip: :
    wrapper.classpath=C:\xsql\lib\xmlparser.jar: :
    wrapper.classpath=C:\xsql\lib\oraclexmlsql.jarYou are
    missing:wrapper.classpath=C:\xsql\lib\oraclexsql.jar

This is the jar file for XSQL Servlet itself.

### Document Creation

#### Question

When I install XSQL Servlet and run the query it should create a XML document on the fly. How the document is being created. As far as my understanding goes we need to hardcode the document. Explain me how it's being created automatically. In that case where do I keep the DTD. I am not seeing any XML documents embedded with DTD. Where does the parser come to picture? Do I need to install any tools to create the documents.

    SELECT salesperson, SUM(sales) AS Total
       FROM sales
       WHERE sale_date between '01-JAN-99' and '30-JUN-99'
       GROUP BY salesperson
    SELECT salesperson, SUM(sales) AS Total
    FROM sales
           WHERE sale_date between '01-JUL-99' and '31-DEC-99'
         GROUP BY salesperson

which would produce results like:

**** How is this being created automatically?****

     Steve        23465500                    Mark        39983400
     Steve        67788400                    Mark        55786990

#### Answer

XSQL Page Processor (used by the XSQL Servlet) processes your page looking for tags. Whenever it finds one, it creates a new instance of an OracleXMLQueryobject (part of the Oracle XML SQL Utility for Java, also downloadable separately) and asks *that* utility to produce the XML for the currentquery. Everything you need to run XSQL Servlet comes bundled with it in it's distribution and it's release notes details the libraries it depends on.

### XSQL with JRUN

#### Question

I'm trying to use XSQL with JRUN on IIS4.0 and getting:XSQL-007: Cannot acquire a database connection to process pageConnection refused(DESCRIPTION=(TMP=)(VSNUM=135286784)......when I execute: xsql emp.xsqlI've tested my JDBC with samples in Oracle8i's JDBC directory and all seems to working fine. I've also tested my JRUN environment running other servlets. I'm also able to connect using SQLW.It maybe the XSQLConnections.xml file. I have it at:jsm-default\services\jws\htdocs\with the startup index.html file. I'm also using JRUN's default 8000 port if that matters.Is there anything else I should have done or I could test to ensure that XSQL can connect to Oracle8i?

#### Answer

It's definitely finding the XSQLConnections.xml(you would have gotten a different error if that were the case), it's just failing to get the JDBC connection using the connection info in your file.What JDBC driver are you using in your XSQLConnections.xml file? If it's not the "thin" driver, are the appropriate paths do necessary DLL's setup correctly?

### XSQL on Oracle8i Lite

#### Question

I am trying to use XSQL with Oracle8i Lite (Win 98) and Apache/JServ Webserver. I am getting error message "no oljdbc40 in java.library.path" even though I have set the olite40.jar in my classpath (which contains the POLJDBC driver). Is there anything extra I need to do to run XSQL for Oracle8i Lite.

#### Answer

You must include the following instruction in your

     .\jserv\conf\jserv.properties file:wrapper.path=C:\orant\bin

where C:\orant\bin is the directory where (by default) the OLJDBC40.DLL lives.

*NOTE* this is *NOT* wrapper.classpath,it's wrapper.path.

### xsql:ora-uri tag

#### Question

I was just viewing XSQL demo codes and came across tag which I can't find in the release note.Could you tell me what it does and its attributes?Thanks,H.Ozawa

#### Answer

<xsql:ora-uri>

is one of several demonstrations of the extensible action elements that will be available in the 0.9.8.3 release of Oracle XSQL Servlet.It is an undocumented test tag in thisrelease.As you have been helping us test the pre-release version you already have the upcoming release in house.For others, it will be available shortly, with lots of new functionality.

### Multiple Parameters in the XSQL Servlet

#### Question

I have a question about XSQL Servlet.Is there any way to handle multiple <form> parameters with the one same name, which is needed for <input type="checkbox">?

#### Answer

XSQL is just a shell to send SQL request. You'll still need to write a logic to convert HTML to request to SQL.

I'll probably use the NAME attribute in INPUT tag to differentiate selections and use it in the WHERE clause.

### XSQL Servlet and Oracle 7.3

#### Question

Is there anything that prevents me from running the XSQL Servlet with Oracle 7.3? I know the XML SQL Utility can be used with Oracle 7.3 as long as I use it as a client-side utility.

#### Answer

Nothing inherent in the XSQL Servlet prevents it from working against 7.3.You just need to make sure: - Your servlet runner environment has an appropriate JDBC driver in the CLASSPATH (if the default 8.1.5 driver doesn't work, which it should), and - That your XSQLConnections.xml file properly reflects the <dburl> that you need to connect to the DB you want to.

### Passing Parameters Between XSQL and XSL

#### Question

I am trying to bring up a form p2.xsql with n no. of records based on a value of (@pname) passed from form p1.xsql. Now say this returns more than 100 records but I am viewing only 10 at a time with previous and next tags, on the click of next I want to fetch the another set of 10 records from the list for @pname, But I am not able to pass the value of pname here, how can I do the same, say on the href of <next> I want to pass pname as a name value parameter.

#### Answer

Did you try using 'max-rows' and 'skip-rows '?Set 'max-rows' to 10 and keep @pname and variables for 'skip-rows' as a global or static variable in your client side program.You'll just have to increase or decrease the value of 'skip-rows' variable which you will be passing to your servlet.

The latest release contains several new features that make this easier, but in release 0.9.6.2, the simplest way to get a parameter value into your datapage is to do:

    <query rowset-element="" row-element="">
    select '{@pname}' as pname from dual
    </query>

### Conditional Query

#### Question

Is it possible for me to put any conditional query in the .xsql file itself?

For example: If I have three parameters, depending on what is populated, I want the query string to be formed. Is it possible for example to state,

    if(p1=NULL) <qurey1>.....</query1>
    if (p2=NULL) <query1>.....</query2>

and so on?

#### Answer

A better way is to do a selection on a client side using Javascript. XSQL is not a language so it is very limited in terms of selection and looping.

Are the three different choices radically different, or slight variations of WHERE clauses against the same table? If the latter, you can do the following:

    SELECT ....  FROM TABLE  WHERE ( ({@p1}='yes') and (...something...))

or

    ( {{@p2}='yes') and (...something else...))

### XSQL Servlet and INSERTs or UPDATEs

#### Question

Can we perform DML operations using XSQL servlet? We can select from tables and present it in XML or HTML format. Can we do an Insert or Update using the servlet?

#### Answer

From release 0.9.8.6 XSQL Servlet supports this, along with other new enhancements, using the <xsql:dml> tag.

### XSQL Servlet and Dynamic SQL

#### Question

Does XSQL Servlet support dynamic SQL? Take a stock screening page as an example. In this example you want to dynamically build the where clause based on user input. Thus if a Minimum PE Ratio of 3 was entered, the where clause of "where PE_Ratio >= 3" would be appended to the query. In this case there may be up to 20 different parameters or more to be mapped dynamically, so it wouldn't be feasible to nest all of the different combinations.

#### Answer

XSQL supports lexical substitution parameters so any and every part of any query can be parameterized. The extreme case is:<query> {@sql} </query>, where the entire query is passed in a parameter (named "sql"). But any combination of parameters and substitutions is legal and can be used to do what you want. Since the variables are not *BIND*variabled, but instead lexical substitution variables, you can do things like:

    <query>
      select {@collist} from {@table} where {@where}
      order by {@orderby}
    </query>

You can provide default parameter values using XML attributes on the <query> element which then can be overridden if a value is passed in the request

    ...<query collist="ename, sal"
         orderby="sal desc" where="1=1"       from="dept">
         select {@collist}     from {@table}     where {@where}
         order by {@orderby}
       </query>

And then a request to the page can pass in a orderby=decode(comm,null,1,0) or something to override the defaults.

### XSQL Servlet and Other Relational Databases

#### Question

Does XSQL Servlets interact with other relational databases?

#### Answer

Yes. You need to provide the following:

- Appropriate JDBC driver in your CLASSPATH

- Appropriate <driver> and <dburl> settings for the connection in your XSQLConnections.xml file

### Out Variable Not Supported in <XSQL:DML>

#### Question

I'm trying to determine the use of <xsql:dml>. I tried calling a stored procedure which has one out parameter, but I was not able to see any results. The executed code resulted in the following statement:

    <xsql-status action="xsql:dml" rows="0"/>.

#### Answer

OUT variables are not supported in this release with <xsql:dml>, just IN parameters. This is because the {@param} variables in XSQL are *lexical* variables and not bind variables.Tell me more about what you're trying to do with the OUT value and I can likely suggest a different approach to achieve the same result. Are you trying to return an OUT value of a stored procedure into your XSQL "datapage"?

For an example, see: ./xsql/test/dml-test.xsql in the latest release.

#### Question 2

That what I'm doing....getting the out variable value onto the XSQL page? Is there a work around?

#### Answer 2

I think the simplest workaround would be to wrap your procedure with a function and then use an <xsql:query> to SELECT the function value as part of a SELECT statement.

So, assume your procedure looked like:

    procedure foo( y number, x out number );

You can build a function that looks like the following:

    function foowrapper( y number) return number is x number;
    begin foo(y,x);
    return x;
    end;.

Then use an <xsql:query> action element to do the following:

    <xsql:query>  select foowrapper({@yparam}) from dual</xsql:query>

### Running XSQL Page Processor on Java Web Server?

#### Question

Can XSQL page processor servlet run on JavaWeb Server? The installation notes say that it has been tested with Apache web server. If I can run on Java Web Server, are there any pointers?

#### Answer

Since XSQL is a servlet, it can be run on any servlet engine and that includes Java Web Server. Although I haven't worked on Java Web Server, I think there must be a mechanism to associate a handler for .xsql pages to the XSQL servlet. Once this is done I think Java Web Server should take XSQL requests.

The Release Notes document what web servers we've tested it with, although any Servlet Engine that's faithful to the Servlet specification should do.

Sun's Java Server Web Development Kit (JSWDK 1.0) is one of the servers we've tested it with. The only basic steps to installation (whose details might differ for *your* servlet engine) are:

- Make sure all of the following zips, archives, directories are in your servlet engine's CLASSPATH:

  - ./xsql/lib/xmlparserv2.jar

  - ./xsql/lib/oraclexmlsql.jar

  - ./xsql/lib/oraclexsql.jar

  - ./xsql/lib/classes111.zip

  - ./xsql/lib

- Map the extension .xsql to the Servlet class oracle.xml.xsql.XSQLServlet

### SID and JDK Errors

#### Question

Don't know where exactly to put the config file - using Websphere 3. Also, I get the following error after 'setting everything up' and trying to access the helloworld.xsql:

    Oracle XSQL Servlet Page Processor 0.9.8.6 (Technology Preview)XSQL-007: Cannot
    acquire a database connection to process page.Connection
    refused(DESCRIPTION=(TMP=)(VSNNUM=135286784)(ERR=12505)(ERROR_
    STACK=(ERROR=(CODE=12505)(EMFI=4))))

Does this mean that it has actually found the config file? I have a user with scott/tiger setup.

#### Answer

Yes. If you get this far, it's actually attempting the JDBC connection based on the <connectiondef> info for the connection named "demo", assuming you didn't modify the HelloWorld.xsql demo page.

By default the XSQLConfig.xml file comes with the entry for the "demo"connection that looks like this:

    <connection name="demo">
          <username>scott</username>
          <password>tiger</password>
          <dburl>jdbc racle:thin:@localhost:1521:ORCL</dburl>
    <driver>oracle.jdbc.driver.OracleDriver</driver>
        </connection>

So the error you're getting is likely because:

1. Your database is not on "localhost" machine.

2. Your database SID is not ORCL

3. Your TNS Listener Port is not 1521

Make sure those values are appropriate for your database and you should be in business.

#### Question 2

Good stuff it now sort of works. The SID was wrong.

### Using Custom Action Elements: XSQLActionHandlerImpl

#### Question

Can I pass parameters to my Java class which extends XSQLActionHandlerImpl.

Where is there extensive documentation for XSQL?

#### Answer

The Release Notes and the JavaDoc fully document how to use the custom Action Elements. The distribution comes with source code for two sample Action Elements, too.

./xsql/src/oracle/xml/xsql/actions/ExampleGetParameterHandler.java ./xsql/src/oracle/xml/xsql/actions/GetCurrentDBDateHandler.java

These illustrate how an Action Element uses its init() method to pick up parameters from any attributes or nested elements of the action element in the XSQL Page and how they write output to the resulting page. You shouldn't have a need to pass parameters to the Action Element Handler, any information should be picked up from attributes or elements in the XSQL Page.

For example:

    <xsql:action handler="my.pkg.SampleHandler"    arg1="foo" arg2="bar">
        <statement> 1,2,3,4,5 </statement>
     </xsql:action>

Your action handler can use standardDOM techniques in its init() method (or make use of some of the helper methods provided in XSQLActionHandlerImpl like getAttributeAllowingParam() to pickup parametrized values to drive the action.

### XSQL Servlet: Writing an Action Element (Handler) to Set a Browser Cookie

#### Question

There is an application, where some users login, and do some operations, say some inserts/updates, whenever there is any such DML statement performed on the database, except of course SELECT, I want a field called the user_id to be updated with the user who had done it and also the time it was changed.

My application is basic, a set of .xsql files. My webserver is Apache Web Server. I am using Java Servlet (jrun). I have the latest XSQL Servlet set up.

Where is such an array of structures with user profiles, like user_id/name, ip address of connect from, and so on is stored. For example, is an environmental variable set somewhere?

#### Answer

If you look at environment variables, they will be the ones on the web server machine and likely not what you want. When the user logs-in to your application, you could write a small XSQL custom action element to set a browser cookie to the value of their username, and then refer to that cookie value for the value of USER_ID in your database inserts.

#### Question 2

I think storing it on the cookie and using it will be perfect. I do not know how to set the browser cookie, is there any an example to which I could refer?

I found couple of things such as, set Cookie:Name=....., and something on owa_Cookie. Which should I use with XSQL?

#### Answer 2

You'll need an action handler that does the following:

    :import javax.servlet.http.*;
    :XSQLServletPageRequest xspr = (XSQLServletPageRequest)getPageRequest();
    if (xspr.getRequestType().equals("Servlet"))
     {
      HttpServletResponse resp  = xspr.getHttpServletResponse();
    Cookie newCookie = new Cookie("name","value");
    resp.addCookie(newCookie);
    }
       }

### Installing XSQL Servlet on IIS with JRun

#### Question

My web server is IIS with JRun. How do I install the XSQL servlet?

#### Answer

Ensure, using the appropriate place in the JRun administration utility, that you have the following in your servlet engine classpath. Assuming you've installed XSQL in E:

    \ ...
    E:\xsql\lib\classes111.zip
    E:\xsql\lib\oraclexmlsql.jar
    E:\xsql\lib\oraclexsql.jar
    E:\xsql\lib\xmlparserv2.jar
    E:\xsql\lib

Ensure that you make the .xsql extension to the oracle.xml.xsql.XSQLServlet servlet class.

### Writing an XSQL Action Handler to Acquire HTTP Request Parameters

#### Question

I am trying to make this work by either using cookie and or by using the ENV structure stored at the browser end. With the cookies I am clear how to set and get values. I want this option in case somebody tries to disable cookie on their browser and it may not work. I want the environment variables to be available. How can this be done?

Using a Perl script, I can do a get of the ENV structure/array and use the same. How can I get the same functionality with XML and XSQL?

#### Answer

I don't understand how checking the system environment variables of the *server* machine can function as a fallback for a cookie to identify the user. At best, they would identify the user name of the user that is running the web server executable, not the current user logged into the browser.

If you can access the information you're looking for in Java then you can write an XSQL Action Handler to retrieve it. There's nothing built-in to handle this.

If by "environmental variables from the browser" you simply mean HTTP request parameters, then you can look at the example Action Handler provided with XSQL that illustrates how to get the HTTP request parameters for an idea of how to implement something similar.

### Converting HTML Key Value Pairs to XML: <xsql:insert-request>

#### Question

How do I use the <xsql:insert-request> tag? I want to convert the key value pairs in a HTML form to XML. I understand that using XSQL Servlet and <xsql:insert-request> make it easier to do.

I need to save the converted XML file in a directory. Here is the form:

    <html>
    <body>  Insert a new news story...  <form action="form.xsql" method="post">
    <b>Title</b>
    <input type="text" name="title_field" size="30">
    <br>
        <b>URL</b>
    <input type="text" name="url_field" size="30">
    <br>    <br>    <input type="submit">
      </form>
    <body>
    </html>

and here is the form.xsql (I am stuck here!)

     <?xml version = '1.0'?>
    <xsql:insert-request-params transform=????/>

How does one just save the converted XML file? Can you suggest how to pass on the XML file to another servlet for processing, either saving it or without saving it?

#### Answer

<xsql:insert-request> is designed to insert the posted XML document (or posted HTML form parameters synthesized into an XML document) into the database. If you want to save it as a file in the server, then you can write a custom action handler (see the Release Notes) which calls:

    getPageRequest().getPostedDocument()

And then writes it to a file by creating a new FileWriter()

Should be just a few lines of Java. The hardest part will be deciding what file name you want to give to the file.

### Running a Procedure from the .xsql File

#### Question

Here is the error message:

    - <test>- <xsql-error action="xsql:include-owa">  <statement>declare buf
    htp.htbuf_arr; param_names owa.vc_arr; param_values owa.vc_arr; rows integer :=
    32767; outclob CLOB;begin param_names(1) := 'HTTP_COOKIE'; param_values
    (1) := ''; param_names(2) := 'SERVER_NAME'; param_values
    (2) := 'shanthi.ijs.com'; param_names
    ...

My .xsql file is:

    <test connection="system" xmlns:xsql="urn:oracle-xsql">
    <
    xsql:include-owa>
    scott.get_date;<
    /xsql:include-owa><
    /test>

#### Answer

Seems like your DBMS_LOB package is either not installed or not installed correctly. Try re-running DBMSLOB.SQL in ./rdbms/admin of your Oracle installation.

### XSQL File Launching from JDeveloper

#### Question

Is it possible to use the WebToGo that comes with JDev481 to test XSQL files. If so how what kind of configuration do I need to do.

#### Answer

This is the .bat file that I use "wtg.bat". It sets up the WebToGo webserver with the jar's for Oracle Lite, WebToGo, and the XSQL Servlet.

This assumes you have JDeveloper installed in J:\

The System property settings are so that the JDK URL classes can "see through" firewalls properly (for example, when parsing an XML document which has a DTD whose SYSTEM identifier is http://somesiteoutside.com/foo.dtd)

    @echo off
    set WTGCLASSPATH=j:\java\lib\classes.zip
    set WTGCLASSPATH=%WTGCLASSPATH%;C:\orant\lite\classes\oljdk11.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;C:\orant\lite\classes\olite40.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;J:\lib\webtogo.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;J:\lib\xmlparser.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;J:\lib\classgen.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;J:\lib\servlet.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;J:\lib\ojsp.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;c:\xsql\lib
    set WTGCLASSPATH=%WTGCLASSPATH%;c:\xsql\lib\classes111.zip
    set WTGCLASSPATH=%WTGCLASSPATH%;c:\xsql\lib\oraclexsql.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;c:\xsql\lib\oraclexmlsql.jar
    set WTGCLASSPATH=%WTGCLASSPATH%;c:\xsql\lib\xmlparserv2.jar

    echo %WTGCLASSPATH%
    jre -DproxySet=true -DproxyHost=www-proxy.us.oracle.com -DproxyPort=80 -clas
    spath %WTGCLASSPATH% oracle.lite.web.JupServer

This is my J:\lib\webtogo.ora file:

Notice the ROOT_DIR line and the xsql= line.

    [WEBTOGO]
    PORT = 7070
    USE_SYSTEM_CLASSPATH=YES
    DEBUG=YES

    [FILESYSTEM]
    TYPE=OS
    ROOT_DIR=C:\

    [MIMES]
    html=text/html
    jsp=text/html;handler=oracle.jsp.runner.JspRunner
    xsql=text/html;handler=oracle.xml.xsql.XSQLServlet

    [APPLICATIONS]
    xmlfile=.\wtgapp.xml

    [SERVLET_PARAMETERS]

### Can I Load Multiple Forms to My Database Using XML?

#### Question

I'm currently running Oracle Forms on the web. Will XML allow me to create a real-time interface whereby I can load multiple records into my Oracle database without going through a form?

#### Answer

Sure. Check out our XSQL Servlet at: http://technet.oracle.com/tech/xml/xsql_servlet. It comes with features, documentation, and demos to easily work with SQL, XML, and XSLT, including the ability to help in posting HTML Forms and posted XML documents into the database.

Try out the demos online at: http://technet.oracle.com/tech/xml/demo/demo1.htm

In particular the "XML Insert Request Demo" shows a simple example of what can be done by posting XML to your database.

### Getting and Storing XML Documents in a Database: Testing Functionality

#### Question

I downloaded the XML-SQL Utility, XSQL Servlet, Apache Webserver, Apache Jserv, and so on onto Windows NT. I installed them according to respective installation instructions. How do I test the functionality to get and store XML documents into database? And how should I test that all the above mentioned software installed are communicating correctly. I am using Oracle8 client on NT and Oracle8i server on Sun Solaris.

#### Answer

XSQL Servlet comes with a number of demonstrations to help test if it is properly installed. The release notes explain how to set them up. You can see the exact same set of demos at: http://technet.oracle.com/tech/xml/demo/demo1.htm To compare results.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index