Does anybody know how to query from MUMPS database using C# without using KBSQL -ODBC?
We have requirement to query from MUMPS database (Mckesson STAR Patient care) and when we use KBSQL it is limited for 6 concurrent users. So we are trying to query MUMPS directly without using KBSQL.
I am expecting something like LINQ TO MUMPS.
I think Mckesson uses Intersystems' Cache as its mumps (M) provider. Cache has support for .Net (see the documentation here). Jesse Liberty has a pretty good article on using C#, .Net and Windows Forms as the front end to a Cache database.
I'm not sure about LINQ (I'm no expert here), but this might give you an idea as to where to start to get your project done.
Michael
First off, I too feel your pain. I had the unfortunate experience of developing in MagicFS/Focus a couple of years back, and we had the exact same request for relational query support. Why do people always want what they can't have?
Anyway, if the version of MUMPS you're using is anything like MagicFS/Focus and you have access to the file system which holds the "database" (flat files), then one possible avenue is:
Export the flat files to XML files. To do this, you'll have to manually emit the XML from the flat files using MUMPS or your language of choice. As painful as this sounds, MUMPS may be the way to go since you may not want to determine the current record manually.
Read in the XML using LINQ to XML
Run LINQ queries.
Granted, the first step is easier said than done, and may even be more difficult if you're trying to build the XML files on the fly. A variant to this would be to manage generation of the XML files like indexes, via a nightly server process or the like.
If you're only going to query in specific ways (i.e. I want to join Foo and Bar tables on ID, and that's all I want), I would consider instead pulling and caching that data into server-side C# collections, and skip querying altogether (or pull those across using WCF or like and then do your LINQ queries).
You can avoid the 6 user limitation by separating the database connection from the application instances
Use the KB/SQL ODBC thru a middle tier ( either a DAL in your application) or a separate service (windows service ).
This component can talk to the MUMPS database using at most 6 separate threads ( in line with the KB/SQL limitation).
The component can use ADO.NET for ODBC to communicate with the KBSQL ODBC driver. You can then consume the data from your application using LINQ for ADO.NET.
You may need to use a queuing system like MSMQ to manage queuing of the data requests. if the 6 concurrent connections are insufficient for the volume of requests. It is a good design practice to queue requests and use LINQ asynchronous calls to avoid blocking user interaction.
All current MUMPS language implementations have the ability to specify MUMPS programs that respond to a TCP/IP connection. The native MUMPS database is structured as a hierarchy of ordered multi-key and value pairs, essentially a superset of the NoSQL paradigm.
KB/SQL is a group of programs that respond to SQL/ODBC queries, translate them into this MUMPS "global" data queries, retrieve and consolidate the results from MUMPS, and then send back the data in the form that the SQL/ODBC protocol expects.
If you have the permissions/security authorization for your implementation that allows you to create and run MUMPS programs (called "routines"), then you can respond to any protocol you desire from the those programs. MUMPS systems can produce text or binary results on a TCP/IP port, or a host operating system file. Many vendors explicitly keep you from doing this in their contracts to provide healthcare and financial solutions.
To my knowledge the LINQ syntax is a proprietary Microsoft product, although there are certainly LINQ-like Open Source efforts out there. I have not seen any formal definition of a line protocol for LINQ, but if there is one, a MUMPS routine can be written to communicate using that protocol. It would have to do something similar to KB/SQL however, since neither the LINQ syntax nor the SQL syntax are very close to the native MUMPS syntax.
The MUMPS data structuring and storage mechanism can be mechanically translated into the an XML syntax. This may still require an extensive effort as it is highly unlikely that the vendor of your system will provide a DTD defined for this mechanically created XML syntax, and you will still have to deal with encoded values and references which are stored in the MUMPS based system in their raw form.
What vendor and version of MUMPS are you using? The solution will undoubtedly be dependent on the vendor's api they have exposed.
