Doubt about SQLite

[copiloto]

Hello,

I'd like someone could help me, please:

I know how to make a simple query in SQLite when this query is about numbers. Ex: SELECT * FROM mytable WHERE age>5... In this case I have no problem.

The thing is that I have a database of strings and I would like to make queries of two types:
1) For example, cities that starts by "NE" (NEW YORK)
SELECT cityname FROM mytable WHERE ??? (cityname 'starts by' "NE")

2) The other question: cities that include a string into their names: "OR" (NEW YORK)
It shoud be something like SELECT cityname FROM mytable WHERE ??? ("OR" 'into cityname')

I will be very grateful for your help.

Thanks for advanced.

 

[jeterry]

I believe this is what you are looking for.

SELECT cityname FROM mytable WHERE cityname like "NE" or cityname like "NEW YORK"

I use this on the device and offer the user two different methods for searching through a inventory data base that has around 15000 records and it's very fast.


Hope this helps

 

[RB Smissaert]

1.
case sensitive:
SELECT cityname FROM mytable WHERE cityname glob 'NE*'

case in-sensitive:
SELECT cityname FROM mytable WHERE cityname like 'NE%'

2.
case sensitive:
SELECT cityname FROM mytable WHERE cityname glob '*NE*'

case in-sensitive:
SELECT cityname FROM mytable WHERE cityname like '%NE%'


RBS

 

[copiloto]

Thank you!!!

Thanks very much to jeterry and to RB Smissaert. I am really happy!:sign0060:

I was looking for this too much time into the sqlite web, but I didn't found any solutions to my doubt. I have been making some programs of databases that taked too much time to recover information (the databases are about 26000 records...) In the cases of small databases, using the Basic4ppc sentences is quick and easy, and I'll keep doing this in this way.

Sincerely, I'm grateful and in debt to you!

 

[RB Smissaert]

that taked too much time to recover information (the databases are about 26000 records...)

That is a small table and when done the right way it should run very fast.
Maybe post your code and somebody will comment how to make it faster.
Another option is to post to the SQLite forum.

RBS

 

[copiloto]

My SQLite programs

Yes, of course, I'll be pleased to show my examples. They are very easy but I like doing things at the easiest and simple way.. I have to say that I forgot to say that these programs are runned on a pocket pc, and not on a PC.. So I think it's normal they take sometime running on the pda.

First case:
I take from the web a list of radars that contains about 6000 Lines, and each one is composed of: coorx, coory, type, speed and direction of driving.
Using GPS library I receive the speed, the coorx and coory of my car and then compare it with this "big" table at real time. It takes 3 or 4 seconds to make the comparation... I know that there must be much more eficient algorithms to do this, for example doing 4 divisions of the table, depending of my situation...

Second case:
I have a "big" table of medicines: 26000 lines. They have several differents names and quantities of mg. Ex: Cardyl 40 mg. It's very useful to be able to find names of medicines that are very similar to this one because sometimes doctors write the name of de medicine very bad and you can't understand what it's writed. Then introducing two o tree pieces of the names you can rescue the names of those... Ex: "ar", "40", "C"

My way of doing this from before to now has been using the strindexof command. It's very good when tables are not too big. On the computer this command works very fast even with my tables, but on my pocket pc... Apart from this, my pocket uses microSD memory... It,s a telephone and pda.

My code:


Sub Globals

End Sub

Sub App_Start
Form2.show
con.New1
reader.New1
command.New1("",con.Value)
con.Open("Data Source=" & "speedcam.sl3")
End Sub

Sub Button1_Click

command.CommandText="SELECT speed FROM speedcam WHERE (speed>myspeed AND sqrt((mycoorx-coorx)^2+(mycoory-coory)^2)<0.01
' in this line I have calculated the distance of my car to the next radars'

command.ExecuteTable("Table1",1) ' I only need the presence of one for the alarm sounds'

IF table1.Count<>0 THEN alarm... 'Sound the alarm if table1 is not empty.'


Really, I can imagine that doing a proffesional navigator with all the features that they include: graphics 3d, dates of speed, radars, menus, routes, recalculating, best way, shortest way... and the pda that today are in the market ( I think that they are slow) is not very easy...

Thanks for helping again!!

 

[RB Smissaert]

To see what could be the problem we need a bit more information:

Could you post the create table statement of speedcam?
Could you post the indexes of speedcam?
Do you need to create a table?
Maybe doing:
Reader.Value = cmd.ExecuteReader
Reader.GetValue(0) etc.
is faster.
Have you looked at the Pragma commands to change page size etc?

Also look at these Pragma's:

cmd.CommandText = "PRAGMA synchronous = OFF;"
cmd.ExecuteNonQuery

