RecordSet is never Nil. What am I doing wrong?

Vladimir_Stevic · February 13, 2016, 1:44am

First of all, let me just say I’m new at this…

This is what I want to do:
I want to select all persons from “person” database with personal ID from my Text Field.
If there is a result of such query, then I send message, else I update my table.
This code always returns rs <> Nil. Why? I’m staring at it for hours and I don’t have a clue.
.
dim rs as RecordSet
if dbas.Connect() then
rs = dbas.SQLSelect(" SELECT * FROM person WHERE persid LIKE ‘"+PersID.Text+"’ ")
if rs <> Nil then
MsgBox Person ID already exists.
.

DaveS · February 13, 2016, 1:49am

The recordset is not nil, and this is correct
a NIL recordset does not exist, in your example, the recordset DOES exist… .and has the structure matching the table “person” due to the “*” operator.
HOWEVER… the recordset may be EMPTY… the difference is… it exists, but your select statement return ZERO values, this is NOT the same as a NIL recordset

dim rs as RecordSet
if dbas.Connect() then
rs = dbas.SQLSelect(" SELECT * FROM person WHERE persid LIKE '"+PersID.Text+"' ")
if rs.recordCount>0 then 
MsgBox Person ID already exists.

Vladimir_Stevic · February 13, 2016, 1:54am

That’s it.
Thanks, Dave!

Eli_Ott · February 13, 2016, 8:29am

