Example usage for java.sql PreparedStatement setAsciiStream

List of usage examples for java.sql PreparedStatement setAsciiStream

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setAsciiStream

Introduction

In this page you can find the example usage for java.sql PreparedStatement setAsciiStream.

Prototype

void setAsciiStream(int parameterIndex, java.io.InputStream x, long length) throws SQLException;

Source Link

Document

Sets the designated parameter to the given input stream, which will have the specified number of bytes.

Usage

From source file:InsertTextFileToOracle.java

public static void main(String[] args) throws Exception {
    String id = "001";
    String fileName = "fileName.txt";

    FileInputStream fis = null;/*w  ww.j a  v  a  2s .  c  o m*/
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        File file = new File(fileName);
        fis = new FileInputStream(file);
        pstmt = conn.prepareStatement("insert into DataFiles(id, fileName, fileBody) values (?, ?, ?)");
        pstmt.setString(1, id);
        pstmt.setString(2, fileName);
        pstmt.setAsciiStream(3, fis, (int) file.length());
        pstmt.executeUpdate();
        conn.commit();
    } catch (Exception e) {
        System.err.println("Error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        pstmt.close();
        fis.close();
        conn.close();
    }
}

From source file:DemoPreparedStatementSetAsciiStream.java

public static void main(String[] args) {
    Connection conn = null;//w  ww.j av a  2s .c om
    PreparedStatement pstmt = null;
    String query = null;
    try {
        conn = getConnection();
        String fileName = "fileName.txt";
        File file = new File(fileName);
        int fileLength = (int) file.length();
        InputStream stream = (InputStream) new FileInputStream(file);

        query = "insert into  LONG_VARCHAR_TABLE(id, stream) values(?, ?)";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, fileName);
        pstmt.setAsciiStream(2, stream, fileLength);

        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = null;//from  ww  w  .  j  a  va  2s .  c  om
    PreparedStatement pstmt = null;
    Statement stmt = null;
    ResultSet rs = null;
    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    stmt = conn.createStatement();
    createXMLTable(stmt);
    File f = new File("build.xml");
    long fileLength = f.length();
    FileInputStream fis = new FileInputStream(f);
    String SQL = "INSERT INTO XML_Data VALUES (?,?)";
    pstmt = conn.prepareStatement(SQL);
    pstmt.setInt(1, 100);
    pstmt.setAsciiStream(2, fis, (int) fileLength);
    pstmt.execute();
    fis.close();
    SQL = "SELECT Data FROM XML_Data WHERE id=100";
    rs = stmt.executeQuery(SQL);
    if (rs.next()) {
        InputStream xmlInputStream = rs.getAsciiStream(1);
        int c;
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        while ((c = xmlInputStream.read()) != -1)
            bos.write(c);
        System.out.println(bos.toString());
    }
    rs.close();
    stmt.close();
    pstmt.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);//from w  w  w  .  j ava  2  s. c o m

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "INSERT INTO mysql_all_table(" + "col_boolean," + "col_byte," + "col_short," + "col_int,"
            + "col_long," + "col_float," + "col_double," + "col_bigdecimal," + "col_string," + "col_date,"
            + "col_time," + "col_timestamp," + "col_asciistream," + "col_binarystream," + "col_blob) "
            + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);

    pstmt.setBoolean(1, true);
    pstmt.setByte(2, (byte) 123);
    pstmt.setShort(3, (short) 123);
    pstmt.setInt(4, 123);
    pstmt.setLong(5, 123L);
    pstmt.setFloat(6, 1.23F);
    pstmt.setDouble(7, 1.23D);
    pstmt.setBigDecimal(8, new BigDecimal(1.23));
    pstmt.setString(9, "a string");
    pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
    pstmt.setTime(11, new Time(System.currentTimeMillis()));
    pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));

    File file = new File("infilename1");
    FileInputStream is = new FileInputStream(file);
    pstmt.setAsciiStream(13, is, (int) file.length());

    file = new File("infilename2");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(14, is, (int) file.length());

    file = new File("infilename3");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(15, is, (int) file.length());

    pstmt.executeUpdate();
}

From source file:com.wabacus.config.database.type.DB2.java

public void setClobValue(int iindex, String value, PreparedStatement pstmt) throws SQLException {
    if (value == null)
        value = "";
    StringBufferInputStream sbis = new StringBufferInputStream(value);
    pstmt.setAsciiStream(iindex, sbis, sbis.available());
}

From source file:hermes.store.schema.DefaultJDBCAdapter.java

