SQLQuery Update on whole record

[erictan]

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]

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][+][-]

1. CREATE TABLE test(x INTEGER, y text);
2. INSERT INTO test (y) VALUES ('hello');
3. INSERT INTO test (y) VALUES ('goodbye');
4. SELECT * FROM test;
5. |hello
6. |goodbye
7. UPDATE test SET y = 'hello again' WHERE y = 'goodbye';
8. SELECT * FROM test;
9. |hello
10. |hello again
11.  

[erictan]

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]

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][+][-]

1. INSERT INTO test VALUES (1, 'ok');
2. SELECT * FROM test;
3. |hello
4. |hello again
5. 1|ok
6.  

[erictan]

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]

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

[rvk]

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

Just set it to SELECT * FROM TABLE and use insert or update.

Code: Pascal  [Select][+][-]

1. Sql.open;
2. while not Sql.eof do
3. begin
4.   Sql.edit;
5.   // set fields with Sql.fieldbyname('field').asString or asInteger etc.
6.   Sql.post;
7.   Transaction.commit; // or you can do this every 100 records or something
8.   Sql.next;
9. end;

[TRon]

Alternatively 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]

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

[erictan]

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]

Hi 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]

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]

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]

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]

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.