Description John Duffy 2003-11-24 11:01:35 EST

Hi,

I have the following java program calling a test stored procedure in Sybase 
ASE 12.

import java.sql.*;
public class read_temp_table {
       public static void main(String[] args)throws Exception {
		
		int legal_entity_id;
		float base_notional_amount;
	
		Class.forName("com.sybase.jdbc2.jdbc.SybDriver");

		Connection con = DriverManager.getConnection
		
	("jdbc:sybase:Tds:hpsybd:5191/dssol01","cbgsdujo","dujo1234");

		CallableStatement stmt = con.prepareCall
			("{?= call dbo.USER_pls_read_temp_table}");

		boolean results = stmt.execute();

			if (results) {
				ResultSet rs = stmt.getResultSet();
					do {
				
					rs.next();
					legal_entity_id = rs.getInt(1);
					base_notional_amount = rs.getFloat(2);
				    System.out.println (legal_entity_id  + ' ' 
+ base_notional_amount);
				  
					} while (rs.next());
				}
		stmt.close();
		con.close();
	}	
}

If the procedure USER_pls_read_temp_table is defined thus 

BEGIN

SELECT a.legal_entity_id,
       a.base_notional_amount
  FROM USER_pls_transaction a
 ORDER BY a.legal_entity_id

END

everything is fine. If I define it as below, using a temp table the declared 
boolean results getting the value of the CallableStatement method returns 
false. Both versions work when called directly through the RapidSQL ISQL 
environment.

BEGIN

CREATE TABLE #filtered_deals (legal_entity_id int null,
                              base_notional_amount decimal(25,2) null)
                              
 INSERT INTO #filtered_deals
  SELECT a.legal_entity_id,
         a.base_notional_amount
  FROM USER_pls_transaction a
  ORDER BY a.legal_entity_id

SELECT legal_entity_id, base_notional_amount 
FROM #filtered_deals


END

Please help!

TIA,

John Duffy