public void insert(Connection connection, String storeId, Message message) throws SQLException, JMSException {
    final String destinationName = message.getJMSDestination() == null ? "default"
            : JMSUtils.getDestinationName(message.getJMSDestination());
    final Domain domain = message.getJMSDestination() == null ? Domain.QUEUE
            : Domain.getDomain(message.getJMSDestination());
    final String messageAsXMLString = xmlHelper.toXML(message);
    final InputStream messageAsXML = new StringInputStream(messageAsXMLString);
    final String messageId = getNextMessageId(storeId);

    ///*from   w w  w  .j av  a 2 s .co m*/
    // DBUtils does not seem to correctly deal with CLOBS, so we have to use
    // normal JDBC...
    //
    // runner.update(connection, "insert into messages values (?, ?)", new
    // Object[] { message.getJMSMessageID(), messageAsXML });

    final PreparedStatement pstmt = connection.prepareStatement("insert into messages values (?, ?)");

    pstmt.setString(1, messageId);
    pstmt.setAsciiStream(2, messageAsXML, messageAsXMLString.length());

    pstmt.execute();
    pstmt.close();

    final QueryRunner runner = new QueryRunner();

    runner.update(connection, "insert into stores values (?, ?, ?, ?)",
            new Object[] { storeId, destinationName, domain.getId(), messageId });
}

From source file:hermes.store.schema.DefaultJDBCAdapter.java

@Override
public void update(Connection connection, String id, Message message) throws SQLException, JMSException {
    final String messageAsXMLString = xmlHelper.toXML(message);
    final InputStream messageAsXML = new StringInputStream(messageAsXMLString);

    final PreparedStatement pstmt = connection
            .prepareStatement("update messages set message = ? where messageid = ?");
    pstmt.setString(1, message.getJMSMessageID());
    pstmt.setAsciiStream(2, messageAsXML, messageAsXMLString.length());
    pstmt.execute();/*www  .j a  v  a2s. c om*/
    pstmt.close();
}

From source file:org.apache.cocoon.util.JDBCTypeConversions.java

/**
 * Set the Statement column so that the results are mapped correctly.
 *
 * @param statement the prepared statement
 * @param position the position of the column
 * @param value the value of the column/*w w w .j  a va  2  s.  c om*/
 */
