DBF to SQL converter program

[James Bott]

Rao,

SQL databases do not create any columns on their own.

Thanks for the clarification. Then it must be a FW ADO function that is automatically adding the ID field?

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.

James

[nageswaragunupudi]

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.

[reinaldocrespo]

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.

[nageswaragunupudi]

Mr James

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.

1. It is now possible to specify your own name for the auto-increment primary key field.
2. Yes we should not have two auto-inc fields and primarykeys
3. In case your DBF has already an autoincrement field ( type "+" ) as the 1st field by whatever name, then the functions in adofuncs.prg do NOT add the ID field. Same autoinc field in DBF is used.

So, if you have your DBFs with first field of whatever you prefer with type "+" the converted SQL table also will have the same fieldname as auto-inc primary key. Both DBF and the SQL table have the same behavior. It is very easy to have a program which runs both for DBF or SQL table.

Incidentally if the program already uses TData(base) to deal with the DBF tables there is a TRecSet class which behaves very similarly. Actually we created TRecSet for easy migration from TDatabase. This also enables same code to deal with either DBF or SQL databases.

[James Bott]

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

[James Bott]

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

[reinaldocrespo]

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)?
 

No, it is totally different than an auto-increment field. Yes, every time you update the record the rowversion field is auto-incremented. Let's say you have a sequences table (as you do) where you store sequences for each field of each table that is to be sequentially incremented. You first read the value on rowversion for that record while extracting the corresponding sequence. Now before you update the sequence field with sequence+1 you check if the rowversion is still the same. If it has changed, then someone else has updated this record. Here is pseudo code.

Code: Select all  Expand view

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 

The table data might look like this:

Code: Select all  Expand view

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.

Well, there are different GUID lengths. However, if it is to be printed or keyed, then it won't solve the problem.

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

Yet another way to follow the KISS principle. In SQL you achieve this same effect by having the whole piece of code inside a transaction; i.e., this is what I described as transactions. Still, I rather have that coupled with a unique indexes.

As for being able to have SQL and non SQL installations, this is were ADS is hard to beat. Yourcode can be ISAM or SQL or a little of both with the ADS RDD. The limitation is that the free ads (they call it local sever) allows no more than 5 users and the paid version (they call it remote server) has a cost, albeit I find it to be the least expensive -for cost- SQL in the market.

Reinaldo

[James Bott]

Reinaldo,

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

I do something similar by saving a copy of the record in memory when the record is loaded for editing. Then I can check each field against the original record before saving and determine exactly which fields were changed.

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

Except if you have 6 users you have to buy the server. And install it. And maintain it. I don't know how much effort this requires. Maybe the server is inexpensive and doesn't require a database administrator, I don't know.

If you have an app on a hundred sites, this could get really complicated.

I get that there are a lot of benefits too; speed, transactions, rollbacks, larger number of users, etc.

I haven't decided on a SQL database yet, and I will keep ADS in mind.

Regards,
James

[AHF]

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.

[bpd2000]

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

Code: Select all  Expand view

#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
 

[nageswaragunupudi]

Instead of

Code: Select all  Expand view

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

try

Code: Select all  Expand view

FW_AdoImportFromDBF( oCn, "c:\fwh64\samples\customer.dbf" )

[bpd2000]

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

[Compuin]

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

How can I use it for many Dbfs and keeping the same structure from Dbfs to SQL Tables ?

[bpd2000]

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

create image url

Code: Select all  Expand view

#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
 

Dear Rao,
Can you help me, I am not successful for importing dbf as per example

[nageswaragunupudi]

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