Community

Using stored procedure to update data set

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.

 

Was this article helpful?

2 out of 2 found this helpful

Comments

11 comments

  • Avatar
    Ekaterina

    Hello,

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

    0
  • Avatar
    chaseernst

    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?

    1
  • Avatar
    Ekaterina

    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.

    0
  • Avatar
    Ola Thuresson

    Hello,

    0
  • Avatar
    Ola Thuresson

    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"

     

     

    2
  • Avatar
    Ola Thuresson

    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

    1
  • Avatar
    chaseernst

    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. 

    0
  • Avatar
    Ola Thuresson

    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.

     

    0
  • Avatar
    Kostya Marushchak

    Hi Ola, 

    Can you please share your script with me?

     

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

    1
  • Avatar
    Samarm

    Hi,

    I have written the script that works fine on visual studio but throwing error message when I tried to update the dataset. Can you please help me........!! Thank you so much for help.

    SQLite error: no such column: __Id: SELECT _Id, _Name, _Street, _City, _State, _ZIP, _CountryCode, _VATID, _EmailAddress, __City, __Street, __Name, __Id, __EmailAddress, __State, __ZIP, __CountryCode, __VATID, __FCInternalId FROM Main WHERE __FCIsInvalid=0

     

    Detail URL

    http://www.capturedocs.com/thread/how-to-call-sql-store-procedure-to-update-the-data-set/?postbadges=true

     

     

    Thanks

    0
  • Avatar
    NAGESHWAR PORLA

    Hi Team,

    Looking same requirement.

    Could you please share the code logic .

    I want to refresh the dataset dynamically (I mean in the script) and get the valuable info from dataset query once refreshed.

     

    0

Please sign in to leave a comment.