Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Workaround for one of the 'key and updateable columns' being a CLOB?

7 posts in DataWindow Last posting was on 2010-01-08 09:21:43.0Z
Development Posted on 2010-01-05 16:21:40.0Z
From: "Development" <development@infotechnics.co.uk>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Workaround for one of the 'key and updateable columns' being a CLOB?
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b436714$1@forums-1-dub>
Date: 5 Jan 2010 08:21:40 -0800
X-Trace: forums-1-dub 1262708500 10.22.241.152 (5 Jan 2010 08:21:40 -0800)
X-Original-Trace: 5 Jan 2010 08:21:40 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90526
Article PK: 410131

I have a PB11/Oracle application. I have a few DWs that have the 'where
clause for update' property set to 'key and updateable columns'.

One of these columns is currently a varchar2(4000) in Oracle. I now need to
increase this since it will now hold rich text so I need to make it a clob
column.

The problem is that the 'key and updateable columns' update property
basically puts the (now clob) column in the where clause of the update - and
Oralce throws an ora-00932 because you cannot use clobs in where clauses
like that. You need to use the dbms_lob package.

I am just wondering if anyone has needed to us a clob column this way and if
there are any smart workarounds to get the same functionaility as the 'key
and updateable columns' gives for "free"

Thanks
Brian


Philip Salgannik Posted on 2010-01-05 17:08:21.0Z
From: "Philip Salgannik" <philip@MeMyselfAndI.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4b436714$1@forums-1-dub>
Subject: Re: Workaround for one of the 'key and updateable columns' being a CLOB?
Lines: 26
Organization: MeMyselfAndI
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b437205@forums-1-dub>
Date: 5 Jan 2010 09:08:21 -0800
X-Trace: forums-1-dub 1262711301 10.22.241.152 (5 Jan 2010 09:08:21 -0800)
X-Original-Trace: 5 Jan 2010 09:08:21 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90530
Article PK: 410135

Nope, there are no workarounds. You need to take it out of the list.

"Development" <development@infotechnics.co.uk> wrote in message
news:4b436714$1@forums-1-dub...
>I have a PB11/Oracle application. I have a few DWs that have the 'where
>clause for update' property set to 'key and updateable columns'.
>
> One of these columns is currently a varchar2(4000) in Oracle. I now need
> to increase this since it will now hold rich text so I need to make it a
> clob column.
>
> The problem is that the 'key and updateable columns' update property
> basically puts the (now clob) column in the where clause of the update -
> and Oralce throws an ora-00932 because you cannot use clobs in where
> clauses like that. You need to use the dbms_lob package.
>
> I am just wondering if anyone has needed to us a clob column this way and
> if there are any smart workarounds to get the same functionaility as the
> 'key and updateable columns' gives for "free"
>
> Thanks
> Brian
>
>


Development Posted on 2010-01-06 00:42:24.0Z
From: "Development" <development@infotechnics.co.uk>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4b436714$1@forums-1-dub> <4b437205@forums-1-dub>
Subject: Re: Workaround for one of the 'key and updateable columns' being a CLOB?
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b43dc70@forums-1-dub>
Date: 5 Jan 2010 16:42:24 -0800
X-Trace: forums-1-dub 1262738544 10.22.241.152 (5 Jan 2010 16:42:24 -0800)
X-Original-Trace: 5 Jan 2010 16:42:24 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90535
Article PK: 410140

Was just thinking, how about this (I've never used this before so correct me
if I'm wrong)..

In rows->stored procedure update I call a stored procedure and pass over all
the columns to be updated and also all these columsn again but with the 'use
original' selected (my assumption here is that this gives me the original
column values when the DW was retrieved). I can then manually compare all
the original values with the current values from the database to make sure
they are the same and if so I can then do the update in the stored
procedure. If the stored proc finds any original values have changed it will
return an error using raise_application_error so a suitable message can be
displayed to the user.

Does this sound feasible?

As I say, I've never used SPs to update from a DW so maybe I am
misunderstanding.

Thanks
Brian

"Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
news:4b437205@forums-1-dub...
> Nope, there are no workarounds. You need to take it out of the list.
>
> "Development" <development@infotechnics.co.uk> wrote in message
> news:4b436714$1@forums-1-dub...
>>I have a PB11/Oracle application. I have a few DWs that have the 'where
>>clause for update' property set to 'key and updateable columns'.
>>
>> One of these columns is currently a varchar2(4000) in Oracle. I now need
>> to increase this since it will now hold rich text so I need to make it a
>> clob column.
>>
>> The problem is that the 'key and updateable columns' update property
>> basically puts the (now clob) column in the where clause of the update -
>> and Oralce throws an ora-00932 because you cannot use clobs in where
>> clauses like that. You need to use the dbms_lob package.
>>
>> I am just wondering if anyone has needed to us a clob column this way and
>> if there are any smart workarounds to get the same functionaility as the
>> 'key and updateable columns' gives for "free"
>>
>> Thanks
>> Brian
>>
>>
>
>


