filePro to MySQL

Lerebours, Jose Jose.Lerebours at EagleGL.com
Tue Nov 15 12:10:46 PST 2005


Simple, simply use a script with following

#LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
#    [REPLACE | IGNORE]
#    INTO TABLE tbl_name
#    [FIELDS
#        [TERMINATED BY '\t']
#        [[OPTIONALLY] ENCLOSED BY '']
#        [ESCAPED BY '\\' ]
#    ]
#    [LINES
#        [STARTING BY '']
#        [TERMINATED BY '\n']
#    ]
#    [IGNORE number LINES]
#    [(col_name,...)]
#
# For more information on this command, see
# http://dev.mysql.com/doc/mysql/en/load-data.html
#

This is a working sample:

if [ $# -eq 3 ]
then

mysql -u filepro -pfilePro -e "LOAD DATA INFILE '$3'
        REPLACE
        INTO TABLE $1.$2
        FIELDS TERMINATED BY '~'
        ESCAPED BY '\\\\'
        LINES TERMINATED BY '\n';"

else

echo "

Usage:  putMySQL "database" "table_to_write_to" "data_source"


"
fi


If you visit the site listed above, you can learn more about
this and many other commands.

So, as you can see, writing to MySQL from filePro is a 1,2,3
sort of thing.  All you need to do is produce the file with
data to be loaded and execute a script similar to the one
above.  Of course, this script is in its simplest form.  I
suggest you read through the above listed URL pages and see
what other things this practice has to offer.

As far as updating filePro from MySQL, it really is subject
to what tools you use.  PHP, CFML, CGI, ASP, ... all have
the means to create text files and automatically transfer
these files to a repository where your 'cron' can pick them
up and import them to your filePro files.  


And if you want, you can even create MySQL tables directly
from filePro using:

mysql -u filepro -pfilePro -D MyfpDataBase -e "CREATE TABLE \`MyfpTable\` (
\`field_01\` VARCHAR( 10 ) ,
\`field_02\` DECIMAL( 9, 2 ) DEFAULT '0.00' NOT NULL ,
\`field_03\` VARCHAR( 10 ) NOT NULL ,
\`field_04\` VARCHAR( 10 ) NOT NULL ,
\`field_05\` VARCHAR( 10 ) ,
PRIMARY KEY ( \`field_03\` ) ,
INDEX ( \`field_04\` ) ,
UNIQUE (
\`field_01\`
)
) COMMENT = 'Created from filePro';"


So, given these two MySQL commands and simple scripts, you 
should now have enough to play around and see how easy it
really is to create MySQL tables matching every one of your
filePro files and keep these tables nicely fat with your
filePro data as well.


Regards;


--
Jose Lerebours
Principal Software Engineer
Eagle Global Logistics
1801 NW 82nd Avenue
Miami, FL 33126
Voice: (305) 594-0038 Ext 7556
Fax #: (305) 593-0431
eMail: Jose.Lerebours at eaglegl.com
http://www.eaglegl.com


More information about the Filepro-list mailing list