SQLite count number of table rows

[Shide]

Hi all, VERY lame question I'm sure, but I've finally given up.

Basically all I want to do is count the number of rows in my SQLite table. Sounds very simple.

sql = "select count(*) from territories";
tbResults = SQLite.QueryToTable(db, sql);
max_rows = Table.Concat(tbResults, "", 1, TABLE_ALL);

Paragraph.SetText("Paragraph1", "The query took " .. hours.. " hrs " .. mins .. " mins "..secs.." secs. \nIt returned " .. no_rows .. " rows. \n" .. max_rows .. " rows imported into SQLite")

Can anyone please suggest why I have an error "SQL logic error or missing database"?

What I'm doing in AMS (and this part is working), is;

a) Connect to Oracle Applications, use Oracle SQL to retrieve data from database to text file (yuk, but there's no direct SQLite to Oracle method I know of....)

b) Load that text file (yuk again) into SQLite in AMS

c) give the user a message like "244 rows imported into SQLite"

Sounds soooo simple...

Thanks in advance
Andy

[azmanar]

Hi,

SQL logic error is caused by wrong sql syntax.

To count number of rows you read to a table, the value for the number of rows is in TableName.Rows.

eg.

nRows = tbResults.Rows; -- you'll get the number of rows

[Shide]

Thanks for the quick reply azmanar.

I've corrected the code as below, the answer it is giving me is perfect however I still get the error message when AMS is evaluating the sql...

"SQL logic error or missing database"

tbResultsx = SQLite.QueryToTable(db, "select * from territories");
nRowsx = tbResultsx.Rows;

Paragraph.SetText("Paragraph1", "The query took " .. hours.. " hrs " .. mins .. " mins "..secs.." secs. \nIt returned " .. file_to_table_rows .. " rows. \n" .. nRowsx .. " rows imported into SQLite")

Any suggestions how to stop the error from appearing...?

In addition, having to load the whole record set into memory seems like an amazing waste of processing time. In Oracle SQL, you can just do "select count (1) from territories" and the results are returned very quickly even on large tables. From what I can see here, we need to scan the entire record set into memory.... freaky.....

Thanks
Andy

[azmanar]

Hi,

I've never used Oracle before but I've been using SQLite and MySQL.

At my side, usually this error occurs if the SQL syntax is erroneous.

While if the SQL DB handle is not created correctly and not opened before using this query "SQLite.QueryToTable(db, "select * from territories");", it won't even count the rows you have.

There could be some other explanation for the error you're having. Probably Worm, Tigger, Shadow, Dermot, Reteset, Bule or Longedge could help.

[Dermot]

COUNT(*) works fine. Try this.

local sql = "SELECT COUNT(*) AS Total FROM territories";
local tbResults = SQLite.QueryToTable(db, sql);
local max_rows = tbResults.Data[1]["Total"]
Dialog.Message("Record Count", max_rows)

If you are still getting the error then something else is wrong. Maybe post a test database so we can try it.

[Shide]

Dermot/azmanar,

Thanks for your advice, however I'm still getting this freaky error. Can you please take a look at the uploaded database.

Simply put the text file into the C:\ directory and press the button and all should run. You will see the freaky "SQL logic error or missing database" error just near the end of the button script.



Thanks in advance.
Andy

P.S. It tells you that it is processing 244 records when there are only 237 in the file... I also don't get that either...

[Dermot]

Actually the text file has 244 records. The error is not from the count(*). The problem is your text file. There is a blank line at the end of the file, that's why it tells you the text file has 245 records. Remove the blank line and you get no error.

[Shide]

Dermot, thank you for that. I've modified the program that exports the data to the file without the trailing chr(34). newbie mistake that one.

Much appreciated.
Andy