Philip Salgannik Posted on 2010-01-06 21:54:13.0Z
From: "Philip Salgannik" <philip@MeMyselfAndI.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4b436714$1@forums-1-dub> <4b437205@forums-1-dub> <4b43dc70@forums-1-dub>
Subject: Re: Workaround for one of the 'key and updateable columns' being a CLOB?
Lines: 61
Organization: MeMyselfAndI
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b450685@forums-1-dub>
Date: 6 Jan 2010 13:54:13 -0800
X-Trace: forums-1-dub 1262814853 10.22.241.152 (6 Jan 2010 13:54:13 -0800)
X-Original-Trace: 6 Jan 2010 13:54:13 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90540
Article PK: 410145

Yep, you are misunderstanding. What the OP wants is for WHERE clause to work
on a CLOB column...

"Development" <development@infotechnics.co.uk> wrote in message
news:4b43dc70@forums-1-dub...
> Was just thinking, how about this (I've never used this before so correct
> me if I'm wrong)..
>
> In rows->stored procedure update I call a stored procedure and pass over
> all the columns to be updated and also all these columsn again but with
> the 'use original' selected (my assumption here is that this gives me the
> original column values when the DW was retrieved). I can then manually
> compare all the original values with the current values from the database
> to make sure they are the same and if so I can then do the update in the
> stored procedure. If the stored proc finds any original values have
> changed it will return an error using raise_application_error so a
> suitable message can be displayed to the user.
>
> Does this sound feasible?
>
> As I say, I've never used SPs to update from a DW so maybe I am
> misunderstanding.
>
> Thanks
> Brian
>
>
>
> "Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
> news:4b437205@forums-1-dub...
>> Nope, there are no workarounds. You need to take it out of the list.
>>
>> "Development" <development@infotechnics.co.uk> wrote in message
>> news:4b436714$1@forums-1-dub...
>>>I have a PB11/Oracle application. I have a few DWs that have the 'where
>>>clause for update' property set to 'key and updateable columns'.
>>>
>>> One of these columns is currently a varchar2(4000) in Oracle. I now need
>>> to increase this since it will now hold rich text so I need to make it a
>>> clob column.
>>>
>>> The problem is that the 'key and updateable columns' update property
>>> basically puts the (now clob) column in the where clause of the update -
>>> and Oralce throws an ora-00932 because you cannot use clobs in where
>>> clauses like that. You need to use the dbms_lob package.
>>>
>>> I am just wondering if anyone has needed to us a clob column this way
>>> and if there are any smart workarounds to get the same functionaility as
>>> the 'key and updateable columns' gives for "free"
>>>
>>> Thanks
>>> Brian
>>>
>>>
>>
>>
>
>
>


Development Posted on 2010-01-07 09:21:20.0Z
From: "Development" <development@infotechnics.co.uk>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4b436714$1@forums-1-dub> <4b437205@forums-1-dub> <4b43dc70@forums-1-dub> <4b450685@forums-1-dub>
Subject: Re: Workaround for one of the 'key and updateable columns' being a CLOB?
Lines: 80
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b45a790$1@forums-1-dub>
Date: 7 Jan 2010 01:21:20 -0800
X-Trace: forums-1-dub 1262856080 10.22.241.152 (7 Jan 2010 01:21:20 -0800)
X-Original-Trace: 7 Jan 2010 01:21:20 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90544
Article PK: 410148

Erm, I am the OP :)

I was actually asking if I was misunderstanding the use of SPs for updating.

I have now implemented the SP for update now and it seems to work. I pass
over each column to the SP twice, one with the new value and one with the
'use original' ticked which gives me the original value read fro the
database.

In PL/SQL I can then do a dbms_log.compare on the clob and therefor manually
code the update functionaility.

Brian

"Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
news:4b450685@forums-1-dub...
> Yep, you are misunderstanding. What the OP wants is for WHERE clause to
> work on a CLOB column...
>
> "Development" <development@infotechnics.co.uk> wrote in message
> news:4b43dc70@forums-1-dub...
>> Was just thinking, how about this (I've never used this before so correct
>> me if I'm wrong)..
>>
>> In rows->stored procedure update I call a stored procedure and pass over
>> all the columns to be updated and also all these columsn again but with
>> the 'use original' selected (my assumption here is that this gives me the
>> original column values when the DW was retrieved). I can then manually
>> compare all the original values with the current values from the database
>> to make sure they are the same and if so I can then do the update in the
>> stored procedure. If the stored proc finds any original values have
>> changed it will return an error using raise_application_error so a
>> suitable message can be displayed to the user.
>>
>> Does this sound feasible?
>>
>> As I say, I've never used SPs to update from a DW so maybe I am
>> misunderstanding.
>>
>> Thanks
>> Brian
>>
>>
>>
>> "Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
>> news:4b437205@forums-1-dub...
>>> Nope, there are no workarounds. You need to take it out of the list.
>>>
>>> "Development" <development@infotechnics.co.uk> wrote in message
>>> news:4b436714$1@forums-1-dub...
>>>>I have a PB11/Oracle application. I have a few DWs that have the 'where
>>>>clause for update' property set to 'key and updateable columns'.
>>>>
>>>> One of these columns is currently a varchar2(4000) in Oracle. I now
>>>> need to increase this since it will now hold rich text so I need to
>>>> make it a clob column.
>>>>
>>>> The problem is that the 'key and updateable columns' update property
>>>> basically puts the (now clob) column in the where clause of the
>>>> update - and Oralce throws an ora-00932 because you cannot use clobs in
>>>> where clauses like that. You need to use the dbms_lob package.
>>>>
>>>> I am just wondering if anyone has needed to us a clob column this way
>>>> and if there are any smart workarounds to get the same functionaility
>>>> as the 'key and updateable columns' gives for "free"
>>>>
>>>> Thanks
>>>> Brian
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
>


Philip Salgannik Posted on 2010-01-07 16:34:12.0Z
From: "Philip Salgannik" <philip@MeMyselfAndI.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4b436714$1@forums-1-dub> <4b437205@forums-1-dub> <4b43dc70@forums-1-dub> <4b450685@forums-1-dub> <4b45a790$1@forums-1-dub>
Subject: Re: Workaround for one of the 'key and updateable columns' being a CLOB?
Lines: 88
Organization: MeMyselfAndI
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b460d04@forums-1-dub>
Date: 7 Jan 2010 08:34:12 -0800
X-Trace: forums-1-dub 1262882052 10.22.241.152 (7 Jan 2010 08:34:12 -0800)
X-Original-Trace: 7 Jan 2010 08:34:12 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90548
Article PK: 410153

Oh, well. Seems to be an overkill, but if that works for U, then fine :-)

