I'm a relatively new user at StackOverflow, so my apologies if I violated any rules in my post.
Here's some quick background to set the context for my question. I work as a quality analyst for a manufacturing company that stores all pertinent information about products, purchase orders, reject information, etc. in a database managed using Sage ERP X3 V6.5.
I would like to create an app that will allow us to analyze reject information in a more powerful way than is currently available. Currently, we are forced to make database connections through the Sage ERP software. I am far more comfortable with Python and feel it is a more powerful way to explore this dataset.
I am not extremely experienced in working with databases, so I have asked a higher up to setup a user with read-only access to our database through an ODBC connection. He has been fairly vague with his reasoning, but he seems to think it is not really possible to do this.
This leads me to my question: is it possible to create a user (through Sage ERP or some other medium) that has general read-only access to all of our databases? As I'm sure you can tell, I don't know a lot of the terminology for a question like this, so I'm happy to expand upon my post or answer any questions from people who respond to the best of my ability.
Just saw this. Apologies for the 9 month delay. You may have already figured this out or found a way around it, but here it is for the next person who has the same question:
Yes, it's not only possible, but quite easy. It's a simple ODBC connection to the SOTAMAS90 DSN. Here is your connection string:
"DSN=SOTAMAS90; UID=myUserId; PWD=myPassword, Company=XXX"
Just make sure that myUserId has the correct permissions (you set that up in User/Role maintenance in the Library Master module). Then use regular ODBC methods to read the data. It's automatically a read-only database.
Note that by default, only a 32-bit ODBC driver is installed when you install Sage 100. The 64-bit version can be found in the WKSetup directory in your Sage install folder.
Also, unfortunately, it's a bit on the slow side. Since Sage 100 uses its own proprietary file-based data storage mechanism and not a relational database to store it's data, it doesn't really have access to non-clustered indexes. That makes every WHERE clause basically a full-table scan. What I've done to deal with this issue in the past, when performance is an issue, is create a nightly ETL job to copy all of the MAS tables that I need into a RDBMS like SQL Server or MySql, then create indexes on all foreign keys and columns I need to search by. If you need the data more up to date than every 24 hours, you can program user-defined scripts against the MAS tables, which handle events like writes, deletes, and updates. In those scripts you can connect to your SQL database and create/update/delete the necessary record.
Of course, depending on your Sage version and installation options, there can be over 1,000 tables, so choose wisely.
Good luck out there.
Aaron