Is it possible for a JDBC driver to update multiple tables?

Is it possible for a JDBC driver to update multiple tables in a target database?

In this pre-2015 thread (JDBC Driver to MS SQL - multiple table sync), Jim W suggested that drivers put data in the "driver table" and then the DBAs do their "DBA magic" (triggers, stored procedures, etc) to update other relevant tables.

Is that still the best approach?  I would really rather see a single driver responsible for these operations rather than multiple drivers, one for each table.

Liam

  • I am pretty sure one driver can manage multiple tables... Driver config, the place you specify the tables in play says this:

    <definition display-name="Table or view names:" name="sync-tables" type="string">
    	<description>Specify the names of the tables or views to synchronize.  You might need to schema-qualify these names (for example owner.table).  Use a semicolon, comma, or space as a delimiter for multiple values.  This parameter is useful if you need to reduce the number of table or view definitions cached by this driver, which can shorten driver start-up time as well as reduce runtime memory utilization.  It is also useful when synchronizing tables or views that reside in different database schemas (that is, are owned by multiple database users).</description>
    	<value>dbo.emg_notification</value>
    </definition>
    
    And the simple text:
    Specify the names of the tables or views to synchronize.  You might need to schema-qualify these names (for example owner.table).  Use a semicolon, comma, or space as a delimiter for multiple values.  This parameter is useful if you need to reduce the number of table or view definitions cached by this driver, which can shorten driver start-up time as well as reduce runtime memory utilization.  It is also useful when synchronizing tables or views that reside in different database schemas (that is, are owned by multiple database users).
    

    Then in the schema map, the class is mapped to a table.

    If you need data from one user to sync to multiple tables, you then map some fake object classes to the second/third/etc table, and then you need to add a policy to change the class of the attribute when it is changing.

  • Hey Jeff!

    That's somewhat encouraging... unfortunately, the tables we're looking at are a per attribute, not per objectclass, so it sounds like we'd need the objectclass workaround that you mention.

    We have a number of attributes that are effectively serialized objects, and some applications that would be happier accessing them as actual records in SQL tables than as json-like strings stored in LDAP.   We have a scripting driver that is handling it now, but it's slow.  We are considering other alternatives, going straight to the JDBC driver seems like it would be attractive.

    Liam

  • The driver expects an attribute to be tied to a class, and thus you Schema map them together.

    But you can manage this, by catching the changing attribute, and convert it to a second (third, fourth) object class, like class-name="Table2" and with the attribute.  That allows writing to it. 

    On a sync, query, mapping it back can be trickier, but you can handle this to merge it back together.

      

  • Can you provide an example of the table structure that you are trying to populate and the source attributes/classes? This sounds very doable but there are 10 different ways to do it. We need more information.

  • We have several attributes of what we refer to as "packed data".  These are multi-valued attributes that hold stringified objects.  alumstatus is our least complicated example, e.g.

    alumstatus: {campus=MAIN}:{degSchool=Arts and Sciences}:{aggDegree=AB 2013 AS}{classYr=2013}

    We have packed data for HR information, for academic information, account lifecycle information, etc.  Each attribute has a different structure (number of key / value pairs), but the stringified version is consistent in format.

    This is rich data, but hard to do useful things with when you're constrained to using strings.  We are integrating with applications that are much happier when we convert those strings into actual objects (in this case, rows in a SQL database).  The SQL structure we've been using has been a table per attribute, with a row for each value, and a column for each "key".

    CREATE TABLE "ALUMSTATUS"

    "DIRECTORYID" VARCHAR2(256 CHAR),
    "CAMPUS" VARCHAR2(1000 BYTE),
    "DEGSCHOOL" VARCHAR2(1000 CHAR),
    "AGGDEGREE" VARCHAR2(1000 CHAR),
    "CLASSYR" VARCHAR2(1000 CHAR),
    "PACKED" VARCHAR2(1000 CHAR)
    )

    We are using the directory identifier and the stringified object to construct the PK for each record.

    We had some existing python code to explode the data and talk to the database, so, as a quick win, we massaged that into a scripting driver.  It works, but it's prone to backups during periods of data churn.  We are discussing maybe splitting the components of the scripting driver into two parts - a REST driver and a web service... but it seems to me IDM has JDBC drivers.  Why not just have IDM update the tables directly? 

    We've had some concerns based on our perception that the JDBC driver would prefer to deal with a single object class talking to a single table.  These are multiple attributes on a single, locally defined (auxiliary) person class.  But it feels like if we're concerned about backlogs, and this data is managed by IDM, the quickest thing to do would be to have IDM update the tables directly over JDBC.

    Liam

  • On a sync, query, mapping it back can be trickier, but you can handle this to merge it back together.

    If this is a subscriber only driver, do we need to worry about that?

    Migrations should only be from IDM -> target

  • Should be ok.

    You can always simply inject SQL commands instead of using the mappings in IDM.

    When you need a Stored Procedure call to properly update the DB, that is the only real choice.