Alternatively:
Dim rs As RecordSet = dbas.SQLSelect(“SELECT * FROM person WHERE persid LIKE '”+PersID.Text+"’;")
If rs <> Nil Then
If Not rs.EOF Then
MsgBox Person ID already exists.
End
End[/code]

But this is how I would do it (and it is faster should person be a table with a lot of records):

If dbas.Connect() Then Dim rs As RecordSet = dbas.SQLSelect("SELECT COUNT(*) AS quantity FROM person WHERE persid LIKE '"+PersID.Text+"';") If rs <> Nil Then If rs.Field("quantity").IntegerValue > 0 Then MsgBox Person ID already exists. End End

DaveS · February 13, 2016, 2:55pm

That would only be preferable, IF you didn’t need the data about the person, and were only checking for “existance”

Also, the elephant in the room that nobody has mentioned… ALWAYS CHECK FOR DATABASE ERRORS

Maximilian_Tyrtania · February 14, 2016, 6:58am

Even that is unnecessary, select count(*) can actually be pretty slow. Just:

Select 1 from…is enough if all you want to know is if the record exists. Depending on what DB is used Select Exists might be even faster, if it is supported. Also, check if the persid field is indexed.

Eli_Ott · February 14, 2016, 8:07am

SELECT COUNT(*) and SELECT COUNT(1) are the same (for single table scans).

The web is full of statements in blogs, Stackoverflow and the like that 1 is faster than , but anytime somebody asks in a comment “are there any benchmarks” the answer is no. In addition we are probably not talking about millions of records here. The recommended way is SELECT COUNT(), which is also database vendor independent.

http://www.postgresql.org/docs/8.2/static/functions-aggregate.html
http://dev.mysql.com/doc/refman/5.7/en/counting-rows.html
https://www.sqlite.org/lang_aggfunc.html
https://msdn.microsoft.com/en-us/library/ms175997.aspx
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions032.htm
and so on

Maximilian_Tyrtania · February 14, 2016, 8:16am

I said Select 1 not select count(1), Elli. You just check if the returned recordset is empty. Select count(whatever) is actually a lot slower than select 1 in Postgres.

DaveS · February 14, 2016, 4:19pm

Everyone is ASSUMING that the existance of the record is the ONLY thing the poster wanted… in which case SELECT COUNT(8) is fine (yes 8 …or any other value) instead of "". Reason? it returns a minimial data vector which is faster than COUNT()

BUT IF the postered wanted to know IF the person existed, and if they did, he needed to do something with the data, then SELECT * is required, otherwise you have to read the database twice. once to determine existance, and once to get the data.

And for those of you who wonder if there is historical significance to using COUNT(8), the “8” is under the “*” on the keyboard

Tim_Hare · February 14, 2016, 5:50pm

I would be surprised if modern db engines don’t do a much better job of optimizing the query.

Maximilian_Tyrtania · February 14, 2016, 7:05pm

Which, looking at the OP’s example code is a pretty straight forward conclusion

This is incorrect. Count(*) and count(8) will generate the same query plan in MS SQL Server, MySQL, PostgreSQL and probably SQLite. The expression given in brackets will just skip NULL values. So, given a table with 100 records and 50 records with a null value in the first name column: Select count(firstname) will return 50. As both * and 8 will always be non null there is no difference at all (and I am not sure what you mean by “returns a minimal data vector” anyway as Select count queries will always return one integer value). Eli is correct on that account (although I think she misread my earlier post).

So to put it in a nutshell:

dim PersonWithThatIDAlreadyExists as boolean
Dim SQL as string=“Select 1 from person where persId=”+PersonIDAsGivenByUser+" limit 1"
dim RS as recordSet=DB.SQLSelect (SQL)

PersonWithThatIDAlreadyExists=(Rs.eof=false)

Note that this code doesn’t use Select count at all. Anyway, all of this is just micro optimization, but still…

DaveS · February 14, 2016, 7:44pm

data vector is the internal structure required by the database to insert the results of a query (read RECORDSET)
If the table has 50 fields, and you are seeking just a count, and use COUNT(*), that “recordset” will be constructed with 50 fields, if you use COUNT(8) [or similar variation), that “recordset” will contain ONE field.

And yes, the EXPLAIN plan will most likely be identical, and the “plan” is HOW it is going to do the query, NOT how it builds the result

Also, I would NOT suggest using LIMIT depending on your focus… As not all database engines support it, and it just might add a tiny bit of unneeded overhead…

only if you ASSUME what the posted intended for the “…” perhaps it was

ELSE
<do something with person>
END IF

but this topic has enough information for the OP to decide which direction works best for them.

Maximilian_Tyrtania · February 14, 2016, 7:57pm

No, both recordsets will just contain one field, the count. There is really no difference. You seem to confuse select count(*) with select *.

DaveS · February 14, 2016, 8:44pm

Not going to continue this fruitless argument… And I’m not confusing anything… too bad I can’t think of a concrete way to prove my point… but COUNT(*) has much more overhead than COUNT(8)… either that or thousands of DBA’s are wrong…

Per ORACLE

and as a parting piece of information

SELECT COUNT(*) from myTABLE // took 0.004 seconds
SELECT COUNT(8) from myTABLE // took 0.001 seconds

SQLITE database table with 643 records on OSX system

Maximilian_Tyrtania · February 14, 2016, 9:46pm

Ah, okay, I thought by saying “that “recordset” will be constructed with 50 fields” you somehow expected the resulting recordset to actually have 50 fields.

Eli_Ott · February 14, 2016, 9:56pm

[quote=247132:@Dave S]"Do not use count() to determine the number of rows in a table. Use count(1) or count(ROWID)
instead. These options are faster because they bypass some unnecessary operations in Oracle’s SQL
processing mechanism."[/quote]
This is from year 2000 or so… I doubt this is valid nowadays. AFAIK the query SELECT COUNT(1) is even internally replaced with SELECT COUNT() prior to being executed.

Maximilian_Tyrtania · February 15, 2016, 8:18am

[quote]SELECT COUNT(*) from myTABLE // took 0.004 seconds
SELECT COUNT(8) from myTABLE // took 0.001 seconds
SQLITE database table with 643 records on OSX system
[/quote]

Well, I think you discovered a bug in the sqlite optimizer.
I am able to reproduce this result however, but actually the other way around:

CREATE TABLE integers (i integer,k integer);
INSERT INTO integers (i,k) VALUES (0,0);
INSERT INTO integers (i,k) VALUES (1,1);
INSERT INTO integers (i,k) VALUES (2,2);
INSERT INTO integers (i,k) VALUES (3,3);
INSERT INTO integers (i,k) VALUES (4,4);
INSERT INTO integers (i,k) VALUES (5,5);
INSERT INTO integers (i,k) VALUES (6,6);
INSERT INTO integers (i,k) VALUES (7,7);
INSERT INTO integers (i,k) VALUES (8,8);
INSERT INTO integers (i,k) VALUES (9,9);

INSERT INTO integers (i,k) SELECT (tenthousands.i * 10000)+(thousands.i * 1000)+(hundreds.i * 100) + (tens.i * 10) + units.i,
(tenthousands.i * 10000)+(thousands.i * 1000)+(hundreds.i * 100) + (tens.i * 10) + units.i AS x
FROM integers AS units
CROSS JOIN integers AS tens
CROSS JOIN integers AS hundreds
CROSS JOIN integers AS thousands CROSS JOIN integers AS tenthousands;

Select (*) from integers
Query time: 1 millisecond(s), Number of affected records: 100000

select count(1) from integers
Query time: 7 millisecond(s), Number of affected records: 100000

result: 100010

Well, this might just be a sqlite 3.9.1 wrinkle.