VFP9 - tag a date and integer together as primiray key 2

[RedLion]

Hello,

In the tablebuilder I have added as expression for the primary key this expression str(id)+dtoc(lastdate) where id is of type integer and lastdata is of type date. When I try to add something to the table I get the error message Error building key for index "c:\...table.cdx" tag "p_key"..

The problem occurs for the use of dtoc(), I find it very strange because dtoc delivers a nice string?!?

What am I overlooking?

Thanks,

Charl

 

[bborissov]

try:

INDEX ON STR(Id)+DTOS(LastDate)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Hi Charl,

The problem is that DTOC() can return different values, depending on the date settings (SET DATE, SET CENTURY, etc). If these settings change between creating the index and accessing the table, you could get an error.

There might also be issues with adding a record with a blank date or a blank ID. These could cause the index strings to be of different lengths. You really need keys to be fixed in length.

For those reasons, I suggest you try the following as your index key:

PADR(id, 10) + DTOS(LastDate)

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[RedLion]

Thanks for all your quick responses!

Mike Yearwood, the problem of using a key that has no meaning to your data means that you aren't guarding the integrity of your data. And for easy programming it is indeed handy to sometimes use an extra field to make the data unique, but that still means that you need to add some guards. This key is for my child table, where my parent table (where id comes from and is generated) has an integer auto numbering data type, at that place the GUID could also be used.

Borislav, this didn't help.

Mike Lewis, thanks for the inside information about DTOC(), I hadn't got a clue what the problem was, and indeed it delivers not always the same results. Using padr(id,10) will give problems using high numbers as key because it will create an overlap on high numbers, using an other pad symbol than the '0' will fix this problem. But this still returned the exception. But this fixed my problem STR(id)+ALLTRIM(STR(DAY(LastDate)))+ALLTRIM(STR(MONTH(LastDate)))+ALLTRIM(STR(YEAR(LastDate))). I always use str because it creates a string of size 10 with spaces.

The only disadvantage for this solution is probably that it will cost a lot of performance...finding the right piece of a date, transform it to a string, trimming the string...there are namely heavy batch inputs into this table.

Thanks for your help guys!

Charl

 

[bborissov]

Charl,
making a non fixed size index key you are asking for troubles.
What didn't work with

INDEX ON STR(Id)+DTOS(LastDate)

This gives you a fixed length index and it didn't cares about current SET DATE format. Then key must look like this:

*123456789012345678
*         120060101
*         520061123

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Charl,

Just to pick up on one of your points:

Using padr(id,10) will give problems using high numbers as key because it will create an overlap on high numbers,

My suggestion of using 10 as the second argument was fairly arbitrary. You could equally use 20 or something even higher (but, as you rightly say, at the expense of performance).

That said, your solution should work just as well. The point is that you are creating a fixed-length string whose value does not depend on SET commands or other things that can change.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[RedLion]

Borislav, you're right my key was still not of fixed size so I changed it to STR(id)+PADL(DAY(LastDate),2)+padl(MONTH(LastDate),2)+ALLTRIM(STR(YEAR(LastDate))).
Yes, I had added candidate to the end of the statement INDEX ON STR(Id)+DTOS(LastDate) because I want to guard for duplicates being inserted into the table.

Mike Lewis, you're right, I was thinking to fast, and the problem only occurs when the key reaches 10 digits, otherwise there are no problems with the use of padr(id,10). While I thought something else...stupid

1234567890
----------
0000002000
2000002000
problem:
2
or
2000000000
no problem:
999999999

But problem solved.

Thanks again,

Charl

 

[bborissov]

Charl, DTOS() will give you Date field in ANSI format:
YYYYMMDD no mattehr what is your SET DATE or SET CENTURY settings, I use that kind of indexes all over my application and have no problems with duplicate records. But if you solve your problems that's OK.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[RedLion]

Ow I'm very sorry Borislav! You should think he isn't listening and ignoring me, it wasn't till now that I see that the function you mension is an other one (with a 's' instead of a 'c'). Thanks for the fact you resisted to convince me! And thats why you both get a star.

Charl