Greetings all!
This is doing my head in!!
Currently working on an iOS app which uses a small SQLite Database.
This works and returns 152 records:
"SELECT count(*) FROM pora"However, this does NOT work and returns a count of zero (the field “pora_Answer” for all 152 records in the “pora” table is zero (0));
"SELECT count(*) FROM pora WHERE pora_Answer=0"What am I doing wrong?..Keeping in mind it is late here in Sydney.
Cheers
Grant
your first example. I assume you meant. it returns ONE record with a value of 152
and there is nothing wrong with your second example…
So my first question would be, did you check for an ERROR after the query?
As I assume you are indicating that the 2nd query in fact returned ONE record with a value of ZERO
if in fact it returned ZERO records, then there is an error, as COUNT will always return one record
Is pora_Answer perhaps defined as something other than INTEGER? If it’s defined as TEXT, you might try this instead, where you surround it with the single quote symbol:
“SELECT count(*) FROM pora WHERE pora_Answer=‘0’”
[quote=396269:@Ralph Alvy]Is pora_Answer perhaps defined as something other than INTEGER? If it’s defined as TEXT, you might try this instead, where you surround it with the single quote symbol:
“SELECT count(*) FROM pora WHERE pora_Answer=‘0’”[/quote]
for SQLite that really should not matter
Ah. Wasn’t sure of that.
BUT… this does matter… if it IS text, then it must not be 0.0, but 0
create table xyz (xxx text);
insert into xyz values('abc');
insert into xyz values('0'); // this is counted
insert into xyz values('0 '); // this is counted
insert into xyz values('0.0'); // this is NOT
select count(*) from xyz where xxx=0;In the above case, couldn’t he capture the intended count with his posted code if he defined the field as REAL?
select count(*) from xyz where CAST(xxx as double)=0;could be used IF (and only if) you know the field is all numeric values (“ABC” would be counted as it casts to zero)
the above example returns 4
assuming he has control of that aspect (and that is is “text” as of now)
but rememeber, SQLite is loose, even if “defined” as “real” or “double”, you can still stick text into the field, SQLite is not strict, as it really has no datatypes (see SQLite Affinity)
Also, if it’s defined as TEXT and he considers a blank pora_Answer field to signify a value of 0, then he might try this:
“SELECT count(*) FROM pora WHERE pora_Answer=‘0.0’ or pora_Answer=’’”
First inspect your dataset, is it consistent? Do you get something from: SELECT pora_answer FROM pora WHERE not (pora_answer>=0) OR ISNULL(pora) OR pora = ‘’ ----?
Do you have blanks? spaces? Nulls?
or as a test… try this
SELECT pora_answer,count(*) FROM pora GROUP BY pora_answerthen visually see how they got grouped
Greetings all,
Thanks for all your replies. Maybe a little background first:
The SQLite database I’m creating is a simple quiz with a 50/50 answer. Once a question is answered by the user I record their answer in the ‘pora_Answer’ field, so I can tell which questions should not be displayed again (unless the user resets the pora_Answer field back to it’s default value of zero so the question can be used again.)
The database as 5 simple fields in one table called ‘pora’; There are no blanks, spaces or nulls:
INTEGER pora_ID
INTEGER pora_Type
INTEGER pora_Answer
TEXT pora_Question
TEXT pora_Description
I created this database in Valentina Studio Pro which allows me to modify the contents and run queries on the database first so that I know they work. The weird thing is that the Count(*) function works in Studio Pro as expected, but not via the SQLSelect statement in my app.
This statement returns one record and as expected, with the total number of records:
"SELECT count(*) FROM pora"This statement returns one record, but the result is zero:
"SELECT count(*) FROM pora WHERE pora_Answer=0"I tried changing the value of the pora_Answer field to something other than zero, say 10, in case zero and NULL might be treated the same from the engine and running it again, but the same result is returned.
I can get around this by modifying my commands and loading the result of a select statement into an array of all records with the value of zero in ‘pora_Answer’, but it’s just annoying!!
Cheers
Grant
Well it is hard to tell where things are going astray… but based simply on the SQL query as it shows in your posting… There is NOTHING wrong. That being said, I’d say there is something else that is wrong.
Could you post the section of your Xojo code that creates and executes this query?
Sure Dave,
[code]if OpenDB then
dim sql as text = “SELECT count(*) FROM pora WHERE pora_Answer=10”
dim data as iOSSQLiteRecordSet
data = poraDB.SQLSelect(sql)
if data <> nil then
dim df as iOSSQLiteDatabaseField = data.IdxField(0)
return df.IntegerValue
end if
end if
return 0[/code]
Cheers
Grant
Oh no. I found the problem. I was doing a query on an old version of the database.
When I run my app in the IDE->Simulator, my app checks whether there’s already a copy of the database in the device documents folder. If there is then is does nothing. If not then it copies the database from the resources folder to the documents folder so it can be written to. When I’ve made changes to my DB via Valentina Studio Pro, the file that was being copied into the resources folder via a copyfiles step was the current version, but not the version in the documents folder.
I feel like such an ID10T.
I better not show my face at XDC Miami next year.
I sure hope nobody has spent TOO much time on this for me.
I’ve added this to my project so that the current version of the database file ALWAYS gets copied into the documents folder when running from the IDE:
[code]dim poraDBfile as FolderItem = SpecialFolder.Documents.Child(DBfileName)
#if DebugBuild
if poraDBfile.Exists then poraDBfile.Delete
#Endif[/code]
Cheers
Grant
LOL… [quote=396374:@Dave S]That being said, I’d say there is something else that is wrong.
[/quote]
Guess I was right… sorry… didn’t mean to laugh… but I have made this type of mistake many times myself.
Dave,
I’m quite happy to admit…you WERE right!!
Cheers
Grant
U can use
if OpenDB then
dim sql as text = "SELECT pora_ID FROM pora WHERE pora_Answer=10"
dim data as iOSSQLiteRecordSet
data = poraDB.SQLSelect(sql)
if data <> nil then
return data.RecordCount
else
return 0
end if
end if
return 0or simply put in a new function …
[code]
function reccount (extends table as string, filter as string)
if table = “” then return 0
if OpenDB then
dim sql as text = "SELECT * FROM " + table + " WHERE " + filter + “;”
dim data as iOSSQLiteRecordSet
data = poraDB.SQLSelect(sql)
if data <> nil then
return data.RecordCount
else
return 0
end if
end if
return 0
end function [/code]
use :
dim aaa as table
dim num_record as integer
num_record = aaa.reccount(“pora_Answer=10”)
Except Data WON’T be nil… it is only NIL if the SQL query is malformed…
In your example it will have RECORDCOUNT=0 and any attempt to read a field WILL be nil… but not the recordset itself