Export to a database
To configure export to a database, perform the following:
- In the Document Definition editor window select the menu item Document Definition → Export Settings...
- Click Add... and select Export to ODBC-compatible database in the Type field.
- Specify whether export is obligatory.
- Specify whether images should be exported.
- Specify which documents (with and/or without errors) should be exported and click Next>.
- 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
For SQL 2012 SP4, 2014 SP2, 2016 SP2, 2017 database:
- Provider - OLE DB Provider for ODBC Drivers;
Note: Selecting "OLE DB Provider for SQL Server" may cause problems when working with certain data types and long fields.
- Driver - SQL Server.
For Access 2003, 2007, 2010 database:
- Provider - OLE DB Provider for ODBC Drivers;
- Driver - Microsoft Access Driver (Driver do Microsoft Access or Microsoft Access-Treiber).
Note: To connect the 64-bit version of FlexiCapture to the Access database, you will need to install the 64-bit version of the Access Database Engine x64 driver.
For Oracle 10g, 11g, 12c database:
- Provider - Oracle Provider for OLE DB;
- Selecting the driver is not required.
For details see Exporting to an Oracle database.
The provider and the driver must be available on the computer where export is performed.
- Provider - OLE DB Provider for ODBC Drivers;
- Select a schema from the drop-down list.
Working with schemas while using MS SQL
You do not have to specify a schema while working with MS SQL (export, caсhing, rule management). If you leave a blank field, the dbo schema will be used automatically.
Note: If a database does not contain tables, the dbo schema will not be specified in the list of schemas. To continue working with the dbo schema, select the blank value in the schema field.
- 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.
The Field Mapping dialog box on the left contains document sections and fields. On the right, you have to match them to the tables and fields on the database. If the database already has tables for data export, then in the Field Mapping dialog box you need to select a database table for each document section and a database table column for each document field. (Besides direct export to database tables it is possible to save data to a database by using stored procedures. For details, please see Specifics of configuring data export by using stored procedures of databases.
If the database has no tables for export, you can create database tables automatically by clicking the Create Tables Automatically button. After you click it, the program will create tables with a structure that you need for export. Document fields will be matched to table columns.
Field groups, fields with several instances and document tables are exported to separate tables in the database. A Primary Key and Foreign Key are used to link the main table to subordinate tables. In the main table, a primary key is added to each entry. In the subordinate table, each entry has a foreign key that records the value of the primary key of the corresponding entry in the main table. The primary and foreign keys are added automatically where required. You only need to specify the field to which the key has to be exported.
Select a column of a database table for each document field.
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.
- 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.
- 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.
- 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).
- 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.
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.
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:
- Role: Connect
- 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.