My First SQLite Project

[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 (29.81 KiB) Viewed 23779 times

Please download the source code

SQLite.zip

(9.05 KiB) Downloaded 2339 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

[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.

[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

[swapan]

Good R&D Sudip!

Rathi has raised valid points. Will check & revert.

[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 (21.55 KiB) Viewed 23767 times

This is a screenshot.

screenshot.jpg (73.44 KiB) Viewed 23767 times

[Roberto Lopez]

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?

[sudip]

Rathi,

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

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

[sudip]

Hello Master Roberto,

Thank you very much for your interest in this humble project.

...
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

[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.

[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)