Using stored procedure to update data set

  • 102 Views
  • Last Post 4 weeks ago
chaseernst posted this 19 September 2018

I am wondering if there is a way that I can use a script to update a dataset within FS12.

 

To explain further, what I would like to do is the following:

Call the script once the document has been scanned

The script will access our database and call a stored procedure (I know how to do this part)

Take the data returned from the stored procedure, and insert this data in the the dataset in Abbyy.

 

  • Liked by
  • Ola Thuresson
Order By: Standard | Newest | Votes
Ekaterina posted this 03 October 2018

Hello,

You may use the DataSet object. Please this link: http://help.abbyy.com/en-us/flexicapture/12/developer/script_update

chaseernst posted this 03 October 2018

I have looked into this, but the documentation isn't very helpful. It doesn't explain how to utilize the IDataSet property. Is there an example available, or better documentation?

  • Liked by
  • Ola Thuresson
Ekaterina posted this 10 October 2018

Hello,

Some scripts' examples that deal with IDataSet object are shared on this forum, for example:

http://www.capturedocs.com/thread/scripting-idataset/

However, if you need more detailed assistance in creation of a script for your needs, please contact your regional Professional Service.

Ola Thuresson posted this 11 October 2018

Hello,

Tson

Ola Thuresson posted this 11 October 2018

This us how I trigger datset update from a bat file wihch I run once an hour, I can also send you exmaple scripts on datset updates thats goes into the document defintion.

 

batfile:

 

C:

 

cd C:\Program Files\ABBYY FlexiCapture 12 Servers

 

FlexiBRSvc.exe please update dataset "http://thuressonsm/3/Thuresson"

 

 

Tson

  • Liked by
  • chaseernst
Ola Thuresson posted this 11 October 2018

I also have examples script but they seem to large to add here send me your mail and I will send you my scripts in C# and vb.net

 

regards,

Ola

Tson

  • Liked by
  • chaseernst
chaseernst posted this 16 October 2018

I ended up getting an answer to the question based off of Ola's private message. Below is my script in C#.

 

            SqlConnection sqlConnection = new SqlConnection();

            sqlConnection.ConnectionString = "your connection string"

            SqlDataReader reader;

            sqlConnection.Open();

 

            IDataSet dataSet = ruleContext.DataSet("customersDataSet"); //ruleContext is the variable that is passed through the event handler. It is Interface type IRuleContext.

            List<string> dataList = new List<string>();

            //Setup the connection parameters

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = sqlConnection;

            cmd.CommandText = "your sql query";

 

            try

            {

                reader = cmd.ExecuteReader();

                IDataSetRecord rec;

 

                while (reader.Read())

                {

                    dataList.Add(reader.GetString(0));

                }

                sqlConnection.Close();

                for(int i = 0; i <= dataList.ToArray().Count() - 1; i++)

                {

                    rec = dataSet.CreateRecord();

                    rec.AddValue("Customer", dataList[i]); //need to specify the column name in the data set here.

                    dataSet.AddRecord(rec);

                }

 

            }

            catch (Exception ex)

            {

                sqlConnection.Close();

                FCTools.ShowMessage(ex.Message);

            }

 

I have this code running through a script applied to a specific field on the document definition. Although this code will update the data set properly, there are a few issues I have found. First, for whatever reason every script in the document definition will make this script execute, even though it is only specified on one control's rule. Second, the iteration of dataSet.AddRecord(rec) takes an extraordinary amount of time. To process 150 records it takes longer than 1 minute to run the for statement. 

Ola Thuresson posted this 17 October 2018

You should run dataset scripts from the document definition properties tab datasets. Abbyy has built in functions for maintaining datasets. Running dataset update from rulescripts will have bad affect on performance in recognition and working in the verification station. you could run dataset update in a separate workflow script stage,

These datasets scripts are triggered by "FlexiBRSvc.exe please update dataset WhatEverDataSet"

Some comment to you code...

...when closing the connection (the first close) use  if (cn != null) { cn.Close(); }

don't close the connection at the catch statment instead use 

finally

            {

                if (cn != null) { cn.Dispose(); }

            }

This will make sure that the connection pool is properly emptied.

 

Tson

Kostya Marushchak posted this 4 weeks ago

Hi Ola, 

Can you please share your script with me?

 

And  do you know how  to check last update time of dataset somehow?

Close