MAS90 ODBC Tool
Friday, February 26, 2010 1:10:26 PM
I've been expanding my support offerings to an old client as of late into the realm of Sage Software's MAS90. It started off with a redesign of their website and the addition of customer data needing to be uploaded regularly to the web site's database from their internal MAS90 installation.
They are currently run MAS90 using the ProviderX ODBC connections. I haven't worked out all of the nomenclature and inner workings yet, so forgive me if I misspeak here.
Not knowing anything about MAS90 when I started I had to leap over a few hurdles to get .Net applications to talk to the database, run queries and tell me the schema of the tables. There may be an easier way of doing this but I was unable to find one, so I broke out some .Net ODBC tutorials, what a mess.
After constantly rewriting and recompiling my test applications to test the different queries I finally got sick of it and wrote a command line interface (CLI) for interacting with the MAS90 database.
Once configured it allows you to throw ad-hoc queries at the database and, if you choose, see the schema. It is a very rough cut but I hope that it helps somebody else out. If not, it has done and will continue to do what I need it to do.
You can download the application here
From the readme:
This is the initial release of my MAS90 CLI. This was developed for testing queries against MAS90 for a customer so that I could learn the internal structure of the MAS90 database tables and extract sample data from them.
Open MAS90CLI.exe.config and setup your connection string, such as server name and path to your ProviderX libraries
You can also set default values in the configuration file
run MAS90CLI.exe it should connect successfully and put you at a MAS90> prompt.
set [variable] = [value]
set showschema = true
set recordlimit = 30
timeout - timeout in seconds for the query to run (0+)
recordlimit - number of records to return before quitting (1+, 0 = all)
showschema - prints out the schema for debugging purposes (true/false)
Running Queries: At the MAS90> prompt type in your SQL query. You may need to reconfigure your console window to fit the data
MAS90> SELECT * FROM CUS_Invoices ORDER BY InvoiceDate DESC
Queries use the standard SQL syntax as allowable via the ProviderX ODBC provider.
You should start seeing the data appear followed by the number of records found and the time the query took to run
From the MAS90> prompt type in 'quit' or 'exit'
If you have any questions or problems feel free to contact me:
Ryan T. Hilton
Pacific NW Data Consultants
http://PNWDC.com - http://RTHilton.com