FiveTech Support Forums

SQL databases do not create any columns on their own.

In FWH15.08 it is possible to specify a name other than "ID" for the automatically inserted auto-increment primary key.

This is for general information.

Hello everyone;

On the subject of using auto-inc fields as Primary/Unique id fields I find this is a good case where planning pays in the long run. If your app will always remain smaller (in number of concurrent users) or you can predict that you will never use (a) replication or will never have to (2) recreate your tables or (3) will never need to merge data in any way or (4) will never restructured the table or (5) implement referential integrity rules, then perhaps auto increment fields as unique primary keys is a safe and easy solution. Keep in mind that an auto-inc field IS NOT an integer field. For some reason, I keep reading posts on other SQL forums of people reporting problems with autoinc fields being used as primary unique ids. Most times it is due to some of the 5 reasons above.

On the other hand, I like to think that my application will grow and that if given the opportunity I will be able to compete against larger scale applications. After all, that's the reason I moved to SQL abandoning DBFs. It is impossible to know what you don't know, but using auto increment fields as primary unique keys is one thing you DO know has a potential to come back and bite you.

To my friend in beautiful and sunny San Diego I will say to try any of these methods when the time comes:

(1) if the id is never going to be keyed-in by the user and you are only looking for a good referential integrity rule satisfying id, then try having a GUID field. GUIDs are implemented by most SQL engines and are auto generated when inserting a new record. A GUID is guaranteed to be GLOBALLY unique.

(2) implement transactions. Most SQL engines allow you to start-end-roll back a transaction. Records involved during a transaction remain locked from other users until the transaction is done. This will ensure no other user is updating the sequece at the same time.

(3) implement a row version field. Most SQL engines allow you to have a rowVersion field that auto-increments any time that record is written/updated. This will allow you to know if someone else has updated the record since you last read it. Keep it on a loop until you can update your counter if the rowversion field hasn't changed. I posted sample code that shows how to do this on another "related" thread on this forum. BTW- this is also a good way to know if someone else has been working on the same customer and has updated the record while you had it on the screen.

(4) keep a unique index. Simple, stupid (you know the KISS principle). If the record update/insert fails do to duplicate key, then re-increment the sequence and retry. Keep it on a loop until successful.

Best regards,

Reinaldo.

If that is the case, then we still have the same problem. So, we will have to use SQL statements to build the database (instead of the FW ADO function) to avoid getting the ID field.

It does seem redundant to be getting two auto-increment fields (ID and HBRECNO) in tables created by the ADORDD. However, the HBRECNO should start at 1 and the ID field could start at any number so maybe they both could have separate purposes.

Reinaldo, my friend in beautiful, sunny, Florida:

First let me mention that, at least right now, I have a client that wants to be able to have the app run with or without SQL. So, implementing SQL only solutions will require a lot more work since I would need both systems. This is why I am so interested in the new ADORDD. Writing a SQL app is a completely different ballgame.

1) if the id is never going to be keyed-in by the user and you are only looking for a good referential integrity rule satisfying id, then try having a GUID field. GUIDs are implemented by most SQL engines and are auto generated when inserting a new record. A GUID is guaranteed to be GLOBALLY unique.

I use these ID's for things like customer numbers, invoice numbers, etc. so they do need to be printable, readable and in sequential order. And I thought GUIDs were NOT guaranteed to be unique. As I understand it, they just have a very low probability of collision. Isn't that why you have to check for duplicates before you use them? And I don't really understand what the advantage of a GUID is over a sequential number? I'm sure there is one, I just don't know what it is.

(3) implement a row version field.

How is this different from an auto-incrementing field? Is it a date/time field? Does it have the same issues as an auto-incrementing field (the possibility of being renumbered)?

4) keep a unique index. Simple, stupid (you know the KISS principle). If the record update/insert fails do to duplicate key, then re-increment the sequence and retry. Keep it on a loop until successful.

I simply lock the sequencing DBF record for the table, read the next value, update it by 1, save it, and unlock it. I have been using this for probably more than 20 years and have never had it fail. Oh, and I don't generate a new number until a new record is saved, not when a user starts a new record. This way the user can abandon a new record without leaving a gap in the sequence. So, it seems the simple thing to do (at least for this app) is to continue using the same system even with SQL. However, with SQL I think I still need to use a DBF for the sequencing database so it can be locked. Or, will Begin Transaction/End Transaction accomplish the same thing? Even if it does, I think I can still use the old DBF method with or without SQL with the current app.

Reinaldo, I do appreciate your writings on SQL, and I am sure they will be even more helpful later when I am writing a SQL only app.

Regards,
James

Rao,

Thanks for the clarifications.

I didn't know that DBFs had auto-increment fields until a few months ago, so I have not been using them. I have my own database class that does it's own auto-incrementing using the method I mentioned in the post above.

I always put the primary key into the first field so I can use the same code in my database class and expect to find it there.