cmd.CommandText = "PRAGMA journal_mode = OFF;"
cmd.ExecuteNonQuery

Lastly, could you take some calculations out of the SQL and let your desktop
app do that and put it ready in the database?

Just some thoughts.
I am sure there is plenty of scope to make it a lot faster.


RBS

 

[copiloto]

to

Hello, RB Smissaert.

I've been doing some tests and you are right. Using this command.text:

SELECT count(*) FROM speedcam WHERE ((x-a)+(y-b))<0.01 takes around 2 second in the calculation.

Other different case is when I write:

SELECT * FROM speedcam WHERE ((x-a)+(y-b))<10 Then it takes around 30 seconds. Is it because of having to write the results into the screen?? The results are around 5400... This is (I think) the neck of bottle... (Really in the life is almost unnecessary to do this...)

Apart from this, I have realise that GPS library get an information event every 4 seconds although I specified that it were every second...

Yet, the statement of create table that I use is simple:

1) table1.LoadCSV("/speedcam.txt",",",true,true)
2) connection.Open("Data Source=" & "speedcam.sl3")
3) connection.CreateSQLTable("Table1","speedcam")

You recommended to me trying this:
Reader.Value = cmd.ExecuteReader
Reader.GetValue(0) etc.

but sincerely I don't know how to work in this way. I've read in the forum and I think that it uses node.add or something like this, but still don,t know how to...

"Also look at these Pragma's:"

cmd.CommandText = "PRAGMA synchronous = OFF;"
cmd.ExecuteNonQuery

cmd.CommandText = "PRAGMA journal_mode = OFF;"
cmd.ExecuteNonQuery

Sorry again!!! I'm still a very newbie in SQL... I don't know the utility of pragma's. I've been reading the sqlite web but It's a bit complicated to understand for me... sign0013:

Generally in the tests I've made (and my little knowledge) the pda in comparation with the computer is much slower writting "big" tables than calculating "count(*)"

Thanks very much for your responses. I'll be glad to receive any commentary of you.

 

[RB Smissaert]

Does your SQLite table speedcam have any indexes?
It sounds it doesn't and that may be one of the problems.
Also it will be helpful if you explained in a simple summary what your setup is.
Eg what is the exact layout of speedcam, where do you get that .csv from etc?

RBS

 

[copiloto]

Hello,

I've been reading for today about SQLIte. Yet, although much commands are explained, there are very few examples... Apart from this, I have some doubts about running this commands on Basic4ppc. Are they recognized by B4ppc??

RB Smissaert, my tables haven't got any indexes (at least I think so not...) But I've been reading it's very important for performance of SQLite (just what you said). I have't found the way to work the command "CREATE INDEX" on Basic4ppc this afternoon... I'd like to be able to see an example of how to use it on B4ppc...

I'll put a piece of database of medicine (26000 rows),(includes string and numbers). It contains several fields, and every field is exactly delimited by the length of this field:

Field 1: position 1-6--> code of medicine
Field 2: " 7-37> name of medicine
Field 3: " ...........
..............
Field aprox 9: " 91-98> price

The archive is a simple .txt which you can see. What I do to transform it to .csv format is:

1) Import this .txt from Word and change "," for "."
2) Import this .txt from Excel and save like .csv
3) Change the extension of ".csv" for ".txt" and so I get a simple .txt but .csv archive.
4) Insert in the first line of the new archive the name of the fields: a, b, c, d ....p
4) Table1.load(name of archive,true,true)
5) connection.Open("Data Source=" & "nameofarchive.sl3")
6) con.CreateSQLTable("Table1","radares3")
Finish.

Now the queries:
1) connection.Open("Data Source=" & "nameofarchive.sl3")

2) CASE A) command.CommandText="SELECT count(*) FROM nameofarchive WHERE a=712341" (In this case it takes 3 seconds find and show the only one row table1. Obviously showing a table of one row doesn't take too much time...)

CASE B.1) command.CommandText="SELECT count(*) FROM nameofarchive WHERE b LIKE '%at%' & b LIKE '%o%' " (it takes also around 3 seconds: Result of query: 2259 matches)

CASE B.2) command.CommandText="SELECT * FROM nameofarchive WHERE b LIKE '%at%' & b LIKE '%o%' " (the same case that B.1 but now it has to write the rows into the table1... it takes 12 seconds.) Is it normal??

CASE C.1) command.CommandText="SELECT count(*) FROM nameofarchive WHERE b LIKE 'CITALOPRAM%' (around 3 seconds also for 71 matches)

CASE C.2) command.CommandText="SELECT count(*) FROM nameofarchive WHERE b LIKE 'C%' (around 3 seconds for 2934 matches)

