Converting VistA Cache DB to SQL Server DB

[KoPa]

Hello,

I'm quite new with VistA and I would appreciate if I could get some
help or hints on converting Cache to SQL Server.
Has anyone done this? Is there any application which can handle this
automatically?

Thank you in advance and looking forward to hearing from you.

Best regards,
KoPa

[Gregory Woodhouse]

It can be done, but your best bet is to treat it as a (partially) manual process with tool support. Other have tried to do this but have either relied too much on generated DDL or paid insufficient attention to the performance implications of the way the database tends to be used in VISTA. if you simply try to swap out Fileman and replace it with something like SQL Server without doing any reengineering along the way, you may not succeed. A good starting point is the SQLI, a tool that generates a relational mapping from the data dictionary.

Sent from my iPhone

> --
> http://groups.google.com/group/Hardhats
> To unsubscribe, send email to Hardhats+u...@googlegroups.com

[kdt...@gmail.com]

Anything is possible. It is always just a simple matter of
programming, right?

But this job would be a very hard job. I would look for another
solution.

Kevin

[KoPa]

Thank you for your time and replies.
So, it seem like there is no easy and fast solution which can do this
work without programming.
May be I should consider other solutions, as right now I can not
afford digging in deep and programming the whole thing.
Thanks again,

[Ben Mehling]

Hi there-

On Tue, Mar 15, 2011 at 2:59 AM, KoPa <kosta....@gmail.com> wrote:

> I'm quite new with VistA and I would appreciate if I could get some
> help or hints on converting Cache to SQL Server.

Cache is not simply a DBMS. Cache (and GT.M) provide both the
database and application execution environments. SQLServer is only a
DBMS. MUMPS-based applications are very tightly integrated with both
their data access and their application environment. SQLServer
(postgres, mysql, oracle, for that matter) are not viable alternatives
to Cache. GT.M is though.

On Wed, Mar 16, 2011 at 6:49 AM, Nancy Anthracite
<nanth...@earthlink.net> wrote:

> And you can access it with SQL queries if you want using the solution from
> Medsphere or others.

The FM Projection tool is a read-only SQL projection of the FileMan
structures into a format that can be queried using SQL syntax (and
tools compatible with MySQL). It does not convert, store, copy, or
synchronize data. It simply projects it in real time.

FM Projection: https://medsphere.org/community/project/fm-projection
Demo of it's capabilities (skip ahead, past OVID, to: 49:40):
https://medsphere.org/blogs/events/2010/06/09/vista-community-meeting-day-2

Thanks, Ben

[Steven McPhelan]

Depending upon the version of Cache that you are using, Cache has a Fileman to SQL feature.  Just search the documentation for Fileman.  I do not remember which version of Cache they introduced this feature.  Little to no programming is required to do it as Intersystems provides utilities to do this for you.

--
Steve
America does not go abroad in search of monsters to destroy. - John Quincy Adams

[Sam Habiel]

Kosta,

You need to tell us exactly what you want to do. I could think of so
many answers, many paralleling what was already mentioned. But I don't
want to keep typing.

Sam

[Ben Mehling]

On Wed, Mar 16, 2011 at 9:11 AM, Steven McPhelan
<smcp...@alumni.uci.edu> wrote:
> Depending upon the version of Cache that you are using, Cache has a Fileman
> to SQL feature.

This function is Cache is similar "projection" style technology, but
more mature than the project I referenced this morning.

I believe it's been renamed... something like FM2CLASS[1] now?

- Ben

[1] http://docs.intersystems.com/documentation/cache/20102/pdfs/AFILEMAN_Conversion.pdf

[David Whitten]

Ben,
I believe you are correct that this link is different than the FileMan
to SQL, as it seems to be
discussing FileMan conversion to classes, which I think is similar to
Medsphere's OVID
rather than Medsphere's FileMan projection.

The contrasting view is that there is mention that the mapping utility
enables access to file data via SQL.
and that there is mention of accessing FileMan data structures using
ODBC (which internally uses SQL)
There is also mention of verifying that you have SQL access to
generated classes.
There also seems to be a correspondence between "Property" and
"SqlFieldName" in one table.

So I guess overall, it looks like Cache has a way to map a FileMan
File to an Cache SQL Table and to a Cache Object Class, with a mapping
between the Table and Class. I also happen to know that there is a
binding between Cache Objects and Java Objects. So I guess this is a
combination of Medspheres OVID and Medsphere's FileMan Projection.

David

[Ben Mehling]

David-

Yes, I think you're correct. A few clarifications (based on my
understanding) below.

On Wed, Mar 16, 2011 at 12:21 PM, David Whitten <whi...@worldvista.org> wrote:

