将大型BLOB传递给存储过程 [英] Passing large BLOBs to Stored Procedure

问题描述

我有一个简单的(示例)脚本将文件上传到数据库(如果需要的话，是Oracle):

I have a simple (example) script to upload a file into the database (Oracle, if it matters):

<cfscript>
param string filename;

if ( FileExists( filename ) )
{
  result = new StoredProc(
    datasource = "ds",
    procedure  = "FILE_UPLOAD",
    result     = "NA",
    parameters = [
      { value = FileReadBinary( filename ), type = "in", cfsqltype = "CF_SQL_BLOB" }
    ]
  ).execute();
}
</cfscript>

但是， ColdFusion CFML参考 FileReadBinary( filepath )的状态:

注意: 此操作将文件读入本地变量"范围内的变量中.不适用于大型文件(例如日志)，因为它们会导致服务器停机.

Note: This action reads the file into a variable in the local Variables scope. It is not intended for use with large files, such as logs, because they can bring down the server.

如果我不应该使用FileReadBinary( filepath )，应该如何上传大文件(0.5-1Tb)?

If I should not use FileReadBinary( filepath ), how should I upload a large (0.5 - 1Tb) file?

推荐答案

根据 @Galcoholic 的建议，您可以利用基础Java类并使用 CallableStatement.setBlob( int, InputStream ) :

As suggested by @Galcoholic, you can utilise the underlying Java classes and use CallableStatement.setBlob( int, InputStream ):

<cfscript>
param string filename;

// Get the necessary Java classes:
Files = createObject( 'java', 'java.nio.file.Files' );
Paths = createObject( 'java', 'java.nio.file.Paths' );

// Do not timeout the request
setting requesttimeout = 0;

try {
  input = Files.newInputStream( Paths.get( filename, [] ), [] );

  connection  = createObject( 'java', 'coldfusion.server.ServiceFactory' )
                  .getDataSourceService()
                  .getDataSource( 'ds' )
                  .getConnection()
                  .getPhysicalConnection();
  statement   = connection.prepareCall( '{call FILE_UPLOAD(?)}' );
  statement.setBlob( JavaCast( 'int', 1 ), input );
  statement.executeUpdate()
}
finally
{
  if ( isDefined( "statement" ) )
    statement.close();
  if ( isDefined( "connection" ) )
    connection.close();
}
</cfscript>

注意:

• 必须为Java方法提供每个参数.因此，对于具有可变数量参数的方法，必须将VARARGS参数作为数组(或无其他参数的空数组)进行传递.
• ColdFusion数值不会隐式强制转换为Java数值文字，因此需要JavaCast( 'int', value ).
• 上面的示例中未包括错误处理.
• 如果文件已上传，则需要将管理控制台中的帖子数据的最大大小"和请求限制内存"设置从默认大小增加到要上传文件大小的适当限制(否则coldfusion.util.MemorySemaphore在解析脚本之前处理上载时将抛出内存不足异常).

• Every argument must be supplied for a Java method; so for methods with variable number of arguments then the VARARGS arguments must be passed as an array (or an empty array for no additional arguments).
• ColdFusion numeric values will not be implicitly coerced to Java numeric literals so JavaCast( 'int', value ) is required.
• Error handling is not included in the above example.
• If the files have been uploaded then the "Maximum size of post data" and "Request Throttle Memory" settings in the Admin console will need to be increased from the default sizes to an appropriate limit for the size of the files being uploaded (otherwise coldfusion.util.MemorySemaphore will throw out-of-memory exceptions when it handles the upload before the script gets parsed).

这篇关于将大型BLOB传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！