Highlighted
Absent Member.
Absent Member.
188 views

Issue when trying to read CLOB column from a JDBC Driver.


I am working on an enhancement on existing JDBC Driver on IDM 3.6 which
connects to Oracle 10g DB, In a given table on this DB I have to sync
the photos stored in CLOB column in the form of Base 64 to IDM. The
Driver's Publisher is running in Triggerless mode.

I am facing below issues with the CLOB column:


- Trying to get event when CLOB value is changed. Driver is able to
detect events on all other columns of the table except the CLOB one.
Is firing events based on CLOB column supported by JDBC Driver ?
- Assuming that the above won't work I modified the table to add
column to server as a flag and configured Driver to retrieve the value
of CLOB when an event is received. I am seeing that event in the logs
(when I am setting the flag in DB) but I am not able read CLOB column
value using read-src-attr value (on the publisher).
-

I have set the attribute mapping to flag as notify and attribute mapping
to CLOB as sync on Publisher Channel.

Can someone please guide me or throw pointers at what I should be doing
in this case

Thanks in Advance.


--
debimohanty
------------------------------------------------------------------------
debimohanty's Profile: https://forums.netiq.com/member.php?userid=1514
View this thread: https://forums.netiq.com/showthread.php?t=46889

Labels (1)
0 Likes
7 Replies
Highlighted
Absent Member.
Absent Member.

Re: Issue when trying to read CLOB column from a JDBC Driver.

I saw a note discussing large data types and it referenced using triggered publication and a query
back event. So some stuff to try:

If you can switch to a triggered event just to try it out and see if there is a limitation there
that would be handy.

When you get your flag attribute and you do a get src attr token for the CLOB, try turning the trace
level up to 25 (yes 25) and then post the trace of the actual query going back into the database or
whatever it is doing. I'm assuming from what you described that you are just getting a null value
back but I want to see the query.

Also what version of the JDBC driver shim are you running?


On 2/20/2013 10:54 AM, debimohanty wrote:
>
> I am working on an enhancement on existing JDBC Driver on IDM 3.6 which
> connects to Oracle 10g DB, In a given table on this DB I have to sync
> the photos stored in CLOB column in the form of Base 64 to IDM. The
> Driver's Publisher is running in Triggerless mode.
>
> I am facing below issues with the CLOB column:
>
>
> - Trying to get event when CLOB value is changed. Driver is able to
> detect events on all other columns of the table except the CLOB one.
> Is firing events based on CLOB column supported by JDBC Driver ?
> - Assuming that the above won't work I modified the table to add
> column to server as a flag and configured Driver to retrieve the value
> of CLOB when an event is received. I am seeing that event in the logs
> (when I am setting the flag in DB) but I am not able read CLOB column
> value using read-src-attr value (on the publisher).
> -
>
> I have set the attribute mapping to flag as notify and attribute mapping
> to CLOB as sync on Publisher Channel.
>
> Can someone please guide me or throw pointers at what I should be doing
> in this case
>
> Thanks in Advance.
>
>


0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Issue when trying to read CLOB column from a JDBC Driver.


Thanks Will.

I will go ahead and do some debugging with trace level 25. Hopefully it
will shed some light on the issue as I am sure that it is not a
permissions issue because I am able to use the user credentials which
the driver is using to do Read and Write operations on the Table.

The driver trace log says version 3.5.4. I will also try to see if there
is a patch available (I haven't done this until now as I thinking it was
more of a problem in how I had set it up.)

Lastly, I will get the trigger and table ready, this is a time taking
process as DBA at my place can take a couple of days to implement
things.

Will keep you posted

Will Schneider;225769 Wrote:
> I saw a note discussing large data types and it referenced using
> triggered publication and a query
> back event. So some stuff to try:
>
> If you can switch to a triggered event just to try it out and see if
> there is a limitation there
> that would be handy.
>
> When you get your flag attribute and you do a get src attr token for the
> CLOB, try turning the trace
> level up to 25 (yes 25) and then post the trace of the actual query
> going back into the database or
> whatever it is doing. I'm assuming from what you described that you are
> just getting a null value
> back but I want to see the query.
>
> Also what version of the JDBC driver shim are you running?
>
>
> On 2/20/2013 10:54 AM, debimohanty wrote:
> >
> > I am working on an enhancement on existing JDBC Driver on IDM 3.6

> which
> > connects to Oracle 10g DB, In a given table on this DB I have to sync
> > the photos stored in CLOB column in the form of Base 64 to IDM. The
> > Driver's Publisher is running in Triggerless mode.
> >
> > I am facing below issues with the CLOB column:
> >
> >
> > - Trying to get event when CLOB value is changed. Driver is able to
> > detect events on all other columns of the table except the CLOB

> one.
> > Is firing events based on CLOB column supported by JDBC Driver ?
> > - Assuming that the above won't work I modified the table to add
> > column to server as a flag and configured Driver to retrieve the

