|
From: Gerd W. <ger...@t-...> - 2010-12-20 20:24:49
|
Am 20.12.2010 21:15, schrieb Fred Toussi:
> Hi Gerd,
>
> Could you send the Java file with the main method. It was not attached
> to your message.
Ups, sorry, is attached now.
Gerd
>
> Fred
>
> On Mon, 20 Dec 2010 20:32 +0100, "Gerd Wagner" <ger...@t-...>
> wrote:
>> Fred Toussi maintainer of the HSQLDB Project was kind enough to tell me
>> how to get stored procedures to work. Thanks again.
>>
>>
>> The main method of the attached Java file shows how SQuirreL executes
>> procedures and why it does not display the ResultSet:
>>
>> Since SQuirreL does not really know what kind of statement is being
>> executed it uses probably the most generic way JDBC offers to find out
>> what the outcome of a statement is. To me it looks like the HSQLDB
>> driver does not yet handle that call the way it is defined by JDBC-API
>> docs.
>>
>> Gerd
>>
>>
>> Am 19.12.2010 17:16, schrieb Fawzib Rojas:
>>> 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
>>>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> 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
>>>
>>
>>
>
|
