Get sql from sqlite_master is nil

Databases
1

I try to get the sql from a database table.

sql = “SELECT * FROM sqlite_master WHERE tbl_name = '” + tablename + "’ "

the sql does not give me an error, but the returning rowset is NIL !!

how do I get the sql of an existing table ?

Xojo 2020.r2

2

I use the following to get all tables:

“SELECT name FROM sqlite_master WHERE type =‘table’ AND name NOT LIKE ‘sqlite_%’;”

3

My sql seems to work, no database error, but the returning rowset is NIL !

if I type the sql in a sqlite browser, I get the right result !!

4

In API 1.0 when RecordSet was nil, your query was bad.
In API 2.0 when your query is bad you should get a DatabaseException.

You may be experiencing a bug where you should be getting a DatabaseException.

5

the query is ok, because the sqlite browser I have returns the proper result !!
no database exception !!!

Screen Shot 2022-08-24 at 21.31.49
Screen Shot 2022-08-24 at 21.31.491804×688 130 KB

6

This code is working for me to show the SQL that created the table:

Var db As New SQLiteDatabase
db.Connect

db.ExecuteSQL("CREATE TABLE sysLayoutHeader (id integer PRIMARY KEY ASC AUTOINCREMENT, " + _
"windowName text NOT NULL, " + _
"layoutName text NOT NULL)")

Var details As RowSet
Var tableName As String = "sysLayoutHeader"
details = db.SelectSQL("SELECT * FROM sqlite_master WHERE tbl_name = '" + tableName + "'")

If details <> Nil And Not details.AfterLastRow Then
  MessageBox(details.Column("sql").StringValue)
End If
7

Paul,
I use the same sql as you did and my rowSet is NIL !!

thanks to all,

I’m doing more testing and hopefully I get some result.

8

I’m so sorry,

I’m using multiple database connections and this was the wrong one !