public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject)
        throws Exception {
    if (value instanceof String) {
        value = ((String) value).trim();
    }
    if (typeObject == null) {
        throw new SQLException("Can't set column because the type is unrecognized");
    }
    if (value == null) {
        /** If the value is null, set the column value null and return **/
        statement.setNull(position, typeObject.intValue());
        return;
    }
    if ("".equals(value)) {
        switch (typeObject.intValue()) {
        case Types.CHAR:
        case Types.CLOB:
        case Types.VARCHAR:
            /** If the value is an empty string and the column is
            a string type, we can continue **/
            break;
        default:
            /** If the value is an empty string and the column
            is something else, we treat it as a null value **/
            statement.setNull(position, typeObject.intValue());
            return;
        }
    }

    File file = null;
    int length = -1;
    InputStream asciiStream = null;

    //System.out.println("========================================================================");
    //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue());
    switch (typeObject.intValue()) {
    case Types.CLOB:
        //System.out.println("CLOB");
        Clob clob = null;
        if (value instanceof Clob) {
            clob = (Clob) value;
        } else if (value instanceof File) {
            File asciiFile = (File) value;
            asciiStream = new BufferedInputStream(new FileInputStream(asciiFile));
            length = (int) asciiFile.length();
            clob = new ClobHelper(asciiStream, length);
        } else if (value instanceof Part) {
            Part anyFile = (Part) value;
            asciiStream = new BufferedInputStream(anyFile.getInputStream());
            length = anyFile.getSize();
            clob = new ClobHelper(asciiStream, length);
        } else if (value instanceof JDBCxlobHelper) {
            asciiStream = ((JDBCxlobHelper) value).inputStream;
            length = ((JDBCxlobHelper) value).length;
            clob = new ClobHelper(asciiStream, length);
        } else if (value instanceof Source) {
            asciiStream = ((Source) value).getInputStream();
            length = (int) ((Source) value).getContentLength();
            clob = new ClobHelper(asciiStream, length);
        } else {
            String asciiText = value.toString();
            asciiStream = new ByteArrayInputStream(asciiText.getBytes());
            length = asciiText.length();
            clob = new ClobHelper(asciiStream, length);
        }

        statement.setClob(position, clob);
        break;
    case Types.CHAR:
        // simple large object, e.g. Informix's TEXT
        //System.out.println("CHAR");

        if (value instanceof File) {
            File asciiFile = (File) value;
            asciiStream = new BufferedInputStream(new FileInputStream(asciiFile));
            length = (int) asciiFile.length();
        } else if (value instanceof JDBCxlobHelper) {
            asciiStream = ((JDBCxlobHelper) value).inputStream;
            length = ((JDBCxlobHelper) value).length;
        } else if (value instanceof Source) {
            asciiStream = ((Source) value).getInputStream();
            length = (int) ((Source) value).getContentLength();
        } else if (value instanceof Part) {
            Part anyFile = (Part) value;
            asciiStream = new BufferedInputStream(anyFile.getInputStream());
            length = anyFile.getSize();
            clob = new ClobHelper(asciiStream, length);
        } else {
            String asciiText = value.toString();
            asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes()));
            length = asciiText.length();
        }

        statement.setAsciiStream(position, asciiStream, length);
        break;
    case Types.BIGINT:
        //System.out.println("BIGINT");
        BigDecimal bd = null;

        if (value instanceof BigDecimal) {
            bd = (BigDecimal) value;
        } else if (value instanceof Number) {
            bd = BigDecimal.valueOf(((Number) value).longValue());
        } else {
            bd = new BigDecimal(value.toString());
        }

        statement.setBigDecimal(position, bd);
        break;
    case Types.TINYINT:
        //System.out.println("TINYINT");
        Byte b = null;

        if (value instanceof Byte) {
            b = (Byte) value;
        } else if (value instanceof Number) {
            b = new Byte(((Number) value).byteValue());
        } else {
            b = new Byte(value.toString());
        }

        statement.setByte(position, b.byteValue());
        break;
    case Types.DATE:
        //System.out.println("DATE");
        Date d = null;

        if (value instanceof Date) {
            d = (Date) value;
        } else if (value instanceof java.util.Date) {
            d = new Date(((java.util.Date) value).getTime());
        } else if (value instanceof Calendar) {
            d = new Date(((Calendar) value).getTime().getTime());
        } else {
            d = Date.valueOf(value.toString());
        }

        statement.setDate(position, d);
        break;
    case Types.DOUBLE:
        //System.out.println("DOUBLE");
        double db;

        if (value instanceof Number) {
            db = (((Number) value).doubleValue());
        } else {
            db = Double.parseDouble(value.toString());
        }
        statement.setDouble(position, db);
        break;
    case Types.FLOAT:
        //System.out.println("FLOAT");
        float f;

        if (value instanceof Number) {
            f = (((Number) value).floatValue());
        } else {
            f = Float.parseFloat(value.toString());
        }
        statement.setFloat(position, f);
        break;
    case Types.NUMERIC:
        //System.out.println("NUMERIC");
        long l;

        if (value instanceof Number) {
            l = (((Number) value).longValue());
        } else {
            l = Long.parseLong(value.toString());
        }

        statement.setLong(position, l);
        break;
    case Types.SMALLINT:
        //System.out.println("SMALLINT");
        Short s = null;

        if (value instanceof Short) {
            s = (Short) value;
        } else if (value instanceof Number) {
            s = new Short(((Number) value).shortValue());
        } else {
            s = new Short(value.toString());
        }

        statement.setShort(position, s.shortValue());
        break;
    case Types.TIME:
        //System.out.println("TIME");
        Time t = null;

        if (value instanceof Time) {
            t = (Time) value;
        } else if (value instanceof java.util.Date) {
            t = new Time(((java.util.Date) value).getTime());
        } else {
            t = Time.valueOf(value.toString());
        }

        statement.setTime(position, t);
        break;
    case Types.TIMESTAMP:
        //System.out.println("TIMESTAMP");
        Timestamp ts = null;

        if (value instanceof Time) {
            ts = (Timestamp) value;
        } else if (value instanceof java.util.Date) {
            ts = new Timestamp(((java.util.Date) value).getTime());
        } else {
            ts = Timestamp.valueOf(value.toString());
        }

        statement.setTimestamp(position, ts);
        break;
    case Types.ARRAY:
        //System.out.println("ARRAY");
        statement.setArray(position, (Array) value); // no way to convert string to array
        break;
    case Types.STRUCT:
        //System.out.println("STRUCT");
    case Types.OTHER:
        //System.out.println("OTHER");
        statement.setObject(position, value);
        break;
    case Types.LONGVARBINARY:
        //System.out.println("LONGVARBINARY");
        statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime()));
        break;
    case Types.VARCHAR:
        //System.out.println("VARCHAR");
        statement.setString(position, value.toString());
        break;
    case Types.BLOB:
        //System.out.println("BLOB");
        if (value instanceof JDBCxlobHelper) {
            statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream,
                    ((JDBCxlobHelper) value).length);
        } else if (value instanceof Source) {
            statement.setBinaryStream(position, ((Source) value).getInputStream(),
                    (int) ((Source) value).getContentLength());
        } else {
            Blob blob = null;
            if (value instanceof Blob) {
                blob = (Blob) value;
            } else if (value instanceof File) {
                file = (File) value;
                blob = new BlobHelper(new FileInputStream(file), (int) file.length());
            } else if (value instanceof String) {
                file = new File((String) value);
                blob = new BlobHelper(new FileInputStream(file), (int) file.length());
            } else if (value instanceof Part) {
                Part anyFile = (Part) value;
                blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize());
            } else {
                throw new SQLException("Invalid type for blob: " + value.getClass().getName());
            }
            //InputStream input = new BufferedInputStream(new FileInputStream(file));
            statement.setBlob(position, blob);
        }
        break;
    case Types.VARBINARY:
        //System.out.println("VARBINARY");
        if (value instanceof JDBCxlobHelper) {
            statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream,
                    ((JDBCxlobHelper) value).length);
        } else if (value instanceof Source) {
            statement.setBinaryStream(position, ((Source) value).getInputStream(),
                    (int) ((Source) value).getContentLength());
        } else if (value instanceof Part) {
            statement.setBinaryStream(position, ((Part) value).getInputStream(), ((Part) value).getSize());
        } else {
            if (value instanceof File) {
                file = (File) value;
            } else if (value instanceof String) {
                file = new File((String) value);
            } else {
                throw new SQLException("Invalid type for blob: " + value.getClass().getName());
            }
            //InputStream input = new BufferedInputStream(new FileInputStream(file));
            FileInputStream input = new FileInputStream(file);
            statement.setBinaryStream(position, input, (int) file.length());
        }
        break;
    case Types.INTEGER:
        //System.out.println("INTEGER");
        Integer i = null;
        if (value instanceof Integer) {
            i = (Integer) value;
        } else if (value instanceof Number) {
            i = new Integer(((Number) value).intValue());
        } else {
            i = new Integer(value.toString());
        }
        statement.setInt(position, i.intValue());
        break;
    case Types.BIT:
        //System.out.println("BIT");
        Boolean bo = null;
        if (value instanceof Boolean) {
            bo = (Boolean) value;
        } else if (value instanceof Number) {
            bo = BooleanUtils.toBooleanObject(((Number) value).intValue() == 1);
        } else {
            bo = BooleanUtils.toBooleanObject(value.toString());
        }
        statement.setBoolean(position, bo.booleanValue());
        break;

    default:
        //System.out.println("default");
        throw new SQLException("Impossible exception - invalid type ");
    }
    //System.out.println("========================================================================");
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testClob() throws Exception {
    try (final Statement stmt = con.createStatement()) {
        stmt.executeUpdate("CREATE TABLE clobtest (id INT, text CLOB(64 K))");
        stmt.execute("INSERT INTO blobtest VALUES (41, NULL)");
        PreparedStatement ps = con.prepareStatement("INSERT INTO clobtest VALUES (?, ?)");
        ps.setInt(1, 42);/*from  ww  w . j  a  v a  2  s . c  om*/
        final char[] buffer = new char[4002];
        IntStream.range(0, 4002).forEach((i) -> buffer[i] = String.valueOf(i % 10).charAt(0));
        ReaderInputStream isr = new ReaderInputStream(new CharArrayReader(buffer), Charset.defaultCharset());

        // - set the value of the input parameter to the input stream
        ps.setAsciiStream(2, isr, 4002);
        ps.execute();
        isr.close();

        final ResultSet resultSet = stmt.executeQuery("select * from clobtest");

        final ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        JdbcCommon.convertToAvroStream(resultSet, outStream, false);

        final byte[] serializedBytes = outStream.toByteArray();
        assertNotNull(serializedBytes);

        // Deserialize bytes to records
        final InputStream instream = new ByteArrayInputStream(serializedBytes);

        final DatumReader<GenericRecord> datumReader = new GenericDatumReader<>();
        try (final DataFileStream<GenericRecord> dataFileReader = new DataFileStream<>(instream, datumReader)) {
            GenericRecord record = null;
            while (dataFileReader.hasNext()) {
                // Reuse record object by passing it to next(). This saves us from
                // allocating and garbage collecting many objects for files with
                // many items.
                record = dataFileReader.next(record);
                Integer id = (Integer) record.get("ID");
                Object o = record.get("TEXT");
                if (id == 41) {
                    assertNull(o);
                } else {
                    assertNotNull(o);
                    assertEquals(4002, o.toString().length());
                }
            }
        }
    }
}

From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java

/**
 * Set the given value as a parameter to the statement.
 *///from w  ww . ja  v a  2  s  . co  m
public void setAsciiStream(PreparedStatement stmnt, int idx, InputStream val, int length, Column col)
        throws SQLException {
    stmnt.setAsciiStream(idx, val, length);
}