我直接把blob 改成long raw,存10个字节正常,存1M字节时,出现下面的错误: java.sql.SQLException: ORA-01653: 表DGMAN.FILESAVE无法通过12(在表空间DAGANG中)扩展 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822) at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446) at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:407) at common.test.method1(test.java:44) at common.test.main(test.java:32)
使用这种方法: Statement stmt1=conn.createStatement(); ResultSet rs = stmt1.executeQuery("select thefile from filesave where id="+id); if(rs.next()) { BLOB blob = ((OracleResultSet)rs).getBLOB("thefile"); OutputStream outStream = blob.getBinaryOutputStream(); outStream.write(bArr); outStream.flush(); rs.close(); System.out.println("插入成功"); } 出现下面错误: java.io.IOException: ORA-22920: 未锁定含有 LOB 值的行 ORA-06512: 在"SYS.DBMS_LOB", line 700 ORA-06512: 在line 1 at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:531) at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:179) at oracle.jdbc.driver.OracleBlobOutputStream.write(OracleBlobOutputStream.java:125) at java.io.OutputStream.write(OutputStream.java:61) at common.test.method3(test.java:100) at common.test.main(test.java:32)没办法,数据库知识太薄弱,出现一点小问题,都解决不了,真实惭愧! 还望大家帮忙!
又有改进: conn.setAutoCommit(false); CallableStatement stmt=conn.prepareCall("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;"); //PreparedStatement stmt = conn.prepareStatement("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;"); stmt.setString(1,id); stmt.registerOutParameter(2,OracleTypes.BLOB); stmt.execute(); BLOB blob=(oracle.sql.BLOB)stmt.getBlob(2); OutputStream outStream = blob.getBinaryOutputStream(); outStream.write(bArr); outStream.flush();错误是: java.io.IOException: ORA-01691: Lob 段DGMAN.SYS_LOB0000005782C00007$$无法通过12(在表空间DAGANG中)扩展 ORA-06512: 在"SYS.DBMS_LOB", line 700 ORA-06512: 在line 1 at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:531) at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:179) at oracle.jdbc.driver.OracleBlobOutputStream.write(OracleBlobOutputStream.java:125) at java.io.OutputStream.write(OutputStream.java:61) at common.test.method4(test.java:122) at common.test.main(test.java:32)两种方案都归结为一个错误(表DGMAN.FILESAVE无法通过12(在表空间DAGANG中)扩展),我估计是数据库的设置有问题,不知道我理解的对不对,改怎样设置?
又有新错误了: conn.setAutoCommit(false); CallableStatement stmt=conn.prepareCall("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;"); //PreparedStatement stmt = conn.prepareStatement("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;"); stmt.setString(1,id); stmt.registerOutParameter(2,OracleTypes.BLOB); BLOB blob=(oracle.sql.BLOB)stmt.getBlob(2); OutputStream outStream = blob.getBinaryOutputStream(); outStream.write(bArr); outStream.flush(); stmt.execute();错误指向BLOB blob=(oracle.sql.BLOB)stmt.getBlob(2); 错误是: java.sql.SQLException: 无效的列索引 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:273) at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatement.java:2413) at oracle.jdbc.driver.OracleStatement.getBLOBValue(OracleStatement.java:2990) at oracle.jdbc.driver.OracleCallableStatement.getBlob(OracleCallableStatement.java:601) at common.test.method4(test.java:118) at common.test.main(test.java:32)
已经通过jspsmartupload上传图片并转换为字节数组byte[] bArr,已经连接数据库得到Connection conn,怎样把bArr传到数据库中,条件是怎么也得保证能传500k的字节吧!
试了好几天,总不好用,真郁闷!! 谁来帮帮我?
我不是已经说了吗,最简单是用long raw型字段,你的语句不用改,就可以上传了,又简单又方便,67M不在话下。
这是LONG RAW型字段处理程序。写成一个函数,FileInputStream作为参数,这样在JSP里调用就可以实现上传到数据库了。
我原来也是玩jspsmartupload的,后来发现太繁。
我省去了建立数据库连接的部分:
String sql = "select picture from tmp_pic where id=1"; Statement stmt = null; ResultSet rs = null; try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
System.out.println(e);
} try { if (rs.next()) { res.setContentType("image/*");
ServletOutputStream sout = res.getOutputStream();
/*
//互联网应用时,如果图片较大(>1M),且网速较低,可以考虑使用以下方式
//效果,使JPG从上到下分为数分,慢慢展示,类似ACDSEE
int BUFFER_SIZE=200000;
InputStream in = rs.getBinaryStream(1); byte b[] = new byte[BUFFER_SIZE];
for (int i = in.read(b,0,BUFFER_SIZE); i!=-1;) {
System.out.println("Read "+i+" bytes.");
sout.write(b,0,i);
i=in.read(b,0,BUFFER_SIZE);
}
*/
/*下面两行是我用的简单的调用方式,目前没有发现问题,而且效率高,如果不喜欢,可用上面注释的部分代替下面两行代码即可。*/
byte [] image =rs.getBytes(1);
sout.write(image);
sout.flush();
sout.close();
}
} catch (Exception e) {
System.out.println(e); PrintWriter toClient = res.getWriter(); //得到向客户端输出文本的对象
res.setContentType("text/html;charset=gb2312");
toClient.write("无法打开图片!");
toClient.close();
} finally {
stmt.close();
conn.close();
}
java.sql.SQLException: ORA-01653: 表DGMAN.FILESAVE无法通过12(在表空间DAGANG中)扩展
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822) at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446) at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:407) at common.test.method1(test.java:44) at common.test.main(test.java:32)
Statement stmt1=conn.createStatement();
ResultSet rs = stmt1.executeQuery("select thefile from filesave where id="+id);
if(rs.next())
{
BLOB blob = ((OracleResultSet)rs).getBLOB("thefile");
OutputStream outStream = blob.getBinaryOutputStream();
outStream.write(bArr);
outStream.flush();
rs.close();
System.out.println("插入成功");
}
出现下面错误:
java.io.IOException: ORA-22920: 未锁定含有 LOB 值的行
ORA-06512: 在"SYS.DBMS_LOB", line 700
ORA-06512: 在line 1
at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:531) at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:179) at oracle.jdbc.driver.OracleBlobOutputStream.write(OracleBlobOutputStream.java:125) at java.io.OutputStream.write(OutputStream.java:61) at common.test.method3(test.java:100) at common.test.main(test.java:32)没办法,数据库知识太薄弱,出现一点小问题,都解决不了,真实惭愧!
还望大家帮忙!
conn.setAutoCommit(false);
CallableStatement stmt=conn.prepareCall("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;");
//PreparedStatement stmt = conn.prepareStatement("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;");
stmt.setString(1,id);
stmt.registerOutParameter(2,OracleTypes.BLOB);
stmt.execute();
BLOB blob=(oracle.sql.BLOB)stmt.getBlob(2);
OutputStream outStream = blob.getBinaryOutputStream();
outStream.write(bArr);
outStream.flush();错误是:
java.io.IOException: ORA-01691: Lob 段DGMAN.SYS_LOB0000005782C00007$$无法通过12(在表空间DAGANG中)扩展
ORA-06512: 在"SYS.DBMS_LOB", line 700
ORA-06512: 在line 1
at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:531) at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:179) at oracle.jdbc.driver.OracleBlobOutputStream.write(OracleBlobOutputStream.java:125) at java.io.OutputStream.write(OutputStream.java:61) at common.test.method4(test.java:122) at common.test.main(test.java:32)两种方案都归结为一个错误(表DGMAN.FILESAVE无法通过12(在表空间DAGANG中)扩展),我估计是数据库的设置有问题,不知道我理解的对不对,改怎样设置?
conn.setAutoCommit(false);
CallableStatement stmt=conn.prepareCall("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;");
//PreparedStatement stmt = conn.prepareStatement("begin insert into filesave(id,thefile) values(?,empty_blob()) return thefile into ?;end;");
stmt.setString(1,id);
stmt.registerOutParameter(2,OracleTypes.BLOB);
BLOB blob=(oracle.sql.BLOB)stmt.getBlob(2);
OutputStream outStream = blob.getBinaryOutputStream();
outStream.write(bArr);
outStream.flush();
stmt.execute();错误指向BLOB blob=(oracle.sql.BLOB)stmt.getBlob(2);
错误是:
java.sql.SQLException: 无效的列索引 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:273) at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatement.java:2413) at oracle.jdbc.driver.OracleStatement.getBLOBValue(OracleStatement.java:2990) at oracle.jdbc.driver.OracleCallableStatement.getBlob(OracleCallableStatement.java:601) at common.test.method4(test.java:118) at common.test.main(test.java:32)
前面的错误看来,好像是表空间设置有问题,注意看看你的临时表空间用的是那张,空间够吗?
DAGANG表空间的设置是否有问题?
如果再不行,干脆重新建个表空间,然后建表到上面,肯定没问题啦。