Auto Increment Field 
Author Message
 Auto Increment Field

Hello,

I am a student in Athens University at the department of
informatics. I have to prepare a series of projects using
different RDBMS. Comparing MS Access XP against MS FoxPro
7 my results are that MS FoxPro 7 is stronger than MS
Access XP but I think MS Access has a strong point which
is missing from MS FoxPro. MS Access has field data type
Autonumber which is value gets increased by one each time
a new recorded is added to the table. In MS FoxPro 7 there
is no any equivalent data type or any function to use its
returning value as the default value for the field.

I will be very grateful if you send me your suggestions
about the solution of this problem. It will be very
helpful to me if you also reply directly to my e-mail.

Thank you,

Christos Stavinou




Sun, 27 Mar 2005 16:24:33 GMT  
 Auto Increment Field
Hi Christos,

The feature you are talking about (auto-increment) will be in vfp8, which
will be release early next year, I think.

Other than that, I believe it is quite common that developers write their
own functionality for this. One solution is to keep a table that includes
one record for each other table, and this record contains the next key for
that table. One could then write a function that performs a lookup to this
table, increments the key-value in the appropriate record, and returns it.

Also, some developers (including me) prefer to use a so-called GUID for keys
instead of integers. Every GUID is guaranteed to be globally unique, so all
you need is a function, say GenGUID(), that returns a valid GUID for your
key-field. If you search google or a similar search engine on the net, you
will find many implementations of an algorithm for generating guid's.

HTH,

Eyvind.



Quote:
> Hello,

> I am a student in Athens University at the department of
> informatics. I have to prepare a series of projects using
> different RDBMS. Comparing MS Access XP against MS FoxPro
> 7 my results are that MS FoxPro 7 is stronger than MS
> Access XP but I think MS Access has a strong point which
> is missing from MS FoxPro. MS Access has field data type
> Autonumber which is value gets increased by one each time
> a new recorded is added to the table. In MS FoxPro 7 there
> is no any equivalent data type or any function to use its
> returning value as the default value for the field.

> I will be very grateful if you send me your suggestions
> about the solution of this problem. It will be very
> helpful to me if you also reply directly to my e-mail.

> Thank you,

> Christos Stavinou





Sun, 27 Mar 2005 16:42:16 GMT  
 Auto Increment Field
Christo,
In general the idea is:

Set Deleted Off
Select max(ID) as MaxID from TableName into Array aMax
m.NewID=iif(_Tally>0, aMax[1]+1, 1)
Set Deleted On
Return m.NewID

Demetrios, Greece


Quote:
> Hello,

> I am a student in Athens University at the department of
> informatics. I have to prepare a series of projects using
> different RDBMS. Comparing MS Access XP against MS FoxPro
> 7 my results are that MS FoxPro 7 is stronger than MS
> Access XP but I think MS Access has a strong point which
> is missing from MS FoxPro. MS Access has field data type
> Autonumber which is value gets increased by one each time
> a new recorded is added to the table. In MS FoxPro 7 there
> is no any equivalent data type or any function to use its
> returning value as the default value for the field.

> I will be very grateful if you send me your suggestions
> about the solution of this problem. It will be very
> helpful to me if you also reply directly to my e-mail.

> Thank you,

> Christos Stavinou





Sun, 27 Mar 2005 17:17:55 GMT  
 Auto Increment Field
Hello,

this solution should work well in a single user environment, but in
multiuser app this approach
isn't reliable to ensure unique id's

Regards

Christian



Quote:
> Christo,
> In general the idea is:

> Set Deleted Off
> Select max(ID) as MaxID from TableName into Array aMax
> m.NewID=iif(_Tally>0, aMax[1]+1, 1)
> Set Deleted On
> Return m.NewID

> Demetrios, Greece



> > Hello,

> > I am a student in Athens University at the department of
> > informatics. I have to prepare a series of projects using
> > different RDBMS. Comparing MS Access XP against MS FoxPro
> > 7 my results are that MS FoxPro 7 is stronger than MS
> > Access XP but I think MS Access has a strong point which
> > is missing from MS FoxPro. MS Access has field data type
> > Autonumber which is value gets increased by one each time
> > a new recorded is added to the table. In MS FoxPro 7 there
> > is no any equivalent data type or any function to use its
> > returning value as the default value for the field.

