Board index » delphi » Help On TQuery and TupdateSQL PLease Urgent !!!

Help On TQuery and TupdateSQL PLease Urgent !!!

I have created a DB in access and set up aliases blah blah
I can access the Db from my app
but when I tried to update or write data it said cannot do on closed/read
only dataset
so i shoved an updateSQL component but now I can't access the paramaters
even if i used SetParams on my modify procedure
and in my insert procedure it replies no SQL Statements
please help
I need to finish this app by tomorow and everything is complete except for
my adding new clients and modifying clents
if you wana see the source files thats fine but please i need help.
I'm fairly new to programming DB's but have programmed in Pascal and Object
Pascal for about 3 years.
Thanx Soul
 

Re:Help On TQuery and TupdateSQL PLease Urgent !!!


On Wed, 22 Dec 1999 14:23:24 +0200, "Soul" <overs...@venturenet.co.za>
wrote:

Quote
>I have created a DB in access and set up aliases blah blah
>I can access the Db from my app
>but when I tried to update or write data it said cannot do on
closed/read
>only dataset
>so i shoved an updateSQL component but now I can't access the
paramaters
>even if i used SetParams on my modify procedure
>and in my insert procedure it replies no SQL Statements
>please help
>I need to finish this app by tomorow and everything is complete except
for
>my adding new clients and modifying clents
>if you wana see the source files thats fine but please i need help.
>I'm fairly new to programming DB's but have programmed in Pascal and
Object
>Pascal for about 3 years.
>Thanx Soul

Regards,
E.Beli
http://www.sauron.co.yu
http://www.imperialstarfleet.com
Remove X from email if you want to mail me

Re:Help On TQuery and TupdateSQL PLease Urgent !!!


On Wed, 22 Dec 1999 14:23:24 +0200, "Soul" <overs...@venturenet.co.za>
wrote:

Quote
>I have created a DB in access and set up aliases blah blah
>I can access the Db from my app but when I tried to update
>or write data it said cannot do on closed/read only dataset
>so i shoved an updateSQL component but now I can't access
>the paramaters even if i used SetParams on my modify
>procedure and in my insert procedure it replies no SQL
>Statements please help.

[...]

What kind of dataset were you using that could not be updated? A TQuery, a
TTable, a TStoredProc, or ... ? This can be important in regards to where
the dataset is made read-only. For instance, a multi-table query produces a
read-only result set. Enabling cached updates and supplying update objects
can overcome this, applying any cached updates to individual base tables.
But if the base tables are simply read-only at the level of the database
back-end (such as because of insufficient privileges, a read-only
attribute, or whatever reason), cached updates and update objects cannot
overcome this.

If the dataset was a TQuery, what is the exact SQL statement? Was there
only one table or multiple tables involved? What was the exact error that
resulted from the failed attempt to edit the data? What type of exception
object was it: an EDatabaseError or EDBEngineError?

In addition to "shoving" a TUpdateSQL component, were you also enabling
cached updates? A TUpdateSQL component alone cannot make a read-only
dataset editable. It must be used in conjunction with cached updates.

Please post any replies to these questions here in the newsgroup.

What follows is a description of basic usage for cached updates and update
objects.

*************************************************************

In order to use TUpdateSQL components to update read-only result sets from
queries, you must enable cached updates. Do this by setting the
CachedUpdates property of the TQuery to true. Enabling cached updates
causes the data component and any connected data-aware controls to act in a
manner that looks like the dataset is updatable. For instance, the user
would be able to insert new records in a TDBGrid. The records are not added
to the base table of the query, of course, but to the cached copy of the
dataset.

If the read-only query involves only one table, you only need one
TUpdateSQL component. This component provides the ability to apply data
updates cached locally to the base tables also used by the query. This is
done by the SQL statements in the DeleteSQL, InsertSQL, and ModifySQL
properties of the TUpdateSQL component. These properties each contain an
SQL statement that, respectively, applies record deletions, applies record
inserts, and applies record modifications. Associate the TUpdateSQL
component with the TQuery by setting the TQuery.UpdateObject property to
the name of the TUpdateSQL component.

If the query uses more than one base table, then you would need more than
one TUpdateSQL. Each TUpdateSQL is capable of applying the cached updates
for one base table. When more than one TUpdateSQL is used with a TQuery,
the association process is different. Leave the TQuery.UpdateObject
property blank. Associate the individual TUpdateSQL components with the
TQuery by setting the DataSet property of each TUpdateSQL to the name of
the TQuery.

