My First SQLite Project

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

My First SQLite Project

Post 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 17088 times
Please download the source code
SQLite.zip
(9.05 KiB) Downloaded 2062 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
Last edited by sudip on Mon Sep 14, 2009 10:46 am, edited 1 time in total.
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post 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.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My First SQLite Project

Post 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
Last edited by sudip on Mon Sep 14, 2009 10:43 am, edited 1 time in total.
With best regards,
Sudip
User avatar
swapan
Posts: 242
Joined: Mon Mar 16, 2009 4:23 am
Location: Kolkata, India
Contact:

Re: My First SQLite Project

Post by swapan »

Good R&D Sudip!

Rathi has raised valid points. Will check & revert.
Thanks & Regards,
Swapan Das

http://www.swapandas.com/
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post 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 17076 times
This is a screenshot.
screenshot.jpg
screenshot.jpg (73.44 KiB) Viewed 17076 times
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: My First SQLite Project

Post 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?
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My First SQLite Project

Post 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
With best regards,
Sudip
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My First SQLite Project

Post 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
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post 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. :)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post 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)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
Post Reply