Announcement

Collapse
No announcement yet.

prepareStatement/Proc SQL Server/CLOB

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • prepareStatement/Proc SQL Server/CLOB

    I am using SQLServer 2005, and trying to call a stored proc which accepts 2 TEXT types as parameters and simply inserts them into a table with 2 TEXT columns.

    Basically I want to put the HL7 message and the XML message into a row.
    The XML is about 26k, so I need to use TEXT columns.

    I can call the proc like this, but it truncates the params to 4000 characters:
    Code:
    var result = dbConn.executeCachedQuery("{ call spTransformMessage('"+rawData+"', '"+xmlData+"') }"«»);
    I thought I could use prepareStatement like this:
    Code:
    var stmt = dbConn.prepareStatement("{ call spTransformMessage(?,?) }"«»);
    stmt.setClob(1, rawData);
    stmt.setClob(2, xmlData);
    But Mirth throws:
    Code:
    org.mozilla.javascript.EcmaError: TypeError: Cannot find function prepareStatement. (6956a2a1-f9f6-4525-838d-04640939a560#21)
            at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3226)
            at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3216)
            at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:3232)
            at org.mozilla.javascript.ScriptRuntime.typeError1(ScriptRuntime.java:3244)
    ...snip...
    I looked at the JTS driver's docs, and it is supposed to support CLOBs, Prepared Statements, etc.

    Oh, using Mirth 1.6.0

    Any help is greatly appreciated. TIA
    Attached is the Channel XML, BTW. Mirth_SQLSERVER_Channel.xml (14584 bytes)

  • #2
    RerepareStatement/Proc SQL Server/CLOB

    Figured it out.
    You need to use:
    Code:
    var dbConnJava = java.sql.DriverManager.getConnection('jdbc:jtds:«»sqlserver://127.0.0.1:1433/RossTest', 'RossTest', 'test');
    to get a jtds JDBC Connection object which supports prepareCall.

    Thanks

    Comment


    • #3
      RerepareStatement/Proc SQL Server/CLOB

      Thanks Ross for figuring this much out.

      I'm trying to get something similar working with Oracle, but I can't successfully create a CLOB object. Code is below, then the error below that.

      The Oracle Stored Procedure:
      Code:
      CREATE OR REPLACE PROCEDURE "CSP_INS_PATIENTDEMO" (P_DATAXML CLOB«») AS ...
      This is what I have so far in the destination:
      Code:
      // The default DatabaseConnectionFactory does NOT support prepared 
      statements
      // Load the Oracle driver
      java.lang.Class.forName("oracle.jdbc.driver.OracleDriver"«»);
      // Get a connection
      var dbConn = 
      java.sql.DriverManager.getConnection('jdbc:oracle:thin:@192.168.0.10:1521:oracle','test','test');
      // Create a CLOB object
      var xmlData=new oracle.sql.CLOB();
      // Set the contents of the object to the XML message
      xmlData.setString(0,$('message.transformedData'));
      // Create the statement
      var stmt = dbConn.prepareStatement("{ call CSP_INS_PATIENTDEMO(?) }"«»);
      stmt.setClob(1, xmlData);
      // Execute the statement
      stmt.executeUpdate();
      stmt.commit();
      // Clean up
      stmt.close();
      dbConn.close();
      It dies at var xmlData = new oracle.sql.CLOB;
      with org.mozilla.javascript.EcmaReferenceError: "oracle" is not defined

      I'll keep poking around.

      Comment


      • #4
        RerepareStatement/Proc SQL Server/CLOB

        var xmlData=new Packages.oracle.sql.CLOB();

        In order to reference a Java class from Javascript, you need to include "Packages" in front of the fully classified path name. The only exception is for java.lang which is automatically imported.
        Chris Lang

        Comment


        • #5
          RerepareStatement/Proc SQL Server/CLOB

          Thanks Chris, that information gave me what I needed to finish this up.

          When writing BLOBs or CLOBs, it's a two step process, in no particular order:
          1) Create a LOB
          2) Execute SQL to insert data

          In the code below, I create a temporary LOB using the connection, then execute a stored proc to insert it into a table.

          I've also done it the other way round, inserting null into the table, then getting the column and updating it.

          The first way seems easier:

          Code:
          // The default DatabaseConnectionFactory does NOT support prepared statements
          
          // Load the Oracle driver
          java.lang.Class.forName("oracle.jdbc.driver.OracleDriver"«»);
          
          // Get a connection
          var dbConn = 
          Packages.java.sql.DriverManager.getConnection('jdbc:oracle:thin:@192.168.0.10:1521:oracle','test','test');
          
          // Using the drag and drop stuff in the UI doesn't seem to work
          var xmlData=messageObject.getTransformedData();
          //logger.info("SQL: " + xmlData);
          //logger.info("raw: " + messageObject.getRawData());
          
          // Create temporary clob
          var tempClob = Packages.oracle.sql.CLOB.createTemporary(dbConn, true, Packages.oracle.sql.CLOB.DURATION_SESSION);
          // Open the clob in read write mode
          tempClob.open(Packages.oracle.sql.CLOB.MODE_READWRITE);
          // Get the outputstream to write to
          var tempClobWriter = tempClob.getCharacterOutputStream();
          // Write the xml Data
          tempClobWriter.write(xmlData);
          tempClobWriter.flush();
          // Clean up
          tempClobWriter.close();  
          tempClob.close();
          
          // Create the statement
          var stmt = dbConn.prepareStatement("{ call CSP_INS_PATIENTDEMO(?) }"«»);
          stmt.setClob(1, tempClob);
          // Execute the statement
          stmt.executeUpdate();
          // Clean up
          stmt.close();
          dbConn.close();

          Comment

          Working...
          X