Re: Help with webservices

[Ben Wolfe]

The default values on the date fields should not be there.  I opened this ticket to have it fixed:
https://tickets.openmrs.org/browse/TRUNK-3329

When looking at this, I saw that person.birthdate is nullable for some reason even though we have it required in the API.  So 0000-00-00 must be mysql's default if you create the row and don't provide a value for a nullable date column?

Ben

On Wed, May 2, 2012 at 12:21 AM, Mathias Lin | Meta Healthcare <mathi...@metahealthcare.com> wrote:

The problematic field was mainly the person.birthdate, which was all set to
0000-00-00 instead of NULL (beside others, having a strange default date as
well).

Adding &zeroDateTimeBehavior=convertToNull to the jdbc connection string in
the openmrs-runtime.properties might solve it, as mentioned somewhere before
on the mailing list.


I found that when doing a clean installation of OMRS 1.8.3, the default
timestamp values of some of the fields in obs, encounter and person have a
strange default value ('0002-11-30 00:00:00'):

(Taken from the mysql manager client, after the setup):

encounter:
 `encounter_datetime` datetime NOT NULL DEFAULT '0002-11-30 00:00:00',
 `date_created` datetime NOT NULL DEFAULT '0002-11-30 00:00:00',

obs:
 `date_created` datetime NOT NULL DEFAULT '0002-11-30 00:00:00',
 `obs_datetime` datetime NOT NULL DEFAULT '0002-11-30 00:00:00',


My table settings:
InnoDB, Encoding: UTF-8 Unicode (utf8), Collation: utf8_general_ci

On Tue, 24 Apr 2012 21:09:17 -0700, Darius Jazayeri
<djazay...@GMAIL.COM> wrote:

