Changing Select Statement on a Defined RemoteView 1

[stanlyn]

Hi,

Normally I create a remote view with the wizard which returns all the rows.

I now need to limit what the remote view returns and do not see a way to manipulate the original view. I really don't want to create new views for every possible scenario. I just want to change the select statement.

The underlying sql table has 2 million rows and I need to process it 10000 rows at a time like with a top 10000.

I know how to do this as spt, but wanted to use the easyness of remote views...

in a .prg

use remoteview
modify the select statement in the remoteview and requery to get a subset
start processing.

Here is the code... (all in a standalone .prg) Its duty is to populate all 2 million sql rows by looking up the county_id (contained in both tables) and fill in the missing county name in the sql table (remoteview).

Set Decimals To 0
Set Escape On

* load local vfp table
If !Used('county')
	Use r:\Projects\vfp\Data\county.Dbf Shared
Endif

Select 'county'
Set Order To 'NATCODE'

aa = ''
lnCtr = 0
lnTotal = 0

* load remote view
If !Used('rv_mailinglist')
	Use Domainstatus!rv_mailinglist In 0
Endif

Set Multilocks On
CursorSetProp("Buffering", 5, "rv_MailingList")

Select 'rv_MailingList'
* would like to change what is brought down
* SET FILTER TO ALLTRIM(county_code) = '25'  && this works but way toooooo slow
Go Top

Do While !Eof('rv_MailingList')
	lnCtr = lnCtr + 1
	lnTotal = lnTotal + 1

	If lnCtr >= 100
		Wait Window Str(lnTotal) Nowait
		lnCtr = 0
		Select 'rv_MailingList'
		qq = Tableupdate(.T.)       && Stop every 100 rows and sync with backend...
	Endif

	aa = '16-' + Padl(Alltrim(rv_mailinglist.county_code), 3, '0')

	Select 'county'
	Seek aa In 'County'

	If Found('county')
		Replace rv_mailinglist.county_name With county.cnty_name
	Endif

	Select 'rv_MailingList'
	Skip 1
Enddo

Tableupdate(.T.)

And, how do I edit the sql code I see when clicking the "view sqL" button within the view editor wizard, since it only lives in the database? What does its object path look like?

Thanks for any pointers and suggestions.

Stanley Barnett

 

[Mike Lewis]

Stanley,

Are you saying that you want to get 10,000 specific rows at a time? That is, 10,000 rows that meet some criterion?

If so, then instead of modifying the SQL, you should use a parameterised filter. In the Filter tab of the view designer, enter the criterion that you want to apply, like this:

[tt]Sales > ?lnSales[/tt]

This will retrieve all rows where the sales field is greater than a defined figure. The question mark in front of lnSales indicates that lnSales is a variable in your program. Now, open the view like this:

[tt]USE MyView NDODATA[/tt]

This will retrieve an empty cursor. To retrieve some actual data, store the relevant sales figure in lnSales, and then issue REQUERY().

Or, do you mean that you want to retrieve 10,000 arbitrary rows? In other words, you want to get the first 10,000, then process them in some way, then get the next 10,000, and so on?

If so, you don't need to take any special action to deal with that. By default, remote views are asynchronous, which means that you can start processing the first batch of records while it is retrieving the next batch. The default batch size is 100. You can vary that using DBSETPROP() to change the FETCHSIZE property. But I can't help thinking this is not what you are asking.

If this does not answer your question, perhaps you could clarify how you want to select the particular 10,000 rows.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

If so, then instead of modifying the SQL, you should use a parameterized filter. In the Filter tab of the view designer, enter the criterion that you want to apply, like this:

I know I can do that, but how do I change it in code? I want to totally edit the views definition all within code. The code I'm talking about is when you modify a remote view and click on "View SQL". The code that shows is what I'd like to edit via code and not the designer ui.

Or, do you mean that you want to retrieve 10,000 arbitrary rows? In other words, you want to get the first 10,000, then process them in some way, then get the next 10,000, and so on?

I was also trying to do the entire 2 million rows and stop, update and requery along the way. Does the filter apply to the view's result set or is it being passed to the backend, so the backend knows what to return?

