Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



Caution

Returning Value of Java Stored Function/Procedure and Precision Type on IN/OUT

To limit the return value of Java stored function/procedure and precision type on IN/OUT, CUBRID processes as follows:

Checks the sql_type of the Java stored function/procedure.

Passes the value returned by Java to the database with only the type converted if necessary, ignoring the number of digits defined during creating the Java stored function/procedure. In principle, the user manipulates the passed data directly in the database.

Take a look at the following typestring() Java stored function.

public class JavaSP1{

    public static String typestring(){

        String temp = " ";

        for(int i=0 i< 1 i++)

            temp = temp + "1234567890";

        return temp;

}

}

 

create function typestring() return char(5)

as language java

name 'JavaSP1.typestring() return java.lang.String';

 

Call typestring()

 

  Result

======================

  ' 1234567890'

Returning java.sql.ResultSet in Java Stored Procedure

In CUBRID, you must use CURSOR as the data type when you declare a Java stored function/procedure that returns a java.sql.ResultSet.

create function rset() return cursor

as language java

name 'JavaSP2.TResultSet() return java.sql.ResultSet'

Before the Java file returns java.sql.ResultSet, it is required to cast to the CUBRIDResultSet class and then to call the setReturnable() method.

import cubrid.jdbc.driver.*;

import java.sql.*;

public class JavaSP2 {

public static ResultSet TResultSet(){

    try{

            Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

    Connection conn = DriverManager.getConnection("jdbc:default:connection:");

    ((CUBRIDConnection)conn).setCharset("euc_kr");

    String sql = "select * from station";

    Statement stmt=conn.createStatement();

    ResultSet rs = stmt.executeQuery(sql);

    ((CUBRIDResultSet)rs).setReturnable();

    return rs;

    } catch (Exception e) {

              e.printStackTrace();

    }

    return null;

  }

}

In the calling block, you must set the OUT argument with Types.JAVA_OBJECT, get the argument to the getObject() function, and then cast it to the java.sql.ResultSet type before you use it. In addition, the java.sql.ResultSet is only available to use in CallableStatement of JDBC.

import java.sql.*;

public class TestResultSet{

  public static void main(String[] args) {

    Connnection conn = null;

    Statement stmt= null;

    int result;

    int i;

 

    try{

           Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

    conn = DriverManager.getConnection("jdbc:CUBRID:localhost:33000:demodb:::","","");

  

    CallableStatement cstmt = con.prepareCall("?=CALL rset()");

    cstmt.registerOutParameter(1, Types.JAVA_OBJECT);

    cstmt.execute();

    ResultSet rs = (ResultSet) cstmt.getObject(1);

    while(rs.next()) {

      System.out.println(rs.getString(1));

    }

      rs.close();

     } catch (Exception e) {

             e.printStackTrace();

    }

}

You cannot use the ResultSet as an input argument. If you pass it to an IN argument, an error occurs. An error also occurs when calling a function that returns ResultSet in a non-Java environment.

IN/OUT of Set Type in Java Stored Function/Procedure

If the set type of the Java stored function/procedure in CUBRID is IN OUT, the value of the argument changed in Java must be applied to IN OUT. When the set type is passed to the OUT argument, it must be passed as a two-dimensional array.

Create procedure setoid(x in out set, z object)

as language java name

'SetOIDTest.SetOID(cubrid.sql.CUBRIDOID[][], cubrid.sql.CUBRIDOID';

 

public static void SetOID(cubrid.sql.CUBRID[][] set, cubrid.sql.CUBRIDOID aoid){

  Connection conn=null;

  Statement stmt=null;

  String ret="";

  Vector v = new Vector();

  cubrid.sql.CUBRIDOID[] set1 = set[0];

  try {

    if(set1!=null) {

      int len = set1.length;

      int i = 0;

      for (i=0 i< len i++)

        v.add(set1[i]);

    }

  v.add(aoid);

  set[0]=(cubrid.sql.CUBRIDOID[]) v.toArray(new cubrid.sql.CUBRIDOID[]{});

  } catch(Exception e) {

    e.printStackTrace();

    System.err.pirntln("SQLException:"+e.getMessage());

  }

}

Using OID in Java Stored Function/Procedure

In case of using the OID type value for IN/OUT in CUBRID, use the value passed from the server.

create procedure tOID(i inout object, q string)

as language java

name 'OIDtest.tOID(cubrid.sql.CUBRIDOID[], java.lang.String)';

 

public static void tOID(CUBRIDOID[] oid, String query)

{

  Connection conn=null;

  Statement stmt=null;

  String ret="";

 

  try {

    Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

    conn=DriverManager.getConnection("jdbc:default:connection:");

 

    conn.setAutoCommit(false);

    stmt = conn.createStatement();

    ResultSet rs = stmt.executeQuery(query);

    System.out.println("query:"+ query);

    while(rs.next()) {

      oid[0]=(CUBRIDOID)rs.getObject(1);

      System.out.println("oid:"+oid[0].getTableName());

    }

    stmt.close();

    conn.close();

  } catch (SQLException e) {

    e.printStackTrace();

    System.err.println("SQLException:"+e.getMessage());

  } catch (Exception e) {

    e.printStackTrace();

    system.err.println("Exception:"+ e.getMessage());

  }

}