Problem with Select SQL and table buffering 
Author Message
 Problem with Select SQL and table buffering

Problem with Select SQL and table buffering

I have the folowing problem:
I have two tables, in a one to many relation.
The field the two tables are linked by is called room_no.

I have to make misc. processing on them using buffering, so I'm opening them
with buffering enabled (set to 5).

Here is my problem.
In my processing, I add / delete / change records to both tables.
But, at some point, I need to create a temporary cursor with a inner join
select.
But, after select is executed, my temp cursor doesn't contains any of the
changes I've made to that tables.
I saw in the documentation Select openes the tables similar with USE ...
AGAIN, so it doesn't take care of my last changes.
How can I solve this?
Might be the only way to build the cursor "by hand", in code ?

Thank you.

Bogdan



Mon, 31 Mar 2003 03:00:00 GMT  
 Problem with Select SQL and table buffering
Bogdan,

By definition, a table buffer holds potential updates in memory before
committing them to disk.  If the table is re-opened, those changes will
not be seen until the buffers on the table are flushed.

Re-writing the SQL by hand will not change the results.

To solve your problem, you have to flush the buffers before opening
your view or turn off table buffering.

- Keith



Quote:
> Problem with Select SQL and table buffering

> I have the folowing problem:
> I have two tables, in a one to many relation.
> The field the two tables are linked by is called room_no.

> I have to make misc. processing on them using buffering, so I'm
opening them
> with buffering enabled (set to 5).

> Here is my problem.
> In my processing, I add / delete / change records to both tables.
> But, at some point, I need to create a temporary cursor with a inner
join
> select.
> But, after select is executed, my temp cursor doesn't contains any of
the
> changes I've made to that tables.
> I saw in the documentation Select openes the tables similar with
USE ...
> AGAIN, so it doesn't take care of my last changes.
> How can I solve this?
> Might be the only way to build the cursor "by hand", in code ?

> Thank you.

> Bogdan

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 31 Mar 2003 03:00:00 GMT  
 Problem with Select SQL and table buffering
Thank you.

It would be nice a SQL to work with the actual alias instead of the table.

It seems I have to build my cursor by hand.

Regards.


Quote:
> Bogdan,

> By definition, a table buffer holds potential updates in memory before
> committing them to disk.  If the table is re-opened, those changes will
> not be seen until the buffers on the table are flushed.

> Re-writing the SQL by hand will not change the results.

> To solve your problem, you have to flush the buffers before opening
> your view or turn off table buffering.

> - Keith



> > Problem with Select SQL and table buffering

> > I have the folowing problem:
> > I have two tables, in a one to many relation.
> > The field the two tables are linked by is called room_no.

> > I have to make misc. processing on them using buffering, so I'm
> opening them
> > with buffering enabled (set to 5).

> > Here is my problem.
> > In my processing, I add / delete / change records to both tables.
> > But, at some point, I need to create a temporary cursor with a inner
> join
> > select.
> > But, after select is executed, my temp cursor doesn't contains any of
> the
> > changes I've made to that tables.
> > I saw in the documentation Select openes the tables similar with
> USE ...
> > AGAIN, so it doesn't take care of my last changes.
> > How can I solve this?
> > Might be the only way to build the cursor "by hand", in code ?

> > Thank you.

> > Bogdan

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Mon, 31 Mar 2003 03:00:00 GMT  
 Problem with Select SQL and table buffering
Bogdan,

Any SQL select statement - those generated by the view builder and
those written by hand - will open a table AGAIN in a seperate work
area.  Re-writing the SQL by hand will not change this.

Sorry for the bad news,
- Keith



Quote:
> Thank you.

> It would be nice a SQL to work with the actual alias instead of the
table.

> It seems I have to build my cursor by hand.

> Regards.



> > Bogdan,

> > By definition, a table buffer holds potential updates in memory
before
> > committing them to disk.  If the table is re-opened, those changes
will
> > not be seen until the buffers on the table are flushed.

> > Re-writing the SQL by hand will not change the results.

> > To solve your problem, you have to flush the buffers before opening
> > your view or turn off table buffering.

> > - Keith



> > > Problem with Select SQL and table buffering

> > > I have the folowing problem:
> > > I have two tables, in a one to many relation.
> > > The field the two tables are linked by is called room_no.

> > > I have to make misc. processing on them using buffering, so I'm
> > opening them
> > > with buffering enabled (set to 5).

> > > Here is my problem.
> > > In my processing, I add / delete / change records to both tables.
> > > But, at some point, I need to create a temporary cursor with a
inner
> > join
> > > select.
> > > But, after select is executed, my temp cursor doesn't contains
any of
> > the
> > > changes I've made to that tables.
> > > I saw in the documentation Select openes the tables similar with
> > USE ...
> > > AGAIN, so it doesn't take care of my last changes.
> > > How can I solve this?
> > > Might be the only way to build the cursor "by hand", in code ?

> > > Thank you.

> > > Bogdan

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 31 Mar 2003 03:00:00 GMT  
 Problem with Select SQL and table buffering
Hello Bogdan

try this

