SQL-Select from table-buffered cursor 
Author Message
 SQL-Select from table-buffered cursor

How can you use a SQL-SELECT to query the current contents of a SQL
view with table buffering?  When I make changes to a table-buffered
SQL view (the child in a one-many form) and then execute a SQL-SELECT
on the view, the result is based on the old values, not the current
values.  Is there a way to get the SQL-SELECT to read the current
values without first having to TABLEUPDATE the changes to disk?
(FYI I'm using VFP 5.0a with VSSP2 installed.)

See below for an example of what I mean.

                     Mind you, moose bites can be pretty {*filter*}...

*) This example sets up a table-buffered cursor, makes changes
*) to that cursor, and then attempts to query the cursor using
*) a SQL-SELECT.

CLOSE DATABASES
CREATE DATABASE TestDBC

*-- Set up a base table
CREATE TABLE TestDBF (nID I(4), FName C(10))
SET COLLATE TO 'MACHINE'
ALTER TABLE 'TestDBF' ADD PRIMARY KEY nID TAG nID
INSERT INTO TestDBF VALUES (1, "Jeffrey")
INSERT INTO TestDBF VALUES (2, "Michael")
INSERT INTO TestDBF VALUES (3, "Laurel")
INSERT INTO TestDBF VALUES (4, "Benjamin")
INSERT INTO TestDBF VALUES (5, "Lyta")

*-- Create an updatable view
CREATE SQL VIEW TestView AS SELECT nID, FName FROM TestDBF
=DBSetProp('TestView', 'View', 'UpdateType', 1)
=DBSetProp('TestView', 'View', 'WhereType', 3)
=DBSetProp('TestView', 'View', 'SendUpdates', .T.)
=DBSetProp('TestView', 'View', 'Tables', 'TestDBC!TestDBF')
=DBSetProp('TestView.nID', 'Field', 'KeyField', .T.)
=DBSetProp('TestView.nID', 'Field', 'Updatable', .T.)
=DBSetProp('TestView.nID', 'Field', 'UpdateName', 'TestDBC!TestDBF.nID')
=DBSetProp('TestView.FName', 'Field', 'KeyField', .F.)
=DBSetProp('TestView.FName', 'Field', 'Updatable', .T.)
=DBSetProp('TestView.FName', 'Field', 'UpdateName',
'TestDBC!TestDBF.FName')

*-- Use the cursor
CLOSE TABLES
USE TestView ALIAS Names
=CURSORSETPROP("Buffering", 5)  && Set table buffering on
BROWSE NOWAIT
GOTO TOP
WAIT WINDOW "This is the table-buffered cursor 'Names'"

*-- Make some changes in the cursor
LOCATE FOR FName = "Jeffrey"
REPLACE FName WITH "John"
LOCATE FOR FName = "Laurel"
REPLACE FName WITH "Susan"
LOCATE FOR FName = "Benjamin"
REPLACE FName WITH "Stephen"
LOCATE FOR FName = "Lyta"
REPLACE FName WITH "Talia"

*-- Show the user the current state of the cursor
BROWSE NOWAIT
GOTO TOP
WAIT WINDOW "This is the table-buffered cursor 'Names' now"

*-- Run a SQL-SELECT on the cursor
SELECT FName, LEFT(FName, 1) AS FInitial FROM Names INTO CURSOR Initials
SELECT Initials
BROWSE NOWAIT
WAIT WINDOW "These are the initials of people in the cursor 'Names'"
USE IN Initials

*-- Maybe ask the user whether to save or revert the changes
IF .T.  && or some condition
  =TABLEUPDATE(1, .F., 'Names')  && Update all rows
  *-- Run another SQL-SELECT on the cursor
  SELECT FName, LEFT(FName, 1) AS FInitial FROM Names INTO CURSOR Initials
  SELECT Initials
  BROWSE NOWAIT
  WAIT WINDOW "These are now the initials of people in the cursor 'Names'"
ELSE
  =TABLEREVERT(.T., 'Names')  && Revert all rows
ENDIF



Sat, 19 Aug 2000 03:00:00 GMT  
 SQL-Select from table-buffered cursor



Quote:


> > How can you use a SQL-SELECT to query the current contents of a SQL
> > view with table buffering?  When I make changes to a table-buffered

> My main confusion lies in figuring out why you're trying to query against
> uncommitted changes... ?

Thanks for the quick response, Dave.

The parent table has two foreign keys from the child table to point to
its primary and secondary child; the parent view cursor is row-buffered,
the child view cursor is table-buffered.  The v_Parent.PrimaryChild and
v_Parent.SecondaryChild are selected using comboboxes populated by a
SQL-SELECT.

