FiveTech Support Forums

Next let us pay attention to field names. It is likely that some of the field names in some of our DBFs conflict with reserved words of the SQL database. It is very difficult for us to review every field name and change only those which conflict.

An easier way is to prefix all column names with a character or some characters while exporting so that none of them conflict with any keyword.

For example, if we prefix "F" to all field names:
FW_AdoImportFromDBF( oCn, "c:\myfolder\state.dbf", "states", "F" )
Then if the field names in state.dbf are "CODE","NAME", the column names created in states table are "ID","FCODE","FNAME".

Also if we use hungarian notation, we can easily identify a field variable.
if nVar, cVar, lVar denote numeric, character and logical variables, fName indicates a field variable.

Incidentally, this also avoids conflict with "ID".

It seems that changing fieldnames is a monumental task since you also have to change all references to them in your app's source code too. Of course, this may be inevitable in some situations such as ID and reserved words.

It does point out that moving an existing app to SQL is not a trivial matter, even with a new SQL RDD (although much easier than without a RDD).

Regards,
James

It seems that changing fieldnames is a monumental task since you also have to change all references to them in your app's source code too

Better done in the beginning than regret later. ( I am not talking about the name ID. That is trivial)
Any change is a big task. And the new software should last for several years to come. So it is worthwhile spending enough time to produce a safe and bug-free software.

When table names of field names conflict with reserved words, the work-around we adopt is to escape the names. In Microsoft products enclose them in [] and for MySql `.
Out adofuncs.prg automatically helps escaping the names suitably depending on the database.
But in Oracle and PostGre there is no way. Theoritically we can escape with double quotes but that makes the names case sensitive and has many other side-effects.

Not taking care of this may create unknown bugs that are difficult to locate.

As far as the name ID is concerned, we can provide for specifying a name for the auto-incremental primary key.

Rao,

My point was that I wouldn't change all the fieldnames just so I could use Hungarian notation. I would change any that were necessary to avoid reserved word issues.

As far as auto-incremented primary-key fields go--they worry me. Since as Reinaldo pointed out, they can get renumbered under several situations which could make your entire database unusable. Right now, I am not convinced it is worth the risk. I think I will stick with my old methods of auto-incrementing for now.

James

If you do not like do not use.

While I am aware of all implications, I just would like to say a majority of programmers in the world use it and I used without any issues.

Interestingly, Oracle does not have the auto-increment fields. Instead we use Sequences. More programming work, but we push the logic inside the triggers. Our adofuncs transparently implements sequences and triggers for Oracle.

Rao,

Just so there is no misunderstanding, I always value your input.

I'm not saying I will never use auto-incrementing fields, just not right now. I have to convert a very large program (around 100,000 lines) to SQL, so I would like to change as little as possible to get it running. And I don't want to take any unnecessary risks on this either. I didn't write the code and it is very hard to read and understand so the less changes, the better--for now.

Regards,
James

James,

I'm not saying I will never use auto-incrementing fields, just not right now. I have to convert a very large program (around 100,000 lines) to SQL, so I would like to change as little as possible to get it running. And I don't want to take any unnecessary risks on this either. I didn't write the code and it is very hard to read and understand so the less changes, the better--for now.

Did you tried it with adordd ?

Rao comment on reserved keywords its very important.
We ve just got such a experience now with adordd in a new client.
Fortunately because we mainly work on tables with Portuguese language we will not have so many problems like that.
In fact we are finding a solution to inform the user /programmer of that situation.

Antonio F.

Yes, that is the app I have been working with on the ADORDD. Without your work developing that it would have been a monumental task.

In fact we are finding a solution to inform the user /programmer of that situation.

Hmm, do you mean something like comparing all the fieldnames to a list of known reserved words? That would be very useful.

Best Regards,
James

James,

Exactly.
A new SET CHECK SQL RESERVED WORDS ON / OFF.

This will be only used when porting dbfs to SQL and in the test phase not to impact on overall performance.
If it finds matches gives a warning or even throws and exception.

In this new app we are working now is easily adapted because its only in 2 table fields but with others to come might not be.

So we are checking the possibility of defining a SET DICTIONARY TO translate those fields in the dbfs tables to their corresponding in SQL tables.

It seems that would be possible and then we would keep our goal of 0 code change in the apps to be converted.

For anyone ,as we, using Crystal Reports these fields must be then remapped when opening the report.
This would be a code change that we cant avoid.

We working now in adordd to detect more efficiently new record additions made by others.

Will keep you posted.

James Bott wrote:Peiter,

The issue I am still concerned about is that when your ID field in the DBF is imported to an auto increment field in the SQL table, then it is going to be renumbered starting at 1 and not skipping any numbers up to the maximum number of records. If your DBF is numbered the same way then you are OK, but if it is not then you are going to have trouble.

The issues that Reinaldo discussed concern me and lead me to believe that auto increment fields should only be used for table "housekeeping" and not for primary keys. They seem best used for recno() type functions.

James

Hi James,

I have checked some of my dbf files with colunname "ID" in it. Sometimes the whole column is empty, and sometimes there are some letters in it. So I did not see any (unique) numbers in the dbf files ID column, so I think that would not be a problem, for now. It is just that the names ID are the same (In the dbf file and sql table), which gives an error. Maybe I will run in to some problems later (with this duplicate ID column name).

Everybody thanks:)

Pieter

Peiter,

Thanks for the report.

Maybe I will run in to some problems later (with this duplicate ID column name).

I can't help but think that this will be true. I'm surprised that the SQL database doesn't error out when you import the file.

James

pieter wrote:James, Thank you, (I can use that code from Roa probably when I want to make my code better readable)

I have still another problem:

image hosting site no sign up

When FW_AdoImportFromDBF( oCn, cFolder + "\" + dbfPath[x][1], "00" + cFileNoExt(dbfPath[x][1]) ) is used, a sql table will be made, with also a column ID autoincrement. Some of my dbf files has also an Column ID. I think because of that the above error occured.

I thought of two options:
1) change the autoincrement Column ID of the SQL Table
2) Change the Column ID of the dbf files which will be converted.

I think Option 1 is the best. My question is, is there a way to change Column ID into for example SQL_ID? (with FW_AdoImportFromDBF)

Pieter

James,

It gives indeed an error, (see image) The sql table from the dbf with "ID" Column name is never made, when I try FW_AdoImportFromDBF. However most sql tables can be made (The dbf's without "ID" Columnname). (about 200 dbf's in total, maybe only 10% have this duplicate "ID" Column in it, so only these dbf's cannot be imported). Besides, this 10% of dbf files, the DBF TO SQL Converter works well:)

Pieter

Pieter,

...the DBF TO SQL Converter works well.

Glad to hear that. Does it automatically add the HBRECNO field also?

James

James,

The sql tables have "ID" as first columnname(so it does not automaticly is changed to HBRECNO), I think it is not so difficult to change this to HBRECNO (Just a sql query). Is it true that hbrecno name has something to do with Adordd?, With the Dbf to Sql converter, i did not do anything with adordd.

I am now trying to use adordd with my companies test application(A smaller variant of the main application).

Pieter

Well, it is my understanding that SQL automatically creates the ID field for any new table (but I'm a not sure).

No.
SQL databases do not create any columns on their own.
It is the programmers' full responsibility to create a Primary Key.