DBF to SQL script tool

[Horizon]

This example shows
1. Connect to MS Sql server
2. Create a new Database (Catalog) by name FWH
3. Export c:\fwh\samples\customer.dbf to the Server in the Catalog (database) FWH
4. Open CUSTOMER table and browse.

Code: Select all  Expand view

#include "fivewin.ch"
#include "adofuncs.ch"

//----------------------------------------------------------------------------//

function Main()

   local oCn, oRs

   //----------------------------------------------------------------------------//
   // 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,FWH,SA,Secret", .t. )
   if oCn == nil
      ? "Connect Fail"
      QUIT
   endif

   ? "Importing Customer.dbf"
   FW_AdoImportFromDBF( oCn, "c:\fwh\samples\customer.dbf", "CUSTOMER" )

   ? "Imported. Opening CUSTOMER table on the MSSQL server"
   oRs      := FW_OpenRecordSet( oCn, "CUSTOMER" )
   XBROWSER oRs

   oRs:Close()
   oCn:Close()
   ? "Done"

return nil
 

In the function FW_OpenAdoConnection:
Please replace
SQLEXPRES with your Server name
FWH with the Database name you want
Secret with your password

Next release of FWH includes TRecSet class.
This will be fully compatible with ADO syntax and also TData/TDatabase syntax.
Existing applications will run without any change. Just replace
oRs := TOleAuto():New( "ADODB.RecordSet" )
with
oRs := TRecSet():New()
Advantages are :
a) We can use oRs:Fields( "HireDate" ):Value or oRs:HireDate
b) We need not check if record set is empty before using MoveFirst(), etc.
We can safely use MoveFirst() or GoTop() etc everywhere. Error checking is builtin
c) Field Access and Assign provide for padding/trimming/null values/ and other necessary conversions and error checking. Also takes care of differences between different databases.

Hi,

Is this code valid for MySQL?

Thanks

[TimStone]

Email:

Tim (at) GTSTONE.com

Thanks.

[cdmmaui]

Hi Tim,

I just sent you PRG.

Sincerely,

[TimStone]

Darrell,

Thank you for the updated function file. The conversion now works perfectly.

Mr. Rao,

If you would like someone to test your new tRecSet( ) functions, I would be happy to do so. I now have a successful conversion of all 108 data files to SQL, and my application is being built with FWH / Harbour / MS Visual Studio 2013. It will be a slow process in rebuilding this app, but that is not a problem.


Tim

[cdmmaui]

Dear Mr. Rao,

I am willing to test as well. We are converting a very large application to MS SQL.

Sincerely,

[nageswaragunupudi]

Mr Hakan ONEMLI

FWH Ado functions work for Access, MS Sql, MySql, Oracle and SQLite3.

[nageswaragunupudi]

Mr Tim and Mr Ortiz

In the next version, we propose to include export function based on bulk inserts which are ten times faster than the present export function. To start with they will work for text only data for MySql and MSSql only. While you may test the present functions, you may wait for a few days and check the new functions for your production software.

It is also a good idea that these functions and TRecSet are beta tested before release. I shall inform you in a few days.

[TimStone]

Mr. Rao,

I will only be using MS SQL at this time. I realize that different versions have variants in the commands.

The function to import the DBF files to an MSSQL database worked perfectly. I am looking forward to the additional functions.

Tim

[TimStone]

Mr. Rao,

Any progress on test versions of this capability ?

Thanks.

Tim Stone

[Horizon]

Hi Mr. Rao,

I trying to connect MySql. I have installed mysql-installer-community-5.6.19.0 with developer edition.

My code :

Code: Select all  Expand view

  ? "Connect to Server Database " + cInitCat
   oCn   := FW_OpenAdoConnection( "MYSQL,localhost,,root,1111", .t. )
   if oCn == nil
      ? "Connect Fail"
      QUIT
   endif

I could not connect to mysql.


Can you help me?

MySql information:


My ODBC window.

[nageswaragunupudi]

Version 5.3 support was not there.

Please open adofuncs.prg and modify the part of static saProviders array relating to MYSQL
Present line:

Code: Select all  Expand view

{ "MYSQL",    "ODBC",  { "Driver={MySQL ODBC 5.1 Driver}", "Driver={MySQL ODBC 3.51 Driver}" }, "Option=3" }, ;
 

Please replace this line with the following:

Code: Select all  Expand view

{ "MYSQL",    "ODBC",  { "Driver={MySQL ODBC 5.3 ANSI Driver}", ;
                         "Driver={MySQL ODBC 5.2 ANSI Driver}", ;
                         "Driver={MySQL ODBC 5.1 Driver}", ;
                         "Driver={MySQL ODBC 3.51 Driver}" }, "Option=3" }, ;
 

Please compile adofuncs.prg with this modification and link with your application.

[Horizon]

Hi Mr. Rao,


I have added and no change. It still does not connect.

[Horizon]

I was installed 64 bit connector. instead I have installed 32 bit connector again.
It connects now.

But there is a cInitCat variable in the sample. what should it be?

[Horizon]

I was installed 64 bit connector. instead I have installed 32 bit connector again.
It connects now.

But there is a cInitCat variable in the sample. what should it be?

up

[Horizon]