Ported SOAPWare to MySQL [Archive]

mbenjam

09-28-2004, 05:30 PM

I am living proof that you don't need to be a "real" computer guru to run SOAPWare off a MySQL database. If anyone's interested, I'll post the (free) instructions here...

I am interested in developing "extensions" to SOAPWare using Visual Basic. Anyone else here working on this? It's 80% of a good EMR system, I figure it'd be not too expensive to work on the other 20%.

Mike

mel

09-28-2004, 05:38 PM

mbenjam

09-28-2004, 07:28 PM

Well, apparently there's a guy out there selling preconfigured systems running a MySQL backend. I'm not that fancy, but I can do the port.

http://www.premiernetworks.net/soapware.htm

He posted instructions on the SOAPware user forum a few months ago, and it's taken me this long to figure out exactly what to do.

SOAPWare is great because it's not exactly open source, but it's open database. The data structures are pretty straightforward. I wonder if other docs have tried to "hack" it to add new functionality...

I'll follow with details...I would be interested to hear Dr. Oates' take on this.

DOCS

09-28-2004, 08:52 PM

We are pleased to hear how docs have been innovative to make SOAPware work better for them. However, our concern is that end-user customizations often break when we release upgrades. We do not support computer code written by others. And, without the source code, the creative end-user can't always know what to fix when the upgrade breaks their innovations. Thus, we are exploring how we might be able to distribute source code in conjunction with a developer agreement. Perhaps if we share source code, developers will share their enhancements, and jointly, we can keep things synchronized? We are not promising, but this is our goal.
MySQL will be the default database in SOAPware v5. There are many sites using this with v4, but these are not supported by DOCS, Inc.

Randall Oates, M.D.
President, DOCS, Inc.

mbenjam

09-28-2004, 11:21 PM

Migrating SOAPWare to MySQL
If you don't know what ODBC, MySQL, or SQL is, don't do this. I can't be responsible for what happens to your setup if you try this. Database canoodling can bring grave heartache--I lost a PCR primer database when I "upgraded" a disk file system once.
This is provided as a reference only. I have only tried this out on a prototype system at home--not at the office yet! I don't want to lose my chemo order codes!
Benefits: no 2GB limit, no decision to buy $10K DBMS, potential for cluster DB, replication, no new hardware (necessarily) needed.

