update sql control question

Posted on 2001-06-03
Last Modified: 2010-04-06
Ok, I'm using MIDAS and need to do a multiple table update, so I decided to use a query and then update the query. However, I recently learned that because of SQL-92 these type of queries are read only, so I was lost. However, I've now found that the UPDATESQL control I've never in my life used comes to solve my problem. Unfortunately, I don't know how to access it's MODIFYSQL, INSERTSQL AND DELETESQL properties from the client application. I don't even know exactly how to use it. Note: the delphi help was very unclear here.

So, this is the SQL I want to execute, how do this?

SQL := ' UPDATE ' + NombreBase + ' SET CuotasPagadas = CuotasPagadas + 1, CodigoRech = ''' + MyError + '''' + ' , FechaDebito = ''' + Fecha + ''', Marca = ''P''' + ' WHERE C.IdAfiliado = ''' + IdAfiliado + ''' AND C.CuotasPagadas < C.CantCuotas';

I would also like to know if I need to link this UPDATESQL control with the actual TTable or with the TQuery.

Question by:pin_plunder
  • 5
  • 3

Expert Comment

ID: 6151331
You can use TQuery that can use for select and update data.
Write your SQL by property "SQL" of TQuery.

Executes the SQL statement for the query.

procedure ExecSQL;


Call ExecSQL to execute the SQL statement currently assigned to the SQL property. Use ExecSQL to execute queries that do not return a cursor to data (such as INSERT, UPDATE, DELETE, and CREATE TABLE).

Note:     For SELECT statements, call Open instead of ExecSQL.

ExecSQL prepares the statement in SQL property for execution if it has not already been prepared. To speed performance, an application should ordinarily call Prepare before calling ExecSQL for the first time.

Example :

Query1.SQL.Add('Delete from Country where Name = ''Argentina''');

