update sql control question

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.

Who is Participating?
FelixinConnect With a Mentor Commented:
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.


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

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.


Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

conatin = contained

pin_plunderAuthor Commented:
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.


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.



pin_plunderAuthor Commented:
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?
pin_plunderAuthor Commented:
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.
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".
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.