Using stored procedure to update data set

  • 63 Views
  • Last Post yesterday
chaseernst posted this 4 weeks ago

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 3 weeks ago

Hello,

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

chaseernst posted this 2 weeks ago

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 2 weeks ago

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 6 days ago

Hello,

Tson

Ola Thuresson posted this 6 days ago

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 6 days ago

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 yesterday

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 yesterday

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

Close