Topic
  • 6 replies
  • Latest Post - ‏2013-02-28T00:32:27Z by Saruton
Saruton
Saruton
111 Posts

Pinned topic 'COUNT field incorrect' error on ODBCRun operator

‏2013-02-25T08:11:23Z |
Hi ALL,
ODBCRun operator which executes SELECT statement outputs error as follows:

SQL Code = 25404
SQL Message = 'COUNT field incorrect'
SQL State = '07002'

I haven't ever seen this error and check the message and state.
Some references says:


The number of parameters specified in SQLBindParameter was less than the number of parameters in the SQL statement contained in *StatementText.


The target table has 60 columns and SQL statement selects all of them. 'WHERE' statement is very simple.


( stream < SmpCommonTuple.T_ITS_EVENT_CACHE > RetrievedEventCache; stream < SmpCommonTuple.T_SQL_ERROR > SelectError ) = ODBCRun( RunCommander ) 
{ param   connectionDocument: 
"connections.xml"; connection: 
"myconnection"; access: 
"qm_select_event_cache"; base_time: RunCommander.base_time; 
}



<access_specification name=
"qm_select_event_cache"> <statement statement=
" SELECT SCHEDULED_TIME, .... (abbreviation) .... FROM MYTABLE WHERE SCHEDULED_TIME &lt;= ? 
" /> <parameters> <parameter name=
"base_time" type=
"rstring" length=
"29" /> </parameters> <uses_connection connection=
"myconnection" /> <native_schema> <column name=
"scheduled_time" type=
"VARCHAR" length=
"29" /> .... (abbreviation) .... </native_schema> </access_specification>


I tried with reducing the 'SELECT' target columns but the situation didn't change.
Is it able to specify SQLBindParameter in Connection Specification Document? If it's unavailable, how should I resolve the error?