UpdateDataQue.SQL.Add('UPDATE PSOWRW.ENERGY SET KW = '+StringGrid1.Cells[1,i]+',KW_IM = '+StringGrid1.Cells[2,i]);
UpdateDataQue.SQL.Add(' WHERE DATETIME = TO_DATE('''+StringGrid1.Cells[0,i]+''',''DD/MM/YYYY HH24:MI'') and GEN_NO = '+GenRECNUM.AsString);

Expert Comment

ID: 6151768

I think you're mixing concepts here.

Yes, a result set produced by a SELECT sentence from a TQuery is originally read-only. It stops being read-only by making RequestLive = TRUE in a non Midas application. By making this, you're telling Delphi to construct appropriate INSERT/UPDATE/DELETE to perform your data updates.

In a Midas application a result set is always updatable, as the provider in the server constructs the INSERT/UPDATE/DELETE sentences that you perform on the client side.

What worries me is that in your question you're saying that "...need to do a multiple table update...".

What do you mean here:

1. You need to perform multiple updates on a single query one at a time?.
Then you don't have any problem : just performs the updates in the TClientDataSet as if you were using a TTable on the client side.

2. You need to perform updates on different tables?. Then you will have to provide the different INSERT/UPDATE/DELETE sentences for the different tables. A TUpdateSQL object will be suitable here. You will have to connect this TUpdateSQL to your original TQuery and follow the help to solve the parameters matching.

3. You need to update multiple rows in a single table?. If so, just put a second TClientDataSet on your Midas client, being this connected to a SQL empty TQuery. Any time you must perform a new update just change the CommandText property a reopen the TClientDataSet. The SQL sentence conatin in the CommandText will be sent to the server an executed. The only work to perform is to refresh the Midas client to view the changes.

Quite long, but hope it helps.



Expert Comment

ID: 6151770
conatin = contained

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.


Author Comment

ID: 6152949
felixin: your 3rd option is the one I'm with. I'm doing exactly what you say. My CommandText for that query selects multiple fields from a table called ctacte.db. Now this 'selection' depends on some WHERE condition. On the other hand I've got another table called padron.db, whit two fields: IdCard, CardNumber. Now the where condition in my query says that if IdCard in CtaCte.db is = to IdCard that corresponds two some CardNumbers.

As you can see this CommandText property makes a multiple table query. So when I do MyQuery.Open or MyQuery.Execute, the CanModify property is turned to FALSE. Read Delphi help about RequestLive and you'll see that this is what happens when you make multiple table queries. Got it?

So, now. To solve this I need to use UpdateSql control, but to be honest I don't have the slightest idea of how to do it.

I'll try to post my CommandText string in a couple of hours. If you're interested.


Expert Comment

ID: 6155362

When perform the multiple table query:

1.-is that to make a SELECT that you're going to show on the Form for, afterwards modifying one or more records and then post them to the table?


2.-this select is to perform a multirow UPDATE? (in fact it is not a select but an update)

If the answer is #1, then I think it is easier than you think. Have a look at the BeforeUpdateRecord event of the provider (you're using MIDAS, aren't you?). You can perform an UPDATE query based on the primary key of the table on the FROM part of the query (the table you want to modify). Look: on the DeltaDS you'll have the old and new values of the selected fields of table. With those you construct a SQL sentence that you execute on a TQuery. If everything is OK, just set the Applied var to TRUE and it is done.

The BeforeUpdateRecord event is called for each record you've modified in the TClientDataSet. The TClientDataSet is updatable even in the case of a Multitable query. Just in the case you're leaving to Delphi the task of performing the update you'll be in trouble.

If the answer is #2 is a little more difficult (if fact I have no concrete idea). I have to think of it.




Author Comment

ID: 6155895
this is the code I'm using right now...
            IdAfiliado := dm.cdsConvenio.Lookup('DocNro',DocNro,'IdAfiliado');
            dm.cdsCierre.CommandText := ' UPDATE ' + NombreBase +
                                        ' SET CuotasPagadas = CuotasPagadas + 1, CodigoRech = ''' + MyError + '''' +
                                        ' , FechaDebito = ''' + Fecha + ''', Marca = ''P''' +
                                        ' WHERE C.IdAfiliado = ''' + IdAfiliado + ''' AND C.CuotasPagadas < C.CantCuotas';

but when I call dm.cdsCierre.Open I get a "can't create cursor handle" error.

any ideas?

Accepted Solution

Felixin earned 50 total points
ID: 6156009
Of course you get the error!.

This is a UPDATE sentence which will not produce a result set.

If you can send such a sentence to a provider (I've never tried) you should do it using an Execute commad, as you make an ExecSQL instead of an Open when you're using a TQuery in a single tier environment.

What you want has nothing to do with what I've been telling you before.



Author Comment

ID: 6156777
yes, I know this has nothing to do with what we've been talking, but apparently this is the easiest way to solve my REAL problem.

Although I'll give you the points because of your last comment, would you please tell me how to use the control called UPDATESQL. thanks.

Expert Comment

ID: 6158740
A TUpdateSQL is used together with a Data Set.

The aim of the TUpdateSQL is to supply the SQL sentences to INSERT/UPDATE/DELETE records from a read only record set.

The record set to which those sentences apply data set like a SELECT TQuery. The TQuery and TUpdateSQL are connected through the TQuery property UpdateObject. When this property is not nill, Delphi does not generate the SQL sentences to update the record set, but pass control to the update object to perform the task.

You'll see that TUpdateSQL have three TStrings properties : InsertSQL, UpdateSQL and DeleteSQL. Each one of them must contain a sentence to Insert/Update/Delete records in the TQuery record set.

That's the concept.

In Delphi, you know, in a data set you can manage the Value, OldValue and NewValue of the fields. This is important when you're using a TUpdateSQL object, because you'll need to identify the original record that is being modified/deleted or you'll need to identify the values for the newly inserted record.

Normally the sentences in the TUpdateSQL should be parametrized sentences like this

UPDATE MyTable SET Column1=:AValue WHERE Key=:KeyValue

How can we change the formal parameters by the real values?, and more, how can we do it with the original data set without writing a lot of code?. Delphi enables you to write no code by using sufix:

UPDATE MyTable SET Column1=:Column1 WHERE Key=:OLD_Key

Assuming that Column1 and Key are selected in the SQL SELECT sentence of your TQuery, on the data set call to the update sentence, Delphi changes the parameters above with the present value (new) of Column1, and with the OldValue of the field 'Key'.

It is very easy, just make a try.

You'll find more information on page 25-11 of the Delph manual, under "USING UPDATE OBJECTS TO UPDATE A DATASET".

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
delphi prevent click fast 2 216
Delphi Dbf export problem to a Visual Foxpro application 6 188
Run video youtube webbrowse 10 61
Multi-layered image in FireMonkey 9 34
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question