Database-inbound protocol

[Mahmoud Ihaddadene]

Hi,

I’m using a database_inbound Protocol on a thread to to an insert to MSQL table. On this table i have 5 fields .

The source message is an HL7 ORU and i’m extracting only MSH-6, OBR-7,MSH-9,PID-3,OBR-20 using a translation to a VRL , but the insertion on the table is not working and this is what i get in the log
<pre>[cmd :cmd :INFO/0:lis3check_cmd:02/06/2020 14:28:34] Command client went away. Closing connection.
[cmd :cmd :INFO/0:lis3check_cmd:02/06/2020 14:29:33] Receiving a command
[cmd :cmd :INFO/0:lis3check_cmd:02/06/2020 14:29:33] Receiving a command
[cmd :cmd :INFO/0:lis3check_cmd:02/06/2020 14:29:33] Received command: ‘BI_checkBdOut eo_alias enable_all’
[cmd :cmd :INFO/0:BI_checkBdOut:02/06/2020 14:29:33] Doing ‘eo_alias’ command with args ‘enable_all’
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:33] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:33] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:33] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:33] msiExportStats: export for thread: BI_checkBdOut
[cmd :cmd :INFO/0:lis3check_cmd:02/06/2020 14:29:33] Receiving a command
[cmd :cmd :INFO/0:lis3check_cmd:02/06/2020 14:29:33] Command client went away. Closing connection.
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:33] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:33] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:33] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:33] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:35] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:35] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:35] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:35] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:35] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:35] OB-Data queue has NO work
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:35] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:35] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:35] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:35] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:36] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:36] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:36] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:36] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:36] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:36] OB-Data queue has NO work
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:36] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:36] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:36] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:36] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:37] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:37] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has NO work
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:37] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:37] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:37] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:37] msiExportStats: export for thread: BI_checkBdOut
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has NO work
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has 109 msgs
[pd :thrd:INFO/1:BI_checkBdOut:02/06/2020 14:29:37] OB-Data queue has NO work
[pti :sche:INFO/2:BI_checkBdOut:02/06/2020 14:29:37] Performing apply callback for thread 3
[msi :msi :DBUG/1:BI_checkBdOut:02/06/2020 14:29:37] msiExportStats: export for thread: BI_checkBdOut</pre>
I added  my translation moe.xlt , BI-BD1.vrl and a config.doc for my insert query.

Thank you in advance.

Regards

Mahmoud

 

Attachments:

You must be logged in to view attached files.

Topic

[Mahmoud Ihaddadene]

i renamed the .xlt and .vrl with .txt

Attachments:

You must be logged in to view attached files.

[Levy Lazarre]

Hi Mahmoud,

Looking at your config file:  In order to insert records in a database, you need to use PROTOCOL:database-outbound, not inbound.

Also, the “Table Schema:” value is required and you should be able to select one from the list of available ones by clicking on the “…” button.

I hope this helps.

[Mahmoud Ihaddadene]

Hi Levy,

I defined my Database Connection through Options/Site Options/Database Configurations from the GUI but when i tried to use Protocol:database-outbound on my thread nothing showed on Database Connection, but when i use protocol:database-inbound i was able to see my Database connection (see inbound-outbound.doc)

Thank you

Attachments:

You must be logged in to view attached files.

[Mahmoud Ihaddadene]

Hi Levy,

 

I was able to connect to the SQL database after creating the Table schema List. The problem now is the following error:

[dbi :elog:INFO/0:BI_checkBdOut:02/08/2020 18:05:12] [0.0.943484155] Setting error context for message
[dbi :elog:INFO/1:BI_checkBdOut:02/08/2020 18:05:12] [0.0.943484155] Context: BADDATA caught exception trying to processMessageFromCloverleaf: Failed to write message to database: Invalid column name ‘dt_msg’.. Previous recovery db state is 11.

 

I’m using the the Sql statement:

INSERT INTO [dbo].[tbl_Msg_Statut]
(
dt_msg,
statut,
msg_id,
dossier,
accession_no
)
VALUES
(
dt_msg,
statut,
msg_id,
dossier,
accession_no
)

Thank you

[Levy Lazarre]

Hi Mahmoud,

In the VALUES() list, you need to prefix each value with the Table schema that you selected.

So, if “Test” is the selected schema, you would have

Test.dt_msg,

Test.statut,

Test.msg_id, …

Give it a try and see what happens.

[Mahmoud Ihaddadene]

Hi Levy,

Here is the error i’m getting:
<pre>[java:wrte:ERR /0:BI_checkBdOut_0:02/11/2020 15:36:35] Call to “doMsg” returned error string “BADDATA caught exception trying to processMessageFromCloverleaf: Failed to write message to database: The multi-part identifier “moe.dt_msg” could not be bound.”. Returning bad send to engine.
[dbp :wrte:ERR /0:BI_checkBdOut_0:02/11/2020 15:36:49] The multi-part identifier “moe.dt_msg” could not be bound.</pre>
Did you try it on your end ? and how you set it.

 

Thank you in advance

[Levy Lazarre]

Hi Mahmoud,

It seems like your table name in the query (moe) is wrong, so Cloverleaf is not able to find the column.

There must be a problem with your configuration.

[Mahmoud Ihaddadene]

Hi Levy,

 

Thank you for your response. I will do and get back to you.

I found your post in 2018 but without the *.png files. Is it possible for you to make a print screen for the xlate and the proprietes for the outbound protocol.

Best Regards

[Levy Lazarre]

As requested, the following screen snippets are uploaded:

xlate_setup.png  – demonstrates how to set up the Xlate. The Output Record Format must be: Database Schema and there you select your DB connection and the Table you are going to insert into.

xlate.png  – the Xlate itself, showing the Copy operations from input to output. Because you selected the Table Schema in the above step, all the column names are automatically prefixed with the Table name in the Output Message Format pane.

db_outbound_props_sql.png – an example of Database Outbound Protocol Properties showing the use of a SQL statement to perform an insert in a table. Note that the Table Schema: must be selected (it’s required by the protocol).

db_outbound_props_sproc.png – another example of Database Outbound Protocol Properties showing the use of a stored procedure call to insert a record in a table and receive an Out parameter back from the DB.

 

Attachments:

You must be logged in to view attached files.

[Mahmoud Ihaddadene]

Hi levy,

Thank you for your pint screen. It’s working now.

Have a great day

 

 

[Author]

Replies