SQLQuery Update on whole record
lazarus / 10-Designer

----------------------------------------------------------------------
erictan · 2020-06-27 12:57

For SQLQuery Dataset, I want to Update or Insert one record at a time
and commit to the Database.
Is there a short-cut in the SQL.Text statement to Update or Insert one
whole record without having to specify all the individual fields in
the table as some of my tables can have as many as 50 fields.

----------------------------------------------------------------------
TRon · 2020-06-27 13:33

Not that i am aware off, other then specifying inside the table itself
that a field has a default value (in which case the default value will
be used for that particular field) (*).

You should be able to update an individual field (or a couple of
fields) without specifying others ? You can do that also with a manual
dataset post so i fail to see why sql wouldn't be able to do so.

edit: (*) assuming you meant fields that are mandatory. If not then
you can leave out the fields (column-names) that you do not wish to
set.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE test(x INTEGER, y text);INSERT INTO test (y) VALUES
('hello');INSERT INTO test (y) VALUES ('goodbye');SELECT * FROM
test;|hello|goodbyeUPDATE test SET y = 'hello again' WHERE y =
'goodbye';SELECT * FROM test;|hello|hello again

----------------------------------------------------------------------
erictan · 2020-06-27 13:58

Thank you TRon for your reply.
I know the easiest thing to do is make all the changes in the Dataset
and commit all back to the database.
The issue is I don't want to make 20 records changes in the Dataset
and have an error with one record and have to make all the changes
again.
I prefer to commit immediately each record updated so that I can
correct any error without affection the other record changes.
I would thought that like 'select * from table' which extract all
fields with having to specify all the fields individually,
SQLQuery.Update or Insert would have similar feature instead of having
to specify each Field-Value pair.

----------------------------------------------------------------------
TRon · 2020-06-27 14:03

ah ok in that case i misunderstood your post.

Of course you can omit the field names, but then you would have to
supply the values in correct order.
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
test VALUES (1, 'ok');SELECT * FROM test;|hello|hello again1|ok

----------------------------------------------------------------------
erictan · 2020-06-27 14:19

But I have already edited the record in the SQLQuery dataset, can I
just post and commit that edited record with the ID back to the
database without specifying any field and values as the dataset record
has the old_valuse and new_value.

----------------------------------------------------------------------
erictan · 2020-06-27 14:23

Alternatively after editing each record in the dataset, I just do a
post and commit back the whole dataset back to the database.

----------------------------------------------------------------------
rvk · 2020-06-27 14:24

Why do you want this in sqlquery.sql.text?

Just set it to SELECT * FROM TABLE and use insert or update.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Sql.open;while not Sql.eof dobegin  Sql.edit;  // set fields with
Sql.fieldbyname('field').asString or asInteger etc.  Sql.post; 
Transaction.commit; // or you can do this every 100 records or
something  Sql.next;end;

----------------------------------------------------------------------
TRon · 2020-06-27 14:26

Quote from: erictan on June 27, 2020, 04:23:07 pmAlternatively after
editing each record in the dataset, I just do a post and commit back
the whole dataset back to the database.
Yes you should be able to. That is usually how its done with a
dataset, an edit then a post

----------------------------------------------------------------------
erictan · 2020-06-27 14:27

Hi rvk, I am using DBGrid and DBEdit components to extract the dataset
for viewing and editing.

----------------------------------------------------------------------
erictan · 2020-06-27 14:30

Yes Tron, but it will be more efficient and faster if I can update
each record individually instead of DBGrid having to scan the dataset,
which can be very large, for any field changes.

----------------------------------------------------------------------
rvk · 2020-06-27 14:34

Quote from: erictan on June 27, 2020, 04:27:56 pmHi rvk, I am using
DBGrid and DBEdit components to extract the dataset for viewing and
editing.
The dbgrid is connected to a sqlquery (via a tdatasource). But you can
just use the tsqlquery to navigate and edit the records directly.

If you want to edit manualy, then yes, you can use tdbgrid and
tdbedit.
(But that wasn't you question)

----------------------------------------------------------------------
erictan · 2020-06-27 14:37

At least I have one solution for now, to update the whole dataset
after each record changes, though it is not the most efficient
solution.
May be I should sweat a bit and can just code the Field and Value pair
one time.
Thanks again Tron and rvk for your suggestion, do let me know if you
have any other other suggestions.

----------------------------------------------------------------------
erictan · 2020-06-27 14:42

Thanks rvk, for data entry with 20 or more fields it's always much
easier to edit on a separate data-entry form instead of directly at
the Grid.
I basically have a main form with the DBGrid displaying all the
records and a separate form for Editing.

----------------------------------------------------------------------
TRon · 2020-06-27 14:45

i do not have experience with grids and the like and perhaps i'm a bit
daft, but isn't a dataset refresh enough to be able to accomplish that
task ?

edit: and with regards to the query: https://www.freepascal.org/docs-
html/fcl/sqldb/tsqlquery.refreshsql.html ?

----------------------------------------------------------------------
erictan · 2020-06-27 14:56

Dataset refresh basically extract the data from the database again.
You have to dataset.applyupdates each time you want to update the
database.
But this is not efficient as it has to scan the whole dataset for any
field changes even for one record changes.

----------------------------------------------------------------------
TRon · 2020-06-27 15:01

ah ok. Thank you for that explanation erictan as i didn't know that.
That's pretty dumb if you ask me. That would be a unworkable situation
for a remote db. e.g. that seems to suggest there must be a better
way....

----------------------------------------------------------------------
rvk · 2020-06-27 15:03

Quote from: erictan on June 27, 2020, 03:58:09 pmI would thought that
like 'select * from table' which extract all fields with having to
specify all the fields individually, SQLQuery.Update or Insert would
have similar feature instead of having to specify each Field-Value
pair.
You don't have to specify each field value pair with .edit.
You can just change the field you want to change.
If you are worried about speed, this is still very fast and the
easiest way.
Otherwise you can follow Trons suggestion to create a separate
sql.update but that's more work. It's up to you. Otherwise I'm not
sure your explaining your problem correctly.