CASE C.3) command.CommandText="SELECT * FROM nameofarchive WHERE b LIKE 'C%' (around 15 seconds for 2934 matches)

3)command.ExecuteTable(“Table1”,0)



Well, Excuse me for having writed too much... Thanks again for reading and helping me!!!

P.D: Like you can see, The use of "%..%" was very important for finding the name of medicines...

 

[RB Smissaert]

Here a simple example how to write a fixed length text file to a SQLite table.
Note that in this example there are 3 fields only with the fixed lenghts of 2, 3 and 4. You will have to adjust this for your particular text file.
This code will run on the desktop. Also note that this does away with the table
control as I don't think you need that. This example sets an index on field one, but your particular data may require more indexes or maybe an index that combines multiple fields. You just will need to experiment and see what gets you fast results.

Sub FixedLengthFile2SQLite

   Dim i
   Dim p
   Dim l
   Dim f
   Dim strFile
   Dim lFileLen
   Dim str
   Dim strSQL
   
   strFile = "C:\FixedTest.txt"
   
   'drop the table if it exists already
   strSQL = "DROP TABLE IF EXISTS TEST"
   cmd.CommandText = strSQL
   cmd.ExecuteNonQuery
   
   'create the SQLite table
   strSQL = "CREATE TABLE TEST([FIELD1] TEXT, [FIELD2] TEXT, [FIELD3] TEXT)"
   cmd.CommandText = strSQL
   cmd.ExecuteNonQuery
   
   'create the insert SQL with the parameters
   strSQL = "INSERT INTO TEST(FIELD1, FIELD2, FIELD3) VALUES(@value1, @value2, @value3)"
   cmd.AddParameter("value1")
   cmd.AddParameter("value2")
   cmd.AddParameter("value3")
   cmd.CommandText = strSQL
   
   'get the file size in bytes
   lFileLen = FileSize (strFile)
   
   'open the text file
   FileOpen (f, strFile, cRandom)
   
   'wrap all the inserts in an transaction to make it a lot faster
   con.BeginTransaction 

   'do all the inserts
   Do While p * 9 < lFileLen
      cmd.SetParameter("value1", FileGet(f, p * 9, 2))
      cmd.SetParameter("value2", FileGet(f, p * 9 + 2, 3))
      cmd.SetParameter("value3", FileGet(f, p * 9 + 5, 4))
      cmd.ExecuteNonQuery
      p = p + 1
   Loop
   
   'create an index on FIELD1
   strSQL = "CREATE INDEX IDX_TEST_FIELD1 ON TEST(FIELD1)"
   cmd.CommandText = strSQL
   cmd.ExecuteNonQuery
   
   'finalize the transaction, so write the data to disc
   con.EndTransaction 
   
   'close the text file
   FileClose(f)

End Sub

RBS

 

[copiloto]

SQLite example

Hello!!

Above all, I want to thank to you for your effort and your time writing this example.

I've been testing your example and almost make it running... I have studied the "INSERT" sentence because addparameter and setparameter are unknown for me... Yet, I think that the insert sentence can sustitute it. I find some errors that think will be solved soon.. I'm trying this:


Code:

strSQL="CREATE TABLE mytable(a text, b text, c text)"
cmd.commandText=strSQL
cmd.Executenonquery


FileOpen(f,strFile,cRandom)

con.BeginTransaction

for p=0 to 10
x=fileget(f,p*100,6)
y=fileget(f,7+p*100,30)
z=fileget(f,37+p*100,6)

cmd.CommandText="INSERT INTO mytable (a,b,c) VALUES ("&x&","&y&","&z&") "
cmd.ExecuteNonQuery

next p

con.EndTransaction


I get a strange error: cmd.ExecuteNonQuery Error description: SQLite error no such column: VITAMIN Continue?

I can't understand why if a omit y and z it works, It only work with x parameter... When I discover the error i'll post it here.

Otherwise, I have perfectly understood the use of indexes for getting best performance on queries. I have used it in my cmd.executeTable and it works fine. I have got matches from 3-4 seconds to only less than 1 second when using 'GLOB' to looking for the beginning of the word. When it's refered about looking for a piece of string not necesary at the beginning of the line, then it takes about 3 seconds: LIKE (%INA%) OR GLOB (*INA*), Perhaps like you say, the secret is discover how to use the indexes or perhaps it is so. Yet, I'm very happy because this type of search taked me between 30-90 seconds before...

Thanks very much for your help.

 

[RB Smissaert]

When it's refered about looking for a piece of string not necesary at the beginning of the line, then it takes about 3 seconds: LIKE (%INA%) OR GLOB (*INA*)

