Hans L


I am trying to index a view.

After having created the view in a prg file, created with Gendbc, I add (in the prg file):


USE vwMysoft
INDEX ON cname TAG Namn OF vwMysoft
USE

In other words, I use the same commands I would use for a table. Okay

Then, when I open a form where I need the indexed view open, I do:

USE vwMysoft
SET ORDER TO Namn


Error message: Variable Namn is not found.

Either I am missing something very obvious, or I have no clue how to index a view.

In any event, help would be appreciated.

Regards,

Hans L



Re: Problem indexing views

CetinBasoz


View indexes are volatile because views themselves are not tables but SQL definitions. You need to index a view each and everytime you use it. Once closed index is gone.

Code Block

use myDbc!vwMySoft

index on cname tag Namn

Later in code you may do:

Code Block
set order to tag namn

but as long as it's not closed.






Re: Problem indexing views

Hans L

Aha, that explains it. Thank you very much, Cetin! Hans L





Re: Problem indexing views

Naomi Nosonovsky

Just to add to Cetin's response - the view must be in buffering 3 mode in order to be indexed (default mode). So, if you use table-buffering you need to switch to record-level buffering first, then switch back.





Re: Problem indexing views

Hans L

Thanks, Naomi.

I have no table buffering yet. My next challenge is to figure out what I have to do to update views and to update tables.

I have an Undo button on each screen with updatable data, so I need to buffer the view (and if default is okay, good), and then, I have to decide when I want to update the underlying table (not until I close the application -- and the view -- I guess).

Regards,

Hans L




Re: Problem indexing views

Naomi Nosonovsky

Hi Hans,

You can leave default 3 (record level buffering), though I prefer to use table level buffering to have more control. Make sure to not buffer underlying table. So, when you want to save your changes you simply issue tableupdate (always check return status of this function) and to Undo do tablerevert.





Re: Problem indexing views

Hans L

Do you mean to say that I use tableupdate and tablerevert for the view And, if that is correct, is the table updated also when I use tableupdate

Hans L




Re: Problem indexing views

CetinBasoz

Yes, the table is updated when you use tableupdate.

If, for whatever reason your table is buffered too, then it's updated but on disk version is not updated yet. Then you need a tableupdate on table too to persist modifications on disk. (Normally underlying table is not buffered and it's immediately persisted to disk when you tableupdate the view. In case you buffer the table keep this in mind).





Re: Problem indexing views

Hans L

Okay, Cetin, I undertand. Thank you both for very valuable info, which will allow me to get by this important step.

Regards,

Hans L




Re: Problem indexing views

Naomi Nosonovsky

Cetin already explained. If you have a view buffered and a table non buffered, then tableupdate on the view updates the table.





Re: Problem indexing views

Hans L

Thanks, Naomi. I have come a long way, but I am not at saving data yet. However, I have one problem. Let me show:

[Creating view vwMysoft]
USE vwMysoft
INDEX ON cname TAG Namn OF vwMysoft


[Creating view vwCategory]
USE vwCategory
INDEX ON cname TAG Namn OF vwCategory

Then ...

SELECT vwCategory
GO TOP
*
SELECT vwMysoft [Program error; see below]
GO TOP
*
DO FORM frmmslist
THISFORM.Release

-----------

Program Error:

When I run the program as I am writing this message, I get
"Alias 'VWMYSOFT' is not found".

Why does "SELECT vwCategory" work, but not "SELECT vwMysoft"

In any event, when I go on to list the records in the view vwMysoft, the list is empty. However, when I click my button to look at the deatails of a record (normally the one highlighted in the list), I actually get a screen with the first record in the list, and there, I have a combobox with RowSource = vwCategory.cname, and it works just fine!

I can't figure this one out. Does not views that you USE create an alias = view name

Regards,

Hans L











Re: Problem indexing views

CetinBasoz

Hans,

Use create an alias by default same as the name of the table/view.

Code Block

USE vwMysoft
INDEX ON cname TAG Namn OF vwMysoft

USE vwCategory
INDEX ON cname TAG Namn OF vwCategory

Use vwMySoft line opens it and next line indexes. It is opened in current workarea. Then you call:

use vwCategory

This call, while opening the table/view first closes any table/view/cursor that is open in current workarea. In other words you're closing vwMySoft while opening vwCategory.

To prevent such issues a safer way:

Code Block

select 0 && select first unused workarea

USE vwMysoft
INDEX ON cname TAG Namn


*[Creating view vwCategory]

select 0 && select first unused workarea
USE vwCategory
INDEX ON cname TAG Namn

Note that I also removed "OF ..." from indexing. Let it create structural index, which in effect same as yours but slightly better against typing errors.

Another side note:

SELECT vwCategory
GO TOP

is just fine. Prefer:

SELECT vwCategory
LOCATE

both have the same end result of locating the first record. Unlike "locate", "Go top" is not (at least was not) Rushmore optimized. For small tables it doesn't make any noticable difference. Might be a factor when table is large and/or there is a filter in effect (while this is known as a good practice with the enhancements in the engine proving the case is almost impossibleSmile.

PS: One drawback of "locate" is that it only works in current workarea, while with "go top" you can use "in < workarea >" and do that w/o changing workareas.





Re: Problem indexing views

Hans L

Thank you, Cetin, I'll try this out this afternoon (buzy with other stuff in the morning -- my shack outside needs repair). I think that "Select 0" is the key here. Grateful for the other info too. Will report back.

Regards,

Hans L




Re: Problem indexing views

Hans L

Well, I discovered that "USE [view] IN 0" does not work :-) So, I use what you suggested, "Select 0", and all is well.

As an side, is it possible to use "DROP View ALL" Kinda' cumbersome to have to delete all views individually.

Now, I am almost there. However, when I list the indexed view records in a list (a grid), the list is okay (in alphabetical order on name), but, no matter which record I (scroll down to) click, I always get to the details screen for the first record in the natural order.

I am looking for a solution.

Regards,

Hans L




Re: Problem indexing views

Hans L

I'm experimenting. I added the "SET ORDER ..." below ...

SELECT vwCategory
SET ORDER TO Name
GO TOP
*
SELECT vwMysoft
SET ORDER TO Name
GO TOP

... and got the error message that "Name" could not be found (or something similar). By experimenting a little more, I found out that it was the "Name" in vwMysoft that could not be recognized. The other Name was okay. Which is strange, since both views are indexed the same way:

SELECT 0
USE vwMysoft
INDEX ON cname TAG Namn

SELECT 0
USE vwCategory
INDEX ON cName TAG Name


And then, when I removed the "SET ORDER ..." in both cases (they are probably not needed, since I only develop one index so far), the list (grid) no longer shows the records in alphabetical order, but in natural order.

Boy, this is fickle!!! I can still only get to the detailed data screen for the first record in natural order.

Help, what is going on

Hans L