Hello,

Can anyone recommend a good tool to create a MS SQL script from DBF structure?

Thank you for your assistance.

Sincerely,

#include "fivewin.ch"
#include "xbrowse.ch"
#include "ribbon.ch"
#include "hbcompat.ch"

Static oServer

FUNCTION Main()

   LOCAL oDlg, oBtn
   LOCAL aBtns := Array( 5 )
   LOCAL oBrw, oQry  // , oServer
   local cValType

   
   D_SetCaseSensitive( .T. )
   
   IF ( oServer := ConnectTo() ) == NIL
      msgalert('cannot connect to server')
      RETURN NIL
   ENDIF
   oServer:bDebug = {| cQry | LogFile( "debuf.log", {cQry} ) }
   
// oQry = oServer:Query( "SELECT * FROM rmty_avl") // where rta_rmty='LOB'" )
   
// oQry:SetPages( 100 )
// oQry:bOnChangePage = { || oBrw:Refresh(), ChangeTitle( oQry, oDlg ) }

   USE CCRACCT SHARED

   DEFINE DIALOG oDlg TITLE 'Import from Dbf' ; // "Current Page: " + StrZero( oQry:nCurrentPage, 5 ) + " / " + StrZero( oQry:nMaxPages, 5 )
                SIZE 565, 480


   @  10, 10 RBBTN oBtn PROMPT 'Test' SIZE 40, 30 OF oDlg ;
                ACTION ImportDbf()


// Uncomment this line for fivewin version < 10.7   
//   SetDolphin( oBrw, oQry )
   
      
   ACTIVATE DIALOG oDlg CENTERED
   
   CLOSE CCRACCT

RETURN NIL


*-------------------*
Procedure ImportDbf
local cFile := cGetFile32('select dbf |*.DBF','Load file')

FW_AdoImportFromDBF( oServer, cFile, "CCRACCT" )

return


#include "connto.prg"
#include "setbrw.prg"

nageswaragunupudi wrote:FWH itself is a good tool.
Buitin function FW_AdoCreateTableSQL( cTable, aCols, oCn, lAddAutoInc ) returns SQL script to create table.

Usage:

Code (fw): Select all Collapse

#include "fivewin.ch"
#include "adodef.ch"

local oCn, cSql

ADOCONNECT oCn TO MSSQL SERVER <server> USER SA PASSWORD <pw>
USE CUSTOMER SHARED
cSql := FW_AdoCreateTableSQL( "CUSTOMER", CUSTOMER->(DBSTRUCT()), oCn )
MemoEdit( cSql )  // script

Actually, using FWH, we can even do more.
Using FWAdoCreateTable( cTable, aCols, oCn, lAddAutoInc ), we can actually create a table on the SQL Server with the same structure as the DBF.

We can do more:
Using FW_AdoImportFromDBF( oCn, cDbf, cAdoTable, cColPrefix, nMultiRowSize, aFields ) we can import a DBF to Sql server.

Usage:
FW_AdoImportFromDBF( oCn, "c:\fwh\samples\customer.dbf", "CUSTOMER" )

Mr Dutch

1) Please request DBFCDX. We are using DBFCDX by default. This should solve your DBF Open problem.
2) For this and any FW_Ado* functions to work properly, we need to open ADO connection, preferably using FW_OpenADOConnection( cStr ) function. These ADO functions recognize ADO connection object only and can not recognize oServer object used by TDolphin/TMySql

We tested FW_AdoImportDBF function with Access, MSSql, MySql, Oracle and SQLite. We could export all DBFs in the \fwh\samples folder successfully, including image data etc.

If you do not want to use ADO, probably Mr Daniel might have provided some functionality to export DBF in the dolphin libs.

ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE easyfo USER root PASSWORD nimda

nageswaragunupudi wrote:Mr Dutch

1) Please request DBFCDX. We are using DBFCDX by default. This should solve your DBF Open problem.
2) For this and any FW_Ado* functions to work properly, we need to open ADO connection, preferably using FW_OpenADOConnection( cStr ) function. These ADO functions recognize ADO connection object only and can not recognize oServer object used by TDolphin/TMySql

We tested FW_AdoImportDBF function with Access, MSSql, MySql, Oracle and SQLite. We could export all DBFs in the \fwh\samples folder successfully, including image data etc.

If you do not want to use ADO, probably Mr Daniel might have provided some functionality to export DBF in the dolphin libs.

   
ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE easyfo USER root PASSWORD nimda

.OR.

 cStr := "Driver={MySQL ODBC 3.51 Driver};Server=localhost;" + ;
    "Database=easyfo;User=root;Password=nimda;Option=3;"

    oCn := FW_OpenAdoConnection( cStr )

nageswaragunupudi wrote:Mr Dutch

1) Please request DBFCDX. We are using DBFCDX by default. This should solve your DBF Open problem.
2) For this and any FW_Ado* functions to work properly, we need to open ADO connection, preferably using FW_OpenADOConnection( cStr ) function. These ADO functions recognize ADO connection object only and can not recognize oServer object used by TDolphin/TMySql

We tested FW_AdoImportDBF function with Access, MSSql, MySql, Oracle and SQLite. We could export all DBFs in the \fwh\samples folder successfully, including image data etc.

If you do not want to use ADO, probably Mr Daniel might have provided some functionality to export DBF in the dolphin libs.

Thank You Very Much!

Mr Dutch

Glad the functions are working for you now.

FW_OpenAdoConnection( cFullConnectionString ) works. But we need to know whether the target PC is using version 3.51 or 5.1.

I advise using either:
1) ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE easyfo USER root PASSWORD nimda
or
2) oCn := FW_OpenAdoConnection( "MYSQL,localhost,easyfo,root,nimda" ) // fwh's connection spec format.

