Stream: implementers
Topic: SQL Server
Paul S (Feb 01 2019 at 01:45):
Can anyone share SQL scripts for FHIR STU3 to generate tables? I am working on a project which requires parsing of JSON to SQL Server DB.
Grahame Grieve (Feb 01 2019 at 02:00):
there are no standard scripts; sql patterns for nromalization, polymorphism etc vary widely and are religious debates. You could ask on the analytics on FHIR track where a take on this is being worked on
Paul S (Feb 01 2019 at 02:01):
Thanks, I shall ask there.
John Silva (Feb 01 2019 at 10:06):
If you are talking about Microsoft SQL Server, you could look at the option of storing the JSON in a blob (image column) as well; maybe with a few relation columns for key properties (e.g. the resource's internal ID property) I believe SQL server also supports querying a relational table and returning the results as JSON, e.g. "SELECT from <table> as JSON" (and there are ways to determine the specific JSON structure to return as well.
Brian Postlethwaite (Feb 02 2019 at 11:37):
Or use the SQL xml datatype.
Paul S (Feb 02 2019 at 15:41):
@Brian Postlethwaite Could you please elaborate or point me to the solution?
Mikko Koppanen (Feb 28 2022 at 21:30):
I am setting up Hapi with SQL server and keep hitting "org.hibernate.QueryException: Ordinal parameter not bound : 2".
It very well might be a problem with my setup but I get the following trace message "About to execute SQL: SELECT top(?) t0.RES_ID FROM HFJ_RESOURCE t0 WHERE ((t0.RES_TYPE = ?) AND (t0.RES_DELETED_AT IS NULL))".
Looking at https://github.com/hapifhir/hapi-fhir/blob/master/hapi-fhir-jpaserver-base/src/main/java/ca/uhn/fhir/jpa/search/builder/sql/SearchQueryExecutor.java#L50 this is not used at all and the query gets parameters from "myGeneratedSql.getBindVariables()" (in my case it contains one entry rather than the expected two).
I guess my question is: should it? Or should I look somewhere else?
Last updated: Apr 12 2022 at 19:14 UTC
