2 Replies Latest reply on Jan 18, 2018 10:01 AM by Roger Palmen

    RDBMS Interface-BAD bind Column iPosition 4




      We have an RDBMS interface in place that is working correctly.

      A simple SQL selection in the Exdesc retrieves a value from Oracle.


      Now I wanted to create a selection where I update tags using concatenated values for the tag namens.

      SELECT T as timestamp,

      CONCAT(FIELD1,'') || '.' || CONCAT(FIELD2,'') as alias,

      V as value


      WHERE DAT IS NULL ORDER BY alias, timestamp


      This SQL was saves as test.sql in de SQL folder (and that was listed in ICU RDBMS SQL Files Directory)


      A test of the SQL statement in Toad4Oracle showed something like:

      16/01/2018 08:47:14NAME.SUB10
      16/01/2018 08:48:00NAME.SUB11
      16/01/2018 08:50:01NAME.SUB12
      16/01/2018 08:46:01NAME.SUB24
      16/01/2018 08:48:01NAME.SUB33


      My goal: to create a tag with the name NAME.SUB1 that will update with the timestamp and values.


      When I create the tag using the point builder I set:

      The name to: NAME.SUB1

      The Point Source to the point source of the RDBMS interface

      Point Type to Float32

      Exdesc to: /ALIAS=alias

      Location1: 1 (scann class defined)

      Location2: 0

      Location3: -1

      Location4: 1

      Location5: 0

      Instrument Tag: test.sql


      The error message that I receive:

      >> Point - NAME.SUB1 : Bad bind column iPosition 4!

      >> Point - NAME.SUB1 : tag refused

      The logging shows the SQL query from the test.sql file so it gets the correct query.


      What am I doing incorrect?

      (could the concatenation cause any problems or am I doing something else incorrectly??)

        • Re: RDBMS Interface-BAD bind Column iPosition 4
          Roger Palmen

          A quick first idea: fix your query:


          Result Columns in SELECT Statements

          • When using the SELECT statement, result columns must be retrieved in the following order: timestamp/value/status. This is shown below:

          SELECT Your_Timestamp_ColumnTitle, Your_Value_ColumnTitle, Your_Status_ColumnTitle FROM TableName...

          • If you want to retrieve columns in a different order the following aliases must be used:
            • PI_TIMESTAMP
            • PI_VALUE
            • PI_STATUS

          Using the above aliases the SELECT statement can be written as follows:

          SELECT Your_Value_ColumnTitle as PI_VALUE, Your_Timestamp_ColumnTitle as PI_TIMESTAMP, Your_Status_ColumnTitle as PI_STATUS FROM TableName...

          (Notice that the Value column is retrieved before the Timestamp column).

          1 of 1 people found this helpful
            • Re: RDBMS Interface-BAD bind Column iPosition 4
              Roger Palmen

              Now almost sure: missing the status column in your query.


              What i overlooked is that the manual states that status is set to 0 automatically when missing, but only if an alias is used in the query:

              When the columns in the SELECT list are aliased, the status column is not mandatory. If status is omitted, the interface writes a status of 0 (Good) to the tag.

              1 of 1 people found this helpful