Questions and answers about the LiveCode platform.
https://www.forums.livecode.com/
Code: Select all
## list a set of table names with record counts
put empty into field "data"
put revdb_tablenames(gConnectID) into tdata -- get a list of the tablenames in the current db
-- now lets get the record count for each table
repeat for each word thisWord in tdata -- loop through all of the table names
put thisWord into tTablename
put "SELECT COUNT(*) FROM " & tTableName into tSQL -- and query the record count
put revDataFromQuery(tab, cr, gConnectID, tSQL) into tCount
put thisWord & comma & " " & tCount & cr after field "data" -- display results in a field
end repeat
Which is probably obvious. Anyway, I tried substituting "information_schema" for "sqlite_master" in the above, because I can see there is a database called information_schema in my On-Rev account, but it just returns an error "revdberr,Table 'lcxxxx80_mydatabase.information_schema' doesn't exist". This may just be one of those permission kinds of things where I can access all of the tables in "lcxxxx80_mydatabase" but I am not allowed to access the database "information_schema". It is interesting that the information_schema provides lots of useful information about tables, including the number of records (rows), but not the number of fields (columns).Simon wrote: put "SELECT * FROM sqlite_master;" into tSQL
put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords
Code: Select all
global gConnectionID
--| Get the table names, row and column counts from a mySQL or SQLite database file
--| and display in a basic Table Field
on mouseUp
put empty into field "data"
put "Table" & tab & "Rows" & tab & "Columns" & cr & cr into tdata
put revdb_tablenames(gConnectionID) into tnames -- get a list of the tablenames in the current db
-- now lets get the record count for each table
repeat for each word thisTable in tnames -- loop through all of the table names
put thisTable into tTablename
-- now lets get a record count
put "SELECT COUNT(*) FROM " & tTableName into tSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tRows
-- now lets get a column count.
put "SELECT * FROM " & tTableName into tSQL
put revQueryDatabase(gConnectionID, tSQL) into recordsetID
put revDatabaseColumnCount(recordsetID) into tColumns
put thisTable & tab & tRows & tab & tColumns & cr after tdata -- ad results to tab delimited field
end repeat
-- display results
set text of field "data" to tdata
end mouseUp
yes, sorry, my fault!If I understand correctly revdb_recordcount() only works on a recordset ID
Well, aren't you the clever oneKlaus wrote:Hi Mark,
put the num of items of revDatabaseColumnNames(tConId, "YourTable") into tNumOfRecords
Code: Select all
put "SELECT max(rowid) FROM YourTable ;" into tSQL
put revDataFromQuery(tab,return,connID,tSQL) into tRecords
answer "There are " & tRecords & " rows in YourTable" not really, that was complete bullshit, since that was NOT the question at all!marksmithhfx wrote:Well, aren't you the clever oneKlaus wrote:Hi Mark,
put the num of items of revDatabaseColumnNames(tConId, "YourTable") into tNumOfRecords
Apart from the fact that you stuck the result in a funny looking variable name. But it works perfectly, and replaced the 3 lines of code I was usingput the num of items of revDatabaseColumnNames(tConId, "YourTable") into tNumOfRecords
and eliminated an extra query. I am hoping both in the case of revDatabaseColumnNames() and in the case of COUNT(*) (which I was already using to count the rows) that the implementer has optimized the code to just read from the file schema where this information already is sitting. No querying of the database should be required. I had not thought of using "the num of items of…" so I thought that was a pretty neat trick.put "SELECT * FROM " & thisTable into tSQL
put revQueryDatabase(gConnectionID, tSQL) into recordsetID
put revDatabaseColumnCount(recordsetID) into tColumns
Yes, I think that one finally unsettled myself a bit in the endApart from the fact that you stuck the result in a funny looking variable name.