VFP 愛用者社區 首頁 VFP 愛用者社區
本討論區為 Visual Foxpro 愛用者經驗交流的地方, 請多多利用"搜尋"的功能, 先查看看有無前例可循, 如果還有不懂的再發問. 部份主題有附加檔案, 須先註冊成為社區居民才可以下載.
 
 常見問題常見問題   搜尋搜尋   會員列表會員列表   會員群組會員群組   會員註冊會員註冊 
 個人資料個人資料   登入檢查您的私人訊息登入檢查您的私人訊息   登入登入 

VFP table upload to mysql

 
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> VFP 討論區
上一篇主題 :: 下一篇主題  
發表人 內容
garfield
Site Admin


註冊時間: 2003-01-30
文章: 2057


第 1 樓

發表發表於: 星期五 三月 10, 2017 4:02 pm    文章主題: VFP table upload to mysql 引言回覆

*只需要先建立 mysql database 及權限, 會自動將這個資料夾所有的 dbf 上傳到 mysql.
*部份程式碼是採用網路上找到 Paul McNett 的資料
代碼:

* Program:   dbf2MySQL.prg      
* Author:   Michael J. Babcock, MCP         modify by : garfield
* Date:      08-25-08            modify date: 2017/3/10
* Purpose:   To upload the VFP data to the MySQL database.   fix: 如果mysql 的table不存在,會自動建立, 會產生一個mysqltable.sql 來記錄產生table的SQL指令
* Preconds:   You might have to make sure the PK fields are NOT set to autoincrement when this is run.  Double-check that.
* Comments:   If you make any mods/improvements, please update the ProFox downloads page so we all benefit.  Cheers!

#DEFINE    tranunicode   .t.
#DEFINE    chgReservedWord   "f_"
#DEFINE    DEFAULT_KEYFIELDLIST   "pk"
#DEFINE    DEFAULT_BUFFERING      5
#DEFINE     VERBOSE                 .T.

