Solved

update sql control question

Posted on 2001-06-03
9
225 Views
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.

thanks.
0
Comment
Question by:pin_plunder
  • 5
  • 3
9 Comments
 

Expert Comment

by:NopparatM
Comment Utility
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;

Description

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.Close;
Query1.SQL.Clear;
Query1.SQL.Add('Delete from Country where Name = ''Argentina''');
Query1.ExecSQL;


UpdateDataQue.Close;
UpdateDataQue.SQL.Clear;
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);
UpdateDataQue.ExecSQL;
0
 
LVL 2

Expert Comment

by:Felixin
Comment Utility
Hi,

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.

Saludos

Felixin
0
 
LVL 2

Expert Comment

by:Felixin
Comment Utility
conatin = contained

(sorry)
0
 

Author Comment

by:pin_plunder
Comment Utility
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.

thanks.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Expert Comment

by:Felixin
Comment Utility
Hi,

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?

or

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.

Greetings

Felixin

0
 

Author Comment

by:pin_plunder
Comment Utility
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?
thanks.
0
 
LVL 2

Accepted Solution

by:
Felixin earned 50 total points
Comment Utility
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.

Greetings

Felixin
0
 

Author Comment

by:pin_plunder
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Felixin
Comment Utility
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".
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now