Thursday, June 26, 2008

DB Adapter Tricks


1. Dealing with Special Characters in Table/Column name

Recently saw the issue on forum that Database does allow creating table or column with special characters inside the name e.g. $, #, etc.. Based on W3C standard (http://www.w3.org/TR/xmlschema-1/#cElement_Declarations) not all special characters are allowed in XSD element/attribute names. $ is certainly not allowed. Oracle Database adapter creates one-to-one mapping between database column/table name and element/attribute name.

Sample Table: I created table named "mytable" with following script:

create table mytable (
id int,
company varchar2(100),
my$comments varchar2(100),
processed varchar2(100),
processed_time date
);

a) Custom SQL : No toplink mapping is used, in this case conversion from $ value to _ is done automatically. Means, when DB adapter creates XSD file it creates attribute with my_comemnts. That's cool, no work.

b)
Insert/Select operation: That uses Toplink mapping which is basically <<SERVICENAME>>_table.xsd file and <<SERVICENAME>>_toplink_mappings.xml file. We can change _table.xsd file and convert my$comments to my_comments as shown below:

Only these changes are required in order to make things work. The same thing can achieved for table name and other types of special characters.






2. Dealing with Dynamic Queries - Query By Example

For simple use case, you can always use custom query with multiple parameters e.g. "select * from TABLENAME where COL1 = ?1 and COL2 = ?2". It works perfectly fine and input parameters shows up in XSD and also in wsdl contract.

Sometimes, some richer query is required, usually the dynamic where clause. Here is the example:

sometimes, you need query, e.g. select * from tablename where col1 = ?1
sometimes, you need query, e.g. select * from tablename where col1 = ?1 and col2 = ?2

Here you can see the where clause is getting build dynamically. It is supported by Oracle DB adapter and it is called Query By Example. It was very well supported in 10.1.2. but somehow it got deleted by some Jdev developer, the good thing is that it is still supported at runtime and you can make small changes to SELECT operation to convert it into Query By Example.

What it does: It takes lets say object as input (e.g. Employee), and returns collection of Employee as output. Here the trick is, where clause is created based on Employee attribute you provide. So if pass null Employee object it would "select * from Employee" querey. If you provide Employee input with Company name "Oracle", it will return all employee matching company name oracle...

How to implement it:

I created DynamicQueries BPEL process and DBSelect partnerlink which does simple SELECT operation (not custom sql), and no parameters on table name Test. Now, we need to modify two files:

DBSelect_table.xsd: Create an Element called Test.


DBSelect.wsdl
: Specify keyword called IsQueryByExample="true" in your JCA operation as shown below:

That's all it needs. You need to create input variable of Element "Test" and assign it to Partnerlink Input variable and deploy the process.





3. Inserting multiple records in Database in single transaction

Well, I know that is supported out of the box and nothing you need to do special to achieve it. If you create database adapter in your BPEL process with INSERT operation it is exposed as collection of object as Input. You can use XSLT to assign that Collection and all records will be inserted in one atomic transaction.


Working example for all three can be found here.



6 comments:

Anonymous said...

Dear Chintan,

I am writing to you to ask you help me to resolve my problem in ODI.

I use Oracle Directory Integration Platform and provisioning service to synchronization of users from ODI to DB and vice versa .but it does not work properly, ODI cannot sync with DB. and below lines are shown in log files.

