Cached Updates with UpdateSQL Update failed error!

da...@goodnet.com

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Cached Updates with UpdateSQL Update failed error!

I was hoping to get some help with this problem. I am new
to Cached Updates and I feel a little lost. Below I have
some questions which are not clear to me with the reading I
have done. I am also hoping that someone can lend me some
wisdom on the update failed error.

I have a query which returns data from 2 tables. I am using
D3 with IB 4.2 and none of the fields are blob types.
Integers, Strings, and a data field.

This query returns 5 fields...lets call them field1-field5.

In the TUpdateSQL I use a stored procedure which I execute
in the inserts as follows

execute procedure mystoredproc(:field1, :field2, :field3,
:field4, :field5);

I have hard coded values in to the stored procedure and it
works fine.

the first 2 fields are not provided on the grid but from
other controls.
I have a nav to work with the grid.

So when insert is pressed the query is in insert mode and I
set the values in the 2 controls to the dataset fields.

MyQuerySource.DataSet.FieldByName('FIELD1').AsInteger :=
some global integer based on earlier user selection;
MyQuerySource.DataSet.FieldByName('FIELD2').AsDateTime :=
some date control value;

The all dataset fields appear to be set correctly, with the
above lines and the values from the grid, when I check them
with dummy assignments in the de{*word*81}.

Here are some of the other coding details when post on the
nav is selected
if MyQuery.UpdatesPending then
begin
try
MyQuery.ApplyUpdates;
MyQuery.Database.Commit;
MyQuery.CommitUpdates;
except
MyQuery.Database.Rollback;
raise;
end;
end;
finally
end;
MyQuery.Close;
MyQuery.Open;

here is the code with the insert
MyQuery.Database.TransIsolation :=
tiDirtyRead;
MyQuery.Database.StartTransaction;

MyQuerySource.DataSet.FieldByName('FIELD1').AsInteger :=
GlobalID;

MyQuerySource.DataSet.FieldByName('FIELD2').AsDateTime :=
XDateTimePicker.Date;

here is the UpdateRecord code
MyQueryUpdateSQL.Apply(UpdateKind);
UpdateAction := uaApplied;

The only property on the MyQueryUpdateSQL I have filled in
right now is the insert which has the previously mentioned
execute procedure....

The Error Message:
I get the error Update Failed....then Table is read only.

So what am I doing wrong or not setting properly?

Should the request live be true with the cashed updates
true?

Should I be using a dirty read with IB? Should you set the
database TransIsolation back when the caching activity is
complete?

Where is the recommended area to perform the start trans
(after post, before post, ...)?

What is the appropriate way to set values for the Query?

Thanks for your help and wisdom.

Barb :)

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Cached Updates with UpdateSQL Update failed error!

da..

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Cached Updates with UpdateSQL Update failed error!

--------------75E309B9A7BCB6A5C0BED0A2
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Here is what happens, everything appears to work fine until
I apply updates. Then I get 'Update Failed' process stopped
etc... then Table is read only error.

Here is what the SQL Monitor shows
20283 11:47:52 SQL Data In: INTRBASE - Param = 1, Name =
, Type = fldINT32, Precision
= 0, Scale = 0, Data = 1
20284 11:47:52 SQL Data In: INTRBASE - Param = 2, Name =
, Type = fldTIMESTAMP,
Precision = 0, Scale = 0, Data = 11/8/1997 16:18:34:0
20285 11:47:52 SQL Data In: INTRBASE - Param = 3, Name =
, Type = fldZSTRING,
Precision = 3, Scale = 0, Data = 327
20286 11:47:52 SQL Data In: INTRBASE - Param = 4, Name =
, Type = fldZSTRING,
Precision = 2, Scale = 0, Data = F1
20287 11:47:52 SQL Data In: INTRBASE - Param = 5, Name =
, Type = fldINT32, Precision
= 0, Scale = 0, Data = 7788
20288 11:47:52 SQL Stmt: INTRBASE - Reset
20289 11:47:52 SQL Vendor: INTRBASE -
isc_dsql_free_statement
20290 11:47:52 SQL Execute: INTRBASE - execute procedure
my_information(?, ?, ?, ?,
?);

20291 11:47:52 SQL Vendor: INTRBASE - isc_dsql_execute
20291 11:47:52 SQL Vendor: INTRBASE - isc_dsql_execute
20292 11:47:52 SQL Data In: INTRBASE - Rows affected = 0

To me this shows that the data is being properly stored in
the dataset but not passed to the
TUpdateSQL. Any ideas why?

