This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Connect from ModScript to an external DB using ODBC

Hello,

I would like to ask if it's possible to connect from within a ModScript to an external database (Oracle) to retrieve data from a specific table.

We have the following scenario: Another department asked us to automate SBM item creation for them. They will provide the data needed for the SBM item in their Oracle DB in an extra table. We should check this table regularly for new entries and if so, read this data and submit a new SBM item. 

Is this at all possible with ModScript to get a connection to this DB via ODBC and execute SQL queries to read the data?

We're using SBM version 11.08 at the moment.

Kind regards,

Andre

Tags:

Parents
  • Suggested Answer

    0  

    Hi Andre, 

    I have only seen ModScript accessing SBM database tables, but you can get the data through other options. 

    I believe that Oracle has a REST data service that can be used to create a REST service that you can access by creating a REST data source in Composer.

    On the SBM side, you can recreate an Advanced XML report that will query your Oracle database.  With this type of report, SBM sees the data as an auxiliary table even though it is not stored in the SBM database.  See KB https://knowledgebase.serena.com/InfoCenter/index?page=content&id=S141342.

    David

  • 0 in reply to   

    Thanks for your response!

    I was hoping to handle this directly through ModScript.

    Unfortunately, the Oracle Rest service is not an option here at this time. We have no control over this database and the team responsible has no idea what Rest even means. They can only provide a DB table.

    I checked your hint with the knowledge base article. We need to set also a status in the other database after submitting the new SBM item. If I understand correctly, I can only view the data as a read-only auxiliary table.

    Anyway, At the moment I plan to do this with a C++ DLL, which implements the ODBC handling and call this DLL from a ModScript. Hope that works for me.

    Thanks again!

    Andre

  • 0   in reply to 

    As long as you are creating a C++ DLL, you may consider creating a ASP or ASP.NET on your IIS server to return data from your ODBC as JSON.  Of course, you would want to protect access to this service by making it reachable only from the IIS/Modscript.

  • 0 in reply to   

    Thanks again for your help! I will check this with ASP and IIS.

Reply Children
No Data