Hehe, I am fine. I only spent a week with Xojo. And this Database thing keeps me occupied.
Thank you for the Code. I will try explore it.
Just got an administration “warning” I already sent you “3 replies”. So I better pause for some hours. 
You don’t know who downloaded it
Maybe it was the OP downloading it many times because of network error?
Of course it does. Those are examples. And the use of ‘new’ has nothing to do with whether the database exists already.
-
Var db as Sqlitedatabase. This declares db as a variable of type SqliteDatabase. In your case you don’t do that, as you’ve already declared db as a property on App. -
Having done (1), you then have to initialise db (ortherwise it has value Nil). So you do
db = new SqliteDatabase. Right, so now db points to an SqliteDatabase object. -
So far, this object is largely uninitialised. An important step then is to say which file on disk this object will refer to. So you are doing
db.DatabaseFile = "/path/to/my/actual/database". I’m ignoring how you obtain that string, for the moment. -
Then you do db.connect(). And if that works, you can start applying SQL statements (CREATE TABLE, SELECT, UPDATE, etc) to your database using the database methods.
1 Like
Tim, thank you so much.
I added a method “open” with one line “Var db as Sqlitedatabase” and a property db, type SQLDB, both scope public.
Window1 has the line db = new SQLDB.
Result: All db.xxxx lines needed to be changed to app.db.
That change done, everything fine! 
Just wondering why I still needed the app. event though scope is “public”.
Next step will be moving all code to a module.
Ha, yes I see I made a mistake in my haste yesterday. It should have been:
3a) Make a folder item which relates to the actual disk database:
Var dbfile as FolderItem
dbfile = new FolderItem ("/path/to/my/actual/database", FolderItem.PathModes.Native)
// Here you can check dbfile.Exists, dbfile.IsWriteable, etc
.
3b) Now you do:
app.db.DatabaseFile = dbfile
- etc.
I don’t think you need this.
I’ll give it a try.
Still struggling with modulization of DB operations. 
You are right, even much faster now.
Hope the modulization won’t change that.
This worked in a first module which selects and opens the datafile and defines dbfile.
However, in a second module which is designed to find the tables and rows/columns, it won’t run with just “app.db = new SQLdbase”, even though in window1, after running through Module1, the same code was fine.
Looks like I have to define dbfile in every module (but not in window1 ??).
OR - Do I have to set the Database opening procedures 1 to 4 (your post 45) and define dbfile in every module and window, where a DB is used?
app.db = New SQLiteDatabase
app.db.Connect
'Getting All Columns of a table
var colNo as Integer
Var columns As RowSet
var erg As Variant
var cn(10) as variant
var i as Integer
columns = app.db.TableColumns(“Lexeme”)
colNo = columns.RowCount 'Number of Rows
Var tables As RowSet
tables = app.db.Tables
var c as Integer
Try 'getting Table-Names
For Each row As DatabaseRow In tables
MessageBox(row.ColumnAt(0).StringValue + " Col.-#: " + str(c))
c = c + 1
Next
tables.Close
Catch error As NilObjectException
MessageBox(“This database has no tables.”)
End Try
MessageBox("Connected to " + dbFile.Name)
colNo = columns.RowCount 'Get number of Rows
st = str(colus)
MessageBox(“Rows =” + st)
columns.Close
You don’t need a new dbfile after you have created the global property and defined it with new. Remove the new and just refer to the dbfile elsewhere.
If you declare db in your related Database module, you do not have to write App.db, only db
Also, some useful reading about the use of Module is here:
https://documentation.xojo.com/getting_started/using_the_xojo_language/modules.html
Hello Jan
I also had some problems at the beginning
with the SQL database but with the new API2
and I get along well with the rowset functions.
some things could perhaps be better directly in the database
be called as just using db.SelectSQL.
take a look at the program, maybe you’ll be fine.
https://www.dropbox.com/s/su7v06lr1zrkz3f/test-sqlite-extern.xojo_binary_project?dl=1
Wow! Great help. Thanks!
I will study this.
Never do this (if you’re using a global property).
Only do this once in your entire app. Do it early enough that it happens before you ever need to use it. Instantiate the database object and connect to it one time, then use it everywhere. You don’t need to use new or connect in any of your other code.
Tim, thanks, I’ve learnd my lesson. Before, I just copied the DB code from the xojo documentation. 
Problem with
aSTR = rowsfound.columnAT(i).stringValue
As long as i is 0 this would show the first row-entry (!) from my rowset.
However, when i is >0 I get an OutOfBoundsException error.
I tried hard to find any relevant documentation, but there is only this sample code, which looks like you can use any number for 0:
Get the string value of a column in a RowSet:
// rs is a RowSet
Var productName As String
productName = rs.ColumnAt(0).StringValue
To retrieve the next/last/first row there is a choice of methods, but only the one above for row 0.
How can I retrieve row (i) with the entries in one row of given columns in a table?
I am not using a listbox to display the content but it seems there is no other way.
What did I miss?
Jan
We need more information. How many columns are in the RowSet? Have you examined its contents in the debugger? What is the sql statement?
This is giving you the content of column 0, not row 0.
To move from one row to another, use the RowSet methods.
Tim, the rowset has entries from 4 columns of a table, sorted by ID. The Rowcount / max(ID) is 2100. Judged from the result of columnAT(0) the SQL works fine: “SELECT a, b , c FROM Table”.
I can walk up and down using next/first/last but not find the data in row X.
Funny that columnat(0) would give entries of row 1 from the tables a, b and c. 