Hi There,

From the forum I got it that in EDB when there are more than one table in the query it is readonly.  This worked in DBISAM.  I cannot see why this behavior was not migrated from DBISAM.

Anyway, if this is the future of EDB, I guess I will have to reluctantly adapt.

Although i have been using DBISAM since the start, I'm not an expert.  This means that I may need some more guidance.  I read a continues reply from the experts to use in memory tables, however, there is never a mention of how to get the data back from the memory table to the actual live table, or am I missing something?

If someone has some sample code on how they accomplished this, it will be very much appreciated

Many thanks
Pierre

Pierre

>From the forum I got it that in EDB when there are more than one table in the query it is readonly. This worked in DBISAM. I cannot see why this behavior was not migrated from DBISAM.

The behaviour that has changed is that in ElevateDB you can no longer edit an insensitive (or in DBISAM speak - canned) dataset.

>Although i have been using DBISAM since the start, I'm not an expert. This means that I may need some more guidance. I read a continues reply from the experts to use in memory tables, however, there is never a mention of how to get the data back from the memory table to the actual live table, or am I missing something?

Its exactly how you would get it back from a canned dataset in DBISAM or from one table to another. You have to write code to do it. This can be SQL or navigational code but its down to you.

>If someone has some sample code on how they accomplished this, it will be very much appreciated

There is no sample code simply because its different every time and if you can get data from one table to another you can do this.

Roy Lambert [Team Elevate]

Roy Lambert [Team Elevate],

OK got it "Canned", "Sensitive"...  Your response was not helpful at all - you just re-iterated what I already know and posted above. and to tell me I'm on my own.  I'm have first bought this system when Tim started his business and stuck with Elevate Soft year after year. I can count on one hand the amount of times I have asked for support during this time.

> Its exactly how you would get it back from a canned dataset in DBISAM or from one table to another. You
> have to write code to do it. This can be SQL or navigational code but its down to you.

Correction - In DBISAM, It just worked without alternate coding.  I don't mind "change", time to work all this out is something I do not have a lot of - I write applications as a hobby in my spare time.

> There is no sample code simply because its different every time and if you can get data from one table to
> another you can do this.

Others posted reference to sample code - I don't need sample code tailored to my needs - just a simple generic  "Hello World" sample that could possible benefit many others on this forum.

Why does this not work with an Update object - which should easily have taken care of a modifying a "Sensitive" result set?

Pierre

Pierre,

<< From the forum I got it that in EDB when there are more than one table in the query it is readonly.>>

That is not entirely correct. In EDB, a query containing sub-queries in the column expressions (thus referring 2 or more tables) can return an updatable (live) result set.
For example this query might return an updatable query in EDB:

SELECT
  CustomerID,
  (SELECT CustomerName FROM Customers C WHERE C.CustomerID=I.CustomerID) AS Name,
  InvoiceNumber,
  InvoiceTotal
FROM
  Invoices I

In DBISAM such a query would always return a canned result set.
So, in many cases where you would have to use JOINs, resulting in read-only result sets, you can now in EDB use sub-queries and get the same result with a live result set.

<< This worked in DBISAM.>>
Only in the sense that the result set was 'writable' but the changes would still *not* change the base tables, only the temporary table containing the result of the query.
You can do exactly the same with TEMPORARY TABLES in EDB.

--
Fernando Dias
[Team Elevate]

Thanks Fernando, much appreciated your explanation.

Regards
Pierre

Pierre du Plessis wrote:

Thanks Fernando, much appreciated your explanation.

Just another thing:  Why cant we make this work with the Update Object?

Many thanks,
Pierre

Pierre

<<OK got it "Canned", "Sensitive"...  Your response was not helpful at all - you just re-iterated what I already know and posted above. and to tell me I'm on my own.  I'm have first bought this system when Tim started his business and stuck with Elevate Soft year after year. I can count on one hand the amount of times I have asked for support during this time.>>

Sorry you didn't  find it helpful. Like yourself I've been a user of Elevate products for a long time (I've just had a look and I go back to DBISAM VCL-STD - Version 1.21 Build 1), and I'm a hobby programmer. Unlike yourself I've asked for assistance many times..

Neither DBISAM nor ElevateDB provide a mechanism for returning the results of a canned/insensitive result set to the underlying table which is what I interpreted your question to be. In DBISAM the canned dataset was editable (and I wish it was still the case in ElevateDB) but you still had to get the data back to the original table yourself.

Fernando shows you an approach that provides a "semi-sensitive" result set. Using it the following fields would be editable

CustomerID,
InvoiceNumber,
InvoiceTotal

BUT (and its a big BUT) Name would not be editable unless Tim has made some changes I don't know about.

Name is treated much as a calculated field or ReadOnly column if you were using a TEDBTable component.

In general, when I've been wanting to edit an insensitive result set, I want to edit all columns not some of them. Unless you're happy to edit only some of the data then my reply still stands.

If you want help creating the in-memory or temporary tables that is a different question, and one we'd all be happy to help with.

Finally, a question, and a correction, for you:

<<Why does this not work with an Update object - which should easily have taken care of a modifying a "Sensitive" result set?>>

What is an Update object?

A Sensitive result set is editable and the changes are sent back to the underlying table. I assume you meant insensitive.


Roy Lambert [Team Elevate]

Pierre,

The purpose of the Update Objects (TEDBUpdateSQL) is to apply updates to the underlying tables  from a TClientDataSet component.

--
Fernando Dias
[Team Elevate]

Pierre,

<< From the forum I got it that in EDB when there are more than one table in
the query it is readonly.  This worked in DBISAM.  I cannot see why this
behavior was not migrated from DBISAM. >>

There are architectural issues with doing this in the EDB engine,
specifically as it relates to refreshing insensitive result sets.  EDB can
refresh insensitive result sets, whereas DBISAM cannot.  I thought it more
important to have the latter functionality, especially since you can still
do what you want with:

CREATE TEMPORARY TABLE <TableName> AS <Query>
WITH DATA

That will always give you a temporary table that you can edit, etc. just
like with DBISAM.  You can even automate this with a stored procedure:

CREATE PROCEDURE "CreateCannedResultSet" (IN "SQL" VARCHAR COLLATE ANSI, IN
"TempTableName" VARCHAR COLLATE ANSI)
BEGIN
  DECLARE CatalogCursor CURSOR FOR CatalogStmt;
  DECLARE ResultCursor CURSOR WITH RETURN FOR ResultStmt;

  PREPARE CatalogStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE
Name=?';
  OPEN CatalogCursor USING TempTableName;

  IF (ROWCOUNT(CatalogCursor)=1) THEN
     EXECUTE IMMEDIATE 'DROP TABLE '+QUOTEDSTR(TempTableName,'"');
  END IF;

  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE
'+QUOTEDSTR(TempTableName,'"')+' AS '+SQL+' WITH DATA';

  PREPARE ResultStmt FROM 'TABLE '+QUOTEDSTR(TempTableName,'"');
  OPEN ResultCursor;
END

Executing this procedure with the desired parameters using a TEDBStoredProc
component will result in a table that you can edit.

Tim Young
Elevate Software
www.elevatesoft.com

Roy, Fernando, Tim,

Thank you for very much for the responses - I will be able to take it from here.  This is kick-ass support and really fast response times, much appreciated!

Pierre