In that case an index can't be used. Compare it to looking somebody up in a phonebook. It is fine to find people where the Surname starts with Smi, but how could you handle finding people with mit in the surname but not at the beginning of that surname?
To see if SQLite uses an index or not use this:
explain query plan - followed by the SQL

I would not use variables in the insert SQL (instead of AddParameter and SetParameter for 2 reasons:
1. It will be slower
2. It will give you problems with quotes
Just read it up in the SQL help, it reallly is very simple.
In fact not using the parameters might explain this problem:

I get a strange error: cmd.ExecuteNonQuery Error description: SQLite error no such column: VITAMIN Continue?

It looks you are on the right track though.


RBS

 

[copiloto]

It works fine!!

Well, finally I got it... I'm really happy! The two aplications which I have used SQLite will be much better now.

All written below is calculated for a Airis pda, 400 mhz.

1) Database of medicines (26000 rows of 100 chars).-

Case a) To find the medicine code (it's not ordered)
Before: It takes me around 1 second
Now: I've learned the importance of having a index at the
end of a book... Indexing: 0.07 seconds/search. Around 14 times
quicker

Case b) To find the medicine name(name are ordered)
Before: I didn't know how to do it, so i did it by conventional searching
sentences. It taked around 9 seconds.
Now: I've learned the GLOB and LIKE properties... 0.13 sec without
index, 0.06 indexing. Around 150 times faster!!
Case c) To find a substring of the medicine name
Before: The same time: 9 seconds
Now: 2.5 sec without and with indexing

2) Database of radars

360 degrees are equivalent to around 40.000 kms of circumference.
1 degree------------------------------------- X kms

X=111,111111 km/degree ;

111,111111 km ------------------------------ 1 degree
1 km ------------------------------ x degree

X=0.009 --> 0,01 aprox. deg/km

-The necesary precision for our navigator is 0,01 deg (it will be desirable that sounds when distance<1 km...)
-The GPS library shows an innecesary number of decimals. Precision of 1 meter needs only 6 decimal (near 5... because 0.000009). But for our navigator is necesary only two.
-Before this post, I used to do this by calculating distance between two near points like if earth was plane (oh God, in this time I should be in the bonfire...):

distance=sqrt((x1-x2)^2+(y1-y2)^2)
it can be approximate by dist=abs(x1-x2)+abs(y1-y2)... In the worse case the distance will not be 1 km, but some more: 1,4 km...

This funtion penalize the time to calculate finding a posible radar near my coordinates.

-After this post, I have a trick: GLOB : SELECT speed_of_radar FROM speedcam WHERE coorx GLOB ('"&posx&"*') AND coory('"&posy&"*')
Previously I had formatted posx and poxy: format(posx,"n2") and posy the same, and posx & posy are our GPS longitude and latitude.

Before: 4 seconds
Now: 0.12 seconds-------> 33 times faster...

I am very grateful to you, RB Smissaert for your great help. Thanks very much.

PD: Lately I have known that some cars of great range have an utility that consist in to be able to call to emergency number if the driver has an accident. Of course that cars are equiped of sensors, but I think that some types of accidents would be able to detect by our pda: Ex. stopping too quickly until vel=0, or better stipulate time to destination. If time is bigger than estipulate ask for some problem: button no--> no problem. No answer:---> sms to family with coordinates and/or calling 112...

 

[RB Smissaert]

OK, all nicely sorted then and some other people may have learned from this as well. I noticed you still use the variables in the SQL and there is a third drawback from that (although problably not in your particular case) and that is called SQL injection attack. Google for that and you will see what that is about.
One more reason to use the parameters.

RBS

 

[copiloto]

Using addparameter

Hello!

Because of I always used to programe on my pda and not on th PC, I wondered why doesn,t appeared an option (just addParameter and setParameter) after writing "cmd."

I have seen today that effectively it appears in the PC and not in my pda. I don't now if my pda's SQLite libraries are not updated... I'll check it. Probably is only a PC SQLite Library feature.
Although yout said "This code will run on the desktop" I never thought in a posible libraries difference.

Finally, using addParameters I have builded the table and works fine.

 

[RB Smissaert]

OK, I think you are done with this then.
Intellisense does work with me on the device. Probably you are missing some
library or component. Can't see much point though to program on the device.
If you want to go for further speed then look at the different pragma's particularly page size. Synchronous off can make a big difference if you are writing to the DB on the device.

RBS

 

[Erel]

You are most probably running an older SQLDevice.dll file on your device.
Add/SetParameter where added in the latest update. Both methods are supported on both desktop and device.

 

[copiloto]

Ok, thanks for your information both RB Smissaert and Erel. I'll update my libraries and myself...