sqlite3_bind_text on select, different result on prepared vs string SQL statement

Question

I have a problem with the sqlite3_bind_text function when I try to use it for select.

The intention is to get the newest value in a 10 min time slot of my data.

If I use a prepared statement and bind my value the result is different compared to a normal string with SQL syntax.

The SQL syntax of the two tests 'should' be the same.

When the code runs I get the following output:

test 1 = 0.000000 AnalogRPM        <-- Error
test 2 = 7.700000 7.69999980926514 <-- Correct value

It seems to me that my bound statement returns the name of the column instead of the value (as if the value is inserted as 'AnalogRPM'

Has anyone of you experienced anything similar? or can you see any faults in my code?

Any feedback is appreciated :)

char str[1000];
sqlite3_stmt *test1;

/** First test, use prepared statement to get double value */
snprintf(str, sizeof(str),
"select ? from DATA WHERE ts_sec BETWEEN ? AND ? ORDER BY rowid DESC LIMIT 1");

/** All 'rc' are check in my code, i just left them out to make it easier to read */
rc = sqlite3_prepare_v2(db_livedata, str, -1, &test1, 0);

if(rc != SQLITE_OK)
    printf("SQL error on line:%d msg:%s \n",__LINE__, sqlite3_errmsg(db_livedata));

rc = sqlite3_bind_text( test1, 1, "AnalogRPM",-1,0);
rc = sqlite3_bind_int(  test1, 2, stat_time.tv_sec - 600);
rc = sqlite3_bind_int(  test1, 3, stat_time.tv_sec);

do
 {
    rc = sqlite3_step( test1);
    switch( rc )
    {
       /** No more data */
       case SQLITE_DONE:
          break;

       /** New data */
       case SQLITE_ROW:
       {
          uint16_t size = sqlite3_column_count( test1);

          if(size == 1) // should always be one
          {
             value   = sqlite3_column_double( test1, 0);
             printf("test 1 = %f %s\n",value, sqlite3_column_text(test1, 0));
          }
       }
       break;

       default:
          break;
    }
}while( rc==SQLITE_ROW );


/** Second test use normal string for prepare */
sqlite3_stmt *test2;
snprintf(str, sizeof(str),
"select AnalogRPM from DATA WHERE ts_sec BETWEEN %d AND %d ORDER BY rowid DESC LIMIT 1"
,stat_time.tv_sec - 600, stat_time.tv_sec);

rc = sqlite3_prepare_v2(db_livedata, str, -1, &test2, 0);

if(rc != SQLITE_OK)
    printf("SQL error on line:%d msg:%s \n",__LINE__, sqlite3_errmsg(db_livedata));

do
{
    rc = sqlite3_step( test2);
    switch( rc )
    {
        /** No more data */
        case SQLITE_DONE:
            break;

        /** New data */
        case SQLITE_ROW:
        {
            uint16_t size = sqlite3_column_count( test2);
            if(size == 1)
            {
                value   = sqlite3_column_double( test2, 0);
                printf("test 2 = %f %s\n",value, sqlite3_column_text(test2, 0));
            }
        }
        break;

        default:
            break;
    }
}while( rc==SQLITE_ROW );

Answer 1

First version is essentially

SELECT 'AnalogRPM' ...

while the second is

SELECT AnalogRPM ...

The difference is the expression being either a string literal or a column name.

You cannot use variable binding for column names. Column names need to be known at SQL statement compile time.