Announcement

Collapse

NextGen (Mirth) Connect 3.10.0 Released!

NextGen (Mirth) Connect 3.10.0 is now available as an appliance update and on our GitHub page. This release includes better SQL Server database support, security improvements through fixes and library updates, and improvements for the Advanced Clustering plugin with a focus on improving performance of many of the tasks that are carried out on a frequent interval. See the release notes for the list of fixes and updates.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

DB connector SQL question

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

  • DB connector SQL question

    I'm trying to test a simple database connector. I'm using the SQL Server/Sybase driver and have gotten as far as a valid connection. However the following SQL is generating an error:
    INSERT IGNORE INTO usr (
    usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
    usrlpcr, usrcnb, usreml, usrphn, usrpwh, usrtyp,
    usrctyp, usrtlid
    ) values (
    'joe', 'joe user', ' ', 0, 123, ' ',
    '${date.get('yyyy-MM-dd')}', 'WMSVISION', '[email protected]', ' ', ' ', 'EMPL',
    ' ', 'en_US - English')

    The error that is occurring is:
    java.sql.SQLException: Invalid parameter index 1. Query: INSERT IGNORE INTO usr (
    usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
    usrlpcr, usrcnb, usreml, usrphn, usrpwh, usrtyp,
    usrctyp, usrtlid
    ) values (
    'joe', 'joe user', ' ', 0, 123, ' ',
    '?', 'WMSVISION', '[email protected]', ' ', ' ', 'EMPL',
    ' ', 'en_US - English')

    Parameters: [2007-07-13]
    --------------------
    I've tried using a date escape of {d '${date.get('yyyy-MM-dd')}'} but that only changes the error to:
    java.sql.SQLException: Invalid JDBC date escape at line position 181.
    SimpleDbconnect.xml (4872 bytes)

  • #2
    ReB connector SQL question

    What datatype is the usruid column??
    Jon Bartels

    Zen is hiring!!!!
    http://consultzen.com/careers/
    Talented healthcare IT professionals wanted. Engineers to sales to management.
    Good benefits, great working environment, genuinely interesting work.

    Comment


    • #3
      ReB connector SQL question

      Here are how the column types. Note I've noticed that usrdiv was a character field and added quotes but still got the same error.
      SQL> describe usr
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      USRUID NOT NULL VARCHAR2(10)
      USRNAM NOT NULL VARCHAR2(30)
      USRPWD NOT NULL VARCHAR2(10)
      USRACC NOT NULL NUMBER(11)
      USRDIV NOT NULL VARCHAR2(3)
      USRSTS NOT NULL VARCHAR2(1)
      USRLPCR NOT NULL DATE
      USRCNB NOT NULL VARCHAR2(16)
      USREML NOT NULL VARCHAR2(50)
      USRPHN NOT NULL VARCHAR2(25)
      USRFPH NOT NULL VARCHAR2(25)
      USRPWH NOT NULL VARCHAR2(80)
      USRTYP NOT NULL VARCHAR2(4)
      USRCTYP NOT NULL VARCHAR2(4)
      USRTLID NOT NULL VARCHAR2(40)

      Comment


      • #4
        ReB connector SQL question

        Deos the INSERT work fine when you try from any other SQL client application?

        Comment


        • #5
          ReB connector SQL question

          After noticing a missing field and trying a few things here are the latest results:

          If the date is hard coded as the following is, the insert works:
          INSERT IGNORE INTO usr (
          usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
          usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
          usrtyp, usrctyp, usrtlid
          ) values (
          'joe', 'joe user', ' ', 0, '123', ' ',
          '2007-07-13', 'WMSVISION', '[email protected]', ' ', ' ', ' ',
          'EMPL', ' ', 'en_US - English')


          But if we try to use a formatted date using the date.get method the insert fails:
          INSERT IGNORE INTO usr (
          usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
          usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
          usrtyp, usrctyp, usrtlid
          ) values (
          'joe', 'joe user', ' ', 0, '123', ' ',
          '${date.get('yyyy-MM-dd')}', 'WMSVISION', '[email protected]', ' ', ' ', ' ',
          'EMPL', ' ', 'en_US - English')

          FAILURE: Error writing to database:
          SQLException
          Invalid parameter index 1. Query: INSERT IGNORE INTO usr (
          usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
          usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
          usrtyp, usrctyp, usrtlid
          ) values (
          'joe', 'joe user', ' ', 0, '123', ' ',
          '?', 'WMSVISION', '[email protected]', ' ', ' ', ' ',
          'EMPL', ' ', 'en_US - English')

          Parameters: [2007-07-16]

          So it seems to be either a date specific problem or a parameter replacement problem.

          Comment


          • #6
            ReB connector SQL question

            It looks like the problem is the datatype of the parameter. I like to do all datatype conversions within the SQL of the insert/update. In your case, the code

            Code:
            CONVERT(datetime,${date.get('yyyy-MM-dd')})
            should work. This converts a string to a datetime usable by the DBMS. In my case, it's MS SQL Server 2000. Hope that helps a little.
            -Tim
            ___________________________________
            Timothy J. Lewis
            System Engineer
            Synergy Health Information Solutions, LLC

            Comment


            • #7
              ReB connector SQL question

              Thanks! Using CONVERT worked. Here is the final SQL for the destination tab.
              INSERT IGNORE INTO usr (
              usruid, usrnam, usrpwd, usracc, usrdiv, usrsts,
              usrlpcr, usrcnb, usreml, usrphn, usrfph, usrpwh,
              usrtyp, usrctyp, usrtlid
              ) values (
              'joe', 'joe user', ' ', 0, '123', ' ',
              CONVERT(datetime,${date.get('yyyy-MM-dd')}), 'WMSVISION', '[email protected]', ' ', ' ', ' ',
              'EMPL', ' ', 'en_US - English')

              Comment

              Working...
              X