opening a non existing Sqlite Db

[MasterQ]

Hi,

is it intended that the attempt to open a non existing sqlite database lead to no error?

auto db =QSqlDatabase::addDatabase("QSQLITE", "test");
db.setDatabaseName("devel.db2");
qDebug() <<"open:"<< db.open();
auto model = QSqlTableModel(nullptr, db);
model.setTable("Dokumente");
qDebug() << "select:" << model.select();
qDebug() << "db.lastError:" << db.lastError();
qDebug() << "model.lastError:"<< model.lastError();
qDebug() << "rowCount:" <<model.rowCount();

in this example the database file devel.db2 does not exist. But I get no error from the db. The model is claiming that the table is not found. But why gives the open() command no error?

open: true
select: false
db.lastError: QSqlError("", "", "")
model.lastError: QSqlError("", "Unable to find table Dokumente", "")
rowCount: 0

[JonB]

@MasterQ
My recollection of SQLite behaviour (I don't use it) is that, by default, if you pass a non-existent filepath to open it creates it as a new, empty database! And opens it. So successful operation, but database is empty. Does the file exist after you have run this when it did not before (not sure where your file will be since you pass a relative path so depends whatever the current directory is at that time)? I think there is some configuration option you can pass when connecting to tell it not to do that if you do not want it to.

[MasterQ]

@JonB

ahh, I see. I can remember to have read about.

It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

thnx

[JonB]

@MasterQ said in opening a non existing Sqlite Db:

It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

I think you are wrong here, and you can change the behaviour to "not create" if you wish, including from Qt. I suspect there is an old post on this forum for this, but good luck finding it! See QSqlDatabase::setConnectOptions(). And I think the option for SQLite is QSQLITE_OPEN_READONLY

If set, the database is open in read-only mode which will fail if no database exists. Otherwise the database will be opened in read-write mode and created if the database file does not yet exist (default)

[SGaist]

Hi,

You can also check the existence of the file before opening it.

[MasterQ]

@JonB said in opening a non existing Sqlite Db:

@MasterQ said in opening a non existing Sqlite Db:

It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

I think you are wrong here, and you can change the behaviour to "not create" if you wish, including from Qt. I suspect there is an old post on this forum for this, but good luck finding it! See QSqlDatabase::setConnectOptions(). And I think the option for SQLite is QSQLITE_OPEN_READONLY

Yes, of course, but I need the database READ/WRITE and not READONLY!!

[MasterQ]

@SGaist said in opening a non existing Sqlite Db:

Hi,

You can also check the existence of the file before opening it.

I now have added such a check before the db is opened

[MasterQ]

@MasterQ

this issue came up during checking another (strange) problem. I will open a new thread for that.

[JonB]

@MasterQ said in opening a non existing Sqlite Db:

Yes, of course, but I need the database READ/WRITE and not READONLY!!

Yes, it's irritating that option says it suppresses the creation but at the expense of opening existing read-only. Looks like the SQLite driver supports this:

public SQLite3::open(string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = ""): void

flags
Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.

SQLITE3_OPEN_READONLY: Open the database for reading only.

SQLITE3_OPEN_READWRITE: Open the database for reading and writing.

SQLITE3_OPEN_CREATE: Create the database if it does not exist.

Qt does not seem to offer these flags, so you could use SQLITE3_OPEN_READWRITE without SQLITE3_OPEN_CREATE.

[MasterQ]

@JonB

the C++ documentation( SQL Database Drivers) says different!

Connection options:

The Qt SQLite plugin honors the following connection options:
Attribute Possible value

QSQLITE_BUSY_TIMEOUT Busy handler timeout in milliseconds (val <= 0: disabled), see SQLite documentation for more information

QSQLITE_OPEN_READONLY If set, the database is open in read-only mode which will fail if no database exists. Otherwise the database will be opened in read-write mode and created if the database file does not yet exist (default)

QSQLITE_OPEN_URI The given filename is interpreted as an uri, see SQLITE_OPEN_URI

QSQLITE_ENABLE_SHARED_CACHE If set, the database is opened in shared cache mode, otherwise in private cache mode

QSQLITE_ENABLE_REGEXP If set, the plugin defines a function 'regex' which can be used in queries, QRegularExpression is used for evaluation of the regex query

QSQLITE_NO_USE_EXTENDED_RESULT_CODES Disables the usage of the extended result code feature in SQLite (for backwards compatibility)

open command:

bool QSqlDatabase::open()

Opens the database connection using the current connection values. Returns true on success; otherwise returns false. Error information can be retrieved using lastError().

See also lastError(), setDatabaseName(), setUserName(), setPassword(), setHostName(), setPort(), and setConnectOptions().

bool QSqlDatabase::open(const QString &user, const QString &password)

This is an overloaded function.

Opens the database connection using the given user name and password. Returns true on success; otherwise returns false. Error information can be retrieved using the lastError() function.

This function does not store the password it is given. Instead, the password is passed directly to the driver for opening the connection and it is then discarded.

See also lastError().

[JonB]

@MasterQ said in opening a non existing Sqlite Db:

the C++ documentation( SQL Database Drivers) says different!

I know! These are the options/constants offered from Qt. That's why I showed the underlying SQLite options and said it's a "shame" they are missing what the SQLite driver supports. [If you are really lucky there might be un undocumented QSQLITE_OPEN_READWRITE Qt constant!]

[JuZhou He]

@MasterQ Check your build directory! Perhaps "devel. db2" is right there!

https://doc.qt.io/qt-6/qsqldatabase.html#setDatabaseName

"For the QSQLITE driver, if the database name specified does not exist, then it will create the file for you unless the QSQLITE_OPEN_READONLY option is set."

I ran your code,"devel. db2" was created。