ORACLE resource connector using multiple tables - Sun Java System Identity Manager(Archived)

The Oracle connector is using a single table to exchange data.
Has someone already implemented a connector working with multiple tables in read/write bi-directionnal modes ?
(select * from Table A, Table B where A.key = B.key...)
Can store procedures be used as well ?
It is somewhat cumbersome to map a relational db using a flat model ;-?
Thanks for your help

I don't think that stored procedures are possible with the adapters
But I am sure SUN has already implemented custom adapters working on multiple tables with sql statements

SUN experts, have you done this ?
Do we need to use an API to do this ? which API ?
You help is very much appreciated
;-) ;-) ;-) ;-) ;-) ;-) ;-) 

I believe that there are two approaches you can take for this:
1) Create views on top of the database tables you need to access and continue to use the Database Table adapter (that's what you're using, right?).
2) Write your own custom adapter starting from that is in the REF kit. See the Adapter Development chapter in the Technical Deployment guide.

An educated guess would be to go with the view in Oracle since this is what oracle is best at!

Well to me, a view if only a flat aggregation of several tables for read-only purpose. It is ok to be used with a simple adapter to read data
However it cannot catter ?:
* db writes on the relational model
* advanced queries
In my case, we want IDM workflow to:
create new objects in the database, update them along with the workflows or provisionning events...
make queries on the db data model, to parse objects if necessary, deduct an approver name, compute escalation paths,etc...
Is that really possible with solution 1) views ?
Thanks for you help


How to update two tables at the same time using jdbc

Plz will anybody tell me what is the code or query in jdbc by which you insert entry in one table and simultaneously it goes in another table?
Thanks in advance 
JDBC is just a bridge to SQL databases. You might be able to design a trigger to do what you're talking about, but that would be a database thing, not a java thing 
You might be
able to design a trigger to do what you're talking
about, but that would be a database thing, not a java
thingAnother option would be an updatable view (google for usage), but it has limitations and should be used with caution. And yes, its a database thinggy and not java.
Or get a database with replication services. The Dbs run in parallel - an insert in one gts replicated to the other DB and vise versa. Oracle has such a product, as does, I suspect, most of the major Db vendors. 
Or make them a single unit of work and perform two insert/update SQL commands. they're called transactions.
Or, dare I suggest that you use a stored procedure, if your database supports such things?

Sun IDM and SQL Databases

