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

    RDBMS Interface-BAD bind Column iPosition 4

    GeraldvBerkel

      Hello,

       

      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

      FROM TABLEX

      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:

      timestampaliasvalue
      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