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!