Hi all,
simple question : has anyone successfully created a Database Resource in IDM, with multiple tables??
I am currently trying to create an MS-SQL database resource on my IDM system.
I know that this can be done using either Tables or Views.
The problem with IDM is that : it can reference only one View at a time (I don't know if this is the same with Tables, but I should imagine so).
As every database expert knows, Views do not allow you to modify (insert, update, delete) more than one base-table at a time.
To solve this problem, I created trigger each for insert, update, delete.
Here is the problem : my database contains 20 tables. Each of these tables is linked and interlinked via Foreign Keys and Constraints.
For some reason, IDM is giving me HELL.........I cannot do any thing (neither Insert, nor Update, nor Delete), because, apparently, IDM has a problem with performing actions simultaneously on several tables, which are linked together.
*(a) IDM does not recognize (or appreciate) when a Primary Key (like ID number) is auto-incremental*
*(b) IDM does not recognize Constraint-relationships between two or more tables.*
Just to be sure, I removed ALL the constraints and inter-relationships between my tables. Also, I removed the "Auto-increment" property on all my primary keys. The only thing these tables had in common were certain "common fields" (which I used to create the VIEW, such as : INNER JOIN this, on INNER JOIN that, etc, etc)
And then, I tried to use IDM to insert and update data.
Big surprise : it worked !!!! I was able to insert, update and delete data to these tables from IDM, no problem.
Needless to say, a database this size NEEDS inter-relationships, otherwise it is pointless. Makes no sense to perform Insert, or Update on database tables, if you have to do each one manually, because the tables are not joined by any Foreign Keys or Constraints.
My question is :
*(a) has anyone successfully a Database Resource (either MySQL or MS-SQL) on IDM, for a Database with a large number of tables?*
*(b) how could I go about creating a View (and the corresponding Triggers) to ensure that data manipulation from IDM (insert, update, delete) works properly?*
*(c) if this approach is not possible, how else can I create a functioning Database Resource in IDM?*
Any help/tips would be greatly appreciated.
I am not a guru in MS-SQL, but it has updatable views, google says. 
If you had looked more carefuly, you would probably have noticed that : a view can indeed be updated, yes! But ONLY ONE BASE-TABLE at a time!
As I mentioned in my posting................................ 
If you had looked more carefuly, you would probably have noticed that
: a view can indeed be updated, yes! But ONLY ONE BASE-TABLE at a
time!Sorry, I did not noticed that.
I would recommend you to write your own adapter. Believe me, it is simple. I did it for my current project, so I can probably help too. 
All I can suggest is taking a look at the scripted JDBC resource.
You can hide all the multi-table operations in the beanshell scripts run when creating or updating users.
We have managed to interface IDM with Efecte and Service Manager applications using beanshell scripts. Efecte certainly used a whole mass of inter-linked tables. 
The inbuilt adapters provided by the product work on the relational databases. I have integrated an Oracle database where requirement was to create account spread across multiple tables. Sun IDM gives us the Oracle Resource adapter, but it works only to create account in a single table. We had to extend the basic adapter and write our own to run multiple insert/update/delete statements in multiple java methods to create/update/delete accounts.
I hope this would be the solution in MS SQL db as well..

Can one entity object represent two db tables

Hi All, I'm currently modelling the schema for some new features in a banking application. We're adding some new account types and I've discovered a lot of crossover between different tables. For example a MUTUAL_FUNDS & EQUITIES account would share a lot of common fields, so I'd like really to put the common fields in another table INVESTMENTS. In the code would it be possible to have an entity object that can create/delete/amend a row consisting of a row represented by a row from each of the tables MUTUAL_FUNDS & INVESTMENTS? The main reason we'd prefer to do this is to prevent a lot duplicate columns across the db as we would expect with each revision the number of account tables to grow considerably. BTW, in the code we were also considering creating an abstract bean inherited by the concrete account entity classes. Any suggestions and advice much appreciated.
Yes, entity object can represent two or more tables as long as they are related.
Your table MUTUAL_FUNDS and INVESTMENTS should be related to each other.
ejb 2.0 provides facilities for using relationship. you can explore that too.
Hi VJ, forgot to put in my first mail that we're using EJB 1.1 (old project, new features). Also the schema is for the original project too, there are no actual relational constraints between tables, instead this is managed through the code as when the application is deployed on client site it hit's against a host system which may not contain any semblance to our db schema. 
Hi VJ, forgot to put in my first mail that we're
using EJB 1.1 (old project, new features). Also the
schema is for the original project too, there are no
actual relational constraints between tables, instead
this is managed through the code as when the
application is deployed on client site it hit's
against a host system which may not contain any
semblance to our db schema.Using DAO persistence layer might be a better idea in this case. 
I wish that were the case, last company I worked in used it extensively. Unfortunately due to backward compatability commitments, status quo attitudes & other issues have to stick with the structure that's currently there, very frustrating but nothing can be done about it. I guess I'll just have to have to stick to the one on one entity-table relationship and then come up with a drill down & merge algorithm.
Cheers for your input!
NO! Using CMP it is not possible! CMP bean can map only one DB table. BMP is another story (actually with BMP you can do everything you can do with pure JDBC).
I've never tried to map CMP on updatable view!?

How to map IDM to two db tables containing user accounts

Hi *,
In our database resource there are two tables containing user accounts, and we want IDM to hold some attributes from each one, but it seems IDM could only map to one table while configuring Database Tables, how could i get around with this? hope i make the question clear. Any ideas are appreciated!
Thanks in advance!
Hi ^].
how about defining two resources, one for each table and link them to he same role. Not ideal (e.g. for reconciling) but as far as I know the ootb IDM database table adapter does not support two tables. An other solution is to build a custom adapter. I do not know how much work is involved in that but it will 'fit' better into IDM.
Regards, Theo 
This is a definite limit with IDM.
You have a few different choices. therijn is right about one, you can simply have two resource adapters. I'm sure your tables map somehow internally. Another option is to build a custom adapter like he mentioned. Another option is to build a 'view' in the database (if it supports it). Database views can however be slow to update, so this might not be ideal if you are dealing with large amounts of data.
The other option, is If you are using IDM 7, take a look at the JDBC Scripted adapter. I haven't used it yet myself, but it is supposed handle multiple database tables.
Good Luck!
I am hoping that you can share the solution with me.I also need to map an IDM account to multiple database tables.How do I go about changing the resource adapter.Any changes in the UI?
I would use the Scripted JDBC adapter, do the SQL calls in the adapter, or write a Store Procedure for the DB and call that store procedure from the Scripted JDBC Adapter.
Using the Scripted Adapter you will have a very clean solution, with one resource adapter that will provide you with the attributes you need from the database.
However, you need to be able to program java, and use the java.sql and javax.sql libraries. There are very good examples provided in IdM 7.0 on how to use the scripted JDBC adapter, that shows some simple java code, which you can change or extend to fit your purposes.
To me, the easiest way seems to be have the DBA create a DB normal join right in the database. A join is where you have, for a lack of a better choice of words, a virtual 3rd table. The 3rd "join" table looks like a table with attributes of the two others presented how you want, acts like one but is really joined to the two tables you want. Then you only have one resource adapter (efficient and simple). 
I have checked that view does not work in IDM. It picks only the tables present in the database when you configure the database adapter.
Correct me if I am wrong.

Problems with database schemas

I'm new in Creator. I'm trying to connect to AS/400 databases. In my datasource I have severals schemas but only can see the tables and views in one of them, the others appear empties. Idon't have retriccions problems, what can Ido? 
If you want to see other database objects (tables, views,...) in other schemas, choose Modify Data Source from the right-click context menu on the data source, go the Schemas tab, click on 'Get Schemas' and select the schema(s) of your interest.
Hope this helps.
Please go through the tutorials titled "Working With Databases" This is available at :
First of all do you have the correct JDBC.. you can have it from clien acces.. then add the data source nd that�s it it�ll works smooth (i advice the use of JT400.jar) 
I have read previous replies, and all seems to I would do. But, if you are new to DB topics, this are more info>
A Schema, is a (can I say) logical grouping of a owernship relation among database objects.
Users could be part or represent a database schema. The schema stores the components owned by the application. In addition, the schema can store the database objects on which the components are based.
Is a good practice to create a schema for sets o related application, or for subsets within an application.
If you are connecting to a databse supporting schemas, you should log on using a user that belongs to a specific schema, the one that containsdatabase objects (tables, sp, views) that you need to use.