User-defined Data Types

This chapter describes how tbJDBC processes user-defined data types.

The JDBC standard provides the java.sql.Array and java.sql.Struct interfaces to support user-defined data types.

• An array type is used to collect and save the same type of values.

• A struct type is used to collect and save multiple types of values.

Array Type

tbJDBC provides the com.tmax.tibero.jdbc.TbArray class which implements the java.sql.Array interface.

Declaring Arrays

Declare an array as follows:

create or replace type t_varr    is varray(16)     of varchar2(128);

create or replace type t_tbl     is table          of varchar2(128);

Using Arrays as Input Arguments

An array object can be sent to a PSM procedure as an input argument as follows:

1. Create an array object by using the Connection object.

public Array Connection.createArrayOf(String typeName, Object[] elements) 
throws SQLException;

1. Bind the array object to a procedure.

An array object can be sent to a PSM procedure by using CallableStatement.setObject().

The following example creates an array object and sends it to a PSM procedure as an input argument.

StringBuffer sbProc = new StringBuffer();

sbProc.append("begin "); 
sbProc.append(" print_varr(?); "); 
sbProc.append("end; ");

Object[] attributes = new Object[16]; 
for (int i = 0; i < 16; i++) {
        attributes[i] = "abc" + i;
}

Array arr = conn.createArrayOf("TIBERO.T_VARR", attributes); 

CallableStatement cstmt = conn.prepareCall(sbProc.toString()); 

cstmt.setObject(1, arr);
cstmt.execute();

int status = 0; 
int i = 0;
while (status == 0) {
      cstmt = conn.prepareCall("{call sys.dbms_output.get_line(?, ?)}"); 
      cstmt.registerOutParameter(1, java.sql.Types.VARCHAR); 
      cstmt.registerOutParameter(2, java.sql.Types.NUMERIC); 
      cstmt.execute();
        
      status = cstmt.getInt(2); '
      if (status != 0)
           break;
      String retStr = cstmt.getString(1); 
      String ret[] = retStr.split(":"); 
      System.out.println(ret[1]);
}

Using Arrays as Output Arguments

An array object can be received from a PSM procedure as an output argument as follows:

1. Call a procedure through out binding. Then, the procedure returns an array object.

2. Receive the array object by using CallableStatement.getArray().

3. Receive the values from the array object.

The following example receives an array object from a procedure as an output argument. The p_out_t_varr procedure returns 16 string array values.

sbProc.append("begin ");
sbProc.append(" p_out_t_varr(?, ?); "); 
sbProc.append("end; ");

CallableStatement cstmt = conn.prepareCall(sbProc.toString()); 
cstmt.registerOutParameter(1, Types.ARRAY, "TIBERO.T_VARR"); 
cstmt.setString(2, "abc");
cstmt.execute();

TbArray arr = (TbArray) cstmt.getArray(1); 

Object obj = arr.getArray();

String[] bdArr = (String[]) obj; 
assertEquals(16, bdArr.length);
for (int i = 0; i < bdArr.length; i++)
    { System.out.println(bdArr[i]);
}

---

Struct Type

Unlike an array that saves a built-in type of values, a struct saves multiple types of values. tbJDBC provides the com.tmax.tibero.jdbc.TbStruct class which implements the java.sql.Struct interface.

Declaring Struct Data Type

Declare a record (struct) by declaring a package as follows:

create or replace type t_obj is object (obj_id number, name varchar(128), d date);

Using Structs as Input Arguments

A struct object can be sent to a PSM procedure as an input argument as follows:

1. Create a struct object by using the Connection object.

public Struct Connection.createStruct(String typeName, Object[] attributes)

1. Bind the struct object to a procedure.

A struct object can be sent to a PSM procedure by using CallableStatement.setObject().

The following example passes a struct object as an input argument. The print_obj procedure receives and processes a struct object.

StringBuffer sbProc = new StringBuffer();

sbProc.append("begin "); 
sbProc.append(" print_obj(?); "); 
sbProc.append("end; ");

Object[] attributes = new Object[3]; 
attributes[0] = 1;
attributes[1] = "abc1";
Calendar cal = Calendar.getInstance(); 
cal.set(2012, 11, 21); // 2012. 12. 21
attributes[2] = new Date(cal.getTimeInMillis());

TbStruct struct = conn.createStruct("TIBERO.T_OBJ", attributes);

CallableStatement cstmt = conn.prepareCall(sbProc.toString());
cstmt.setObject(1, struct);
int row = cstmt.executeUpdate();

Using Structs as Output Arguments

A struct object can be received from a PSM procedure as an output argument as follows:

1. Call a procedure through out binding. Then, the procedure returns a struct object.

2. Receive the struct object by using CallableStatement.getStruct().

3. Receive the values from the struct object.

The following example receives a struct object from a procedure as an output argument. The p_out_t_rec procedure returns NUMBER, VARCHAR, and DATE types of record objects.