After your add / delete / change records to both tables do a
=TABLEUPDATE(1,.T. <tablename>) (1 = all records) for both tables, then
create your cursor using the SELECT statement

Your base tables are not getting updated, thus no data in the cursor

Good Luck
Mohammed
www.imaginecorp.com


Quote:
> Problem with Select SQL and table buffering

> I have the folowing problem:
> I have two tables, in a one to many relation.
> The field the two tables are linked by is called room_no.

> I have to make misc. processing on them using buffering, so I'm opening
them
> with buffering enabled (set to 5).

> Here is my problem.
> In my processing, I add / delete / change records to both tables.
> But, at some point, I need to create a temporary cursor with a inner join
> select.
> But, after select is executed, my temp cursor doesn't contains any of the
> changes I've made to that tables.
> I saw in the documentation Select openes the tables similar with USE ...
> AGAIN, so it doesn't take care of my last changes.
> How can I solve this?
> Might be the only way to build the cursor "by hand", in code ?

> Thank you.

> Bogdan

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.193 / Virus Database: 93 - Release Date: 9/19/00


Mon, 31 Mar 2003 03:00:00 GMT  
 Problem with Select SQL and table buffering
You could always try the following, but it depends upon your situation:

Begin Transaction
TableUpdate( table1 )
TableUpdate( table2 )
Select .... (your inner join
Rollback

Des


Quote:
> Problem with Select SQL and table buffering

> I have the folowing problem:
> I have two tables, in a one to many relation.
> The field the two tables are linked by is called room_no.

> I have to make misc. processing on them using buffering, so I'm opening
them
> with buffering enabled (set to 5).

> Here is my problem.
> In my processing, I add / delete / change records to both tables.
> But, at some point, I need to create a temporary cursor with a inner join
> select.
> But, after select is executed, my temp cursor doesn't contains any of the
> changes I've made to that tables.
> I saw in the documentation Select openes the tables similar with USE ...
> AGAIN, so it doesn't take care of my last changes.
> How can I solve this?
> Might be the only way to build the cursor "by hand", in code ?

> Thank you.

> Bogdan



Mon, 31 Mar 2003 03:00:00 GMT  
 Problem with Select SQL and table buffering
Thank you for all answers.
I have build my cursor by hand (scan, scatter / gather name ... and all the
stuf).

It would be nice, in future versions of VFP, Select SQL to take care of
table buffering, if enabled on some of processed tables, and use the actual
values.

Regards


Quote:
> Problem with Select SQL and table buffering

> I have the folowing problem:
> I have two tables, in a one to many relation.
> The field the two tables are linked by is called room_no.

> I have to make misc. processing on them using buffering, so I'm opening
them
> with buffering enabled (set to 5).

> Here is my problem.
> In my processing, I add / delete / change records to both tables.
> But, at some point, I need to create a temporary cursor with a inner join
> select.
> But, after select is executed, my temp cursor doesn't contains any of the
> changes I've made to that tables.
> I saw in the documentation Select openes the tables similar with USE ...
> AGAIN, so it doesn't take care of my last changes.
> How can I solve this?
> Might be the only way to build the cursor "by hand", in code ?

> Thank you.

> Bogdan



Tue, 01 Apr 2003 07:04:04 GMT  
 Problem with Select SQL and table buffering
Thank you.

Your advice seems very good, unfortunately I can't use it in my particular
situation, since I'm using free tables, so I can't use transactions.

Regards,
Bogdan

Quote:

> You could always try the following, but it depends upon your situation:

> Begin Transaction
> TableUpdate( table1 )
> TableUpdate( table2 )
> Select .... (your inner join
> Rollback

> Des



> > Problem with Select SQL and table buffering

> > I have the folowing problem:
> > I have two tables, in a one to many relation.
> > The field the two tables are linked by is called room_no.

> > I have to make misc. processing on them using buffering, so I'm opening
> them
> > with buffering enabled (set to 5).

> > Here is my problem.
> > In my processing, I add / delete / change records to both tables.
> > But, at some point, I need to create a temporary cursor with a inner
join
> > select.
> > But, after select is executed, my temp cursor doesn't contains any of
the
> > changes I've made to that tables.
> > I saw in the documentation Select openes the tables similar with USE ...
> > AGAIN, so it doesn't take care of my last changes.
> > How can I solve this?
> > Might be the only way to build the cursor "by hand", in code ?

> > Thank you.

> > Bogdan



Tue, 01 Apr 2003 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Problem with Select SQL and table buffering

2. SQL-Select on buffered tables

3. SQL Select & Table Buffering

4. SQL-Select from table-buffered cursor

5. How can you SQL select buffered tables

6. How can you SQL Select buffered tables

7. SQL Select from Buffered Table

8. Table buffering and SQL SELECT and DE

9. Update SQL On Buffer Mode Override = Optimistic Table Buffering

10. Table Buffering Problem (using level 5 buffering)

11. Problem of putting the result of SQL Select to an existing table

12. - How to SELECT from table buffered view without issuing TABLEUPDATE

 

 
Powered by phpBB® Forum Software