This will happen if the file is Nil because it then creates an in-memory database and Connect succeeds. That would also be the reason that the SelectSQL call fails as there is a database but no tables exist.
Just to clarify… you’re on Windows right? If so, that path doesn’t look right… especially the leading \
In that case you don’t need to worry about the distribution yet. But when you do decide to distribute and get a license, use that blog to help automate the process of including the data and make it transparent to the users.
In the meantime for your own testing, do as @Wayne_Golding suggested above (here) and put the file in an easier path to get right such as your Documents folder.
Or use the full path to the file for your operating system. So if on Windows add the C: and full path etc. But I’d suggest you just copy your database to Documents like Wayne mentioned then use:
Dim dbFile As FolderItem = SpecialFolder.Documents.Child("dinobase.sqlite")
Blockquote
create table dino (record integer, name text, meaning text, ...
I’m not sure what you are referring to. I don’t have square brackets around my columns. I am not trying to create a database. The database already exists. It is the same SQLite database that I use in my Purebasic app. and the table is dino.
Yeah I know it already exists. I’m just pointing out that whoever created it didn’t need those things. It’ll work anyway, of course.
But add the check for whether the folderitem step worked.
I downloaded your project and I get this on my Mac when I Run the project:
If Xojo is saying “Connected!” on Windows, that may be a bug.
When you Run (debug) an application with Xojo, the program is created in a temporary directory and the database that you want to access is not there.
As mentioned above, is better to put the database in a known directory for testing. When you start building your app for distribution you can copy the database to the correct special directory so it can be reached.
This is what I changed:
'dbFile = GetFolderItem("PrehistoricLife\dinobase.sqlite") to dbFile = SpecialFolder.Desktop.Child("dinobase.sqlite")
TextField1.Text = sql.Column("Name").StringValue after sql = db.SelectSQL("SELECT * FROM dino WHERE record = 1")
Now I get “Connected!” and this displays on your application:
This is the code I have in the Open event:
Dim dbFile As FolderItem
'dbFile = GetFolderItem("PrehistoricLife\dinobase.sqlite")
dbFile = SpecialFolder.Desktop.Child("dinobase.sqlite")
//dbFile = GetFolderItem("\Xojo Projects\PrehistoricLife\dinobase.sqlite")
Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile
If db.Connect Then
MsgBox("Connected!")
Else
MsgBox("Error: " + db.ErrorMessage)
End If
Var sql As RowSet
sql = db.SelectSQL("SELECT * FROM dino WHERE record = 1")
TextField1.Text = sql.Column("Name").StringValue
Hope this helps.
I may have thought so too, but @Greg_O_Lone explained it above here
This will happen if the file is Nil because it then creates an in-memory database and Connect succeeds. That would also be the reason that the SelectSQL call fails as there is a database but no tables exist.
Which is why I was pressing the idea of checking whether the requested folderitem exists. And which would have caught this issue a lot sooner. That can be done either in code or when stepping through with the debugger.
I moved the file to the documents folder, added the check, dbfile.Exists=False, and I do not get the “Could not find database” message. I still get the “Connected!” message however, in the variables window I get this:
Although dbFile is no longer showing Nil, sql is still showing Nil.
Sounds like progress. Now click on the underlined DatabaseException in the debug panel to get more details on that exception. You will likely find an error description telling you there is a syntax error in the SQL statement given, or other helpful data.
Compare what you have to what @AlbertoD used above.
Can you share your new code for the open event?
@AlbertoD I tried your code but changed “Desktop” to “Documents” but got errors. I then added a folder in the PrehistoricLife folder and named it dbase and then copied the dinobase.sqlite file into it. I commented out the “dbFile = SpecialFolder.Documents.Child(“dinobase.sqlite”)” line in your code and added this line:
dbFile = GetFolderItem(“E:Xojo Projects\PrehistoricLife\dbase\dinobase.sqlite”)
Now it works! Thank you all very much for your help and especially your patients.
The only problem that I can see now is that when it comes time to build for distribution, by using the full path to the database file, not many people will have the app on Drive E. Is there a way to determine which drive the app is running on so that it can use a variable for the drive in the path?
Try this:
Var here, fullpath as String
here = new FolderItem ("", FolderItem.PathModes.Native)
fullpath = here.NativePath
Then you can parse fullpath.
When it comes times for distribution and you have a build, I strongly encourage you to not use that technique but instead follow the recommendations of the blog article mentioned above.
But to more specifically answer your question, the Application class (with an instance called “app” for desktop projects) has a property called ExecutableFile which is the FolderItem for the actual application. So by using its Parent property you can get a folder where it is located. From the above linked page:
You can get the location of the folder in which the app is running, by getting the Parent of the executable file:
Var f As FolderItem f = App.ExecutableFile.Parent
And after that use can use .Child() to find things starting from there.
But I really DISCOURAGE that, as OS’s tend to keep making it harder and harder to access things. I strongly suggest you follow the recomendations in the blog article.
Having the database right next to the app probably won’t work if the user that’s running is a non-admin as the application folders are locked down on Windows 10.
Thanks guys. I’ll use it the way it is for testing and read the blog again when getting ready to Build. I do have another question about the TextArea but it is probably better as a new post.
Thanks again for your help and your patience.