With the help from Rick Lipkin and enrico i have no a working example.
Taking Customer.dbf as source , rick builds the SQL-statement literal as :
cSQL := "CREATE TABLE " + cTab //Customer"
cSQL += "( "
cSQL += "[CustomerEid] Counter NOT NULL, " // primary key
cSQL += ......
This can not be done with a arbitrarily choosen dbf. Enrico has a routine to build this statement.
- Code: Select all Expand view
FOR i = 1 TO LEN( aFlds )
cType = aFlds[ i, DBS_TYPE ]
IF cType <> "M" .OR. cMot <> "MYSQL"
aFlds[i,1] := ALLTRIM(aFlds[i,1])
IF LEFT(aFlds[i,1],1)<>"["
aFlds[i,1] := "[" + aFlds[i,1]
END
IF RIGHT(aFlds[i,1],1)<>"]"
aFlds[i,1] += "]"
END
END
DO CASE
CASE cType = "C"
cQuery += aFlds[ i, DBS_NAME ] + " VARCHAR ( " + NTRIM( aFlds[ i, DBS_LEN ] ) + " ), "
CASE cType = "N"
cQuery += aFlds[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFlds[ i, DBS_LEN ] + 1 ) + ", " + NTRIM( aFlds[ i, DBS_DEC ] ) + " ), "
CASE cType = "D"
cQuery += aFlds[ i, DBS_NAME ] + " DATETIME, "
CASE cType = "L"
cQuery += aFlds[ i, DBS_NAME ] + " INT, "
CASE cType = "M"
IF cMot == "JET"
cQuery += "[" + aFlds[ i, DBS_NAME ] + "]" + " MEMO, "
ELSEIF cMot == "MSSQL"
cQuery += "[" + aFlds[ i, DBS_NAME ] + "]" + " TEXT, "
ELSEIF cMot == "MYSQL"
cQuery += aFlds[ i, DBS_NAME ] + " TEXT, "
ENDIF
ENDCASE
NEXT
Also this code works , BUT :
1)
in CASE cType = "N"
cQuery += aFlds[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFlds[ i, DBS_LEN ] +1 ) + ", " + NTRIM( aFlds[ i, DBS_DEC ] ) + " ), "
I had to add 1 to the len from the field , if not i got e error in column salary (proc populate)
2) CASE cType = "L"
cQuery += aFlds[ i, DBS_NAME ] + " INT, "
Values are -1 or o (integer) , in the code from rick it is logical
cSQL += "[Married] Yesno NULL, "
3) Code from enrico , column salary shows the numbers*100 !!!!
Why ?
Main question : can the code from enrico be improved ?
Frank
- Code: Select all Expand view
# include "fivewin.ch"
# include "dbstruct.ch"
# include "ado.ch"
# include "xbrowse.ch"
#xcommand DBG <vars,...> => ;
XBrowse( ArrTranspose( \{ \{ <"vars"> \}, Eval( \{ || \{ <vars> \} \} ) \} ), ;
ProcName(0) + " : Line : " + LTrim( Str( ProcLine(0) ) ),, ;
{ |o| o:cHeaders := { "Variable", "Value" } } )
#define STRIM( cStr, nChr ) Left( cStr, Len( cStr ) - nChr )
#define NTRIM( nNumber ) LTrim( Str( nNumber ) )
PROC MAIN(...)
// DbfFile , MdbFile , TableName
LOCAL aFlds
LOCAL aParams := HB_aParams()
LOCAL oCon
LOCAL nYear , cFile , nStart , cDefa
//LOCAL xCONNECT := "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=Test.mdb"
SET DELETED on
SET CENTURY on
SET 3DLOOK on
nYEAR := ( year( DATE() )-30 )
SET EPOCH to ( nYEAR )
REQUEST DBFCDX
rddsetdefault ( "DBFCDX" )
cFILE := GetModuleFileName( GetInstance() )
// where .exe started from is default directory //
nSTART := RAT( "\", cFILE )
cDEFA := SUBSTR(cFILE,1,nSTART-1)
SET DEFA to ( cDEFA )
USE CUSTOMER
aFlds := DBSTRUCT()
CLOSE
IF File("Test.mdb")
fErase("Test.mdb")
END
IF ! File("Test.mdb")
FW_CreateMDB( "Test.mdb") // FWH1305
END
AddTable("JET","Customer",aFlds,"Test.mdb")
Populate("Test.mdb","Customer","CUSTOMER.DBF")
AdoBrowse("Test.mdb","Customer")
RETURN
FUNC AddTable(cMot, cTab, aFld , cMdbFile)
LOCAL oCn , xConnect , cSQL , oErr
Try
oCn := CREATEOBJECT( "ADODB.Connection" )
Catch
MsgInfo( "Could not create the ADO object for connection")
End Try
TRY
xCONNECT := "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=" + cMdbFile //Test.mdb"
oCn:Open( xCONNECT )
CATCH oErr
MsgInfo( "Could not open a Connection to Database "+cMdbFile )
RETURN(.F.)
END TRY
# ifndef TEST
cSQL := Enrico(aFld,"JET",cTab)
# else
cSQL := "CREATE TABLE " + cTab //Customer"
cSQL += "( "
cSQL += "[CustomerEid] Counter NOT NULL, " // primary key
cSql += "[First] Char(20) NULL ,"
cSql += "[Last] Char(20) NULL ,"
cSQL += "[Street] Char(30) NULL, "
cSQL += "[City] Char(30) NULL, "
cSQL += "[State] Char(2) NULL, "
cSQL += "[Zip] Char(10) NULL, "
cSQL += "[HireDate] DateTime NULL, "
cSQL += "[Married] Yesno NULL, "
cSQL += "[Age] Integer DEFAULT 0, "
cSQL += "[Salary] Money Default 0, "
cSQL += "[Notes] Memo NULL, "
cSQL += "CONSTRAINT PK_CUSTOMER PRIMARY KEY ( CustomerEid )"
cSQL += " )"
# endif
Try
oCn:Execute( cSQL )
Catch
MsgInfo( "Create Table Customer Failed" )
oCn:Close()
Return(.f.)
End try
oCn:Close()
oCn := nil
RETURN(.T.)
FUNC Populate(cMdbFile,cTableName,cDbfFile)
LOCAL xConnect := "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=" + cMdbFile
LOCAL oRs , cSQL , oErr , saying , i , j , n
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic
cSQL := "SELECT * FROM " + cTableName //Customer"
TRY
oRs:Open( cSQL, xCONNECT )
CATCH oErr
Saying := "Can not open table CUSTOMER"
If ! MsgNoYes( saying )
Return(.f.)
Endif
End Try
Use (cDbfFile) via "DBFCDX" EXCLUSIVE NEW
//Set Order to Tag Last
//Select Customer
Go Top
i := 0
Do While .not. Eof()
i++
oRs:AddNew()
FOR j := 1 TO oRS:Fields:Count
fName := oRs:Fields[j-1]:Name
IF (n := FieldPos(fName)) > 0
oRs:Fields(fName):Value := Fieldget(n)
END
NEXT
/*
oRs:Fields("First"):Value := Customer->First
oRs:Fields("Last"):Value := Customer->Last
oRs:Fields("Street"):Value := Customer->Street
oRs:Fields("City"):Value := Customer->City
oRs:Fields("State"):Value := Customer->State
oRs:Fields("Zip"):Value := Customer->Zip
oRs:Fields("HireDate"):Value := Customer->HireDate
oRs:Fields("Married"):Value := Customer->Married
oRs:Fields("Age"):Value := Customer->Age
//oRs:Update()
//? Customer->Salary
oRs:Fields("Salary"):Value := Customer->Salary
//oRs:Update()
oRs:Fields("Notes"):Value := Customer->Notes
*/
oRs:Update()
IF i == 2
//? oRs:Fields("Salary"):Type
//? oRs:Fields("Salary"):Value , Customer->Salary , oRs:Fields("Married"):Value
//DBG oRs:Fields("Salary"):DefinedSize , oRs:Fields("Salary"):NumericScale , oRs:Fields("Salary"):Precision
END
Select Customer
Skip
Enddo
DBG i
CLose Databases
oRs:CLose()
oRs := nil
CLose Databases
RETURN .T.
PROC AdoBrowse(cMdbFile,cTableName)
***********************************
local oCon
local xCONNECT := "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source="+cMdbFile //Test.mdb"
LOCAL oRs , oErr
Try
oCon := CREATEOBJECT( "ADODB.Connection" )
Catch
MsgInfo( "Could not create the ADO object for connection")
End Try
TRY
oCon:Open( xCONNECT )
CATCH oErr
MsgInfo( "Could not open a Connection to Database "+cMdbFile )
RETURN(.F.)
END TRY
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType = 1 // opendkeyset
oRs:CursorLocation = 3 // local cache
oRs:LockType = 3 // lockoptimistic
try
oRs:Open( "SELECT * FROM " + cTableName, oCon ) // Password="abc" )
catch oErr
? oErr:Description , Procname() , procline()
end
XBROWSER oRS TITLE "Adobrowse : " + cMdbFile + ","+cTableName
oRs:Close()
oRs := nil
oCon:Close()
oCon := nil
RETURN
*********************************************************************
FUNC enrico(aFld,cMot,cTab)
*****************
LOCAL cQuery := "CREATE TABLE " + cTab + " ( "
LOCAL cType
LOCAL i , el
LOCAL aFlds := aClone(aFld)
IF cMot == "JET"
cQuery += "Id COUNTER PRIMARY KEY, "
ELSEIF cMot == "MSSQL"
cQuery += "Id INT IDENTITY PRIMARY KEY, "
ELSEIF cMot == "MYSQL"
cQuery += "Id SERIAL, "
ENDIF
FOR i = 1 TO LEN( aFlds )
cType = aFlds[ i, DBS_TYPE ]
IF cType <> "M" .OR. cMot <> "MYSQL"
aFlds[i,1] := ALLTRIM(aFlds[i,1])
IF LEFT(aFlds[i,1],1)<>"["
aFlds[i,1] := "[" + aFlds[i,1]
END
IF RIGHT(aFlds[i,1],1)<>"]"
aFlds[i,1] += "]"
END
END
DO CASE
CASE cType = "C"
cQuery += aFlds[ i, DBS_NAME ] + " VARCHAR ( " + NTRIM( aFlds[ i, DBS_LEN ] ) + " ), "
CASE cType = "N"
cQuery += aFlds[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFlds[ i, DBS_LEN ] + 1 ) + ", " + NTRIM( aFlds[ i, DBS_DEC ] ) + " ), "
CASE cType = "D"
cQuery += aFlds[ i, DBS_NAME ] + " DATETIME, "
CASE cType = "L"
// Changed , see comment !!!
//"[Married] Yesno NULL, "
IF cMot == "JET"
cQuery += aFlds[ i, DBS_NAME ] + " yesno NULL, "
ELSE
cQuery += aFlds[ i, DBS_NAME ] + " INT, "
ENDIF
CASE cType = "M"
IF cMot == "JET"
cQuery += "[" + aFlds[ i, DBS_NAME ] + "]" + " MEMO, "
ELSEIF cMot == "MSSQL"
cQuery += "[" + aFlds[ i, DBS_NAME ] + "]" + " TEXT, "
ELSEIF cMot == "MYSQL"
cQuery += aFlds[ i, DBS_NAME ] + " TEXT, "
ENDIF
ENDCASE
NEXT
cQuery = STRIM( cQuery, 2 ) + " )"
RETURN cQuery