But I can't help thinking this is not what you are asking

Actually, I'm asking both ways... by asking how to modify the select statement within the view. If I fully understood how it works, what is allowed, what is not, and how to change it, then I can do either.

Is it an object? How do I reference it in code?

Also, another strange unexplained behavior I'm seeing is: in the status bar I can see it fetching data. If the data is being fetched (not yet finished) and I run the program, all tableupdates fail and it very quickly counts to numbers that is far higher that the number of rows and appears it will count indefinitely. I killed it once it hit 25 million. Once the fetch has completed, the programs works as expected. I'm talking about the code above...

Thanks, Stanley

 

[stanlyn]

And why do I have to close and reopen a view where changes were made in the designer. I was thinking requery would do it but it doesn't. The only way it works is close and reopen it. None of this works including the filter statement...

DO c:\domstatus\programs\fixcountynames.prg
REQUERY()
MODIFY VIEW rv_mailinglist
REQUERY()  &&same data from old settings

SELECT Rv_mailinglist
BROWSE LAST  &&same data from old settings
REQUERY()  &&same data from old settings
SET FILTER TO county_name = 'Perry'
REQUERY()  &&same data from old settings
GO bott
GO top
SET FILTER TO county_name = 'Perry'
REQUERY()  &&same data from old settings
SELECT Rv_mailinglist
SET FILTER TO county_name = 'Perry'
REQUERY()  &&same data from old settings

To get correct data, I have to close and reopen the view.

 

[Mike Lewis]

Stanley, I'll try answer your questions.

I want to totally edit the views definition all within code.

No, you can't do that. The view's underlying SQL code is exposed in a property called (appropriately) SQL. But the property is read-only. You can access it with DBGETPROP(), but you can't change it with DBSETPROP().

What you can do, however, is programmatically delete the view and then create a new one (using CREATE SQL VIEW). The new view can have whatever SQL code you want, so that would be one way of achieving your goal.

Does the filter apply to the view's result set or is it being passed to the backend, so the backend knows what to return?

It's passed to the back-end. That's really the whole point. The actual filtering is done on the back end and only the records that meet your criterion are sent back over the network.

And why do I have to close and reopen a view where changes were made in the designer.

Because REQUERY() works on the current query, and when you modify a view, you are in effect creating a different query. REQUERY() doesn't know you've change the view. It only knows about the query as it existed when it was created. Your test code (in your most recent post) demonstrates that.

None of this works including the filter statement...

There is no point in using SET FILTER, for two reasons: It does not affect the data being sent from the server; and in any case it is not particularly useful. The point here is that the filtering should be done on the server (as mentioned above). And you achieve that by using a parameterised filter in the view definition, as per my first post above.

Don't confuse "parameterised filter" with the type of filter that you create with SET FILTER. A parameterised filter is part of the underlying SQL's WHERE clause. So, in your example, the SQL would look something like: [tt]SELECT <some fields> FROM MyBackEndTable WHERE county = ?lcCounty[/tt]. Then, to retrieve records for a particular county, you do, for example, [tt]lcCounty = 'Perry'[/tt] followed by [tt]REQUERY()[/tt].

Finally, I'm not happy with your code that says "Stop every 100 rows and sync with backend..." In theory, this shouldn't be necessary because, as I mentioned earlier, remote views are synchronous, so you will only get records in batches of 100 anyway. But if you are really updating two million rows in one go, then I would question your entire approach. If your aim is to replace every county name with a new county name, then a better approach would be to programmatically construct a single UPDATE statement, and to send that to the back-end using SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Thanks Mike, you have answered a lot of asked and un-asked questions.

I'm not happy with your code that says "Stop every 100 rows and sync with backend..."

The idea behind this was to not update after every change, instead wait until a hundred view records gets changed, then stop and do a tableupdate() that would sync the view's data with the backend data.

If I understand you correctly, this whole tableupdate stuff is not needed due to it being synchronous and doesn't need a tableupdate(). Are you suggesting that tableupdate() is being implicitly applied after each row's value changes?

