VFP6 Parameterized View Data Persistence

My first time working with remote data, connections, and views. A simple
form accepts input (user id) that is passed as a filter in the SELECT
portion of a CREATE SQL VIEW command. A view is created and a local query
against the view retrieves data into a cursor. Since the view is
parameterized only one record is available in the cursor (the record that
matches the user id). A child form displays the cursor data. Afterward, the
cursor is destroyed with the USE command and the parameterized view is
eliminated with DELETE VIEW. The process works fine the fist time in a
session.

The problem is, each successive time in a session that the use inputs data
and launches the process, the data from the first time through is
persistent. In other words, the first user in a session (user A) inputs
their id and gets back their information (Data A) as intended. All
subsequent users in a session enter their id (user X) and get back the
original user's information (Data A).

Sleuthing reveals that the .TMP file created for the local cursor has the
same name each time a user launches the process. I would normally expect
.TMP file names to be randomly generated each time a cursor is created. I
have confirmed that the .TMP file is deleted from the drive each time the
cursor is cleared. But the next cursor creates the same .TMP file name.

Using INDBC() I have also confirmed that the parameterized view is deleted
each time the process completes. I also confirmed that a different parameter
(user ID) is passed in the SELECT portion of subsequent CREATE SQL VIEW
command iterations.

Where does the persistence come from and how do I get rid of it?

* Load event of form contains

OPEN DATABASE mydbc

*mydbc contains CONNECTION (dbccon) to remote data source

*capture user input

lcUserId = THISFORM.Text1.Value

*validate locally

*concatenate user input to SELECT statement and assign to local variable

lcSelStr = "SELECT fields FROM remotetable WHERE somefield = " +
lcUserId

*create athe parameterized view

CREATE SQL VIEW myview CONNECTION dbccon AS &lcSelStr

*query data from view

SELECT fields FROM myview INTO CURSOR csrFromView

* validate non-empty cursor

*display retrieved data in child form

DO FORM childform WITH fields

*close query and cursor

USE IN csrFromView

*delete view from database

DELETE VIEW myview

Willianto
Wed Nov 05 14:49:54 CST 2003

Hi Fred:
> My first time working with remote data, connections, and views. A
> simple form accepts input (user id) that is passed as a filter in the
> SELECT portion of a CREATE SQL VIEW command. A view is created and a
> local query against the view retrieves data into a cursor.
What do you mean 'passed as a filter in the SELECT portion...' Are you
sure you create the parameterized view correctly? Your command to create
the view should look about like this:
CREATE SQL VIEW rv_viewname REMOTE "Conn" AS ;
SELECT * FROM the_table WHERE table_id = ?liTableID
Notes: Conn should be define as a connection in the dbc.

I was in your position a couple of years ago, and I started to learn
views from native vfp view designer. It's easier. Try to check the view
you created with native vfp view designer.

hth,
Willianto

>Since the
> view is parameterized only one record is available in the cursor (the
> record that matches the user id). A child form displays the cursor
> data. Afterward, the cursor is destroyed with the USE command and the
> parameterized view is eliminated with DELETE VIEW. The process works
> fine the fist time in a session.
>
> The problem is, each successive time in a session that the use inputs
> data and launches the process, the data from the first time through is
> persistent. In other words, the first user in a session (user A)
> inputs their id and gets back their information (Data A) as intended.
> All subsequent users in a session enter their id (user X) and get
> back the original user's information (Data A).
>
> Sleuthing reveals that the .TMP file created for the local cursor has
> the same name each time a user launches the process. I would normally
> expect .TMP file names to be randomly generated each time a cursor is
> created. I have confirmed that the .TMP file is deleted from the
> drive each time the cursor is cleared. But the next cursor creates
> the same .TMP file name.
>
> Using INDBC() I have also confirmed that the parameterized view is
> deleted each time the process completes. I also confirmed that a
> different parameter (user ID) is passed in the SELECT portion of
> subsequent CREATE SQL VIEW command iterations.
>
> Where does the persistence come from and how do I get rid of it?
>
> * Load event of form contains
>
> OPEN DATABASE mydbc
>
> *mydbc contains CONNECTION (dbccon) to remote data source
>
> *capture user input
>
> lcUserId = THISFORM.Text1.Value
>
> *validate locally
>
> *concatenate user input to SELECT statement and assign to local
> variable
>
> lcSelStr = "SELECT fields FROM remotetable WHERE somefield =
> " + lcUserId
>
> *create athe parameterized view
>
> CREATE SQL VIEW myview CONNECTION dbccon AS &lcSelStr
>
> *query data from view
>
> SELECT fields FROM myview INTO CURSOR csrFromView
>
> * validate non-empty cursor
>
> *display retrieved data in child form
>
> DO FORM childform WITH fields
>
> *close query and cursor
>
> USE IN csrFromView
>
> *delete view from database
>
> DELETE VIEW myview

