filePro to MySQL

[Lerebours, Jose]

Tim posted:

> Lerebours, Jose wrote:
> 
> >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.
> >
> >

> But wouldn't this recreate the entire table each and every 
> time?  We're 
> using files with over 400,000 records in it, so it wouldn't 
> really work 
> too well.  The way we do it updates individual rows in the 
> MySQL table - 
> could this be altered to do the same?  (It looks like it could be.)
> 

Actually, it does as many records as are held in the source file.
You can have 1 or 400,000.  Notice that using REPLACE means that
if the UNIQUE key already exists, said record will be replaced.
And of course, if key is not there, a new record will be added.

You have the option to queue today's work and process it all at
once.  You also have the option to create a single dynamic file
per record and process that one file with that one record using
'system' command.  The latter is very attractive as your data
is posted to MySQL just as it is recorded in filePro thus making
it a LIVE update - In fact, MySQL is updated first since you have
not reached an 'end' command in filePro (provided that you have
not written to disk).  In fact, this is just as good as fpTech's
own SQL based version (if only to write to MySQL).

With time and creativity, you can run queries on MySQL directly
from filePro.  Why not?  You can run a stored procedure right
from the command line and have it produce anything you need. 
With that in mind, one should be able to run these procedures
from filePro and then deal with the produced output.  I am no
expert in filePro nor MySQL, but I am sure that if you take
this to those known to be, they can help you pave your way
in and around this concept.  Perhaps, with enough interest,
Mark can put together a tool-kit to get the job done relatively
easy (if only for the final user).

Regards;


Jose Lerebours