Cant create SQLite Database file in several locations

Gerardo_Garca · December 28, 2016, 6:13pm

Hi all!

I’m starting to create a small project for store certain info and build a small database, cuz of this I’m not using mySQL o PostgreSQL, mariaDB or other else.
In this case I rather to use SQLite, other reason is that I don’t have a Database License in order to compile with big databases.

And I notice that Its possible to compile projects with SQLITE.

BUT that’s no the issue.

The issue here is that I have a start code to Create a database if the file doesn’t exists:

// La base de Datos estar en:
Dim dbFile As FolderItem = SpecialFolder.Desktop.Child(“OC.sqlite”)
App.DB = New SQLiteDatabase

//Msgbox dbFile.absolutepath

// Crearla si no existe
If NOT dbFile.Exists Then
//Msgbox “No existe”
// Create the Database
App.DB.DatabaseFile = dbFile
If App.DB.CreateDatabaseFile Then
  mIsConnected = True
  CreateStatusLabel.Text = "OK."
Else
  mIsConnected = False
  CreateStatusLabel.Text = "Error: " + App.DB.ErrorMessage
End If
Else
//Msgbox “Si existe”
App.DB.DatabaseFile = dbFile
If App.DB.Connect Then
  mIsConnected = True
  CreateStatusLabel.Text = "OK."
End If

So, Whats wrong??

Well, If I use SpecialFolder Locations like:
Desktop
Documents

It works flawlessly!

The issue happen when I use locations like:
SharedPreferences
Applications
ApplicationData
CurrentWorkingDirectory

When I use one of the mentioned locations, I can’t create the database file.
And I got an error: “Error: unable to open database file”
And Also I go to the mentioned folder and In fact the Databasefile isn’t been created.

I’m thinking that Maybe I need special permissions or something.

I’m running on Mac.

Regards

scott_boss · December 28, 2016, 6:24pm

try looking at this page…
http://documentation.xojo.com/index.php/SQLiteDatabase.CreateDatabaseFile

Tim_Parnell · December 28, 2016, 6:32pm

First, stop using AbsolutePath.

You can’t use SharedPreferences and Applications because most users have no write privileges there, and Xojo isn’t equipped out-of-the-box to ask for permissions. Depending on the CurrentWorkingDirectory location the user may not have write privileges there either.

Do not put the SQLite data next to the app, especially if you intend to keep the app in /Applications

The ApplicationData dir should allow you to write, but you need to use your own child inside it.

If the user doesn’t need to access the actual SQLite file, you need to store it in your own child folder in ApplicationData.
My TPSF module has a nice cross-platform mechanism for getting your app’s own subfolder.

If the user does need to access the SQLite file, use a SaveDialog and OpenDialog to handle it.

tl;dr
If you use anything other than SpecialFolder.ApplicationData.Child("MyApp") you are doing it wrong.

Gerardo_Garca · December 28, 2016, 7:11pm

[quote=305895:@Tim Parnell]First, stop using AbsolutePath.

You can’t use SharedPreferences and Applications because most users have no write privileges there, and Xojo isn’t equipped out-of-the-box to ask for permissions. Depending on the CurrentWorkingDirectory location the user may not have write privileges there either.

Do not put the SQLite data next to the app, especially if you intend to keep the app in /Applications

The ApplicationData dir should allow you to write, but you need to use your own child inside it.

If the user doesn’t need to access the actual SQLite file, you need to store it in your own child folder in ApplicationData.
My TPSF module has a nice cross-platform mechanism for getting your app’s own subfolder.

If the user does need to access the SQLite file, use a SaveDialog and OpenDialog to handle it.

tl;dr
If you use anything other than SpecialFolder.ApplicationData.Child("MyApp") you are doing it wrong.[/quote]
Thanks Tim, Its ok.

So only I have a doubt more, Why I must not use AbsolutePath?
Is It deprecated or something else?

In that case I use it ignorer to show the path in a message box in order to know if theres something wrong with it.
Then I guess that might be a permissions issue

Tim_Parnell · December 28, 2016, 7:40pm

Yes, AbsolutePath is deprecated. Use NativePath instead.

Yes, I saw the MsgBox was commented out; but the earlier you stop using deprecated methods the better. You’ll be used to the new methods and ready to use them when the deprecated methods get removed.

Emile_Schwarz · December 28, 2016, 8:13pm

http://documentation.xojo.com/index.php/FolderItem.AbsolutePath