DBF to SQL

DBF to SQL

Postby TimStone » Wed Apr 14, 2021 9: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
User avatar
TimStone
 
Posts: 2950
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA

Re: DBF to SQL

Postby driessen » 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. 24.07 - Harbour 3.2.0 (February 2024) - xHarbour Builder (January 2020) - Bcc773
User avatar
driessen
 
Posts: 1422
Joined: Mon Oct 10, 2005 11:26 am
Location: Genk, Belgium

Re: DBF to SQL

Postby TimStone » 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
User avatar
TimStone
 
Posts: 2950
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA

Re: DBF to SQL

Postby nageswaragunupudi » Thu Apr 15, 2021 3: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: Select all  Expand view  RUN

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  RUN

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
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DBF to SQL

Postby nageswaragunupudi » Thu Apr 15, 2021 4:33 am

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

Code: Select all  Expand view  RUN

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  RUN

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
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DBF to SQL

Postby TimStone » Thu Apr 15, 2021 7: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
User avatar
TimStone
 
Posts: 2950
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA

Re: DBF to SQL

Postby TimStone » 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
User avatar
TimStone
 
Posts: 2950
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA

Re: DBF to SQL

Postby nageswaragunupudi » Fri Apr 16, 2021 3: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=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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DBF to SQL

Postby TimStone » Tue Apr 20, 2021 4: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
User avatar
TimStone
 
Posts: 2950
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA

Re: DBF to SQL

Postby Antonio Linares » Tue Apr 20, 2021 6: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
User avatar
Antonio Linares
Site Admin
 
Posts: 42203
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Re: DBF to SQL

Postby TimStone » Tue Apr 20, 2021 6: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
User avatar
TimStone
 
Posts: 2950
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA

Re: DBF to SQL

Postby Antonio Linares » Tue Apr 20, 2021 7: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
User avatar
Antonio Linares
Site Admin
 
Posts: 42203
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Re: DBF to SQL

Postby Horizon » Wed Apr 21, 2021 6: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.04
Horizon
 
Posts: 1322
Joined: Fri May 23, 2008 1:33 pm

Re: DBF to SQL

Postby Rick Lipkin » Wed Apr 21, 2021 1: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
User avatar
Rick Lipkin
 
Posts: 2666
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: DBF to SQL

Postby TimStone » Wed Apr 21, 2021 6: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
User avatar
TimStone
 
Posts: 2950
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 79 guests