Direct insert of data into the database (into the WFelements table)

0

All operations of modifying or creating data directly in the database are not supported and prohibited by the license agreement.

Hi Radek,

even so there's nothing to add to Michałs answer I'm curious why you want to do it at all

If you need to import/migrate data you would have two options:

1. Upload the data to an item list via import from Excel and start workflows for each row. Maybe with an invisible path to move the workflow directly to the final step.
2. If the data is accessible using a data source, you could use this instead of the item list approach. It could also be an option, to create an own database, where you store the necessary data and use this one.

Best regards,
Daniel

I'll add third approach -
you can use global cyclic action with start subworkflow action.

Basically you write the select for data you want to import with mappings which column from your source is which field in Webcon.
Add some identifier field in webcon, and filter out those that are already existing in bps. You should also limit it with a TOP and make that action repeat x times, so it won't bps won't ddos itself.

I think it's the closest to directly inserting, but without all the need to think about connected tables like WFHistoryElements, WFElementDetails, WFHistoryElementDetails and who knows what else :)

Regarding the question, why would I want to import data directly into the database. I have data in an old database in a data model with many tables with 1-to-1, 1-to-many, many-to-many relationships, and at least 3 levels of table dependency.
I thought that the easiest way would be to write sql code that will properly select and combine data into a flattened model (WFElements), which I will add to the webcon database. In addition, I also have objects to add like a list.

The proposed approach #1 may be too complicated for many different relationships... Is there an extended guide for complex migrations?
I don't understand approach #2, can you elaborate?

Hi,
regarding the 3rd approach, is there any guide, instruction, or thread on the form that describes it in more detail?
What do you mean limiting with TOP (what is TOP)?
This may actually be a very close approach to direct insertion.
P.S. To put it simply - after remodeling the "view", I have to add about 40,000 lines of main objects. Plus at least about 100,000 rows (as a list to selected main objects)

You can register elements with rest api: https://developer.webcon.com/docs/rest-api/
Just implement a simple script that iterates through your dataset and calls the appropriate rest API method for each row.

By TOP i mean

SELECT TOP(for example 50)
SomeColumns,
OriginalIDColumn
FROM
YourTable
WHERE
OriginalIDColumn NOT IN ( SELECT WFD_AttributeWithOriginalIDColumn FROM WFElements WHERE (logic to catch only documents from your process/workfllow/doctype))

It requires you to add techical attribute, where you will keep some unique identifier, to check if record is already imported.

You have to split the data in smaller batches, so execution time wont result with timeout exception. Depending on complexity of the form, and "power" of server it may require different settings.

Make the cyclic action repeat every 10-30minutes, but first make sure, that where condition is set properly so it won't loop indefinitely.

Hi,
I tried option 2 for a small chunk of data and it works fine, although for tens of thousands of rows it may crash webcon. Maybe dividing the data into pieces, as my colleague Maksymilian Stachowiak suggested, can help, but in a manual way it defeats the purpose.

Do you have an idea to import data like this together with item list fields (WFElementsDetails)?

This solution requires writing a program in C# (uncomplicated code) that will read data from a foreign database and insert it into the Webcon database.

1. Is it possible to add 1-to-many bindings here to add more items to the Wbcon database with related item lists (WFElementsDetails)?
2. Can such a program be run on a server with Webcon without any problems?
3. Is it worth trying the run api post method by t-sql, like eq. https://www.zealousweb.com/calling-rest-api-from-sql-server-stored-procedure/

1. Yes, here is a link to the public API documentation: https://developer.webcon.com/2023/resources/rest_api5.0/?PageSpeed=off#tag/PublicApiElements/paths/~1api~1data~1v5.0~1db~1 %7BdbId%7D~1elements/post
There is a collection: itemLists.
2. The server does not matter much here, a similar script can be run from any machine or even your own workstation.
3. I've never tried this approach, so I don't know if it would be possible. You can also use SQL Server Integration Services to build a solution that exports data from SQL tables to the REST API service.

Turning on OLE Automations on SQL Server might increase the attack surface of SQL Server, so don't forget to turn it off if you'll go that way.
1. It's possible to map values to list items - you can see it in the docs here:
https://developer.webcon.com/2023/resources/rest_api5.0/#tag/PublicApiElements/paths/~1api~1data~1v5.0~1db~1%7BdbId%7D~1elements/post
2. Server/desktop doesn't really matter - just make sure that executing system have access to both servers - webcon, and sql one.
3. To use method with REST API you don't necessarily need to write C# code, you could use any language, even scripting ones like powershell/bash.

@Edit
Seems that Michał was little bit faster ;)

Option no 3. A very interesting solution. I'm also testing the solution with the cyclical actions you mentioned (it's described here https://community.webcon.com/posts/post/cyclical-actions/106), but I'm getting a strange error. As a single action staret on a path it works fine, but the same as a cyclical action doesn't work anymore. There is an error like "No suitable match found for: Xxx". This is problem with matching Xxx from dictionary/fixed list. But then it worked just like a regular path action.

Error stack:
Error occured while executing action New action1 (ID=118) from recurrent cycle New cycle (ID=2)
Message: Validation error, Reason for checking (ID: 2214) No match found for: Xxx
Requesting Service/Institution (ID: 2215) No matching match found for: Yyy
Type: WebCon.WorkFlow.Common.Exceptions.WorkFlowException
Source: WebCon.WorkFlow.Base
StackTrace: at WebCon.WorkFlow.Base.Actions.StartManyWorkFlows.CreateMoveToNextStepErrorMessage(IWFAction wfAction, String& message, String& logMessage, MoveElementToNextStepResult result, ElementForm newElementForm)....

Hi Radek, sorry for late response - unfortunatelly I've never seen this error and it's hard to replicate without any information about configuration.

I wrote whole article on that topic in the meantime - maybe it will help :)

https://blog.lumenn.pl/importing-data-to-webcon-1/
https://blog.lumenn.pl/importing-data-to-webcon-2/

Hi,

@Radek
I'm sorry too. After coming back from vacation there was just not enough time.

If you still have the issue you could check the following:
No match found for: Xxx
Is probably for a choose field which value does not exist in your source or is not unique.

@Maksymilian
Congrats to your new blog. :)
Do you mind when I add a link to yours on my blog?

Best regards,
Daniel

@Daniel, of course you can, thanks! I'll add link to your's too ;)

Sorry for misusing the thread but I don't have another option.

@Maksymilian
Do you have an LinkedIn account? I've seen your Facebook and Twitter icons, but I don't have either one.

Best regards,
Daniel