topic-40259

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

DBF to SQL
Posted: Wed Apr 14, 2021 09:57 PM

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.

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

driessen

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Re: DBF to SQL
Posted: Wed Apr 14, 2021 10:58 PM

Hello Tim,

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

Thanks.

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

TimStone

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Re: DBF to SQL
Posted: Thu Apr 15, 2021 12:22 AM

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.

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: DBF to SQL
Posted: Thu Apr 15, 2021 03:11 AM

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: DBF to SQL
Posted: Thu Apr 15, 2021 04:33 AM

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

Code (fw): Select all Collapse

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 (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

TimStone

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Re: DBF to SQL
Posted: Thu Apr 15, 2021 07:45 PM

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

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

TimStone

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Re: DBF to SQL
Posted: Thu Apr 15, 2021 11:45 PM

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

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: DBF to SQL
Posted: Fri Apr 16, 2021 03:21 AM

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=3t=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.

&

Regards

G. N. Rao.

Hyderabad, India

TimStone

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Re: DBF to SQL
Posted: Tue Apr 20, 2021 04:01 PM

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

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

Antonio Linares

Posts: 44162

Joined: Thu Oct 06, 2005 05:47 PM

Re: DBF to SQL
Posted: Tue Apr 20, 2021 06:10 PM

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

regards, saludos

Antonio Linares
www.fivetechsoft.com

TimStone

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Re: DBF to SQL
Posted: Tue Apr 20, 2021 06:57 PM

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.

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

Antonio Linares

Posts: 44162

Joined: Thu Oct 06, 2005 05:47 PM

Re: DBF to SQL
Posted: Tue Apr 20, 2021 07:10 PM

Dear Tim,

> Is FWH 64 bit now fully functional ?

Yes, it is. Rock solid :-)

Microsoft is doing it very well!

regards, saludos

Antonio Linares
www.fivetechsoft.com

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: DBF to SQL
Posted: Wed Apr 21, 2021 06:58 AM

Antonio Linares wrote: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.

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: DBF to SQL
Posted: Wed Apr 21, 2021 01:26 PM

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

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Re: DBF to SQL
Posted: Wed Apr 21, 2021 06:39 PM

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.

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

FiveTech Support Forums

Continue the discussion