No, you can't do that. The view's underlying SQL code is exposed in a property called (appropriately) SQL. But the property is read-only. You can access it with DBGETPROP(), but you can't change it with DBSETPROP().

What you can do, however, is programmatically delete the view and then create a new one (using CREATE SQL VIEW). The new view can have whatever SQL code you want, so that would be one way of achieving your goal.

OK, that explains a lot and why things are not working as I am expecting... Thanks for that... Now what book did you read that explains what you just said, as I have read nowhere this crucial info.

I going to explore the create view in code as that is probably the best way, but how would I know?

But if you are really updating two million rows in one go, then I would question

On the surface, it should be ease with my code above that loads the entire 2 million rows in a view and do a dowhile not eof(), that iterates the view until the end. Then process each row by doing a quick seek to the vfp table, get the county name and do a replace, then onto the next. Real easy and fast with dbfs. And I've been told that remote views can be treated equally with same results, and I'm learning that is not true as demonstrated by this thread's contents. I know that many cringe at the thought of processing 2 million rows in one go. Actually, it takes about 5 seconds before data is shown in a browse window, and another 1+ minutes before the status bar shows all rows loaded.

Is it normal to not being able to do anything while the status bar activity is happening? It shows 1/23564 and later 1/235645 until it stops counting upward until it stops. My experience running the code above completely fails when the status bar activity is counting. Is this normal?

Thanks again...
Stanley

 

[stanlyn]

Now we are getting somewhere...

lcCountyName = 'MyCounty'

Create Sql View 'rv_ML1' Connection AppData ;
	AS Select pk, county_code, postal_code, county_name ;
	From MailingList ;
	WHERE county_name = ?lcCountyName

How would I write this using parameterization when what I need is:
a. county_name is null or empty
b. county_name is null or empty AND color is "red"

Also, how do I test whether a view exists by name?

Thanks,
Stanley

 

[stanlyn]

Progress is being made now... Here is the new version.

Set Decimals To 0
Set Escape On

If !Used('county')
	Use r:\Projects\vfp\Server\Data\county.Dbf Shared
Endif

Select 'county'
Set Order To 'NATCODE'   && NAT_CODE

aa = ''
lnCtr = 0
lnTotal = 0

If Used('rv_ML1')
	Use In Select('rv_ML1')
Endif

Delete View 'rv_ML1'

lcCountyName = 'Breathitt'

Create Sql View 'rv_ML1' Connection AppData ;
	AS Select pk, county_code, postal_code, county_name ;
	From MailingList ;
	WHERE county_name <> ''		&&= ?lcCountyName

Use 'rv_ML1' In 0
Select 'rv_ML1'

Set Multilocks On
CursorSetProp("Buffering", 5, "rv_ML1")

Select 'rv_ML1'
Requery()
Go Top
Browse Last

Do While !Eof('rv_ML1')
	lnCtr = lnCtr + 1
	lnTotal = lnTotal + 1
	Select 'rv_ML1'

	If lnCtr >= 1000
		Wait Window Str(lnTotal) Nowait
		lnCtr = 0

		If ! Tableupdate(.T.)
			Set Step On
		Endif
	Endif

	aa = '16-' + Padl(Alltrim(rv_ML1.county_code), 3, '0')

	Select 'county'
	Seek aa In 'County'

	If Found('county')
		Replace rv_ML1.county_name With county.cnty_name
	Endif

	Select 'rv_ML1'
	Skip 1
Enddo

Tableupdate(.T.)

If Used('rv_ML1')
	Use In Select('rv_ML1')
Endif

Delete View 'rv_ML1'

This code processes all 2 million rows in only 15 seconds after ignoring the error generated at the "Delete View 'rv_ML1'" line. I asked about that above.

Also note that this does not use parameterization as there is no ?variable parameter in use, instead I created a hardcoded where clause.

WHERE county_name <> ''		&&= ?lcCountyName

Is this acceptable?

I also found an article that was helpful at:

http://www.dfpug.com/loseblattsammlung/migration/demos/remviews.htm

Please comment on my questions and observations on my way through this...

Thanks again,
Stanley