Trey
Wed Nov 05 15:04:38 CST 2003

Actually, you haven't really created a parameterized view - you've been
[re]creating a custom view every time a user hits a particular form. I think
in this case you would benefit from rethinking this process a bit. This
doesn't answer the TMP table persistence question, but rather avoids the
issue entirely.

Creating the view every time will cause some performance hits, so create it
once as a parameterized view, set it to NoDataOnLoad in the DataEnvironment
of the form, and REQUERY() it with the given parameter. This way, you won't
have to use the csrFromView at all -- e.g.

** create the view - will persist in the database [this is a good thing :)]
** note: it can clarify things in other code by naming the view parms
differently
** the ? before makes it a parameterized view
CREATE SQL VIEW myview REMOTE CONNECTION dbccon as ;
SELECT fields FROM remotetable WHERE somefield = ?vpcUserId

** modify the child form [i.e., the one the info is displayed in], add the
view to the dataenvironment (database must be opened first)
** set the view's cursor's NoDataOnLoad property =.T.
** in the child form Init(), change parameters to take the user id instead
of a field list
** and makes sure the controls on the child form only display the fields
you're interested in
LPARAMETERS tcUserId
Local vpcUserId
vpcUserId=tcUserId
REQUERY("myview")

This way all users are using the correct data from the view.

"Fred Simmons" <djei38@hotmail.com> wrote in message
news:enXQYt9oDHA.2588@tk2msftngp13.phx.gbl...
> My first time working with remote data, connections, and views. A simple
> form accepts input (user id) that is passed as a filter in the SELECT
> portion of a CREATE SQL VIEW command. A view is created and a local query
> against the view retrieves data into a cursor. Since the view is
> parameterized only one record is available in the cursor (the record that
> matches the user id). A child form displays the cursor data. Afterward,
the
> cursor is destroyed with the USE command and the parameterized view is
> eliminated with DELETE VIEW. The process works fine the fist time in a
> session.
>
> The problem is, each successive time in a session that the use inputs data
> and launches the process, the data from the first time through is
> persistent. In other words, the first user in a session (user A) inputs
> their id and gets back their information (Data A) as intended. All
> subsequent users in a session enter their id (user X) and get back the
> original user's information (Data A).
>
> Sleuthing reveals that the .TMP file created for the local cursor has the
> same name each time a user launches the process. I would normally expect
> .TMP file names to be randomly generated each time a cursor is created. I
> have confirmed that the .TMP file is deleted from the drive each time the
> cursor is cleared. But the next cursor creates the same .TMP file name.
>
> Using INDBC() I have also confirmed that the parameterized view is deleted
> each time the process completes. I also confirmed that a different
parameter
> (user ID) is passed in the SELECT portion of subsequent CREATE SQL VIEW
> command iterations.
>
> Where does the persistence come from and how do I get rid of it?
>
> * Load event of form contains
>
> OPEN DATABASE mydbc
>
> *mydbc contains CONNECTION (dbccon) to remote data source
>
> *capture user input
>
> lcUserId = THISFORM.Text1.Value
>
> *validate locally
>
> *concatenate user input to SELECT statement and assign to local variable
>
> lcSelStr = "SELECT fields FROM remotetable WHERE somefield = " +
> lcUserId
>
> *create athe parameterized view
>
> CREATE SQL VIEW myview CONNECTION dbccon AS &lcSelStr
>
> *query data from view
>
> SELECT fields FROM myview INTO CURSOR csrFromView
>
> * validate non-empty cursor
>
> *display retrieved data in child form
>
> DO FORM childform WITH fields
>
> *close query and cursor
>
> USE IN csrFromView
>
> *delete view from database
>
> DELETE VIEW myview
>
>

Fred
Wed Nov 05 15:31:55 CST 2003

Thanks Trey.

I love it when someone can COMPETENTLY illuminate my ignorance. This
certainly makes sense and I will give it a try.

Thanks


