I change normally the values of my tables/fields in delphi with a DBNavigator, Datasource
and DBisamQuery and its working well.

But when i use in my SQL-Query an order-comand or when i input a table, it is not saving
more in the database the alterations that i do in the appliction:

when i do with this:

select  *  from orders where finaldate is null

i can save the values in the database with the DBNavigator without any problem.

But when i do this:

example1) select  *  from orders where finaldate is null order by client    //(client is a
field of the table orders)
or i use a second table in the query like this:
example2 select  *  from orders,pgroups where finaldate is null and
orders.pgroup=pgroups.pgroup

the alterations are not saved in the DBisam Table, why not? Sometimes i need to order the
query like in example1) or to use a secont table like in example 2)

Thomas


You've encountered the differences between a live result set and a canned one. With DBISAM if it can produce the result using existing indices and without any joins then it should be possible to get a live result set otherwise its canned (a separate copy of the data).

So if you use an ORDER BY clause that doesn't use an existing index OR you join another table you will definitely get a canned result set.

What you will have to do in cases like this is use the before post event to update the underlying table.

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Thomas


You've encountered the differences between a live result set and a canned one. With DBISAM
if it can produce the result using existing indices and without any joins then it should
be possible to get a live result set otherwise its canned (a separate copy of the data).

So if you use an ORDER BY clause that doesn't use an existing index OR you join another
table you will definitely get a canned result set.

What you will have to do in cases like this is use the before post event to update the
underlying table.

Roy Lambert

Thanks Roy!

I added in may table the pgrups (field) to my indexed fields (see the pic), then i tried
to add the order by prgoup in my query but it don't save...

I found also here
http://www.elevatesoft.com/scripts/newsgrp.dll?action=searchopenmsg&group=8&msg=5279&keywords=not*%20saving*#msg5279

something, to put nocase after order by...

I added nocase in my query:
select  *  from orders where finaldate is null order by pgroup nocase   

but it is the same, it is not saving!!

Thomas


pgroup needs to be set up as an index by itself. DBISAM will only take notice of the first field in a multi field index for the WHERE clause and I think its the same for the ORDER BY clause.

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Thomas


pgroup needs to be set up as an index by itself. DBISAM will only take notice of the first
field in a multi field index for the WHERE clause and I think its the same for the ORDER
BY clause.

Roy Lambert

thanks a lot Roy, now i changed the place of pgroup and order in my indexed fields, put
the case-ins to yes and then with the code

order by pgroup nocase

its saving now.

see also the picture.

you was a big help to me Roy