Page 1 of 10

My First SQLite Project

Posted: Mon Sep 14, 2009 10:09 am
by sudip
Hi Friends,

I just created my first SQLite project with facility to create databse, table and add, modify and delete records from a grid.
I added hbsqlit3 in Tools->Preferences->Additional Libraries textbox of IDE and made some changes in my old MySql project.
SQLite database app is ready!!!
sqlite.jpg
sqlite.jpg (29.81 KiB) Viewed 8324 times
Please download the source code
SQLite.zip
(9.05 KiB) Downloaded 1536 times
, compile and test.

Warning! Source code contains many quick and dirty codes, as I want to share my first excitement with you all. :)

I used some codes with (major) modification taken from samples of Harbour Installation's Contrib folder and Minigui Extended Samples.

Please don't forget to send your comments as before.

With best regards.

Sudip

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 10:25 am
by Rathinagiri
I am really happy to see this Sudip.

I want to know about the compatibility of date functions.

If we can do 'date' functions and if it is multi-user (say for less than 10 users), small projects can be ported to SQLite I think.

Thanks again Sudip. I will review your code and come back soon.

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 10:36 am
by sudip
Hello Rathi, my MySQL guru,

Thanks for your kind words. I used many techniques learned from you :)

SQLite doesn't have date field. I have a plan. I want to store date in string format using DTOS() function and retrieve using STOD() function.

But, I haven't tested it yet.

As per the site http://www.sqlite.org. It is multi user, but we can't expect it to run like MySql. For, Client/Server application, they recommend Client/Server RDBMS.

With best regards.

Sudip

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 10:40 am
by swapan
Good R&D Sudip!

Rathi has raised valid points. Will check & revert.

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 11:02 am
by Rathinagiri
Yes. It doesn't have separate date 'type' fields. Everything is treated as characters and numbers.

However, they have date functions to use date strings.

For converting those strings, we could use DTOS() and STOD() as you have mentioned.

Long time ago, I tested SQLite too. At that time, I used to verify a string to be date, if it is in ANSI format, ie., yyyy-mm-dd. If it is, I would convert to date. See this sql function.

Code: Select all

function sql(dbo1,qstr)
local table := {}
local currow := nil
local tablearr := {}
local rowarr := {}
local datetypearr := {}
local numtypearr := {}
local typesarr := {}
local current := ""
local i := 0
local j := 0
local type1 := ""
table := sqlite3_get_table(dbo1,qstr)
if sqlite3_errcode(dbo1) > 0 // error
   msgstop(sqlite3_errmsg(dbo1)+" Query is : "+qstr)
   return nil
endif 
stmt := sqlite3_prepare(dbo1,qstr)
IF ! Empty( stmt )
   for i := 1 to sqlite3_column_count( stmt )
      type1 := sqlite3_column_decltype( stmt,i)
      do case
         case type1 == "TEXT"
            aadd(typesarr,"C")
         case type1 == "INTEGER" .or. type1 == "REAL"
            aadd(typesarr,"N")
      endcase
   next i
endif
sqlite3_reset( stmt )
   
if len(table) > 1
   asize(tablearr,0)
   rowarr := table[2]
   for i := 1 to len(rowarr)
      current := rowarr[i]
      if typesarr[i] == "C" .and. len(alltrim(current)) == 10 .and. val(alltrim(substr(current,1,4))) > 0 .and. val(alltrim(substr(current,6,2))) > 0 .and. val(alltrim(substr(current,6,2))) <= 12 .and. val(alltrim(substr(current,9,2))) > 0 .and. val(alltrim(substr(current,9,2))) <= 31 .and. substr(alltrim(current),5,1) == "-" .and. substr(alltrim(current),8,1) == "-" 
         aadd(datetypearr,.t.)
      else
         aadd(datetypearr,.f.)
      endif
   next i
   for i := 2 to len(table)
      rowarr := table[i]
      for j := 1 to len(rowarr)
         if datetypearr[j]
            rowarr[j] := CToD(SubStr(alltrim(rowarr[j]),9,2)+"-"+SubStr(alltrim(rowarr[j]),6,2)+"-"+SubStr(alltrim(rowarr[j]),1,4))
         endif
         if typesarr[j] == "N"
            rowarr[j] := val(rowarr[j])
         endif    
      next j
      aadd(tablearr,aclone(rowarr))
   next i
endif
return tablearr
I had used this code to create a nice project called LogiBase for logistics management. My client didn't want to install MySQL and wanted the database to be a single file. It is single user. Hence I had used SQLite and even now they are using the same.

This is the logo of the program. :)
tanfama.jpg
tanfama.jpg (21.55 KiB) Viewed 8312 times
This is a screenshot.
screenshot.jpg
screenshot.jpg (73.44 KiB) Viewed 8312 times

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 12:46 pm
by Roberto Lopez
sudip wrote:Hi Friends,

I just created my first SQLite project with facility to create databse, table and add, modify and delete records from a grid.
<...>
The sample is very interesting.

My question is, what is the benefit of using a SQL local engine, instead the built in Harbour RDD's?

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 12:51 pm
by sudip
Rathi,

Thanks a lot for the function. This will be very much helpful to all of us (especially for me right now :lol: )

Screenshot of your old Sqlite program is excellent.

Regarding date and other fields, I read from their site that, Sqlite can typecast our data to it's specific field type!!!

I read from your old post that Sqlite is good for 3/4 users. Again, I hope it is better than dbf and VFP tables (dbf) :)

And, I hope it will be helpful for my small clients :)

Regarding this project, God inspired me to convert my first MySql project to SQLite on a special day of my life.

Thank you all.

Sudip

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 1:02 pm
by sudip
Hello Master Roberto,

Thank you very much for your interest in this humble project.
Roberto Lopez wrote:...
My question is, what is the benefit of using a SQL local engine, instead the built in Harbour RDD's?
I am trying to answer this within my limited knowledge.

I found that DBF/CDX files are easily corrupted. So, I was looking for a good RDBMS. I was learning MySql. As MySql RDD has some problems regarding update data, I used TMySql class (I learned from Rathi). Then, I need one DBMS which will be installation free (like DBF/CDX) for my small clients, who are mainly FMCG (Fast Moving Consumer Goods) distributors, so that I need not to visit their place ;). In the meantime I read about licensing issue of MySql. I planned to try for PostGreSQL, but I read it is not very fast and again my clients have to install it.

Then I tried SQLite. I don't know whether there is any any RDD already available for SQLite.

Now, I need your advice about Pos and Cons of using it :)

With best regards.

Sudip

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 1:12 pm
by Rathinagiri
...on a special day of my life.
That means, today is your Birth day?!

Sorry, I had not seen the front page. :(

Many more happy returns of the day Sudip. :)

Re: My First SQLite Project

Posted: Mon Sep 14, 2009 1:16 pm
by Rathinagiri
IMHO the needs for SQLite:

1. Use of SQL queries.
2. Index would be done automatically every time.
3. Single file per database of numerous tables.
4. Mutli-user
5. Speed
6. Security (Password protection)