I also have a recordset class that I wrote. I am not using it in the current situation since I am trying to change the least amount of code possible. The author of the program I am converting to SQL did use my database class, but not completely, so there is a mix of both database class and database statements. This complicates things considerably.

As far as I can tell, you and I are the only die-hard database class programmers on this forum. Well, there are others using them (Tim for instance), but not subclassing them and extending them to get the maximum benefit.

Regards,
James

How is this different from an auto-incrementing field? Is it a date/time field? Does it have the same issues as an auto-incrementing field (the possibility of being renumbered)?
 

WHILE true

    SELECT nextSeq, RowVer FROM sequences s WHERE table = 'customers' 
    UPDATE sequences SET NextSeq = s.NextSeq+1 WHERE RowVer = s.RowVer 

    IF recordsUpdated > 0 THEN exitLoop 

END

Field          Sequence       RowVersion
--------------------------------------------
ClaimKey        16                177709
CustNo           31644           240408
ServTicket      5666             240409
InvNumb         50344           240555

And I thought GUIDs were NOT guaranteed to be unique. As I understand it, they just have a very low probability of collision.

I simply lock the sequencing DBF record for the table, read the next value, update it by 1, save it, and unlock it.

Now before you update the sequence field with sequence+1 you check if the rowversion is still the same.

As for being able to have SQL and non SQL installations, this is were ADS is hard to beat.

I agree ADS its the best.

Pros:
Installation very easy
Maintenance very easy
Apps conversion very few code changes. The logic doesnt change at all.
DBFs imported immediately to the dictionary or start immediately working as free tables.
Strong security
Record movement extremely fast like dbf
Filters extremely fast
WEB enable apps
Clients for every programming language. Oledb, ODBC, RDD
Servers for Windows and Linux
All features other SQLs maintaining true ISAM
And many more.....

Cons:
Price expensive you can get today many SQLs for free
Local server (5 users) if I remember well doesn't work trough RDP without additional licences
Future development under SAP - Some of my clients are very concerned with the future of ADS and some are changing because of that, although a Version 12 is announced to the end 2015.(Check ADS forums)
This was the first reason why we started with adordd development.

If you want to go to SQL without changing app code the only alternatives are ADS RDD and ADORDD although this last rdd its emulating ISAM way of working, much slower and not cross-platform because it depends of ADO framework only available for windows.

Thank you for reply
I am facing same error after changes as suggested

bpd2000 wrote:Thank you for reply
I am facing same error after changes as suggested

bpd2000 wrote:I am not able to Import dbf file, fwh database create successfully, but while Import error as per screen shot

create image url

Code (fw): Select all Collapse

#include "fivewin.ch"
#include "adodef.ch"

//----------------------------------------------------------------------------//

function Main()

   local oCn, oRs
   local SQLEXPRESS := "192.168.13.22"
   local Secret   := "password"
   local SA := "SA"
   local cinitcat := "fwh"
    
   //----------------------------------------------------------------------------//
   // Check if Database cInitCat exists on the server
   // and create if does not exist
   //----------------------------------------------------------------------------//

   ? "Connect to Server"
   oCn   := FW_OpenAdoConnection( {"MSSQL",SQLEXPRESS,,SA,Secret}, .t. )
   
   if oCn == nil
      ? "Connect Fail"
      QUIT
   endif
   ? "Check and create database " + cInitCat + " if needed"
   oRs   := oCn:OpenSchema( 1, cInitCat )
   if oRs == nil
      ? "Error"
      QUIT
   endif
   if oRs:RecordCount() > 0
      ? "DataBase " + cInitCat + "Exists"
   else
      oCn:Execute( "CREATE DATABASE " + cInitCat )
      ? "Database " + cInitCat + " Created"
   endif
   oRs:Close()
   oCn:Close()

   //----------------------------------------------------------------------------//
   // Connect to the sever / Database, Export Data and Browse exported data
   //----------------------------------------------------------------------------//

   ? "Connect to Server Database " + cInitCat
   oCn   := FW_OpenAdoConnection( {"MSSQL",SQLEXPRESS,cinitcat,SA,Secret}, .t. )
   if oCn == nil
      ? "Connect Fail"
      QUIT
   endif

   ? "Importing Customer.dbf"
   FW_AdoImportFromDBF( oCn, "c:\fwh64\samples\customer.dbf", cinitcat )

   ? "Imported. Opening CUSTOMER table on the MSSQL server"
   oRs      := FW_OpenRecordSet( oCn, "CUSTOMER" )
   XBROWSER oRs

   oRs:Close()
   oCn:Close()
   ? "Done"

return nil

May I know your FWH version?
If you have 32 bit version, you will find the customer.dbf in c:\fwh\samples folder but not c:\fwh64\samples folder
In this call
FW_AdoImportFromDBF( oCn, "c:\fwh64\samples\customer.dbf", cinitcat )
ommit the 3rd paramter