NULL value in clv 6.1 stored procedure config?

[Wilger Hooijmeijer]

Hi all,

I’m trying to use the stored procedure in the outbound database protocol of Cloverleaf 6.1 but sending a NULL-value doesn’t seem to work. Does anyone can help me with this?

The (simplified) situation is:

– In a MS SQL server 2012 I’ve a database with a table, let’s say ‘patient’ with columns ‘patientid’ (int), ‘name’ (varchar), and ‘dob’ (datetime)

– There is a stored procedure that puts new patients in this table

– I want to call this stored procedure by Cloverleaf, based on a translation from HL7 ADT to VRL (based on the imported database schema)

This all works when the id, name and dob-values are present in the stored procedure call. The problem arises when the DOB is unknown: then I want to call the stored procedure with putting a NULL as value for DOB, so that the field in the database would have the NULL-value.

I’ve tried to options in the Xlate from ADT–>VRL:

1) COPY of =NULL -> field DOB (in VRL). This doesn’t work, because the driver or database sees this as text and tries to convert to a datetime datatype. Relevant parts of the process-log:

[pd

Topic

[Levy Lazarre]

Wilger,

You could try the following work-around:

1. Use a DEFAULT constraint when you define your dob column in the table

   

Code:

    dob DATETIME DEFAULT NULL
   

2. Make the dob an optional parameter in the stored procedure by giving it a default value of NULL

   

Code:

   @dob DATETIME = NULL
   

3. Finally, if the dob is null, just don’t include it in the insert list when you exec the stored procedure. This will force the default value (NULL) to be inserted in the database

 

   

Code:

   [HumanResources].[Update_Patienten_Remote](12345,John Doe)
   

I hope this helps.

[Wilger Hooijmeijer]

Hi Levy,

Thanks very much for your reply and suggestion. Unfortunately, this doesn’t help: even if after making the parameter optional with NULL as default value (both in the table and the stored procedure) I get the 1900-… datetime in the table after calling the stored procedure.

🙁

Wilger

[Levy Lazarre]

Hi Wilger,

In my opinion, this could only happen if you are still passing the “@null” from Cloverleaf to the stored procedure. The “@null” is probably transformed to ” (its representation in C) and this is what SQL Server is translating to the default date of 1900…

If you really want to test this, you could make a scaled down version of your stored proc (with only two parameters: @patientid, @name). This new stored proc should be programmed to insert a “null” in the dob field.

In your Cloverleaf code, you would check for the value of dob, and if null, call the scaled-down version of the stored proc. If not null, call the regular stored proc. Not the cleanest way, but this should work.

Good luck.

[Author]

Replies