CODEWITHSUNDEEP
oracle-databasestored-procedurescoldfusionblob
MT0
MT0

Reputation: 167842

Passing large BLOBs to Stored Procedure

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>

However, the ColdFusion CFML Reference states for 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.

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

Upvotes: 3

Views: 2327

Answers (2)

MT0
MT0

Reputation: 167842

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>

Note:

  • 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).

Upvotes: 1

Galcoholic
Galcoholic

Reputation: 518

If using Java is an option, then you can pass an InputStream object to a PreparedStatement for filling a Blob field. Something like this, exception handling and all other stuff to be added:

Connection con = someDataSource.getConnection();
String sql = "INSERT INTO MY_TABLE(MY_BLOB) VALUES(?)";
PreparedStatement ps = con.prepareStatement(sql);
InputStream fis = new FileInputStream("MyBigFile.big");
ps.setBlob(1, fis);
ps.executeUpdate();

I think Java will do it using buffers, and not load the whole file into memory.

Upvotes: 1

Related Questions