English (English)

Export to a database

To configure export to a database, complete the following steps:

  1. In the Document Definition editor window select the menu item Document Definition → Export Settings...
  2. Click Add... and select Export to ODBC-compatible database in the Type field.
  3. Specify whether export is obligatory.
  4. Specify whether images should be exported.
  5. Specify which documents (with and/or without errors) should be exported and click Next>.
  6. Specify export settings and click Next>.
    • Establish a connection with a database. Enter the connection parameters in the Connection string window or click the Setup... button and configure the connection parameters in the Data Link Properties dialog box.

      Tested providers and drivers

    • Select a schema from the drop-down list.

      Working with schemas while using MS SQL

  • You can check the connection with the database by clicking the Test Connection button.
  • Click the Create Tables Automatically button to automatically create new tables in the database, where data from documents will be exported. A separate table will be created for each document section. The structure and the format of fields in automatically created tables correspond to the set of Document Definition fields and their data types.
    Note: Before the export, ABBYY FlexiCapture queries the database for information about all objects of the schema. It is recommended that schemas with a small number of objects should be used for export. Otherwise creating tables can take considerable time (up to several hours).
  • If it is necessary to export data to existing tables of a database, establish a correspondence between the Document Definition fields and the database table fields using the Set Up Field Mapping... button.
  • In the Field Mapping dialog box specify to what tables and table columns of the database the program will export field values of the Document Definition.

    Details...

If a column, to which data from some field are to be exported, is missing in the table, it can be added to the existing table automatically. For this, it is enough to leave the correspondence of the document field to the database field with a similar name and with the comment (auto) in brackets. Or you can select a desired field with a mouse and click the Auto button.

You can also use the Auto button to create a new table in the database whose name will be similar to the section or table name of the Document Definition. If a new table, whose name and column captions are different from the Document Definition field names, must be created in the fields correspondence settings dialog, use the New button to create a table for each field.

Use the Move Up and Move Down buttons to manage the sequence order of columns in the automatically created table of a database.

Use the Format... button to configure parameters of saving typified fields, for example, the number of decimal digits in the field value of Number type or format and separators for a field of Data type.

Note: When exporting field of the Time data type, a corresponding column of DateTime type must be created in database table. By default the column of SmallDateTime type is created which results in error.

  1. If images should be saved (document images and/or field images of the Picture type), specify image saving options and click Next>.
    • Specify whether images should be saved to the database or as files.
    • If you choose to save images to files, specify a file naming template (click Edit...) and when files should be overwrite if their names coincide. For more information, see Export file naming options.
    • If required, specify the fields to be concealed upon export.

Note: Depending on the selected option, the contents of Picture type fields and document images can be exported to the file system or a database.
When exporting to separate files, the name and the path to a picture or image file are stored in the corresponding field of a database (it is configured when establishing the correspondence of fields). If a network disk is specified as a path to an export folder (for images, pictures, etc.), make sure that all stations working with this disk can access it. A special procedure allows you to convert a binary image from any database to a file.
Note: When exporting images to separate files, the length of a string storing paths to files may exceed 255 characters and an error message will occur. In this case, you should increase the character limit for the given column.

  1. If the option to save images was selected, specify a file saving format and click Next>.
    • Select the desired image format, quality, and color.
    • To change the resolution of the source images, select Change resolution to and select the appropriate value from the list.
    • If you select PDF or PDF/A as a storage format, you can create searchable PDF files.
  2. Enter an export profile name and click Finish.

Note:

  1. Pictures are exported to separate files. The name and path to the picture file can be stored in the corresponding field of the database (this can be set up when mapping fields).
  2. If the path to the target folder for images, pictures, etc. contains a network disk, ensure that all the processing stations working with the project have access to this disk.

Specifics of configuring data export by using stored procedures of databases

Besides exporting data to database tables directly, it is possible to save data to database by using stored procedures.

A stored procedure is a database object representing a set of SQL-instructions which is compiled once, and then stored on the server. Stored procedures can be used to perform numerical calculations and operations on character data, whose results can be assigned to variable and parameters, as well as to carry out standard operations with databases.

Advantages of using stored procedures over direct export to database tables:

  • Speed of working with a database
  • Possibility to use additional calculations
  • Ensuring data integrity

A sample of a shared procedure for MS SQL:

Create procedure Export( @userId as int, @lastName as nvarchar(255), @firstName as nvarchar(255) )
as
begin
 if Exists( Select Id from Users where Id = @userId ) begin
    Update Users set LastName = @lastName, FirstName=@firstName where Id = @userId
 end else begin
    Insert into Users ( Id, LastName, FirstName ) values( @userId, @lastName, @firstName
)
 end
end

When sending data from FlexiCapture to database by using the procedure above, the following actions are performed:

The three parameters passed to the input of the procedure are the fields of a FlexiCapture document:

  • an identifier (UserId);
  • the first name (FirstName);
  • the last name (LastName).

If there is no entry with such an identifier in the database table (Users), a new entry is added to the table. If an entry with such an identifier exists, the values of the first and last name in this entry are replaced with new values (the values of the FlexiCapture fields).

Configuring export by using stored procedures

In order to configure export by using stored procedures, it is necessary to establish a connection to database first (see above). Further, click the Set Up Field Mapping... to determine correspondence of document fields to parameters of the stored procedure.

In the Set Up Field Mapping... dialog box, select from the list of tables and database stored procedures a required stored procedure for saving data.

Unlike configuring export to columns of a database table directly, the fields proposed for export are parameters of a stored procedure. Procedure parameters are typified. For mapping it is necessary that the parameter type should coincide with the data type of the Document Definition field.

Map the document fields and parameters of the database stored procedure.

Note: If it is necessary to export not only separate fields from the document, but also tables and complex fields (e.g. fields with a few instances), you must set up correct indexes for connecting child entries with the main document data entry. Specify the return value (@RETURN_VALUE) or an output parameter of the stored procedure as an exportable primary key (ROW_INDEX). Thus, when exporting data, the procedure will write data to the database table and return the identifier of the current entry that will be passed to the stored procedure for exporting child entries.

Exporting to an Oracle database

We recommend using the authorization scheme that was used for users when exporting data to databases.

The following parameters must be specified in order to export data:

  1. Role: Connect
  2. Privileges:
    • CREATE ANY TABLE for creating a table
    • INSERT ANY TABLE and UNLIMITED TABLESPACE for exporting data

Note: you do not need any privileges for setting up the Field Mapping dialog box.

Note: If you are using an Oracle database, we advise against using field names longer than 30 characters in your Document Definitions. Longer names will be truncated when exporting to Oracle databases.

12.04.2024 18:16:02

Please leave your feedback about this article

Usage of Cookies. In order to optimize the website functionality and improve your online experience ABBYY uses cookies. You agree to the usage of cookies when you continue using this site. Further details can be found in our Privacy Notice.