> > I will be very grateful if you send me your suggestions
> > about the solution of this problem. It will be very
> > helpful to me if you also reply directly to my e-mail.

> > Thank you,

> > Christos Stavinou





Sun, 27 Mar 2005 17:37:15 GMT  
 Auto Increment Field
Hello Christian,

I think that it works, as far as, it is used as a "Default Value"
in the field which you want to AutoNumber.
In that case you don't issue the ID when you insert a record
but instead you let the database to fill it. (I hope ).

Demetrios, Greece

Quote:
> Hello,

> this solution should work well in a single user environment, but in
> multiuser app this approach
> isn't reliable to ensure unique id's

> Regards

> Christian



> > Christo,
> > In general the idea is:

> > Set Deleted Off
> > Select max(ID) as MaxID from TableName into Array aMax
> > m.NewID=iif(_Tally>0, aMax[1]+1, 1)
> > Set Deleted On
> > Return m.NewID

> > Demetrios, Greece



> > > Hello,

> > > I am a student in Athens University at the department of
> > > informatics. I have to prepare a series of projects using
> > > different RDBMS. Comparing MS Access XP against MS FoxPro
> > > 7 my results are that MS FoxPro 7 is stronger than MS
> > > Access XP but I think MS Access has a strong point which
> > > is missing from MS FoxPro. MS Access has field data type
> > > Autonumber which is value gets increased by one each time
> > > a new recorded is added to the table. In MS FoxPro 7 there
> > > is no any equivalent data type or any function to use its
> > > returning value as the default value for the field.

> > > I will be very grateful if you send me your suggestions
> > > about the solution of this problem. It will be very
> > > helpful to me if you also reply directly to my e-mail.

> > > Thank you,

> > > Christos Stavinou





Sun, 27 Mar 2005 18:03:05 GMT  
 Auto Increment Field
Hello,

imagine following situation:

Tables are buffered (Buffering = 5)

User 1 add's new record
User 2 add's new record before User 1 hit's the save button ...
BOOM ..
two identical id's and User 2 will get an error when trying to save the
record

without any buffering it may work

Regards

Christian



Quote:
> Hello Christian,

> I think that it works, as far as, it is used as a "Default Value"
> in the field which you want to AutoNumber.
> In that case you don't issue the ID when you insert a record
> but instead you let the database to fill it. (I hope ).

> Demetrios, Greece

> > Hello,

> > this solution should work well in a single user environment, but in
> > multiuser app this approach
> > isn't reliable to ensure unique id's

> > Regards

> > Christian



> > > Christo,
> > > In general the idea is:

> > > Set Deleted Off
> > > Select max(ID) as MaxID from TableName into Array aMax
> > > m.NewID=iif(_Tally>0, aMax[1]+1, 1)
> > > Set Deleted On
> > > Return m.NewID

> > > Demetrios, Greece



> > > > Hello,

> > > > I am a student in Athens University at the department of
> > > > informatics. I have to prepare a series of projects using
> > > > different RDBMS. Comparing MS Access XP against MS FoxPro
> > > > 7 my results are that MS FoxPro 7 is stronger than MS
> > > > Access XP but I think MS Access has a strong point which
> > > > is missing from MS FoxPro. MS Access has field data type
> > > > Autonumber which is value gets increased by one each time
> > > > a new recorded is added to the table. In MS FoxPro 7 there
> > > > is no any equivalent data type or any function to use its
> > > > returning value as the default value for the field.

> > > > I will be very grateful if you send me your suggestions
> > > > about the solution of this problem. It will be very
> > > > helpful to me if you also reply directly to my e-mail.

> > > > Thank you,

> > > > Christos Stavinou





Sun, 27 Mar 2005 18:59:13 GMT  
 Auto Increment Field


