Reordered points:
>>Is there a reason why you prefer currency?
As it spans only 8 bytes, it allows more keys to fit in the bTree page leaf, making all index using steps faster.
Justin Case reminds us, if you doubt original vfp docs:
https://www.ibm.com/docs/pt-br/was-nd/8.5.5?topic=code-activex-java-primitive-data-type-conversion-valuesAs they have
BigDecimal
another ISO-implementing Currency
plus homegrown types like
BCD in various flavors
YodaMoney
Also in MS "Currency" a human readable "function" already built in,
although IIRC you should verify MIN_VALUE and MAX_VALUE from backend can be display-handled in clientREPLACE mon WITH NTOM(0)
REPLACE mon WITH mon - NTOM(9000000000000)
REPLACE mon WITH mon - NTOM(200000000000)
REPLACE mon WITH mon - NTOM(20000000000)
REPLACE mon WITH mon - NTOM(3000000000)
REPLACE mon WITH mon - NTOM(300000000)
- if only to pipe out in error handlers or similar cases, if you hide the key from typical users (I do, but often have "smartass" setting to display keys in forms as well for debugging purposes)
>>Bigint allows values up to 2^63-1 (9,223,372,036,854,775,807)
Yes and No. Bigint allows that many single variations. BUT: Currency "Denomination" is NOT $1, but "CentPercent",
each next bit value having more 0.0001 more "worth". For simple addition and subtraction done in bookkeping with its fixed rules on how to round fractions occurring in real life (VAT calculation...) this is clearly more than enough. Vfp provides toe NtoM() and MtoN() functions in runtime.
Trying for a dumb example: if you have rented a safe inside a bank vault. dimensions are part of the contract. You might even have been told you can fit in a million $ in standardized gold bar size. Now you come with a million $ worth, but in copper bars and moan that they don't fit in.
If your room needs further heating (winter coming in your area...)
set talk off
set odometer to
local lnStop, lnDone, lnStart, lnSecs
= setup(10000000)
lnStop = 900
lnStop = 100000000 / reccount() * m.lnStop
lnDone = lnStop*reccount()
lnStart = seconds()
for lnRun = 1 to m.lnStop
= one_loop()
if mod(m.lnRun, 10)=1
? datetime(), lnRun, loopcnt.cur_loop
endif
next
lnSecs = seconds() - m.lnStart
? m.lnSecs, lnDone, "with ", reccount()
browse last nowait
select loopcnt
browse last nowait
function setup(tnRecs)
create cursor loopcnt (autopk int autoinc, cur currency, cur_loop currency)
create cursor chkcur (autopk int autoinc, cur currency, loop_add currency, cur_loop currency, st c(40))
dimension p[m.tnRecs,1]
p = ""
append from array p fields st
replace all cur with ntom((autopk-reccount())*0.0001)
go bott
replace cur_loop with ntom(0)
return
function one_loop()
select chkcur
go bottom
scatter name oScat fields like cu*
insert into loopcnt from name oScat
local lCurAdd
lCurAdd = ntom(reccount()*0.0001)
select chkcur
replace all ;
cur with cur + m.lCurAdd ;
loop_add with ntom(autopk*0.0001) + loopcnt.cur_loop ;
in chkcur
go bott
replace cur_loop with cur_loop + m.lCurAdd
>- What VFP field type, if any, actually works in a RV or CA for BIGINT PK > 2^31-1.
You could also use blob or binary string because:
>>>Again: As vfp NEVER does anyting but check if ==/!= and replace the value as received, why even think of FP or DP ?
>>>
It is a surrogate key. If you need something human readable, Currency is available or build Show() function translating into Hex...
and all "mathmatics" is done server side, also with the benefit that anchor and increase
SQL server does a better job "protecting" PK rules set in schema as vfps: " Value controlled by autoincrement Next and Step values" that are not protected in vfp...
>>>Again: As vfp NEVER does anyting but check if ==/!= and replace the value as received, why even think of FP or DP ?
>>>
It is a surrogate key. If you need something human readable, Currency is available or build Show() function translating into Hex...
>
>Bigint allows values up to 2^63-1 (9,223,372,036,854,775,807)
>
>CREATE TABLE test (f1 Y, f2 B,f3 F(20))
>APPEND BLANK
>replace f1 with 9223372036854775807
>*---Error: numeric overflow. Data was lost.
>replace f2 with 9223372036854775807
>*---no error; but field contains 9223372036854780000
>replace f3 with 9223372036854775807
>*---no error; but field contains 9223372036854780000
>
>
>Currency unusable; other field types also unusable because they drop digits.
>Clearly VFP cannot handle full range for BIGINT data type.
>
>For this context: I agree there's a lot of deleting and inserting to get an Identity column from 2^31-1 to 2^63-1. So, trying again with 2^40-1:
>
>- and *any* of the field types is usable, with 1,099,511,627,775 approx 500X the INT upper limit.
>
>This means that for my purpose, VFP *can* handle BIGINT values for the forseeable future as Identity approaches 2B.
>
>If you've got the idea I need to see or munge the field- no, but if a row is edited with Identity PK>2^31-1, it needs to update the row correctly. So, what field type to use in the WHERE clause for a RV or CA? You advocate currency; I'd want to check
>
>- What VFP field type, if any, actually works in a RV or CA for BIGINT PK > 2^31-1.
>- If more than one type works: I'd want to compare performance of currency versus double and even float that may need twice the space, but who cares if it's just a VFP placeholder representation for an update WHERE clause. Is it quicker for a high volume interface?
>
>Is there a reason why you prefer currency?