The delphi documentation eludes to the fact that any legal
SQL can be used in the TUpdateSQL.
Where have I gone astray?

thanks again for your help
Barb ;)

--------------75E309B9A7BCB6A5C0BED0A2
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>
Here is what happens, everything appears to work fine until I apply updates. 
Then I get 'Update Failed' process stopped etc... then Table is read only
error.

Here is what the SQL Monitor shows
 20283   11:47:52  SQL Data In: INTRBASE - Param = 1,
Name = , Type = fldINT32, Precision
 = 0, Scale = 0, Data = 1
 20284   11:47:52  SQL Data In: INTRBASE - Param = 2,
Name = , Type = fldTIMESTAMP,
 Precision = 0, Scale = 0, Data = 11/8/1997 16:18:34:0
 20285   11:47:52  SQL Data In: INTRBASE - Param = 3,
Name = , Type = fldZSTRING,
 Precision = 3, Scale = 0, Data = 327
 20286   11:47:52  SQL Data In: INTRBASE - Param = 4,
Name = , Type = fldZSTRING,
 Precision = 2, Scale = 0, Data = F1
 20287   11:47:52  SQL Data In: INTRBASE - Param = 5,
Name = , Type = fldINT32, Precision
 = 0, Scale = 0, Data = 7788
 20288   11:47:52  SQL Stmt: INTRBASE - Reset
 20289   11:47:52  SQL Vendor: INTRBASE - isc_dsql_free_statement
 20290   11:47:52  SQL Execute: INTRBASE - execute
procedure my_information(?, ?, ?, ?,
 ?);

20291   11:47:52  SQL Vendor: INTRBASE - isc_dsql_execute
 20291   11:47:52  SQL Vendor: INTRBASE - isc_dsql_execute
 20292   11:47:52  SQL Data In: INTRBASE - Rows affected
= 0

To me this shows that the data is being properly stored in the dataset
but not passed to the
 TUpdateSQL.  Any ideas why?

The delphi documentation eludes to the fact that any legal SQL can be
used in the TUpdateSQL.
 Where have I gone astray?

thanks again for your help
 Barb  ;)</HTML>

--------------75E309B9A7BCB6A5C0BED0A2--

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Cached Updates with UpdateSQL Update failed error!

da..

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Cached Updates with UpdateSQL Update failed error!

--------------1184D48CF742D68D994F9B6B
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Thanks to code insight I found this following command that I
was not aware of.

Here is what I changed the MyQueryApplyUpdates to
with MyUpdateSQL, Query[Updatekind] do
begin
SetParams(UpdateKind);

MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD1').AsInteger
:= MySource.DataSet.FieldByName('FIELD1').AsInteger;

MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD2').AsDate
:= MySource.DataSet.FieldByName('FIELD2').AsDateTime;

MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD3').AsString
:= MySource.DataSet.FieldByName('FIELD3').AsString;

MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD4').AsInteger
:= MySource.DataSet.FieldByName('FIELD4').AsInteger;

MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD5').AsString
:= MySource.DataSet.FieldByName('FIELD5').AsString;
ExecSQL;
UpdateAction := uaApplied;
end;

Everything works.

So any TGurus out there that can explain to me why the field
values in the dataset are not understood and I had to
actually set things up this way instead of just calling
MyUpdateSQL.Apply(UpdateKind)?

Thanks for the explanation in advance

Barb ;)

Quote

da...@goodnet.com wrote:

> Here is what happens, everything appears to work fine
> until I apply updates. Then I get 'Update Failed' process
> stopped etc... then Table is read only error.

> Here is what the SQL Monitor shows
> 20283 11:47:52 SQL Data In: INTRBASE - Param = 1, Name
> = , Type = fldINT32, Precision
> = 0, Scale = 0, Data = 1
> 20284 11:47:52 SQL Data In: INTRBASE - Param = 2, Name
> = , Type = fldTIMESTAMP,
> Precision = 0, Scale = 0, Data = 11/8/1997 16:18:34:0
> 20285 11:47:52 SQL Data In: INTRBASE - Param = 3, Name
> = , Type = fldZSTRING,
> Precision = 3, Scale = 0, Data = 327
> 20286 11:47:52 SQL Data In: INTRBASE - Param = 4, Name
> = , Type = fldZSTRING,
> Precision = 2, Scale = 0, Data = F1
> 20287 11:47:52 SQL Data In: INTRBASE - Param = 5, Name
> = , Type = fldINT32, Precision
> = 0, Scale = 0, Data = 7788
> 20288 11:47:52 SQL Stmt: INTRBASE - Reset
> 20289 11:47:52 SQL Vendor: INTRBASE -
> isc_dsql_free_statement
> 20290 11:47:52 SQL Execute: INTRBASE - execute
> procedure my_information(?, ?, ?, ?,
> ?);

