VFP ·R¥ÎªÌªÀ°Ï ­º­¶ VFP ·R¥ÎªÌªÀ°Ï
¥»°Q½×°Ï¬° Visual Foxpro ·R¥ÎªÌ¸gÅç¥æ¬yªº¦a¤è, ½Ð¦h¦h§Q¥Î"·j´M"ªº¥\¯à, ¥ý¬d¬Ý¬Ý¦³µL«e¨Ò¥i´`, ¦pªGÁÙ¦³¤£À´ªº¦Aµo°Ý. ³¡¥÷¥DÃD¦³ªþ¥[ÀÉ®×, ¶·¥ýµù¥U¦¨¬°ªÀ°Ï©~¥Á¤~¥i¥H¤U¸ü.
 
 ±`¨£°ÝÃD±`¨£°ÝÃD   ·j´M·j´M   ·|­û¦Cªí·|­û¦Cªí   ·|­û¸s²Õ·|­û¸s²Õ   ·|­ûµù¥U·|­ûµù¥U 
 ­Ó¤H¸ê®Æ­Ó¤H¸ê®Æ   µn¤JÀˬd±zªº¨p¤H°T®§µn¤JÀˬd±zªº¨p¤H°T®§   µn¤Jµn¤J

VFP table upload to mysql

 
µoªí·s¥DÃD   ¦^ÂÐ¥DÃD    VFP ·R¥ÎªÌªÀ°Ï ­º­¶ -> VFP °Q½×°Ï
¤W¤@½g¥DÃD :: ¤U¤@½g¥DÃD  
µoªí¤H ¤º®e
garfield
Site Admin


µù¥U®É¶¡: 2003-01-30
¤å³¹: 2157


²Ä 1 ¼Ó

µoªíµoªí©ó: ¬P´Á¤­ ¤T¤ë 10, 2017 4:02 pm    ¤å³¹¥DÃD: VFP table upload to mysql ¤Þ¨¥¦^ÂÐ

*¥u»Ý­n¥ý«Ø¥ß mysql database ¤ÎÅv­­, ·|¦Û°Ê±N³o­Ó¸ê®Æ§¨©Ò¦³ªº dbf ¤W¶Ç¨ì mysql.
*³¡¥÷µ{¦¡½X¬O±Ä¥Îºô¸ô¤W§ä¨ì Paul McNett ªº¸ê®Æ
¥N½X:

* 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: ¦pªGmysql ªºtable¤£¦s¦b,·|¦Û°Ê«Ø¥ß, ·|²£¥Í¤@­Ómysqltable.sql ¨Ó°O¿ý²£¥ÍtableªºSQL«ü¥O
* 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 ¬O§_±N¤å¦rÄæ¦ìÂন unicode ==>¥u¾A¥Î©óvfp9
*chgReservedWord   ·í¹J¨ìÄæ¦ì¦WºÙ¸òmysql«O¯d¦r¤@¼Ë®É, ­n±N¥¦ªºÄæ¦ì¦WºÙ¥[¤W ¨º¨Ç¦r
*DEFAULT_KEYFIELDLIST   ²£¥Íªº mysql table ³£·|¦Û°Ê¥[¤W¤@­ÓÄæ¦ì pk ¥¦¬O 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¸ê®Æµ²ºc :'+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=¨Ï¥ÎªÌ¦WºÙ;PWD=±K½X;Stmt=set names utf8;charset=utf8;database=¸ê®Æ®w¦WºÙ;'
*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 )      &&--²£¥Í«áºÝ¸ê®Æ®wªºtable
         ? 'µLªk²£¥Í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)      &&--¦³¥Î¨ì«O¯d¦r,·|¥ý¥[¤W 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 ¬°§ó·s¥Îªº¥DÁä­È.

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¥¦ªº¤¤¤å¦r½X¬O3byte,¤ñBIG5ªº2byte¦h¤@ÂI, ©Ò¥Hªø«×­n¦h¤@ÂI
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      &&--Àˬd¬O§_¦³mysql«O¯d¦r, ¦³­n¥[¤W 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 «O¯d¦r
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)

_________________
§Q¥Î>>·j´M<<ªº¥\¯à·|¤ñ°ÝªºÁÙ­n§Ö±o¨ìµª®×.
¦^³»ºÝ
À˵ø·|­û­Ó¤H¸ê®Æ µo°e¨p¤H°T®§ µo°e¹q¤l¶l¥ó
±q¤§«eªº¤å³¹¶}©lÅã¥Ü:   
µoªí·s¥DÃD   ¦^ÂÐ¥DÃD    VFP ·R¥ÎªÌªÀ°Ï ­º­¶ -> VFP °Q½×°Ï ©Ò¦³ªº®É¶¡§¡¬° ¥x¥_®É¶¡ (GMT + 8 ¤p®É)
²Ä1­¶(¦@1­¶)

 
«e©¹:  
±z µLªk ¦b³o­Óª©­±µoªí¤å³¹
±z µLªk ¦b³o­Óª©­±¦^ÂФ峹
±z µLªk ¦b³o­Óª©­±½s¿è¤å³¹
±z µLªk ¦b³o­Óª©­±§R°£¤å³¹
±z µLªk ¦b³o­Óª©­±¶i¦æ§ë²¼
±z µLªk ¦b³o­Óª©­±ªþ¥[ÀÉ®×
±z µLªk ¦b³o­Óª©­±¤U¸üÀÉ®×


Powered by phpBB © 2001, 2005 phpBB Group
¥¿Å餤¤å»y¨t¥Ñ phpbb-tw ºûÅ@»s§@