Technical and Installation Discussions

[scott123]

Hello all, I'm bumping this thread to ascertain if any better ways have come along for exporting from MAS to MySQL. I'm exporting every table. The whole enchilada- from MAS to MySQL. I've been using the SOTAMAS driver and a third party app called WinSQL to bring everything into CSV and then I have a script to take it from CSV to MySQL. The whole process is incredibly labor-intensive, and, considering I need to take a snapshot once a day, I really need to find a better way. I'm open to anything- third party tools, vb, VI (if I have to )... anything.

[nneul]

If you have Perl on your system, writing code to compare and sync the tables in MySQL should be relatively easy. (Can be done in other languages as well, I just happen to prefer perl.) Best approach depends significantly on your data set size of the tables that you need to keep replicated and what update latency is acceptable. (i.e. if you have 5000 orders in your system or 500,000 makes a big difference to the best approach to take.) Are you currently replicating the entire set of tables and views?

[scott123]

Yes, the entire set of tables and views. I'm not much of a Perl guy. I do know a little PHP. Can I do this in PHP? I know basic commands for interfacing with a mysql database with PHP. With ODBC, can I approach the providex database in a similar manner?

[nneul]

Yes, you should be able to connect from php just as easily as perl using the odbc database type.

[scott123]

Okay, just to clarify. Inside PHP, Can I use all the basic SQL commands (SELECT, DELETE, UPDATE, etc.) to work with an ODBC accessed providex database?

[nneul]

No, select / read-only queries only. If you get the read-write providex driver (not supported by sage) - you can do updates (never tried insert or delete), but it can be dangerous (especially if you do anything that violates content-integrity rules and does not work with all tables. (And good luck getting an answer as to which ones - my suspicion is that it works ok with any table that is part of the new business object framework, but not the older ones.) In the very narrow case where I use it, it is ONLY to update the value of a UDF or non-related field (such as volume, weight, description, etc.) on existing records. In any case, stick with selects from MAS and use particular VI jobs for specific updates as required.