Using sqllite to store a HL7 message

Clovertech Forums Read Only Archives Cloverleaf Cloverleaf Using sqllite to store a HL7 message

  • Creator
    Topic
  • #53047
    Michael Vork
    Participant

      Hi there,

      I am trying to store a HL7 message in a sqllite database.

      If I do this, the message which should be stored is truncated on the position in the message where a space or a r occurs.

      I tried to bypass this by defining the storage field as clob. That did not help.

      How do I pursuade the insert sql to take the full message instead of just a part of it?

      Example:

      Create a table

      CREATE TABLE MSGBUFFER (MSGID varchar, MSG clob)

      Set MSGID

      Set MSG is

      Try to save the message:

      INSERT INTO MSGBUFFER VALUES (‘$MSGID’, ‘$MSG’)

      Wih this query only the MSH segment is stored in the MSG database-field.

    Viewing 6 reply threads
    • Author
      Replies
      • #76362
        Tom Rioux
        Participant

          Have you tried “blob” instead of “clob”?

          Tom Rioux

        • #76363
          Ian Morris
          Participant

            I use a blob to store the messages.  We don’t have problems with r.

          • #76364
            Michael Vork
            Participant

              Hello responders,

              Yes, I tried blob as well with the same result.

              If I echo the $MSG just before the sql statement is executed, it contains the full message. It appears that during the insert action the message it truncated on the r. I also tried to embed the whole message between double quotes, but that did nog work either.

              @Ian, could you publish the part of your tcl source in which the insert is executed?  I must be overlooking something.

              Thanks in response.

              Micha

            • #76365
              Michael Vork
              Participant

                Responders,

                The problem I described occured earlier in the proces.

                I prepared the record that I want to insert  as follows:

                set rec

                  and inserted it as follows:

                  INSERT INTO MSGBUFFER VALUES (‘$MSGID’, ‘$MSG’)

                  The “set rec

                  ” was the problem.

                  I changed this to:

                  set rec [concat ‘$MSGID’, ‘$MSG’]

                  followed by

                  INSERT INTO MSGBUFFER VALUES ($rec)

                  This results in the total MSG saved in the database.

              • #76366

                For future reference, here is an example script of one way to do it.

                Schema:

                [code]—

                -- Max Drown (Infor)

              • #76367
                Charlie Bursell
                Participant

                  Regardless of how you are doing it, it will bite you.  If you have a message with a name like O’Hair, etc. it will bite.

                  I always do something like this before insert.

                  set msg [string map

                    $msg]

                    Note the ” is not quotation but double single quotes

                    When any database sees double single quotes in the data it treats as a single quote.

                • #76368
                  David Barr
                  Participant

                    Charlie Bursell wrote:

                    Regardless of how you are doing it, it will bite you.

                Viewing 6 reply threads
                • The forum ‘Cloverleaf’ is closed to new topics and replies.