Quote:
> I think that it works, as far as, it is used as a "Default Value"
> in the field which you want to AutoNumber.
> In that case you don't issue the ID when you insert a record
> but instead you let the database to fill it. (I hope ).

Demetrios, use something like the NewID() stored procedure in TasTrade as
the default value for the primary key field. Its just as easy as your idea
and will always be correct.  You are correct that you supply all the values
except for the PK when you insert a record and the database will fill it
with the default value.

--

Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Sun, 27 Mar 2005 20:31:16 GMT  
 Auto Increment Field

Quote:
> I am a student in Athens University at the department of
> informatics. I have to prepare a series of projects using
> different RDBMS.

Christos, since you'll most likely be back with more questions, let me
suggest that you "cross post" (posting once with copies to more than one
newsgroup) rather than "multi post" (posting separately to more than one
newsgroup). That way all of the replies show up in each newsgroup.

To do this using Outlook Express, press the button in the upper left under
the "Newsgroups:" word and choose one or two groups that are relevant to
your question.

--

Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Sun, 27 Mar 2005 20:52:34 GMT  
 Auto Increment Field
Hi Christos,
  As someone else pointed out, auto-increment is included in VFP8.
  I have written a standard routine that gets the next available key value
from a table that holds the current high value for each table.  This has
always worked perfectly.
  I will not use auto-incrementing when it is available because of a problem
with auto-incrementing that manifests when you try to create a parent and
child record within the same transaction.  The scenario is as follows:
  1. create a new parent record
  2. create a child record.  The child record must contain the value of the
parent's key.  However, the value of the parent's key is not known until
after the transaction.  If you try to solve this problem by creating the
parent record in one transaction and the child record in another
transaction, you now have the problem that if the child record creation
fails, you cannot roll back the parent record creation.  As far as I know,
there is no elegant solution to this problem (there are awkward solutions
that may introduce problems of their own).  So, ultimately, auto
incrementing can be a very bad idea.
  So I suggest that you do not use the availability of this feature as a
comparison in your study.  Other aspects of functionality are much more
important, such as:
  .ability to handle large tables
  .ability to handle large number of simultaneous users
  .speed of execution of queries and updates
  .robustness

  In general, Access is an end user tool whereas Visual FoxPro is a
professional development tool.  For beginners, it is easier to get started
in Access, but if you want to build a professional (ie robust, reliable,
scalable) application, Visual FoxPro is the choice.  Or, in other words, it
is easier to build a simple application in Access, but it is much easier to
build a professional robust application in VFP.

  Regards, Chaim Caron (New York City)



Sun, 27 Mar 2005 21:51:02 GMT  
 Auto Increment Field
Hello Christos,
I follow a deferent approach to this. I use the SYS(2015) function. The name
that SYS(2015) returns is created from the system date and system time.
Calling SYS(2015) more than once during the same millisecond interval will
return a unique character string. And to be even more secure for a
multi-user environment with a large number of users (access to the same
microsecond or nanosecond) you can add a tinny (against a user interaction)
delay of  .5msec with the INKEY(.0005) function, before the SYS(2015).

???? ??? ????? ??? ???? ???????? ???? ??????? ???! (This is GREEK, for all
the others!)

Have a nice day (or night),
Nick Pofiris,
ErmisPliroforiki
Athens-Greece
www.ermis-pliroforiki.com.gr



Quote:
> Hello,

> I am a student in Athens University at the department of
> informatics. I have to prepare a series of projects using
> different RDBMS. Comparing MS Access XP against MS FoxPro
> 7 my results are that MS FoxPro 7 is stronger than MS
> Access XP but I think MS Access has a strong point which
> is missing from MS FoxPro. MS Access has field data type
> Autonumber which is value gets increased by one each time
> a new recorded is added to the table. In MS FoxPro 7 there
> is no any equivalent data type or any function to use its
> returning value as the default value for the field.

> I will be very grateful if you send me your suggestions
> about the solution of this problem. It will be very
> helpful to me if you also reply directly to my e-mail.

> Thank you,

> Christos Stavinou





