To improve your OP system performance, we’re excited to announce that we are upgrading our infrastructure. For many of you, this will be a non-issue as our plan is to facilitate this on your behalf. Otherwise, if either of the following applies to your practice, this document is for you.
- If you’ve written your own DBViewer queries, custom reports, or QIC queries, you may need to make a few updates to those queries.
- If you have other entities that tap into your server for information, run queries that are passed off to another vendor, and/or connect with third-party vendors like Solution Reach, Rhinogram, or FigMD, share this document regarding the infrastructure update. They will need to work with OP to make the necessary changes by the end of Q4, so they can connect directly to your server. Please note that as of Q2 2022, your current and future third-party vendors will need to use our APIs to connect to your server. They will no longer be able to establish a direct connection.
Office Practicum will be changing its backend database from Firebird to MySQL. Firebird and MySQL both use "dialects" of the SQL language. SQL (Structured Query Language) is the programming language that both make calls from and sends updates to, a practice's OP database.
OP will be updating most of the queries and reports in your instance automatically. However, if you have created any custom queries or reports in the Database Viewer or OP Reports, you'll want to start following the guidelines outlined in this document now to ensure you are ready to go before we do your migration. The Cloud migrations begin early Q4 2021 and the On-Premise migrations begin in late Q4 2021. We’ll let you know your exact migration date at least two weeks prior to the event.
To learn how to write SQL that is compatible with both Firebird and MySQL, click here.
Where Would I Find Custom SQL
Custom SQL queries may be found in a number of areas within OP. Below are those areas and who will be addressing any incompatibilities.
|Area in OP||Addressing Incompatibility|
|Care Plans||The OP team will convert all SQL's associated with Care Plans in Office Practicum.|
|QIC||The OP team will convert all SQL's that are used to calculate quality measures in the QIC tool.|
|DB Viewer||The OP team will automatically convert most SQL's stored in DB Viewer. The OP Help Center will contain updates to, or MySQL equivalents of, queries originally provided in the SQL Clip and Save Library. The OP team will work with customers to make adjustments to any remaining incompatible queries.|
|Date Parameters||If you have a date parameter in your query, make sure the parameter name ends in "_date", for example, start_date and end_date. When you do this, DB Viewer knows to use a date picker for that parameter.|
The OP Team will be converting any incompatible reports that we include in OP with updated reports that work in MySQL. These include all reports in Billing Performance, InsCreditSummary, PatCreditSummary, and Standard reports delivered with OP. If you have created custom reports, the OP Team can work with you to make them compatible.
There are two kinds of reports in OP Reports: Standard reports and those that are built with an SQL statement.
select rb_item.name as report_name, coalesce(rf4.name||'/','')||coalesce(rf3.name||'/','')|| coalesce(rf2.name||'/','')||rb_folder.name as folder_path from rb_item left join rb_folder on (rb_item.folder_id=rb_folder.folder_id) left join rb_folder rf2 on (rf2.folder_id=rb_folder.parent_id) left join rb_folder rf3 on (rf3.folder_id=rf2.parent_id) left join rb_folder rf4 on (rf4.folder_id=rf3.parent_id) where rb_item.template like '%EditSQLAsText%' order by 2,1
If you have an On-Premise server (i.e. you are NOT in OP Cloud) you may have an ODBC or JDBC connection to your database so that you can extract data into tools like Excel, or that a hospital can pull data from your database. Those connections will need to be updated to use MySQL.
If you have other entities that tap into your server for information, run queries that are passed off to another vendor, and/or connect with third-party vendors like:
- Solution Reach
The practice will need to share this document regarding the infrastructure update. They will need to work with OP to make the necessary changes by the end of Q4 2021, that they may connect directly to your server. Please note that as of Q2 2022, your current and future third-party vendors will need to use our APIs to connect to your server. They will no longer be able to establish a direct connection.