Thetopics thatfollowcontaininformationaboutwritingSQLJapplications. v “BasicstepsinwritinganSQLJapplication”
v “Connectingtoa datasourceusingSQLJ”onpage92
v “JavapackagesforSQLJsupport”onpage97
v “VariablesinSQLJapplications”onpage98
v “Commentsinan SQLJapplication”onpage99
v “ExecutingSQLstatementsinSQLJapplications”onpage100
v “Workingwith XMLdatainSQLJapplications” onpage133
v “TransactioncontrolinSQLJapplications”onpage137
v “HandlingerrorsandwarningsinSQLJapplications”onpage138
v “Closingtheconnectiontoadatasourceinan SQLJapplication”onpage140
Basic
steps
in
writing
an
SQLJ
application
Writing aSQLJapplicationhasmuchincommonwithwritinganSQLapplication inanyotherlanguage:Ingeneral,youneed todothefollowingthings:
v ImporttheJavapackagesthatcontainSQLJand JDBCmethods.
v Declarevariablesfor sendingdatatoorretrievingdatafromDB2tables.
v Connectto adatasource.
v ExecuteSQLstatements.
v HandleSQLerrorsand warnings.
v Disconnectfromthedatasource.
Althoughthetasksthatyouneedtoperformare similartothoseinother languages,thewaythatyouexecutethosetasks,andtheorderinwhichyou executethosetasks, issomewhat different.
Figure34onpage90isa simpleprogramthatdemonstrateseachtask.
import sqlj.runtime.*; 1
import java.sql.*;
#sql context EzSqljCtx; 3a
#sql iterator EzSqljNameIter (String LASTNAME); 4a
public class EzSqlj {
public static void main(Stringargs[]) throws SQLException
{
EzSqljCtxctx = null;
String URLprefix = "jdbc:db2:"; String url;
url = new String(URLprefix + args[0]);
// Location name is an input parameter String hvmgr="000010"; 2 String hvdeptno="A00"; try { 3b Class.forName("com.ibm.db2.jcc.DB2Driver"); } catch (Exception e) {
throw new SQLException("Error in EzSqlj: Could not load the driver"); }
try {
System.out.println("Aboutto connect using url: " + url);
Connection con0 = DriverManager.getConnection(url); 3c // Create a JDBC Connection con0.setAutoCommit(false); // set autocommit OFF ctx = new EzSqljCtx(con0); 3d try { EzSqljNameIter iter; int count=0; #sql [ctx] iter =
{SELECT LASTNAME FROM EMPLOYEE}; 4b // Create result table of the SELECT while (iter.next()) { 4c System.out.println(iter.LASTNAME()); // Retrieve rows from result table count++;
}
System.out.println("Retrieved " + count + " rows of data"); }
NotestoFigure34onpage90:
Note Description
1 Thesestatementsimportthejava.sqlpackage,whichcontainstheJDBCcore API,andthesqlj.runtimepackage,whichcontainstheSQLJAPI.For informationonotherpackagesorclassesthatyoumightneedtoaccess,see AccessJavapackagesforSQLJsupport.
2 Stringvariableshvmgrandhvdeptnoarehostidentifiers,whichareequivalent toDB2hostvariables.SeeDeclarevariablesinSQLJapplicationsformore information.
3a,3b, 3c,and 3d
Thesestatementsdemonstratehowtoconnecttoadatasourceusingoneofthe threeavailabletechniques.SeeConnecttoadatasourceusingSQLJformore details.
catch( SQLException e ) 5 {
System.out.println ("**** SELECT SQLException..."); while(e!=null) {
System.out.println ("Error msg: " + e.getMessage()); System.out.println ("SQLSTATE: " + e.getSQLState()); System.out.println ("Error code: " + e.getErrorCode()); e = e.getNextException(); // Check for chained exceptions }
}
catch( Exception e ) {
System.out.println("**** NON-SQL exception
= " + e); e.printStackTrace(); } try { #sql [ctx] 4d {UPDATE DEPARTMENTSET MGRNO=:hvmgr
WHERE DEPTNO=:hvdeptno};
// Update data for one department 6 #sql [ctx] {COMMIT}; // Commit the update
}
catch( SQLException e ) {
System.out.println ("**** UPDATE SQLException...");
System.out.println ("Error msg: " + e.getMessage() + ". SQLSTATE="+ e.getSQLState() + " Error code=" + e.getErrorCode());
e.printStackTrace(); }
catch( Exception e ) {
System.out.println("**** NON-SQL exception = " + e); e.printStackTrace();
}
iter.close(); // Close the iterator ctx.close(); 7 }
catch(SQLException e) {
System.out.println ("**** SQLException ...");
System.out.println ("Error msg: " + e.getMessage() + ". SQLSTATE=" + e.getSQLState() + " Error code=" + e.getErrorCode());
e.printStackTrace(); }
catch(Exception e) {
System.out.println ("**** NON-SQL exception= " + e); e.printStackTrace();
}
}
Note Description
4a, 4b,4c, and4d
ThesestatementsdemonstratehowtoexecuteSQLstatementsinSQLJ. Statement4ademonstratestheSQLJequivalentofdeclaringanSQLcursor. Statements4band4cshowonewayofdoingtheSQLJequivalentofexecuting SQLFETCHes.Statement4dshowshowtodotheSQLJequivalentof
performinganSQLUPDATE.Formoreinformation,seeExecuteSQLinan SQLJapplication.
5 Thistry/catchblockdemonstratestheuseoftheSQLExceptionclassforSQL errorhandling.FormoreinformationonhandlingSQLerrors,seeHandleerrors inanSQLJapplication.FormoreinformationonhandlingSQLwarnings,see HandleSQLwarningsinanSQLJapplication.
6 Thisisanexampleofacomment.ForrulesonincludingcommentsinSQLJ programs,seeIncludecommentsinanSQLJapplication.
7 Thisstatementclosestheconnectiontothedatasource.SeeClosethe connectiontothedatasourceinanSQLJapplication.
Related concepts:
v “JavapackagesforSQLJsupport”onpage97
v “VariablesinSQLJapplications”onpage98
v “SQLstatementsinan SQLJapplication”onpage100
Related tasks:
v “Connectingtoa datasourceusingSQLJ”onpage92
Connecting
to
a
data
source
using
SQLJ
InanSQLJapplication,asinanyotherDB2 application,youmust beconnectedto a databaseserverbefore youcanexecuteSQLstatements.InSQLJ, asinJDBC,a database serveriscalleda datasource.
Youcanuseoneofthefollowingtechniques toconnecttoadatasource.
Connectiontechnique1:ThistechniqueusestheJDBCDriverManagerasthe underlyingmeansforcreating theconnection.Useit withanyleveloftheJDBC driver.
1. ExecuteanSQLJconnectiondeclarationclause.
Doingthis generatesaconnection contextclass.Thesimplestformofthe connectiondeclaration clauseis:
#sql context context-class-name;
Thenameofthegeneratedconnectioncontextclassiscontext-class-name.
2. Loada JDBCdriverbyinvoking theClass.forNamemethod:
v For theIBM DB2DriverforJDBC andSQLJ, invokeClass.forNamethisway: Class.forName("com.ibm.db2.jcc.DB2Driver");
v For theDB2 JDBCType2 Driver,invokeClass.forNamethisway: Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
3. Invoketheconstructorfortheconnectioncontextclassthatyoucreatedinstep
1.
Doingthis createsaconnectioncontext objectthatyouspecifyineachSQL statementthatyouexecuteattheassociateddatasource.Theconstructor invocationstatementneedstobe inoneofthefollowingforms:
connection-context-class connection-context-object=
new connection-context-class(String url, boolean autocommit);
connection-context-class connection-context-object=
new connection-context-class(String url, String user, String password, boolean autocommit);
connection-context-class connection-context-object=
new connection-context-class(String url, Properties info, boolean autocommit);
Themeaningsof theparametersare:
url Astringthatspecifies thelocationnamethatisassociatedwith thedata source.Thatargumenthasoneoftheformsthatare specifiedinConnectto a datasourceusingtheDriverManagerinterfacewiththeIBMDB2 Driver forJDBCand SQLJ.Theform dependsonwhichJDBCdriveryouare using.
userandpassword
Specifya userIDandpasswordforconnectiontothedatasource,if the datasourcetowhichyouare connectingrequiresthem.
info
Specifiesanobjectoftypejava.util.Propertiesthatcontainsa setof driverpropertiesfortheconnection.For theDB2 JDBCType2 Driverfor Linux,UNIXandWindows(DB2 JDBCType2 Driver),youshouldspecify onlytheuserand passwordproperties. FortheIBMDB2Driver forJDBC andSQLJ, youcanspecifyanyof thepropertieslistedinPropertiesforthe IBMDB2 DriverforJDBCand SQLJ.
autocommit
Specifieswhetheryouwantthedatabasemanagertoissuea COMMITafter everystatement. Possiblevaluesare trueorfalse.Ifyouspecifyfalse, youneedtodoexplicit commitoperations.
Thefollowingcodeusesconnectiontechnique 1tocreateaconnection tolocation NEWYORK.Theconnectionrequiresa userIDandpassword,and doesnotrequire autocommit.Thenumberstotherightofselectedstatementscorrespondtothe previously-describedsteps.
Connectiontechnique2:ThistechniqueusestheJDBC DriverManagerinterfacefor creating theconnection.Useit withanyleveloftheJDBCdriver.
#sql context Ctx; // Create connection context class Ctx 1 String userid="dbadm"; // Declare variables for user ID and password String password="dbadm";
String empname; // Declare a host variable ...
try { // Load the JDBC driver
Class.forName("com.ibm.db2.jcc.DB2Driver"); 2 } catch (ClassNotFoundException e) { e.printStackTrace(); } Ctx myConnCtx= 3 new Ctx("jdbc:db2://sysmvs1.stl.ibm.com:5021/NEWYORK",
userid,password,false); // Create connection context object myConnCtx // for the connection to NEWYORK
#sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE WHERE EMPNO='000010'};
// Use myConnCtx for executing an SQL statement
1. ExecuteanSQLJconnectiondeclarationclause.
Thisisthesameasstep 1onpage92inconnectiontechnique1. 2. Loadthedriver.
Thisisthesameasstep 2onpage92inconnectiontechnique1. 3. InvoketheJDBCDriverManager.getConnectionmethod.
Doingthis createsaJDBC connectionobjectfortheconnectiontothedata source.YoucanuseanyoftheformsofgetConnectionthatarespecified in Connecttoa datasourceusingtheDriverManagerinterfacewiththeIBMDB2 Driver forJDBCandSQLJ.
Themeaningsof theurl,user,andpasswordparametersare thesame asthe meaningsof theparametersinstep3 onpage92ofconnectiontechnique1. 4. Invoketheconstructorfortheconnectioncontextclassthatyoucreatedinstep
1.
Doingthis createsaconnectioncontext objectthatyouspecifyineachSQL statementthatyouexecuteattheassociateddatasource.Theconstructor invocationstatementneedstobe inthefollowingform:
connection-context-class connection-context-object=
new connection-context-class(Connection JDBC-connection-object);
TheJDBC-connection-objectparameteristheConnectionobjectthatyoucreated instep 3.
The followingcode usesconnection technique2tocreateaconnection tolocation NEWYORK.Theconnection requiresa userIDandpassword,and doesnotrequire autocommit.Thenumberstotherightofselectedstatementscorrespondtothe previously-describedsteps.
Connectiontechnique3:ThistechniqueusestheJDBCDataSourceinterfacefor creating theconnection.
1. ExecuteanSQLJconnectiondeclarationclause.
Thisisthesameasstep 1onpage92inconnectiontechnique1.
2. Ifyour systemadministratorcreatedaDataSourceobjectinadifferentprogram:
a. Obtainthelogicalnameofthedatasourcetowhichyouneedtoconnect. #sql context Ctx; // Create connection context class Ctx 1 String userid="dbadm"; // Declare variables for user ID and password String password="dbadm";
String empname; // Declare a host variable ...
try { // Load the JDBC driver Class.forName("com.ibm.db2.jcc.DB2Driver"); 2 } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection jdbccon= 3 DriverManager.getConnection("jdbc:db2://sysmvs1.stl.ibm.com:5021/NEWYORK", userid,password);
// Create JDBC connection object jdbccon jdbccon.setAutoCommit(false); // Do not autocommit 4 Ctx myConnCtx=new Ctx(jdbccon); 5 // Create connection context object myConnCtx // for the connection to NEWYORK
#sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE WHERE EMPNO='000010'};
// Use myConnCtx for executing an SQL statement
b. Createacontexttouseinthenextstep.
c. Inyourapplication program,usetheJava NamingandDirectoryInterface
(JNDI)togettheDataSourceobjectthatisassociatedwith thelogicaldata sourcename.
Otherwise,createaDataSourceobjectandassignpropertiestoit,asshownin ″Creatingand usinga DataSourceobjectinthesameapplication″inConnectto a datasourceusingtheDataSourceinterface.
3. InvoketheJDBCDataSource.getConnectionmethod.
Doingthis createsaJDBC connectionobjectfortheconnectiontothedata source.YoucanuseoneofthefollowingformsofgetConnection:
getConnection();
getConnection(user, password);
Themeaningsof userand passwordparametersarethesameasthemeaningsof theparametersinstep 3onpage92ofconnectiontechnique1.
4. Ifthedefaultautocommitmodeisnotappropriate,invoketheJDBC
Connection.setAutoCommitmethod.
Doingthis indicateswhetheryouwantthedatabase managertoissuea COMMITaftereverystatement. Theformofthis methodis:
setAutoCommit(boolean autocommit);
5. Invoketheconstructorfortheconnectioncontextclassthatyoucreatedinstep
1 onpage94.
Doingthis createsaconnectioncontext objectthatyouspecifyin eachSQL statementthatyouexecuteattheassociateddatasource.Theconstructor invocationstatementneedstobe inthefollowingform:
connection-context-class connection-context-object=
new connection-context-class(Connection JDBC-connection-object);
TheJDBC-connection-objectparameteristheConnectionobjectthatyoucreated instep 3.
Thefollowingcodeusesconnectiontechnique 3tocreateaconnection toalocation with logicalnamejdbc/sampledb.Thenumberstotherightofselectedstatements correspondtothepreviously-describedsteps.
Connectiontechnique4 (IBMDB2 DriverforJDBCandSQLJonly):Thistechnique usestheJDBCDataSourceinterfaceforcreating theconnection.Thistechnique
requiresthattheDataSourceisregisteredwithJNDI.
import java.sql.*; import javax.naming.*; import javax.sql.*; ...
#sql context CtxSqlj; // Create connection context class CtxSqlj 1 Context ctx=new InitialContext(); 2b DataSource ds=(DataSource)ctx.lookup("jdbc/sampledb"); 2c Connection con=ds.getConnection(); 3 String empname; // Declare a host variable
...
con.setAutoCommit(false); // Do not autocommit 4 CtxSqlj myConnCtx=new CtxSqlj(con); 5
// Create connection context object myConnCtx #sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE
WHERE EMPNO='000010'};
// Use myConnCtx for executing an SQL statement
1. From yoursystemadministrator, obtainthelogicalnameof thedatasourceto
whichyouneedtoconnect.
2. ExecuteanSQLJconnectiondeclarationclause.
For thistypeofconnection,theconnectiondeclarationclauseneedstobeof thisform:
#sql public static context context-class-name
with (dataSource="logical-name");
Theconnectioncontext mustbedeclaredaspublicandstatic.logical-nameisthe datasourcenamethatyouobtainedinstep 1.
3. Invoketheconstructorfortheconnectioncontextclassthatyoucreatedinstep
2.
Doingthis createsaconnectioncontext objectthatyouspecifyineachSQL statementthatyouexecuteattheassociateddatasource.Theconstructor invocationstatementneedstobe inoneofthefollowingforms:
connection-context-class connection-context-object=
new connection-context-class();
connection-context-class connection-context-object=
new connection-context-class (String user, String password);
Themeaningsof theuserandpasswordparametersarethesameasthe meaningsof theparametersinstep3 onpage92ofconnectiontechnique1.
The followingcode usesconnection technique4tocreateaconnection toalocation with logicalnamejdbc/sampledb.Theconnectionrequiresauser IDandpassword.
Connectiontechnique5:Thistechniqueusesapreviouslycreatedconnectionto connecttothedatasource.Ingeneral,oneprogramdeclaresa connectioncontext class,creates connectioncontexts,andpassesthemasparameterstoother
programs.Aprogramthatusestheconnectioncontextinvokesaconstructorwith thepassed connectioncontextobjectasitsargument.
Example:ProgramCtxGen.sqljdeclaresconnectioncontextCtxand createsinstance oldCtx:
#sql context Ctx; ...
// Create connection context object oldCtx
Programtest.sqljreceivesoldCtxasa parameterandusesoldCtxastheargument of itsconnectioncontextconstructor:
#sql public static context Ctx
with (dataSource="jdbc/sampledb"); 2 // Create connection context class Ctx String userid="dbadm"; // Declare variables for user ID and password String password="dbadm";
String empname; // Declare a host variable ...
Ctx myConnCtx=new Ctx(userid, password); 3 // Create connection context object myConnCtx // for the connection to jdbc/sampledb #sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE
WHERE EMPNO='000010'};
// Use myConnCtx for executing an SQL statement
void useContext(sqlj.runtime.ConnectionContext oldCtx)
// oldCtx was created in CtxGen.sqlj {
Ctx myConnCtx= new Ctx(oldCtx);
// Create connection context object myConnCtx // from oldCtx
#sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE WHERE EMPNO=’000010’};
// Use myConnCtx for executing an SQL statement ...
}
Connectiontechnique6:Thistechniqueusesthedefault connectiontoconnectto thedatasource.Itshouldbeusedonlyinsituationswherethedatabasethreadis controlled byanotherresourcemanager,suchastheJava storedprocedure environment.YouusethedefaultconnectionbyspecifyingyourSQLstatements withoutaconnection contextobject.Whenyouusethistechnique,youdonotneed toload aJDBCdriverunlessyouexplicitlyuseJDBC interfacesinyour program. For example:
#sql {SELECT LASTNAME INTO :empname FROM EMPLOYEE WHERE EMPNO='000010'}; // Use default connection for
// executing an SQL statement
Tocreatea defaultconnectioncontext,SQLJdoesa JNDIlookupfor
jdbc/defaultDataSource.Ifnothingisregistered,anull contextexceptionisissued whenSQLJattemptstoaccessthecontext.
Related concepts:
v “HowJDBCapplicationsconnecttoa datasource”onpage24
Related tasks:
v “Connectingtoa datasourceusingtheDriverManagerinterfacewith theIBM
DB2DriverforJDBC andSQLJ”onpage27
v “Connectingtoa datasourceusingtheDataSourceinterface”onpage30
Related reference:
v “PropertiesfortheIBMDB2DriverforJDBC andSQLJ”onpage232
Java
packages
for
SQLJ
support
Before youcanexecuteSQLJstatementsorinvokeJDBCmethodsinyourSQLJ program, youneed tobeabletoaccessallorpartsofvariousJavapackagesthat containsupport forthosestatements.Youcandothateither byimportingthe packagesorspecific classes,orbyusingfully-qualifiedclassnames.Youmight need thefollowingpackagesorclassesforyourSQLJprogram:
sqlj.runtime
Contains theSQLJrun-timeAPI.
java.sql
Contains thecoreJDBCAPI.
com.ibm.db2.jcc
Contains theDB2-specificimplementationofJDBCandSQLJ.
javax.naming
Contains classesand interfacesforJava NamingandDirectoryInterface (JNDI),whichisoftenusedforimplementing aDataSource.
javax.sql
Contains methodsforproducingserver-sideapplicationsusingJava.
Related concepts:
v “BasicstepsinwritinganSQLJapplication”onpage89
Variables
in
SQLJ
applications
InDB2programsin otherlanguages,youusehostvariablestopassdatabetween theapplication programand DB2.InSQLJprograms,hostvariablesareknownas
hostexpressions.Ahostexpressioncanbea simpleJavaidentifier,oritcanbe a complex expression.Everyhostexpressionmuststart witha colonwhenitisused in anSQLstatement.Hostexpressionsarecasesensitive.
AJava identifiercanhaveanyofthedatatypeslisted intheJava datatypecolumn of Java,JDBC,andSQLJdatatypes.Data typesthatare specifiedinaniteratorcan be anyof thetypesintheJavadatatypecolumn ofJava,JDBC,andSQLJdata types.
Acomplex expressionisanarrayelementorJavaexpressionthatevaluatestoa single value.AcomplexexpressioninanSQLJclausemustbe surroundedby parentheses.
The followingexamples demonstratehowtousehostexpressions.
Example: Declaringa Javaidentifierandusingitina SELECTstatement:
Inthisexample,thestatementthatbeginswith#sqlhasthesame functionasa SELECT statementinotherlanguages.Thisstatementassignsthelastnameofthe employeewithemployeenumber000010toJava identifierempname.
String empname; ...
#sql [ctxt]
{SELECT LASTNAME INTO :empname FROM EMPLOYEE WHERE EMPNO='000010'}; Example: Declaringa Javaidentifierandusingitina storedprocedurecall:
Inthisexample,thestatementthatbeginswith#sqlhasthesame functionasan SQLCALLstatementinotherlanguages.ThisstatementusesJava identifierempno asaninputparameter tostoredprocedureA.ThevalueIN,whichprecedesempno, specifies thatempnoisaninputparameter.Fora parameterinaCALLstatement, IN isthedefault.Theexplicitordefaultqualifierthatindicateshow theparameter is used(IN,OUT, orINOUT)mustmatchthecorrespondingvaluein theparameter definitionthatyouspecifiedintheCREATEPROCEDUREstatementforthestored procedure.
String empno = "0000010"; ...
#sql [ctxt] {CALL A (:IN empno)};
Example: Usingacomplex expressionasa hostidentifier:
Thisexampleusescomplexexpression(((int)yearsEmployed++/5)*500)asa host expression.
#sql [ctxt] {UPDATE EMPLOYEE
SQLJperformsthefollowingactionswhenitprocessesa complexhostexpression: v Evaluateseachofthehostexpressionsinthestatement,fromleftto right,before