When multiple TUpdateSQL components are used with a single TQuery, the
updates are not automatically applied when you call the ApplyUpdates method
for the TQuery. Instead, you have to have a handler for the OnUpdateRecord
event of the TQuery. In this handler, explicitly call the Apply method for
each TUpdateSQL. Also be sure to set the UpdateAction parameter of the
event handler to uaApplied.

The SQL statements in the DeleteSQL, InsertSQL, and ModifySQL properties of
the TUpdateSQL component are executed once for each record in the cache of
the read-only dataset. They must then be composed so as to be applicable to
only a single row in the table -- the one matching the row in the cache for
which the modifications are currently being applied. Also, the SQL
statement in each of these properties should match the property and its
purpose. For instance, the InsertSQL property should contain an SQL
statement using INSERT.

The DeleteSQL property should contain only an SQL statement with the DELETE
command. The base table to be updated must be named in the FROM clause. So
that the SQL statement only deletes the record in the base table that
corresponds to the record deleted in the update cache, use a WHERE clause.
In the WHERE clause, use a parameter for one or more fields to uniquely
identify the record in the base table that corresponds to the cached update
record. If the parameters are named the same as the field and prefixed with
OLD_, the parameters are automatically given the values from the
corresponding field from the cached update record. If the parameter are
named in any other manner, you must supply the parameter values.

  DELETE FROM Inventory I
  WHERE (I.ItemNo = :OLD_ItemNo)

Some tables types might not be able to find the record in the base table
when fields used to identify the record contain NULL values. In these
cases, the delete update fails for those records. To accommodate this, add
a condition for those fields that might contain NULLs using the IS NULL
predicate (in addition to a condition for a non-NULL value). For example,
when a FirstName field may contain a NULL value:

  DELETE FROM Names
  WHERE (LastName = :OLD_LastName) AND
  ??((FirstName = :OLD_FirstName) OR (FirstName IS NULL))

The InsertSQL statement should contain only an SQL statement with the
INSERT command. The base table to be updated must be named in the INTO
clause. In the VALUES clause, supply a comma-separated list of parameters.
If the parameters are named the same as the field, the parameters are
automatically given the value from the cached update record. If the
parameter are named in any other manner, you must supply the parameter
values. The list of parameters supplies the values for fields in the newly
inserted record. There must be as many value parameters as there are fields
listed in the statement.

  INSERT INTO Inventory
  (ItemNo, Amount)
  VALUES (:ItemNo, 0)

The ModifySQL statement should contain only an SQL statement with the
UPDATE command. The base table to be updated must be named in the FROM
clause. Include one or more value assignments in the SET clause. If values
in the SET clause assignments are parameters named the same as fields, the
parameters are automatically given values from the fields of the same name
in the updated record in the cache. You can assign additional field values
using other parameters, as long as the parameters are not named the same as
any fields and you manually supply the values. As with the DeleteSQL
statement, supply a WHERE clause to uniquely identify the record in the
base table to be updated using parameters named the same as the fields and
prefixed with OLD_. In the update statement below, the parameter :ItemNo
is automatically given a value and :Price is not.

  UPDATE Inventory I
  SET I.ItemNo = :ItemNo, Amount = :Price
  WHERE (I.ItemNo = :OLD_ItemNo)

Considering the above update SQL, take an example case where the
application end-user modifies an existing record. The original value for
the ItemNo field is 999. In a grid connected to the cached dataset, the
end-user changes the ItemNo field value to 123 and Amount to 20. When the
ApplyUpdates method is invoked, this SQL statement affects all records in
the base table where the ItemNo field is 999, using the old field value in
the parameter :OLD_ItemNo. In those records, it changes the ItemNo field
value to 123 (using the parameter :ItemNo, the value coming from the grid)
and Amount to 20.

More information is available on cached updates and TUpdateSQL components
in the online help. See the VCL Reference for specifics on individual
objects, properties, methods, and events. See the Database Developer's
Guide online help for more information on using cached updates and
TUpdateSQL components. In the table of contents for the Delphi online help,
navigate to the topic title:

  Developing Database Applications
    Working with cached updates

This last topic is the beginning of the chapter on cached updates. The
chapter contains a lot of information including setting update SQL at
runtime and design-time, understanding and using parameters, using
TUpdateSQL components, using TTable and TQuery components to apply cached
updates, and numerous other related topics.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Other Threads