Insert images from files into a database table

Overview

In Etlworks, you can insert images from files into a database table.

Use case

Let's assume that:

• our target database supports inserting images from files. The example below was created for MS SQL Server.
• there is a folder which either already contains images, or you copied/moved images to that folder using the Copy or Move Files ETL Flow. The folder must be accessible by the target database.
• there is an image table like the one below (as an example):

CREATE TABLE image (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FileName VARCHAR(255) NOT NULL,
    Image VARBINARY(max) NOT NULL
);

Process

Here's how you can insert images from files into a database table given the example above:

Step 1. Create a database Connection to the target database.

Step 2. Create a Server Storage Connection to the folder with images. Let's call this Connection Local images.

Step 3. Create a JavaScript Flow. This is an example of some JavaScript code that you might want to use. Take note of the URL of the Connection in the FileManagerTask.list method. It must be the same as the Connection created in Step 2.

var javaImports = new JavaImporter(com.toolsverse.etl.core.task.common, 
com.toolsverse.util); 

with (javaImports) {

var javaImports = new JavaImporter(com.toolsverse.etl.core.task.common, 
com.toolsverse.util); 

with (javaImports) {

var Alias alias = new Alias();
alias.setUrl('the same URL as in Step2');

etlConfig.setValue('image files to load', FileManagerTask.list(alias, *.*'));
}
}

Step 4. Create an Execute SQL Flow and use the Connection created in Step 1. This is an example of SQL statements that you might want to use.

Note: The token {FILE_TO_LOAD} is used.

INSERT INTO Image(FileName, Image) 
VALUES ('{FILE_TO_LOAD}', 
(SELECT * FROM OPENROWSET(BULK N'{FILE_TO_LOAD}', SINGLE_BLOB) AS Image))

in this example, we are using MS SQL Server syntax to insert an image from the file. Check your target database documentation for more examples.

Step 5. Create a Delete Files Flow. Use the Connection created in Step 2 as a source. Enter {FILE_TO_DELETE} into the FROM field.

Step 6. Create a nested Flow, which combines the Flows created in Steps 4 and 5 (in that order).

Step 7. Create a final nested Flow by doing the following:

7.1 Add the Flow created in Step 3.

7.2 Add the Flow created in Step 6. Click the Edit condition (pen) icon.

7.3 Add the following code as a Loop Script: 

Note: These tokens are used: {FILE_TO_LOAD} and {FILE_TO_DELETE}.

var javaImports = new JavaImporter(com.toolsverse.etl.core.task.common, 
com.toolsverse.util); 

with (javaImports) {

var files = etlConfig.getValue('image files to load');

if (!files.isEmpty()) {
  var variable = scenario.getExecute().get(0).getVariable('FILE_TO_LOAD');

  if (variable == null) {
      variable = new Variable();
      variable.setName('FILE_TO_LOAD');
      scenario.getExecute().get(0).addVariable(variable);
  }  

  variable.setValue(files.get(0).getPath());
  SystemConfig.instance().getProperties().put('FILE_TO_DELETE', f
iles.get(0).getName());

  files.remove(0);
}  

value = files.isEmpty() ? null : 'continue';
}