Java Code Examples for java.sql.CallableStatement#getClob()

The following examples show how to use java.sql.CallableStatement#getClob() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: mybatis   File: NClobTypeHandler.java    License: Apache License 2.0 8 votes vote down vote up
@Override
public String getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = cs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 
Example 2
Source Project: gemfirexd-oss   File: EXTDTAInputStream.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Create a new EXTDTAInputStream from a CallableStatement.
 * 
 * 
 * @param cs
 *            CallableStatement from which to retrieve the lob
 * @param column
 *            column number
 * @param drdaType
 *            FD:OCA type of object one of
 * 			   DRDAConstants.DRDA_TYPE_NLOBBYTES
 * 			   DRDAConstants.DRDA_TYPE_LOBBYTES
 * 			   DRDAConstants.DRDA_TYPE_NLOBCMIXED
 *  		   DRDAConstants.DRDA_TYPE_LOBCMIXED
 */
public static EXTDTAInputStream getEXTDTAStream(CallableStatement cs, int column, int drdaType)
       throws SQLException
   {
	    
	int ndrdaType = drdaType | 1; //nullable drdaType

       switch ( ndrdaType )
       {
       case DRDAConstants.DRDA_TYPE_NLOBBYTES:
           Blob    blob = cs.getBlob( column );
           if ( blob ==  null ) { return null; }
           return new EXTDTAInputStream( blob, ndrdaType );
       case DRDAConstants.DRDA_TYPE_NLOBCMIXED:
           Clob    clob = cs.getClob( column );
           if ( clob == null ) { return null; }
           return new EXTDTAInputStream( clob, ndrdaType );
       default:
           badDRDAType( ndrdaType );
		return null;
       }
}
 
Example 3
Source Project: gemfirexd-oss   File: EXTDTAInputStream.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Create a new EXTDTAInputStream from a CallableStatement.
 * 
 * 
 * @param cs
 *            CallableStatement from which to retrieve the lob
 * @param column
 *            column number
 * @param drdaType
 *            FD:OCA type of object one of
 * 			   DRDAConstants.DRDA_TYPE_NLOBBYTES
 * 			   DRDAConstants.DRDA_TYPE_LOBBYTES
 * 			   DRDAConstants.DRDA_TYPE_NLOBCMIXED
 *  		   DRDAConstants.DRDA_TYPE_LOBCMIXED
 */
public static EXTDTAInputStream getEXTDTAStream(CallableStatement cs, int column, int drdaType)
       throws SQLException
   {
	    
	int ndrdaType = drdaType | 1; //nullable drdaType

       switch ( ndrdaType )
       {
       case DRDAConstants.DRDA_TYPE_NLOBBYTES:
           Blob    blob = cs.getBlob( column );
           if ( blob ==  null ) { return null; }
           return new EXTDTAInputStream( blob, ndrdaType );
       case DRDAConstants.DRDA_TYPE_NLOBCMIXED:
           Clob    clob = cs.getClob( column );
           if ( clob == null ) { return null; }
           return new EXTDTAInputStream( clob, ndrdaType );
       default:
           badDRDAType( ndrdaType );
		return null;
       }
}
 
Example 4
/**
 * Create a new EXTDTAInputStream from a CallableStatement.
 * 
 * 
 * @param cs
 *            CallableStatement from which to retrieve the lob
 * @param column
 *            column number
 * @param drdaType
 *            FD:OCA type of object one of
 * 			   DRDAConstants.DRDA_TYPE_NLOBBYTES
 * 			   DRDAConstants.DRDA_TYPE_LOBBYTES
 * 			   DRDAConstants.DRDA_TYPE_NLOBCMIXED
 *  		   DRDAConstants.DRDA_TYPE_LOBCMIXED
 */
public static EXTDTAInputStream getEXTDTAStream(CallableStatement cs, int column, int drdaType)
       throws SQLException
   {
	    
	int ndrdaType = drdaType | 1; //nullable drdaType

       switch ( ndrdaType )
       {
       case DRDAConstants.DRDA_TYPE_NLOBBYTES:
           Blob    blob = cs.getBlob( column );
           if ( blob ==  null ) { return null; }
           return new EXTDTAInputStream( blob, ndrdaType );
       case DRDAConstants.DRDA_TYPE_NLOBCMIXED:
           Clob    clob = cs.getClob( column );
           if ( clob == null ) { return null; }
           return new EXTDTAInputStream( clob, ndrdaType );
       default:
           badDRDAType( ndrdaType );
		return null;
       }
}
 
