sybase and stored procedure
gladrags Sep 30, 2003 11:15 PMDear Friends
I have developed a stateless session bean which calls the stored procedure in sybase backend.I am using jconnect driver.
I have changed the stored procedure backend
sp_procxmode procname ,'anymode'
I get the following exception
com.sybase.jdbc2.jdbc.SybSQLException
SELECT INTO command not allowed within multi-statement transaction.
/**
* ReportMasterSessionBean
*
* @ejb:bean name="ae/reliance/dac/ReportMasterSession"
* display-name="ReportMasterSession"
* type="Stateless"
* transaction-type="Container"
* trans-attribute="NotSupported"
*
* jndi-name="ae/reliance/dac/ReportMasterSession"
*/
apart from other methods i am connecting to database using following method
/**
* Gets JDBC connection from connectionpool
*
* @return the JDBC connection
* @ejb:interface-method view-type="remote"
*/
public java.sql.Connection getConnection() {
java.sql.Connection con = null;
try {
//System.out.println("Inside the getConnection method");
Context ctx = new InitialContext();
//ds=(javax.sql.DataSource)ctx.lookup("java:jdbc/SybaseDB");
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("java:jdbc/SybaseDB");
//System.out.println("After getting the datasource" + ds);
con = ds.getConnection();
ctx.close();
//System.out.println("Getting connection in getConnection " + con);
} catch (Exception e) {
System.out.println("Exception occured while getting datasource");
System.out.println("Exception occured "+e);
}
return con;
}
the method which calls the procedure
/**
* getUserRightsRep
*
* @return StringBuffer Description of the Return Value
* @exception java.rmi.RemoteException Description of the Exception
* @ejb:interface-method view-type="remote"
* @ejb:transaction-type="NotSupported"
* This method selects the appointments from the table based on location and appointment
* Status.
*/
public StringBuffer getUserRightsRep(long usercode) throws java.rmi.RemoteException
{
//System.out.println("getProgramList,inside method");
java.sql.Connection con = null;
java.sql.ResultSet rs=null;
StringBuffer sbuf=null;
long reqid=0;
try {
con = getConnection();
String sql = "{ call p_dac_user_rights_xml_rep (?) }";
java.sql.CallableStatement cs = con.prepareCall(sql);
cs.setLong(1,usercode);
rs = cs.executeQuery();
sbuf=new StringBuffer("<?xml version='1.0'?>");
while(rs.next())
{
sbuf=sbuf.append(rs.getString("xmlstring1"));
sbuf=sbuf.append(rs.getString("xmlstring2"));
sbuf=sbuf.append(rs.getString("xmlstring3"));
}
//System.out.println("getProgramList after EXECUTE QUERY"+sbuf);
} catch (java.sql.SQLException e) {
try {
//System.out.println("Exception occured ");
System.out.println("Exception occured "+e);
} catch (Exception ex) {}
} finally {
try {
if (con != null)
con.close();
} catch (java.sql.SQLException e) {
//System.out.println("Exception occured inside finally ");
System.out.println("Exception occured "+e);
}
}
return sbuf;
}
Can anyone suggest me more about transaction and how it behaves.
i tried to setAutoCommit(false) but it thorw me another error
SET CHAINED command not allowed within multi-statement transaction.