Jconn2.jar not capturing Error 277 from sybase

[Karthick]

We noticed if a stored procedure(in unchained mode) creating
#temp tables is called inside a transaction scope, the
transaction is failing to rollback the transactions. Instead
it commits all the transaction.

However, the same procedure when called from ISQL connection
from a transaction scope errors out Error 277 < Number (277)
Severity (16) There was a transaction active when exiting
the stored procedure 'testcheckhash'. The temporary table
'#tabletimeperiod' was dropped in this transaction either
explicitly or implicitly. This transaction has been aborted
to prevent database corruption.>

Other required details:
------------------------
Database : Sybase ASE 12.5.3
DB Server: Solaris
Client : windows
Java version : j2sdk1.4.2_05

ISQL Code:
----------
begin tran
insert into sometable values (10)
exec testcheckhash
commit tran

--<Procedure code>
create procedure testcheckhash
as
Begin
CREATE TABLE #tabletimeperiod (timeperiod
VARCHAR(12))
DROP TABLE #tabletimeperiod
End
go
EXEC sp_procxmode 'dbo. testcheckhash','unchained'

Same code when I execute from Java using jconn2.jar(Jconnect
5), I am getting no error instead the insert done on
“sometable” doesnot get rolled back in the event of
failure in the procedure.

Can you let me know if any one else encountered this
behavior and resolved it.

Thanks in Advance,
Karthick

[Paul]

Hi Karthick,

Also when you upgrade try setting a property in your connection string:

ServerInitiatedTransaction=false

and see if this resolves the problem.

-Paul

"Paul" <pa...@sybase.com> wrote in message news:480e2854$1@forums-1-dub...
> Hi Karthick,
>
> This sounds familiar and I think the problem is resolved. You never
> posted the
> exact jConnect version or provide jdbc details of your code like
> connection
> string, etc.
>
> I can't recall the original problem but it sounds familiar. Try this and
> see if it works.
> If not post a sample of the test so we can see what the code looks like.
>
> -Paul
>
> <Karthick> wrote in message news:480de23f.532...@sybase.com...

[Paul]

Hi Karthick,

This sounds familiar and I think the problem is resolved. You never posted
the
exact jConnect version or provide jdbc details of your code like connection
string, etc.

I can't recall the original problem but it sounds familiar. Try this and
see if it works.
If not post a sample of the test so we can see what the code looks like.

-Paul

<Karthick> wrote in message news:480de23f.532...@sybase.com...

[Karthick]

Paul,

Thanks for the response.

The code where this problem occurs goes like this :

aseConn = batchClass.getConnection();
aseConn.setAutoCommit(false);
Statement stmt = aseConn.createStatement();

stmt.execute("set chained off");
stmt.execute("begin tran");
stmt.execute("Insert into testnit values ('Main Value')");
CallableStatement cstmtCallProc = aseConn.prepareCall("{call
checkhash(?) }");
cstmtCallProc.registerOutParameter(1,
java.sql.Types.INTEGER);
cstmtCallProc.execute();

int iProcOut = cstmtCallProc.getInt(1);
Thread.sleep(60000);
stmt.execute("commit tran");

<<In this code when the Thread.sleep is called there are no
lock held by this connection on testnit table(where the
insert is performed before calling the procedure).

If there are any exception that occurs inside the procedure
or after calling the procedure, the rollback transaction
called in the exception block doesnot rollback the insert
made on testnit table.

Hope this explains the problem better than before>>

Find the java code below that is used for getting the
connection. The connection is taken from the application
server (Weblogic). COnnection pool in the application server
has been configured to use jconn2.jar

private Connection getConnection()
{
DataSource dmsInputDataSource = null;
Connection connection = null;
InitialContext context = null;
try {
Hashtable ctxProps = new Hashtable();

ctxProps.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");

ctxProps.put(Context.PROVIDER_URL, "t3://XXXX:XXX");
context = new InitialContext(ctxProps);

dmsInputDataSource = (DataSource)
context.lookup("INPUT_DATA_SOURCE");

System.out.println(dmsInputDataSource);
System.out.println("Step 1.2");
connection = dmsInputDataSource.getConnection();
System.out.println("Step 1.3");

System.out.println(connection);

} catch (Exception e) {
e.printStackTrace();
}
return connection;

}

Please let know if you would more information to simulate/
understand the problem.

