DBF to SQL

[TimStone]

I know this is a repeat question, but I also know work was done on this issue. The current number of features entices me to perhaps convert my existing application to SQL from DBF.

I know the FW_AdoImportFromDBF( ) exists, and I've seen a couple of posts on the forum that show it. However, I have not found a complete sample of using it.

Essentially I want to build a converter to take 140 .dbf files and make them into tables in a single SQL database ( MSSQL Express ). If using the function, must the tables exist in the SQL database already, or will the Import function actually create a table, and then import the data ?

I would be happy to see a full sample of this ( and just give me the name if it exists in my FWH Sample folder).

Sorry for repeating this thread but I reviewed others first, most of which were prior to the FW function mentioned above.

Thanks for the responses.

[driessen]

Hello Tim,

I am very interested in this subject. So, with your permission, I'd like to follow this item too.

Thanks.

[TimStone]

I have had so much to do, but now I have time to devote to this ... and perhaps others have not gotten around to making the switch.

Every time an update comes out more and more features are available for using with SQL, so there is plenty to explore now and implement.

[nageswaragunupudi]

FW_AdoImportFromDBF() creates and imports the dbf. If the table already exists on the server, the function prompts if the table is to be overwritten.

Does not matter 140 tables or 1400 tables we can import in one go.

Example:

Code: Select all  Expand view

RDDSETDEFAULT( "DBFCDX" )

oCn := FW_OpenAdoConnection( "MSSQL,server,database,sa,password", .t. )
if oCn == nil
   ? "connect fail"
   return nil
endif
AEval( DIRECTORY( "*.dbf"), { |a| FW_AdoImportFromDBF( oCn, a[ 1 ] ) } )
? "done"
 

I advise using MySQL or MariaDB instead of SQLEXPRESS.
If you use MySQL/MariaDB then I advise using the built-in library of FWH than using ADO.

Example using the built-in library of FWH for MySql/MariaDB

Code: Select all  Expand view

RDDSETDEFAULT( "DBFCDX" )

oCn := maria_Connect( "server,database,root,password" )
if oCn == nil
   ? "connect fail"
   return nil
endif
AEval( DIRECTORY( "*.dbf"), { |a| oCn:ImportFromDBF( a[ 1 ] ) } )
? "done"
 

If you have a lot of code using TData/TDatabase, using MySql/MariaDB built-in library enables faster migration because using the RowSet class is very similar to using TDatabase class.

[nageswaragunupudi]

Table handling with ADO (MSSQL,SQLEXPRESS,MSACCESS,MYSQL,ORACLE,etc)

Code: Select all  Expand view

oRs := FW_OpenRecordSet( oCn, "select * from customer" )
// accessing field value
? oRs:Fields( "salary" ):Value
// modifying field value and saving
oRs:Fields( "age" ):Value := 35
oRs:Fields( "Salary" ):Value := 40000
oRs:Update()
 

Table handling using the built-in library for MySql/MariaDB

Code: Select all  Expand view

oRs := oCn:RowSet( "customer" ) // or ( "select ... from customer" )
// accessing field value
? oRs:Salary
// modifying field value and saving
oRs:Age    := 35
oRs:Salary := 40000
oRs:Save() // or oRs:Update()
 

[TimStone]

Thank you for the samples and recommendations. I downloaded Maria db and installed it. My existing code would appear to be better suited to using that option since it would require less modificaition ( I think ).

[TimStone]

There is a section in pinned notes that references an EMBEDDED SERVER.

It provides instructions for MySQL. Will that also work for MariaDB ?

I had MariaDB running fine, but MySQL Workshop doesn't find the server.

Tim

[nageswaragunupudi]

Can we know the latest FWH version you are having with you?

Long time back, Oracle provided MySql embedded server. This enables working with MySql on local computer without installing full MySql server, though wit some limitations. Oracle dicontinued support a few years ago. Now there are no official downloads available. It is possible to download from 3rd party websites and FWH also provides it. We do NOT recommend using it.

viewtopic.php?f=3&t=33798

Install either MySql or MariaDb downloaded from their websites.

You can use either MySql workbench or HeidiSQL for directly connecting to the server and working like DBU. But FWH by itself is more than enough.

If you have already installed MariaDB, we can straight away start some samples and get used to it in one day.

If you like we can start today. Please tell us your latest FWH version.

[TimStone]

Good Morning,

I have been away from the computer doing some much needed house upgrades.

First, I use the very latest releases of FWH, which in this case is 21.02. I also build everything using Microsoft Visual Studio Community and use the very latest version ( which is 2019 I believe but continually updated ). I use Harbour, and I believe have the latest version provided from the Five Tech website.

I did get MySQL installed and running. MariaDb seems to be newer, but there are more training resources available for MySQL.

I found the embedded server idea to be good, but it is NOT necessary, and I will be happy to follow your advice and not use it. I just saw the original thread and sensed it would be easier to distribute.

Thanks. I will be jumping back into this tomorrow. Today I must attend to some clients fixing significant errors they created by making a mistake and trying to fix it themselves.

Tim

[Antonio Linares]

Dear Tim,

A new Visual Studio 2022 is coming which seems very very interesting:
https://devblogs.microsoft.com/visualstudio/visual-studio-2022
very interesting reading to understand why they have moved to 64 bits to build it

As soon as we can get our hands on it, we will start exploring its possibilities

[TimStone]

Of course. Thanks for the alert on this.

Is FWH 64 bit now fully functional ? I recall when I was doing builds with it, there were some issues and features that I could not include in my program. I sense it won't be long before we must move to 64 bit since Microsoft has now stabilized all their proprietary apps at that level ... and 128 bit processing is likely soon to become the new option.

[Antonio Linares]

Dear Tim,

> Is FWH 64 bit now fully functional ?

Yes, it is. Rock solid

Microsoft is doing it very well!

[Horizon]

Dear Tim,

> Is FWH 64 bit now fully functional ?

Yes, it is. Rock solid

Microsoft is doing it very well!

Hi Antonio,

I understand that the ide is only 64 bit. It still continues to produce 32 bit application.

Of course, We should produce 64 bit application but there are still 32 bit 3rd party library that has not 64 bit support. We should change them to 64 bit others libs.

[Rick Lipkin]

Tim

Just sent you my dbf to sql conversion code .. it is not elegant but you are in control .. Check your e-mail and let me know if I can answer any questions ..

Thanks
Rick Lipkin

[TimStone]

Hacan,

Yes, that was my problem before. I have a 32 bit license for a spell checker. They made no improvements, but finally compiled it as 64 bit but wanted a very expensive fee for the "upgrade". I think I had a couple of other libraries also that were only 32 bit and wouldn't work, so I had to set the 64 bit build aside. Another issue was trying to get a 64 bit set of ADS libs. ALL FWH work seemed fine.

Rick,

I got the email. Thank you.