First step: back up your databases. There is a (good) chance this can break your installation. Try it out on a prototype system first.
Second step: install and run MySQL and MyODBC on the target system
Third step: run the migration tool on your databases. You can download a free one that works at http://www.kofler.cc/mysql. Use the vb script--it's pretty self explanatory. Read the opening comments--it tells you what to do. You're best off making .sql files, then sourcing those files into mysql. You need to modify the sw_images.sql file to include the line 'ALTER TABLE Reports_Binary max_rows=1000000 avg_row_length=200000;
USE sw_images;' after the 'CREATE TABLE sw_images.`Re....' statement. Use your favorite text editor.
Fourth: import the .sql files into mysql. I didn't have much luck with \.sw_images.sql from the mysql> prompt, but you can cut and paste into the mysql control center if you like. I didn't experiment with the 'source' command, but that might work too.
Fifth: create a user called 'soapuser' password 'soapware' in MySQL
Sixth: you need to delete the MSSQL ODBC DSN entries and create new ones for MySQL--re-create the same DSN entries using the same database names and DSN names.
Seventh: You should be good to go! Next step: replication, clustering, instant failover!

Good luck.
HTH,
Mike

srkatari

09-29-2004, 09:09 AM

I use Task manager of MSSQL to look at my data. I query and copy and paste to Excel to get some lists of my patients. I do not use my main server for this hacking. I use my home back up and try to get more reports out of my Soapware data.

mel

09-29-2004, 09:12 AM

swampfox

09-30-2004, 06:37 AM

Probably a bad choice of names but the idea of trying to customize has interested me. I think Randall's idea of a developer agreement and source code sharing may allow me to play with it and accomplish something everyone can use.

I did a boatload of quickbasic programming years ago , a fair amount of visual basic when it first came out, a little visual C++ and even a little asm in the early years.

I am not clear on how this runs on linux. I have presumed soapware is written in visual basic. The only DB programming I have done is foxpro, again, a number of years ago.

Take me thru the basic steps on what program to use and where to look at the files. I would only do this on a separate computer at home.

srkatari

09-30-2004, 09:53 AM

Welcome to Docsboard
Do you carry a CD-RW of all your data to & from home to do this?
:confused:
YES. I lost data by not checking my back up data long time ago. Since then I do carry my data home every day, restore it on home computer. I zip file the back up. I use USB memory and/or CDRW. I back up only charts not including images on day to day basis. I do back up of image files included once a week to burn a CD.

I think Soapware is developed in C language. I am happy to see my data separate from the front end program. Access or Visual Basic can be used to get more customized reports out of the data made by Soapware.

mbenjam

09-30-2004, 11:50 AM

To me, backups are nice, but as you say, they are fallible. What I'd like to implement is a professional-style data center, like how real data-driven businesses do it. For example, Amazon.com doesn't burn its order database to CD-RW each week. It has a data center, with a set of redundant servers that operate as one unit. When one server goes down, a little light flashes somewhere, a tech pulls out the bad server, goes to Fry's, buys a new server, and sticks it back in. Meanwhile, the end users don't even notice the glitch.

I think physicians need EMR software that looks nice and works well with their workflow, but they also need EMR software to be fault tolerant. MySQL is a platform that allows savvy users to implement fault tolerant features like cluster servers and replication. That's why I'm so excited to have a MySQL implementation.

They use these database tools to sell books online, so why shouldn't we use tools like these to care for patients?

My next task is to implement a database cluster in the office. It may have to wait until after the boards 11/3-11/4. We have a gigabit switch already, and a bunch of P4's sitting around making idle processor cycles, so we might as well distribute the database.

The problem is that when you talk to doctors about database replication and fault tolerance, their eyes glaze over...these are principles that have been understood in IT for years.

mbenjam

09-30-2004, 11:54 AM

The language doesn't have to be VisualBasic. Scripting languages like Perl and PHP have well-developed tools for doing operations on databases like MSSQL and MySQL. Dreamweaver even lets you make data-aware web pages in PHP using a visual editor.

I think the low fruits for customization are a (better) scanning interface, a bill abstractor, and custom reports, and tighter flowsheet-to-plan, vital-sign-to-objective, and lab-to-objective integration. If people are interested, I can expand on these ideas. Perhaps DOCS Inc are already working on these.

swampfox

09-30-2004, 12:42 PM

My biggest problems are Rx. managment and tracking lab and messages for legal purposes with good paper trail. I believe that to show up within the soapware program, it will have to be in the source code and compliled into the program, such as an integrated rx manager.
It looks like it would be fairly easy to mine the data outside of the program for various lists, sort of like their module to archive charts. Outside of the soapware program, it would seem to be of limited usefulness.
I think it would work like Dr. Oates said. He distributes source code and a developer agreement to protect his business. We get the access to the code for the fun or some relatively low cost benefit like free upgrade, etc. He gets free programming ideas and code , but it always passes thru him to make sure it is what they want and can support. I guess if I were him, I would want the final say as to what if any reimbursement for the coding .

DOCS

09-30-2004, 01:42 PM

In all honesty, SOAPware v5 will eventually address 99% of the problems and suggestions for v4 and addresses all of those listed by mbenjam. We will not have all the enhancements in 5.0.1. as we have had to make some compromises to get it out the door.
Do not do much customization with SOAPware v4 as it will have to be thrown away in v5.
v4 is written in Visual C++. SOAPware v5 is written in C# in the .Net environment. In this environment, Visual Basic, C++, Java and other languages can be used to develop enhancements. The default database is MySQL and all data is stored with XML. Microsoft SQL will be supported as well and this will likely be the default for our integrated product (i.e. Billing, scheduling, etc in addition to EHR).
We have not decided yet the nature of the agreements where we will release source code. Some may simply want to purchase a developer's kit and may want to sell an enhanced product on their own. The single docs that don't want to purchase a developer kit may very well want to exchange their code.

Randall Oates, M.D.
President, DOCS, Inc.

mbenjam

09-30-2004, 05:44 PM

Thanks for the info, Dr. Oates. I enjoy using SOAPWare, and I think it's pretty good at what it does. The timeframe for version 5 would be the key issue--if it's coming in a month, then as you say, it's not worthwhile to upgrade. If it's coming in a year, then users might as well cobble together some improvements.

I'm not sure I need scheduling and billing integration--we just invested $30,000 in a new billing system for the office, and I would rather invest another $3,000 in making the systems talk to each other than start from scratch with v5.

The other thing is, how much are these new features going to cost? DOCS inc seems to be moving in the same direction as other EMR vendors: single upstart MD has bright idea, cobbles together EMR, other docs like it, bells and whistles get added, the price goes up, and finally, a major company buys up the small upstart. This happened to patientkeeper, may happen to Infor-Med, and who did GE buy? I forget the name of the company. Things get expensive over time.

Anyway, the major part I love about SOAPWare is it's cheap. The other thing is, it's got a simple enough architecture that it can be modified by innovative users. It represents a huge help to the physician developer, because instead of writing database schema and encounter note interfaces from scratch, it's already in place.

Maybe what I'm getting to is, don't abandon v4 even though you put out v5. I don't think I will. I think there must be a base of users out there who don't necessarily need a more technologically advanced SOAPWare, and wouldn't necessarily pay more for one either.

The source code would be nice: instead of one development branch, there could be many. Some of the portal programs have features where it's simple to apply patches to the source.

EMRhelp.org

09-30-2004, 06:43 PM

SOAPWare is great because it's not exactly open source, but it's open database. The data structures are pretty straightforward.

Nice ! can you ODBC connect to the tables ? or connect in another way ?

mbenjam

10-01-2004, 05:20 PM

You can use ODBC drivers to connect to the SOAPWare database. There are "DSN-less" ways of connecting to MSSQL databases as well--there are lots of books and websites that describe how to do this within almost every language out there.

DOCS

10-02-2004, 11:33 AM

We are in early beta with v5 right now. I predict (not promise) a usable beta by the end of 2004 with product release sometime in 2005.
We are taking out the open database connectivity (ODBC) and will only support SQL. The free-standing EHR product will use MySQL as the default. The fully integrated (EHR, Billing, Scheduling) product will probably require Microsoft SQL. We are taking out the ODBC interface primarily to get everyone off Access. Access is just not stable enough or powerful enough for a widely distributed EHR. It is particularly problematic in wireless environments when connections are lost and the database gets corrupted.
Being completely honest, we really don't know when in 2005 it will be usable in real time. Most of 5.0 should come together fairly smoothly and be surprisingly bug free simply because some of what has been problematic has recently been removed for the 5.0.1 release. BUT, “SmartText” is the big unknown and most complex part of 5. It basically has to work perfectly and will most likely take a fair number of testers to find everything hidden in all the dark corners before we can consider it a released product. SmartText is a very elegant means of using XML to allow very complex use of well-defined and discreet objects (multiple layered, nested objects) in what otherwise appears as free text. This will also allow us to greatly enhance integrations, (e.g. X-Link). The whole interface is different with v5 being Tablet-Voice and “task” centric whereas v4 is keyboard and “soap note” centric.
The conversion, v4 to 5 should be pretty straight forward. We've learned a lot over time with this being our third major conversion in 10 years. Also, the long-term annual costs will be lower for those who get v4 and upgrade to v5 ( e.g. $400 vs $500).

Randall Oates, M.D.
President, DOCS, Inc.

BigDoc

10-02-2004, 12:00 PM

We are in early beta with v5 right now. I predict (not promise) a usable beta by the end of 2004 with product release sometime in 2005.
Randall Oates, M.D.
President, DOCS, Inc.
Randall, thanks for the update :D

doch

04-29-2005, 05:53 AM

I am considering using Soapware and Lytec for a new solo family practice. Anybody have any thoughts about using these products together?

My real question is what backend database to use for Soapware? Originally, I had the idea of using the Soapware recommended/supported Microsoft SQL Server. Since I've learned that version 5 is going to default to MySQL, I am considering starting with MSDE (to save money) and migrating to MYSQL when version 5 is stable. Any thoughts on this path, as I'm not very saavy with computer issues?

Thanks for any advice.

mbenjam

04-29-2005, 02:37 PM

What I did was install the product with the included MSDE server, according to the instructions. One could then install MySQL on either the same machine or a different one, then port the databases to MySQL using the instructions I provide above.

I have been using a MySQL backend for about six months now, and I haven't had any problems. I also have an automated plain SQL dump set up to back up my tables daily. The system was kind of a pain to set up initially, but works well now.

I would be happy to help you set up your MySQL system for a fee :). The main advantage is that there is no limit to the database size with MySQL, and performance may be a teeny bit better than MSDE. Since the limiting factor with using Soapware is how fast you can type (or dictate), the database performance is not a huge issue.

I have five desktop PCs hitting the same MySQL database over the wired LAN in the office. The database server is running on an AMD 2400 HP. I also use this PC to run the client program.

I offer the advice in setting up the MySQL server as a way to give users the ability to run Soapware on an industrial-strength database platform. If this appeals to you, go for it. If you don't know what this means, you are probably better off sticking to MSDE.

Rereading your question, you seem to be implying that you can migrate data server architectures when you upgrade to Soapware v5. I'm not sure that's a valid assumption. They sound like they are going to have a new data schema with the new version, and this may complicate the upgrade. Dr. Oates and co. will probably give us tools to simplify the upgrade, so one should wait and see what happens, and just do what feels right for the time being.

DOCS

05-01-2005, 05:44 AM

There has been a great deal of work expended on the SOAPware v4 to v5 converter. It appears that it should be a fairly straight-forward and lossless transfer. The only caveat is that some print report formats may need to be tweaked a bit. However, the process of report format creation has been greatly simplified into a “drag-n-drop to a lay-out form” instead of having to insert a bunch of arcane commands.
MySQL is a pretty amazing database, and I can understand the eagerness to move to it sooner rather than later. However, at present, I would suggest using the default MSDE (not MySQL) simply because the converter is optimized and tested with MSDE (or Microsoft SQL) as storing the original data.
The v5-beta has been in limited distribution for 5 months, and is on schedule for public distribution in June. The release candidate is not expected until late this year.

OBTW: Please contact support at DOCS, Inc. so we can schedule your installation. We have been extending our support staff-services and are now encouraging SOAPware users to allow us to do installs and updates remotely (no additional charges for new users and those with a support/update agreement).
All SOAPware users should plan for high speed internet access to take advantages of many new services and functions in development. For example, e-Prescribing will be available within a few weeks. e-Prescribing has turned out to be way too cool to have to wait for v5!

Randall Oates, M.D.
President DOCS, Inc.
www.SOAPware.com

DaffyDuck

06-06-2005, 04:49 PM

At some point I will upgrade my sytem to a true server net work. Do I use Microsoft SQL or MySQL.

mbenjam

10-06-2005, 10:05 PM

So, we got a snazzy networked scanner/MFP, so I will try importing a simple image into my MySQL/Soapware setup, right?

No, the client barfs on loading the imported image, calls the scanner utility, and gives the LEADTOOLS error. I think this is a simple database incompatibility. D'oh!

I thought I was so cool...

The system's worked well so far. The other docs in my community are impressed. It's slower than dictating or writing would be, but my stuff is all typed.

Next: consider "upgrading" to MSSQL... We'll see...Any thoughts? Randall?

BTW: if you wanna fire your copier lease, read my Epinion on the CX11NF...

Graham

07-23-2006, 03:33 PM

Just curious as to the size of the databases after conversion. Were they the same or smaller?

I am doing a conversion at present to Firebird, and am stripping out all the extraneous formatting in the soap notes. Seems a 1.3Gb database can be reduced to under 100Mb in this way. But not sure if the problem is in the sql server table format, or just the way soapware stores the data.

mbenjam

07-24-2006, 01:27 AM

Hi there. I abandoned my MySQL project shortly after the October 2005 post because the MySQL MyODBC setup couldn't handle image files, and I started scanning some reports at that point. SOAPWare is still a great piece of equipment, though I dictate into it at $ .08 per line (thru human transcribers in India).

I don't remember the size of the MySQL converted databases, but I don't think it was near 1.3Gb. MSSQL hasn't complained about the size of the database yet, so I must be lower than 1Gb. Tomorrow, I'll check into this. I must learn how to backup the database file on the server.

It's an unfortunate choice of database platforms, this Firebird, for this task, since converting MSSQL -> MySQL and back is a (nontrivial) relatively simple task, while Firebird may be new enough that there isn't a MSSQL -> Firebird tool out there yet.

Writing Perl scripts to handle the conversion yourself is as much fun as eating cold soup.

HTH,
Mike

Graham

07-24-2006, 02:47 AM

Actually Firebird, being a fork of Interbase, is one of the oldest sql databases still in use.

I chose to use Firebird as it is free of the mysql licensing restrictions.

Perl ? Nah. I used something else to code the data transfer scripts, as well as rip out all the bloat caused by using RTF inside the clinical notes - this is what you get by violating basic database principles.

It's now looking more like 250mb from the original 1.3Gb, but the conversion is still in progress.