The user would be able to add or modify child records while editing a
parent, but still have the option to save or discard all changes made
for that parent (if for example the user was editing the wrong parent).
Also the user would be able to add a new parent along with some child
records, and then have the option either to save or to discard the new
parent along with its children (if for example the user was adding a
duplicate parent).
But in either case, the user could select a new or modified child to be
the primary or secondary child.  For this I would need to populate the
two comboboxes with data from the uncommitted buffer.

I could probably populate the comboboxes with arrays instead of SQL,
and just make sure the arrays match the child view cursor whenever
they need to be.  But for now I'm leaning toward the following method:
1) force the child view buffer to be saved to disk (or canceled)
sometime before the primary/secondary child key comboboxes receive
focus--probably when the page containing the child view's grid is
deactivated;
2) save new parent records to disk immediately to prevent RI problems
with (1) above;
3) instead of using a TABLEREVERT() to cancel new parent records
(along with their child records), use a cascading delete.
Any thoughts on which method above (or a different approach) would be
better?



Sun, 20 Aug 2000 03:00:00 GMT  
 SQL-Select from table-buffered cursor

There are a few possible solutions to select from a buffered table.

1. select youbufftable
    afields(a)
    create cursor temp from array a
    select yourtable
    scan
    scatter memvar
    insert into temp from memvar
   endscan
2.
    select yourtable
    begin tran           * important ( later roolback)
    tableupdate(.t.,.t.,'yourtable')
    select * from yourtable into cursor temp
    rollback

Quote:

>How can you use a SQL-SELECT to query the current contents of a SQL
>view with table buffering?  When I make changes to a table-buffered
>SQL view (the child in a one-many form) and then execute a SQL-SELECT
>on the view, the result is based on the old values, not the current
>values.  Is there a way to get the SQL-SELECT to read the current
>values without first having to TABLEUPDATE the changes to disk?
>(FYI I'm using VFP 5.0a with VSSP2 installed.)

>See below for an example of what I mean.

>                     Mind you, moose bites can be pretty {*filter*}...

>*) This example sets up a table-buffered cursor, makes changes
>*) to that cursor, and then attempts to query the cursor using
>*) a SQL-SELECT.

>CLOSE DATABASES
>CREATE DATABASE TestDBC

>*-- Set up a base table
>CREATE TABLE TestDBF (nID I(4), FName C(10))
>SET COLLATE TO 'MACHINE'
>ALTER TABLE 'TestDBF' ADD PRIMARY KEY nID TAG nID
>INSERT INTO TestDBF VALUES (1, "Jeffrey")
>INSERT INTO TestDBF VALUES (2, "Michael")
>INSERT INTO TestDBF VALUES (3, "Laurel")
>INSERT INTO TestDBF VALUES (4, "Benjamin")
>INSERT INTO TestDBF VALUES (5, "Lyta")

>*-- Create an updatable view
>CREATE SQL VIEW TestView AS SELECT nID, FName FROM TestDBF
>=DBSetProp('TestView', 'View', 'UpdateType', 1)
>=DBSetProp('TestView', 'View', 'WhereType', 3)
>=DBSetProp('TestView', 'View', 'SendUpdates', .T.)
>=DBSetProp('TestView', 'View', 'Tables', 'TestDBC!TestDBF')
>=DBSetProp('TestView.nID', 'Field', 'KeyField', .T.)
>=DBSetProp('TestView.nID', 'Field', 'Updatable', .T.)
>=DBSetProp('TestView.nID', 'Field', 'UpdateName', 'TestDBC!TestDBF.nID')
>=DBSetProp('TestView.FName', 'Field', 'KeyField', .F.)
>=DBSetProp('TestView.FName', 'Field', 'Updatable', .T.)
>=DBSetProp('TestView.FName', 'Field', 'UpdateName',
>'TestDBC!TestDBF.FName')

>*-- Use the cursor
>CLOSE TABLES
>USE TestView ALIAS Names
>=CURSORSETPROP("Buffering", 5)  && Set table buffering on
>BROWSE NOWAIT
>GOTO TOP
>WAIT WINDOW "This is the table-buffered cursor 'Names'"

>*-- Make some changes in the cursor
>LOCATE FOR FName = "Jeffrey"
>REPLACE FName WITH "John"
>LOCATE FOR FName = "Laurel"
>REPLACE FName WITH "Susan"
>LOCATE FOR FName = "Benjamin"
>REPLACE FName WITH "Stephen"
>LOCATE FOR FName = "Lyta"
>REPLACE FName WITH "Talia"

>*-- Show the user the current state of the cursor
>BROWSE NOWAIT
>GOTO TOP
>WAIT WINDOW "This is the table-buffered cursor 'Names' now"

