Board index » delphi » do cached updts actually work?

do cached updts actually work?

I've been trying for 3 days to get around an error while using Cached
Updates with an Interbase generated key field, and constantly getting
the error  message "record or field constraint failed - value is not
null".  If I use a TTable and remove Cached Updates, the record insert
works without hassle.  It also stores properly on an Edit rather than
an Insert on a TQuery with ApplyUpdates.

I have read all of the obvious set-ups for this situation:
1.) added generator, trigger and stored proc in IB database (following
code of Robert Shieck posted in this area)
2.) changed Required property for KeyField to false in Fields Editor
3.) added KeyField.value := 1 in OnNewRecord event
4.) added TStoredProc to get next generated number in BeforePost event
then stored new number to KeyField

I have tried every combination of Post, Apply Updates, Commits,
OnUpdateRecord events, etc, but can never get rid of this message.  I
even moved the StoredProc from BeforePost to OnNewRecord and even
OnUpdateRecord, but nothing.  

I even converted to a TQuery with RequestLive (but couldn't get a Live
dataset for "SELECT * FROM CustInfo Order BY Last,First" which is
frankly surprising since there are no aggregate fields or other
reasons why it shouldn't (the primary field is IdNo, but there is also
an index on Names).  I also tried a TQuery with a TUpdateSql, but it
still doesn't work.  

I'm pretty sure this is a KeyField problem since I can post the data
using a TTable without Cached Updates using the same 4 setups above,
whereas other fields should give me an error in this situation too,

I'm frankly ready to give up on TQuerys and just use TTables without
Cached Updates even though this is supposed to be used with Interbase.

Thanks in advance for any replies.

Jim

 

Re:do cached updts actually work?


On Fri, 26 Dec 1997 08:59:39 GMT, orlowskiDEL...@CAPSpacbell.net (Jim

Quote
Orlowski) wrote:
>I've been trying for 3 days to get around an error while using Cached
>Updates with an Interbase generated key field, and constantly getting
>the error  message "record or field constraint failed - value is not
>null".  If I use a TTable and remove Cached Updates, the record insert
>works without hassle.  It also stores properly on an Edit rather than
>an Insert on a TQuery with ApplyUpdates.

>I have read all of the obvious set-ups for this situation:
>1.) added generator, trigger and stored proc in IB database (following
>code of Robert Shieck posted in this area)
>2.) changed Required property for KeyField to false in Fields Editor
>3.) added KeyField.value := 1 in OnNewRecord event
>4.) added TStoredProc to get next generated number in BeforePost event
>then stored new number to KeyField

>I have tried every combination of Post, Apply Updates, Commits,
>OnUpdateRecord events, etc, but can never get rid of this message.  I
>even moved the StoredProc from BeforePost to OnNewRecord and even
>OnUpdateRecord, but nothing.  

>I even converted to a TQuery with RequestLive (but couldn't get a Live
>dataset for "SELECT * FROM CustInfo Order BY Last,First" which is
>frankly surprising since there are no aggregate fields or other
>reasons why it shouldn't (the primary field is IdNo, but there is also
>an index on Names).  I also tried a TQuery with a TUpdateSql, but it
>still doesn't work.  

>I'm pretty sure this is a KeyField problem since I can post the data
>using a TTable without Cached Updates using the same 4 setups above,
>whereas other fields should give me an error in this situation too,

>I'm frankly ready to give up on TQuerys and just use TTables without
>Cached Updates even though this is supposed to be used with Interbase.

>Thanks in advance for any replies.

>Jim

Although I have other problems with cached updates, which I haven't
solved yet, I don't have this one.
I use TQueries with cached updates, and I create key values as folows:
In the AfterInsert event, I call a stored procedure which increments
and returns a generator. This value is inserted in the key field of
the TQuery. Works fine.

Jan den Ouden

Re:do cached updts actually work?


Quote
Jan den Ouden wrote:

> On Fri, 26 Dec 1997 08:59:39 GMT, orlowskiDEL...@CAPSpacbell.net (Jim
> Orlowski) wrote:

> >I've been trying for 3 days to get around an error while using Cached
> >Updates with an Interbase generated key field, and constantly getting
> >the error  message "record or field constraint failed - value is not
> >null".  If I use a TTable and remove Cached Updates, the record insert
> >works without hassle.  It also stores properly on an Edit rather than
> >an Insert on a TQuery with ApplyUpdates.

> >I have read all of the obvious set-ups for this situation:
> >1.) added generator, trigger and stored proc in IB database (following
> >code of Robert Shieck posted in this area)
> >2.) changed Required property for KeyField to false in Fields Editor
> >3.) added KeyField.value := 1 in OnNewRecord event
> >4.) added TStoredProc to get next generated number in BeforePost event
> >then stored new number to KeyField

> >I have tried every combination of Post, Apply Updates, Commits,
> >OnUpdateRecord events, etc, but can never get rid of this message.  I
> >even moved the StoredProc from BeforePost to OnNewRecord and even
> >OnUpdateRecord, but nothing.