"Trey Walpole" <treyNOpole@SPcomcastAM.net> wrote in message
news:OsDptB#oDHA.2188@TK2MSFTNGP11.phx.gbl...
> Actually, you haven't really created a parameterized view - you've been
> [re]creating a custom view every time a user hits a particular form. I
think
> in this case you would benefit from rethinking this process a bit. This
> doesn't answer the TMP table persistence question, but rather avoids the
> issue entirely.
>
> Creating the view every time will cause some performance hits, so create
it
> once as a parameterized view, set it to NoDataOnLoad in the
DataEnvironment
> of the form, and REQUERY() it with the given parameter. This way, you
won't
> have to use the csrFromView at all -- e.g.
>
> ** create the view - will persist in the database [this is a good thing
:)]
> ** note: it can clarify things in other code by naming the view parms
> differently
> ** the ? before makes it a parameterized view
> CREATE SQL VIEW myview REMOTE CONNECTION dbccon as ;
> SELECT fields FROM remotetable WHERE somefield = ?vpcUserId
>
> ** modify the child form [i.e., the one the info is displayed in], add the
> view to the dataenvironment (database must be opened first)
> ** set the view's cursor's NoDataOnLoad property =.T.
> ** in the child form Init(), change parameters to take the user id instead
> of a field list
> ** and makes sure the controls on the child form only display the fields
> you're interested in
> LPARAMETERS tcUserId
> Local vpcUserId
> vpcUserId=tcUserId
> REQUERY("myview")
>
> This way all users are using the correct data from the view.
>
> "Fred Simmons" <djei38@hotmail.com> wrote in message
> news:enXQYt9oDHA.2588@tk2msftngp13.phx.gbl...
> > My first time working with remote data, connections, and views. A simple
> > form accepts input (user id) that is passed as a filter in the SELECT
> > portion of a CREATE SQL VIEW command. A view is created and a local
query
> > against the view retrieves data into a cursor. Since the view is
> > parameterized only one record is available in the cursor (the record
that
> > matches the user id). A child form displays the cursor data. Afterward,
> the
> > cursor is destroyed with the USE command and the parameterized view is
> > eliminated with DELETE VIEW. The process works fine the fist time in a
> > session.
> >
> > The problem is, each successive time in a session that the use inputs
data
> > and launches the process, the data from the first time through is
> > persistent. In other words, the first user in a session (user A) inputs
> > their id and gets back their information (Data A) as intended. All
> > subsequent users in a session enter their id (user X) and get back the
> > original user's information (Data A).
> >
> > Sleuthing reveals that the .TMP file created for the local cursor has
the
> > same name each time a user launches the process. I would normally expect
> > .TMP file names to be randomly generated each time a cursor is created.
I
> > have confirmed that the .TMP file is deleted from the drive each time
the
> > cursor is cleared. But the next cursor creates the same .TMP file name.
> >
> > Using INDBC() I have also confirmed that the parameterized view is
deleted
> > each time the process completes. I also confirmed that a different
> parameter
> > (user ID) is passed in the SELECT portion of subsequent CREATE SQL VIEW
> > command iterations.
> >
> > Where does the persistence come from and how do I get rid of it?
> >
> > * Load event of form contains
> >
> > OPEN DATABASE mydbc
> >
> > *mydbc contains CONNECTION (dbccon) to remote data source
> >
> > *capture user input
> >
> > lcUserId = THISFORM.Text1.Value
> >
> > *validate locally
> >
> > *concatenate user input to SELECT statement and assign to local variable
> >
> > lcSelStr = "SELECT fields FROM remotetable WHERE somefield = " +
> > lcUserId
> >
> > *create athe parameterized view
> >
> > CREATE SQL VIEW myview CONNECTION dbccon AS &lcSelStr
> >
> > *query data from view
> >
> > SELECT fields FROM myview INTO CURSOR csrFromView
> >
> > * validate non-empty cursor
> >
> > *display retrieved data in child form
> >
> > DO FORM childform WITH fields
> >
> > *close query and cursor
> >
> > USE IN csrFromView
> >
> > *delete view from database
> >
> > DELETE VIEW myview
> >
> >
>
>

Trey
Wed Nov 05 15:40:12 CST 2003

