Saturday, February 22, 2014

eClinicalWorks (ECW) FTP File Data

So, we recently ran into a problem on our eClinicalWorks (ECW) server with a disk that is failing and/or a corrupt file system. Specifically the disk used to store all of the FTP data for patient documents. The possibly failing/corrupt disk (and/or file system) was only partially available -- the data seemed to be accessible, however, you could not get a complete directory listing (we're assuming due to file system corruption). We could copy data off if we knew the file name that we needed.

All of these patient documents are stored on the server and accessible via FTP (service running on server). The client application then looks up the file names associated with a patient, and that's what you see listed in the client application window. We figured this data (the file names) must be stored in the ECW MySQL database. We approached eClinicalWorks tech. support about getting a full listing (query) for the database, however, they were unsure of how to do this.

I didn't know the credentials for the ECW MySQL database, but they were pretty easy to find on our system. I looked in "Scheduled Tasks" applet in the Control Panel and found a job called "mysql_optimize". I found the batch script the job used and the username/password for the DB was listed.

I then connected to the DB with the mysql application and poked around a bit. I ended up doing a complete dump of the database (SQL) to a flat file, then looked for a file name that we already knew (some were able to be listed on the file system). This led me to the correct table and column needed: document (table name), fileName (column containing what we need)

Then you can simply extract the data to a flat file:
mysql -uecwDbUser -pPASSWORD -P4928
use mobiledoc;
select fileName into 'c:/file_names.txt' from document;

That gave me a flat text file for all the patient files that belong in the FTP root; I could then loop over and run robocopy for each use a batch script. I hope this tip helps some else with eClinicalWorks!

5 comments:

  1. Hey Marc, I have some questions about querying an eCW db. I think there is a lot of misinformation out there spread maybe purposefully by eCW to get people to buy their paid reporting modules. Is it possible for me to e-mail you a few questions about this? Thanks

    ReplyDelete
    Replies
    1. Yeah I wouldn't doubt it... I've worked in IT for quite a while now, and I have to yet to come across another company quite like eClinicalWorks... the company, support, and product are well, special. Definitely not an ECW DB expert but I'd be happy to help: msmith626 at gmail dot com

      --Marc

      Delete
    2. Thanks for the that information. Its nice to know. I might have a similar situation coming up, and might need some assistance then. Would it be ok to reach out to you ? I do see you email listed on this thread, let me know if that would still be valid

      Delete
    3. Hi, yes, that is fine: msmith626 at gmail dot com

      Delete
  2. This comment has been removed by the author.

    ReplyDelete