Announcement

Collapse
No announcement yet.

Issue with SQL query execution

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

  • Issue with SQL query execution

    I am using Mirth 3.6.1 and in the destination transformation step, I am executing a SQL statement (Microsoft SQL Standard 2012). It works 99.9% but once in a while, it will not populate the information even though the details are present in the database. The weird thing is no error generated. Have someone seen such issue. Or is there something I am doing incorrectly.


    var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
    'net.sourceforge.jtds.jdbc.Driver',
    'jdbc:jtds:sqlserver://localhost:1433/db',
    'user',
    'password'
    );
    var sql = "SELECT LastName, FirstName,MiddleName FROM Operator where code = '" +optid+"'";
    results = dbConn.executeCachedQuery(sql);

    if(results.next())
    {
    optlname = results.getString("LastName");
    optmname = results.getString("MiddleName");
    optfname = results.getString("FirstName");
    optname = optlname + ' ' + optfname + ' ' + optmname ;

    } else
    {
    optname = 'undefined user';
    }
    }
    finally
    {
    if(dbConn)
    dbConn.close();
    }
    Last edited by Mits87; 07-19-2019, 11:51 AM.

  • #2
    Hello,


    can you post the error?
    Thanks


    Best Regards
    Best Regards,
    Alex Neiva

    Comment


    • #3
      Unfortunately, there is no error generated. It is just even though the data is available in DB the query still doesn't return it. When I resend the result again mirth finds the record no issue. This only happens roughly 5% of my total transmission.

      Comment


      • #4
        is it a possibility that the ones that fail are ones where "optid" has a single quote in it to escape your SQL query?

        Also I believe you have an extra } in your code

        maybe instead of:
        Code:
        if(results.next())
        {
        	optlname = results.getString("LastName");
        	optmname = results.getString("MiddleName");
        	optfname = results.getString("FirstName");
        	optname = optlname + ' ' + optfname + ' ' + optmname ;
        }
        else 
        { 
        	optname = 'undefined user';
        }
        }
        finally
        {
        	if(dbConn)
        	dbConn.close();
        }
        use:
        Code:
        if(results.size() == 1)
        {
        	results.next();
        	optlname = results.getString("LastName");
        	optmname = results.getString("MiddleName");
        	optfname = results.getString("FirstName");
        	optname = optlname + ' ' + optfname + ' ' + optmname;
        }
        else 
        { 
        	optname = 'undefined user';
        }
        finally
        {
        	if(dbConn)
        	dbConn.close();
        }

        Comment


        • #5
          Are you sure optid is unique?

          Comment


          • #6
            Yes, they are unique and in my query, it will always have only one result returned. I have already taken care of the special character in previous steps.In addition to that, I placed the catch (e) in the try loop. but no error is shown.

            I feel sql might be timing out before it gets the result of the query. is it possible to extend the wait time for query execution?

            Comment

            Working...
            X