> value
> > of CLOB when an event is received. I am seeing that event in the

> logs
> > (when I am setting the flag in DB) but I am not able read CLOB

> column
> > value using read-src-attr value (on the publisher).
> > -
> >
> > I have set the attribute mapping to flag as notify and attribute

> mapping
> > to CLOB as sync on Publisher Channel.
> >
> > Can someone please guide me or throw pointers at what I should be

> doing
> > in this case
> >
> > Thanks in Advance.
> >
> >



--
debimohanty
------------------------------------------------------------------------
debimohanty's Profile: https://forums.netiq.com/member.php?userid=1514
View this thread: https://forums.netiq.com/showthread.php?t=46889

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Issue when trying to read CLOB column from a JDBC Driver.

All of the sudden I'm moving patching to the top of the list lol
http://download.novell.com/Download?buildid=OnmgJAuXWQ0~

There are a number of fixes for triggerless publication in a later version than you are running.
That could very well be the cause. The tricky thing with a CLOB is that usually the data is stored
elsewhere and the database just holds a pointer. So there could very well be something untested and
buggy about that. I just don't have an oracle database handy to test with.


On 2/20/2013 12:04 PM, debimohanty wrote:
>
> Thanks Will.
>
> I will go ahead and do some debugging with trace level 25. Hopefully it
> will shed some light on the issue as I am sure that it is not a
> permissions issue because I am able to use the user credentials which
> the driver is using to do Read and Write operations on the Table.
>
> The driver trace log says version 3.5.4. I will also try to see if there
> is a patch available (I haven't done this until now as I thinking it was
> more of a problem in how I had set it up.)
>
> Lastly, I will get the trigger and table ready, this is a time taking
> process as DBA at my place can take a couple of days to implement
> things.
>
> Will keep you posted
>
> Will Schneider;225769 Wrote:
>> I saw a note discussing large data types and it referenced using
>> triggered publication and a query
>> back event. So some stuff to try:
>>
>> If you can switch to a triggered event just to try it out and see if
>> there is a limitation there
>> that would be handy.
>>
>> When you get your flag attribute and you do a get src attr token for the
>> CLOB, try turning the trace
>> level up to 25 (yes 25) and then post the trace of the actual query
>> going back into the database or
>> whatever it is doing. I'm assuming from what you described that you are
>> just getting a null value
>> back but I want to see the query.
>>
>> Also what version of the JDBC driver shim are you running?
>>
>>
>> On 2/20/2013 10:54 AM, debimohanty wrote:
>>>
>>> I am working on an enhancement on existing JDBC Driver on IDM 3.6

>> which
>>> connects to Oracle 10g DB, In a given table on this DB I have to sync
>>> the photos stored in CLOB column in the form of Base 64 to IDM. The
>>> Driver's Publisher is running in Triggerless mode.
>>>
>>> I am facing below issues with the CLOB column:
>>>
>>>
>>> - Trying to get event when CLOB value is changed. Driver is able to
>>> detect events on all other columns of the table except the CLOB

>> one.
>>> Is firing events based on CLOB column supported by JDBC Driver ?
>>> - Assuming that the above won't work I modified the table to add
>>> column to server as a flag and configured Driver to retrieve the

>> value
>>> of CLOB when an event is received. I am seeing that event in the

>> logs
>>> (when I am setting the flag in DB) but I am not able read CLOB

>> column
>>> value using read-src-attr value (on the publisher).
>>> -
>>>
>>> I have set the attribute mapping to flag as notify and attribute

>> mapping
>>> to CLOB as sync on Publisher Channel.
>>>
>>> Can someone please guide me or throw pointers at what I should be

>> doing
>>> in this case
>>>
>>> Thanks in Advance.
>>>
>>>

>
>


0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Issue when trying to read CLOB column from a JDBC Driver.


@Will, The Trace that is just got null back. I executed the query in SQL
Developer and it ran fine, so I am guessing the problem is not there.
Any other ideas ?

debimohanty;225771 Wrote:
> Thanks Will.
>
> I will go ahead and do some debugging with trace level 25. Hopefully it
> will shed some light on the issue as I am sure that it is not a
> permissions issue because I am able to use the user credentials which
> the driver is using to do Read and Write operations on the Table.
>
> The driver trace log says version 3.5.4. I will also try to see if there
> is a patch available (I haven't done this until now as I thinking it was
> more of a problem in how I had set it up.)
>
> Lastly, I will get the trigger and table ready, this is a time taking
> process as DBA at my place can take a couple of days to implement
> things.
>
> Will keep you posted



--
debimohanty
------------------------------------------------------------------------
debimohanty's Profile: https://forums.netiq.com/member.php?userid=1514
View this thread: https://forums.netiq.com/showthread.php?t=46889

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Issue when trying to read CLOB column from a JDBC Driver.

So you copied and pasted the query from the trace and it runs fine as the same user against the
database?
Could you post the query and result from the trace? I'll need it anyway if patching doesn't work to
pursue a bug.