Example 5
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	String value = "";
	Clob clob = cs.getClob(columnIndex);
	if (clob != null) {
		int size = (int) clob.length();
		value = clob.getSubString(1, size);
	}
	return value;
}
 
Example 6
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	String value = "";
	Clob clob = cs.getClob(columnIndex);
	if (clob != null) {
		int size = (int) clob.length();
		value = clob.getSubString(1, size);
	}
	return value;
}
 
Example 7
Source Project: mybaties   File: ClobTypeHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public String getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = cs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 
Example 8
Source Project: mybaties   File: NClobTypeHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public String getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = cs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 
Example 9
Source Project: mybatis   File: ClobTypeHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public String getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = cs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 
Example 10
/**
 * Test that a call to getClob() to retrieve the value of a non-CLOB
 * parameter fails with the expected SQLException. Used to throw
 * ClassCastException, see DERBY-4970.
 */
public void testGetClobFromIntParameter() throws SQLException {
    CallableStatement cs = prepareCall("call int_out(?)");
    cs.registerOutParameter(1, Types.INTEGER);
    cs.execute();
    try {
        cs.getClob(1);
        fail("getClob() on int parameter expected to fail");
    } catch (SQLException sqle) {
        assertSQLState("22005", sqle);
    }
}
 
Example 11
Source Project: micro-integrator   File: SQLQuery.java    License: Apache License 2.0 4 votes vote down vote up
private ParamValue getOutparameterValue(CallableStatement cs, String type, int ordinal)
        throws DataServiceFault {
    try {
        Object elementValue;
        if (type.equals(DBConstants.DataTypes.STRING)) {
            elementValue = cs.getString(ordinal);
            return new ParamValue(elementValue == null ? null : elementValue.toString());
        } else if (type.equals(DBConstants.DataTypes.DOUBLE)) {
            elementValue = cs.getDouble(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Double) elementValue));
        } else if (type.equals(DBConstants.DataTypes.BIGINT)) {
            elementValue = cs.getLong(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Long) elementValue));
        } else if (type.equals(DBConstants.DataTypes.INTEGER)) {
            elementValue = cs.getInt(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Integer) elementValue));
        } else if (type.equals(DBConstants.DataTypes.TIME)) {
            elementValue = cs.getTime(ordinal);
            return new ParamValue(elementValue == null ? null
                    : this.convertToTimeString((Time) elementValue));
        } else if (type.equals(DBConstants.DataTypes.DATE)) {
            elementValue = cs.getDate(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Date) elementValue));
        } else if (type.equals(DBConstants.DataTypes.TIMESTAMP)) {
            if (timeConvertEnabled) {
                elementValue = cs.getTimestamp(ordinal, calendar);
            } else {
                elementValue = cs.getTimestamp(ordinal);
            }
            return new ParamValue(elementValue == null ? null
                    : this.convertToTimestampString((Timestamp) elementValue));
        } else if (type.equals(DBConstants.DataTypes.BLOB)) {
            elementValue = cs.getBlob(ordinal);
            return new ParamValue(elementValue == null ? null
                    : this.getBase64StringFromInputStream(((Blob) elementValue)
                            .getBinaryStream()));
        } else if (type.equals(DBConstants.DataTypes.CLOB)) {
            elementValue = cs.getClob(ordinal);
            return new ParamValue(elementValue == null ? null :
                   deriveValueFromClob((Clob) elementValue));
        } else if (type.equals(DBConstants.DataTypes.STRUCT)) {
            elementValue = cs.getObject(ordinal);
            return new ParamValue(elementValue == null ? null : (Struct) elementValue);
        } else if (type.equals(DBConstants.DataTypes.ARRAY)) {
            Array dataArray = cs.getArray(ordinal);
            ParamValue paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY);
            if (dataArray != null) {
                this.processSQLArray(dataArray, paramValue);
            }
            return paramValue;
        } else if (type.equals(DBConstants.DataTypes.NUMERIC)) {
            elementValue = cs.getBigDecimal(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((BigDecimal) elementValue));
        } else if (type.equals(DBConstants.DataTypes.BIT)) {
            elementValue = cs.getBoolean(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Boolean) elementValue));
        } else if (type.equals(DBConstants.DataTypes.TINYINT)) {
            elementValue = cs.getByte(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Byte) elementValue));
        } else if (type.equals(DBConstants.DataTypes.SMALLINT)) {
            elementValue = cs.getShort(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Short) elementValue));
        } else if (type.equals(DBConstants.DataTypes.REAL)) {
            elementValue = cs.getFloat(ordinal);
            return new ParamValue(elementValue == null ? null
                    : ConverterUtil.convertToString((Float) elementValue));
        } else if (type.equals(DBConstants.DataTypes.BINARY)) {
            elementValue = cs.getBlob(ordinal);
            return new ParamValue(elementValue == null ? null
                    : this.getBase64StringFromInputStream(((Blob) elementValue)
                            .getBinaryStream()));
        } else {
            throw new DataServiceFault("Unsupported data type: " + type);
        }
    } catch (SQLException e) {
        throw new DataServiceFault(e, "Error in getting sql output parameter values.");
    }
}
 
