Connect to an SQLite database?

Databases
1

Does one connect to an SQLite database?
I mean… it’s a file on disk not a server.

2

yes one does…
I’d post a sample, but the external interface to my disk array died last night, and the new one doesn’t arrive until tommrow

3

Yes, you point it to the file and then you connect to it. There is example code on the SQLite page.

4

With sqlite, Connect is basically opening the file.

5

The connect method returns a boolean to say wether it worked or not, so you use it like

[code]Dim dbFile As FolderItem
dbFile = GetOpenFolderItem("")

If dbFile <> Nil Then
Dim db As New RealSQLDatabase
db.DatabaseFile = dbFile
If db.Connect Then
MsgBox("Connected to " + dbFile.Name)
Else
MsgBox("Error: " + db.ErrorMessage)
End If
End If[/code]

6

Thanks you all.
@S to hear about your drive dave…

7

[quote=210282:@Brian O’Brien]Thanks you all.
@S to hear about your drive dave…[/quote]

Thanks… this week has been all bad luck.
Seems the surge protector that powered the computer and disk unit failed.
The computer protected itself by shutting down, the disk unit smoked (I hope the drives themselves are ok)
Replacing the power strip seemed to fix the issue… computer hasn’t crashed in 36 hours now
and am buying another disk unit later today, and spending the weekend moving data around

8

@S … Joy :frowning:

9

I can’t figure this sqlite out…
Is this database just a text file with sql statements in it?

How do you create the initial instance?

[code]
dbFile = SpecialFolder.Home.Child(“myDatabase.sqlite”)

// Should I be checking if the file exists and if it doesn’t do something?

If dbFile <> Nil Then
if db = nil then
db = new RealSQLDatabase
db.DatabaseFile = dbFile
If db.Connect Then
MsgBox("Connected to " + dbFile.Name)
db.SQLExecute(sql)
If db.Error Then
MsgBox("DB Error: " + db.ErrorMessage)
Else
MsgBox(“my database created successfully.”)
End If
Else
MsgBox("Error: " + db.ErrorMessage)
End If
End If
End if[/code]

The sql string contains my table creation sql. “create table if not exists etc etc…”
Right now this is saying that the database file doesn’t exist in the specified location.
Well duh! I know I’m trying to create it… :slight_smile:

10

here is the connect code cut right out an app I wrote
It has more than you need … it checks if DB is encrypted or not, if so asks for password (GET_DB_PASSWORD)
Checks if tables exists and creates them if not (DB_CREATE)

but I think the main piece you are asking about is
DB=New SQLiteDatabase

where DB is
DIM DB As SQLiteDatabase
at a GLOBAL level (ie in a MODULE)

  // Create Database Object
  Dim x As Integer
  DB=New SQLiteDatabase
  DB.DatabaseFile=Path_to_Database
  db.MultiUser=True
  DB_IS_CONNECTED=False
  // Connect to the database
  If DB.databaseFile.exists=True Then
    //
    // Check if Database is Encrypted
    //
    Cypher_Key=""
    db.EncryptionKey=Cypher_Key
    If DB.Connect=False Then
      x=db.ErrorCode
      If x=21 Or x=26 Then' File is encrypted or is not a database
        //
        Database_Is_Encrypted=True
        Do
          Cypher_Key=Get_db_Password
          If Cypher_Key="" Then  'user gave up
            Quit
            Return
          End If
          db.EncryptionKey=Cypher_Key
          If db.Connect Then Exit Do
        Loop
        //
      Else
        DB_Error False
        Quit
      End If
    Else
      Database_Is_Encrypted=False
      Cypher_Key=Get_db_Password
    End If
    
    // Check Tables in case any new ones were added since last version
    //
    If Not DB_create(False) Then Quit
  Else
    //
    // The database file does not exist so we want to create a new one.
    //
    If Not DB_Create(True) Then Quit
  End If
11

@Dave S Thanks… but what is DB_Create?

13

it is a method I wrote that creates the specific tables for this application if they do not exist already… That was beyond the scope of your question which was “how to connect” :slight_smile:

14

I found my issue… SpecialFolder.Home isn’t ‘my’ home it’s the home folder and not the user underneath home…

15

Must I use transactions to use sqlite?
is there a close database that must happen for everything to process?

16

No, but you do have to Commit your changes.

17

Isn’t it a good idea to use transactions ?

Another good idea is to get an eye on the Language Reference (built-in / online) and eventually SQLite home, Documentation button in the top bar .

18

Note that the default functionality for auto commit did change with the newer sqlitedatabase class. That might be part of the confusion.

19

Thanks for the link.