> I believe you are correct that this link is different than the FileMan
> to SQL, as it seems to be discussing FileMan conversion to classes,

Yes, that's my understanding from Intersystems. They basically map
the FM structures to (Cache) Classes. Any Cache Class can be
"projected" into a relational DB structure. They then provide an
ODBC/JDBC endpoint for accessing all projected Classes

> So I guess overall, it looks like Cache has a way to map a FileMan
> File to an Cache SQL Table and to a Cache Object Class, with a mapping
> between the Table and Class.

My understanding (again, this all "internals" to Cache) is that it's a
two part mapping: FileMan File -> Cache Class -> "SQL Table"
(projected class accessible via ODBC/JDBC)

Medsphere's FM Projection purposefully uses MySQL as an intermediary
(for example, all the query optimization, SQL syntax parsing, etc. is
done in MySQL). So we map the FileMan Files into an external MySQL
Database (using MySQL "storage engine" API).

MySQL simply views them as tables and operates on them accordingly. So
a user can then hook-up tools to MySQL's ODBC/JDBC endpoints if they
like or query using MySQL native tools. One other thing -- the
database (that contains projected FileMan Files) can contain native
MySQL tables or tables provided by any other MySQL "storage engine" as
well -- meaning you could query across data from multiple disparate
sources all within one tool. This can be quite powerful.

- Ben

[Nancy Anthracite]

Why is it that you want to do this in the first place?

Perhaps what you want to do has another solution. If you are looking to
replace Cache on a Windows system, for instance, you could run a virtual
machine on the Windows system that hosts VistA and GTM on Linux. It doesn't
get much cheaper than that.

And you can access it with SQL queries if you want using the solution from
Medsphere or others.

You can convert a Cache database to a GTM database with some work, but not a
huge amount. I would recommend you let an expert do it, but it might be
largely done within hours with some tire kicking needed after that.

--
Nancy Anthracite

[Michael Reach]

We have been using the Cache' Fileman to SQL converter, ConvertFiles^
%fm2cache, and it has been doing great. It takes only seconds to
convert all your Fileman files, it worked perfectly the first time for
us, and you can use an ODBC connection to access your SQL database
from ColdFusion or mySQL or whatever.

[skip ormsby]

Just a caution, if my memory is correct and that is somewhat debatable, is that some Free Text field do not map properly, because there is no length in the Input Transform.  For example, File: 50, Field: .01
^DD(50,.01,0)="GENERIC NAME^RFXa^^0;1^D ^PSSGENM"
notice the missing K:$L.. in the above example so it defaults to 30 instead of 40.  Here is an example with the "standard" K:$L...
^DD(4,.01,0)="NAME^RFX^^0;1^K:$L(X)>30!($L(X)<3)!'(X'?1P.E) X K:($P($G(^DIC(4,+$G(DA),0)),U,11)=""N"")&'$G(XUMF) X"

Now maybe the Cache routine has been changed an it looks also a the traditional "B" xref.

[KoPa]

Hello Sam,

The story goes like this:
I have a tool which can access only relational data. This tool reads
the database and retrieves the tables, relationships and fields.
Next, these information are used to auto generate SQL queries and
select data from the db. Can I do the same in Cache straight forward,
without converting it to SQL Server DB?
Now, I am reading on cache, its features and tools. I thought if I
could convert it to SQL Server DB, I don't need to change my tool in
accessing cache.

Thank you very much for your help and time.
Regards,
Kosta

On Mar 16, 6:42 pm, Sam Habiel <sam.hab...@gmail.com> wrote:
> Kosta,
>
> You need to tell us exactly what you want to do. I could think of so
> many answers, many paralleling what was already mentioned. But I don't
> want to keep typing.
>
> Sam
>
> On Wed, Mar 16, 2011 at 6:11 PM, Steven McPhelan
>

[KoPa]

Thank you Michael,

I will look into this. It seems a good solution for my case.

Thank you for your time.
Regards,
Kosta

[Sam Habiel]

Kosta,

It seems that you may do well with Medsphere's FM Projection.

I have tried using Cache's FM Projection back when I worked on RPMS in
IHS, and the results were very disappointing. I couldn't do any joins.
With Medsphere's FM Projection, I have had no issues doing relational
joins. I have tried Cache's projection only on RPMS; and Medsphere's
counterpart only on VISTA. I don't think they will differ; but that's
the extent of my experience anyways.

Kosta, I have to give you the usual warning that is standard issue:
- Data retrieved from VISTA through relational means is totally
unfiltered. DO NOT attempt to produce statistics from the system
unless you validate your queries against other tools or against manual
counting.

Sam