Invalid Key Length

[fairviewcomputing]

tbl = table.open("hours")
indx = tbl.index_get("rchk1")
tbl.index_primary_put(indx)
tbl.fetch_last()
fld = tbl.field_get("rchk")
var->LastCheck = fld.value_get()
indx = tbl.index_get("rchk0")
tbl.index_primary_put(indx)
rec_count = indx.records_get()
fld = table.field_get("rchk")
tbl.fetch_first()
tbl.change_begin()
for x = 1 to rec_count
fld.value_put(var->LastCheck + x)
tbl.change_end()
tbl.fetch_next()
tbl.change_begin()
Next
tbl.change_end()
tbl.close()I am trying to assign check numbers to records that do not have numbers assigned. The "hours" table has a field "rchk" that holds the check numbers, numeric 5/0.
I have indexed this field using rchk1 and rchk0. I can step through the attached script and do not get an index error but when I run it, I get "invalid key length". The first part of the script puts the records in check number order so that I may retrieve the last check number using tbl.fetch_last(), then assign this number to a variable. The next section, using the index rchk0 finds only the records that have no check number and do have netpay >0. This is set up in the index definition. When I look at the results in the expression builder (for index) it shows me a four digit check number followed by a decimal point and six zeros. I don't understand why the decimal and zeros and if this is the cause of the key length error. I barely know how to write xbasic and this has me baffled (as usual).

[Peter.Wayne]

RE: Invalid Key Length

Howard,
I think part of the problem is that you are changing the field that
is indexed while you are fetching with that index. I'm not
sure if Alpha knows how to fetch_next after you change
the indexed field.

I would rewrite your code this way:

dim lastcheck as n
tbl = table.open("hours")
tbl.index_primary_put("rchk1")
tbl.fetch_last()
LastCheck = tbl.rchk
query.filter="isblank('rchk') .and. netpay>0"
query.order=""
query.options="M"
ix=tbl.query_create()
tbl.fetch_first()
x=0
while .not. tbl.fetch_eof()
x=x+1
tbl.change_begin()
tbl.rchk=lastcheck+x
tbl.change_end(.t.)
tbl.fetch_next()
end while
ix.drop()
tbl.close()

I think you will find this script will work.
indx = tbl.index_get("rchk0")
tbl.index_primary_put(indx)
rec_count = indx.records_get()
fld = table.field_get("rchk")
tbl.fetch_first()
tbl.change_begin()
for x = 1 to rec_count
fld.value_put(var->LastCheck + x)
tbl.change_end()
tbl.fetch_next()
tbl.change_begin()
Next
tbl.change_end()
tbl.close()

[Peter.Wayne]

RE: Invalid Key Length

oops - i left part of your script at the end of my message - ignore the part
after "this will work"

[fairviewcomputing]

RE: Invalid Key Length

A great big thanks for responding to my call for help. A couple of comments:
1 - query.options("M"). The "M" is not one of the options shown in my xbasic manual. What is the meaning?
2 - In my text I mentioned "netpay". Actually it's "RegNetPay", so I got an error "field not found" but that wasn't too hard to find.
3 - The code you sent works perfectly if I step through it, bypassing the "While" and "End While" ....but...if I run it normally, I get an error and can't figure out why. Error is: ERROR: mismatched end of loop or if block. I checked the syntax in my manual and everything looks OK. Any ideas?
I am struggling to learn more about xbasic but I have a long way to go.
Howard

[Stan Mathews]

RE: Invalid Key Length

Howard,

I had trouble with this one before, myself. It's a good idea to print out the Readme files that come with each version.

New Options for .Query_Create Method
Alpha Five now has two new option flags for the .query_create() method. These options are set with
the query.options variable. The options are:
query.options = "M"
If this option is specified, Alpha Five will build a new query list even though a) an existing query list exists,
and b) an existing Index exists (both of which, Alpha Five could have used rather than running a new
query).
query.options = "X"
If this option is specified, Alpha Five will use an index even if an existing query exists (which Alpha Five
would otherwise have used in preference to the index)
If no options are specified, then when you run a query, Alpha Five does the following:
a) if the same query has been previously run, use that query list.
b) if not, but if an index that satisfies the query criteria exists, use the index.
c) if a previously run query does not exist, and if no index exists, run a new query.
Previously, Alpha Five only offered one option, �N�, which caused Alpha Five to run a query, rather than
selecting an index, if an index would have satisfied the query criteria.
If you want to trade off speed for certainty that your applications will run correctly, you should consider
using the �M� option in your Xbasic code. (In most applications the speed difference will probably not even
be noticeable.) This ensures that whenever you use the query_create() method, Alpha Five is creating a new
query from scratch, and not relying on a previously run query, or an index, that could be corrupt.

[fairviewcomputing]

RE: Invalid Key Length

Thanks, Stan. My manual shows an "N" for the option you specify as "M".

[fairviewcomputing]

RE: Invalid Key Length

I found my "bug" in the while/end while. I neglected to put a space between the while and the .not. Guess these old eyes need some refurbishing. Now...I am back to the same problem I reported first...Invalid key length. Even though the program seems to work when I use interactive mode and step through it. Could this problem be somewhere else and not with this particular code? This is the only code attached to the "Print Checks" button at this point.

[JohnZaleski]

RE: Invalid Key Length

Howard,
Is your highest check number going to be greater than 99999 using the old or new program?? Did you rebuild your indexes before testing the new code??
John

[fairviewcomputing]

RE: Invalid Key Length

John, Check numbers are currently 4 digits. I have space for 5.
Yes, I have rebuilt indexes until I am blue in the face....
I don't know what I did but now the procedure works fine without errors. Murphy's law says, "If you fool around with something long enough you will break it". I guess there is an opposite law, too: "Be careful, you may even fix it".
Thanks, John