Cannot open SQLiteDatabase

I have changed the program design and moved first Database related code to a module.
After adding properties selecting “db” and db.TableColumns worked fine.

However, back in window1, I could not connect again to the db-database. Also renewing “var db as [new] SQLitedatabase” did not help. Can you? Sorry, I know it is very basic, but after spending a day …

It sounds like window1 is not using the database instance that you connected to in the module. Each time you “var db as new SQLitedatabase” you are creating a new instance that is not connected to the database. You normally want to have one instance that is in scope and used throughout your app.

Try moving the db variable to the database module if it isn’t already there. Then, before the first access to the database don’t Var it. Rather, in the App.Open event use a statement like

theDB = New SQLiteDatabase

After that, the db variable should be global. If you do it in the Window’s Open then it will only be available in that Window.

Then do the Connect and don’t close it until you need to.

Well, I tried, but no success.
Window1 starts with command
Modul1.SelectDBase
In Modul1 I select a SQLiteDB
Var db As New SQLiteDatabase


dbFile = Folderitem.ShowOpenFileDialog(sqliteType)

Get the number of clumns and Column-Names of table

columns = db.TableColumns(“Row1”)
colcount = columns.RowCount
columns.close
.
.
Var tables As RowSet
tables = db.Tables

and go back to window1 to open a rowset

rb.connect
rowsFound = db.SelectSQL(“SELECT * from Row1”).

Here, at db. the compiler stops without comment.
db is not closed anywhere.

Previously, as proposed, I had added an App.open method
with property DB as SQLiteDatabase (public)
in the same way I had done with Module1 and added

Var DB as New SQLiteDatabase
db.Connect

What is wrong with this?

Jim, thanks. The problem seems unrelated to declaration of the dbase.
Also, I declared “var db as new SQLitedatabase" only in window1 and then switched to the module. It worked in the module, but not back in the window1.

Update: I had moved the var db as new SQLitedatabase to App.open and then again declared same in window1. This worked for
db.Connect
db.ExecuteSQL(“BEGIN TRANSACTION”)

but stopped again with the following

rowsFound = db.SelectSQL(sql) (where SQL is "SELECT * FROM colum1 WHERE …)

Looks like there is a problem with the dbFile.Name which got lost when leaving the module. So, why is there a “public” or “global” property for the module?

As long as you have a var for your database your code can’t work.

Let’s say you have a method in Module1 called MyMethod. If you needed to call that method from outside of the module, The scope determines how specific you need to be:

MyMethod // Global
Module1.MyMethod // Public
// Private, only from within the module.

Thanks Beatrix. However, when I changed to
DB = New SqliteDatabase
as Dale recommended, nothing changed.

Also, before I created modules, I found no problem with Var for databases. Could you give me more info?

Without having a look at your code it’s hard to say what makes the problems. As Greg says you should have a module where your database is a global property like globals.database. Then you can talk everywhere to your database with globals.database.

Thanks Greg. As I understand you, public/global would work only in one direction and - in my case - database properties (i.e. db.name, columns) found in a global modul cannot be used when back in the window?

In the screenshot below I’m using an SQLite database. The database here is private because I made methods to access the database. If I would make the database public I could use it everywhere as PlanData.PlanDataDB.

Xojo properties, variables and globals are references. They don’t work in one direction.

Thanks. This looks exactly like mine (except for the number of modules :blush:).
Will try to find what I did wrong

Create a brand new project,
add a new Module
and make the whole working there.

Once it works, you will be able to do the same in your main project.

Emile, I’ll try that. Just let me present my code (in short):

App.open: “DB = new SQLDB”
Property: DB as SQLDB
“db = new SQLDB”
Public

Window1: Module1.SelectDB

Module1: VAR DB as NEW SQLDB
Var dbFile …
(Selecting DB from HD)
db.DatabaseFile = dbFile …

     db.TableColumns
     (get tablenames and columnnames)


      Var tables as Rowset
      tables = db.tables 
      (works!)

Property:DB As SQLDB
Global

Back to Window1
Var rowsFound as RowSet
db = new SQLDB

     db.connect
     rowsFound = db.SelectSQL(SQL) STOP/ERROR!

Emile, I just placed all code into the module, but no change. I’ll try moving everything to window1 now.

Worked with complete code in Window1. So why not in Module? :roll_eyes:
Edit: works in Module, too, when complete.
Where is the culprit?

Are you properly grasping the point that, by having:

Var db as new SqliteDatabase

in a method, you are creating a local variable (db) which ceases to exist after the method completes?

db needs to be declared as a global property so you can initialise it here and use it there. If you make it a property of the app, so you can use it where needed, then you must also remove all Var db as ... statements.

Ok, I deleted the declaration in the moduel and added

Var db as new SqliteDatabase
in a new method App.open

changed this to
Var db as SqliteDatabase
and also
db = SqliteDatabase


But no success

The last line is almost correct. You need

db = new SQLiteDatabase

You said at the beginning “I could not connect again to the db-database”. What is your code and what error do you get? Why do you need to connect again? Can you make us an example?

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.