How to use MySQL to read xml dataset for integration test instead of H2 database

Tags: #<Tag:0x00007f01bb1fd010> #<Tag:0x00007f01bb1fced0>

Hi there, we are developing a reporting module that uses specific MySQL functions in queries. When we run the queries in H2 database for integration test it complains the syntax.

I tried to replace the functions for equivalent ones so that both H2 and MySQL can understand. But when I do that the results are not the same in MySQL.

There are some functions that I changed to use in both databases:

    • date_add() replaced with timestampadd()
    • datediff() replaced with timestampdiff()

As the module uses MySQL in production I would like to know how to configure project to use MySQL + DbUnit + XML Dataset. May be there are other solutions to do that, If you guys know please let me know

1 Like

In your test, override the useInMemoryDatabase() method and return false. Then supply the connection information as system properties. This will give you some context: https://github.com/openmrs/openmrs-core/blob/master/api/src/test/java/org/openmrs/test/BaseContextSensitiveTest.java#L312-L349

Thank you for your reply. Does this configuration require a physical instance of MySQL database in the machine?

Depends on your databaseUrl value

ok I understand. let me try it

by this it means mysql can not be used in memory for unit tests?

MySQL is not primarily an in memory database. https://en.wikipedia.org/wiki/List_of_in-memory_databases

the idea is to run those SQL functions in database server who understands the syntax… or use mysql in memory.

Tell me something, how do I load xml dataset in MySQL physical instance for tests using dbunit ? May be that is the solution

Did you try doing what i advised above? It should work for MySQL.

Yes sure. But when do that I get the below error:

https://pastebin.com/H7dpG3dY

Looks like there is required privilege missing to stick the data in the database.

Bellow my initial dataset:

<!-- user -->
<users user_id="1" system_id="admin" creator="1"
	date_created="2008-10-13 11:32:52" person_id="1" retired="0"
	uuid="1c3db49d-440a-11e6-a65c-00e04c680037" />

<!-- role -->
<role role="System Developer"
	description="Developers of the OpenMRS .. have additional access to change fundamental structure of the database model."
	uuid="8d94f852-c2cc-11de-8d13-0010c6dffd0f" />

<role role="Authenticated CDaccess"
	description="For Concept Dictionary Synchronization only"
	uuid="e2f0a906-1d5f-11e0-b929-000c29ad1d07" />
	
	<role role="Anonymous"
	description="Privileges for non-authenticated users."
	uuid="774b2af3-6437-4e5a-a310-547554c7c65c" />
	
	<role role="Provider"
	description="General privileges held by all providers"
	uuid="8d94f280-c2cc-11de-8d13-0010c6dffd0f" />
	
<!-- role_role -->
<role_role parent_role="System Developer"
	child_role="Authenticated CDaccess" />

<!-- user_role -->
<user_role user_id="1" role="System Developer" />
<user_role user_id="1" role="Provider" />


<!-- privilege -->
<privilege privilege="Get Patients"
	description="Able to get patients"
	uuid="d05118c6-2490-4d78-a41a-390e3596a244" />


<!-- role_privilege -->
 
	<role_privilege role="Provider"
	privilege="Get Patients" />
	
	

<!-- user_property -->
<user_property user_id="1" property="loginAttempts"
	property_value="0" />

Can you edit your post and replace the stack trace with a link to pastebin.com where your paste is?

Yes sure https://pastebin.com/H7dpG3dY

You need to add the required privilege.

thank you! that’s was what I was thinking, any privilege for writing? or all of them mentioned in here https://wiki.openmrs.org/pages/viewpage.action?pageId=3346872

Doesn’t the stack trace have the missing privilege?

yes it does, Let me look for what privilege is and then I will come back