My First SQLite Project
Moderator: Rathinagiri
My First SQLite Project
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!!! Please download the source code , 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
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!!! Please download the source code , 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
Sudip
- 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
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.
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.
South or North HMG is worth.
...the possibilities are endless.
Re: My First SQLite Project
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
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
Sudip
Re: My First SQLite Project
Good R&D Sudip!
Rathi has raised valid points. Will check & revert.
Rathi has raised valid points. Will check & revert.
- 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
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.
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. This is a screenshot.
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
This is the logo of the program. This is a screenshot.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
- Roberto Lopez
- HMG Founder
- Posts: 4004
- Joined: Wed Jul 30, 2008 6:43 pm
Re: My First SQLite Project
The sample is very interesting.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.
<...>
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)
Roberto
(Veritas Filia Temporis)
Re: My First SQLite Project
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
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
With best regards,
Sudip
Sudip
Re: My First SQLite Project
Hello Master Roberto,
Thank you very much for your interest in this humble project.
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
Thank you very much for your interest in this humble project.
I am trying to answer this within my limited knowledge.Roberto Lopez wrote:...
My question is, what is the benefit of using a SQL local engine, instead the built in Harbour RDD's?
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
Sudip
- 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
That means, today is your Birth day?!...on a special day of my life.
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.
South or North HMG is worth.
...the possibilities are endless.
- 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
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)
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.
South or North HMG is worth.
...the possibilities are endless.