Board index » delphi » Cached Updates with UpdateSQL Update failed error!

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 :)

 

Re:Cached Updates with UpdateSQL Update failed error!


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.&nbsp;
Then I get 'Update Failed' process stopped etc... then Table is read only
error.

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

<P>20291&nbsp;&nbsp; 11:47:52&nbsp; SQL Vendor: INTRBASE - isc_dsql_execute
<BR>20291&nbsp;&nbsp; 11:47:52&nbsp; SQL Vendor: INTRBASE - isc_dsql_execute
<BR>20292&nbsp;&nbsp; 11:47:52&nbsp; SQL Data In: INTRBASE - Rows affected
= 0

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

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

<P>thanks again for your help
<BR>Barb&nbsp; ;)</HTML>

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

Re:Cached Updates with UpdateSQL Update failed error!


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.

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

<P>Everything works.

<P>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)?

<P>Thanks for the explanation in advance

<P>Barb ;)
<BR>&nbsp;
<BR>&nbsp;

Quote
<P>da...@goodnet.com wrote:

<BLOCKQUOTE TYPE=CITE>&nbsp;

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

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

<P>20291&nbsp;&nbsp; 11:47:52&nbsp; SQL Vendor: INTRBASE - isc_dsql_execute
<BR>20291&nbsp;&nbsp; 11:47:52&nbsp; SQL Vendor: INTRBASE - isc_dsql_execute
<BR>20292&nbsp;&nbsp; 11:47:52&nbsp; SQL Data In: INTRBASE - Rows affected
= 0

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

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

<P>thanks again for your help
<BR>Barb&nbsp; ;)</BLOCKQUOTE>
&nbsp;</HTML>

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

Other Threads