Care Coordination Module issue importing documents

Situation
Care Coordination Module displays the following error message after attempting to import a ccd document for a new patient, not added yet:

An error occurred

An error occurred during execution; please try again later.

This is a persistent error and also occurs in the CCDA import page.
The CCR page itself displays the following;
ERROR :
Exception: Statement could not be executed (HY093 - - )
Exception: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
SQL statement : SELECT am.id as amid, cat.name, u.fname, u.lname, d.imported, d.size, d.date, d.couch_docid, d.couch_revid, d.url AS file_url, d.id AS document_id, ad.field_value, ad1.field_value, ad2.field_value, pd.pid, CONCAT(ad.field_value,’ ‘,ad1.field_value) as pat_name, DATE(ad2.field_value) as dob, CONCAT_WS(’ ',pd.lname, pd.fname) as matched_patient
FROM documents AS d
JOIN categories AS cat ON cat.name = ‘CCR’
JOIN categories_to_documents AS cd ON cd.document_id = d.id AND cd.category_id = cat.id
LEFT JOIN audit_master AS am ON am.type = ‘11’ AND am.approval_status = ‘1’ AND d.audit_master_id = am.id
LEFT JOIN audit_details ad ON ad.audit_master_id = am.id AND ad.table_name = ‘patient_data’ AND ad.field_name = ‘lname’
LEFT JOIN audit_details ad1 ON ad1.audit_master_id = am.id AND ad1.table_name = ‘patient_data’ AND ad1.field_name = ‘fname’
LEFT JOIN audit_details ad2 ON ad2.audit_master_id = am.id AND ad2.table_name = ‘patient_data’ AND ad2.field_name = ‘DOB’
LEFT JOIN patient_data pd ON pd.lname = ad.field_value AND pd.fname = ad1.field_value AND pd.DOB = DATE(ad2.field_value)
LEFT JOIN users AS u ON u.id = d.owner
WHERE d.audit_master_approval_status = 1
ORDER BY date DESC(‘CCR’)

#0 /var/www/html/openemr/interface/modules/zend_modules/module/Application/src/Application/Model/ApplicationTable.php(62): Laminas\Db\Adapter\Driver\Pdo\Statement->execute()
#1 /var/www/html/openemr/interface/modules/zend_modules/module/Ccr/src/Ccr/Model/CcrTable.php(95): Application\Model\ApplicationTable->zQuery()
#2 /var/www/html/openemr/interface/modules/zend_modules/module/Ccr/src/Ccr/Controller/CcrController.php(72): Ccr\Model\CcrTable->document_fetch()
#3 /var/www/html/openemr/vendor/laminas/laminas-mvc/src/Controller/AbstractActionController.php(77): Ccr\Controller\CcrController->indexAction()
#4 /var/www/html/openemr/vendor/laminas/laminas-eventmanager/src/EventManager.php(331): Laminas\Mvc\Controller\AbstractActionController->onDispatch()
#5 /var/www/html/openemr/vendor/laminas/laminas-eventmanager/src/EventManager.php(188): Laminas\EventManager\EventManager->triggerListeners()
#6 /var/www/html/openemr/vendor/laminas/laminas-mvc/src/Controller/AbstractController.php(103): Laminas\EventManager\EventManager->triggerEventUntil()
#7 /var/www/html/openemr/vendor/laminas/laminas-mvc/src/DispatchListener.php(139): Laminas\Mvc\Controller\AbstractController->dispatch()
#8 /var/www/html/openemr/vendor/laminas/laminas-eventmanager/src/EventManager.php(331): Laminas\Mvc\DispatchListener->onDispatch()
#9 /var/www/html/openemr/vendor/laminas/laminas-eventmanager/src/EventManager.php(188): Laminas\EventManager\EventManager->triggerListeners()
#10 /var/www/html/openemr/vendor/laminas/laminas-mvc/src/Application.php(331): Laminas\EventManager\EventManager->triggerEventUntil()
#11 /var/www/html/openemr/src/Core/ModulesApplication.php(116): Laminas\Mvc\Application->run()
#12 /var/www/html/openemr/interface/modules/zend_modules/public/index.php(66): OpenEMR\Core\ModulesApplication->run()

OpenEMR Version
I’m using OpenEMR version 6.0.0 with the 6-0-0-Patch-3 applied.
PHP 8.0.12 (cli) (built: Oct 19 2021 10:34:32) ( NTS gcc x86_64 )
Copyright (c) The PHP Group
Zend Engine v4.0.12, Copyright (c) Zend Technologies
with Zend OPcache v8.0.12, Copyright (c), by Zend Technologies

Browser:
I’m using: Firefox 94.0 (64-bit)

Operating System
I’m using: Fedora 35

Logs
I just turned onn User Debugging under Administration->Globals->Logging User Debugging Options=>All.

Simple-xml is enabled;
php -i | grep -i simplexml
/etc/php.d/20-simplexml.ini,
SimpleXML
SimpleXML support => enabled

Let me know what else that you may need.
In a search, I could not find a previous situation that described my issue.

Thanks!

That’s a really bizarre error because that query doesn’t have any bound parameters as far as I can tell at all. Can you copy the query and run it in phpmyadmin? Does the query execute?

Only thing I can think of is that maybe your missing a column and need to run the sql_upgrade.php and the query error message is being triggered due to a missing column and not a bound parameter problem.

OK, I’m not a phpmyadmin regular user, but this is what I get;

SQL query:
SELECT am.id as amid, cat.name, u.fname, u.lname, d.imported, d.size, d.date, d.couch_docid, d.couch_revid, d.url AS file_url, d.id AS document_id, ad.field_value, ad1.field_value, ad2.field_value, pd.pid, CONCAT(ad.field_value,’ ‘,ad1.field_value) as pat_name, DATE(ad2.field_value) as dob, CONCAT_WS(’ ',pd.lname, pd.fname) as matched_patient FROM documents AS d JOIN categories AS cat ON cat.name = ‘CCR’ JOIN categories_to_documents AS cd ON cd.document_id = d.id AND cd.category_id = cat.id LEFT JOIN audit_master AS am ON am.type = ‘11’ AND am.approval_status = ‘1’ AND d.audit_master_id = am.id LEFT JOIN audit_details ad ON ad.audit_master_id = am.id AND ad.table_name = ‘patient_data’ AND ad.field_name = ‘lname’ LEFT JOIN audit_details ad1 ON ad1.audit_master_id = am.id AND ad1.table_name = ‘patient_data’ AND ad1.field_name = ‘fname’ LEFT JOIN audit_details ad2 ON ad2.audit_master_id = am.id AND ad2.table_name = ‘patient_data’ AND ad2.field_name = ‘DOB’ LEFT JOIN patient_data pd ON pd.lname = ad.field_value AND pd.fname = ad1.field_value AND pd.DOB = DATE(ad2.field_value) LEFT JOIN users AS u ON u.id = d.owner WHERE d.audit_master_approval_status = 1 ORDER BY date DESC(‘CCR’);

MySQL said: Documentation
#1583 - Incorrect parameters in the call to native function ‘CONCAT’

Double check that wherever you are copying that query from doesn’t have special quote characters. From what you are posting here it looks like different quotes are being used but that may just be a rendering problem here in the forum. I’d double check that.

Thanks, I will try and do this. Interestingly, i installed another instance of OpenEMR in a virtual machine on Windows 11 with XAMPP. Same errors on the import pages.

I get the same error. xampp 8 on mac, openemr 6 patch 3. I did not have errors with care coordination until I applied the patch. Did not see any issues when I ran sql_patch.php

The only error in php_error_log is:
Invalid parameter number: number of bound variables does not match number of tokens
SQL statement : SELECT option_id, title FROM list_options WHERE list_id = ‘abook_type’(’’)
#0 /Applications/XAMPP/xamppfiles/htdocs/openemr/interface/modules/zend_modules/module/Application/src/Application/Model/ApplicationTable.php(62): Laminas\Db\Adapter\Driver\Pdo\Statement->execute(Array)

Yes, that error shows on my installation as well under the export tab in the CareCoordination module.

So, the net result right now is that no imported xml files show under imports, therefore, cannot be linked to existing patients.

i also did a complete reinstall on the Fedora machine…same problem.

I’m going to play around with some other things and see what happens.


This shows what is being displayed in the import tabs.