|
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¸ü.
|
¤W¤@½g¥DÃD :: ¤U¤@½g¥DÃD |
µoªí¤H |
¤º®e |
garfield Site Admin
µù¥U®É¶¡: 2003-01-30 ¤å³¹: 2158
²Ä 1 ¼Ó
|
µ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¨ìµª®×. |
|
¦^³»ºÝ |
|
|
|
|
±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§@
|