FWH uses the version avaible on the PC and uses that version to connect.

Same is the case with Access, MSSql, Oracle, etc. Better we let FWH to use the driver/provider available on the customer's PC.

Mr Darrell Ortiz

Hope the information is useful to you.

Dear Mr. Rao,

Is there a limit to the number of fields? I am trying to create script for DBF that has 279 fields and I am getting the following error.

I am trying to connect to MS SQL 2008.

Source code is below error.

Application

Path and name: C:\Winapps\cargo\data\cdmsql.exe (32 bits)
Size: 2,546,688 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20130422)
FiveWin Version: FWHX 14.06
Windows version: 6.1, Build 7601 Service Pack 1

Time from start: 0 hours 0 mins 2 secs
Error occurred at: 06/26/2014, 20:34:18
Error description: Error BASE/1132 Bound error: array access
Args:
[ 1] = A { ... }
[ 2] = N 1

Stack Calls

Called from: .\source\function\ADOFUNCS.PRG => FW_ADOCREATETABLESQL( 678 )
Called from: cdmsql.prg => _SQLSCRIPT( 102 )
Called from: cdmsql.prg => (b)MAIN( 54 )
Called from: .\source\classes\MENU.PRG => TMENU:COMMAND( 463 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND( 1050 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3279 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:ACTIVATE( 1003 )
Called from: cdmsql.prg => MAIN( 72 )

// Source code...
//----------------------------------------------------------------------------//
function _SqlScript( oSay, oSay2 )

local lFail := .F. , ;
cSkipped := '' , ;
oCn , ;
cSql , ;
cSqlCon := "" , ;
aCdmDbf := Directory( "*.DBF" ) , ;
nX1 := 0 , ;
cTotSql := "" , ;
cCdmDbf := ""

// Init...
cSqlCon := "Server=0.0.0.0;Database=CDMWINFRT;User Id=xxxxxxxxxx;Password=xxxxxxxxxx;"

// Connect to MS SQL...
oCn := FW_OpenAdoConnection( cSqlCon )

FOR nX1=1 TO LEN( aCdmDbf )
cCdmDbf := aCdmDbf[nX1,1]

  oSay:SetText( "Creating SQL Script for " + cCdmDbf + " . . ." )
  SysRefresh()

  USE (cCdmDbf) SHARED
  cSql    := FW_AdoCreateTableSQL( cCdmDbf, (cCdmDbf)->(DBSTRUCT()), oCn )
  cTotSql += cSql
  DbCloseAll()
  EXIT

NEXT nX1

MemoEdit( cTotSql ) // script

Dear Mr. Rao,

I found the problem. I did not truncate the .DBF.

Mr. Rao,

I am getting the following error when trying to display 'DBMS Name'

Application

Path and name: C:\Winapps\cargo\data\cdmsql.exe (32 bits)
Size: 2,547,200 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20130422)
FiveWin Version: FWHX 14.06
Windows version: 6.1, Build 7601 Service Pack 1

Time from start: 0 hours 0 mins 5 secs
Error occurred at: 06/27/2014, 06:20:10
Error description: Error BASE/1004 Class: 'NIL' has no exported method: PROPERTIES
Args:
[ 1] = U
[ 2] = C DBMS Name

Stack Calls

Called from: => PROPERTIES( 0 )
Called from: cdmsql.prg => _SQLSCRIPT( 102 )
Called from: cdmsql.prg => (b)MAIN( 54 )
Called from: .\source\classes\MENU.PRG => TMENU:COMMAND( 463 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND( 1050 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3279 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:ACTIVATE( 1003 )
Called from: cdmsql.prg => MAIN( 72 )

Mr. Rao,

I get the following error when trying to display Extended Properties

Application

Path and name: C:\Winapps\cargo\data\cdmsql.exe (32 bits)
Size: 2,547,200 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20130422)
FiveWin Version: FWHX 14.06
Windows version: 6.1, Build 7601 Service Pack 1

Time from start: 0 hours 0 mins 3 secs
Error occurred at: 06/27/2014, 06:25:43
Error description: Error BASE/1004 Class: 'NIL' has no exported method: PROPERTIES
Args:
[ 1] = U
[ 2] = C Extended Properties

Stack Calls

Called from: => PROPERTIES( 0 )
Called from: cdmsql.prg => _SQLSCRIPT( 103 )
Called from: cdmsql.prg => (b)MAIN( 54 )
Called from: .\source\classes\MENU.PRG => TMENU:COMMAND( 463 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND( 1050 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3279 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:ACTIVATE( 1003 )
Called from: cdmsql.prg => MAIN( 72 )

That means oCn is NIL and that in turn means you could not successfully connect to the mssql server.

After using oCn := FW_OpenAdoConnection( cStr )
we need to check:
if oCn == nil
// connecton failed. take appropriate action
else
// connection succeeded. Proceed with next work
endif

You failed to connect to the server because your connection string ( cSqlCon := "Server=0.0.0.0;Database=CDMWINFRT;User Id=xxxxxxxxxx;Password=xxxxxxxxxx;" is wrong.

Typical connection string to MSSql server looks like this:
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;
User Id=myUsername;Password=myPassword;
You may refer to http://www.connectionstrings.com/sql-server/ for more information.

In case you like FWH to construct the connection string for you, you can use this syntax:

ADOCONNECT oCn TO MSSQL SERVER <sqlservername>
DATABASE <initialcatalog> USER <username,eg:SA> PASSWORD <password>

After successful connection to the server, then you can use any other ado functions. If oCn != nil, the connection is successful.