>Hi Jonathan,
>
>At the root of the error message is: Caused by: java.sql.SQLException:
>Cannot convert value '0000-00-00' from column 4 to TIMESTAMP
>
>You must have some (illegal) dates in your database that are set to zero.
>Is this something you can clean up?
>
>-Darius
>
>On Tue, Apr 24, 2012 at 8:43 PM, Jonathan Galingan
<galin...@gmail.com>wrote:
>
>> I tried:
>>
>> curl -i -u "admin:Password1"
>> http://localhost:8080/openmrs/ws/rest/v1/patient?q=123
>>
>> and it worked
>>
>> I tried:
>>
>> curl -i -u "admin:Password1"
>> http://localhost:8080/openmrs/ws/rest/v1/patient?q=john
>> and I get this error
>> http://justpaste.it/xhr
>>
>> I tried:
>> curl -i -u "admin:Password1"
>> http://localhost:8080/openmrs/ws/rest/v1/patient?q=james
>> and I get the same error
>>
>> but if I tried:
>> curl -i -u "admin:Password1"
>> http://localhost:8080/openmrs/ws/rest/v1/patient?q=jam
>> it works
>>
>> I think this is a bug with the web service. Could anybody interpret the
>> error? thanks
>>
>>
>> On Tue, Apr 17, 2012 at 6:12 AM, Jonathan Galingan
<galin...@gmail.com>wrote:
>>
>>> Oh. I see. I think I've got it. Thanks a lot.
>>>
>>> The switch to patient did the trick.
>>>
>>>
>>> On Tue, Apr 17, 2012 at 2:44 AM, Darius Jazayeri
<djazay...@gmail.com
>>> > wrote:
>>>
>>>> Hi Jonathan,
>>>>
>>>> Are you intentionally using the person resource rather than the patient
>>>> one? Because for 95% of use cases, the patient resource is the right
one.
>>>> (Also, you can't search on the person resource for patient ids.)
>>>>
>>>> I peeked quickly at the code, and I think the person resource delegates
>>>> its searching
>>>> to org.openmrs.api.db.hibernate.HibernatePersonDAO.getPeople(String,

>>>> Boolean). This method *does* look at the given, middle, family, and

>>>> family2 name components. If you're actually intending to search via the
>>>> person resource, and you're really seeing this bug, I'll research further.
>>>>
>>>> Assuming you actually want the patient resource, as far as I know
>>>> whatever you pass as its q parameter is treated identically to the patient
>>>> search box in OpenMRS. (I.e. you can find partial names, or exact
>>>> identifiers)
>>>>
>>>> -Darius
>>>>
>>>> On Mon, Apr 16, 2012 at 11:29 AM, Jonathan Galingan <
>>>> galin...@gmail.com> wrote:
>>>>
>>>>> When I tried:
>>>>> http://localhost:8080/openmrs/ws/rest/v1/person?q=john
>>>>>
>>>>> it only searched through patients that have a given name "john" . I
>>>>> wanted to also get people with the middle name or last name of
"Johnson"
>>>>> Would anybody know how to make the webservices do that?
>>>>>
>>>>> I then tried:
>>>>> http://localhost:8080/openmrs/ws/rest/v1/person?q=john_smith
>>>>>
>>>>> it now searched through patients that have a given name "john" while
>>>>> the middle name or last name is "smith." Is it possible to make this to
>>>>> return a patient named "smith Johnson"?
>>>>>
>>>>> For the identifier I tried:
>>>>> http://localhost:8080/openmrs/ws/rest/v1/person?q=123
>>>>>
>>>>> I have a patient with an id of "123" but it did not search for it.
>>>>> Would I need to change the q to something else?
>>>>>
>>>>> On Mon, Apr 16, 2012 at 9:39 PM, Friedman, Roger (CDC/CGH/DGHA)
(CTR) <
>>>>> rd...@cdc.gov> wrote:
>>>>>
>>>>>>  the q parameter will search both name and ID.  i would search on id

>>>>>> and then verify that the name returned is the one expected.****
>>>>>>
>>>>>> ** **
>>>>>>
>>>>>> *From:* implem...@openmrs.org
[mailto:implem...@openmrs.org] *On
>>>>>> Behalf Of *Jonathan Galingan
>>>>>> *Sent:* Sunday, April 15, 2012 8:46 PM
>>>>>> *To:* openmrs-i...@LISTSERV.IUPUI.EDU
>>>>>> *Subject:* [OPENMRS-IMPLEMENTERS] Help with webservices****
>>>>>>
>>>>>> ** **

>>>>>>
>>>>>> Hi, I'm trying to replicate the patient search box of OpenMRS in

>>>>>> OpenERP to integrate the two. ****
>>>>>>
>>>>>> ** **

>>>>>>
>>>>>> Would anybody know how a complete name with identifier could be

>>>>>> searched through the webservices?****
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Jonathan D. Galingan, MD****
>>>>>>
>>>>>> Project Manager for Computerization****
>>>>>>
>>>>>> Philippine General Hospital****
>>>>>>
>>>>>> ** **
>>>>>>  ------------------------------
>>>>>>
>>>>>> Click here to unsubscribe<LIST...@LISTSERV.IUPUI.EDU?
body=SIGNOFF%20openmrs-implement-l>from OpenMRS Implementers' mailing
list
>>>>>> ****
>>>>>>  ------------------------------
>>>>>> Click here to unsubscribe<LIST...@LISTSERV.IUPUI.EDU?
body=SIGNOFF%20openmrs-implement-l>from OpenMRS Implementers' mailing

list
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Jonathan D. Galingan, MD
>>>>> Project Manager for Computerization
>>>>> Philippine General Hospital
>>>>>

>>>>>  ------------------------------
>>>>> Click here to unsubscribe<LIST...@LISTSERV.IUPUI.EDU?
body=SIGNOFF%20openmrs-implement-l>from OpenMRS Implementers' mailing
list
>>>>>
>>>>
>>>> ------------------------------
>>>> Click here to unsubscribe<LIST...@LISTSERV.IUPUI.EDU?
body=SIGNOFF%20openmrs-implement-l>from OpenMRS Implementers' mailing

list
>>>>
>>>
>>>
>>>
>>> --
>>> Jonathan D. Galingan, MD
>>> Project Manager for Computerization
>>> Philippine General Hospital
>>>
>>>
>>
>>
>> --
>> Jonathan D. Galingan, MD
>> Project Manager for Computerization
>> Philippine General Hospital
>>

>>  ------------------------------
>> Click here to unsubscribe<LIST...@LISTSERV.IUPUI.EDU?
body=SIGNOFF%20openmrs-implement-l>from OpenMRS Implementers' mailing
list
>>
>
>_________________________________________
>
>To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to
LIST...@LISTSERV.IUPUI.EDU with "SIGNOFF openmrs-implement-l" in the
body (not the subject) of your e-mail.
>
>[mailto:LIST...@LISTSERV.IUPUI.EDU?body=SIGNOFF%20openmrs-
implement-l]
>

_________________________________________

To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to LIST...@LISTSERV.IUPUI.EDU with "SIGNOFF openmrs-implement-l" in the  body (not the subject) of your e-mail.

[mailto:LIST...@LISTSERV.IUPUI.EDU?body=SIGNOFF%20openmrs-implement-l]

[Mathias Lin | Meta Healthcare]

Hi Ben,

sorry for the late reply.

> So 0000-00-00 must be mysql's default if you create the row and don't provide a value for a nullable date column?

No, this doesn't seem to be the case. I've run a clean installation
and created one person without a birthday (only age), and the
person.birthdate field is filled with NULL, which looks fine.

The birthdate field actually looks fine, just the date_created field
is weird. This is the table structure after a clean OMRS 1.8.3
installation, when I look into the table structure info in my mysql
client:

CREATE TABLE `person` (
`person_id` int(11) NOT NULL AUTO_INCREMENT,
`gender` varchar(50) DEFAULT '',
`birthdate` date DEFAULT NULL,
`birthdate_estimated` smallint(6) NOT NULL DEFAULT '0',
`dead` smallint(6) NOT NULL DEFAULT '0',
`death_date` datetime DEFAULT NULL,
`cause_of_death` int(11) DEFAULT NULL,
`creator` int(11) NOT NULL DEFAULT '0',

`date_created` datetime NOT NULL DEFAULT '0002-11-30 00:00:00',

`changed_by` int(11) DEFAULT NULL,
`date_changed` datetime DEFAULT NULL,
`voided` smallint(6) NOT NULL DEFAULT '0',
`voided_by` int(11) DEFAULT NULL,
`date_voided` datetime DEFAULT NULL,
`void_reason` varchar(255) DEFAULT NULL,
`uuid` char(38) NOT NULL,

- Mathias

> ________________________________
> Click here to unsubscribe from OpenMRS Implementers' mailing list