Whether ‘tis nobler to read data from flat files and Excel or to take arms against a sea of IT objections and demand access to the latest, centralized data. Prince Hamlet actually had it easier.
It is critical that our calculations are computing using the most current and accurate data. Yet every day I hear from engineers why they can’t get all the data they wanted or the latest data, or only in this file format, on this machine, on this network and only when I am in the building.
Databases have put many of these hurdles to rest. The database vendors have become an IT dept.’s best friend, building in security, redundancy and failover strategies. Oracle, IBM, SAP and Microsoft are the vendors of choice. These are not mom and pop vendors. And they have all come together to support SQL as a common language and ODBC as common access method. There are too many SQL and ODBC references for me to list. You will not have trouble finding primers for both if you wish.
What we’d like to discuss are two new modules for PTC Mathcad Prime: ODBCread and ODBCwrite. ODBCread (ConnectString, QueryString) takes a connect string and a query string. On 64 bit platforms ODBCread takes an additional string, which is your Custom Functions directory. We’ll talk about this later. The ConnectString says what database we are hitting, what flavor and is there any logon info. Alternatively (and much simpler) you can pass a Data Source Name (DSN). The second argument is your QueryString, usually a SELECT command, stating what columns, from what tables do you want to retrieve. This syntax is written in SQL language. In the download of the ODBCread/write EFIs there are worksheets with many examples of ConnectString and QueryString.
ODBCwrite (ConnectString, QueryString, mat) as you can see takes a ConnectString as discussed above and QueryString, also discussed above , but you will be doing INSERT SQL commands rather than SELECT commands.
Mathcad 15 allowed us to SELECT data from databases using the ODBC component. Users never had the ability to write to databases. This is new for Mathcad users. The data we write into the database is in the 3rd argument mat. It is important to note that ODBCwrite only writes numbers, no strings. There are examples of the INSERT syntax in the ODBCwrite example worksheet.
ODBCread example using long ConnectString (not DSN).
Explanation of the 3rd argument for 64 bit ODBCread. While many of us have 64 bit computers most of us are running MS Office 32 bit. You can see this by bringing up your task manager with MS Office products running. Below is my task manager running on Windows 7-64 bit OS.
OK and now another piece of technical trivia: 64 bit processes cannot load 32 bit DLLs just as 32 bit processes cannot load 64 bit DLLs. And this applies for DLLs on DLLs .. a 64 bit DLL such as a Custom Function build for Mathcad Prime 64 bit, cannot load a 32 bit DLL like a 32 bit ODBC DLL required to talk to MS Access *32. Here comes the work around. In the ODBCread/write 64 bit download you’ll notice dbread.exe and dbwrite.exe. These are 32 bit executables which use the 32 bit ODBC DLL afore mentioned. ODBCread/ODBCwrite for Mathcad Prime 64 bit creates external processes running dbread/dbwrite and these processes connect to the 32 bit database and pass data to and from ODBCread/write * 64. I know I am a clever clover🙂. So there you have it, why the Custom Functions directory argument.. to find where you put dbread/dbwrite.
These new modules are available to all active Global Support customers free of charge on the Engineering Resources page.
Try it out in PTC Mathcad Express Now
Free for life version
90 second videos
Training, live demos, free worksheets