Example 12
/**
 * Verify correct mapping of clobs.
 */
public void testClobMapping() throws Exception
{
    Connection conn = getConnection();
    PreparedStatement ps;
    CallableStatement cs;
    Clob outVal;

    //
    // Clob input parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobIn\n" +
         "( in c clob, out result varchar( 100 ) )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobIn'\n"
         );
    ps.execute();
    ps.close();

    cs = chattyPrepareCall( conn, "call clobIn( cast( 'def' as clob ), ? )" );
    cs.registerOutParameter( 1, Types.VARCHAR );
    cs.execute();
    assertEquals( "def", cs.getString( 1 ) );
    cs.close();

    cs = chattyPrepareCall( conn, "call clobIn( ?, ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 2, Types.VARCHAR );
    cs.execute();
    assertEquals( "ghi", cs.getString( 2 ) );
    cs.close();

    //
    // Clob output parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobOut\n" +
         "( out c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobOut( ? )" );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "abc", outVal.getSubString( 1L, (int) outVal.length() ) );
    cs.close();

    //
    // Clob inout parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobInOut\n" +
         "( inout c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobInOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobInOut( ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "ihg", outVal.getSubString( 1L, (int) outVal.length() ) );

    Clob inValue = makeBigClob();
    cs.setClob( 1, inValue );
    cs.execute();
    Clob outValue = cs.getClob( 1 );
    compareClobs( inValue, outValue );
    
    cs.close();
}
 
Example 13
/**
 * Verify correct mapping of clobs.
 */
public void testClobMapping() throws Exception
{
    Connection conn = getConnection();
    PreparedStatement ps;
    CallableStatement cs;
    Clob outVal;

    //
    // Clob input parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobIn\n" +
         "( in c clob, out result varchar( 100 ) )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobIn'\n"
         );
    ps.execute();
    ps.close();

    cs = chattyPrepareCall( conn, "call clobIn( cast( 'def' as clob ), ? )" );
    cs.registerOutParameter( 1, Types.VARCHAR );
    cs.execute();
    assertEquals( "def", cs.getString( 1 ) );
    cs.close();

    cs = chattyPrepareCall( conn, "call clobIn( ?, ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 2, Types.VARCHAR );
    cs.execute();
    assertEquals( "ghi", cs.getString( 2 ) );
    cs.close();

    //
    // Clob output parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobOut\n" +
         "( out c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobOut( ? )" );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "abc", outVal.getSubString( 1L, (int) outVal.length() ) );
    cs.close();

    //
    // Clob inout parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobInOut\n" +
         "( inout c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobInOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobInOut( ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "ihg", outVal.getSubString( 1L, (int) outVal.length() ) );

    Clob inValue = makeBigClob();
    cs.setClob( 1, inValue );
    cs.execute();
    Clob outValue = cs.getClob( 1 );
    compareClobs( inValue, outValue );
    
    cs.close();
}
 