> >I even converted to a TQuery with RequestLive (but couldn't get a Live
> >dataset for "SELECT * FROM CustInfo Order BY Last,First" which is
> >frankly surprising since there are no aggregate fields or other
> >reasons why it shouldn't (the primary field is IdNo, but there is also
> >an index on Names).  I also tried a TQuery with a TUpdateSql, but it
> >still doesn't work.

> >I'm pretty sure this is a KeyField problem since I can post the data
> >using a TTable without Cached Updates using the same 4 setups above,
> >whereas other fields should give me an error in this situation too,

> >I'm frankly ready to give up on TQuerys and just use TTables without
> >Cached Updates even though this is supposed to be used with Interbase.

> >Thanks in advance for any replies.

> >Jim

> Although I have other problems with cached updates, which I haven't
> solved yet, I don't have this one.
> I use TQueries with cached updates, and I create key values as folows:
> In the AfterInsert event, I call a stored procedure which increments
> and returns a generator. This value is inserted in the key field of
> the TQuery. Works fine.

> Jan den Ouden

What I did was to develop a descendent class of TQuery, which I am going
to post, which includes code to obtain a primary-key from a stored proc.
The application universally applies these rules:
   (1)  It's the app's responsibility to get and to provide a key.
   (2)  But in addition, a server trigger will also provide a key if the
client does not, so that bulk-inserts work when the data is being
provided by another application.

Re:do cached updts actually work?


Jan,

Thanks to you and Mike and Nancy for the replies.

Quote
>I use TQueries with cached updates, and I create key values as folows:
>In the AfterInsert event, I call a stored procedure which increments
>and returns a generator. This value is inserted in the key field of
>the TQuery. Works fine.

In my original post, I mentioned that I tried adding a call to a
TStoredProc to get next generated number in 1.) a BeforePost and 2.)
an OnNewRecord event.  I then stored the new number to the KeyField.
However, I continued to get a Not Null constraint error.

I finally gave up and connected a TTable to the TQuery via a
MasterSource / MasterFields link, and then attached the DbEdit fields
to it.  I put in a StartTransaction.. Post.. Commit.. Rollback setup,
and no longer get an error.  It seems to me that the error is somehow
on the Cached Updates side of the equation and not the Interbase side.

Thanks again.

Jim

Re:do cached updts actually work?


Jim, please read my answer to your previous post.

Cheers.

On Tue, 30 Dec 1997 07:01:06 GMT, orlowskiDEL...@CAPSpacbell.net (Jim

Quote
Orlowski) wrote:
>Jan,

>Thanks to you and Mike and Nancy for the replies.

>>I use TQueries with cached updates, and I create key values as folows:
>>In the AfterInsert event, I call a stored procedure which increments
>>and returns a generator. This value is inserted in the key field of
>>the TQuery. Works fine.

>In my original post, I mentioned that I tried adding a call to a
>TStoredProc to get next generated number in 1.) a BeforePost and 2.)
>an OnNewRecord event.  I then stored the new number to the KeyField.
>However, I continued to get a Not Null constraint error.

>I finally gave up and connected a TTable to the TQuery via a
>MasterSource / MasterFields link, and then attached the DbEdit fields
>to it.  I put in a StartTransaction.. Post.. Commit.. Rollback setup,
>and no longer get an error.  It seems to me that the error is somehow
>on the Cached Updates side of the equation and not the Interbase side.

>Thanks again.

>Jim

Nuno Leal

_______________________________________
Tweak - Tecnologias de Informa??o, Lda
email: nl...@mail.telepac.pt

Re:do cached updts actually work?


Quote
Jim Orlowski wrote:

> Jan,

> Thanks to you and Mike and Nancy for the replies.

> >I use TQueries with cached updates, and I create key values as folows:
> >In the AfterInsert event, I call a stored procedure which increments
> >and returns a generator. This value is inserted in the key field of
> >the TQuery. Works fine.

> In my original post, I mentioned that I tried adding a call to a
> TStoredProc to get next generated number in 1.) a BeforePost and 2.)
> an OnNewRecord event.  I then stored the new number to the KeyField.
> However, I continued to get a Not Null constraint error.

Jim is right, you need to put this in the AfterInsert event and it will
work fine.  There is a timing problem with OnNewRecord and running
some algorithms (works fine for simple assigns).  The NOT NULL
TField check is run before BeforePost event.  

If you want to use the BeforePost event, just assign a dummy number
to the field, like zero '0'.  This satisfies the NOT NULL check and
then it will be overwritten in the BeforePost event.  

--
Thomas Miller
BSS Accounting & Distribution Software
BSS Application FrameWork
BSS VCL Pack

http://www.bss-software.com

Re:do cached updts actually work?


Tom:

Quote
>...you need to put this in the AfterInsert event and it will work fine.  
>There is a timing problem with OnNewRecord and running
>some algorithms (works fine for simple assigns).  The NOT NULL
>TField check is run before BeforePost event.  
>If you want to use the BeforePost event, just assign a dummy number
>to the field, like zero '0'.  This satisfies the NOT NULL check and
>then it will be overwritten in the BeforePost event.  

You were right, AfterInsert event works while BeforePost and
OnNewRecord doesn't.  Thanks a lot for the help.

Jim

Other Threads