"Development" <development@infotechnics.co.uk> wrote in message
news:4b45a790$1@forums-1-dub...
> Erm, I am the OP :)
>
> I was actually asking if I was misunderstanding the use of SPs for
> updating.
>
> I have now implemented the SP for update now and it seems to work. I pass
> over each column to the SP twice, one with the new value and one with the
> 'use original' ticked which gives me the original value read fro the
> database.
>
> In PL/SQL I can then do a dbms_log.compare on the clob and therefor
> manually code the update functionaility.
>
> Brian
>
>
> "Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
> news:4b450685@forums-1-dub...
>> Yep, you are misunderstanding. What the OP wants is for WHERE clause to
>> work on a CLOB column...
>>
>> "Development" <development@infotechnics.co.uk> wrote in message
>> news:4b43dc70@forums-1-dub...
>>> Was just thinking, how about this (I've never used this before so
>>> correct me if I'm wrong)..
>>>
>>> In rows->stored procedure update I call a stored procedure and pass over
>>> all the columns to be updated and also all these columsn again but with
>>> the 'use original' selected (my assumption here is that this gives me
>>> the original column values when the DW was retrieved). I can then
>>> manually compare all the original values with the current values from
>>> the database to make sure they are the same and if so I can then do the
>>> update in the stored procedure. If the stored proc finds any original
>>> values have changed it will return an error using
>>> raise_application_error so a suitable message can be displayed to the
>>> user.
>>>
>>> Does this sound feasible?
>>>
>>> As I say, I've never used SPs to update from a DW so maybe I am
>>> misunderstanding.
>>>
>>> Thanks
>>> Brian
>>>
>>>
>>>
>>> "Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
>>> news:4b437205@forums-1-dub...
>>>> Nope, there are no workarounds. You need to take it out of the list.
>>>>
>>>> "Development" <development@infotechnics.co.uk> wrote in message
>>>> news:4b436714$1@forums-1-dub...
>>>>>I have a PB11/Oracle application. I have a few DWs that have the 'where
>>>>>clause for update' property set to 'key and updateable columns'.
>>>>>
>>>>> One of these columns is currently a varchar2(4000) in Oracle. I now
>>>>> need to increase this since it will now hold rich text so I need to
>>>>> make it a clob column.
>>>>>
>>>>> The problem is that the 'key and updateable columns' update property
>>>>> basically puts the (now clob) column in the where clause of the
>>>>> update - and Oralce throws an ora-00932 because you cannot use clobs
>>>>> in where clauses like that. You need to use the dbms_lob package.
>>>>>
>>>>> I am just wondering if anyone has needed to us a clob column this way
>>>>> and if there are any smart workarounds to get the same functionaility
>>>>> as the 'key and updateable columns' gives for "free"
>>>>>
>>>>> Thanks
>>>>> Brian
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
>


Development Posted on 2010-01-08 09:21:43.0Z
From: "Development" <development@infotechnics.co.uk>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4b436714$1@forums-1-dub> <4b437205@forums-1-dub> <4b43dc70@forums-1-dub> <4b450685@forums-1-dub> <4b45a790$1@forums-1-dub> <4b460d04@forums-1-dub>
Subject: Re: Workaround for one of the 'key and updateable columns' being a CLOB?
Lines: 106
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b46f927$1@forums-1-dub>
Date: 8 Jan 2010 01:21:43 -0800
X-Trace: forums-1-dub 1262942503 10.22.241.152 (8 Jan 2010 01:21:43 -0800)
X-Original-Trace: 8 Jan 2010 01:21:43 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90557
Article PK: 410162

What would be your suggestion on how to verify that the clob value had not
been changed by another user?

Any time a user changes a record I need to verify that none of the
updateable columns (including the clob) have been changed in the interim by
another user.

I am thinking that maybe I just remove the clob from the DW updateable
columns and update this column seperately. I would then need to manually
check if the clob column had changed in a pre-update event for the DW.

"Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
news:4b460d04@forums-1-dub...
> Oh, well. Seems to be an overkill, but if that works for U, then fine :-)
> "Development" <development@infotechnics.co.uk> wrote in message
> news:4b45a790$1@forums-1-dub...
>> Erm, I am the OP :)
>>
>> I was actually asking if I was misunderstanding the use of SPs for
>> updating.
>>
>> I have now implemented the SP for update now and it seems to work. I pass
>> over each column to the SP twice, one with the new value and one with the
>> 'use original' ticked which gives me the original value read fro the
>> database.
>>
>> In PL/SQL I can then do a dbms_log.compare on the clob and therefor
>> manually code the update functionaility.
>>
>> Brian
>>
>>
>> "Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
>> news:4b450685@forums-1-dub...
>>> Yep, you are misunderstanding. What the OP wants is for WHERE clause to
>>> work on a CLOB column...
>>>
>>> "Development" <development@infotechnics.co.uk> wrote in message
>>> news:4b43dc70@forums-1-dub...
>>>> Was just thinking, how about this (I've never used this before so
>>>> correct me if I'm wrong)..
>>>>
>>>> In rows->stored procedure update I call a stored procedure and pass
>>>> over all the columns to be updated and also all these columsn again but
>>>> with the 'use original' selected (my assumption here is that this gives
>>>> me the original column values when the DW was retrieved). I can then
>>>> manually compare all the original values with the current values from
>>>> the database to make sure they are the same and if so I can then do the
>>>> update in the stored procedure. If the stored proc finds any original
>>>> values have changed it will return an error using
>>>> raise_application_error so a suitable message can be displayed to the
>>>> user.
>>>>
>>>> Does this sound feasible?
>>>>
>>>> As I say, I've never used SPs to update from a DW so maybe I am
>>>> misunderstanding.
>>>>
>>>> Thanks
>>>> Brian
>>>>
>>>>
>>>>
>>>> "Philip Salgannik" <philip@MeMyselfAndI.com> wrote in message
>>>> news:4b437205@forums-1-dub...
>>>>> Nope, there are no workarounds. You need to take it out of the list.
>>>>>
>>>>> "Development" <development@infotechnics.co.uk> wrote in message
>>>>> news:4b436714$1@forums-1-dub...
>>>>>>I have a PB11/Oracle application. I have a few DWs that have the
>>>>>>'where clause for update' property set to 'key and updateable
>>>>>>columns'.
>>>>>>
>>>>>> One of these columns is currently a varchar2(4000) in Oracle. I now
>>>>>> need to increase this since it will now hold rich text so I need to
>>>>>> make it a clob column.
>>>>>>
>>>>>> The problem is that the 'key and updateable columns' update property
>>>>>> basically puts the (now clob) column in the where clause of the
>>>>>> update - and Oralce throws an ora-00932 because you cannot use clobs
>>>>>> in where clauses like that. You need to use the dbms_lob package.
>>>>>>
>>>>>> I am just wondering if anyone has needed to us a clob column this way
>>>>>> and if there are any smart workarounds to get the same functionaility
>>>>>> as the 'key and updateable columns' gives for "free"
>>>>>>
>>>>>> Thanks
>>>>>> Brian
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
>