From OpenLaszlo
Here is the two jsp. Because Oracle XSU deals with elements, in Laszlo we convert every tablerow-elements
into nodes with attributes and vica-versa.
This is the ToOracle jsp:
<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ page language="java" import="java.util.*"%>
<%@ page language="java" import="java.lang.*"%>
<%@ page language="java" import="java.sql.*"%>
<%@ page language="java" import="oracle.xml.sql.dml.OracleXMLSave"%>
<%
// Create connection
Connection connection = null;
String xmlString = "";
try {
String action = request.getParameter("action");
int actionInt = 0;
if (action.equals("update"))
{
actionInt = 1;
}
if (action.equals("delete"))
{
actionInt = 2;
}
String tableName = request.getParameter("tableName");
String tableKeys = request.getParameter("tableKeys");
String curXML = request.getParameter("curXML");
String oraHostName = request.getParameter("oraHostName");
String dbName = request.getParameter("dbName");
String usrName = request.getParameter("usrName");
String usrPwd = request.getParameter("usrPwd");
if (oraHostName == null || oraHostName == "") {
oraHostName = "Hostname";
}
if (dbName == null || dbName == ""){
dbName = "DataBaseName";
}
if (usrName == null || usrName == ""){
usrName = "UserName";
}
if (usrPwd == null || usrPwd == ""){
usrPwd = "Password";
}
Class.forName("oracle.jdbc.driver.OracleDriver");
String conStr = "jdbc:oracle:thin:@" + oraHostName + ":1521:"+ dbName;
connection = DriverManager.getConnection(conStr, usrName, usrPwd);
if (curXML != null && curXML != "")
{
// curXML = "<?xml version='1.0' encoding='utf-8'?>"+ "\n" + curXML;
System.out.println("curXML: " + curXML);
OracleXMLSave sav = new OracleXMLSave(connection, tableName);
// load columns of compound key into array keyColumns
// from string tableKeys with format: key1 key2 key3 ... keyN
// ...start
String[] keyColumns;
int rows=0;
int pStart = 0;
int pEnd = 0;
boolean ok = true;
while( (tableKeys.length()> 0) && ok)
{
pEnd = tableKeys.indexOf(" ", pStart);
if (pEnd < 0)
{
ok = false;
}
pStart = pEnd + 1;
rows++;
}
keyColumns = new String[rows];
ok = true;
rows = 0;
pStart = 0;
while( (tableKeys.length()> 0) && ok)
{
pEnd = tableKeys.indexOf(" ", pStart);
if (pEnd < 0)
{
ok = false;
pEnd = tableKeys.length();
}
String sor = tableKeys.substring(pStart, pEnd);
pStart = pEnd + 1;
keyColumns[rows] = sor;
rows++;
}
// ...end
switch (actionInt)
{
case 0:
sav.insertXML(curXML);
break;
case 1:
sav.setKeyColumnList(keyColumns);
sav.updateXML(curXML);
break;
case 2:
sav.setKeyColumnList(keyColumns);
sav.deleteXML(curXML);
break;
default:
}
xmlString = "<ROWSET><ROW>OK</ROW></ROWSET>";
}
}
catch (SQLException se) {
se.printStackTrace();
xmlString = "<ROWSET><ROW><![CDATA[" + se.getMessage()+ "]]></ROW></ROWSET>";
}
catch (Exception e) {
e.printStackTrace();
xmlString = "<ROWSET><ROW><![CDATA[" + e.getMessage()+ "]]></ROW></ROWSET>";
} finally {
try {
connection.close();
} catch (SQLException e) {
}
}
%>
<%=xmlString%>
This is the FromOracle jsp
<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ page language="java" import="java.util.*"%>
<%@ page language="java" import="java.io.*" %>
<%@ page language="java" import="java.lang.*"%>
<%@ page language="java" import="java.sql.*" %>
<%@ page language="java" import="oracle.xml.sql.query.OracleXMLQuery" %>
<%
// Create connection
Connection connection = null;
String xmlString = "";
try {
request.setCharacterEncoding("UTF-8");
String querySrc = request.getParameter("sqlQuerySrc"); // get an sql statement used as a parameter to OracleXMLQuery
String oraHostName = request.getParameter("oraHostName");
String tableName = request.getParameter("tableName");
String dbName = request.getParameter("dbName");
String usrName = request.getParameter("usrName");
String usrPwd = request.getParameter("usrPwd");
if (oraHostName == null || oraHostName == "") {
oraHostName = "HostName";
}
if (dbName == null || dbName == ""){
dbName = "DatabaseName";
}
if (usrName == null || usrName == ""){
usrName = "UserName";
}
if (usrPwd == null || usrPwd == ""){
usrPwd = "Password";
}
Class.forName("oracle.jdbc.driver.OracleDriver"); // get Connection to the Oracle database
String conStr = "jdbc:oracle:thin:@" + oraHostName + ":1521:"+ dbName;
connection = DriverManager.getConnection(conStr, usrName, usrPwd);
OracleXMLQuery qry = new OracleXMLQuery(connection, querySrc);
qry.setEncoding("UTF-8");
qry.setRowIdAttrName("");
xmlString = qry.getXMLString();
int xml_sor_vege = xmlString.indexOf("?>");
if (xml_sor_vege != -1)
xmlString = xmlString.substring(xml_sor_vege+2, xmlString.length());
}
catch (Exception e) {
xmlString = "ERROR: " + e.getMessage();
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
}
}
%>
<%=xmlString%>