Example 14
public static Object getValue(final CallableStatement cs, final int index, Class<?> requiredType) throws SQLException {
    Object value = null;
    boolean wasNullCheck = false;
    if (requiredType == null) {
        return cs.getObject(index);
    }
    requiredType = primitiveToWrapper(requiredType);
    // Explicitly extract typed value, as far as possible.
    if (String.class.equals(requiredType)) {
        value = cs.getString(index);
    } else if (Integer.class.equals(requiredType)) {
        value = cs.getInt(index);
        wasNullCheck = true;
    } else if (Double.class.equals(requiredType)) {
        value = cs.getDouble(index);
        wasNullCheck = true;
    } else if (Boolean.class.equals(requiredType)) {
        value = cs.getBoolean(index) ? Boolean.TRUE : Boolean.FALSE;
        wasNullCheck = true;
    } else if (java.sql.Date.class.equals(requiredType)) {
        value = cs.getDate(index);
    } else if (java.sql.Time.class.equals(requiredType)) {
        value = cs.getTime(index);
    } else if (java.sql.Timestamp.class.equals(requiredType)) {
        value = cs.getTimestamp(index);
    } else if (java.util.Date.class.equals(requiredType)) {
        value = new java.util.Date(cs.getTimestamp(index).getTime());
    } else if (Byte.class.equals(requiredType)) {
        value = cs.getByte(index);
        wasNullCheck = true;
    } else if (Short.class.equals(requiredType)) {
        value = cs.getShort(index);
        wasNullCheck = true;
    } else if (Long.class.equals(requiredType)) {
        value = cs.getLong(index);
        wasNullCheck = true;
    } else if (Float.class.equals(requiredType)) {
        value = cs.getFloat(index);
        wasNullCheck = true;
    } else if (Number.class.equals(requiredType)) {
        value = cs.getDouble(index);
        wasNullCheck = true;
    } else if (byte[].class.equals(requiredType)) {
        value = cs.getBytes(index);
    } else if (java.math.BigDecimal.class.equals(requiredType)) {
        value = cs.getBigDecimal(index);
    } else if (java.sql.Blob.class.equals(requiredType)) {
        value = cs.getBlob(index);
    } else if (java.sql.Clob.class.equals(requiredType)) {
        value = cs.getClob(index);
    } else if (java.net.URL.class.equals(requiredType)) {
        value = cs.getURL(index);
    } else {
        // Some unknown type desired -> rely on getObject.
        value = cs.getObject(index);
    }
    // Perform was-null check if demanded (for results that the JDBC driver returns as primitives).
    if (wasNullCheck && value != null && cs.wasNull()) {
        value = null;
    }
    return value;
}
 
Example 15
/**
 * Verify correct mapping of clobs.
 */
public void testClobMapping() throws Exception
{
    Connection conn = getConnection();
    PreparedStatement ps;
    CallableStatement cs;
    Clob outVal;

    //
    // Clob input parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobIn\n" +
         "( in c clob, out result varchar( 100 ) )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobIn'\n"
         );
    ps.execute();
    ps.close();

    cs = chattyPrepareCall( conn, "call clobIn( cast( 'def' as clob ), ? )" );
    cs.registerOutParameter( 1, Types.VARCHAR );
    cs.execute();
    assertEquals( "def", cs.getString( 1 ) );
    cs.close();

    cs = chattyPrepareCall( conn, "call clobIn( ?, ? )" );
    cs.setClob( 1, new HarmonySerialClob( "ghi" ) );
    cs.registerOutParameter( 2, Types.VARCHAR );
    cs.execute();
    assertEquals( "ghi", cs.getString( 2 ) );
    cs.close();

    //
    // Clob output parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobOut\n" +
         "( out c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobOut( ? )" );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "abc", outVal.getSubString( 1L, (int) outVal.length() ) );
    cs.close();

    //
    // Clob inout parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobInOut\n" +
         "( inout c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobInOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobInOut( ? )" );
    cs.setClob( 1, new HarmonySerialClob( "ghi" ) );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "ihg", outVal.getSubString( 1L, (int) outVal.length() ) );

    Clob inValue = makeBigClob();
    cs.setClob( 1, inValue );
    cs.execute();
    Clob outValue = cs.getClob( 1 );
    compareClobs( inValue, outValue );
    
    cs.close();
}