StringBuffer sbProc = new StringBuffer();

sbProc.append("begin ");
sbProc.append(" p_out_t_obj(?); ");
sbProc.append("end; ");

CallableStatement cstmt = conn.prepareCall(sbProc.toString());
cstmt.registerOutParameter(1, Types.STRUCT, "TIBERO.T_OBJ");
cstmt.execute();

Struct struct = (Struct) cstmt.getObject(1);
Object[] structArr = struct.getAttributes();

System.out.println(structArr.length);

System.out.println("number : " + structArr[0]);
System.out.println("varchar : " + structArr[1]);
System.out.println("date : " + structArr[2]);

---

Sample Code with Arrays and Structs

The following is sample procedure code to create arrays and structs.

create or replace type t_obj is object (obj_id number, name varchar(128), d date);
/
create or replace type t_varr is varray(16) of varchar2(128);
/
create or replace type t_varr_varr is varray(8) of t_varr;
/
create or replace type t_tbl is table of varchar2(128);
/
create or replace type t_tbl_tbl is table of t_tbl;
/
create or replace type t_varr_obj is varray(4) of t_obj;
/
create or replace type t_tbl_obj is table of t_obj;
/
create or replace procedure p_out_t_obj(o out t_obj)
is
begin
    o := t_obj(1,'abc',to_date('1900-01-01', 'YYYY-MM-DD')); end;
/
create or replace procedure p_out_t_varr(o out t_varr, str varchar2) 
as
    o_val t_varr; 
begin
    o_val := t_varr();
    
    for i in 1 .. 16 loop 
        o_val.extend;
        o_val(i) := str || to_char(i); 
    end loop;
    o := o_val; 
end;
/
create or replace procedure p_out_t_varr_varr(o out t_varr_varr) 
as
    o_val t_varr_varr; 
begin
    o_val := t_varr_varr();
    
    for i in 1 .. 8 loop 
        o_val.extend;
        p_out_t_varr(o_val(i), 'elem' || to_char(i)); 
    end loop;
    o := o_val; 
end;
/
create or replace procedure p_out_t_tbl(o out t_tbl, str varchar2) 
as
    o_val t_tbl; 
begin
    o_val := t_tbl();
    
    for i in 1 .. 4 loop 
        o_val.extend;
        o_val(i) := str || to_char(i); 
    end loop;
    o := o_val; 
end;
/
create or replace procedure p_out_t_tbl_tbl(o out t_tbl_tbl) 
as
    o_val t_tbl_tbl; 
begin
    o_val := t_tbl_tbl();
    
    for i in 1 .. 2 loop 
        o_val.extend;
        p_out_t_tbl(o_val(i), 'elem' || to_char(i)); 
    end loop;
    o := o_val; 
end;
/
create or replace procedure p_out_t_varr_obj(o out t_varr_obj) 
as
    o_val t_varr_obj; 
begin
    o_val := t_varr_obj(); 
    for i in 1 .. 4 loop
        o_val.extend;
        p_out_t_obj(o_val(i)); 
    end loop;
    o := o_val; 
end;
/
create or replace procedure p_out_t_tbl_obj(o out t_tbl_obj) 
as
    o_val t_tbl_obj; 
begin
    o_val := t_tbl_obj(); 
    for i in 1 .. 6 loop
        o_val.extend; 
        p_out_t_obj(o_val(i));
    end loop;
    o := o_val; 
end;
/
create or replace procedure print_obj(p in t_obj) 
as
begin
    dbms_output.put_line('obj_id:' || p.obj_id); 
    dbms_output.put_line('name :' || p.name); 
    dbms_output.put_line('d :' || p.d);
end;
/
create or replace procedure print_varr(p in t_varr) 
as
begin
    for i in 1 .. 16 loop 
        dbms_output.put_line(to_char(i) || ':' || p(i));
    end loop; 
end;
/
create or replace procedure print_varr_varr(p in t_varr_varr) 
as
begin
    for i in 1 .. 8 loop 
        print_varr(p(i));
    end loop; 
end;
/
create or replace procedure print_tbl(p in t_tbl) 
as
begin
    for i in 1 .. 4 loop
        dbms_output.put_line(to_char(i) || ':' || p(i)); 
    end loop;
end;
/
create or replace procedure print_tbl_tbl(p in t_tbl_tbl) 
as
begin
    for i in 1 .. 2 loop 
        print_tbl(p(i));
    end loop; 
end;
/
create or replace procedure print_varr_obj(p in t_varr_obj) 
as
begin
    for i in 1 .. 4 loop 
        print_obj(p(i));
    end loop; 
end;
/
create or replace procedure print_tbl_obj(p in t_tbl_obj) 
as
begin
    for i in 1 .. 6 loop 
        print_obj(p(i));
    end loop; 
end;
/