Thank you for your help in advance.
  • Saruton
    Saruton
    111 Posts

    Re: 'COUNT field incorrect' error on ODBCRun operator

    ‏2013-02-25T09:23:46Z  
    I'm sorry, there are some mistakes on describing the situation.
    The situation described above occurs when I remove WHERE clause from <statement> element. At the beginning, this <access_specification> with WHERE clause doesn't emit not only result set tuples but also sql error tuples (Result set must exist). So I temporarily removed WHERE clause and got the situation I described above.

    Thanks,
  • pcbye
    pcbye
    26 Posts

    Re: 'COUNT field incorrect' error on ODBCRun operator

    ‏2013-02-25T21:35:11Z  
    • Saruton
    • ‏2013-02-25T09:23:46Z
    I'm sorry, there are some mistakes on describing the situation.
    The situation described above occurs when I remove WHERE clause from <statement> element. At the beginning, this <access_specification> with WHERE clause doesn't emit not only result set tuples but also sql error tuples (Result set must exist). So I temporarily removed WHERE clause and got the situation I described above.

    Thanks,
    Hello,

    So when you remove the WHERE clause, there are no '?' parameter markers in the statement? In that case you would have to remove the <parameter> from the connection.xml as well, otherwise I believe it would try to bind the parameter to the statement but the statement would have no parameter marker to bind to.

    Please let me know if I am misunderstanding. Also, I noticed the name of your field is date/time related. Is it actually a CHAR/VARCHAR column in the database? If not, I don't believe it would bind correctly (for the case where you were using the WHERE clause). Unfortunately the Database Toolkit does not support datetime columns at this point.
  • Saruton
    Saruton
    111 Posts

    Re: 'COUNT field incorrect' error on ODBCRun operator

    ‏2013-02-26T03:41:31Z  
    • pcbye
    • ‏2013-02-25T21:35:11Z
    Hello,

    So when you remove the WHERE clause, there are no '?' parameter markers in the statement? In that case you would have to remove the <parameter> from the connection.xml as well, otherwise I believe it would try to bind the parameter to the statement but the statement would have no parameter marker to bind to.

    Please let me know if I am misunderstanding. Also, I noticed the name of your field is date/time related. Is it actually a CHAR/VARCHAR column in the database? If not, I don't believe it would bind correctly (for the case where you were using the WHERE clause). Unfortunately the Database Toolkit does not support datetime columns at this point.
    Thanks for your help!

    So when you remove the WHERE clause, there are no '?' parameter markers in the statement? In that case you would have to remove the <parameter> from the connection.xml as well, otherwise I believe it would try to bind the parameter to the statement but the statement would have no parameter marker to bind to.

    I understood! As you mentioned, I removed the <parameter> element from the connection.xml and could resolve the problem! It was very careless of me.

    Please let me know if I am misunderstanding. Also, I noticed the name of your field is date/time related. Is it actually a CHAR/VARCHAR column in the database? If not, I don't believe it would bind correctly (for the case where you were using the WHERE clause). Unfortunately the Database Toolkit does not support datetime columns at this point.

    'SELECT' statement on ODBCRun operator doesn't work with or without 'WHERE' clause. Specifically, ODBCRun doesn't emit any tuples(SQL answer set or SQL error).
    As you guess, the table has some timestamp columns and one of them (scheduled_time) is used in 'WHERE' clause and others are used as answer set columns.
    In the SPL application, I can insert records containing those datetime columns by ODBCAppend, and also delete records with 'WHERE' clause (WHERE SCHEDULED_TIME <= ?) by ODBCRun operator. 'SELECT' statement is ran by ODBCRun in the same way as 'DELETE', but it doesn't work.

    I know timestamp is not listed in 'Table 1. SQL to SPL type mapping'(Column element) But we know, from the experiences, that we can retrieve timestamp columns as answer set of 'SELECT' statement if we handle it as VARCHAR like following:

    
    <native_schema> <column name=
    "scheduled_time" type=
    "VARCHAR" length=
    "29" /> ... </native_schema>
    


    Let me ask some questions, please.

    Q-1) As a criterion in WHERE clause, we should not use timestamp column, so I will change column type to int64 by convert timestamp to milli sec elapsed since midnight Jan.1st 1970 UTC. Is this decision right? Are there any recommendations?

    Q-2) As answer set columns, can we use timestamp columns by the specification shown above? Or we should change timestamp columns to other type even if they're in answer set columns not WHERE clause?

    I appreciate your help in advance.
  • Saruton
    Saruton
    111 Posts

    Re: 'COUNT field incorrect' error on ODBCRun operator

    ‏2013-02-26T04:35:41Z  
    • Saruton
    • ‏2013-02-26T03:41:31Z
    Thanks for your help!

    So when you remove the WHERE clause, there are no '?' parameter markers in the statement? In that case you would have to remove the <parameter> from the connection.xml as well, otherwise I believe it would try to bind the parameter to the statement but the statement would have no parameter marker to bind to.

    I understood! As you mentioned, I removed the <parameter> element from the connection.xml and could resolve the problem! It was very careless of me.

    Please let me know if I am misunderstanding. Also, I noticed the name of your field is date/time related. Is it actually a CHAR/VARCHAR column in the database? If not, I don't believe it would bind correctly (for the case where you were using the WHERE clause). Unfortunately the Database Toolkit does not support datetime columns at this point.

    'SELECT' statement on ODBCRun operator doesn't work with or without 'WHERE' clause. Specifically, ODBCRun doesn't emit any tuples(SQL answer set or SQL error).
    As you guess, the table has some timestamp columns and one of them (scheduled_time) is used in 'WHERE' clause and others are used as answer set columns.
    In the SPL application, I can insert records containing those datetime columns by ODBCAppend, and also delete records with 'WHERE' clause (WHERE SCHEDULED_TIME <= ?) by ODBCRun operator. 'SELECT' statement is ran by ODBCRun in the same way as 'DELETE', but it doesn't work.

    I know timestamp is not listed in 'Table 1. SQL to SPL type mapping'(Column element) But we know, from the experiences, that we can retrieve timestamp columns as answer set of 'SELECT' statement if we handle it as VARCHAR like following:

    <pre class="jive-pre"> <native_schema> <column name= "scheduled_time" type= "VARCHAR" length= "29" /> ... </native_schema> </pre>

    Let me ask some questions, please.

    Q-1) As a criterion in WHERE clause, we should not use timestamp column, so I will change column type to int64 by convert timestamp to milli sec elapsed since midnight Jan.1st 1970 UTC. Is this decision right? Are there any recommendations?

    Q-2) As answer set columns, can we use timestamp columns by the specification shown above? Or we should change timestamp columns to other type even if they're in answer set columns not WHERE clause?

    I appreciate your help in advance.
    I'm going to change scheduled_time column type from TIMESTAMP to BIGINT to handle date/time on SPL application as int64 type.
    What about this?
  • Saruton
    Saruton
    111 Posts

    Re: 'COUNT field incorrect' error on ODBCRun operator

    ‏2013-02-26T09:01:28Z  
    • Saruton
    • ‏2013-02-26T04:35:41Z
    I'm going to change scheduled_time column type from TIMESTAMP to BIGINT to handle date/time on SPL application as int64 type.
    What about this?
    I changed all timestamp columns in the solidDB table to BIGINT, and tested. But the situation didn't improved.

    At this moment, the table contains some records as follows:

    
    [streamsadmin@myhost Desktop]$ solsql -e 
    "select scheduled_time from its_event_cache" 
    "my_solid_db" solid solid IBM solidDB SQL Editor (teletype) - Version: 7.0.0.0 Build 2011-10-14 Copyright Oy International Business Machines Ab 1993, 2011. SCHEDULED_TIME -------------- 1361866308419 1361866308420 1361866308421 1361866308422 1361866308423 1361866458722 1361866458724 1361866458726 1361866458730 1361866458732 10 rows fetched.
    


    Log of ODBCRun operator, which runs 'SELECT' statement is as follows (Trace output level=trace):

    26 Feb 2013 17:15:16.716 [1584] INFO #splapptrc,J[19],P[327],Emission.RetrievedEventCache,spl_operator M[RetrievedEventCache.cpp:process:195] - Beginning of result set processing.
    26 Feb 2013 17:15:18.715 [1584] DEBUG #splapptrc,J[19],P[327],Emission.RetrievedEventCache,spl_operator M[RetrievedEventCache.cpp:process:133] - Process Tuple {base_time=1361866518715}

    As you can see above, parameter 'base_time'(= SCHEDULED_TIME) is bigger than current records, so ODBCRun must returns by "WHERE SCHEDULED_TIME <= ?", I expected.
    Additionally, I checked the output of ODBCRun operator. It contains only Window Marker Punctuations. It says SQL runs normally and answer set contains no records, isn't it?

    I also checked generated .cpp source and found prepared statement but couldn't find any suspicious points :
    
    
    // Prepare statement rc = SQLPrepare(hstmt[0], (SQLCHAR *) 
    "     SELECT      SCHEDULED_TIME,      SOURCEDEVICEID,              UDPFWMESSAGEID,      GNHEADER,      DEVICETYPE,      TRIGGERMESSAGEID,      EVENT_ID,      EVENT        _TIME,      EVENT_TYPE,      DEVICE_ID,      START_TIME,      END_TIME,      PROCESSED_TIME,              SEVERITY,      LATITUDE,      LONGITUDE,      POWER_USAGE,      EVENT_VALUE1,      EVENT_VALUE2,              EVENT_VALUE3,      EVENT_VALUE4,      EVENT_VALUE5,      EXTENDED_KEYS,      EXTENDED_VALUES,              LAST_MODIFIED,      STATION_ID,      DEVICE_TYPE,      SEQUENCE_NO,      DATA_VERSION,      F        REQUENCY,      RELIABILITY,      IS_NEGATION,      ELEVATION,      GRID_S,      ACTION_TYPE,              NOTIFY_CONTENTS_DEVICE_TYPE,      NOTIFY_CONTENTS_VEHICLE_TYPE,      NOTIFY_CONTENTS_EVENT_TYPE,              NOTIFY_DELAY,      NOTIFY_PERIOD,      NOTIFY_INTERVAL,      COLLECT_INTERVAL_ON,      COLLECT        _INTERVAL_OFF,      TARGET,      TARGET_DEVICE_TYPE,      TARGET_VEHICLE_TYPE,      TARGET_SERVICE        _TYPE,      TARGET_SERVICE_INFO_ID,      CENTER_GRID_TYPE,      CENTER_GRID,      CENTER_LATITUDE,              CENTER_LONGITUDE,      ALTITUDE,      HEADING,      HEADINGFORWARD,      HEADINGBACKWARD,              HEADINGRIGHT,      HEADINGLEFT,      ACTIONGRID          FROM      ITS_EVENT_CACHE     WHERE              SCHEDULED_TIME <= ?    ", SQL_NTS);
    


    Hmm, I can't come up with a good idea...
    Is there any other way to catch the actual SQL statement during run time?
  • Saruton
    Saruton
    111 Posts

    Re: 'COUNT field incorrect' error on ODBCRun operator

    ‏2013-02-28T00:32:27Z  
    • Saruton
    • ‏2013-02-26T09:01:28Z
    I changed all timestamp columns in the solidDB table to BIGINT, and tested. But the situation didn't improved.

    At this moment, the table contains some records as follows:

    <pre class="jive-pre"> [streamsadmin@myhost Desktop]$ solsql -e "select scheduled_time from its_event_cache" "my_solid_db" solid solid IBM solidDB SQL Editor (teletype) - Version: 7.0.0.0 Build 2011-10-14 Copyright Oy International Business Machines Ab 1993, 2011. SCHEDULED_TIME -------------- 1361866308419 1361866308420 1361866308421 1361866308422 1361866308423 1361866458722 1361866458724 1361866458726 1361866458730 1361866458732 10 rows fetched. </pre>

    Log of ODBCRun operator, which runs 'SELECT' statement is as follows (Trace output level=trace):

    26 Feb 2013 17:15:16.716 [1584] INFO #splapptrc,J[19],P[327],Emission.RetrievedEventCache,spl_operator M[RetrievedEventCache.cpp:process:195] - Beginning of result set processing.
    26 Feb 2013 17:15:18.715 [1584] DEBUG #splapptrc,J[19],P[327],Emission.RetrievedEventCache,spl_operator M[RetrievedEventCache.cpp:process:133] - Process Tuple {base_time=1361866518715}

    As you can see above, parameter 'base_time'(= SCHEDULED_TIME) is bigger than current records, so ODBCRun must returns by "WHERE SCHEDULED_TIME <= ?", I expected.
    Additionally, I checked the output of ODBCRun operator. It contains only Window Marker Punctuations. It says SQL runs normally and answer set contains no records, isn't it?

    I also checked generated .cpp source and found prepared statement but couldn't find any suspicious points :
    <pre class="jive-pre"> // Prepare statement rc = SQLPrepare(hstmt[0], (SQLCHAR *) " SELECT SCHEDULED_TIME, SOURCEDEVICEID, UDPFWMESSAGEID, GNHEADER, DEVICETYPE, TRIGGERMESSAGEID, EVENT_ID, EVENT _TIME, EVENT_TYPE, DEVICE_ID, START_TIME, END_TIME, PROCESSED_TIME, SEVERITY, LATITUDE, LONGITUDE, POWER_USAGE, EVENT_VALUE1, EVENT_VALUE2, EVENT_VALUE3, EVENT_VALUE4, EVENT_VALUE5, EXTENDED_KEYS, EXTENDED_VALUES, LAST_MODIFIED, STATION_ID, DEVICE_TYPE, SEQUENCE_NO, DATA_VERSION, F REQUENCY, RELIABILITY, IS_NEGATION, ELEVATION, GRID_S, ACTION_TYPE, NOTIFY_CONTENTS_DEVICE_TYPE, NOTIFY_CONTENTS_VEHICLE_TYPE, NOTIFY_CONTENTS_EVENT_TYPE, NOTIFY_DELAY, NOTIFY_PERIOD, NOTIFY_INTERVAL, COLLECT_INTERVAL_ON, COLLECT _INTERVAL_OFF, TARGET, TARGET_DEVICE_TYPE, TARGET_VEHICLE_TYPE, TARGET_SERVICE _TYPE, TARGET_SERVICE_INFO_ID, CENTER_GRID_TYPE, CENTER_GRID, CENTER_LATITUDE, CENTER_LONGITUDE, ALTITUDE, HEADING, HEADINGFORWARD, HEADINGBACKWARD, HEADINGRIGHT, HEADINGLEFT, ACTIONGRID FROM ITS_EVENT_CACHE WHERE SCHEDULED_TIME <= ? ", SQL_NTS); </pre>

    Hmm, I can't come up with a good idea...
    Is there any other way to catch the actual SQL statement during run time?
    I could resolve the problem by adding 'SELECT' columns one by one.
    There might be some wrong expression in connection specification document, I guess. But I cannot understand why runtime error doesn't occur if SQL error is contained.