updating table with a .cdx

[Koen Piller]

Hi,

Griff constgructed an indexroutine

Function HIERACHY
	Parameters m.KeyField
	PRIVATE m.KeyField,m.String, m.WordCount, I 
	m.Wordcount = GetWordCount(m.KeyField,".")
	m.String = STR(m.WordCount,3,0)
	For I = 1 to m.WordCount
		m.String = m.String + STR(VAL(GetWordNum((m.KeyField,I,".")),3,0)
	Next
	Return(m.String)

which works perfectly.
This procedure will create a .cdx file. When updating my table it errors "The nonstructural CDX file should be closed"
Is there a construction/coding possible to have this indexroutine as index part of the table to overcome this error?

Stay healthy,
Koen

 

[Mike Lewis]

Koen, could you please explain how this routine is used (or link back to Griff's original post)? I can see what the code is doing, but it is not clear how that relates to the CDX.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

That said, I assume from the error message that you are trying to create a structural CDX (one whose name is the same as the DBF), but another CDX (with a different name) is currently in force.

If that's the case, all you need to do is to issue CLOSE INDEXES. That will close the non-structural index, but allow the structural index to be opened or remain open.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You rarely create a CDX, you create a tag in the main CDX of a table, you create this main CDX when you issue the first INDEX ON expression TAG tagname for a DBF or create the first index in the table designer.

And the only thing that's special about using a function in an index expression is, that SET PROCEDURE must make this function available, or usage of the CDX gets in trouble. Remember the main CDX of a table will be maintained automatically. If you update data or insert a new record all tags of the main CDX will need an update and for that to work the Hierarchy function must be visible to VFP. Besides that specialty for indexes based on user-defined functions, there is nothing special about updating a table which has a cdx besides perhaps also an fpt, you only USE the DBF, any memo like fields automatically go into the fpt and any index changes due to data changes automatically go into the CDX. For this to be possible in case an index expression uses a function, well, as already said, VFP must know where to find that to be able to call it.

I guess the problem is somewhere here.

You can create secondary CDXes when you use the OF CDXFilename clause of the INDEX command, but I doubt you do. Then there can be any number of additional IDX files, but they play no role anymore, could be used for temp indexes, but I doubt that you use the hierarchy only temporary and that you put this in an extra idx or cdx. Do you?

As Mike already said, you don't actually show the code creating an index (tag), you just show the function used in the index expression. I remember this was in a lengthy recent thread, not more than a month old. It won't matter, but what matters is, that you got some wrong ideas here about how indexes work, it seems. The only reason you would put that into an extra CDX I see as possible is, that this index is larger than usual tags. Though I remember we made it some C(5) at max for each node, wasn't it in that regime? Then you're far below something even as simple as an index on a char(20) field, for example, for which you also wouldn't create an extra CDX file, so size isn't the issue. The only other reason would be to not depend on SET PROCEDURE to be configured correctly every time you use the dbf. The index then won't update automatically, though, just like IDX index files need to be open/set to update together with table data changes. So it's not a good reason.

I think besides that function, you got INDEX ON hierarchy(keyfield) TAG tagname or something like that. And, well, that's something you only do once, then this index tag is part of your table like any field is, just like you don't ALTER TABLE ADD COLUMN every time you use a table, you also don't INDEX ON anymore, once an index is established, you only use it from then on. The only added dependency with an index expression calling self-defined function is, that this function must be visible when you work in the DBF.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Mike,

Koen, could you please explain how this routine is used (or link back to Griff's original post)? I can see what the code is doing, but it is not clear how that relates to the CDX.

I am referring to thread id=1804754

The purpose is to construct an order/sequence

My dbf consists of a ID which is a little bit different constructed as we know usualy: it is unique, with the following construction:
a.b.c.d.e
where a is the master and is always 1
where b is the first related record to a
where c is the first related record to b
a.s.o. and the sylables are nu numbers i indicating the sequence so 1.3.12
means 1 is the 'godfather', 3 is the third child of 1, 12 is the twelfth child of the third child of the godfather
so you can end up ID's like:
1.1
1.1.2.1
1.1.2.2
1.2.1
1.3.1
which I would like to order:
1.1.
1.2.1
1.3.1
1.1.2.1
1.1.2.2

I use Griff's procedure, which creates a Names.CDX using Names.DBF
When updating Names.dbf it errors/complains a out a CDX which should be closed, I can only close the CDX when I also close the DBF, I cannot update a closed DBF. So if I could find a way to convert/implent Griff's method into the IDX, I think I will be fine.
For completeness here is Griffs, perfectly working procedure:

Parameters m.KeyField
Private m.KeyField,m.String,m.WordCount,I
m.WordCount = Getwordcount(m.KeyField,".")
m.String = Str(m.WordCount,2,0)
For m.I = 1 To m.WordCount
	m.String = m.String + Str(Val(Getwordnum(m.KeyField,m.I,".")),2,0)
Next
Return(M.String)

Stay healthy,
Koen

 

[Olaf Doschke]

Griffs, procedure still just computes one hierarchy value, not an index.

You had to have something like INDEX ON HIERARCHY(keyfield) TAG tagname

And for this index to work, the Hierarchy function has to be visible. And I know, that doesn't explain an error message telling you to close a cdx, but it must have to do with this.
You open VFP, you have no SET PROCEDURE at all, you USE your table with this index, now even just an APPEND BLANK will lead to an error like "Function Hierarchy not found", or, as I know VFP then rather thinks this must be an array name "Array HIERARCHY not defined".

Well, now that you have such an index, you always have to have SET PROCEDURE TO functions.PRG with Griffs HIERARCHY function, or the DBF won't work. An index isn't just created once when you INDEX ON, it becomes an active component, it's expression is repeatedly used for new or changed data to add to the index, too.

I guess you have a corrupt index and that leads to some error like that, so reindex the dbf. And once that's rebuild never use the DBF Without the HIERARCHY function visible.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,

You rarely create a CDX, you create a tag in the main CDX of a table, you create this main CDX when you issue the first INDEX ON expression TAG tagname for a DBF or create the first index in the table designer.

And the only thing that's special about using a function in an index expression is, that SET PROCEDURE must make this function available, or usage of the CDX gets in trouble. Remember the main CDX of a table will be maintained automatically. If you update data or insert a new record all tags of the main CDX will need an update and for that to work the Hierarchy function must be visible to VFP. Besides that specialty for indexes based on user-defined functions, there is nothing special about updating a table which has a cdx besides perhaps also an fpt, you only USE the DBF, any memo like fields automatically go into the fpt and any index changes due to data changes automatically go into the CDX. For this to be possible in case an index expression uses a function, well, as already said, VFP must know where to find that to be able to call it.

However 'it does not work'. I have following code in the click on myform:

Set Confirm Off
*!* SelectUnbuffered('names')
*!* Index On hierarchy(parenteelid) To ParenteelIndex
SET ORDER TO HIERARCHY   && HIERARCHY(" parenteelid")
Locate
Set Confirm On

the dbf is not sorted according to the method hierarchy, comment the line "Set Order" and uncomment the 2 lines 'Seletunbuffered... and Index on' creates a names.cdx and sets the dbf in the requested order. Fine however now I cannot update since a structural CDX must be closed.
Do you require a small test dbc with names.dbf?
Stay healthy,
Koen

 

[Olaf Doschke]

When you name your index PArenteeindex, you don't SET ORDER TO HIERARCHY.

It's not that complicated, just distinguish between function and index name and index file name, too.
And I always recommend not to name one like the other to get aware of the difference, ie also not to name an index on a simple field just like the field. Names and expressions are two different things.

And also when you create an IDX, while you use it the function must be visible to VFP. I didn't recommend and I won't recommend a separate CDX or IDX, just get used to need a SET PROCEDURE while you use your dbf with an index using Griffs function. If you store it as HIEARCHY.PRG, then CD into the folder and better also SET PATH TO it additionally, so HIERARCHY(x) can always be executed.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Besides, just by the way, if you use an IDX instead, the command to do so then is SET ORDER TO IDXFilename, instead of SET ORDER TO tagname, if you make it a tag of the CDX.

And I still recommend that, there is no size reason and you CAN and just NEED to make that a habit or start default... you can let the hierarchy function be visible before you use your table.

And then, last not least, look what HIERARCHY("parenteelid") results in.

You want to call HIERARCHY(parenteelid), field name without quotes. The field value is your parameter, not the field name.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

Hi Koen

I think you are using the wrong command to create your index, you probably need to use this construct:

use names exclusive
select names
index on hierarchy(parenteelid) tag Parenteel

This will create a structural index, a file called Names.cdx that can contain a number of tags, that will generally open whenever you open Names.dbf

That way you can update names as much as you like and the index will be kept up to date as well.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Koen Piller]

Griff,

Than I shall have to change the structure of my DBF
I now have:

without the quotes around parenteelID in the indexexpression, VFP errors.

Stay healthy,

Koen

 

[Olaf Doschke]

Well, likely there already is a names.cdx as you would always have at least an index on the primary key of a table. But that's just a side note.
Otherwise I second Griffs way of indexing, make your index a tag of the normal structural CDX index file a DBF already has anyway.

Index usage then simply is

SET ORDER TO Parenteel

or more verbose

SET ORDER TO TAG Parenteel

and most verbose

SET ORDER TO TAG Parenteel OF Names.cdx

But using the names.cdx is default, I wouldn't get that verbose.

But, but, the last but, always have HIERARCHY.PRG visible by SET DEFAULT (or CD) or by SET PATH ... ADDITIVE, at best before you even USE the dbf. It's not optional.
The only situation in which it is becoming option is when you don't change the index, ie when you only read from the DBF, but even in that case you want to sort by it, you want it to accelerate queries and locates. So just when you buy into using a user defined function as an index expression, you also have to buy into making it visible while you use the table.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

without the quotes around parenteelID in the indexexpression, VFP errors.

Well, what error do you get?

It can't be right just because it doesn't error. Griffs function pulls out the digits separated by points and recombines them the way it's necessary. If you pass in "parenteelid", then there are neither points nor digits in that, you always get an empty string as result of that, and it is no wonder that index gets corrupt and causes all kinds of errors, as an index with all nodes being an empty string isn't working, even not when it doesn't need to be unique, just a regular index. That comapres to a c(0) field, which also will error sooner or later. So it won't perhaps error while indexing, but when using the table.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

There is an error in that screenshot Koen
the index key should be:

hierarchy(parenteelid)

hierarchy(" parenteelid") will produce the same index value for any record because " parenteelid" is a constant not a reference to a field

The route you are using though can't 'see' hierarchy() to create the index or maintain it.

Which is why the code version works better, although if you have the code for hierarchy() in a source file you could SET PROCEDURE TO HIERARCHYCODE.PRG ADDITIVE so the function was visible in the IDE

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Let me assume what error you get while indexing, it#s the function not being found or array not defined. Maybe it's even just that paranteelid is not the string datatype it should be. Anyway, the function HIERARCHY() must be available, visible, known where to find by VFP, while you index and later when you use the table.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

If your DBF is part of a DBC, it would be a good idea to add Griff's function to the stored procedures.

Though when that DBC is in some share then the first call to it would load the DBCs stored procs first and so a local drive location or embedded within the EXE will always be the simplest and fastest way as VFP looks inside its EXE first, then in the current directory and then elsewhere. The main advantage there is it's found. Under many conditions. I imagine a condition where it's not found, but for that, you'd really need to desperately want to hide it from being found.

You can't just have it in the code you use to index, then the indexing works, but nothing afterward. The HIERARCHY() function has to stay available to VFP, it will not become part of the DBF or CDX header nor does VFP automatically make it a stored procedure. The index expression is stored in the CDX header and will be taken and evaluated every time a new node is added to the index tree at least, but only the expression, ie the call of HIERARCHY(), not the definition itself.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
I am getting the error on updating, indexing works ok.
The function hierarchy is available.
Parenteelid is a string (c 25)
Stay healthy
Koen

 

[Olaf Doschke]

If this was still at the time you called HIERARCHY(" parenteelid") I think we clarified that's not the way to call it, both me and Griff.

You have to delete that tag and create it new with the right expression HIERARCHY(parenteelid). You can't just change the expression when the CDX is corrupt, better even DELETE TAG ALL and recreate all indexes from scratch.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Hi,

Olaf gave me the solution with his remark that

the procedure should be available

. The file is located in my Progs direcotry, the Progs directory is in my path, but aparantly not visible to the dbf, why I don't know, but a simple

Set procedure to hierarchy

in my startup.prg before calling the dbf saved that.

error when not using quotes on the indexexpression:

the result/sorting order when using the index expression hierarchy("parenteelid") is shown on the left, the result when sorting on in shown on the right.
I am fully satisfied with the result on the left and can now update my dbf without an error about a non-structural index file to be deleted.

Thanks all for participating in this matter.
Stay healthy,
Koen

 

[Olaf Doschke]

Again, hierarchy("parenteelid") is wrong, it can't do it's job, it isn't passing in the parenteelid value but always the constant string literal "parenteelid".

Here are essential points in Griffs function and what "parenteelid" leads to:

? hierarchy("parenteelid")

Function HIERARCHY()
	Parameters m.KeyField                      && value passed in is "paranteelid"
	PRIVATE m.KeyField,m.String, m.WordCount, I
	m.Wordcount = GetWordCount(m.KeyField,".") && will be 1, no "." is present, which means the whole string counts as one word
	m.String = STR(m.WordCount,3,0) && will be "  1"
	For I = 1 to m.WordCount && loop from 1 to 1, one iteration
		m.String = m.String + STR(VAL(GetWordNum((m.KeyField,I,".")),3,0) && will be "  1  0"
	Next
	Return(m.String)

Every index node of your index will have the value " 1 0". I wrongly said it would be empty, as I was thinking of the word count to be 0, even then the starting value of STR(m.WordCount,3,0) would be " 0". Anyway, an index on a constant result will not sort anything. In an index, where all values are equal, everything is just sorted as is, by recno, there is no sorting effect unless data already is in the order you want it by recno() anyway.

So, I'm pretty sure as your form now shows data in the order you want it, your index is on Hierarchy(parenteelid).

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name