Existing Database Integration

Hi.

I do not know if I can get your support since I am using the free version of the platform.

I am trying to integrate with a SQL server database that already exists. I created the link in the service center and thereafter ran the wizard in the Integration Studio "Import Entities from Database" that failed in the fourth step:

Module Name:	Web Service
Message:	Invalid database object name 'information_schema.referential_constraints'.
Stack:	em ssServiceCenter.Actions.WsIntegrationStudio.WebSrvcIntegrationStudioEntityIntrospectionGetTables(HeContext heContext, String inParamusername, String inParampassword, RCEntityIntrospectionServerRecord inParamserver, RCEntityIntrospectionDatabaseRecord inParamdatabase, RLEntityIntrospectionTableRecordList& outParamtables) em ssServiceCenter.WebServices.IntegrationStudio.EntityIntrospectionGetTables(String username, String password, WORCEntityIntrospectionServerRecord server, WORCEntityIntrospectionDatabaseRecord database, WORCEntityIntrospectionTableRecord[]& tables) -Env- eSpaceVer: 0 (Id=1, PubId=1, CompiledWith=5.1.0.22) RequestUrl: https://localhost/ServiceCenter/IntegrationStudio.asmx AppDomain: /LM/W3SVC/1/ROOT/ServiceCenter-1-129324701289760955 FilePath: C:\...\PS\running\ServiceCenter.0396478449\ Locale: pt-PT DateFormat: yyyy-MM-dd PID: 5488 ('w3wp', Started='25-10-2010 08:48:38', Priv=156Mb, Virt=505Mb) TID: 3 .NET: 2.0.50727.4927

Thanks.

Best Regards,
Luis

Hi Luís, and welcome to the OutSystems community forums.

Of course you can get our support, regardless of the version of the platform you're using! After all, we're here to help.

From the look of the error you have in hands, it does look like a technical problem - either a linked server configuration, or a proper bug.

The first thing I'll ask, is what SQL Server version of the database are you linking to?

The second relates to the community edition version. Are you using the latest 5.1 community edition version? If not, I would suggest updating it first.

If you are, then what I may suggest is that you download the latest Integration Studio package (standalone) here. Maybe it will have that specific bug fixed.

If not, unless someone has already gone through such an issue, I will suggest you submit the issue to our support department, at support@outsystems.com , since there's a chance they have already heard of such an issue, and will probably either have a fix for you or be able to direct it to our maintenance department.

Let me know how this goes, and if you're able to get this to work.

Regards,

Paulo Tavares

Hi Paulo.

Thanks for your help.

I'm trying to link to SQL Server 2008 Express Edition instance and i'm using the lastest version of community edition (5.1.0.22).

I will report the situation to your support department as suggested.


Best Regards,
Luis

Estou com o mesmo problema e descobri que o INTEGRATION STUDIO está tentando checar a tabela com nome em minusculo "information_schema.referential_constraints "  porém o SQL 2008 só funciona o acesso a esta tabela em maiúsculo "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS",  pode testar no SQL

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  --assim funciona
SELECT * FROM information_schema.referential_constraints --assim não funciona

 
 Olá Fabio,

Bem-vindo aos foruns!

Tudo indica que a base de dados remota onde está a tentar ligar está configurada como case-sensitive, uma configuração não suportada pela Agile Platform.

Cumps,


Miguel

I'm bumping into the same problem, MSSQL is Latin1_General_CS_AS.
Will this ever be supported?

I have the same issue, but I cannot read Portugese.  Can someone translate what the final answer to this issue was in English?

Hi Kevin,

What I said above (in Portuguese) is that Fabio's problem (whereby Integration Studio seemed to be unable to check the information_schema.referential_constraints table because SQL could only query the table if we used its name in UPPERCASE) seemed to indicate that the SQL Server DB he was trying to reach was configured as Case Sensitive, which is not supported by the OutSystems Platform.

Cheers,


Miguel

Wow Miguel,

Great response time!   Thank-you.   I will have our DBA check the settings and have this corrected.
I will update with the results.

Thanks again!

Kevin

Hi Miguel, how are you?

Sorry to bother, but my curiosity was stronger about this tecnical stuff..
Can you try to explain with a little more detailled information the core reason to support only that kind of letters Case?
Just a geek curiosity :)
 
Cheers and nice to see you here,
Gonçalo M.

The DB Schema that I am referencing happens to be a Lawson schema using the coalition: Latin1_General_BIN.
This I understand is Case Sensitive.

We are located in the US. What coalition would you recommend we use?  I was unaware of this when we did our initial configuration/install.  The Lawson Windows platform is new for us as we are converting from AS400.
We are still in the building/testing phase.

You can find the recommendations for which coalition to use in the OutSystems Platform Installation Checklist.

But in a nutshell: any case insensitive / accent insensitive should do fine.

Also, if you let Configuration Tool do the work for you, a database will be created with a correct coalition.

