<tbl>.fetch_find() - Conflicting expression Data types

[aschone]

I have written a script that is designed to be ran from an external database. The scripts purpose is to document tables/sets and some details about the structures.

I have used the Fetch_find() several times in the script, frequently when I run the script I will receive "Conflicting expression data types". Usually the error message is tied to line #76, 149, & 172. I have not received an error message tied to the use of the Fetch_find() on line 43 as of yet.

It is my understanding that I am setting the index to a character value and am doing a Fetch_find() using a character value. I am struggling on how to develop an approach on troubleshooting this. Up till now its just been a guessing game that I am losing at horribly.

I have attached a copy of the database, the script "Documenter" will need to be edited for the environment that it is ran in. The following lines will need to be changed to point to the proper file paths. (Side challenge - anyway to have the table paths be dynamic so they work no matter where you put the database?)

Dbs = "X:\DB_Doc\Dbs.dbf"
Tbls = "X:\DB_Doc\Tbls.dbf"
Flds = "X:\DB_Doc\Flds.dbf"
Sets = "X:\DB_Doc\Sets.dbf"
Child_tbls = "X:\DB_Doc\Child_tbls.dbf"

When I run the script I am usually in an interactive window of a sperate database and execute. Again the path will have to be modified to fit the enviroment

script_play("Documenter@x:\db_doc\db_doc.alb")

Any help or comments are appreciated.

Thank you for your time
-Andy

[martinwcole]

Re: &lt;tbl&gt;.fetch_find() - Conflicting expression Data types

I didn't try your zip, but am doing something like this already.

the index definition must match the search key - but it does not have to ba a character

like the index for "clients" is on lastname and named lastname

t=table.open("c:\somefolder\someotherfolder\clients")
'(I am referencing a table outside the current database)
t.index_primary_put("lastname")
variable="Smith"
rec=t.fetch_find(variable)
if rec > 0
msgbox("","foundit")
else
msgbox("","didn't find it")
end if
t.close()

[Mario Prieto]

Re: &lt;tbl&gt;.fetch_find() - Conflicting expression Data types

The reason you are getting that error message is because you have ti index the table with whatever field you are searching on. For example. If I want to find the record that contains the first_name="Mario"

pTbl=table.open('names.dbf') 'Open table
pTbl.index_primary_put('names') ' Here is where you get the problem.Make sure you have an index with the field you are searching

pTbl.fetch_first()
nRec=Ptbl.fetch_find("Mario") 'It can also be a variable that contains "Mario"
pTbl.fetch_goto(nRec)

etc....


I hope this helps.

[aschone]

Re: &lt;tbl&gt;.fetch_find() - Conflicting expression Data types

Based on my table design each record in the children tables have two identifying fields that combined make the record unique. My first attempt at making indexes I tried to have the index include both fields. I had an issue where if I opened the database in Version 5 I would lose the index. I also wasnt sure I could use the <tbl>.fetch_find() on an index that is an concatenation of two fields, one being numeric the other character. Those are the reasons why I choose not to use indexes originally.

Instead I opted to make queries that would would sort on a character field and then filter on the numeric field. It was my understanding that by placing the sort on the character field I could perform the <tbl>.fetch_find() on the same character field simply by using a variable argument that was of the character type. This method works sporadically which is why I made the post.

When I attempt to run the script sometimes it will complete without an error message, other times it will only be able to scan a couple of tables prior to throwing an error message.

*edited info

Forgot to mention that after seeing the posts I created indexes on the fields that I am attemping to do the <tbl>.Fetch_find() on. But because I need to filter the table as well, I am doing a <tbl>.Index_primary_put() to set the index then running a <tbl>.Query_create() immediately afterwards. Then I tested the script and still received the error message.

[aschone]

Re: &lt;tbl&gt;.fetch_find() - Conflicting expression Data types

I believe that I have eliminated the index errors that I was receiving, but in order to make the script work I had to do a lot more error checking than I expected.

First and foremost I discovered that if your index configuration contains anything other than just the field in the "order expresion" your index will be lost if you try to open the database in version 5. If you recreate the index in version 5 then open the database in version 8 the index again will be gone. Because of this my script now handles this problem and will re-create the index on the fly via <tbl>.index_tag_add(). This issue was the original reason why i choose to use a query instead of an index to begin with.

The next issue has to do with the <tbl>.fetch_find() function. I am running this function inside of a for..next loop. I would assume that if I apply a query or an index prior to entry of the for..next loop that it would stay in effect the entire time I left the table pointer open for processing. This was simply not the case. Sporadically I would receive "Conflicting expression Data types" error messages. Never did this error occur on the first loop of the for..next loop. It would only happen on the 2nd or subsequent loops. I have discovered that somehow the table would revert back to the recno index and this is what caused the error. While I have resolved the issue, I would like to understand why this happened.

To resolve the issue with the index changing sporadically while in mid process I now set the filter using <tbl>.index_primary_put(). I do this just before I run the <tbl>.fetch_find() command.