>*-- Run a SQL-SELECT on the cursor
>SELECT FName, LEFT(FName, 1) AS FInitial FROM Names INTO CURSOR Initials
>SELECT Initials
>BROWSE NOWAIT
>WAIT WINDOW "These are the initials of people in the cursor 'Names'"
>USE IN Initials

>*-- Maybe ask the user whether to save or revert the changes
>IF .T.  && or some condition
>  =TABLEUPDATE(1, .F., 'Names')  && Update all rows
>  *-- Run another SQL-SELECT on the cursor
>  SELECT FName, LEFT(FName, 1) AS FInitial FROM Names INTO CURSOR Initials
>  SELECT Initials
>  BROWSE NOWAIT
>  WAIT WINDOW "These are now the initials of people in the cursor 'Names'"
>ELSE
>  =TABLEREVERT(.T., 'Names')  && Revert all rows
>ENDIF



Sun, 20 Aug 2000 03:00:00 GMT  
 SQL-Select from table-buffered cursor

I didn't have time to read this entire post but one time I had to do an SQL
Select from uncommitted buffer (it was a table buffer) and the way I
finally had to accomplish it was to take the table and do the following:

COPY TO <tempfilename>

and then query off of this new table created.  Everything in the view
(buffer included) will make it to that temp DBF.

Hope this helps.



Quote:
> > > How can you use a SQL-SELECT to query the current contents of a SQL
> > > view with table buffering?  When I make changes to a table-buffered



Mon, 21 Aug 2000 03:00:00 GMT  
 SQL-Select from table-buffered cursor


Quote:
> I didn't have time to read this entire post but one time I had to do an SQL
> Select from uncommitted buffer (it was a table buffer) and the way I
> finally had to accomplish it was to take the table and do the following:

> COPY TO <tempfilename>

That was my solution, too. I even took this to M$ -- only the second time
I had used one of my 2 support calls (the first one was at a client's
insistence). They didn't have any better answer for me....

--
Garrett Fitzgerald
UNICOM, Providence, RI
#INCLUDE "\Program Files\DevStudio\VFP\StdDsclm.h"
(ICQ: 7360952)



Mon, 21 Aug 2000 03:00:00 GMT  
 SQL-Select from table-buffered cursor

Won't there be drawbacks to using this as a substitute for buffering?  I
don't know how Fox handles Fox table file locking and what not for
Transaction processing, but I have encountered this in developing a C/S app
with SQL Server back-end and have come to the conclusion it's much better
in that scenario to keep the Transaction span as short as humanly possible,
so that locking periods would be much shorter.  The transactions ONLY
encompass the TABLEUPDATE() calls and commit or rollback depending on the
success of the TABLEUPDATE() calls.  I guess I wonder how Fox logs this
transaction activity also, and if there is a resource issue involved with
long-winded transactions.

Quote:
> 4) BEGIN TRANSACTION...ROLLBACK/END TRANSACTION.



Mon, 21 Aug 2000 03:00:00 GMT  
 SQL-Select from table-buffered cursor

Not surprised to hear that.  When I looked back and evaluated the headaches
I endured trying to accomplish this, I decided it was an approach I
wouldn't use again.  Of course, that didn't much help me when I was in the
thick of it. <g>



Quote:
> That was my solution, too. I even took this to M$ -- only the second time
> I had used one of my 2 support calls (the first one was at a client's
> insistence). They didn't have any better answer for me....



Mon, 21 Aug 2000 03:00:00 GMT  
 SQL-Select from table-buffered cursor



Quote:


> FoxPro record locks (SET MULTILOCKS ON).  Any other user attempting to
access
> records used in the transaction gets a message stating that the record is
> unavailable until you commit or roll back the transaction.  The rest of
the
> table's still accessible, though.

Excuse me if I'm wrong about this, but I think the original poster said he
was using table buffering.  The transactions will still use record
buffering only, even though the buffering mode is table?

Quote:
> I concur with keeping transactions as short as possible, and offer the
> solution up as an alternative to consider. I normally simply buffer the
> tables, show the child records in a grid, and throw Save and Cancel
buttons
> on a toolbar or the form to issue the TABLEUPDATE() or TABLEREVERT(),
> respectively.

Yeah, this is pretty much the norm I go with too.


Tue, 22 Aug 2000 03:00:00 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. SQL-SELECT on buffered cursors

2. SQL-Select on buffered tables

3. SQL Select & Table Buffering

4. Problem with Select SQL and table buffering

5. Problem with Select SQL and table buffering

6. How can you SQL select buffered tables

7. How can you SQL Select buffered tables

8. SQL Select from Buffered Table

9. Table buffering and SQL SELECT and DE

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

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

12. SELECT from a view with table buffering question

 

 
Powered by phpBB® Forum Software