Updating the Status..
Setting Event Success Count : 0
Setting Event Failure Count : 0
Propagation Status : SUCCESS
Last change Key Set to:2800
Profile status Update - SUCCESS
Cleaned/Closed Readers and Writers
Initialization - Starting for Mode Bootstrap
Prov Reader - Initialize : Instantiating oracle.ldap.odip.prov.AppBootstrapEventReader
Provisioning Failure Retry Limit for App is : 1
Status Attribute in OID:orcluserapplnprovstatus;rasaApp_rasaapp
Default Subscription Status Attribute:orcluserapplnprovstatus;rasaapp
Timestamp attribute 20080713064905z
LDAP URL : (srv-metasearc.padl.local:636 cn=odisrv+orclhostname=srv-metasearc,cn=registered instances,cn=directory integration platform,cn=products,cn=oraclecontext
Specifying binary attributes: mpegvideo objectguid objectsid guid usercertificate orclodipcondirlastappliedchgnum
Connecting in SSL
LDAP Connection success
AppBootstrap searchFilter : (&(objectclass=orcluserv2)(|(orcluserapplnprovstatus;rasaApp_rasaapp=PROVISIONING_FAILURE)(orcluserapplnprovstatus;rasaApp_rasaapp=PENDING_UPGRADE)(orcluserapplnprovstatus;rasaApp_rasaapp=PROVISIONING_NOT_REQUIRED)(!(orcluserapplnprovstatus;rasaApp_rasaapp=*))))
Search Time : 0
App Bootstrap Search Successful for application : 'null'
Intialized the App Bootstrap Event Reader.
Prov Reader - Initialized
Prov Writer - Initialize : Instantiating oracle.ldap.odip.prov.LDAPEventWriter
LDAP URL : (srv-metasearc.padl.local:636 cn=odisrv+orclhostname=srv-metasearc,cn=registered instances,cn=directory integration platform,cn=products,cn=oraclecontext
Specifying binary attributes: mpegvideo objectguid objectsid guid usercertificate orclodipcondirlastappliedchgnum
Connecting in SSL
LDAP Connection success
Retreived Factory from Profile..
Prov Writer - Initialized ..
Initialization - Ending ..
Event Propagation - Begin..
More Data Found..
Reader finds some data...
More Data Found..
Getting Users for the BootStrap cases..
More Data Found..
Processing User - cn=orcladmin
UserCreateTimeStamp 20080713064639z
Application TimeStamp 20080713120917z
Upgrade Timestamp 20080713064905z
Current status : null
Upgrade Case..
Need to Generate Upgrade Event
Current User FailCount : 0
[fine] Inside Generate Events..
[fine] Object Type identified as : USER
[fine] LDAP Changetype : ADD
[fine] getObjTypeRules:Processing Event Defn (0) - ,Object Type:ENTRY
[fine] getObjTypeRules:Processing Event Defn (1) - ,Object Type:USER
[fine] getObjTypeRules:Matched Object Type :USER
[fine] getObjTypeRules:# Event Rules:0
[fine] EventEngine: No ObjectType Rules for :USER - changeType : ADD
[fine] EventEngine: # Generated Events : 0
More Data Found..
Processing User - cn=PUBLIC
UserCreateTimeStamp 20080713064639z
Application TimeStamp 20080713120917z
Upgrade Timestamp 20080713064905z
Current status : null
Upgrade Case..
Need to Generate Upgrade Event
Current User FailCount : 0
[fine] Inside Generate Events..
[fine] Object Type identified as : USER
[fine] LDAP Changetype : ADD
[fine] getObjTypeRules:Processing Event Defn (0) - ,Object Type:ENTRY
[fine] getObjTypeRules:Processing Event Defn (1) - ,Object Type:USER
[fine] getObjTypeRules:Matched Object Type :USER
[fine] getObjTypeRules:# Event Rules:0
[fine] EventEngine: No ObjectType Rules for :USER - changeType : ADD
[fine] EventEngine: # Generated Events : 0
More Data Found..
Processing User - cn=User1
UserCreateTimeStamp 20080713124312z
Application TimeStamp 20080713120917z
Upgrade Timestamp 20080713064905z
Current status : null
Current User FailCount : 0
[fine] Inside Generate Events..
[fine] Object Type identified as : USER
[fine] LDAP Changetype : ADD
[fine] getObjTypeRules:Processing Event Defn (0) - ,Object Type:ENTRY
[fine] getObjTypeRules:Processing Event Defn (1) - ,Object Type:USER
[fine] getObjTypeRules:Matched Object Type :USER
[fine] getObjTypeRules:# Event Rules:0
[fine] EventEngine: No ObjectType Rules for :USER - changeType : ADD
[fine] EventEngine: # Generated Events : 0
App bootstrap events generated successfully. Number of events: 0
Events Read from Source : 0

Events to write 0
Process Event Status for 0
Not calling status processor
Event Propagation - End ..
Updating the Status..
Cleaned/Closed Readers and Writers

I am thankful for your great consideration in advanced. I look forward to hearing from you as soon as possible.

Unknown said...

This has been really useful. specially when you may or may not have the where condition and this post solves that problem.

Anonymous said...

Two questions:
1) Why does JDev 10.1.3.3 not support QueryByExample any more?
2) Does the DB-Adapter support QueryByExamplePolicy to set the where clause operator to "like" instead of "equals"?

Anonymous said...

Dear Chintan,

This is regarding building dynamic where clause using QueryByExample property. I need to retreive data for multiple id's for a column from a table which I will get at runtime. But, with the above property I am not able to set multiple values for a single column. Can you suggest me how to get records by setting multiple values for a single column or how to append "AND" to the query so that the other values can be set in the same way.

Thanks in Advance.
Ravi

IT said...
This comment has been removed by the author.
User said...

Thanks for your information.. But i am facing issue while doing a special character insert into database through assign using database adapter in bpel. I have a string input, that has $ in it.. for eg '^(O_|(o_)?DC_U\.(i|I)$'.... Here everything goes into database if i pass it as '^(O_|(o_)?DC_U\.(i|I)' but with $ appended.. the insert doesnt work... . Could you please help me on this.

Thanks