To query, or not to query, that is the question

database

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).

Mathcad and Databases

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.

Mathcad and Databases

 

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.

Download ODBCread/write

Try it out in PTC Mathcad Express Now

 

Quick Links

PTC Mathcad Express

Free for life version

PTC Mathcad Features

90 second videos

Resources

Training, live demos, free worksheets

This entry was posted in Mathcad, The Inside Scoop, Tips & Tricks and tagged , , , , . Bookmark the permalink.

2 thoughts on “To query, or not to query, that is the question”

  1. Stuart Bruff says:

    A potentially useful extension to Mathcad!

    However, as both Mathcad and Prime stand, they are effectively limited to handling 2-dimensional tables whereas much data is multidimensional. Although Mathcad and Prime have the nested array data structure, they provide little effective support for their use, eg the clumsy indexing system for gaining access to elements inside a nested array. What is needed to make effective use of such data is a data type and set of operations (eg, slicing, concatenation, aggregation) that natively supports multidimensional arrays (MDAs).

    Stuart

    1. John Sheehan says:

      Couldn’t agree more Stuart. In a former life I did OLAP and everything was in a cube. I also agree that nested matrices is a poor substitution. Many of our customers are still using RDBMs. I think you are implying 3D databases are growing in popularity ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s