Loading Images Into a Database Table

DataMigrator has the capability to load image files into a database table by specifiying their location in the input data.

The first step is to create a table to store the images. The table to load should have a column with a data type that can hold binary data. In the current release of MS SQL Server, that would be varbinary(max). In previous releases, it is image. In the synonym, the USAGE and ACTUAL fields for the column are set to BLOB (Binary Large OBject, as shown in the following image.

The following example outlines the remaining steps needed to load images into a database table.


Top of page

Example: Loading Images Into a Database Table

The first step is to map an application directory. The location of the files containing the images is specified in the ususal appdir/filename format. To map the application directory:

  1. Right-click Application Directories, select New, and then click Application Directory.

    The Create New Application dialog box opens.

  2. From the Application Type drop-down menu, select Application Mapping to Disk.
  3. In th Application Name box, enter graph.
  4. In the Physical Location box, enter c:\ibi\srv77\home\graph.
  5. From the Profile drop-down menu, select the desired profile.

    The Create New Application dialog box should look like the following image.

  6. Click OK.

    Next, create a source file and synonym. The source data needs a list of the names of the image files. From the DMC:

  7. Right-click baseapp.
  8. Select New and then click File.
  9. Enter names as a header line, followed by the names of some of te .gif files (without the extension).
  10. Click the Save button.
  11. Enter tiles as the name and click OK.

    The following image shows the result.

    Then, create a synonym for the file.

  12. Right-click Application Directories, select New, and then click Synonym.
  13. Select <local> for the Under Delimited Flat File. Click OK.
  14. Clear the Create multiple synonyms checkbox and select Server Side for File Location.
  15. For Data File, enter baseapp/tiles.txt.
  16. Click Next twice.
  17. For Header, select Yes.
  18. Click Create Synonym
  19. Click Close.

    Next, create a data flow that loads the images into a database table. The source is the delimited flat file tiles that contains the names of the image files. The target is the existing database table textures.

  20. Right-click baseapp. Select New and then click Flow.
  21. Right-click the left side of the workspace, select Add Source, and then click baseapp/tiles.
  22. Right-click the right side of the workspace, select Add Target, then Existing, and then click baseapp/textures.
  23. Double-click the SQL object to open it.
  24. Double-click Name to add it to selected columns. Enter FNAME for SQL Alias.
  25. Double-click Name and click the calculator button.
  26. For Alias, enter PICTURE. For Expression, enter 'graph/' || T1.NAME || '.gif'.
  27. Click the Test SQL button to see the values for the columns.

  28. Close the Test SQL dialog box and click OK to close the SQL object.
  29. Double-click the Target object.

    The Transformations window opens, as shown in the following image.

  30. Click the Automap button.
  31. Click OK to close the Transformations window.
  32. On the toolbar, click the Run button and click Submit.
  33. Enter loadim for the procedure name and click Save.

    Wait for the completed message to appear in the console log and then click View Last Log to verify that the three rows were loaded.

    To see the images that were loaded into the table, you need to write a FOCUS report that sends its output to a PDF file.

  34. RIght-click baseapp, select New, and then click Procedure.
  35. Enter the following lines:
    TABLE FILE textures
    PRINT PICTURE
    BY FNAME
    ON TABLE PCHOLD AS basapp/texturpt FORMAT PDF
    ON TABLE SET STYLE *
    TYPE=REPORT,$
    TYPE=DATA,COLUMN=PICTURE,IMAGE=(PICTURE),SIZE=(1 1),POSITION=(+0.5 +0.5),$
    END
  36. Click Run.

    The PDF file will open in the workspace.


iWay Software