Having trouble creating an SQLITE database

Databases
1

I created an sqlite database and added tables. This is part of an application I wrote to convert a tab separated file to an sqlite database on the fly. Everything was working, perfectly. However, I now find that the following code doesn’t work:

Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child(“MyDatabase.sqlite”)
Try
db.CreateDatabase
Catch error As IOException
MessageBox("The database file could not be created: " + error.Message)
End Try

I get the following message:

An exception of class

SQLiteException was not
handled. The application must
shut down.

Exception Message: unable to open
database file
Exception Error Number: 14

I have checked the permissions and I can read/write to the Desktop, but the database is not being created.

I would appreciate any suggestions regarding where to look in order to fix this.

2

Which version of Xojo? Which OS? Which version of the OS? Does the database already exist at the location? I use the following:

PlanDataDB = New SQLiteDatabase
PlanDataDB.DatabaseFile = PlanDataFolderitem

If PlanDataFolderitem.Exists Then
  ConnectOpen
else
  ConnectCreateTables
end if
1 Like
3

Hi Beatrix,
Thanks
It was working fine until this morning. I have reinstalled Xojo 2023.r2.
I am using a MacBook. I haven’t changed the code in the main program, but the test doesn’t work.

I have the database I created originally, but want to add a table to it. When I run the original code that created the database and added two tables, it no longer works. I tried your approach, and get the same answer:“Unable to open the database” - this is because the database is not created.

16-inch, 2023
Chip Apple M2 Max
Memory 32 GB
Startup disk Macintosh HD
macOS Ventura 13.5.1

4

If your database already exists, why are you trying to create it?

1 Like
5

Nedi is correct.

You can read and follow this link contents:

https://documentation.xojo.com/topics/databases/database_basics_for_beginners.html#database-basics-for-beginners

6

For SQLite, CreateDatabase() works exacly as Connect() if the database exists. So, that’s not the problem.

If you check MacError.h you see this:

dsIOCoreErr = 14, /IO Core Error/

For SQlite, we see this:

(14) SQLITE_CANTOPEN

The SQLITE_CANTOPEN result code indicates that SQLite was unable to open a file. The file in question might be a primary database file or one of several temporary disk files.

7

Sorry…my little use of SQLite made me thing wrong…

8

Go to your desktop folder, look for any MyDatabase* files. Maybe there are some “detritus” left from other experiments unexpectedly interfering?

9

This code works without error by testing:

  • no MyDatabase.sqlite on Desktop (MyDatabase.sqlite is created)
  • with MyDatabase.sqlite on Desktop
  • with MyDatabase.sqlite locked on Desktop
  • an image.png renamed as MyDatabase.sqlite on Desktop

so my guess is that your problem is somewhere else.

MacOS 13.5.1, Xojo2023R2

10

I have written two functions - one to create a database table from the first line of a tab separated file, the other to add the data to the sqlite database. I wanted to add information from two other TSV files to the existing database. It seems easier to use existing coder rather than write new code to add the tables. The I tried to do that, I found an error.

If I use something like:

db.DatabaseFile = New FolderItem(“MyDB.sqlite”)

it works.

When I try:

dbFile = SpecialFolder.Documents.Child(“MyDatabase.sqlite”)

The code works
// DB As SQLiteDatabase is a property on the App object
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile

If App.DB.CreateDatabaseFile Then
MessageBox(“Database created”)
// Use the database
Else
MessageBox(“Database NOT created”)
End If

The code fails. The problem seems to be that I am trying to create a database in a different directory.

I now realise that the problem occurred after Update the operating system → macOS Ventura 13.5.1 (22G90)

It seems something broke after that.

Thank you to all who made suggestions.

11

Having rebooted my computer several times, the problem seems to have resolved itself. If the problem returns, will explore more fully and report back. Thanks for the advice.

12

Hi,
Thanks for the input - I had no idea that Sqlite created temporary files before creating the actual database. I will spend some time looking at the link you provided. The problem I had was related directly to the inability for db.create to create the database, but has now been resolved. The rate thing about the forum is the sharing knowledge.
Regards to all who are helping to educate me.