update using sql - jdbc

[hilde.noyez]

IDMS network - Update using SQL - jdbc

Currently we have an SQL layer on our networkschema allowing us to read the IDMS database.
This is used for ODBC connections but no updates are done.

Currently we are looking for a possibility to update our IDMS network database through java application by using the JDBC driver. 
Up to now we have no foreign key specifications on our sets which will be needed to allow updates through SQL.

Has anyone experience with updating IDMS network database by using SQL?
 

[Legacy User]

Hilde,

We have been using JAVA with SQL and JDBC drivers for a while here.

As far as queries, we use dynamic SQL all the time.  It works fine.

When it comes to updates, we are using a SQL procedure.  That means we have a unique SQL procedure that can call any ADSO dialog to perform specific updates.  That way we are not restricted on the physical structure of the IDMS network from the SQL point of view.

Basically, the SQL procedure is a table connected to a dialog.  The dialog processes the values in the table passed by the JAVA program in a WHERE clause.  The dialog does all the work and returns an appropriate error code and message to the calling JAVA program through the SQL procedure.  

I would refer you to the SQL Reference manual on how to define SQL Procedures.

If you need more details on how we do this, please let me know.

Claude Ferland

Contractor at WSSC IT

 

 

 

[dilda02]

Hi Hilde,

You'll want to use the Type4 version of the IDMS JDBC driver for your Java application... It is by far the most robust and performant version of the IDMS JDBC driver. With respect to performing updates to IDMS Network databases using SQL... There have been a number of sessions given on this topic at CA World over the years.  I'll attach a zip file to this reply with a couple of these sessions. The zip file contains the following files:

1. "Implementation Techniques to Modernize your CA IDMS Database - CA World 2007.ppt": This session outlines the problems associated with updating Network DB's and the solutions than can be employed. This includes the use of IDMS SQL extensions, Views, Procedures, Tables Procedures, the addition of Foreign Keys, etc. There are some good speaker notes in this slide deck that you'll want to have a loot at.

2. "IDMS Modernization - Connections Article Summer 2007.pdf": This issue of the IUA Connections publication contains a nice article written by Dave Ross entitled "Modernize CA IDMS for SOA". It covers many of the same topics as the CA World presentation from the perspective of Web applications development based on a Service Oriented Architecture.

3. "Modernizing CA IDMS for Todays Applications - CAWorld 2010.pptx" - 2010 version of the same session contains a nice table comparing the various techniques that can be employed for updating Network DB's using SQL and contains several slides on the use of SOAP.

4. "Modern Application Development with CA IDMS - CAWorld 2011.pptx" - Latest itteration of this session again covers many of the same topics with Relational to Network mapping and extends the discussion to the use of JPA (Java Persistence API) and Hibernate.

You may also want to have a look at the "IDMS SQL Programming Guide", for information specific to IDMS SQL (including access to Network databases), as well as the IDMS SQL Reference Guide.   Of course, if you have specific questions or problems please feel free to open an issue with IDMS Support.

I hope this helps,

Dave

Attachment(s)

IDMS Modernization Techniques.zip   3.73 MB 1 version