Getting the number of TABLEs

Databases
1

In the code below, I consistently get 1 in MyTABLE_Count, and when I click in Contents (Debugger), I see:

Name: COUNT(*)
Value: 6

6 in that case is the value I was expected…

In the code below, db is valid (not shown)

[code] Dim MyTABLE_RS As RecordSet
Dim MyTABLE_Count As Integer

MyTABLE_RS = db.SQLSelect(“SELECT COUNT (*) FROM sqlite_master WHERE type=‘table’”)
MyTABLE_Count = MyTABLE_RS.RecordCount[/code]

What am I doing wrong ?

BTW: I only want to get the number of TABLEs in the loaded .sqlite file (that can be any file know and unknown).

2

Try:

[code]Dim MyTABLE_RS As RecordSet
Dim MyTABLE_Count As Integer

MyTABLE_RS = db.SQLSelect(“SELECT COUNT (*) AS cnt FROM sqlite_master WHERE type=‘table’”)
MyTABLE_Count = MyTABLE_RS.Field(“cnt”).IntegerValue[/code]

3

Use COUNT() instead of COUNT ()
Without the space

MyTABLE_RS.Field(“cnt”).IntegerValue

4

Thanks, works fine.

Now, from where this tip can comes ? I tried the string below (google) without success:

site:sqlite.com +"SELECT COUNT(*) AS"
site:documentation.xojo.com +"SELECT COUNT(*) AS"

site:developer.xojo.com +“SELECT COUNT(*) AS”

Now, the query above may not be the correct way to get an answer.

PS: I searched on the internet until I found the syntax above (searching the information in the sqlite_master TABLE).

My fault (more or less): the page I read to get the syntax I used… I stopped to read it once I saw what I feel is the answer. The real answer was some paragraphs to the bottom.

Sorry folks.

5

Well all selects should have a selector without spaces.

So (*) is part of COUNT.

See:
sqlite tutorials

6

not sure what the issue here is… but

SELECT COUNT    (*)

works just as well as

SELECT COUNT(*)

just the 2nd syntax is more “acceptable”
The SQLite parser is whitespace tolerant

7

I love to have a space (in this case), but I do not care to remove it if it is mandatory ;-:slight_smile:

Having a space in this case make the line more readable / better looking syntax, but as I wrote above, if I have to remove it, I will do.

In our industry, we always have to make changes for many reasons and we continue. So I do.

Thank you everybody.

8

When you do:

MyTABLE_RS    = db.SQLSelect("SELECT COUNT (*) FROM sqlite_master WHERE type='table'")
MyTABLE_Count = MyTABLE_RS.RecordCount

…what you get is an answer to this question: How many numbers are the number of tables in my database?
To which the answer is: 1

9 
sqlite> create table foo ( bar , baz ) ;
sqlite> create table baz (for , bar ) ;
sqlite> create table bar (foo, baz ) ;
sqlite> select count (*) from sqlite_master where type = 'table' ;
3

This code is just wrong for getting the COUNT back

[code]
MyTABLE_RS = db.SQLSelect(“SELECT COUNT (*) FROM sqlite_master WHERE type=‘table’”)
MyTABLE_Count = MyTABLE_RS.RecordCount

[code]

RecordCount gives the NUMBER OF ROWS retrieved and in the case there is 1 row
It doesnt check the value of the data retrieved (which from the example above is 1 ROW with 1 column that contains the value 3)

[code]
MyTABLE_RS = db.SQLSelect(“SELECT COUNT (*) FROM sqlite_master WHERE type=‘table’”)
MyTABLE_Count = MyTABLE_RS.idxField(1).Integervalue

[code]
gets the VALUE from the one column in the one row returned