*tranunicode 是否將文字欄位轉成 unicode ==>只適用於vfp9
*chgReservedWord   當遇到欄位名稱跟mysql保留字一樣時, 要將它的欄位名稱[上 那些字
*DEFAULT_KEYFIELDLIST   產生的 mysql table 都會自動[上一個欄位 pk 它是 autoincrement

SYS(3101,65001)
SET SAFETY OFF
SET MEMOWIDTH TO 65534
SET EXACT OFF
SET STATUS BAR OFF
SET TALK OFF


LOCAL liHandle as Integer, lcConnection as String, lcDBC as String, lcSQL as String, lcTable as String, loRec as String, ;
      llSuccess as Logical, lcMsg as String
LOCAL ARRAY laTables[1], laError[1]
LOCAL msql

STRTOFILE('//自動建立mysql資料結構 :'+TTOC(DATETIME())+CHR(13)+CHR(10),'mysqltable.sql')
DELETE FILE zchkstru.*

CLOSE DATABASES ALL
SQLDISCONNECT(0)
lcConnection = [DRIVER={MySQL ODBC 3.51 Driver};SERVER=your-server-here;UID=root;PWD=password;DATABASE=yourdb;option=131609]
lcConnection = 'DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;UID=使用者名稱;PWD=密碼;Stmt=set names utf8;charset=utf8;database=資料庫名稱;'
*lcDBC = [c:\yourdatapath\yourVFPdb.DBC]

liHandle = SQLSTRINGCONNECT(lcConnection)
IF liHandle > 0 THEN
   *OPEN DATABASE (lcDBC) NOUPDATE
   ADIR(mlist,'*.dbf')
   *ADBOBJECTS(laTables,"TABLE")
   DIMENSION mTables[ALEN(mlist,1)]
   FOR i=1 TO ALEN(mlist,1)
      mTables[i]=mlist[i,1]
   NEXT
   
   FOR EACH lcTable IN mTables
      WAIT WINDOW NOWAIT "Copying " + lcTable
      lctable = strt(LOWER(lctable),'.dbf','')
      msql = mysqltablestruct( lctable, lctable )
      STRTOFILE(msql+CHR(13)+CHR(10),'mysqltable.sql', .t. )

      IF 0> SQLEXEC(liHandle , msql )      &&--產生後端資料庫的table
         ? '無法產生mysql table'
         susp
      endif
      USE (lcTable) IN 0 ALIAS VFPData

      lcSQL = [select * from ] + lcTable + [ where 1=0]
      IF SQLEXEC(liHandle,lcSQL,lcTable) = 1 THEN && got the cursor...now make it updatable
         IF makeupdatable(lcTable) THEN
            * Now cycle through the vfp data and insert into MySQL recordset
            SELECT VFPData
            SCAN
               WAIT WINDOW NOWAIT "Copying " + lcTable + " (" + TRANSFORM(RECNO()) + " of " + TRANSFORM(RECCOUNT()) + ")"
               SCATTER MEMO NAME loRec
               INSERT INTO (lcTable) FROM NAME loRec
               SELECT (lctable)
               FOR i=1 TO FCOUNT(lctable)
                  mfield=FIELD(i,lctable)
                  msfield = 'vfpdata.'+mfield
                  IF LOWER(LEFT(mfield,3))= LOWER(chgReservedWord)      &&--有用到保留字,會先[上 f_
                     msfield = 'vfpdata.'+SUBSTR(mfield,3)
                  endif
                  IF TYPE(mfield) $ 'CM' AND tranunicode
                     *轉unicode
                     IF TYPE(msfield)<>'U'
                        REPLACE &mfield WITH STRCONV(&msfield,9)
                     ENDIF
                  ELSE
                     IF LOWER(LEFT(mfield,3))=LOWER(chgReservedWord) .and. TYPE(msfield)<>'U'
                        REPLACE &mfield WITH &msfield
                     ENDIF
                  ENDIF
               next
            ENDSCAN

            * Now update the cursor
            llSuccess = TABLEUPDATE(2,.T.,lcTable)            
            IF NOT llSuccess THEN
               ? 'error tableupdate:',lctable,RECCOUNT()
               *AERROR(laError)
               *lcMsg = laError(2)
               *SET STEP on
               *MESSAGEBOX("Unable to save changes to " + lcTable + CHR(13) + CHR(13) + TRANSFORM(lcMsg),16,"Problem")
            ENDIF
            
         ENDIF
      ELSE
         MESSAGEBOX("Problem getting cursor " + lcTable,16,"Problem")
      ENDIF

      * close table/cursor
      USE IN (SELECT("VFPData"))
      USE IN (SELECT(lcTable))
   ENDFOR
ELSE
   MESSAGEBOX("Problem getting handle",16,"Problem")
ENDIF && liHandle > 0

***************************************
PROCEDURE makeupdatable

* makeupdatable.prg       - Make a SQL Cursor updatable
* 5/20/2002, Paul McNett

* This is a simple example, hacked together quickly based on MS KB articles, Profox
* discussions, etc.  Anyone may use this for any purpose whatsoever!

* So you have an alias as the result of a SPT call.  Great!  Now lets make it updateable:

* lSuccess = makeupdatable(cBackEndTableName [, cAlias [, nBuffering [, cKeyFieldList [, cUpdateNameList]]]])

* After you've modified the alias, you will need to use tableupdate() to update the backend as usual:
*   lSuccess = tableupdate(2, .T., m.cAlias), for example

* Parms:
*         cBackEndTableName : Name of table on server (required)
*         cAlias            : Name of VFP alias (optional, default to currently selected alias)
*         nBuffering        : The buffering mode.  Optional, defaults to DEFAULT_BUFFERING
*         cKeyFieldList     : The key field(s).  Optional, defaults to DEFAULT_KEYFIELDLIST
*         cUpdateNameList   : The fields to update.  Optional.  Defaults to allowing updates
*                             to all fields in the alias, and assuming that the field names in
*                             the alias match the field names on the server.

* Requires that you have a connection open to the database that contains the backend table
* you want to make updatable.

* I use this all the time for manually editing interactively.  My command window session looks something like:
*    ihandle = sqlconnect(...)
*    ? sqlexec(m.ihandle, "select * from apinvoice where length(cstatus) = 0", "temp")
*    do makeupdatable with "apinvoice"
*    browse
*    ? tableupdate(2, .t., "temp")

* I don't use this in production work, although it could probably be relied upon.  For production
* work, I create dynamic views at runtime and make them updateable based on fields in the dynamic
* view dictionary.

* If anyone can figure out how to get the backend table name so we don't need to require it as a parameter,
* that would be great.  As far as I can see, it doesn't exist in any of the SQLGETPROP() information. 

LPARAMETERS cBackEndTableName, cAlias, nBuffering, cKeyFieldList, cUpdateNameList, cUpdatableFieldList
LOCAL ncount, cfield


IF VARTYPE(m.cAlias) == "C" AND !EMPTY(m.cAlias) AND USED(m.calias)
  cAlias = m.cAlias
ELSE
  cAlias = ALIAS()
ENDIF

IF EMPTY(m.calias)
  ? "Invalid alias."
  RETURN .f.
ELSE
  calias = ALLTRIM(m.calias)
ENDIF


IF VARTYPE(m.cbackendtablename) <> "C" OR EMPTY(m.cbackendtablename)
  ? "You need to send the name of the backend table."
  RETURN .F.
ELSE
  cbackendtablename = ALLTRIM(m.cbackendtablename)
ENDIF

IF VARTYPE(m.nBuffering) <> "N" OR m.nbuffering < 0
  nBuffering = DEFAULT_BUFFERING
ENDIF

IF VARTYPE(m.cKeyFieldList) <> "C" OR EMPTY(m.cKeyFieldList)
  cKeyFieldList = DEFAULT_KEYFIELDLIST
ENDIF

IF VARTYPE(m.cUpdateNameList) <> "C" OR EMPTY(m.cUpdateNameList)
  * create UpdateNameList based on fields in m.cAlias
  cUpdateNameList = ""
 
  FOR ncount = 1 to fcount(m.calias)
    cfield = LOWER(ALLTRIM(FIELD(m.ncount, m.calias)))
   
    cupdatenamelist = m.cupdatenamelist + IIF(EMPTY(m.cupdatenamelist), "", ", ") ;
                    + m.cfield ;
                    + " " + m.cbackendtablename + "." ;
                    + m.cfield
  ENDFOR
 
ENDIF


IF VARTYPE(m.cUpdatableFieldList) <> "C" OR EMPTY(m.cUpdatableFieldList)
  * create UpdatableFieldList based on fields in m.cAlias
  cUpdatableFieldList = ""
 
  FOR ncount = 1 to fcount(m.calias)
    cfield = LOWER(ALLTRIM(FIELD(m.ncount, m.calias)))
   
    cupdatablefieldlist = m.cupdatablefieldlist + IIF(EMPTY(m.cupdatablefieldlist), "", ", ") ;
                        + m.cfield
  ENDFOR
 
ENDIF


IF VERBOSE == .T.
  ? "BackEndTableName:       ", m.cBackEndTableName
  ? "Alias:             ", m.cAlias
  ? "Buffering:          ", m.nBuffering
  ? "KeyFieldList:          ", m.cKeyFieldList
  ? "UpdateNameList:       ", m.cUpdateNameList
  ? "UpdatableFieldList:    ", m.cUpdatableFieldList 
ENDIF

CURSORSETPROP("wheretype", 1, m.cAlias)
CURSORSETPROP("tables", m.cBackendTableName, m.cAlias)
CURSORSETPROP("updatenamelist", m.cUpdateNameList, m.cAlias)
CURSORSETPROP("keyfieldlist", m.cKeyFieldList, m.cAlias)
CURSORSETPROP("updatablefieldlist", m.cUpdatableFieldList, m.cAlias)
CURSORSETPROP("sendupdates", .T., m.cAlias)

CURSORSETPROP("Buffering", m.nBuffering, m.cAlias)

***************************
PROCEDURE mysqltablestruct
LPARAMETERS mtable , mtablename
SET SAFETY off
LOCAL msele
msele=SELECT()
IF !USED(mtable)
   SELECT 0
   USE (mtable)
   COPY STRUCTURE EXTENDED TO zchkstru

ELSE
   SELECT (mtable)
   COPY STRUCTURE EXTENDED TO zchkstru
   SELECT 0

ENDIF
USE zchkstru
LOCAL msql
msql = [CREATE TABLE IF NOT EXISTs ]+mtablename+[ (]
msql = msql+ DEFAULT_KEYFIELDLIST +[ INT(11) NOT NULL auto_increment,]      &&-- pk 為更新用的主鍵值.

SCAN
   msql = msql+ LOWER(ALLTRIM(mysqlfield_name(field_name)))+' '+mysqlfield_type( field_type, field_len , field_dec)+','
ENDSCAN
USE
DELETE FILE zchkstru.*
SELECT (msele)

*msql = LEFT(msql , LEN(msql)-1)
msql = msql +[PRIMARY KEY (]+DEFAULT_KEYFIELDLIST+[)]      &&--[PRIMARY KEY (pk)]
msql = msql +[)  engine=myISAM AUTO_INCREMENT=1]
? msql
RETURN msql
**************************************
PROCEDURE mysqlfield_type
LPARAMETERS mfield_type, mfield_len, mfield_dec
mfield_type = UPPER(mfield_type)
local mret
mret =''
DO case
CASE mfield_type='C'
   *mret='varchar('+TRANSFORM(mfield_len)+')'
   mret='varchar('+TRANSFORM(MIN(255,IIF(mfield_len>=6,CEILING(mfield_len*1.3),mfield_len)))+')'
   *]為放unicode它的中文字碼是3byte,比BIG5的2byte多一點, 所以長度要多一點
CASE mfield_type='N'
   IF mfield_dec>0
      IF mfield_len-mfield_dec>=14
         mret='double('+TRANSFORM(mfield_len)+','+TRANSFORM(mfield_dec)+')'
      else
         mret='float('+TRANSFORM(mfield_len)+','+TRANSFORM(mfield_dec)+')'
      endif
   ELSE
      DO case
      case mfield_len<=2
         mret = 'tinyint'
      case mfield_len<=4
         mret = 'smallint'
      other
         mret = 'int'
      endcase
   endif
CASE mfield_type='I'
   mret = 'int'
CASE mfield_type='D'
   mret = 'date'
CASE mfield_type='T'
   mret = 'datetime'
CASE mfield_type='M'
   mret = 'text'
CASE mfield_type='L'
   mret = 'bit'
CASE mfield_type='G'
   mret = 'blob'
ENDCASE
RETURN mret
***********************
PROCEDURE mysqlfield_name      &&--檢查是否有mysql保留字, 有要[上 f_
LPARAMETERS mfield_name
IF TYPE('mysqlword')<>'C'
   defword()   
ENDIF
LOCAL mret
mret = mfield_name
IF ASCAN(mword_list, mfield_name ,1,ALEN(mword_list),1,1+2+4)>0
   mret = chgReservedWord + field_name
ENDIF
RETURN mret
*****************************
PROCEDURE defword      &&--mysql 保留字
RELEASE mysqlword
PUBLIC mysqlword, mword_list[1]

TEXT TO mysqlword
ADD
ALL
ALTER
ANALYZE
AND
AS
ASC
AUTO_INCREMENT
BDB
BEFORE
BERKELEYDB
BETWEEN
BIGINT
BINARY
BLOB
BOTH
BTREE
BY
CASCADE
CASE
CHANGE
CHAR
CHARACTER
CHECK
COLLATE
COLUMN
COLUMNS
CONSTRAINT
CREATE
CROSS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATABASE
DATABASES
DAY_HOUR
DAY_MINUTE
DAY_SECOND
DEC
DECIMAL
DEFAULT
DELAYED
DELETE
DESC
DESCRIBE
DISTINCT
DISTINCTROW
DIV
DOUBLE
DROP
ELSE
ENCLOSED
ERRORS
ESCAPED
EXISTS
EXPLAIN
FALSE
FIELDS
FLOAT
FOR
FORCE
FOREIGN
FROM
FULLTEXT
FUNCTION
GRANT
GROUP
HASH
HAVING
HIGH_PRIORITY
HOUR_MINUTE
HOUR_SECOND
IF
IGNORE
IN
INDEX
INFILE
INNER
INNODB
INSERT
INT
INTEGER
INTERVAL
INTO
IS
JOIN
KEY
KEYS
KILL
LEADING
LEAVE
LEFT
LIKE
LIMIT
LINES
LOAD
LOCALTIME
LOCALTIMESTAMP
LOCK
LONG
LONGBLOB
LONGTEXT
LOW_PRIORITY
MASTER_SERVER_ID
MATCH
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
MIDDLEINT
MINUTE_SECOND
MOD
MRG_MYISAM
NATURAL
NOT
NULL
NUMERIC
ON
OPTIMIZE
OPTION
OPTIONALLY
OR
ORDER
OUTER
OUTFILE
PRECISION
PRIMARY
PRIVILEGES
PROCEDURE
PURGE
READ
REAL
REFERENCES
REGEXP
RENAME
REPLACE
REQUIRE
RESTRICT
RETURNS
REVOKE
RIGHT
RLIKE
RTREE
SELECT
SET
SHOW
SMALLINT
SOME
SONAME
SPATIAL
SQL_BIG_RESULT
SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT
SSL
STARTING
STRAIGHT_JOIN
STRIPED
TABLE
TABLES
TERMINATED
THEN
TINYBLOB
TINYINT
TINYTEXT
TO
TRAILING
TRUE
TYPES
UNION
UNIQUE
UNLOCK
UNSIGNED
UPDATE
USAGE
USE
USER_RESOURCES
USING
VALUES
VARBINARY
VARCHAR
VARCHARACTER
VARYING
WARNINGS
WHEN
WHERE
WITH
WRITE
XOR
YEAR_MONTH
ZEROFILL
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP
ENDTEXT

ALINES(mword_list,mysqlword)

_________________
利用>>搜尋<<的功能會比問的還要快得到答案.
回頂端
檢視會員個人資料 發送私人訊息 發送電子郵件
從之前的文章開始顯示:   
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> VFP 討論區 所有的時間均為 台北時間 (GMT + 8 小時)
1頁(共1頁)

 
前往:  
無法 在這個版面發表文章
無法 在這個版面回覆文章
無法 在這個版面編輯文章
無法 在這個版面刪除文章
無法 在這個版面進行投票
無法 在這個版面附加檔案
無法 在這個版面下載檔案


Powered by phpBB © 2001, 2005 phpBB Group
正體中文語系由 phpbb-tw 維護製作