SQLite db in Windows not opening

I have an app that works fine under Mac. Move the project to Windows 8 under R2.4. Copied the db from the mac to the windows and can view it using SQLite Browser. I can also add it as a resource to the app; however, when running either from the IDE or from the build, I can see that the database is opened, but the sql statement returns the table does not exist. Ideas?

Without seeing any of your code, I’m going to guess that you are not actually opening the DB because your path to it is likely different between Mac and Windows. Verify that path your FolderItem points to and that Exists = True.

Dim dbFile As FolderItem
dbfile = New FolderItem(“DBFileListing.sqlite”)
if dbfile <> Nil then

Dim sql as String
Dim data1 As RecordSet
mDb = New SQLiteDatabase
mDb.DatabaseFile = dbFile

If mDb.CreateDatabaseFile Then
  mIsConnected = True
Else
  mIsConnected = False
  MsgBox("Error connecting to Bible:  " + mDb.ErrorMessage)
  
End If

sql = "SELECT distinct display FROM akjv; "

me.DeleteAllRows
If mDB.Error Then
  MsgBox("DB Error: " + mDB.ErrorMessage)
  Return
End If

The error: “DB Error: No such Table akjv”

The database can be opened and viewed in SQLite Browser

Okay, I guess I don’t understand how R2.4 handles resources in the dev folder. When I ran the app in the IDE, it created the Debug… folder, and ran from there. While the error msg was on the screen, I copied the database to the Debug… folder. And it picked it up.

delete the previous… forgot about the ‘build step’ in R2.4

Are you expecting DBFileListing.sqlite to exist? If it doesn’t, your code will create a new DB file, which would explain why you’d get a “no such table” error.

If you are trying to connect to an existing DB, you’ll need to make sure that dbFile properly points to it (the location relative to the debug app varies on Mac vs. Windows). I’d also call the Connect method (not CreateDatabaseFile) as that more clearly describes your intent. Just set a breakpoint on this line:

dbfile = New FolderItem("DBFileListing.sqlite")

and check its path and Exists property. If you put the DB in the same folder as the project, the above code will work on a Mac because the debug app also appears in the same folder as the project. It won’t work on Windows because the debug app appears in its own folder, so you’d probably want to do something like this:

dbFile = GetFolderItem("").Parent.Child("DBFileList.sqlite")

I had the same problem and I fixed it by stripping the .sqlite extension leaving the name of the file on the computer without an extension and adding the extension .db to my file name in my program.

I’ve done that because I remarked on the properties of my original file the name was containing the extension .db at the end … example if my filename of the DB was “MesHorairesPrefsWin.sqlite”, when you take a look at the properties the name is changed to “MesHorairesPrefsWin.sqlite.db”.

I don’t know why is that happening but stripping the sqlite extension did the job for me.

My Database was created with Navicat for Sqlite and not by my application.

here’s an example of my code

var dbfile As FolderItem

if DebugBuild then // for the debugger the file is on desktop
dbFile = SpecialFolder.Desktop.Child(“MesHorairesPrefsWin01.db”)
else // for the running program the file has to be put in the application folder
dbFile = SpecialFolder.CurrentWorkingDirectory.Child(“MesHorairesPrefsWin.db”)
end if

app.MyPrefsDB = new MyDatabaseClass // MydatabaseClass is a Sqlite database
app.MyPrefsDB.DatabaseFile = dbfile

If dbfile <> Nil and dbfile.exists Then

Try
app.MyPrefsDB.Connect
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

else // it doesn<t work then open the dialog and ask the user to find the file

dbfile = FolderItem.ShowOpenFileDialog("")
Try
app.MyPrefsDB.Connect
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

End If

[quote=483024:@Jean-Maurice Vandergoten]I had the same problem and I fixed it by stripping the .sqlite extension leaving the name of the file on the computer without an extension and adding the extension .db to my file name in my program.

I’ve done that because I remarked on the properties of my original file the name was containing the extension .db at the end … example if my filename of the DB was “MesHorairesPrefsWin.sqlite”, when you take a look at the properties the name is changed to “MesHorairesPrefsWin.sqlite.db”.

I don’t know why is that happening but stripping the sqlite extension did the job for me.

My Database was created with Navicat for Sqlite and not by my application.

here’s an example of my code

var dbfile As FolderItem

if DebugBuild then // for the debugger the file is on desktop
dbFile = SpecialFolder.Desktop.Child(“MesHorairesPrefsWin01.db”)
else // for the running program the file has to be put in the application folder
dbFile = SpecialFolder.CurrentWorkingDirectory.Child(“MesHorairesPrefsWin.db”)
end if

app.MyPrefsDB = new MyDatabaseClass // MydatabaseClass is a Sqlite database
app.MyPrefsDB.DatabaseFile = dbfile

If dbfile <> Nil and dbfile.exists Then

Try
app.MyPrefsDB.Connect
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

else // it doesn<t work then open the dialog and ask the user to find the file

dbfile = FolderItem.ShowOpenFileDialog("")
Try
app.MyPrefsDB.Connect
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

End If[/quote]

I’d speculate that your problem is actually that you have file extension hidden in your file viewer…

https://en.wikipedia.org/wiki/Computer_file :

On most modern operating systems, files are organized into one-dimensional arrays of bytes. The format of a file is defined by its content since a file is solely a container for data, although on some platforms the format is usually indicated by its filename extension, specifying the rules for how the bytes must be organized and interpreted meaningfully.

So, saving a file without extension is the beggining of troubles.
Edit:
How do you associate an icon to this file ?

How do you associate that file to your application without a file extension ?

Because you use SQLite, I will advice .sqlite as a file extension.

Also, since the last seven years, I have an application using SQLite (and the file extension is .sqlite) that is used somewhere. The curator have a Macintosh and the Client use a Windows XP (sic !). The data base file is often moved from MacOS to Windows and vice versa without any read nor write trouble.

I hope this will help.

BTW: what r2.4 xojo version are-you using ?

It’s like James said, My file viewer adds an hidden extension .db to my file name. Knowing that, now it’s working without any problem with the .sqlite extension !

Thanks everybody !!

Emile, I use r 3.1 :slight_smile:

By contrast I use no extensions on any of my SQLite database files and have no problems. I can move the set of database files between app versions on Mac/Win/Lin without any issues. Extensions are unnecessary.