Filter in SQLite

Databases
#1

In MS SQL Server you can filter records in a recordset like this :

RecordsetName.Filter = “Fieldname = 5”

Is it possible to do the same in SQLite ? Or is there an alternative ?

#2

Why wouldn’t you include that in the where clause of the query? Just curious.

#3

In stead of using the filter, I also can use the WHERE clause, but …
The query-structure is rather complicated :

sql = "SELECT MAGSPEC.THERSOORT, SPECTHER.THERNUM, SPECTHER.THERDOSIS, MED_SPEC.NAAM, MED_SPEC.VERPAK, "
sql = sql + "MED_SPEC.DOSIS, MED_SPEC.RIZIV, MED_SPEC.SOORT, MED_SPEC.BIJNUM "
sql = sql + "FROM MAGSPEC INNER JOIN SPECTHER ON MAGSPEC.NUMREC = SPECTHER.id "
sql = sql + "INNER JOIN MED_SPEC ON SPECTHER.THERNUM = MED_SPEC.ZOEKN WHERE MAGSPEC.PCODE = ? AND "
sql = sql + "MAGSPEC.THERDAT = ? AND MAGSPEC.THERSOORT = 's'"

I must call this query every time that I select another date in a listbox.
And this takes too much time.

#4

That is far from be a complicated query.

  1. make sure your select is only required fields
  2. make sure you have proper indexes (use the EXPLAIN feature of your database to check this)
#5

Tx Dave.
I use ALL the fields I select in the query.
But your right, there are no indexes !
When you examine my query, for which fields have I to create an index please ?

#6

real quick glance … you might try

Index #1 : MagSpec by NUMREC
Index #2 : Specther by ID
Index #3 : Specther by Thernum
Index #4 : Med_Spec by Zoekn

Since the joins are seperate, you don’t want to combine index #2 and #3 even though it is the same table

#7

I do this and I give you the results asap !
Tx

#8

[quote=63256:@Antoon Verleysen]Tx Dave.
I use ALL the fields I select in the query.
But your right, there are no indexes !
When you examine my query, for which fields have I to create an index please ?[/quote]
Start by indexing primary and foreign keys
If the column is not very selective (i.e. there are 2 distinct values in millions of rows) don’t bother as they are usually not used in that case

When in doubt run the query in a tool that you can use “EXPLAIN” to see what the query optimizer will do

#9

DAVE :
I did what you told me, and it works !!!
I’m a happy man.
Norman :
What do you mean with " run the query in a tool that you can use “EXPLAIN” …
How do I create such a tool ?

#10

There are many available on the internet. Do a search for “sqlite query browser”.

#11

[quote=63262:@Antoon Verleysen]DAVE :
I did what you told me, and it works !!!
I’m a happy man.
Norman :
What do you mean with " run the query in a tool that you can use “EXPLAIN” …
How do I create such a tool ?[/quote]
If you’re on OS X you can us SQLite from the command line and have it explain its query plans

#12

Hello Antoon… (goedemiddag)…

I use the tools supplied by SQLabs. It allows you to look into the table structure, indices and you can restructure a table holding data. I use it to test SQL statements against tables too. If it runs in the Manager you know the query itself contains no errors but your query set-up in Xojo probably does.

#13

Hi Alexander (ook een goedemiddag…)

Do you mean the tool SQLiteManager ?
I tried it out. The program seems ok, but I miss the graphical layout + connections of the different tables.
Tx anyway.

PS : Ik heb deze post ook op de Nederlandse groep geplaatst. Echter geen teken van leven.

#14

Yes, the SQLitemanager. It’s a cheap tool and indeed there is no graphical layout of tables and connections. If you want that take a look at Valentina Studio.

#15

Tx Alexander.
I’ll try it out also.

#16

For the Mac, you might also try this: SQLEditor by Malcom Hardie

#17

It looks great ! I download the trial.
Tx

#18

If you are talking about SQLEditor, I should also say that the tech support has been very prompt and good.

#19