Thanks for the tips. It works fine now. It took me 18 hours to migrate and test using hbdc including reading manual from the internet. I borrowed the code from the hmg sample as well. Here take a look at the code. It's my first code using SQL statement.
Code: Select all
#include "minigui.ch"
#include "inkey.ch"
#include "common.ch"
#include "dale-aid.ch"
#require "hbsqlit3"
**********************
function sq3Makefile()
LOCAL cMsg := ""
LOCAL lRetVal := .f.
LOCAL cCreateSQL := ""
LOCAL cIndexSQL := ""
LOCAL cQstr := ""
LOCAL cID := ""
LOCAL cNm := ""
LOCAL cAR := ""
LOCAL cLC, cCC, cST, cNT
LOCAL oConn, oMeta, oStmt, cSql, oRS
LOCAL aItems_ := {}
BEGIN SEQUENCE
IF .NOT. importOpen()
BREAK
ENDIF
oConn := hdbcSQLTConnection():New( sqDBLang, .T. )
oMeta := oConn:getMetaData()
cQstr := "SELECT * FROM sqlite_master WHERE type = 'table' ORDER BY name"
**cQStr := "SELECT name, tbl_name FROM sqlite_master"
oStmt := oConn:CreateStatement()
**-- check sql database if tables are needed to be created
oRS := oStmt:executeQuery( cQstr )
oRS:setTableName( "sqlite_master" )
cNT := ""
DO WHILE oRs:next()
cNT := oRs:getString( "tbl_name" )
IF .NOT. EMPTY( cNT )
AADD( aItems_, cNT )
ENDIF
ENDDO
IF LEN( aItems_ ) > 0
lRetVal := .t.
oRs:Close()
oStmt:Close()
oConn:Close()
BREAK
ENDIF
oRs:Close()
oStmt:Close()
cNT := ""
cCreateSQL := "CREATE TABLE country ("
cCreateSQL += " CCID char( 2 ) NOT NULL," // Country ID - Two-letter code from ISO-3166
cCreateSQL += " CCNM varchar( 75 ) NOT NULL," // Name - Country Name
cCreateSQL += " AREA varchar( 10 ) NOT NULL);" // Area - World Area
cCreateSQL += "CREATE TABLE LangCodes ("
cCreateSQL += " LANGID char(3) NOT NULL," // -- Three-letter code iso-639-3
cCreateSQL += " COUNTRY char(2) NOT NULL," // -- Main country where used
cCreateSQL += " STATUS char(1) NOT NULL," // -- L(iving), (e)X(tinct)
cCreateSQL += " NAME varchar(75) NOT NULL);" // -- Primary name in that country
cCreateSQL += "CREATE TABLE LangIndex ("
cCreateSQL += " LANGID char(3) NOT NULL," // -- Three-letter code for language iso-639-3
cCreateSQL += " COUNTRY char(2) NOT NULL," // -- Country where this name is used
cCreateSQL += " NAMETYPE char(2) NOT NULL," // -- L(anguage), LA(lternate), D(ialect), DA(lternate), LP,DP (a pejorative alternate)
cCreateSQL += " NAME varchar(75) NOT NULL)" // -- The name
cIndexSQL := "CREATE UNIQUE INDEX IdxCountry ON country (CCID,CCNM,AREA);"
cIndexSQL += "CREATE INDEX LangC ON LangCodes (LANGID,COUNTRY,NAME);"
cIndexSQL += "CREATE INDEX LangI ON LangIndex (LANGID,COUNTRY,NAME)"
oStmt := oConn:createStatement()
oStmt:executeUpdate( cCreateSQL )
oStmt := oConn:CreateStatement()
oStmt:executeUpdate( cIndexSQL )
oStmt:Close()
oConn:StartTransaction()
DBSELECTAREA( "COUNTRY" )
DBGOTOP()
WHILE .NOT. EOF()
cID := ALLTRIM( COUNTRY->CCID )
cNM := ALLTRIM( COUNTRY->CCNM )
cAR := ALLTRIM( COUNTRY->AREA )
cNM := STRTRAN( cNM, "'", "''" ) // stuff extra single quote
cQstr := "INSERT INTO country (CCID, CCNM, AREA) VALUES ( "
cQstr += c2SQL( cID ) + ","
cQstr += c2SQL( cNM ) + ","
cQStr += c2SQL( cAR ) + ")"
oStmt := oConn:CreateStatement()
oStmt:executeUpdate( cQstr )
DBSKIP()
END
oStmt:close()
DBSELECTAREA( "WORLDLANG" )
DBGOTOP()
WHILE .NOT. EOF()
cLC := ALLTRIM( WORLDLANG->LANGID )
cCC := ALLTRIM( WORLDLANG->COUNTRY )
cST := ALLTRIM( WORLDLANG->STATUS )
cNM := ALLTRIM( WORLDLANG->NAME )
cNM := STRTRAN( cNM, "'", "''" )
cQstr := "INSERT INTO LangCodes (LANGID,COUNTRY,STATUS,NAME) VALUES ( "
cQstr += c2SQL( cLC ) + ","
cQstr += c2SQL( cCC ) + ","
cQStr += c2SQL( cST ) + ","
cQStr += c2SQL( cNM ) + ")"
oStmt := oConn:CreateStatement()
oStmt:executeUpdate( cQstr )
DBSKIP()
END
oStmt:close()
DBSELECTAREA( "INDEXWORLD" )
DBGOTOP()
WHILE .NOT. EOF()
cLC := ALLTRIM( INDEXWORLD->LANGID )
cCC := ALLTRIM( INDEXWORLD->COUNTRY )
cNT := ALLTRIM( INDEXWORLD->NAMETYPE )
cNM := ALLTRIM( INDEXWORLD->NAME )
cNM := STRTRAN( cNM, "'", "''" )
cQstr := "INSERT INTO LangIndex (LANGID,COUNTRY,NAMETYPE,NAME) VALUES ( "
cQstr += c2SQL( cLC ) + ","
cQstr += c2SQL( cCC ) + ","
cQStr += c2SQL( cNT ) + ","
cQStr += c2SQL( cNM ) + ")"
oStmt := oConn:CreateStatement()
oStmt:executeUpdate( cQstr )
DBSKIP()
END
oStmt:close()
oConn:Commit()
oConn:Close()
lRetVal := .t.
MSGINFO( "Import to SQL is done.", "Finished" )
END SEQUENCE
IF .NOT. lRetVal
MSGSTOP( cMsg, "Error" )
ENDIF
DBCLOSEALL()
RETURN lRetVal
****************************
static function importOpen()
LOCAL lRetVal := .f.
BEGIN SEQUENCE
IF .NOT. NETUSE( ( dbCountry ), "COUNTRY", Shared, ReadOnly )
cBreakMsg := "Cannot open file " + dbLangCC + "."
BREAK
ENDIF
ORDLISTADD( ( ixCountry ) )
ORDSETFOCUS( "CountryID", ( ixCountry ) )
IF .NOT. NETUSE( ( dbTLWorldLang ), "WORLDLANG", Shared, ReadOnly )
cBreakMsg := "Cannot open file " + dbLangCd + "."
BREAK
ENDIF
ORDLISTADD( ( ixTLWorldLang ) )
ORDSETFOCUS( "CountryID", ( ixTLWorldLang ) )
IF .NOT. NETUSE( ( dbIndexWorld ), "INDEXWORLD", Shared, ReadOnly )
cBreakMsg := "Cannot open file " + dbIndexWorld + "."
BREAK
ENDIF
ORDLISTADD( ( ixIndexWorld ) )
ORDSETFOCUS( "LangArea", ( ixIndexWorld ) )
lRetVal := .t.
END ENSEQUENCE
RETURN lRetVal
function C2SQL(Value)
local cValue := ""
local cFormatoDaData := set(4)
do case
case Valtype(Value) == "N"
cValue := AllTrim(Str(Value))
case Valtype(Value) == "D"
if !Empty(Value)
cdate := dtos(value)
cValue := "'"+substr(cDate,1,4)+"-"+substr(cDate,5,2)+"-"+substr(cDate,7,2)+"'"
else
cValue := "''"
endif
case Valtype(Value) $ "CM"
IF Empty( Value)
cValue="''"
ELSE
cValue := "'" + value+ "'"
ENDIF
case Valtype(Value) == "L"
cValue := AllTrim(Str(iif(Value == .F., 0, 1)))
otherwise
cValue := "''" // NOTE: Here we lose values we cannot convert
endcase
return cValue