Next upgrade the shim.
What version of the oracle jdbc driver are you using?
Are you running in a remote loader?



On 2/20/2013 1:44 PM, debimohanty wrote:
>
> @Will, The Trace that is just got null back. I executed the query in SQL
> Developer and it ran fine, so I am guessing the problem is not there.
> Any other ideas ?
>
> debimohanty;225771 Wrote:
>> Thanks Will.
>>
>> I will go ahead and do some debugging with trace level 25. Hopefully it
>> will shed some light on the issue as I am sure that it is not a
>> permissions issue because I am able to use the user credentials which
>> the driver is using to do Read and Write operations on the Table.
>>
>> The driver trace log says version 3.5.4. I will also try to see if there
>> is a patch available (I haven't done this until now as I thinking it was
>> more of a problem in how I had set it up.)
>>
>> Lastly, I will get the trigger and table ready, this is a time taking
>> process as DBA at my place can take a couple of days to implement
>> things.
>>
>> Will keep you posted

>
>


0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Issue when trying to read CLOB column from a JDBC Driver.


@Will
Yes, the query ran just fine in SQL Developer, also the elevated log
didn't show any error. I updated the Driver Shim to the latest one 3.6.0
but it didn't help.
My driver is running local, there is no remote loader.
Will Schneider;225791 Wrote:
> So you copied and pasted the query from the trace and it runs fine as
> the same user against the
> database? *-Yes-*
> Could you post the query and result from the trace? I'll need it anyway
> if patching doesn't work to
> pursue a bug.
>
> Next upgrade the shim.
> What version of the oracle jdbc driver are you using?
> Are you running in a remote loader?
>
>
>
> On 2/20/2013 1:44 PM, debimohanty wrote:
> >
> > @Will, The Trace that is just got null back. I executed the query in

> SQL
> > Developer and it ran fine, so I am guessing the problem is not there.
> > Any other ideas ?
> >
> > debimohanty;225771 Wrote:
> >> Thanks Will.
> >>
> >> I will go ahead and do some debugging with trace level 25. Hopefully

> it
> >> will shed some light on the issue as I am sure that it is not a
> >> permissions issue because I am able to use the user credentials

> which
> >> the driver is using to do Read and Write operations on the Table.
> >>
> >> The driver trace log says version 3.5.4. I will also try to see if

> there
> >> is a patch available (I haven't done this until now as I thinking it

> was
> >> more of a problem in how I had set it up.)
> >>
> >> Lastly, I will get the trigger and table ready, this is a time

> taking
> >> process as DBA at my place can take a couple of days to implement
> >> things.
> >>
> >> Will keep you posted

> >
> >



--
debimohanty
------------------------------------------------------------------------
debimohanty's Profile: https://forums.netiq.com/member.php?userid=1514
View this thread: https://forums.netiq.com/showthread.php?t=46889

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Issue when trying to read CLOB column from a JDBC Driver.

I still need you to post the query and the result from the trace.
The XDS document that generates the query would also be helpful.
And which Oracle driver are you using? There are a couple of options there so I want to make sure
we explore those. You can send those to me off list if you prefer to keep the table details out of
the public forum.

On 2/20/2013 4:14 PM, debimohanty wrote:
>
> @Will
> Yes, the query ran just fine in SQL Developer, also the elevated log
> didn't show any error. I updated the Driver Shim to the latest one 3.6.0
> but it didn't help.
> My driver is running local, there is no remote loader.
> Will Schneider;225791 Wrote:
>> So you copied and pasted the query from the trace and it runs fine as
>> the same user against the
>> database? *-Yes-*
>> Could you post the query and result from the trace? I'll need it anyway
>> if patching doesn't work to
>> pursue a bug.
>>
>> Next upgrade the shim.
>> What version of the oracle jdbc driver are you using?
>> Are you running in a remote loader?
>>
>>
>>
>> On 2/20/2013 1:44 PM, debimohanty wrote:
>>>
>>> @Will, The Trace that is just got null back. I executed the query in

>> SQL
>>> Developer and it ran fine, so I am guessing the problem is not there.
>>> Any other ideas ?
>>>
>>> debimohanty;225771 Wrote:
>>>> Thanks Will.
>>>>
>>>> I will go ahead and do some debugging with trace level 25. Hopefully

>> it
>>>> will shed some light on the issue as I am sure that it is not a
>>>> permissions issue because I am able to use the user credentials

>> which
>>>> the driver is using to do Read and Write operations on the Table.
>>>>
>>>> The driver trace log says version 3.5.4. I will also try to see if

>> there
>>>> is a patch available (I haven't done this until now as I thinking it

>> was
>>>> more of a problem in how I had set it up.)
>>>>
>>>> Lastly, I will get the trigger and table ready, this is a time

>> taking
>>>> process as DBA at my place can take a couple of days to implement
>>>> things.
>>>>
>>>> Will keep you posted
>>>
>>>

>
>


0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.