implement auto increment
When I upgraded from vfp-6 to vfpr-9 I was looking forward to the auto increment to generate the primary keys for many of my tables. I tried changing the data type from integer to auto increment (no problem) but on my forms my current INSERT INTO statement generates a duplicate primary key violation. I know it's something simple but I can't find an example how to do it. Gary Jeurink --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/83FE1DCB481B47EEAEFF9168CC72B59F@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: implement auto increment
You need to leave the autoinc field out of your insert/update statements. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1359840076.20677.140661186014397.48352...@webmail.messagingengine.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: implement auto increment
IN This example I have scattered the contents of my table to working and set controlsource of my input boxes to thisform.working. IF MESSAGEBOX(Save Changes, 20)=6 SELECT (thisform.aliasis) IF thisform.working.id0 = SEEK(thisform.working.id, thisform.aliasis, ID) GATHER MEMO NAME thisform.working FIELDS EXCEPT Id ELSE APPEND BLANK GATHER MEMO NAME thisform.working FIELDS EXCEPT Id ENDIF ELSE GOTO thisform.current_record IN thisform.aliasis ENDIF SCATTER MEMO NAME thisform.working Jerry -Original Message- From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Gary Jeurink Sent: Saturday, February 02, 2013 1:13 PM To: 'ProFox Email List' Subject: implement auto increment When I upgraded from vfp-6 to vfpr-9 I was looking forward to the auto increment to generate the primary keys for many of my tables. I tried changing the data type from integer to auto increment (no problem) but on my forms my current INSERT INTO statement generates a duplicate primary key violation. I know it's something simple but I can't find an example how to do it. Gary Jeurink --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/8155957F617342FAB2E5A576722E7D67@jerryfootePC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: implement auto increment
Should have been jerry -Original Message- From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of jerry foote Sent: Saturday, February 02, 2013 5:05 PM To: 'ProFox Email List' Subject: RE: implement auto increment IN This example I have scattered the contents of my table to working and set controlsource of my input boxes to thisform.working. IF MESSAGEBOX(Save Changes, 20)=6 SELECT (thisform.aliasis) IF thisform.working.id0 = SEEK(thisform.working.id, thisform.aliasis, ID) GATHER MEMO NAME thisform.working FIELDS EXCEPT Id ELSE APPEND BLANK GATHER MEMO NAME thisform.working FIELDS EXCEPT Id ENDIF ELSE GOTO thisform.current_record IN thisform.aliasis ENDIF SCATTER MEMO NAME thisform.working Jerry -Original Message- From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Gary Jeurink Sent: Saturday, February 02, 2013 1:13 PM To: 'ProFox Email List' Subject: implement auto increment When I upgraded from vfp-6 to vfpr-9 I was looking forward to the auto increment to generate the primary keys for many of my tables. I tried changing the data type from integer to auto increment (no problem) but on my forms my current INSERT INTO statement generates a duplicate primary key violation. I know it's something simple but I can't find an example how to do it. Gary Jeurink --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/0DA109BF936149EA82AB45AF21824A41@jerryfootePC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
My preferred method is a UDF() that interrogates a table holding one record for each table in the system and it's next primary key. Access the relevant record, lock it for a short period of time whilst incrementing the next sequential record then unlock and return back the PK. This works faultlessly in Multi user systems. With such a method, you have to perform integrity control prior to use it. Because your DBFs are not in a safe. Any customer is able to open your nextpk table with excel and modify it ! Gérard. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/93F5E0FAA0D34473A8937E9B1C7CCE07@MuriellePC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Gerard, That is true for ALL the data not just the pointer table so that isn't an issue when DBF's are used because the whole of the DBF structure is available to change as long as the user has access rights. Normally a $ Share is sufficient to hide the data away from potential prying eyes on server hosted hardware. In any case, ALL data is available for hacking if one has the relevant expertise. My point in identifying this method was to indicate that there is a simple Multi-User alternative to using Autoincrement which is robust and reliable. The only really secure way of hiding your data is not to put it in DBF's in the first place but to put it into a SQL container like M$SQL or MySQL etc. Dave -Original Message- From: profox-boun...@leafe.com [mailto:profox-boun...@leafe.com] On Behalf Of Gérard Lochon Sent: 05 September 2012 07:09 To: ProFox Email List Subject: Re: auto increment My preferred method is a UDF() that interrogates a table holding one record for each table in the system and it's next primary key. Access the relevant record, lock it for a short period of time whilst incrementing the next sequential record then unlock and return back the PK. This works faultlessly in Multi user systems. With such a method, you have to perform integrity control prior to use it. Because your DBFs are not in a safe. Any customer is able to open your nextpk table with excel and modify it ! Gérard. [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/bd031ecabf2b60499200aab3dbb4a999f1265...@ex-a-fpl.fpl.LOCAL ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Gerard, That is true for ALL the data not just the pointer table so that isn't an issue when DBF's are used because the whole of the DBF structure is available to change as long as the user has access rights. Yes indeed, that's the genetic lack of open systems, but i wanted to especially point that because in only one operation you can make a lot of desagrements in many tables ; multiples accesses to each dbf to modify them needs a longer time and maybe is easier to trace back. Normally a $ Share is sufficient to hide the data away from potential prying eyes on server hosted hardware. In any case, ALL data is available for hacking if one has the relevant expertise. My point in identifying this method was to indicate that there is a simple Multi-User alternative to using Autoincrement which is robust and reliable. You know, my mind is always wondering when i see asks and further discussions about that problem ; because it is not new, and through dozens of years still the same old chats are remaining, as algorithmic solutions have been found long time ago. So, sometimes it becomes boring. So that, so what ? It looks like a psychotic fix about this point, but what about the multi-user update of a normal value, like a stock value ? That is the same game. In earlier 70's, when neither RDBS nor L4G existed, we had, using procedural languages (as COBOL) to respond to the same questions. And we did it. It is curious that this knowlegde basis can't be transmitted trough generations of valorous programmers ... The only really secure way of hiding your data is not to put it in DBF's in the first place but to put it into a SQL container like M$SQL or MySQL etc. IMO, the only really secure way is to fully understand the underlying mechanisms, and practise, practise ... MSSQL is not written in MSSQL language, nor MySql is written in MySql language. They are written in low-level language, the same ones that we used long ago. Presentation and application layers have obscured minds. Gérard. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/02ED87C28F7548F19B3B421B39CF4311@MuriellePC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Gerard, Yes we did it, of course we did and I was brought up on Cobol and Algol in the 70's so I understand where you are coming from very well. There were always ways in which to get around potential locking problems whatever you were using. In fact data records were locked in Cobol by placing an * in a particular column of the data record as record locking only came about in Cobol at a later date. I was on the conversion team with ICL in the early 80's when DBASE II single user was ported onto ICL's IBM PC equivalent Multi User Concurrent CPM (MUCCPM) by Digital Research before Multi-user PC networks had even been developed. We ran on thin ethernet at 10Mbs (we used to know it as Serial I/O) using protocol developed by Singer Business Machines who I joined straight from University and we had to allow for multiple virtual machines to update a single user DBASE II table because customers expected multi screens to be able to update the tables. http://www.mail-archive.com/profox@leafe.com/msg78221.html It wasn't supported but we did it nevertheless because we used our in depth knowledge of the hardware and software, so much the same as today. The good programmers will always find ways to circumvent the shortcomings of Languages and Systems by lateral thinking. A long time ago I learnt that there is no right or wrong way to write software, there are just ways, some of which may be more efficient than others. The end result is though that the end user NEVER EVER sees the work that goes into a program and doesn't in fact need to. They see the end result and if it works ... then fine, job accomplished. 4GL, RDBMS etc, etc still haven't changed the underlying attributes of good programmers to innovate and think out of the box and that is what makes the good ones stand out in the crowd. For example, I hate with a vengeance the .Net framework, but I work with it on a day to day basis, cursing the fact that I could write the same code in VFP far quicker but that is life. SQL Server is somewhat the same, but I work within its structure and restrictions... as well as working WITH all the superior bits that MySQL and M$SQL provide. Presentation and Application layers along with Data Layers are things that most good programmers will have learnt about years before the terminology was standardised and turned into a Mantra for everyone. We just didn't give all the attributes formal names. Consequently, the advent of Multi-Tier applications and OOP thinking wasn't a quantum leap, at least for myself. Embrace new technology and thinking as it is formalised and introduced but never forget that innovation and logical thinking is normally the way to solve most difficult programming problems and that is mostly a natural ability to concentrate under pressure and think outside the box... to whatever degree you are able. As for low level languages, I've done my share of writing in Assembler and supporting O/S's at that level, but the problems and solutions are exactly the same, apart from the fact that the restrictions on HOW you can solve problems at assembler level are obviously less than with a 4GL ... but the timescales are HUGE! I wrote a multi user ops system in 8K ... yes 8K not 8Mb or 8Gb that happily supported up to 127 terminals and gave ISAM files on the server with multi user access at the same time so I do speak with some authority, but what good would imparting that knowledge to new programmers achieve? Precisely nothing, that's what. It is in the past but the techniques and experience remain with me forever. There is a good saying.. Experience is the one thing that you only obtain immediately AFTER the very time that you need it most and it can't be taught, no matter how good a teacher you are, In essence, use the tools you have got to the best of your ability with your own creativity to solve the problems that are thrown at you. O, and just as a final addition. Take a look at some of the open source code and frameworks that are being developed by the younger guys these days. Some of the concepts and implementation methods are truly wonderful and innovative from a programming point of view (Geeky smile). Wow, that turned into a right soap-box session... oops! ... or should I say procedural OOPS being old school! Dave -Original Message- From: profox-boun...@leafe.com [mailto:profox-boun...@leafe.com] On Behalf Of Gérard Lochon Sent: 05 September 2012 12:06 To: ProFox Email List Subject: Re: auto increment Gerard, That is true for ALL the data not just the pointer table so that isn't an issue when DBF's are used because the whole of the DBF structure is available to change as long as the user has access rights. Yes indeed, that's the genetic lack of open systems, but i wanted to especially point that because in only one operation you can make a lot of desagrements in many tables ; multiples accesses to each dbf to modify
Re: auto increment
On Sep 5, 2012, at 7:51 AM, Dave Crozier wrote: Embrace new technology and thinking as it is formalised and introduced but never forget that innovation and logical thinking is normally the way to solve most difficult programming problems and that is mostly a natural ability to concentrate under pressure and think outside the box... to whatever degree you are able. IMO, if you can't do this, then being a programmer is not the right career for you. The only thing you can count on is that new stuff will always be coming, and the best developers are those who can discern between real improvements and things that are simply new and shiny. -- Ed Leafe ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/3e077321-2d68-4e96-8508-2c4673ba7...@leafe.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Gary, Your (Max+1) routine works fine, until the day when you run into VFP buffering when the records can be held in memory and not flushed out to disk. This occurs during begin transaction.end transaction and even spuriously when using standard VFP procedural instructions, especially in multi-user situations. Horses for courses I guess, but I would never use it. My preferred method is a UDF() that interrogates a table holding one record for each table in the system and it's next primary key. Access the relevant record, lock it for a short period of time whilst incrementing the next sequential record then unlock and return back the PK. This works faultlessly in Multi user systems. Dave -Original Message- From: profox-boun...@leafe.com [mailto:profox-boun...@leafe.com] On Behalf Of Gary Jeurink Sent: 24 August 2012 16:55 To: 'ProFox Email List' Subject: RE: auto increment A lot of information. Thanks. For now, the max+1 will work as long as I use an unfiltered list so that updates through view update only their key record but logging in users I use a username and encrypted password. They never see their actual member number id. So is it okay to stay simple with user-id if the security is really at the gate with user-name password to get in? Gary Jeurink -Original Message- From: Alan Bourke [mailto:alanpbou...@fastmail.fm] Sent: Friday, August 24, 2012 9:31 AM To: profox@leafe.com Subject: Re: auto increment On Fri, Aug 24, 2012, at 02:44 PM, Lew Schwartz wrote: I never understood why these routines would be any better than max()+1. Because they are GUIDs - globally unique. That fact is very important in some applications, but overkill for a lot of others. -- Alan Bourke alanpbourke (at) fastmail (dot) fm [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/bd031ecabf2b60499200aab3dbb4a999f1261...@ex-a-fpl.fpl.LOCAL ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/24/12 7:18 PM, Ken Dibble wrote:
There are no guarantees in life. But the danger of sys(2015) lies in it's
generation
based on the timestamp. What are the chances of collision on this even not
concatenating the machine name?
Somewhat high, I believe. System clock ticks processor cycles. As I
understand it, in VFP RAND() uses the system clock, and RAND() will
generate the same number over and over and over in a tight loop on a fast
machine, until the system clock ticks over.
Well, here's the test code I used:
{{{
create cursor temp (gid c(16))
for n = 1 to 100
insert into temp (gid) values (sys(2015))
endfor
select gid, count(*) from temp group by 1 having count(*) 1
}}}
Let me know how many non-unique rows you get on your processor. I'm running XP
on a
virtual machine so that could be throttling my system enough to not see any
issue.
With auto-incrementing integer keys, how would you handle offline inserts
without
things getting incredibly complex?
Paul
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/503bc8c7.3050...@ulmcnett.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Hi, Paul
Took about 3 seconds, no duplicates.
Win 7-64, 4 GB memory, 3-core AMD with Folding@Home running in the
background(!).
Dan Covill
San Diego
On 08/27/12 12:21, Paul McNett wrote:
{{{
create cursor temp (gid c(16))
for n = 1 to 100
insert into temp (gid) values (sys(2015))
endfor
select gid, count(*) from temp group by 1 having count(*) 1
}}}
Let me know how many non-unique rows you get on your processor. I'm running
XP on a
virtual machine so that could be throttling my system enough to not see any
issue.
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/503bccfa.2030...@san.rr.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
As always, it depends on what you're doing, how many users, how fast a network, how many transactions per minute, but: 1) FLOCKing a table means no one can update ANY record, where 2) RLOCKing the one record in the keys table that has the primay key you're incrementing has a much smaller window of opportunity for contention, and only one place in the code (the NewID() function you'll likely write as a Stored Procedure) where you have to handle the lock conflict, timeouts and retries. In my experience in most of the systems I've written, there are more updates than inserts. In addition, your FLOCK requires a more complex processing, SELECT MAX() that would require index access, file traversal, record readng, than the simple LOCK, READ, UPDATE, UNLOCK process of the separate table. I'm a big, big, big fan of non-data-bearing, integer primary keys for VFP tables. The entire engine is tuned to make them wickedly efficient, and programmer-friendly, too. If you've got to do data syncronization (master-master) there are other better designs, but for most of the SMB low-transaction data processing systems I've worked on, this is the way to go. Yes, it's true, VFP throws something called a header lock on a table any time it updates the number of records, and does some kind of index table locking when updating indexes, but the engine has the built in logic to handle the infrequent, transient conflicts these cause. There's even a SYS() function to tune the process. But in most day-to-day processes, these are too fast to worry about. On Sat, Aug 25, 2012 at 5:33 PM, Frank Cazabon frank.caza...@gmail.com wrote: No, if you do the flock, then you won't get duplicates. But you should only try to lock records or tables for the minimum time necessary. So using a separate table, locking the record, incrementing it and unlocking should be faster than your example and result in less contention for the lock On 25 Aug 2012 12:31, Lew Schwartz lew1...@gmail.com wrote: Getting back to max()+1 and ignoring one sided b-tree dirty buffer issues, are you guys telling me that: flock(mytable) select max()+1 as idno into temp insert values (,,temp.idno) blah, blah ... unlock Won't reliably produce unique idno's. (I don't have a network avail to test this out myself). --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- ... ___ Post Messages to: ProFox@leafe.com Subscription Main... This message: http://leafe.com/archives/byMID/profox/cafuu78fhtwfjto9h9exzxai-lsstxkxjbsgtjaxesmwcuux...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not cons... Report [OT] Abuse: http://leafe.com/reportAbuse/cafuu78fhtwfjto9h9exzxai-lsstxkxjbsgtjaxesmwcuux...@mail.gmail.com --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cacw6n4u2d-tg3hant9+ljzdtvquo6syegock6239g0ph+us...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
At 03:21 PM 8/27/2012, you wrote:
On 8/24/12 7:18 PM, Ken Dibble wrote:
There are no guarantees in life. But the danger of sys(2015) lies in
it's
generation
based on the timestamp. What are the chances of collision on this
even not
concatenating the machine name?
Somewhat high, I believe. System clock ticks processor cycles. As I
understand it, in VFP RAND() uses the system clock, and RAND() will
generate the same number over and over and over in a tight loop on a fast
machine, until the system clock ticks over.
Well, here's the test code I used:
{{{
create cursor temp (gid c(16))
for n = 1 to 100
insert into temp (gid) values (sys(2015))
endfor
select gid, count(*) from temp group by 1 having count(*) 1
}}}
Let me know how many non-unique rows you get on your processor. I'm
running XP on a
virtual machine so that could be throttling my system enough to not see
any issue.
None. AMD Athlon 64x Core Duo 5600+ @ 2.9 GHz.
It remains theoretically possible though. :)
With auto-incrementing integer keys, how would you handle offline inserts
without
things getting incredibly complex?
I understand the advantages of using GUIDs when you need to able to synch
offline data.
I don't synch offline data in my apps. I'm not convinced there's an
advantage to GUIDs in situations where centralized key generation is always
available.
I don't use auto-increment to generate keys. I use my tried-and true
stand-alone procedure and pk lookup table. It's based on the one in Booth
Sawyer's Effective Techniques, with some modifications.
Ken Dibble
www.stic-cil.org
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/5.2.1.1.1.20120827201403.01f9e...@pop-server.stny.rr.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
The VFP9 help for sys(2015) says: Calling SYS(2015) more than once during the same millisecond interval returns a unique character string. Meaning that the result will still be unique even though you call it more than once in the same millisecond. I ran Paul's code in 3 seconds, which generated 1 million strings and then compared them for duplicates. My observations indicated that the generation took much less time than the comparison, say on the order of 1 second. This would be 1000 calls to sys(2015) per millisecond, and I encountered NO duplicates. Personally, I'd rather use autoincrement, but I still don't see any evidence that sys(2015) generates duplicates. Dan Covill On 08/27/12 17:34, Ken Dibble wrote: On 8/24/12 7:18 PM, Ken Dibble wrote: There are no guarantees in life. But the danger of sys(2015) lies in it's generation based on the timestamp. What are the chances of collision on this even not concatenating the machine name? Somewhat high, I believe. System clock ticks processor cycles. As I understand it, in VFP RAND() uses the system clock, and RAND() will generate the same number over and over and over in a tight loop on a fast machine, until the system clock ticks over. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/503c3066.2000...@san.rr.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/27/12 7:43 PM, Dan Covill wrote: Personally, I'd rather use autoincrement, but I still don't see any evidence that sys(2015) generates duplicates. I think if I recall correctly the danger was with 2 separate processes of VFP on the same or different computers generating non-unique values when run in the same timeframe. This was the reason for concatenating the machine name plus maybe the process id. Auto-increment was easy and worked well, until my client wanted to bring a laptop offline and do real work with it. It turned out that the method I'd started with (sys(2015)) was the method I returned to, and upon reflection was the overall easiest, most reliable, and most flexible of the VFP-native pk-generating options. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/503c4c18.5060...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Morning Lew, I agree that vfp will probably do some kind of locking behind the scene. However, my point was about limiting the time of locking when generating the next id. On 25 Aug 2012 18:38, Lew Schwartz lew1...@gmail.com wrote: I would be extremely surprised if tableupdate() didn't do a behind the scenes lock/unlock cycle. Have a look at MSDN Buffering Data: http://msdn.microsoft.com/en-us/library/aa975707%28v=vs.71%29.aspx On Sat, Aug 25, 2012 at 6:31 PM, Frank Cazabon frank.caza...@gmail.com wrote: This is the process: Seek the record in the next id table that you want. Lock it. Replace the... OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http:... This message: http://leafe.com/archives/byMID/profox/cafuu78egqruof9rjtshv87xr7gr3oatrfqrrbjwtnsxxglz...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not cons... Report [OT] Abuse: http://leafe.com/reportAbuse/cafuu78egqruof9rjtshv87xr7gr3oatrfqrrbjwtnsxxglz...@mail.gmail.com --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAHEe=u5lgcap8xjplt+g-xh2mavgaimtb_8q2fb2jov0ine...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Don't even get me started on 32-bit vs 64-bit operating systems. Windows 98 could address every spot on a 128 GB hard drive, so why can't Windows XP address more than 4 GB of RAM? And even if there is some technical reason for this (as opposed to deliberate planned obsolescence): The CPU could technically address 64GB, XP was capped at 4GB because of driver issues discovered during development, is the official line anyway. -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1345888550.16301.140661119368557.54f54...@webmail.messagingengine.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On Fri, Aug 24, 2012 at 4:18 AM, Paul Hill paulroberth...@gmail.com wrote: Hi All, Guids are nice but can add a significant size overhead to your database. In my SQL database here I have 86 tables each with a primary key and 547 foreign keys. Currently I'm using 4 byte ints. Guids are 16 bytes, Given a table with a million rows the Guid version will be 11Mb larger, and that's just for 1 key. However, I do use Guids for replicating tables between different servers. For example, my Customer table can be shared between sites. Using a Guid I can easily identify the same customer at different sites. --- guids are not that bad compared to say scanned medical images. -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAJidMYLPLuiLM6sbLfEt=1-du+2ql6iweyjzegdhlff2hrh...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Not sure if I agree with you, Frank. You are still going to have contention using the separate table method. It'll just be in a different place. Also, max()+1 will be highly optiimized because it'll be supported by an integer index on the pk. You've got to lock/unlock and update 2 separate tables for every operation. On Sat, Aug 25, 2012 at 5:33 PM, Frank Cazabon frank.caza...@gmail.comwrote: No, if you do the flock, then you won't get duplicates. But you should only try to lock records or tables for the minimum time necessary. So using a separate table, locking the record, incrementing it and unlocking should be faster than your example and result in less contention for the lock On 25 Aug 2012 12:31, Lew Schwartz lew1...@gmail.com wrote: Getting back to max()+1 and ignoring one sided b-tree dirty buffer issues, are you guys telling me that: flock(mytable) select max()+1 as idno into temp insert values (,,temp.idno) blah, blah ... unlock Won't reliably produce unique idno's. (I don't have a network avail to test this out myself). --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- ... ___ Post Messages to: ProFox@leafe.com Subscription Main... This message: http://leafe.com/archives/byMID/profox/cafuu78fhtwfjto9h9exzxai-lsstxkxjbsgtjaxesmwcuux...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not cons... Report [OT] Abuse: http://leafe.com/reportAbuse/cafuu78fhtwfjto9h9exzxai-lsstxkxjbsgtjaxesmwcuux...@mail.gmail.com --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cafuu78eyaeduupsepylq6ypq3moywdv4onag-st4xujxwlf...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
I would be extremely surprised if tableupdate() didn't do a behind the scenes lock/unlock cycle. Have a look at MSDN Buffering Data: http://msdn.microsoft.com/en-us/library/aa975707%28v=vs.71%29.aspx On Sat, Aug 25, 2012 at 6:31 PM, Frank Cazabon frank.caza...@gmail.comwrote: This is the process: Seek the record in the next id table that you want. Lock it. Replace the next id with next id plus one. Unlock. Now you do your insert in whatever other table. Hopefully you are using table buffering so you do not have to lock the other table, just do an if tableupdate(). Your example locked the table, incremented the id, did the insert and then unlocked. By removing the insert from the code the time involved is less. Also if you have a million record table, the select max () +1 will probably be slower than the seek in a small table, even with the appropriate index. On 25 Aug 2012 18:02, Lew Schwartz lew1...@gmail.com wrote: Not sure if I agree with you, Frank. You are still going to have contention using the separate table method. It'll just be in a different place. Also, max()+1 will be highly optiimized because it'll be supported by an integer index on the pk. You've got to lock/unlock and update 2 separate tables for every operation. On Sat, Aug 25, 2012 at 5:33 PM, Frank Cazabon frank.caza...@gmail.com wrote: No, if you do the flock, then you won't get duplicates. But you should only try to lock records... text/plain (text body -- kept) text/html --- [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cafuu78egqruof9rjtshv87xr7gr3oatrfqrrbjwtnsxxglz...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Hi Lew, SELECT MAX() + 1 can result in two or more people getting the same next ID. If you were running the system at the same time I was, we could both run that code at almost the same time and end up with the same ID which could cause no end of problems. The standard ways to do this is to 1. use a function like NewID() or NextID() (can't remember the name) which you can find in the VFP samples which maintains a table with a row for each possible new id, locks the row, increments the ID and returns the incremented value. 2. use an autoincrement id 3. use a GUID Frank. Frank Cazabon On 24/08/2012 09:44 AM, Lew Schwartz wrote: I never understood why these routines would be any better than max()+1. Are they any faster? Also, can't these strings contain inadvertent 'bad' words? On Fri, Aug 24, 2012 at 4:52 AM, Dave Crozier da...@flexipol.co.uk wrote: Why not use thee routines to produce true Guids like Paul has said, then there is never a possibility of key duplicates: * Start Code * FUNCTION StringFromGUID(cGUID) LOCAL cBuffer, nBufsize nBufsize=128 cBuffer = REPLICATE(CHR(0), nBufsize*2) = StringFromGUID2(cGUID, @cBuffer, nBufsize) cBuffer = SUBSTR(cBuffer, 1, AT(CHR(0)+CHR(0), cBuffer)) RETURN STRCONV(cBuffer, 6) function CreateGuid() DECLARE INTEGER CoCreateGuid IN ole32 STRING @pguid DECLARE INTEGER CLSIDFromString IN ole32; STRING lpsz, STRING @pclsid DECLARE INTEGER StringFromGUID2 IN ole32; STRING rguid, STRING @lpsz, INTEGER cchMax LOCAL cGUID, cGUIDString, cGUID1 cGUID = REPLICATE(CHR(0), 16) 128 bits IF CoCreateGuid(@cGUID) = 0 cGUIDString = StringFromGUID(cGUID) *? cGUID *? cGUIDString * converting from String back to GUID cGUID1 = REPLICATE(CHR(0), 16) = CLSIDFromString(STRCONV(cGUIDString,5), @cGUID1) *? cGuid1 endif return Substr(cGuidString, 2, Len(cGuidString)-2) * endfunc * * End Code ** Dave [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/5037887f.2010...@gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On Fri, Aug 24, 2012, at 02:44 PM, Lew Schwartz wrote: I never understood why these routines would be any better than max()+1. Because they are GUIDs - globally unique. That fact is very important in some applications, but overkill for a lot of others. -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1345818669.4492.140661119061949.31634...@webmail.messagingengine.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Wow, way over my head, but I've got it and can use it as a stored procedure and play with it, right? Thanks Gary -Original Message- From: Dave Crozier [mailto:da...@flexipol.co.uk] Sent: Friday, August 24, 2012 3:52 AM To: ProFox Email List Subject: RE: auto increment Why not use thee routines to produce true Guids like Paul has said, then there is never a possibility of key duplicates: * Start Code * FUNCTION StringFromGUID(cGUID) LOCAL cBuffer, nBufsize nBufsize=128 cBuffer = REPLICATE(CHR(0), nBufsize*2) = StringFromGUID2(cGUID, @cBuffer, nBufsize) cBuffer = SUBSTR(cBuffer, 1, AT(CHR(0)+CHR(0), cBuffer)) RETURN STRCONV(cBuffer, 6) function CreateGuid() DECLARE INTEGER CoCreateGuid IN ole32 STRING @pguid DECLARE INTEGER CLSIDFromString IN ole32; STRING lpsz, STRING @pclsid DECLARE INTEGER StringFromGUID2 IN ole32; STRING rguid, STRING @lpsz, INTEGER cchMax LOCAL cGUID, cGUIDString, cGUID1 cGUID = REPLICATE(CHR(0), 16) 128 bits IF CoCreateGuid(@cGUID) = 0 cGUIDString = StringFromGUID(cGUID) *? cGUID *? cGUIDString * converting from String back to GUID cGUID1 = REPLICATE(CHR(0), 16) = CLSIDFromString(STRCONV(cGUIDString,5), @cGUID1) *? cGuid1 endif return Substr(cGuidString, 2, Len(cGuidString)-2) * endfunc * * End Code ** Dave ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/B3CBDFE5EBC94B2EB971D94EB59AA77F@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
A lot of information. Thanks. For now, the max+1 will work as long as I use an unfiltered list so that updates through view update only their key record but logging in users I use a username and encrypted password. They never see their actual member number id. So is it okay to stay simple with user-id if the security is really at the gate with user-name password to get in? Gary Jeurink -Original Message- From: Alan Bourke [mailto:alanpbou...@fastmail.fm] Sent: Friday, August 24, 2012 9:31 AM To: profox@leafe.com Subject: Re: auto increment On Fri, Aug 24, 2012, at 02:44 PM, Lew Schwartz wrote: I never understood why these routines would be any better than max()+1. Because they are GUIDs - globally unique. That fact is very important in some applications, but overkill for a lot of others. -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/32B978E8ED5445E09D6DA58D1797EB75@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On Aug 24, 2012, at 9:31 AM, Alan Bourke wrote: Because they are GUIDs - globally unique. That fact is very important in some applications, but overkill for a lot of others. When OpenStack was first created, it used auto-incremented keys to identify resources such as servers, volumes, networks, etc. Later it was redesigned to be able to scale horizontally; iow, scaling by creating separate independent deployments that could communicate and act as a single system. Of course, all the code to create PKs had to be changed to use UUIDs, and all the relational code had to be updated to reference the UUID keys instead of the integer keys. The update was a huge pain, and to this day there are both integer and UUID keys in most tables as a result. The worst part is that several of us argued for UUIDs from the start, but lost that discussion to those who favored the simplicity of letting the database handle key generation. -- Ed Leafe ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/d88e3e2d-3e3f-4b43-8121-a953a8f8e...@leafe.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/24/12 2:18 AM, Paul Hill wrote: Guids are nice but can add a significant size overhead to your database. Yes, that is the one disadvantage I've identified, but storage is cheap. It isn't as if query performance necessarily goes down as the database grows in size. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/5037e2f0.7000...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/24/12 10:17 AM, Ed Leafe wrote: The worst part is that several of us argued for UUIDs from the start, but lost that discussion to those who favored the simplicity of letting the database handle key generation. In my mind, keeping it simple means UUID. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/5037e3b8.2080...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On Aug 24, 2012, at 3:24 PM, Paul McNett wrote: Guids are nice but can add a significant size overhead to your database. Yes, that is the one disadvantage I've identified, but storage is cheap. It isn't as if query performance necessarily goes down as the database grows in size. Actually, query performance can typically improve. Keys that sort sequentially don't form well-balanced trees, and many indexing algorithms rely upon a tree distribution. -- Ed Leafe ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/5eb7fc81-5267-4eeb-b22e-fee937f3a...@leafe.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/24/12 1:29 PM, Ed Leafe wrote: On Aug 24, 2012, at 3:24 PM, Paul McNett wrote: Guids are nice but can add a significant size overhead to your database. Yes, that is the one disadvantage I've identified, but storage is cheap. It isn't as if query performance necessarily goes down as the database grows in size. Actually, query performance can typically improve. Keys that sort sequentially don't form well-balanced trees, and many indexing algorithms rely upon a tree distribution. I guess space considerations do come into play with databases that are limited in how large they are allowed to grow. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/5037e54b.8080...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Over the years of my experience with Fox and VFP, I used all kinds of ways to generate primary keys, and in retrospect the best ever was concatenating the machine name with the output of sys(2015). Wasn't it true at one time that SYS(2015) didn't guarantee uniqueness? More like a really really really good try sort of thing? That's why I concatenate with the machine name. I guess if you are really paranoid that a second instance of VFP could be running sys(2015) for the same table in the exact same timeslice as the first process you could also concatenate the process id. Machine names are not guaranteed to be unique indefinitely; they are only guaranteed to be unique on a single domain at one point in time. On my network I name machines for a department name plus a number. These names may be recycled among different machines and users as a department expands and contracts and machines are removed and replaced. As I understand it, no GUID generator can truly guarantee unique IDs; there is only a very, very, very high probability that no two GUIDs generated will be the same. It's kind of like the very high but not absolute probability that existing tests can accurately distinguish DNA samples from different sources. Very high probability that something won't happen impossible. Nor does probability == predictability. Something that is very improbable not only can happen, but it can happen several times in row. Someday somebody's going to get bitten by these GUID generators. Unless they concatenate something guaranteed to be unique to the setting in which they are generated. A MAC number might be a better choice. Ken Dibble www.stic-cil.org ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/5.2.1.1.1.20120824201135.01fa2...@pop-server.stny.rr.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/24/12 5:21 PM, Ken Dibble wrote: That's why I concatenate with the machine name. I guess if you are really paranoid that a second instance of VFP could be running sys(2015) for the same table in the exact same timeslice as the first process you could also concatenate the process id. Machine names are not guaranteed to be unique indefinitely; they are only guaranteed to be unique on a single domain at one point in time. There are no guarantees in life. But the danger of sys(2015) lies in it's generation based on the timestamp. What are the chances of collision on this even not concatenating the machine name? Now, what are the chances of someone changing a machine name *and* going back in time to the right timestamp to collide with a prior record created by a machine that formerly had that name? Practicality beats purity. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/50381c97.7020...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/24/12 5:21 PM, Ken Dibble wrote: That's why I concatenate with the machine name. I guess if you are really paranoid that a second instance of VFP could be running sys(2015) for the same table in the exact same timeslice as the first process you could also concatenate the process id. Machine names are not guaranteed to be unique indefinitely; they are only guaranteed to be unique on a single domain at one point in time. There are no guarantees in life. But the danger of sys(2015) lies in it's generation based on the timestamp. What are the chances of collision on this even not concatenating the machine name? Somewhat high, I believe. System clock ticks processor cycles. As I understand it, in VFP RAND() uses the system clock, and RAND() will generate the same number over and over and over in a tight loop on a fast machine, until the system clock ticks over. So in a hypothetical situation where creation of a parent record automatically generates a fairly large number of child records, the timestamp on many or all of those child records will be exactly the same. Conceivably, this increases the odds that GUID keys generated for those child records will be the same. And what was less likely to happen on your old slow 2.6 GHz machine last year will be much more likely to happen on your latest and greatest 3.4 GHz machine this year. But yes, practicality beats purity--until it fails and somebody's expensive database gets corrupted. Nobody will ever fill up a 100 gigabyte hard drive. Nobody will ever break 40-bit encryption. Nobody will still be using our software that relies on 2-digit-year dates in 2000. There is no reason anyone would want a computer in their home. -- Ken Olson, CEO, Digital Equipment Corp., 1977 On the other hand, as best as I can tell given a reasonably robust locking strategy and a next-key table, User ID integers can be centrally generated for any database system that are absolutely, positively, no exceptions no matter what happens on this fair planet, guaranteed to be unique until a number is reached that is 2/3 of the population of the earth using unsigned 4-byte integers. Use an 8-byte integer and you get 18 quintillion unique numbers. Why not require users to login with a unique user name that is associated with a centrally-generated user ID integer (as, indeed, everybody already does in multi-user systems), and then, to use a VFP example, prepend (TRANSFORM(UserID)) + |) to your GUID? Not difficult, and the value of the guarantee of uniqueness would be much higher than that of any GUID, or GUID + anything else I can think of, alone. It's all fun and games until somebody gets hurt, as my mom used to say. :) Why do people in the software business constantly go for good enough for today when simple solutions that are good enough for 100 years are staring them in the face? I keep asking myself, would it really have killed programmers in 1970 to allocate 2 bytes for a year? Don't even get me started on 32-bit vs 64-bit operating systems. Windows 98 could address every spot on a 128 GB hard drive, so why can't Windows XP address more than 4 GB of RAM? And even if there is some technical reason for this (as opposed to deliberate planned obsolescence): Since everybody right now knows that someday people are going to want to use 128 bits, why haven't they built in the ability to do that, or even 256, or 512 bits, into today's operating systems? I can't imagine it's anything more than extending the bounds of an existing algorythm. Think about it: compatibility for the next 50 years instead of the next 5! But oh man... somebody's not going to be able to squeeze as much money out of that strategy Ken Dibble www.stic-cil.org ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/5.2.1.1.1.20120824213758.01fa2...@pop-server.stny.rr.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Create a little test database with a couple of test views and experiment! -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1345707352.7962.140661118464229.0c3cf...@webmail.messagingengine.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/22/12 12:07 PM, Gary Jeurink wrote: Now that I've upped from fp-6 to fp-9, I want to change a few tables that use an integer idno as a primary key to an auto increment type. The only reason these tables need a primary key is so I can update their values through a view. I learned that the hard way in a football data base when I would mark 1-game as a conference game and then find that many games were also checked also. Will it require me to go through all the tables, views, and related forms then reset the data type? I'm not good at generating the next higher idno from a list that is filtered. In this case I keep an admin table that contains the next game idno to use (occasionally fails). Over the years of my experience with Fox and VFP, I used all kinds of ways to generate primary keys, and in retrospect the best ever was concatenating the machine name with the output of sys(2015). In my opinion, auto-incrementing integer keys are to be avoided. I use 40-char GUID's in my Python work, but to get those in VFP you'd have to rely on an external library which could slow you down compared to VFP's sys(2015). Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/50366ba0.3080...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On Thu, Aug 23, 2012 at 12:35 PM, MB Software Solutions General Account mbsoftwaresoluti...@mbsoftwaresolutions.com wrote: On Wed, August 22, 2012 6:14 pm, Tracy Pearson wrote: I believe changing a column to auto-increment is not a breaking change with the views. I may be wrong, perhaps others have an idea. You can't have the AutoIncrement key field set as updatable, unless I'm mistaken? -- Why would you ever update a PKey? It is just a pointer. -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAJidMY+10XdTUS97Lke-Pc-G=+wpK=aixjj2pwgaexvqrty...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Stephen Russell wrote on 2012-08-23: On Thu, Aug 23, 2012 at 12:35 PM, MB Software Solutions General Account mbsoftwaresoluti...@mbsoftwaresolutions.com wrote: On Wed, August 22, 2012 6:14 pm, Tracy Pearson wrote: I believe changing a column to auto-increment is not a breaking change with the views. I may be wrong, perhaps others have an idea. You can't have the AutoIncrement key field set as updatable, unless I'm mistaken? -- Why would you ever update a PKey? It is just a pointer. Stephen, Converting an existing field to be an AutoIncrement and that table is already connected to a view. The view would probably have the field set as Updateable. So, this is one of the problems that the OP was asking for. Tracy Pearson PowerChurch Software ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/000d01cd8157$fcd41df0$f67c59d0$@powerchurch.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/23/12 10:47 AM, M Jarvis wrote: On Thu, Aug 23, 2012 at 10:42 AM, Paul McNett p...@ulmcnett.com wrote: Over the years of my experience with Fox and VFP, I used all kinds of ways to generate primary keys, and in retrospect the best ever was concatenating the machine name with the output of sys(2015). Wasn't it true at one time that SYS(2015) didn't guarantee uniqueness? More like a really really really good try sort of thing? That's why I concatenate with the machine name. I guess if you are really paranoid that a second instance of VFP could be running sys(2015) for the same table in the exact same timeslice as the first process you could also concatenate the process id. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/503670fa.7030...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On Thu, August 23, 2012 1:42 pm, Paul McNett wrote: Over the years of my experience with Fox and VFP, I used all kinds of ways to generate primary keys, and in retrospect the best ever was concatenating the machine name with the output of sys(2015). In my opinion, auto-incrementing integer keys are to be avoided. I use 40-char GUID's in my Python work, but to get those in VFP you'd have to rely on an external library which could slow you down compared to VFP's sys(2015). Neat tip. So how wide is your PK field using this approach? ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/c9466572e2d641b4c3f9cb15d0bddf64.squir...@webmail.dssco.net ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/23/12 11:48 AM, MB Software Solutions General Account wrote: I use 40-char GUID's in my Python work, but to get those in VFP you'd have to rely on an external library which could slow you down compared to VFP's sys(2015). Neat tip. So how wide is your PK field using this approach? 40. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/503688f6.9000...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On Aug 23, 2012, at 3:09 PM, Lew Schwartz wrote: Unless speed is of the utmost importance, I've used select max(idno)+1 as idno from target into cursor nextkey to get the next value. Are all your systems limited to a single user? -- Ed Leafe ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/b31c6236-af9c-4203-aa29-0c79ce508...@leafe.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Hmmm. Hadn't thought of that. I guess it'd still work with the right locks in place. On Thu, Aug 23, 2012 at 4:11 PM, Ed Leafe e...@leafe.com wrote: On Aug 23, 2012, at 3:09 PM, Lew Schwartz wrote: Unless speed is of the utmost importance, I've used select max(idno)+1 as idno from target into cursor nextkey to get the next value. Are all your systems limited to a single user? -- Ed Leafe [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cafuu78ednmsbm-mvd4zlchyc+vm6ahy65kynr2nx0xc1w8l...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
On 8/23/12 1:09 PM, Lew Schwartz wrote: Unless speed is of the utmost importance, I've used select max(idno)+1 as idno from target into cursor nextkey to get the next value. Sorry, but I disagree. You'd have to do this inside a locking mechanism, unless you are only talking about a single user. How is using an integer simpler than using a random, virtually guaranteed to be unique string? The problem with autoincrement is that can be very time consuming to fix after a system crash. The problem with autoincrement is that you are relying on an opaque algorithm. Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/503691cb.8080...@ulmcnett.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
I like random, unique strings too. Incrementing integers give a chronological footprint which is sometimes interesting or useful. -Lew Schwartz On Aug 23, 2012 4:25 PM, Paul McNett p...@ulmcnett.com wrote: On 8/23/12 1:09 PM, Lew Schwartz wrote: Unless speed is of the utmost importance, I've used select max(idno)+1 as idno from target into cursor nextkey to get the next value. Sorry, but I disagree. You'd have to do this inside a locking mechanism, unless you are only talking about a single user. How is using an integer simpler than using a random, virtually guaranteed to be unique string? The problem with autoincrement is that can be very time consuming to fix after a system crash. The problem with autoincrement is that you are relying on an opaque algorithm. Paul [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAFuU78f1W7NCWAQzdELDj0aG0BLj=xcee-enth3ckfg3prf...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
We use a next id free table which stores a next id value for a range of table names. New records in these tables trigger a stored procedure which retrieves the next id value. This works pretty flawlessly on hundreds of sites, some with 40 or 50 concurrent users. -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1345757197.6596.140661118771161.02888...@webmail.messagingengine.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
As I figured out the hard way, when I mix tables in a view then I click the update criteria and mark the key and the related updateable fields. I did it once with nothing in criteria and changes are not kept. I did it once with the update fields marked but no key big oops, it changed every record in the table. I'm just wanting to get a new key without doing the work per new record. I think it just has to be an integer (big guess). Gary Jeurink -Original Message- From: MB Software Solutions General Account [mailto:mbsoftwaresoluti...@mbsoftwaresolutions.com] Sent: Thursday, August 23, 2012 12:36 PM To: ProFox Email List Subject: RE: auto increment On Wed, August 22, 2012 6:14 pm, Tracy Pearson wrote: I believe changing a column to auto-increment is not a breaking change with the views. I may be wrong, perhaps others have an idea. You can't have the AutoIncrement key field set as updatable, unless I'm mistaken? ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/A3F5C313878E4F57A7FAD75D4B7894F4@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Same here. And I use with Views, my Views are not stored in the DB. E. From: Alan Bourke alanpbou...@fastmail.fm To: profox@leafe.com Sent: Thursday, August 23, 2012 6:26 PM Subject: Re: auto increment We use a next id free table which stores a next id value for a range of table names. New records in these tables trigger a stored procedure which retrieves the next id value. This works pretty flawlessly on hundreds of sites, some with 40 or 50 concurrent users. -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1345757197.6596.140661118771161.02888...@webmail.messagingengine.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1345770877.36169.yahoomail...@web140604.mail.bf1.yahoo.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Security is not an issue. Just need a unique number and I figured auto increment would save writing a whole procedure. Gary Jeurink -Original Message- From: Paul McNett [mailto:p...@ulmcnett.com] Sent: Thursday, August 23, 2012 12:43 PM To: profox@leafe.com Subject: Re: auto increment On 8/22/12 12:07 PM, Gary Jeurink wrote: Now that I've upped from fp-6 to fp-9, I want to change a few tables that use an integer idno as a primary key to an auto increment type. The only reason these tables need a primary key is so I can update their values through a view. I learned that the hard way in a football data base when I would mark 1-game as a conference game and then find that many games were also checked also. Will it require me to go through all the tables, views, and related forms then reset the data type? I'm not good at generating the next higher idno from a list that is filtered. In this case I keep an admin table that contains the next game idno to use (occasionally fails). Over the years of my experience with Fox and VFP, I used all kinds of ways to generate primary keys, and in retrospect the best ever was concatenating the machine name with the output of sys(2015). In my opinion, auto-incrementing integer keys are to be avoided. I use 40-char GUID's in my Python work, but to get those in VFP you'd have to rely on an external library which could slow you down compared to VFP's sys(2015). Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/2CBB83FF7C9A4B23A07909938B555FA2@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
I'm a visual user and the view wizard has an update tab and you mark [1] the key, [2] the fields to update. Look at my other reply, it tells what happens when I do it wrong my bad Gary -Original Message- From: Stephen Russell [mailto:srussell...@gmail.com] Sent: Thursday, August 23, 2012 12:49 PM To: ProFox Email List Subject: Re: auto increment On Thu, Aug 23, 2012 at 12:35 PM, MB Software Solutions General Account mbsoftwaresoluti...@mbsoftwaresolutions.com wrote: On Wed, August 22, 2012 6:14 pm, Tracy Pearson wrote: I believe changing a column to auto-increment is not a breaking change with the views. I may be wrong, perhaps others have an idea. You can't have the AutoIncrement key field set as updatable, unless I'm mistaken? -- Why would you ever update a PKey? It is just a pointer. -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/A3FF603BA24647CBB2887D881FB065E8@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Does max(idno)+1 work if my view is a filter list? Gary -Original Message- From: Lew Schwartz [mailto:lew1...@gmail.com] Sent: Thursday, August 23, 2012 3:09 PM To: ProFox Email List Subject: Re: auto increment Unless speed is of the utmost importance, I've used select max(idno)+1 as idno from target into cursor nextkey to get the next value. The problem with autoincrement is that can be very time consuming to fix after a system crash. On Thu, Aug 23, 2012 at 1:42 PM, Paul McNett p...@ulmcnett.com wrote: On 8/22/12 12:07 PM, Gary Jeurink wrote: Now that I've upped from fp-6 to fp-9, I want to change a few tables that use an integer idno as a primary key to an auto increment type. The only reason these tables need a primary key is so I can update their values through a view. I learned that the hard way in a football data base when I would mark 1-game as a conference game and then find that many games were also checked also. Will it require me to go through all the tables, views, and related forms then reset the data type? I'm not good at generating the next higher idno from a list that is filtered. In this case I keep an admin table that contains the next game idno to use (occasionally fails). Over the years of my experience with Fox and VFP, I used all kinds of ways to generate primary keys, and in retrospect the best ever was concatenating the machine name with the output of sys(2015). In my opinion, auto-incrementing integer keys are to be avoided. I use 40-char GUID's in my Python work, but to get those in VFP you'd have to rely on an external library which could slow you down compared to VFP's sys(2015). Paul [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DA38E0231E9547969894E87903F492D7@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
A new record would be by an administrator but the view is to update scores after a football game [shared] by people looking for final scores. In my view we find colGame rec where hmtm=[NEBR] and vstm=[miss st] and season=[2012]... THEN UPDATE hmScore=62, vsScore[7] (go huskers). As long as I check the right key and the related records to update in the view wizard, it works. I can here you pro's growl .. learn to write an SQL statement Does max[id]+1 work on a filtered list or does it just look at that list? Gary -Original Message- From: Ed Leafe [mailto:e...@leafe.com] Sent: Thursday, August 23, 2012 3:12 PM To: ProFox Email List Subject: Re: auto increment On Aug 23, 2012, at 3:09 PM, Lew Schwartz wrote: Unless speed is of the utmost importance, I've used select max(idno)+1 as idno from target into cursor nextkey to get the next value. Are all your systems limited to a single user? -- Ed Leafe ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1F9764A4E4484E989722D8D42B872DF5@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: auto increment
Dunno. Safest thing is to specify the unfiltered master table in the from clause. On Thu, Aug 23, 2012 at 9:37 PM, Gary Jeurink g.jeur...@charter.net wrote: A new record would be by an administrator but the view is to update scores after a football game [shared] by people looking for final scores. In my view we find colGame rec where hmtm=[NEBR] and vstm=[miss st] and season=[2012]... THEN UPDATE hmScore=62, vsScore[7] (go huskers). As long as I check the right key and the related records to update in the view wizard, it works. I can here you pro's growl .. learn to write an SQL statement Does max[id]+1 work on a filtered list or does it just look at that list? Gary -Original Message- From: Ed Leafe [mailto:e...@leafe.com] Sent: Thursday, August 23, 2012 3:12 PM To: ProFox Email List Subject: Re: auto increment On Aug 23, 2012, at 3:09 PM, Lew Schwartz wrote: Unless speed is of the utmost importance, I've used select max(idno)+1 as idno from target into cursor nextkey to get the next value. Are all your systems limited to a single user? -- Ed Leafe [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAFuU78fKU=9C8sTJ=4gfe2omoy21p87_jndvn+fcuglbmb9...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
auto increment
Now that I've upped from fp-6 to fp-9, I want to change a few tables that use an integer idno as a primary key to an auto increment type. The only reason these tables need a primary key is so I can update their values through a view. I learned that the hard way in a football data base when I would mark 1-game as a conference game and then find that many games were also checked also. Will it require me to go through all the tables, views, and related forms then reset the data type? I'm not good at generating the next higher idno from a list that is filtered. In this case I keep an admin table that contains the next game idno to use (occasionally fails). Gary Jeurink --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/A9C7A1A6137340679FE5C9FD15E3BEAE@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: auto increment
Gary Jeurink wrote on 2012-08-22: Now that I've upped from fp-6 to fp-9, I want to change a few tables that use an integer idno as a primary key to an auto increment type. The only reason these tables need a primary key is so I can update their values through a view. I learned that the hard way in a football data base when I would mark 1-game as a conference game and then find that many games were also checked also. Will it require me to go through all the tables, views, and related forms then reset the data type? I'm not good at generating the next higher idno from a list that is filtered. In this case I keep an admin table that contains the next game idno to use (occasionally fails). Gary Jeurink Gary, I don't use views. I believe changing a column to auto-increment is not a breaking change with the views. I may be wrong, perhaps others have an idea. Tracy Pearson PowerChurch Software ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/001901cd80b3$76e43120$64ac9360$@powerchurch.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
