Sqlite nested select

GeorgiosKoropoulis

Hi ,
I am using sqlite to hold a table as such :
CREATE TABLE IF NOT EXISTS table_name (
id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL
);

table is created ok using QSqlQuery prepare() followed by exec().
My problem arises when I try to add an item turning following statement:

const auto addanItem = QLatin1String(R"(INSERT INTO table_name (id,title) VALUES (:id,: title))");
QString queryStr (addanItem);

QSqlQuery queryAdd;
queryAdd.prepare(queryStr); // this is succesful

queryAdd.bindValue(":id", "(SELECT id FROM table_name WHERE title = 'someNewTitle')");
queryAdd.bindValue(":title", "someNewTitle");

bool success = queryAdd.exec(); // this fails with "Unable to fetch row", "datatype mismatch"...

So question is : Can we use nested queries such as (in SQLITE terminology )
"INSERT OR IGNORE INTO table_name (id,title) VALUES ((SELECT id FROM table_name WHERE title='someNewTitle'),'someNewTitle)';

I tested it with plain SQLITE commands and seems working OK

Christian Ehrlicher

@GeorgiosKoropoulis said in Sqlite nested select:

queryAdd.bindValue(":id", "(SELECT id FROM table_name WHERE title = 'someNewTitle')");

How should this work? You can only bind values, not subselects.

GeorgiosKoropoulis

@Christian-Ehrlicher said in Sqlite nested select:

ly bind values, not subselects.

Many thanks! That s exactly what I was asking

JonB

@GeorgiosKoropoulis
If you want to use whatever SQLite constructs in queries you have, like INSERT OR IGNORE INTO ... or SELECT ... FROM/VALUES (SELECT ... FROM), you can but you have to build the text of the query yourself. In this case you may find that binding does not work (that is supported by the driver, so depends what that allows for binding), but you can build that yourself into the text of the query without using binding. Just beware of correct syntax (e.g. quoting) and SQL injection.