Tim

Is there any chance of a query component which does not produce a sensitive result set but is editable?

A typical scenario goes:

contacts form has a career summary grid for which the data is produced by a query. Details are shown on a tab of a page control and (for example) a company could be changed, or a leaving date set. I update the underlying table and, in DBISAM days, I would then update the query result set so the display was consistent.

The query producing the career summary information is fired as a user scrolls up/down a list of contacts.

I know I can create temporary / memory tables, but it seems a bit cacky to run a query to create a table, have to add an index to get the order I want, or switch the grid (TMS) from PageMode true to false, have to add an extra hidden column in and keep things in sync manually.

I'd be happy with an additional component TEDBNonSensitiveButEditableQuery or somesuch since I know how much grief the editable and live vs canned scenario caused you.

Roy Lambert

You get my votes.

It can be a special property "Editable" with default "False" (common
behavior).

Eduardo

Roy,

<< Is there any chance of a query component which does not produce a
sensitive result set but is editable? >>

Sure, just use CREATE TABLE AS SELECT....

<< I know I can create temporary / memory tables, but it seems a bit cacky
to run a query to create a table, have to add an index to get the order I
want, or switch the grid (TMS) from PageMode true to false, have to add an
extra hidden column in and keep things in sync manually. >>

Why do you think that you need to do any of that ?  CREATE TABLE AS SELECT
is the exact same thing as the DBISAM canned result sets, the only
difference being the fact that you're terminating the SELECT statement's
existence as a query when you convert it into a table (the indexes, etc.
created during the query are all still there, they're just hidden).
Remember, the reason for this switch is that the EDB insensitive result sets
can refresh themselves, and that is not something that you want to do have
happening when you're updating the data "in place".

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< I know I can create temporary / memory tables, but it seems a bit cacky
>to run a query to create a table, have to add an index to get the order I
>want, or switch the grid (TMS) from PageMode true to false, have to add an
>extra hidden column in and keep things in sync manually. >>

>Why do you think that you need to do any of that ?

CREATE TABLE AS SELECT does not respect any ORDER BY statement so I either have to use a table with an index or use a TMS grid in non page mode and sort the grid. Once a TMS grid is in non-page mode it loads the entire dataset and effectively becomes just a string grid with no link back to the dataset. Therefore, a column has to hold the dataset key so when you move up/down the grid you can do a locate/findkey to stay in sync.

>Remember, the reason for this switch is that the EDB insensitive result sets
>can refresh themselves, and that is not something that you want to do have
>happening when you're updating the data "in place".

I'd forgotten that (assuming I ever noticed in the first place, but now I think about it I do remember something about refresh without close/open).

Let me describe the latest scenario that prompted the post (first get a cup of coffee I want your brain working at peak).

Part of the app that I'm rewriting is a module that deals with eShots (like a mailshot but using email, and no its not intended for spam). To do this I allow users to select from the database which generates a query along the lines

SELECT
FALSE AS _Selected,
_fkContacts,
_fkCompanies,
_Surname,
_Forename,
(_Forename+' '+_Surname) AS _xContactName,
IF(_EMail IS NOT NULL,_EMail,_HomeEMail) AS _xEMail,
_EMail,
_HomeEMail,
_JobTitle,
_Name AS _CompanyName,
_Left,
_NoEshots
FROM Contacts
JOIN Career ON Career._fkContacts = _ID
JOIN Companies ON Companies._ID = Career._fkCompanies
WHERE.......
ORDER BY _Surname, _Forename

The user can scroll up and down this ticking which contact they want including. Unfortunately this requires an editable dataset since _Selected is changed to reflect the flag.

Now ,to make matters more complicated as every good programmer does, a user could have several instances of this form open simultaneously. Doing a CREATE TABLE AS means that I also have to manage the table name (agreed its not much effort, but it is additional work) which I didn't have to do before.

I have a number of these sort of scenarios which I'm working through.

If you also think about the "How to get around no Live Result set problem?" thread in dbisam.general you'll see there are reasons for wanting editable result sets which can be solved by CREATE TABLE AS but which are simpler if the result set can be edited.

Roy Lambert

Tim


And as a ps

If I want to use an in-memory table then I have to resolve the database references as well. So I'm thinking about temporary tables instead.

Here's my take on the pros and cons

IN FAVOUR OF TEMPORARY TABLES
No need to worry about the database if all the tables in the select statement are in the same database

AGAINST TEMPORARY TABLES
Not self clearing (so a bit like canned result sets), but this oly counts in the event of abnormal termination
Slower than in-memory, especially if a number of indices have to be created

Any other thoughts from anyone

Roy Lambert

Roy,

<< CREATE TABLE AS SELECT does not respect any ORDER BY statement >>

Yeah, I forgot about that.  However, I did give you a nice script that takes
care of that, didn't I ?

<< I'd forgotten that (assuming I ever noticed in the first place, but now I
think about it I do remember something about refresh without close/open). >>

Yep, works great because it avoids a close/open and the "flicker" effect.

<< Now ,to make matters more complicated as every good programmer does, a
user could have several instances of this form open simultaneously. Doing a
CREATE TABLE AS means that I also have to manage the table name (agreed its
not much effort, but it is additional work) which I didn't have to do
before. >>

Just use a CREATE TEMPORARY TABLE AS.

<< If you also think about the "How to get around no Live Result set
problem?" thread in dbisam.general you'll see there are reasons for wanting
editable result sets which can be solved by CREATE TABLE AS but which are
simpler if the result set can be edited. >>

Yeah, but they won't work with a refreshable result set, which DBISAM didn't
have.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< AGAINST TEMPORARY TABLES
Not self clearing (so a bit like canned result sets), but this oly counts
in the event of abnormal termination
Slower than in-memory, especially if a number of indices have to be created
>>

They're not really that much slower if you actually compare the two.  They
fully use the buffering available, so they only occasionally write to disk
when the buffers are full.  All of the metadata management for temporary
tables is completely in memory, also.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< CREATE TABLE AS SELECT does not respect any ORDER BY statement >>
>
>Yeah, I forgot about that. However, I did give you a nice script that takes
>care of that, didn't I ?

Yup and its working well. One of these days I'm going to add a bit more to allow it to create multiple indices.

><< I'd forgotten that (assuming I ever noticed in the first place, but now I
>think about it I do remember something about refresh without close/open). >>
>
>Yep, works great because it avoids a close/open and the "flicker" effect.

Still can't switch between indices though

><< Now ,to make matters more complicated as every good programmer does, a
>user could have several instances of this form open simultaneously. Doing a
>CREATE TABLE AS means that I also have to manage the table name (agreed its
>not much effort, but it is additional work) which I didn't have to do
>before. >>
>
>Just use a CREATE TEMPORARY TABLE AS.

But I still have to manage the name, just don't have to worry about the database

><< If you also think about the "How to get around no Live Result set
>problem?" thread in dbisam.general you'll see there are reasons for wanting
>editable result sets which can be solved by CREATE TABLE AS but which are
>simpler if the result set can be edited. >>
>
>Yeah, but they won't work with a refreshable result set, which DBISAM didn't
>have.

Which is why I suggested a new component TEDBNonSensitiveButEditableQuery <vbg>

Roy Lambert

Roy,

<< But I still have to manage the name, just don't have to worry about the
database >>

You had to manage the name with DBISAM, also.

<< Which is why I suggested a new component TEDBNonSensitiveButEditableQuery
<vbg> >>

Uh, yeah, not going to happen.

--
Tim Young
Elevate Software
www.elevatesoft.com