quite welcome :)
"Fred Simmons" <djei38@hotmail.com> wrote in message
news:uEbX9Q%23oDHA.360@TK2MSFTNGP12.phx.gbl...
> Thanks Trey.
>
> I love it when someone can COMPETENTLY illuminate my ignorance. This
> certainly makes sense and I will give it a try.
>
> Thanks
>
>
> "Trey Walpole" <treyNOpole@SPcomcastAM.net> wrote in message
> news:OsDptB#oDHA.2188@TK2MSFTNGP11.phx.gbl...
> > Actually, you haven't really created a parameterized view - you've been
> > [re]creating a custom view every time a user hits a particular form. I
> think
> > in this case you would benefit from rethinking this process a bit. This
> > doesn't answer the TMP table persistence question, but rather avoids the
> > issue entirely.
> >
> > Creating the view every time will cause some performance hits, so create
> it
> > once as a parameterized view, set it to NoDataOnLoad in the
> DataEnvironment
> > of the form, and REQUERY() it with the given parameter. This way, you
> won't
> > have to use the csrFromView at all -- e.g.
> >
> > ** create the view - will persist in the database [this is a good thing
> :)]
> > ** note: it can clarify things in other code by naming the view parms
> > differently
> > ** the ? before makes it a parameterized view
> > CREATE SQL VIEW myview REMOTE CONNECTION dbccon as ;
> > SELECT fields FROM remotetable WHERE somefield = ?vpcUserId
> >
> > ** modify the child form [i.e., the one the info is displayed in], add
the
> > view to the dataenvironment (database must be opened first)
> > ** set the view's cursor's NoDataOnLoad property =.T.
> > ** in the child form Init(), change parameters to take the user id
instead
> > of a field list
> > ** and makes sure the controls on the child form only display the fields
> > you're interested in
> > LPARAMETERS tcUserId
> > Local vpcUserId
> > vpcUserId=tcUserId
> > REQUERY("myview")
> >
> > This way all users are using the correct data from the view.
> >
> > "Fred Simmons" <djei38@hotmail.com> wrote in message
> > news:enXQYt9oDHA.2588@tk2msftngp13.phx.gbl...
> > > My first time working with remote data, connections, and views. A
simple
> > > form accepts input (user id) that is passed as a filter in the SELECT
> > > portion of a CREATE SQL VIEW command. A view is created and a local
> query
> > > against the view retrieves data into a cursor. Since the view is
> > > parameterized only one record is available in the cursor (the record
> that
> > > matches the user id). A child form displays the cursor data.
Afterward,
> > the
> > > cursor is destroyed with the USE command and the parameterized view is
> > > eliminated with DELETE VIEW. The process works fine the fist time in a
> > > session.
> > >
> > > The problem is, each successive time in a session that the use inputs
> data
> > > and launches the process, the data from the first time through is
> > > persistent. In other words, the first user in a session (user A)
inputs
> > > their id and gets back their information (Data A) as intended. All
> > > subsequent users in a session enter their id (user X) and get back the
> > > original user's information (Data A).
> > >
> > > Sleuthing reveals that the .TMP file created for the local cursor has
> the
> > > same name each time a user launches the process. I would normally
expect
> > > .TMP file names to be randomly generated each time a cursor is
created.
> I
> > > have confirmed that the .TMP file is deleted from the drive each time
> the
> > > cursor is cleared. But the next cursor creates the same .TMP file
name.
> > >
> > > Using INDBC() I have also confirmed that the parameterized view is
> deleted
> > > each time the process completes. I also confirmed that a different
> > parameter
> > > (user ID) is passed in the SELECT portion of subsequent CREATE SQL
VIEW
> > > command iterations.
> > >
> > > Where does the persistence come from and how do I get rid of it?
> > >
> > > * Load event of form contains
> > >
> > > OPEN DATABASE mydbc
> > >
> > > *mydbc contains CONNECTION (dbccon) to remote data source
> > >
> > > *capture user input
> > >
> > > lcUserId = THISFORM.Text1.Value
> > >
> > > *validate locally
> > >
> > > *concatenate user input to SELECT statement and assign to local
variable
> > >
> > > lcSelStr = "SELECT fields FROM remotetable WHERE somefield = "
+
> > > lcUserId
> > >
> > > *create athe parameterized view
> > >
> > > CREATE SQL VIEW myview CONNECTION dbccon AS &lcSelStr
> > >
> > > *query data from view
> > >
> > > SELECT fields FROM myview INTO CURSOR csrFromView
> > >
> > > * validate non-empty cursor
> > >
> > > *display retrieved data in child form
> > >
> > > DO FORM childform WITH fields
> > >
> > > *close query and cursor
> > >
> > > USE IN csrFromView
> > >
> > > *delete view from database
> > >
> > > DELETE VIEW myview
> > >
> > >
> >
> >
>
>