Mon, 28 Mar 2005 00:48:25 GMT  
 Auto Increment Field
Cindy,

Thanks for your suggestion.
Although I didn't have problems yet with my approach I will reconsider it.
In case that I would like to modify my Stored procedure NewID is there a way
to be done by code?

Demetrios, Greece


Quote:


> > I think that it works, as far as, it is used as a "Default Value"
> > in the field which you want to AutoNumber.
> > In that case you don't issue the ID when you insert a record
> > but instead you let the database to fill it. (I hope ).

> Demetrios, use something like the NewID() stored procedure in TasTrade as
> the default value for the primary key field. Its just as easy as your idea
> and will always be correct.  You are correct that you supply all the
values
> except for the PK when you insert a record and the database will fill it
> with the default value.

> --

> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Mon, 28 Mar 2005 10:50:45 GMT  
 Auto Increment Field
Hello Eyvind,

What is the type of GUID (an integer, long integer ?).
I mean what type of filed do you need to store this?
The way of having a PK number before you insert the record is a good idea in
the case of parent and child tables as Chaim Caron remarks in his post.
Otherwise the solution is more complex.

Demetrios, Greece


Quote:
> Hi Christos,

> The feature you are talking about (auto-increment) will be in vfp8, which
> will be release early next year, I think.

> Other than that, I believe it is quite common that developers write their
> own functionality for this. One solution is to keep a table that includes
> one record for each other table, and this record contains the next key for
> that table. One could then write a function that performs a lookup to this
> table, increments the key-value in the appropriate record, and returns it.

> Also, some developers (including me) prefer to use a so-called GUID for
keys
> instead of integers. Every GUID is guaranteed to be globally unique, so
all
> you need is a function, say GenGUID(), that returns a valid GUID for your
> key-field. If you search google or a similar search engine on the net, you
> will find many implementations of an algorithm for generating guid's.

> HTH,

> Eyvind.



> > Hello,

> > I am a student in Athens University at the department of
> > informatics. I have to prepare a series of projects using
> > different RDBMS. Comparing MS Access XP against MS FoxPro
> > 7 my results are that MS FoxPro 7 is stronger than MS
> > Access XP but I think MS Access has a strong point which
> > is missing from MS FoxPro. MS Access has field data type
> > Autonumber which is value gets increased by one each time
> > a new recorded is added to the table. In MS FoxPro 7 there
> > is no any equivalent data type or any function to use its
> > returning value as the default value for the field.

> > I will be very grateful if you send me your suggestions
> > about the solution of this problem. It will be very
> > helpful to me if you also reply directly to my e-mail.

> > Thank you,

> > Christos Stavinou





Mon, 28 Mar 2005 10:50:29 GMT  
 Auto Increment Field
Has someone with the VFP8 beta figured out how to get the auto-incremented pk back? If you use local views, you need the pk from
the new parent record before you can write the new child record for that parent.

*************
Demetrios, use something like the NewID() stored procedure in TasTrade as
the default value for the primary key field. Its just as easy as your idea
and will always be correct.  You are correct that you supply all the values
except for the PK when you insert a record and the database will fill it
with the default value.

--

Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Mon, 28 Mar 2005 11:14:04 GMT  
 Auto Increment Field

Quote:
> In case that I would like to modify my Stored
> procedure NewID is there a way to be done by code?

Demetrios, you can add or modify a stored procedure through the GUI
interface of the Project Manager or by right-clicking on the Database and
choosing "Edit Stored Procedures."

You can add or change a stored procedure with code like this from
TasTrade.PRG. Note that TasTrade.PRG is the result of running GenDBC.PRG on
the TasTrade database. You might read about GenDBC in the Help.

********* Procedure Re-Creation *********
IF !FILE([TASTRADE.krt])
    ? [Warning! No Procedure File Found!]
