Gambas sqlite

Post your Gambas programming questions here.
Post Reply
jessa
Posts: 2
Joined: Tuesday 26th September 2017 12:55am

Gambas sqlite

Post by jessa »

How to use sqlite in gambas? I'm using gambas in raspberry pi and DB browser for Sqlite.
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Gambas sqlite

Post by stevedee »

Thats a big question, and I don't know how much you know about Gambas & Sqlite, but you will need to include the gb.db component and if you want to use Data Bound controls gb.db.form.

This is the simplest code I can think of to get you started;

Create a new project and add a textArea to the Form. Enter this text:-

Code: Select all

' Gambas class file

Private dbConnection As New Connection

Public Sub Form_Open()
Dim myTable As Table

  dbConnection.Name = "/home/steve/Gambas/my-tv/me-tv.db" 'the path to a valid database
  dbConnection.Type = "sqlite3"
  TextArea1.Clear()
  Try dbConnection.Open()
  ' display each database table name
  For Each myTable In dbConnection.Tables
    TextArea1.Text &= myTable.Name & gb.CrLf
  Next

End

When you run this code using a valid database, the textArea should list your database tables.
jessa
Posts: 2
Joined: Tuesday 26th September 2017 12:55am

Re: Gambas sqlite

Post by jessa »

Thank you very much! I'l try the code.
User avatar
sjsepan
Posts: 68
Joined: Saturday 12th October 2019 10:11pm
Location: Leeper, PA, USA
Contact:

Re: Gambas sqlite

Post by sjsepan »

I tried this out today too, since I'm finally looking at db access in Gambas. Trying the code as is (with my own project and database paths) I got the equivalent of the following error (I say equivalent because I have edited it to show what it would look like with the path information from the example given):

"Unable to locate database `/home/steve/Gambas/my-tv/me-tv.db` in ``"

When I altered mine to move the path portion of the name into the Host property (in a manner similar to what is shown below), then it opened just fine.
' Gambas class file

Private dbConnection As New Connection

Public Sub Form_Open()
Dim myTable As Table

  dbConnection.Host = "/home/steve/Gambas/my-tv/"
  dbConnection.Name = "me-tv.db" 'the path to a valid database
  dbConnection.Type = "sqlite3"

  TextArea1.Clear()
  Try dbConnection.Open()
  ' display each database table name
  For Each myTable In dbConnection.Tables
    TextArea1.Text &= myTable.Name & gb.CrLf
  Next

End
stevedee wrote: Wednesday 27th September 2017 10:45am Thats a big question, and I don't know how much you know about Gambas & Sqlite, but you will need to include the gb.db component and if you want to use Data Bound controls gb.db.form.

This is the simplest code I can think of to get you started;
[...]
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Gambas sqlite

Post by stevedee »

This is a bit odd. When I wrote my original sqlite3 database code in 2009, it certainly worked with .Host="" and .Name={full path + file name}

The Gambas Documentation now show .Host =path and .Name is just the file name.

However, the documentation still suggests that .Host is a computer name or IP address (which is normally what "host" implies):-

"Returns or sets the host where the database server resides.
This host can be a machine name, or an IP address. The default host is localhost."
User avatar
sjsepan
Posts: 68
Joined: Saturday 12th October 2019 10:11pm
Location: Leeper, PA, USA
Contact:

Re: Gambas sqlite

Post by sjsepan »

Agreed, that is how I've always though of host.
Perhaps, since this is a server-less (or in their words 'engine-less file-based') database, they felt they needed to re-purpose the Host property.

Anyway, here is the help for SQLite, where they are even suggesting 'Application.path' for the value:
http://gambaswiki.org/wiki/howto/databasesqlite?nl
stevedee wrote: Friday 13th December 2019 11:09pm This is a bit odd. When I wrote my original sqlite3 database code in 2009, it certainly worked with .Host="" and .Name={full path + file name}

The Gambas Documentation now show .Host =path and .Name is just the file name.

However, the documentation still suggests that .Host is a computer name or IP address (which is normally what "host" implies):-

"Returns or sets the host where the database server resides.
This host can be a machine name, or an IP address. The default host is localhost."
User avatar
Got2BeFree
Posts: 91
Joined: Saturday 26th November 2016 2:52am
Location: Lost

Re: Gambas sqlite

Post by Got2BeFree »

I haven't created a mySQL db in about 10 years since SQLite is all I've needed for my apps. If you think of ".Host" as a "location" and not just as a path, machine name, or IP address, and use a variable to hold that "location", you should be able to craft your db access code to be swap-able between mySQL and SQLite with minimum effort. There used to be a blurb on this in the wiki, but it looks like db access has been rewritten in the wiki for both SQLite and mySQL.

A few things...

When setting the ".host" path, no need to use a trailing slash ("/").
When putting the db file in a user's home directory, set the ".host" path by using "User.Home" (instead of hard coding the path).
With hDB      'open the database
        .Type = "sqlite3"
        .Host = User.Home &/ "Gambas/my-tv"
        .Name = "me-tv.db"
End With
When putting the db file in a directory outside of the user's home directory (such as an NFS shared directory), omit "User.Home" and add an absolute path with a beginning slash ("/").
With hDB      'open the database
        .Type = "sqlite3"
        .Host = /path/to/shared/directory/Gambas/my-tv"
        .Name = "me-tv.db"
End With


This is how I set up my sqlite databases. (This is from my event reminder app.) If I need to have the db in a NFS location, I can easily change the sBaseDir string variable and drop the "User.Home &/" from the xxxPath string variables. When creating a new db app, I just copy these 2 files into the new app and make a few changes.

MGlobal.module file...
' Gambas module file 

' directory and file paths
Public sBaseDir As String = ".WhiteIslandSoftware"
Public sConfigDir As String = "Config"
Public sDataDir As String = "Data"
Public sAppDir As String = Application.Name
Public sConfigFilename As String = sAppDir & ".conf"
Public sAppDirPath As String = User.Home &/ sBaseDir &/ sAppDir
Public sConfigPath As String = User.Home &/ sBaseDir &/ sAppDir &/ sConfigDir
Public sDataPath As String = User.Home &/ sBaseDir &/ sAppDir &/ sDataDir

' database
Public hResData As Result
Public hDB As New Connection
Public sDBName As String = "x31"
MDatabase.module file...
' Gambas module file

Public Sub DBConnect()

' db.Debug = True

    If Not Exist(MGlobal.sDataPath) Then FMain.CheckDirIntegrity()
    If Not Exist(MGlobal.sDataPath &/ MGlobal.sDBName) Then DBInitialize()       '  Return
    Try MGlobal.hDB.Close

    With MGlobal.hDB      'open the database
        .Type = "sqlite3"
        .Host = MGlobal.sDataPath
        .Name = MGlobal.sDBName
    End With

    Try MGlobal.hDB.Open()

End
sholzy

I'm wondering around lost in the past, not knowing where the present is.
User avatar
sjsepan
Posts: 68
Joined: Saturday 12th October 2019 10:11pm
Location: Leeper, PA, USA
Contact:

Re: Gambas sqlite

Post by sjsepan »

Good path tips, Thanks!
Got2BeFree wrote: Saturday 14th December 2019 5:50pm
This is how I set up my sqlite databases. (This is from my event reminder app.) If I need to have the db in a NFS location, I can easily change the sBaseDir string variable and drop the "User.Home &/" from the xxxPath string variables. When creating a new db app, I just copy these 2 files into the new app and make a few changes.
Post Reply