SQLJSP Problem

From: Neil <npluckne_at_ford.com>
Date: 21 May 2001 08:45:29 GMT
Message-ID: <Xns90A8633C8E59524361267ford@19.5.30.190>

This is reposted from the 5th as it seems the external feed dropped before delivery. Apologies if it did get through and you've already seen it.

Neil.

Peoples,

Once again, I fear, I have to call upon your collective wisdom. I've been trying to setup some querys on a website that interrogate an 8.1.7 database. Being cute/lazy (delete as applicable..) I took one of the sample files that came with the databse and modified the parameters to suit my needs. Needless to say the one I needed most was a resounding failure!!! The error message returned in the browser window is:

JSP Error:
Request URI:/demo/sql/sqlj/SQLJSelectInto.sqljsp Exception:
oracle.jsp.provider.JspCompileException: SQLJ compilation error

I've attached the code below - if any one out there is familiar with SQLJSP files could you have a look at it please and tell me where I've goofed?

Thanks,

Neil.

Begin code insertion:

<%@ page

import="sqlj.runtime.ref.DefaultContext,oracle.sqlj.runtime.Oracle" %>

<HTML>
<HEAD> <TITLE> Registration Search Facility</TITLE> </HEAD>
<BODY BGCOLOR=white>

   String connStr=request.getParameter("connStr");    if (connStr==null) {
     connStr=(String)session.getValue("connStr");    } else {
  session.putValue("connStr",connStr);
   }
   if (connStr==null) { %>
<jsp:forward page="../setconn.jsp" />
<%

}
%>

String regnum = request.getParameter("regnum"); if (regnum != null) { %>

      <H3> Registration # <%=regnum %> Details: </H3>
      <%= runQuery(connStr,regnum) %>
      <HR><BR>

<% } %>

<B>Enter an Registration number:</B>
<FORM METHOD=get>
<INPUT TYPE="text" NAME="regnum" SIZE=10>
<INPUT TYPE="submit" VALUE="Ask Oracle");
</FORM>
</BODY>
</HTML>
<%!

private String runQuery(String connStr, String regnum) throws java.sql.SQLException {

     DefaultContext dctx = null;
     String regnum = null;  first_name = null;  String last_name = null; 
String cdsid = null; Double phone_ext = 0;
     StringBuffer sb = new StringBuffer();
     try {
	dctx = Oracle.getConnection(connStr, "sys", "avdba");
	#sql [dctx] { SELECT first_name, last_name, cdsid, phone_ext, regnum) 
                      INTO :first_name, :last_name, :cdsid, :phone_ext, 
:regnum
                      FROM sys.evldb WHERE UPPER(regnum) = UPPER(:regnum)
        };
	sb.append("<BLOCKQUOTE><BIG><B><PRE>\n");
     sb.append("First Name       : " + first_name + "\n");
	sb.append("Last Name        : " + last_name + "\n");
	sb.append(CDS-ID		 : " + cdsid + "\n");
	sb.append("Phone            : " + phone_ext + "\n");
	sb.append("Reg Num'r        : " + regnum);
        sb.append("</PRE></B></BIG></BLOCKQUOTE>");

     } catch (java.sql.SQLException e) {
         sb.append("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
     } finally {
         if (dctx!= null) dctx.close();
     }
     return sb.toString();

}
%>

End code insertion. Received on Mon May 21 2001 - 03:45:29 CDT