Hi Scott,

I tried to test the connection to MS SQL Server 2005 with JDBC by a third-party SQL tool SQuirrel. Having the same connection string set up as the one in IRIS, using the same JDBC jar file, yet with a recent JDK (openjdk version "18.0.2" 2022-07-19), I was able to connect to the database of the target MS SQL Server instance.

Now I'm trying to figure out whether the JDK version made the differences. I didn't mention that in order to make it work, I had to modify the file java.security according to some posts (https://stackoverflow.com/questions/69623611/how-do-i-allow-java-client-...). But this was done before I switched JDK so that should be independent.

Thanks, Vitality. I got these error messages captured in the log file of the SQL Gateway Logging. It's good to see something new now.

Invalid Message Count (452); expected: -1 got: 1. Server returned: 19801. Closing Connection

My bad... and of that Youtube video. I had made those uuids in the bundle reckonlessly which caused the failure of resolving the references in IRIS. After I have corrected them into proper ones, that works!!

Having modified a bit the FHIR bundle transaction and tested again. This time it helped prevent having the same Patient resource created twice, which is already good enough for me.

But the "fullUrl" reference of the Patient resource still cannot get resolved with the logical reference. I'm a bit disappointed with that the value of the "subject"."reference of the ServiceRequest got unchanged.

Here is the FHIR Bundle Transaction sent to /csp/healthshare/fhirserver/fhir/r4 (places bold are those modified):

{
    "resourceType": "Bundle",
    "type": "transaction",
    "entry": [
        {
            "fullUrl": "urn:uuid:patient",
            "resource": {
                "resourceType": "Patient",
                "active": true,
                "birthDate": "1970-04-12",
                "gender": "female",
                "identifier": [
                    {
                        "type": {
                            "coding": [
                                {
                                    "system": "http://myterminology.com",
                                    "code": "1001949"
                                }
                            ]
                        },
                        "use": "usual",
                        "value": "A1234563"
                    }
                ],
                "name": [
                    {
                        "family": "CHAN",
                        "given": [
                            "CLARA"
                        ],
                        "use": "official"
                    }
                ]
            },
            "request": {
                "method": "POST",
                "url": "Patient",
                "IfNoneExist": "identifier=A1234563"
            }
        },
        {
            "resource": {
                "resourceType": "ServiceRequest",
                "requisition": {
                    "system": "http://myexample.com",
                    "value": "H2302-10648"
                },
                "status": "active",
                "intent": "original-order",
                "category": [
                    {
                        "coding": [
                            {
                                "system": "http://snomed.info/sct",
                                "code": "108252007"
                            }
                        ]
                    }
                ],
                "priority": "routine",
                "code": {
                    "coding": [
                        {
                            "system": "http://myexample.com",
                            "code": "CBC"
                        }
                    ]
                },
                "subject": {
                    "type": "Patient",
                    "reference": "urn:uuid:patient"
                },
                "authoredOn": "2023-02-08T08:40:00+08:00"
            },
            "request": {
                "method": "POST",
                "url": "ServiceRequest"
            }
        }
    ]
}

And here is the response of the HTTP GET from /csp/healthshare/fhirserver/fhir/r4/ServiceRequest/53 for viewing the ServiceRequest resource after having the FHIR bundle submitted (note that the reference of "subject" in the ServiceRequest resource does not resolve into a logical reference):

{
    "resourceType": "ServiceRequest",
    "requisition": {
        "system": "http://myexample.com",
        "value": "H2302-10648"
    },
    "status": "active",
    "intent": "original-order",
    "category": [
        {
            "coding": [
                {
                    "system": "http://snomed.info/sct",
                    "code": "108252007"
                }
            ]
        }
    ],
    "priority": "routine",
    "code": {
        "coding": [
            {
                "system": "http://myexample.com",
                "code": "CBC"
            }
        ]
    },
    "subject": {
        "type": "Patient",
        "reference": "urn:uuid:patient"
    },
    "authoredOn": "2023-02-08T08:40:00+08:00",
    "id": "53",
    "meta": {
        "lastUpdated": "2023-02-16T15:39:14Z",
        "versionId": "1"
    }
}

And here is the response of the HTTP GET from /csp/healthshare/fhirserver/fhir/r4/Patient/52 for viewing the Patient resource: