40. Can the UPLOAD ODBC connector load images directly into a SQLServer/Oracle database column?
Yes, but we don't recommend doing this as the database files may become very big, causing poor database performance and system backups may take too long to complete.
Do this at your own risk! That said, here is here is how to do it…
The SQL Server database column must be setup as a varbinary(max) or image type. The Oracle Server database column must be setup as a BLOB.
Please note if you are using Oracle you must use the Oracle Oracle ODBC driver, not the Microsoft Oracle ODBC driver. The Microsoft Oracle ODBC driver does not support loading images into a BLOB.
There are two options that can be used.
Use the EzeScan UPDATE_WITH_IMAGE placeholder.
Use the EzeScan <<DOCBLOBDATA>> placeholder.
Option 1: Use the EzeScan UPDATE_WITH_IMAGE placeholder.
In the example the operator runs an insert statement to firstly upload the data, and then a select statement to select the row which UPDATE_WITH_IMAGE is used to pass the document in.
e.g. Insert into dbo.invoices (supplier, invno, invdate, invamount, transactionid) values ('<<Supplier Name>>','<<Invoice Number>>','<<Date>>','<<Amount>>','<<Transaction ID>>');
UPDATE_WITH_IMAGE select invfile, transactionid from dbo.invoices where transactionid = '<<Transaction ID>>';
Please Note: In this example the column transactionid is the primary key, it is recommended to include the primary key column in the update_with_image statement.
Option 2: Use the EzeScan <<DOCBLOBDATA>> placeholder.
The placeholder <<DOCBLOBDATA>> can be used. This option is designed if the operator wishes to call a stored procedure (so therefore everything is done in one statement in EzeScan) or instead of using UPDATE_WITH_IMAGE (option 1) the insert statement can call the <<DOCBLOBDATA>> placeholder directly.
e.g. call poc_upload.upload_claim_doc('<<(Field 1)>>,'<<DOCBLOBDATA>>')
Please note:
If using Oracle with option 2 the operator must use single quotes around the <<DOCBLOBDATA>> placeholder.
If using SQL with option 2 the operator must prefix a 0x in front of the <<DOCBLOBDATA>> placeholder.
It is not possible to populate a BLOB parameter when calling an Oracle Stored Procedure using the Oracle ODBC Driver. Use NVARCHAR2 instead.
The Oracle ODBC Driver is only capable of passing parameters values of up to 32k in size. As soon as you exceed this limit the driver crashes EzeScan. Due to this limitation it is not recommend to use a stored procedure with DOCBLOBDATA with EzeScan. Use the UPDATE_WITH_IMAGE option instead.