Thanks and Regards,
Karthick

[Paul]

Hi Karthick,

I understand this now.

I tested your code in a simple application:

Stmt.java:
============
// 4-24-08 Thu
// for newsgroup question

import com.sybase.jdbcx.*;
import java.sql.*;
import java.util.Properties;
import java.io.*;

public class Stmt
{
public static void main(String args[])
{
java.sql.Connection con = null;
String url = "jdbc:sybase:Tds:XXXX:12530/jdbc";

// set up for input stream to cause delay prior to rs.next
BufferedReader console = new BufferedReader(new
InputStreamReader(System.in));
String getInput = null;

// Register jdbc driver
try
{
Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
}

catch (Exception e)
{
System.out.println(e.getMessage());
}

try
{
Properties props = new Properties();
//props.put("PROTOCOL_CAPTURE","tds_data");
props.put("user","sa");
props.put("password","");
//props.put("USE_METADATA", "false");
con = DriverManager.getConnection(url, props);
con.setAutoCommit(false);

// connected
System.out.println("Connection made.");

// Now for the Stmt statement
Statement stmt = null;
ResultSet rs = null;

stmt = con.createStatement();
// batch of stmts

stmt.execute("set chained off");
stmt.execute("begin tran");

stmt.execute("Insert into sometable values (88)");
CallableStatement cstmtCallProc = con.prepareCall("{call
testcheckhash(?) }");
cstmtCallProc.registerOutParameter(1,
java.sql.Types.INTEGER);
cstmtCallProc.execute();

int iProcOut = cstmtCallProc.getInt(1);
Thread.sleep(60000);
stmt.execute("commit tran");

System.out.println("rs.next starts here");
System.out.println("Check ASE tds then enter some stuff
and continue.");
try {
getInput = console.readLine();
}
catch (IOException ioe) {
getInput = "<" + ioe +">";
}

// commit
con.commit();

// close result set and statement
rs.close();
stmt.close();

System.out.println("Closing connection.");
//con.close();

}
catch (com.sybase.jdbc3.jdbc.SybSQLException sqe)
{
System.out.println("\nThis is a SybSQLException\n");
System.out.println("getStatus=" + sqe.getStatus());
System.out.println("getLineNumber=" + sqe.getLineNumber());
System.out.println("getProcedureName=" + sqe.getProcedureName());
System.out.println("getServerName=" + sqe.getServerName());
System.out.println("getSeverity=" + sqe.getSeverity());
System.out.println("getState=" + sqe.getState());
System.out.println("getTranState=" + sqe.getTranState());
System.out.println("getTranState=" + sqe.getMessage());
sqe.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
//System.out.println(e);
System.exit(1);
}
finally
{
try
{
con.close();
System.out.println("Connection closed");
}
catch (Exception e)
{
System.out.println("Exception on closing cstmt or conn:");
e.printStackTrace();
}
}
} // main
} // Stmt

Results:

newsgroup_42408>java Stmt
Connection made.

This is a SybSQLException

getStatus=0
getLineNumber=6
getProcedureName=testcheckhash
getServerName=pvxp1253
getSeverity=16
getState=1
getTranState=0
getTranState=There was a transaction active when exiting the stored

procedure 'testcheckhash'. The temporary table '#tabletimeperiod' was dr
opped in this transaction either explicitly or implicitly. This transaction
has been aborted to prevent database corruption.

at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ParamManager.new(Unknown Source)
at com.sybase.jdbc3.jdbc.ParamManager.doGetOutValueAt(Unknown
Source)
at com.sybase.jdbc3.jdbc.ParamManager.getOutValueAt(Unknown Source)
at com.sybase.jdbc3.jdbc.SybCallableStatement.getInt(Unknown Source)
at Stmt.main(Stmt.java:58)
Connection closed

In this case the SQLException was caught and the ASE aborted the
transaction:
(sometable originally has 10 in the table):

1> select * from sometable
2> go
c1
-----------
10

(1 row affected)

Maybe it's not getting caught. Also, if the error occurs on ASE I think it
should get aborted.
Could you try this on your ASE? If it doesn't work, use this property:

//props.put("PROTOCOL_CAPTURE","tds_data");

and post ASE version and jConnect version

Thank you,

-Paul

<Karthick> wrote in message news:4810c830.409...@sybase.com...