> 20291 11:47:52 SQL Vendor: INTRBASE - isc_dsql_execute
> 20291 11:47:52 SQL Vendor: INTRBASE - isc_dsql_execute
> 20292 11:47:52 SQL Data In: INTRBASE - Rows affected =
> 0

> To me this shows that the data is being properly stored in
> the dataset but not passed to the
> TUpdateSQL. Any ideas why?

> The delphi documentation eludes to the fact that any legal
> SQL can be used in the TUpdateSQL.
> Where have I gone astray?

> thanks again for your help
> Barb ;)

--------------1184D48CF742D68D994F9B6B
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>
Thanks to code insight I found this following command that I was not aware
of.

Here is what I changed the MyQueryApplyUpdates to
       with MyUpdateSQL, Query[Updatekind]
do
         begin
       SetParams(UpdateKind);
       MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD1').AsInteger
:= MySource.DataSet.FieldByName('FIELD1').AsInteger;
       MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD2').AsDate
:= MySource.DataSet.FieldByName('FIELD2').AsDateTime;
       MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD3').AsString
:= MySource.DataSet.FieldByName('FIELD3').AsString;
       MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD4').AsInteger
:= MySource.DataSet.FieldByName('FIELD4').AsInteger;
       MyUpdateSQL.Query[UpdateKind].ParamByName('FIELD5').AsString
:= MySource.DataSet.FieldByName('FIELD5').AsString;
       ExecSQL;
       UpdateAction := uaApplied;
         end;

Everything works.

So any TGurus out there that can explain to me why the field values
in the dataset are not understood and I had to actually set things up this
way instead of just calling MyUpdateSQL.Apply(UpdateKind)?

Thanks for the explanation in advance

Barb ;)

Quote

da...@goodnet.com wrote:

Here is what happens, everything appears to work fine until I apply
updates.  Then I get 'Update Failed' process stopped etc... then Table
is read only error.

Here is what the SQL Monitor shows
 20283   11:47:52  SQL Data In: INTRBASE - Param = 1,
Name = , Type = fldINT32, Precision
 = 0, Scale = 0, Data = 1
 20284   11:47:52  SQL Data In: INTRBASE - Param = 2,
Name = , Type = fldTIMESTAMP,
 Precision = 0, Scale = 0, Data = 11/8/1997 16:18:34:0
 20285   11:47:52  SQL Data In: INTRBASE - Param = 3,
Name = , Type = fldZSTRING,
 Precision = 3, Scale = 0, Data = 327
 20286   11:47:52  SQL Data In: INTRBASE - Param = 4,
Name = , Type = fldZSTRING,
 Precision = 2, Scale = 0, Data = F1
 20287   11:47:52  SQL Data In: INTRBASE - Param = 5,
Name = , Type = fldINT32, Precision
 = 0, Scale = 0, Data = 7788
 20288   11:47:52  SQL Stmt: INTRBASE - Reset
 20289   11:47:52  SQL Vendor: INTRBASE - isc_dsql_free_statement
 20290   11:47:52  SQL Execute: INTRBASE - execute
procedure my_information(?, ?, ?, ?,
 ?);

20291   11:47:52  SQL Vendor: INTRBASE - isc_dsql_execute
 20291   11:47:52  SQL Vendor: INTRBASE - isc_dsql_execute
 20292   11:47:52  SQL Data In: INTRBASE - Rows affected
= 0

To me this shows that the data is being properly stored in the dataset
but not passed to the
 TUpdateSQL.  Any ideas why?

The delphi documentation eludes to the fact that any legal SQL can be
used in the TUpdateSQL.
 Where have I gone astray?

thanks again for your help
 Barb  ;)</BLOCKQUOTE>
 </HTML>

--------------1184D48CF742D68D994F9B6B--

Other Threads

1. Updated failed Error with cached updates and persistent fields

2. UpdateSQL Error: "Update Failed"

3. Update Failed error Message (UpdateSQL)

4. "Update Failed" With Cache Update

5. "Update failed" on paradox cached update

6. Cached Updates QUESTION: Sorting table of updated and non-updated records

7. Cached Updates are dead - long live cached updates?

8. Cached updates with UpdateSQL

9. Cached Updates and UpdateSQL

10. Cached Updates / UpdateSQL / Sybase HELP !!!