Update Statement Doesn't Update.
lazarus / 16-Databases

----------------------------------------------------------------------
gkelby · 2015-01-11 06:58

Hello:

I have a particular problem with the Update Statement.  I have two
tables, a primary and secondary.  When the fields on the form are
completed, the user clicks on Save and the data is either inserted
into the primary table or it is updated.  Which function is performed
is based on whether the data is new or has been updated.  In either
case both the Insert and Update work. For the secondary table, the
Insert works fine however for the Update, only the "Amount" value is
to be updated.  The Update qualifier finds the row that contains the
old amount value and updates that amount with the new value.  At least
that's the intent.  The problem is that whenever an update is
performed, the Amount is updated to a zero value.  The old value is
wiped and a zero is in it's place.  The new amount value is never put
in.  I've checked the new amount value prior to the Update and it is
correct but it never finds its way into the table.


Code: [Select]Procedure TPaymentSummaries.UpdateFees(Year, Profile,
Payer, OldAmount, NewAmount: Integer);
Begin

  DM2.SQLQuery1.Close;

  DM2.SQLQuery1.SQL.Text := 'UPDATE Deductions SET Amount = :Field5 '
+
  ' Where ProfileID = :Field1 AND PayerID = :Field2 AND Year = :Field3
'+
  ' AND Amount = :Field4';

  DM2.SQLQuery1.Params.ParamByName('Field1').AsInteger := Profile;
  DM2.SQLQuery1.Params.ParamByName('Field2').AsInteger := Payer;
  DM2.SQLQuery1.Params.ParamByName('Field3').AsInteger := Year;
  DM2.SQLQuery1.Params.ParamByName('Field4').AsInteger := OldAmount;
  DM2.SQLQuery1.Params.ParamByName('Field5').AsInteger := NewAmount;

  DM2.SQLQuery1.ExecSQL;
  DM1.SQLTransaction1.Commit;
  DM2.SQLQuery1.Close;

End;
And assistance would be appreciated.
Thanks...
GK

I am using:
Lazarus version 1.1.99pre-release, FPC version 2.6.2, SVN version
42829, i386-win32-win32/win64
SQLite3 and Windows 7 Ultimate 64, SQLite V3.

----------------------------------------------------------------------
JanRoza · 2015-01-11 12:47

Insert statement Code: [Select]DM2.SQLQuery1.ApplyUpdates; before the
commit statement.
That way you're sure that the record is updated before you commit it.

----------------------------------------------------------------------
LacaK · 2015-01-12 06:13

Are you sure, that your "NewValue" is not zero ?
(if value in database changes, that mean, that update is performed,
does not ?)
Are "OldValue" and "NewValue" integers ? They have not decimal places
?

----------------------------------------------------------------------
gkelby · 2015-01-12 08:04

Hi:

Putting DM2.SQLQuery1.ApplyUpdate before the commit statement also
means having to put DM2.SQLQuery1.Open; ahead of the Apply Update
statement in order for it to work so it looks like this:

  DM2.SQLQuery1.Open;
  DM2.SQLQuery1.ApplyUpdates;
  DM1.SQLTransaction1.Commit;
  DM2.SQLQuery1.Close;

But it doesn't work.  There is still an error..."Cannot Open a Non-
Select Statement.  See attachment.

And yes, the new value is greater than zero.  I use a ShowMessage
Statement just before the Params set of statements and it clearly
shows a value greater than zero with no decimal point as an integer. 
The interesting thing is that when there is nothing in the table that
matches the query, an insert is performed and that works just fine. 
Does it have anything to do with the fact that a another table is
being updated before this one?  Or is it something about database
locks?

Another interesting thing about this, as some will have noticed, I'm
using a straight text-book technique, nothing complex and yet it
doesn't appear to work in this instance.

GK.

----------------------------------------------------------------------
engkin · 2015-01-12 08:17

DM refers to Data Module?
I'm confused, can you explain number one here:
QuoteDM1.SQLTransaction1.Commit;

----------------------------------------------------------------------
gkelby · 2015-01-13 08:20

Hi Engkin:

The method used is based on the SQLdb Tutorial4.  It seemed like a
good method to isolate DB transactions from the code and have one
single place for the various component definitions as suggested in the
tutorial.
Essentially DM1 contains the definitions for SQLite3Connection1 and
SQLTransaction1 while DM2 contains the definitions for Datasource1 and
SQLQuery1.

GK

----------------------------------------------------------------------
engkin · 2015-01-13 08:58

Thanks for the explanation. Try to replace:
Code: [Select]DM1.SQLTransaction1.Commit;
with:
Code: [Select]DM2.SQLQuery1.Transaction.Commit;

----------------------------------------------------------------------
gkelby · 2015-01-14 07:53

Well, I tried that and I get a compile error, "unit5.pas(1438,29)
Error: identifier idents no member "Commit".  It seems "Commit" is not
a function, procedure or property of "SQLQuery1.Transaction".

GK.

----------------------------------------------------------------------
engkin · 2015-01-14 14:57

Code: [Select]  TSQLTransaction(SQLQuery1.Transaction).Commit;

----------------------------------------------------------------------
gkelby · 2015-01-15 08:33

Are you sure?

That too generates an error, "Error: Identifier not found "SQLQuery1".
As does, DM1.SQLTransaction1(SQLQuery1.Transaction).Commit; which
generates error "Error: Illegal expression
".

----------------------------------------------------------------------
rvk · 2015-01-15 08:47

Quote from: gkelby on January 15, 2015, 09:33:06 amThat too generates
an error, "Error: Identifier not found "SQLQuery1".
Your SQLQuery1 is in DM2 so you'll need to use:
Code: [Select]TSQLTransaction(DM2.SQLQuery1.Transaction).Commit;

----------------------------------------------------------------------
gkelby · 2015-01-16 05:48

Sorry, the suggestion should have been obvious.  I'll give it a try
later today.

Thanks...
GK.