Looking for guidance - Non ODBC App export to MySQL 1

[josel]

Howdy!

I have never used MySQL and have been putting it off for God knows how long (big mistake). I am now faced with the
need to "literally" mirror my home grown application on MySQL.

I am currently using SCO Unix but will be migrating to SuSE 9.1 in the near future. I use a character based application and I can do just about anything one can think of but it has its limitations and the BIGGEST one is that it is not ODBC compliant (client nor server). I have been pushing flat files (or CSV files) to MS SQL. I want to stop this and mirror my application data in MySQL.

I figure I can continue to dump my records to flat files and load them to MySQL.

All that said, I hope to find if:

1) Available tools I can use to help load and process my flat files

2) Available tools I can use to manage MySQL via GUI in lieu of text (for Linux SuSE 9.1)

3) Could I use a cron process to monitor for flat files and trigger action to load said file(s) to MySQL

I am sure I have hundreds of questions but an answer to this post should get me started and thus allow me to ask more to the point questions.

Thank you all in advance!


Jose Lerebours


KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[TonyGroves]

MySQL has native API's for several languages (C, C++, Perl, Java, PHP, Python, etc) that don't use ODBC, but access the database directly, so there should be no need to use text files as an interface. It sounds like a really awkward, unreliable, and inefficient way of doing things.

However, you probably have little choice at the moment, so to load data into a table from a text file, you could use something like:
[tt] mysql -u username -ppassword -h hostname -e "LOAD DATA INFILE 'filepath' INTO TABLE dbname.tablename"[/tt]
The mysqlimport program can also be used, but it's not as flexible as the preceding syntax. Like any other commands, commands like this can be wrapped in scripts run by cron. If you need help with that, you could try the shell-scripting forum.

The download pages at

http://www.mysql.com

have MySQLAdmin, a database administration program which runs under X. I think there is a SuSE rpm available.

 

[josel]

This is great TonyGroves!

I will be playing with this first thing tomorrow morning. I'll love to see, what I now feel should be pretty simple, how I can mirror my files "virtually live".

Thanks a lot !!!!


Jose Lerebours


KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[josel]

TonyGroves,

I have a file

   10108:03/11/2005:   :: : :   :: : : : : : : :ARG:

How do I tell command you posted

mysql -u username -ppassword -h hostname -e "LOAD DATA INFILE 'filepath' INTO TABLE dbname.tablename"

that the colon are field separators ...

I mean, how does MySQL know the begining and the end of a field?

Thanks;


Jose Lerebours

KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[TonyGroves]

The syntax of the LOAD DATA command is covered in the MySQL manual, at

http://dev.mysql.com/doc/mysql/en/load-data.html

. You would use the[tt] FIELDS TERMINATED BY ':' [/tt]option.

One thing though: The date field in your sample record will not load correctly into a MySQL DATE field because MySQL expects dates to be in the format "yyyy-mm-dd". You would have to load it into a string field, then later convert it into a date.

Also, the "mysql" shell command can be used to execute any MySQL commands, not just LOAD DATA, so if your programming language supports shell-command execution, that would be a way to access the database directly instead of going through the text-file routine.