//***************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
//
// The following sample of source code ("Sample") is owned by International
// Business Machines Corporation or one of its subsidiaries ("IBM") and is
// copyrighted and licensed, not sold. You may use, copy, modify, and
// distribute the Sample in any form without payment to IBM, for the purpose of
// assisting you in the development of your applications.
//
// The Sample code is provided to you on an "AS IS" basis, without warranty of
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
// not allow for the exclusion or limitation of implied warranties, so the above
// limitations or exclusions may not apply to you. IBM shall not be liable for
// any damages you suffer as a result of using, copying, modifying or
// distributing the Sample, even if IBM has been advised of the possibility of
// such damages.
//***************************************************************************
//
// SOURCE FILE NAME: XmlInsert.sqlj
//
// SAMPLE: How to insert XML data into a table
//
// SQL Statements USED:
// SELECT
// INSERT
//
// Classes used from Util.sqlj are:
// Db
// Data
// SqljException
//
// PREQUISITES : 1. copy the files cust1021.xml, cust1022.xml and
// cust1023.xml to working directory
// 2. Create the pre-requisite tables by running the command:
// XmlInsertScrpt
// Alternatively,you can run the command:
// db2 -tvf XmlInsert_setup.db2
//
// EXECUTION : 1. Compile the sample using:
// bldsqlj XmlInsert <userid> <password> <server_name> <port_number> <db_name>
// 2. Run the sample as:
// java XmlInsert
// 3. Perform a clean up once the sample has been executed using:
// db2 -tvf XmlInsert_cleanup.db2
//
//
// Output will vary depending on the JDBC driver connectivity used.
//***************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
// http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/
import java.lang.*;
import java.sql.*;
import java.util.*;
import java.io.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator TbXMLinsert_cursor1(int, String);
class XmlInsert
{
public static void main(String argv[])
{
try
{
Db db = new Db(argv);
System.out.println();
System.out.println(
"THIS SAMPLE SHOWS HOW TO INSERT XML TABLE DATA.");
// connect to the 'sample' database
db.getDefaultContext();
preRequisites();
mostSimpleInsert();
InsertFromAnotherXmlColumn();
InsertFromAnotherStringColumn();
InsertwhereSourceisXmlFunction();
InsertwhereSourceisBlob();
InsertwhereSourceisClob();
InsertBlobDataWithImplicitParsing();
InsertFromStringNotWellFormedXML();
InsertwhereSourceisTypecastToXML();
InsertwithValidationSourceisVarchar();
ValidateXMLDocument();
DeleteofRowwithXmlData();
// disconnect from the 'sample' database
db.disconnect();
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
}// main
static void mostSimpleInsert()
{
try
{
int customerid = 0;
String customerInfo = "";
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM A SIMPLE INSERT.");
// display the content of the 'customer' table
CustomerTbContentDisplay(1006);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" VALUES(1006,XMLPARSE(document "+
" '<customerinfo Cid=\"1006\"><name>divya" +
" </name></customerinfo>' preserve whitespace))\n" +
" \n");
#sql {
INSERT INTO customer(cid,info) VALUES(1006,XMLPARSE(document
'<customerinfo Cid="1006"><name>divya</name>
</customerinfo>' preserve whitespace))};
// display the content of the 'customer' table
CustomerTbContentDisplay(1006);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
}//mostSimpleInsert
static void InsertFromAnotherXmlColumn()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS FROM ANOTHER XML COLUMN.");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1007);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" SELECT ocid,information FROM oldcustomer p "+
" WHERE p.ocid=1007\n" +
"\n");
#sql {
INSERT INTO customer(cid,info)
SELECT ocid,information
FROM oldcustomer p
WHERE p.ocid=1007};
// display the content of the 'customer' table
CustomerTbContentDisplay(1007);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} // InsertFromAnotherXmlColumn
static void InsertFromAnotherStringColumn()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS FROM ANOTHER STRING COLUMN.");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1008);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" SELECT ocid,XMLPARSE(document addr preserve whitespace) " +
" FROM oldcustomer p " +
" WHERE p.ocid=1008\n" +
" \n");
#sql {
INSERT INTO customer(cid,info)
SELECT ocid,XMLPARSE(document addr preserve whitespace)
FROM oldcustomer p
WHERE p.ocid=1008};
// display the content of the 'customer' table
CustomerTbContentDisplay(1008);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
}//InsertFromAnotherStringColumn
static void InsertAnotherStringWithImplicitParsing()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS FROM " +
"ANOTHER STRING COLUMN WITH IMPLICIT PARSING");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1011);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" SELECT ocid,addr " +
" FROM oldcustomer p " +
" WHERE p.ocid=1011\n" +
" \n");
#sql {
INSERT INTO customer(cid,info)
SELECT ocid,addr
FROM oldcustomer p
WHERE p.ocid=1011};
// display the content of the 'customer' table
CustomerTbContentDisplay(1011);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
}//InsertAnotherStringWithImplicitParsing
static void InsertwithValidationSourceisVarchar()
{
try
{
System.out.println();
System.out.println(
"-------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
" TO PERFORM AN INSERT WITH VALIDATION WHERE " +
" SOURCE IS OF TYPE VARCHAR.");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1009);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" SELECT ocid,XMLVALIDATE(XMLPARSE(document " +
" addr preserve whitespace)according to " +
" XMLSCHEMA id customer) " +
" FROM oldcustomer p " +
" WHERE p.ocid=1009\n" +
" \n");
#sql {
INSERT INTO customer(cid,info)
SELECT ocid,XMLVALIDATE(XMLPARSE(document addr preserve
whitespace)according to
XMLSCHEMA id customer)
FROM oldcustomer p
WHERE p.ocid=1009};
// display the content of the 'customer' table
CustomerTbContentDisplay(1009);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
}//InsertwithValidationSourceisVarchar
static void ValidateXMLDocument()
{
try
{
String xmldata = "XMLPARSE(document '<customerinfo " +
"cid=\"1012\"><address country= " +
"\"india\"><street>12 gandhimarg " +
"</street><city>belgaum</city><state>"+
"karnataka</state></address>" +
"</customerinfo>' preserve whitespace)";
System.out.println();
System.out.println(
"-------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
" TO PERFORM AN INSERT WITH VALIDATION WHEN " +
" DOCUMENT IS NOT AS PER SCHEMA");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1012);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" VALUES (1012, XMLVALIDATE(XMLPARSE(document '<customerinfo"+
" ocid=\"1012\"><address country=\"india\"><street>12 gandhimarg"+
" </street><city>belgaum</city><state>karnataka</state>"+
" </address></customerinfo>' preserve whitespace))"+
" according to XMLSCHEMA ID customer) \n");
#sql {
INSERT INTO customer(cid,info)
VALUES (1012, XMLVALIDATE(:xmldata
according to XMLSCHEMA ID CUSTOMER)) };
// display the content of the 'customer' table
CustomerTbContentDisplay(1012);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
}
catch(Exception e)
{}
} //ValidateXMLDocument
static void InsertwhereSourceisXmlFunction()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS A XML FUNCTION.");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1010);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" SELECT ocid,XMLPARSE(document XMLSERIALIZE" +
" (content XMLELEMENT(NAME\"oldCustomer\", "+
" XMLATTRIBUTES(s.ocid,s.firstname||' '||s." +
" lastname AS \"name\")) " +
" as varchar(200)) strip whitespace) " +
" FROM oldcustomer s " +
" WHERE s.ocid=1010\n" +
"\n");
#sql {
INSERT INTO customer(cid,info)
SELECT ocid,XMLPARSE(document XMLSERIALIZE(content
XMLELEMENT(NAME "oldCustomer",XMLATTRIBUTES(s.ocid,s.
firstname||' '||s.lastname AS "name"))
as varchar(200)) strip whitespace)
FROM oldcustomer s
WHERE s.ocid=1010};
// display the content of the 'customer' table
CustomerTbContentDisplay(1010);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} // InsertwhereSourceisXmlFunction
static void InsertwhereSourceisBlob()
{
try
{
String xsdData = new String();
xsdData=returnFileValues("cust1021.xml");
byte[] byteArray=xsdData.getBytes();
// Create a BLOB object
java.sql.Blob blobData =
com.ibm.db2.jcc.t2zos.DB2LobFactory.createBlob(byteArray);
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS A BLOB VARIABLE.");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1021);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" VALUES(1021,XMLPARSE(document " +
" cast(? as Blob) strip whitespace))\n" +
"\n");
#sql {
INSERT INTO customer(cid,info)
VALUES(1021,XMLPARSE(document cast(:blobData as Blob)
strip whitespace))};
// display the content of the 'customer' table
CustomerTbContentDisplay(1021);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} // InsertwhereSourceisBlob
static void InsertBlobDataWithImplicitParsing()
{
try
{
String xsdData = new String();
xsdData=returnFileValues("cust1022.xml");
byte[] byteArray=xsdData.getBytes();
// Create a BLOB object
java.sql.Blob blobData =
com.ibm.db2.jcc.t2zos.DB2LobFactory.createBlob(byteArray);
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS A BLOB VARIABLE" +
" WITH IMPLICIT PARSING" );
// display the content of the 'customer' table
//CustomerTbContentDisplay(1022);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" VALUES(1022," +
" cast(? as Blob) strip whitespace)\n" +
"\n");
#sql {
INSERT INTO customer(cid,info)
VALUES(1022, :blobData )};
// display the content of the 'customer' table
CustomerTbContentDisplay(1022);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} //InsertBlobDataWithImplicitParsing
static void InsertwhereSourceisClob()
{
try
{
int customerid = 0;
String customerInfo = "";
String xsdData = new String();
xsdData=returnFileValues("cust1023.xml");
// Create a CLOB Object
java.sql.Clob clobData =
com.ibm.db2.jcc.t2zos.DB2LobFactory.createClob(xsdData);
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS A CLOB VARIABLE.");
// display the content of the 'customer' table
// CustomerTbContentDisplay(1023);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" VALUES(1023,XMLPARSE(document " +
" cast(? as Clob) strip whitespace))\n" +
"\n");
#sql {
INSERT INTO customer(cid,info)
VALUES(1023,XMLPARSE(document cast(:clobData as Clob)
strip whitespace))};
// display the content of the 'customer' table
CustomerTbContentDisplay(1023);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} // InsertwhereSourceisClob
static void InsertwhereSourceisTypecastToXML()
{
try
{
int customerid = 0;
String customerInfo = "";
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM AN INSERT WHERE SOURCE IS TYPECAST TO XML.");
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" VALUES(1031,XMLCAST(? AS XML))" +
"\n");
#sql {
INSERT INTO customer(cid,info)
VALUES(1031,XMLCAST(XMLPARSE(document '<oldcustomerinfo ocid=
"1031"><address country="india"><street>56 hillview</street>
<city>kolar</city><state>karnataka</state> </address>
</oldcustomerinfo>' preserve whitespace) as XML))};
//display the content of the 'customer' table
CustomerTbContentDisplay(1031);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} // InsertwhereSourceisTypecastToXML
static void InsertFromStringNotWellFormedXML()
{
try
{
int customerid = 0;
String customerInfo = "";
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO PERFORM INSERT WITH NOT WELL FORMED XML");
// display the content of the 'customer' table
//CustomerTbContentDisplay(1032);
System.out.println();
System.out.println(" Perform:\n" +
" INSERT INTO customer(cid,info)\n" +
" VALUES(1032, "+
" '<customerinfo Cid=\"1032\"><name>divya" +
" </name>')\n" +
" \n");
#sql {
INSERT INTO customer(cid,info) VALUES(1032,
'<customerinfo Cid="1032"><name>divya</name>
' )};
// display the content of the 'customer' table
CustomerTbContentDisplay(1032);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
}
catch(Exception e)
{}
} //InsertFromStringNotWellFormedXML
// helping function
static void preRequisites()
{
try
{
// create table 'oldcustomer'
System.out.println("\nThe table oldcustomer is created in the setup script \n" +
"XmlInsert_setup.db2 using the command \n" +
"'CREATE TABLE oldcustomer(ocid integer, \n" +
" firstname varchar(15), \n" +
" lastname varchar(15), \n" +
" addr varchar(350), \n" +
" information XML)' \n");
// populate table oldcustomer with data
#sql {
INSERT INTO oldcustomer VALUES (1007,'Raghu','nandan',
'<addr country="india">tate>karnataka<district>bangalore
</district></state></addr>',XMLPARSE(document'
<oldcustomerinfo ocid="1007"><address country=
"india"><street>24 gulmarg</street> <city>bangalore
</city><state>karnataka</state></address>
</oldcustomerinfo>'preserve whitespace))};
#sql {
INSERT INTO oldcustomer VALUES(1008,'Rama','murthy','<addr
country="india"><state>karnataka<district>belgaum
</district></state></addr>',XMLPARSE(document'<oldcustomerinfo
ocid="1008"><address country="india"><street>12 gandhimarg
</street><city>belgaum</city><state>karnataka</state>
</address></oldcustomerinfo>'preserve whitespace))};
#sql {
INSERT INTO oldcustomer VALUES(1009,'Rahul','kumar',
'<customerinfo Cid="1009">
<name>Rahul</name><addr country="Canada"><street>25 Westend
</street><city>Markham</city><prov-state>Ontario</prov-state>
<pcode-zip>N9C-3T6</pcode-zip></addr><phone type="work">
905-555-725 8</phone></customerinfo>',XMLPARSE(document
'<oldcustomerinfo ocid="1009"><address country=
"Canada"><street>25 Westend</street><city>Markham</city>
<state>Ontario</state></address></oldcustomerinfo>'
preserve whitespace))};
#sql {
INSERT INTO oldcustomer VALUES(1010,'Sweta','Priya','<addr
country="india"><state>karnataka<district>kolar</district>
</state></addr>', XMLPARSE(document'<oldcustomerinfo ocid=
"1010"><address country="india"><street>56 hillview</street>
<city>kolar</city><state>karnataka</state> </address>
</oldcustomerinfo>'preserve whitespace))};
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} //preRequisites
static void CustomerTbContentDisplay(int Cid)
{
try
{
int customerid = 0;
String customerInfo = "";
System.out.println();
System.out.println(
" SELECT cid,XMLSERIALIZE(info as varchar(600))\n" +
" FROM customer WHERE cid=" + Cid);
TbXMLinsert_cursor1 cur1;
#sql cur1 = {SELECT cid,XMLSERIALIZE(info as varchar(600))
FROM customer WHERE cid = :Cid};
System.out.println(
" CUSTOMERID CUSTOMERINFO \n" +
" ---------- -------------- ");
#sql {FETCH NEXT FROM :cur1 INTO :customerid, :customerInfo};
// retrieve and display the result from the SELECT statement
while (true)
{
if (cur1.endFetch())
{
break;
}
System.out.println(
" " +
Data.format(customerid, 10) + " " +
Data.format(customerInfo, 1024));
#sql {FETCH NEXT FROM :cur1 INTO :customerid, :customerInfo};
}
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} // CustomerTableContentDisplay
static void DeleteofRowwithXmlData()
{
try
{
int customerid = 0;
String customerInfo = "";
System.out.println();
System.out.println(
"---------------------------------------\n\n" +
"USE THE SQL STATEMENT:\n" +
" DELETE\n" +
"TO PERFORM A DELETE OF ROWS WITH XML DATA.");
System.out.println();
System.out.println(" Perform:\n" +
" DELETE FROM customer\n" +
" WHERE cid>=1007 and cid <= 1032\n" +
"\n");
#sql {
DELETE FROM customer
WHERE cid>=1006 and cid <= 1032};
// display the content of the 'customer' table
CustomerTbContentDisplay(1007);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
} // DeleteofRowwithXmlData
// this function will Read a file in a buffer and
// return the String value to called function
public static String returnFileValues(String fileName)
{
String record = null;
try
{
FileReader fr = new FileReader(fileName);
BufferedReader br = new BufferedReader(fr);
record = new String();
record = br.readLine();
String descReturn=record;
while ((record = br.readLine()) != null)
descReturn=descReturn+record;
return descReturn;
}
catch (Exception e)
{
// catch possible io errors from readLine()
System.out.println(" file " + fileName + "doesn't exist");
System.out.println(" Quitting program!");
System.out.println();
System.exit(-1);
}
return null;
}// returnFileValues
}//XmlInsert