SQLITE_MISUSE in prepared statement

Question

I'm getting a SQLITE_MISUSE error on the following code, and I am wondering if it might be caused by having the table name be a bound parameter? What are some different causes of SQLITE_MISUE?

const char sqlNeuralStateInsert[] =
    "INSERT INTO ?1(LAYER_ID, NEURON_ID, INPUT_ID, VALUE)"
    "VALUES(?2, ?3, ?4, ?5);";
sqlite3_stmt* stmt1;
rc = sqlite3_prepare_v2(db, sqlNeuralStateInsert, -1, &stmt1, NULL);
if(rc){
    //!< Failed to prepare insert statement
}
sqlite3_bind_text(stmt1, 1, this->getNName().c_str(), -1, SQLITE_STATIC); 
for(uint32_t i = 0; i < m_nlayers; i++){
    sqlite3_bind_int(stmt1, 2, i); // Layer id
    for(uint32_t j = 0; j < m_layers[i]->getNeuronCount(); j++){
        std::vector<double> weights = m_layers[i]->getWeights(j);
        sqlite3_bind_int(stmt1, 3, j); // Neuron id
        for(uint32_t k = 0; k < weights.size(); k++){
            sqlite3_bind_int(stmt1, 4, k);
            sqlite3_bind_double(stmt1, 5, weights[k]);
            rc = sqlite3_step(stmt1);
            printf("%d\n", rc);
        }
    }
}
sqlite3_finalize(stmt1);

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

You're right; you cannot bind the table name:

Generally one cannot use SQL parameters/placeholders for database identifiers (tables, columns, views, schemas, etc.) or database functions (e.g., CURRENT_DATE), but instead only for binding literal values.

You could have trivially tested this hypothesis by hard-coding the table name.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 19 '15 at 21:20

Lightness Races in Orbit

378,754
76
643
1,055

How can I bind a database identifier then? – HSchmale Jun 19 '15 at 21:22
1

@HSchmale: Not sure what part of "you cannot" was unclear? Anyway, If all these tables have the same schema, why are they not _one_ table with your `?1` being a column? In general, if you think the table name needs to be dynamic, it indicates a problem with your design, and that's part of why it's not supported. – Lightness Races in Orbit Jun 19 '15 at 21:27
Then what is a better design, if I wanted to create a table on the fly, then insert into it. – HSchmale Jun 19 '15 at 21:42
Because I'm dumping a neural network to a database, and I might have multiple neural networks that I want to have available or able to pull up on the fly, so each would need to have it's own separate table. Is it bad practice to make new tables on the fly? – HSchmale Jun 19 '15 at 22:16
@HSchmale: I still don't see why this requires multiple, dynamically-created tables. What's wrong with a "network ID" column? – Lightness Races in Orbit Jun 19 '15 at 23:26
I just realized that is better, however wouldn't there be issues with full table scans as the total number of rows increased. – HSchmale Jun 19 '15 at 23:29
1

@HSchmale: Why do you have full table scans? For a start you will obviously have an index on the "network ID" column. I think you've been trying to use multiple tables to "group" subsets of data sharing the same form, which isn't an appropriate use of the feature. That's why you shouldn't be surprised that the technology does not support your intentions. :) The appropriate way to do this is a column to perform that grouping. And with that, your problem just melts away! – Lightness Races in Orbit Jun 19 '15 at 23:39

SQLITE_MISUSE in prepared statement

1 Answers1