Question

Openedge ODBC and Workflow

Workflow Forms

Updated April 15, 2021

asked on June 21, 2017

I have created an ODBC connection to a Progress DB using OpenEdge 11.

I got a username “guest’ to use as log in.

Following all the steps provide by my client the ODBC connection works 100%

When using Laserfiche WF to query data, I get an error. Using Custom query in wf I do get data back from the look up, but it is all the data in db.

When using Forms to connect to the ODBC, I get the following error.

[LFF502-UnexpectedError] Inner exception: System.Data.Odbc.OdbcException Message: ERROR [42S02] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Table/view/synonynm "GUEST.CREDITOR"

So, when creating the ODBC connection using the username guest, it also creates a Schema called GUEST. Using this I do not get any data back using Laserfiche Forms or the Query data in WF

The Schema must be PUB (as per screenshot)

The other problem that I am facing is that the table columns name is created with hyphens in ( cr-number) for example.

How will I use these in the custom query in wf? I have tried to use square brackets ([]) to specify a column but no luck

1.png (14.08 KB)

| Download

1 0

Answer

SELECTED ANSWER

replied on June 21, 2017

This is a driver error, not a Laserfiche error, so we're not going to be able to provide much help.

The error message you got in Forms seems to be truncated so it's hard to say what's going on. Based on the error code, the Progress documentation indicates it's expected behavior and you should be using double quotes not brackets.

For the Guest/Pub schemas, you'll probably have to create a synonym in Guest's schema to point to Pub's table.

0 0

View 8 previous replies

replied on June 21, 2017

Thank you so much

I will go to the client and test and update the post

0 0

replied on April 15, 2021

Any updates to share on this? Been a minute since the original post.

0 0

replied on April 15, 2021

Hey @Chris, what type of issues are you having with querying a Progress database? The schema is quite different from a typical T-SQL RDBMS so your syntax will be quite different, even between SSMS and Workflow.

0 0

replied on April 15, 2021

Hi! I can't get Workflow or Forms talk to it via ODBC connection. It times out. I saw posts like these but they are 4 or 5 years old so I am hoping Laserfiche has made some headway since then with the ODBC driver, which is what I am using.

I am just now starting to dig into the tables, so I am only 3 weeks in at this point. I have been using WinSQL very successfully to write data back to the database (I have a test database).

I have heard many use cases for why we should export data out during off hours and then manipulate it. Then add it back in during off hours again. Have you been able to write back in realtime? If so, how did you do it?

Thanks so much :)

0 0

replied on April 15, 2021

I actually haven't had a use case to append data TO a Progress database, but some things to consider:

If you are able install and configure the ODBC connection in ODBC Data Source Administrator as a System DSN, but are unable to use it in Laserfiche Workflow, it's possible the driver was installed on the incorrect server. You would want to install the driver on the server where LF SQL is installed on.
If you appending data to a database but are running into errors, it's possible the database tables have constraints that require you to populate (NON NULL, foreign key/primary key requirements, etc.). Check the database tables you are appending to in order to see if there are any requirements.

While it is best to export the data and manipulate locally before appending, it would depend on if you are uploading ALL of the data at once or doing a recursive statement to UPDATE rather than delete and upload.

1 0

replied on April 15, 2021

So, you have been successful getting Workflow to query OpenEdge via ODBC?

Have you done anything in the Financial side of the database?

Thanks for the info :)

0 0

replied on April 15, 2021

The best way that I found to communicate with Openedge is to do a linked server with a MS SQL server. Solved a lot of problems for me.

1 0

replied on April 15, 2021

Chris,

Yes, once the ODBC driver has been installed correctly, we would create a LINKED SERVER in SSMS so that Workflow can query against the database, OR you can query the database directly from WORKFLOW but that requires a slightly different syntax (using [ instead of "). Querying a Progress database requires a ton of syntax updates compared to T SQL.

Here is the OpenEdge SQL documentation that should help you. https://documentation.progress.com/output/OpenEdge117/openedge117/#page/dmsrf%2Fopenedge-sql-statements.html%23

1 0

replied on April 15, 2021

@████████, did you use SSIS? (Integration Services) or just SSMS? Could you write back in real time?

This is really exciting stuff!

@████████ thanks for the reference! This is extremely helpful.

0 0

replied on April 15, 2021

I have used both. I used SSMS to create the linked server in MS SQL and created views into the Openedge database. Those views were used to do the lookups with Forms and Workflow as it was much easier to work with.

I used SSIS for some of the writing back to Openedge, but I was also able to use ODBC for Workflow to write back to the Openedge database. It was not the easiest thing to figure out on how to write back to Openedge, but I did eventually get it to work.

1 0

replied on April 15, 2021

Wow, that is great news. I just now discovered that the OE ODBC driver will not work with the 'Query' activity in WF but WILL work with the 'Custom Query' activity.

Did you build in any way tracking validation, retries or change logging? I am concerned that there could be loss of data if there is a disruption in communication. Have you seen anything like that in your travels?

Thanks so much

0 0

Replies

replied on June 22, 2017

Hello Miruna

The complete error when i use the odbc via forms is as follows:

HTTP Status Code: 500
Business Process ID: 0
User: LFForms
IP: ::1
Browser: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.86 Safari/537.36
Business Process Name:

Stack Trace:
Caught exception: Laserfiche.Forms.CommonUtils.Exceptions.LFFormsException
Message: An unexpected error has occurred. [LFF502-UnexpectedError]

Inner exception: System.Data.Odbc.OdbcException
Message: ERROR [42S02] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Table/view/synonynm "GUEST.REMOVALS" cannot be found. (15814)
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Laserfiche.Forms.EntityModels.Data.ODBCDBConnector.GetDataSet(String cmdTxt, ICollection`1 parameters, OdbcConnection connection)
at Laserfiche.Forms.EntityModels.Data.ODBCDBConnector.GetTable(String cmdTxt, ICollection`1 parameters)
at Laserfiche.Forms.CommonUtils.LookupServiceProvider.InternalGetLookupResult(Expression`1 ruleCondition, Dictionary`2 lookups, IEntityContext context, String LogonUserName)

I used the double quotes now and I am able to retrieve data from the DB

I have asked if they can create me a view so i can use forms to do the look up

Thank you again for the information

0 0

replied on June 22, 2017

I have had a lot of experience querying Progress DB's and thought I would just give you a heads up that Progress is not your typical SQL. In order to query it there is specific syntax that is outside of the normal SQL that you would normally use. So just be ready for some frustration while you try to get it working.

0 0

replied on September 16, 2019

Hey Blake,
I'm currently working on a conversion project with a Progress DB and there is an "img" data type that isn't translating over to SSMS correctly. I've tried casting to SQL_VARBINARY but I am at a loss. Do you think you can help me out with this?

0 0

You are not allowed to follow up in this post.

Question

Question

Openedge ODBC and Workflow

Answer

Replies

Sign in to reply to this post.