ELSE
 CLOSE DATABASE
 USE 'TASTRADE.DBC'
 g_SetSafety = SET('SAFETY')
 SET SAFETY OFF
 LOCATE FOR Objectname = 'StoredProceduresSource'
 IF FOUND()
        APPEND MEMO Code FROM [TASTRADE.krt] OVERWRITE
     REPLACE Code WITH SUBSTR(Code, 70, 76156)
 ENDIF
 LOCATE FOR Objectname = 'StoredProceduresObject'
 IF FOUND()
        APPEND MEMO Code FROM [TASTRADE.krt] OVERWRITE
        REPLACE Code WITH SUBSTR(Code, 76226)
 ENDIF
    SET SAFETY &g_SetSafety
 USE
 OPEN DATABASE [TASTRADE.DBC]
ENDIF

--

Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Mon, 28 Mar 2005 11:52:37 GMT  
 Auto Increment Field
Demetrios,

as to the guid, go to

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/com/...
_05r8.asp

It returns an 128 bit integer (16 bytes).  You can store that in a c(16)
binary field or convert it to hex and store in a c(32) field

Here's how
*-----------------------------------------------
Function Guid()


 local guid
 guid = space(16)
 do case

  return guid
 otherwise
  assert FALSE
  return Null
 endcase

endfunc
*--------------------------------------------------------
Function Guid_String()



 local guid, guidString, n, s
 guid = space(16)
 do case

  guidString = space(78)

  do case
  case n = len(guidString)/2
   s = chrtran(guidString, chr(0) + '-{}', '')
   return s
  otherwise
   assert FALSE
   return Null
  endcase

 otherwise
  assert FALSE
  return Null
 endcase

endfunc

Gregory
________________

| Hello Eyvind,
|
| What is the type of GUID (an integer, long integer ?).
| I mean what type of filed do you need to store this?
| The way of having a PK number before you insert the record is a good idea
in
| the case of parent and child tables as Chaim Caron remarks in his post.
| Otherwise the solution is more complex.
|
| Demetrios, Greece
|

| > Hi Christos,
| >
| > The feature you are talking about (auto-increment) will be in vfp8,
which
| > will be release early next year, I think.
| >
| > Other than that, I believe it is quite common that developers write
their
| > own functionality for this. One solution is to keep a table that
includes
| > one record for each other table, and this record contains the next key
for
| > that table. One could then write a function that performs a lookup to
this
| > table, increments the key-value in the appropriate record, and returns
it.
| >
| > Also, some developers (including me) prefer to use a so-called GUID for
| keys
| > instead of integers. Every GUID is guaranteed to be globally unique, so
| all
| > you need is a function, say GenGUID(), that returns a valid GUID for
your
| > key-field. If you search google or a similar search engine on the net,
you
| > will find many implementations of an algorithm for generating guid's.
| >
| > HTH,
| >
| > Eyvind.
| >
| >


| > > Hello,
| > >
| > > I am a student in Athens University at the department of
| > > informatics. I have to prepare a series of projects using
| > > different RDBMS. Comparing MS Access XP against MS FoxPro
| > > 7 my results are that MS FoxPro 7 is stronger than MS
| > > Access XP but I think MS Access has a strong point which
| > > is missing from MS FoxPro. MS Access has field data type
| > > Autonumber which is value gets increased by one each time
| > > a new recorded is added to the table. In MS FoxPro 7 there
| > > is no any equivalent data type or any function to use its
| > > returning value as the default value for the field.
| > >
| > > I will be very grateful if you send me your suggestions
| > > about the solution of this problem. It will be very
| > > helpful to me if you also reply directly to my e-mail.
| > >
| > > Thank you,
| > >
| > > Christos Stavinou
| > >

| > >
| > >
| >
| >
|
|



Mon, 28 Mar 2005 16:58:52 GMT  
 
 [ 23 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Auto Increment Field

2. Auto Increment Field

3. Auto-Incremented field

4. Auto-increment Field in Foxpro

5. auto-increment field

6. Auto-incrementing a primary key field

7. auto increment a field

8. Visual FoxPro ODBC driver fails with "Not a table" if table has auto-incrementing field

9. is there anyway to make an auto-increment field as in Access?

10. Auto Increment

11. Auto-Incrementing

12. Auto Incrementing Primary Key

 

 
Powered by phpBB® Forum Software