Kevin -

We have worked on a Lawson integration too, talking to an AS/400 machine. We ended up using linked servers, synonyms, and views in SQL Server to provide read-only access to the data, and created Web Service calls on the Lawson side for updates. For a few queries, we needed OPENQUERY to make our calls because of collation issues. There is now an iSeries connector for OutSystems that we are exploring.

I highly recommend doing as much as possible through API calls, and NEVER doing updates through the DB directly. Lawson is a big system, trying to mess with its data is not a good idea, even if you are 100% certain you know what it "should" be.

J.Ja

Let me give you a quick update of our systems:
We have been on Lawson in the AS400 environment for 10 years,currently on the latest release.
We are converting the Lawson Environment to Windows.
I aggree on not updating DB tables directly, but the interface tables are ok because that is basically what they are ment for. We also use the Addin tool and batch methods for plugging data into interface. We also use web services for various functions/interfaces etc..
My issue is the when trying to link to the Lawson DB via Outsystems I'm getting the "Invalid database object name 'information_schema.referential_constraints." error.  Based on information in the thread it has something to do with the colation not ready table names with all upper cases.  Looking at the lawson schema, I see there are defined views with all upper case letters and this appears to be where my issue arises.  I'm not sure if Lawson would want us to change the colating sequence of their DB anyway. 

This problem has existed for some time , has OutSystems come up with a better workaround without me having to alter Lawsons DB? 

Kevin -

No, there is no solution to that issue (I went through this a few days ago).

Your best solution is to use the iSeries connector from the Forge for direct access, or to use a linked server in SQL Server.

J.Ja

Thanks Justin.   I am using the DB2 Connector.  The Linked server is where I am having the issue :(.

I need to pull data from ISeries Lawson Interface table to Windows Lawson interface table for while until we completely migrate at our own pace.

Basically we are modifing our in-house AS400 Lawson interface apps to create an entry in a table that outsystems will monitor.
We will then pull that batch from AS400 and plug into windows, then delete the batch from AS400.

This way we can leverage our existing in-house interface apps and slowy build the coresponding OutSystem apps on our own time.

Kevin -

That's odd, we didn't have that problem with our linked server... but we're also using Advanced Queries against the Linked Server with an OutSystems database connection set up. We tried to use Integration Studio against the Linked Server and had issues with synonyms, but we *could* use it to use views for *read only* work. Basically, as soon as you let system generate the SQL, it runs into these issues.

I have a jam-packed week, but I would not mind if we can grab a couple minutes on a call/screenshare to get you headed in the right direction.

J.Ja

Hi Justin,

If I correctly understood your post, going with Adv.Query INSERT/UPDATE SQL statements will get us into those sort of issues, is that it?

Let me give you an example, while running the following query we get the bellow error:

 

...by the way, the above query works fine when run into SQL Server Management Studio.

Pedro -

Correct. We found that the linked server route was fine for reading data, but when writing data, we had a number of issues. It was best to find another approach. In our case, the system we were integrating with exposed a Web service that we could use for updating data.

J.Ja

Thanks for the quick reply, Justin.

Web services are not an option as far as I can tell. We're currently considering going for OPENQUERY implementation, though we'd rather avoid it (...TrueChange unfriendly) :)

Pedro -

Even OPENQUERY was a problem for us. :(

J.Ja

SOLUTION:  The above UPDATE SQL statement works in SQL Management Studio because it was not wrapped in a transaction.  You can duplicate the error we get from the Outsytems Platform by wrapping the UPDATE or INSERT Statement in a BEGIN TRAN and COMMIT TRAN:

BEGIN TRAN
UPDATE ....
COMMIT TRAN

The reason you get the error is because the OLEDB  Provider you are using in your Linked Server connection does not support Two Phase Commitment control (or Distributed Transactions).  The platform is always  wrapping it in a transaction and your OLEDB Provider must provide this transaction interface.  

You can use the Microsoft OLEDB Provider for DB2 BUT it must be version 4.0 AND you must be running SQL Server 2012.  It will not work with SQL 2008 R2.  I couldn't figure out how to configure the IBM OLEDB Provider for Two Phase Commitment Control or Distributed transactions.  The good news is that there is a third party software provider that has an OLEDB Provider for DB2/400 that supports two phase commitment control.  It is Hit Software.  www.hitsw.com
The product is their HiT OLEDB Server/400 Developer Edition v6.0.  64 bit edition. You must configure it using their ToolBox and create a udl file, specifying Rowset Locale and Use Two Phase Commit in the Advanced tab.  Then in your Advanced SQL action, you must include the statement SET XACT_ABORT ON prior to your INSERT/UPDATE/CREATE.  It is advised to set it off after by a SET XACT_ABORT OFF statement.  
This works successfully for us.  It still doesn't allow the entity import feature in Integration Studio.  I haven't found anything that enables that feature yet.  We define our entities manually into Integration Studio.