sql fail i dont understand

Databases
1

Hi there,

i tried this in xojo:

        'check if user already in DB
        System.DebugLog("select count(1) from user where username = '"+ txt_user.Text +"'")
        Dim userdata as RecordSet = db.getSQL("select count(*) from user where username = '"+ txt_user.Text +"'")
        Dim result as string = userdata.Field("count(*)")
        
        msgbox result
        
        if result = "1" then
          msgbox "User already exists..."
          return
        else

There is a User with the name Tester in the DB… when i now use the name Tester0 the count return a 1… but this is impossible in SQLiteStudio this select returns a 0 of course. Then when i try Tester00 its ok…

Xojo puts this select to sql which should normally return a 0: select count(*) from user where username = ‘Tester0’

2

don’t you need this code : userdata.Field("count(*)").stringvalue ?

3

hm dunno first i tried it as integer and with val(userdata…)

4

Dim result as string = userdata.IdxField(1).StringValue

5

personally I dislike the use of indexed return values, what if you change the SQL later, and add or move field names within the statement?

I would suggest this instead… and while its not a big deal with this simple statement, I think its a good habit to get into

Dim userdata as RecordSet = db.getSQL("select count(*) AS CNT from user where username = '"+ txt_user.Text +"'")
Dim result as string = userdata.Field("CNT").stringvalue

also, since you are getting COUNT, which is a NUMBER, I’d return it as a NUMBER

Dim result as integer = userdata.Field("CNT").integerValue
6

If you only want to know if the user is in the database can’t you just use EOF?

Dim userdata as RecordSet = db.getSQL("select * from user where username = '"+ txt_user.Text +"'") Dim result as Boolean = Not(userdata.EOF)

I would also use a prepared statement to avoid SQL Injection issues.

7

[quote=279374:@Wayne Golding]If you only want to know if the user is in the database can’t you just use EOF?

Dim userdata as RecordSet = db.getSQL("select * from user where username = '"+ txt_user.Text +"'") Dim result as Boolean = Not(userdata.EOF)

I would also use a prepared statement to avoid SQL Injection issues.[/quote]

using "" as you did, causes the Database engine to create a fully populated data vector when one is not required, plus if the result of the where statement results in more than ONE return result, then you have THAT overhead and time as well. Best practice is to never return more than you need…
and actually using COUNT(8) instead of COUNT() is even more effective.

8

Yeah, that, and to make the code work with names like “O’Neill”. See: xkcd: Exploits of a Mom

9

Or just use a prepared statement that uses an exists clause
Then the db engine can short circuit the existence check on the first result rather than having to do a count of possibly many rows This way you avoid the aggregate entirely

select 1 from user where exists ( select 1 from user where username = ‘"+ txt_user.Text +"’")

all this will tell you is there IS one or more user(s) with that name

10

[quote=279397:@Norman Palardy]Or just use a prepared statement that uses an exists clause
Then the db engine can short circuit the existence check on the first result rather than having to do a count of possibly many rows This way you avoid the aggregate entirely

select 1 from user where exists ( select 1 from user where username = ‘"+ txt_user.Text +"’")

all this will tell you is there IS one or more user(s) with that name[/quote]
I’d be interested in seeing the “EXPLAIN PLAN” on both of those :slight_smile:

11

Depending on the table definition mine can be quicker or slower

Postgresql 9.3
-- Table: "user"

-- DROP TABLE "user";

CREATE TABLE "user"
(
  username character varying(25)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "user"
  OWNER TO postgres;

explain select 1 from  public."user" where exists ( select 1 from public."user" where "user".username = 'foo')

"Result  (cost=5.09..23.39 rows=830 width=0)"
"  One-Time Filter: $0"
"  InitPlan 1 (returns $0)"
"    ->  Seq Scan on "user" user_1  (cost=0.00..20.38 rows=4 width=0)"
"          Filter: ((username)::text = 'foo'::text)"
"  ->  Seq Scan on "user"  (cost=0.00..18.30 rows=830 width=0)"

explain select count(8) from public."user" where "user".username = 'foo'
"Aggregate  (cost=20.39..20.40 rows=1 width=0)"
"  ->  Seq Scan on "user"  (cost=0.00..20.38 rows=4 width=0)"
"        Filter: ((username)::text = 'foo'::text)"

But with this table definition its quite different

// ===============================
-- Table: "user"

-- DROP TABLE "user";

CREATE TABLE "user"
(
  username character varying(25) NOT NULL,
  CONSTRAINT pk_key PRIMARY KEY (username)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "user"
  OWNER TO postgres;

explain select 1 from  public."user" where exists ( select 1 from public."user" where "user".username = 'foo')

"Result  (cost=8.17..26.47 rows=830 width=0)"
"  One-Time Filter: $0"
"  InitPlan 1 (returns $0)"
"    ->  Index Only Scan using pk_key on "user" user_1  (cost=0.15..8.17 rows=1 width=0)"
"          Index Cond: (username = 'foo'::text)"
"  ->  Seq Scan on "user"  (cost=0.00..18.30 rows=830 width=0)"

explain select count(8) from public."user" where "user".username = 'foo'

"Aggregate  (cost=8.17..8.18 rows=1 width=0)"
"  ->  Index Only Scan using pk_key on "user"  (cost=0.15..8.17 rows=1 width=0)"
"        Index Cond: (username = 'foo'::text)"

The right answer is “it depends and testing will tell you which to use”

12

[quote=279375:@Dave S]using "" as you did, causes the Database engine to create a fully populated data vector when one is not required, plus if the result of the where statement results in more than ONE return result, then you have THAT overhead and time as well. Best practice is to never return more than you need…
and actually using COUNT(8) instead of COUNT() is even more effective.[/quote]

You know what Dave, 99 times out of 100 I want to at least know the existing users id, so whilst your are totally correct in the overhead it would be worse if a second query requested that data.

13

connecting to databases that are far far away, I’m ok with Dave on that point !
every single connection must be returning something useful and only that.
otherwise you end up with slow apps…

14

I would use a prepared statement checking for the id.
Once you get used using prepared statements and have some methods to just use it easily, you never go back to plain SQL.