filePro to MySQL

[Tim Fischer]

Excellent - that's the basic idea that we've done here - maybe we 
over-complicated things.  :)

Tim Fischer




Lerebours, Jose wrote:

>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
>
>_______________________________________________
>Filepro-list mailing list
>Filepro-list at lists.celestial.com
>http://mailman.celestial.com/mailman/listinfo/filepro-list
>
>
>  
>