Re: [Squirrel-sql-users] HSQLDB database and stored procedures

[Fawzib R. <f_...@sp...>]

Stored procedure:

public static void msim_getDictionary(String lang,ResultSet[] rs) throws SQLException{
   StringList sql=new StringList();
   Connection  conn=DriverManager.getConnection("jdbc:default:connection");
   PreparedStatement stmt;
   // set string
   sql.clear();
   sql.add("select de.phrase,dv.definition as value");
   sql.add("from dictentries de,dictvalues dv");
   sql.add("where de.id=dv.id");
   sql.add("and dv.locale=?");
   // do query
   stmt=conn.prepareStatement(sql.getText());
   stmt.setString(1, lang);
   rs[0]=stmt.executeQuery();
}



The create procedure statement is:

CREATE PROCEDURE msim_getDictionary(lang VARCHAR(2))
READS SQL DATA
LANGUAGE JAVA
DYNAMIC RESULT SETS 1
EXTERNAL NAME 'CLASSPATH:com.mycompany.HSQLDB.msim_getDictionary'


On 12/17/2010 9:40 PM, Gerd Wagner wrote:
> Could you please post your procedure and how you created it on the database.
>
>
> Gerd
>
> Am 16.12.2010 21:54, schrieb Fawzib Rojas:
>> I created a database in HSQLDB 2.0 and have been testing the java stored
>> procedures. I created one simple procedure just a select, when I call it
>> from SQuirrel SQL it returns an empty result set. I made a small program
>> to test the stored procedure and the procedure does work. Don't know why
>> is not working from SQuirrel SQL.
>>
>> import java.sql.*;
>>
>> public class Application {
>> 	public static void printRows(ResultSet rs,int limit) throws SQLException{
>>         		System.out.println("----------------");
>> 	        for(int c=0;c<limit&&   rs.next()==true;c++){
>> 	        	System.out.println("Row "+c+": "+rs.getString(1));
>> 		}
>> 	}
>>       	public static void main(String[] args) {
>> 	        // load driver
>> 	        try {
>> 		      Class.forName("org.hsqldb.jdbcDriver" );	
>> 	        } catch (Exception e) {
>> 		      System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
>> 		      e.printStackTrace();
>> 		      return;
>> 	        }	
>> 		// test procedure
>> 	        try{
>> 	        	Connection conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/TEST", "sa", "");
>> 	             	CallableStatement stmt;
>>               		stmt=conn.prepareCall("{call msim_getDictionary(?)}");
>> 	            	stmt.setString(1, "en");
>> 			if(stmt.execute()) printRows(stmt.getResultSet(),5);
>> 		}catch (SQLException e){
>> 			e.printStackTrace();
>> 		}
>> 	}
>> }
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Lotusphere 2011
>> Register now for Lotusphere 2011 and learn how
>> to connect the dots, take your collaborative environment
>> to the next level, and enter the era of Social Business.
>> http://p.sf.net/sfu/lotusphere-d2d
>> _______________________________________________
>> Squirrel-sql-users mailing list
>> Squ...@li...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>