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
Roberto Lopez
HMG Founder
Posts: 3966
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 23 times
Been thanked: 161 times

Re: My First SQLite Project

Post by Roberto Lopez » Mon Sep 14, 2009 1:30 pm

Rathinagiri, Sudip,

Thanks for pointing me the benefits of SqLite.

I have not available time right now to do it myself, but I have an idea :)

A nice thing could be from the same source code, have local and client/server access changing only something like a SET command to specify that.

So, could be easy to make some sort of intermediate layer to unify functions to access SQL (local via SqLite or remote via MySql or PostgreSql).

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

User avatar
Rathinagiri
Posts: 5263
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 169 times
Been thanked: 172 times
Contact:

Post by Rathinagiri » Mon Sep 14, 2009 1:46 pm

So, could be easy to make some sort of intermediate layer to unify functions to access SQL (local via SqLite or remote via MySql or PostgreSql).
Exactly what I have thought of. You are always many steps ahead.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
sudip
Posts: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Mon Sep 14, 2009 1:55 pm

Roberto Lopez wrote:Rathinagiri, Sudip,

Thanks for pointing me the benefits of SqLite.

I have not available time right now to do it myself, but I have an idea :)

A nice thing could be from the same source code, have local and client/server access changing only something like a SET command to specify that.

So, could be easy to make some sort of intermediate layer to unify functions to access SQL (local via SqLite or remote via MySql or PostgreSql).

Regards,

Roberto.
Yes Master Roberto,
You always have an idea many times forward than rest of us :D
With best regards.

Sudip
With best regards,
Sudip

User avatar
sudip
Posts: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Mon Sep 14, 2009 1:57 pm

Hello Rathi,
rathinagiri wrote: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)
Thank you very much for your wish :D

Can you please tell me how can I password protect SQLite database?

Thanks in advance.

With best regards.

Sudip
With best regards,
Sudip

User avatar
Roberto Lopez
HMG Founder
Posts: 3966
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 23 times
Been thanked: 161 times

Post by Roberto Lopez » Mon Sep 14, 2009 3:33 pm

Roberto Lopez wrote: So, could be easy to make some sort of intermediate layer to unify functions to access SQL (local via SqLite or remote via MySql or PostgreSql).
Some years ago, I've created a small code layer called 'MiniSql'.

Perhaps, this code could be useful to you in some way.

I'm sure that I have a backup of it, but, I can't find it :)

So, if someone has it, please upload!

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

User avatar
sudip
Posts: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Mon Sep 14, 2009 5:26 pm

Hello Master Roberto,

Is the following code you are talking about?

Code: Select all

/*
 * HMG - Harbour Win32 GUI library
 * 
 * MiniSql (a Simple MySql Access Layer)
 *
 * Copyright 2002-2005 Roberto Lopez <harbourminigui@gmail.com>
 * http://harbourminigui.googlepages.com/
 *
*/

*------------------------------------------------------------------------------*
Function SqlDoQuery( nHandle , cQuery )
*------------------------------------------------------------------------------*
Local nNumRows
Local nNumFields
Local nQueryResultHandle
Local aQuery := {}
Local aRow := {}
Local i

	if __mvexist ('SqlTrace')
		If SqlTrace == .T.
			MsgInfo (cQuery)
		EndIf
	endif

	SqlQuery ( nHandle , cQuery ) ; SqlErrorCheck ( nHandle )

	nQueryResultHandle := sqlStoreR( nHandle ) ; SqlErrorCheck ( nHandle )

	nNumRows := sqlNRows( nQueryResultHandle ) ; SqlErrorCheck ( nHandle )
	nNumFields := sqlNumFi( nQueryResultHandle ) ; SqlErrorCheck ( nHandle )

	For i := 1 To nNumRows

		aRow := sqlFetchR(nQueryResultHandle) ; SqlErrorCheck ( nHandle )

		aadd ( aQuery , aRow )

	Next i

Return aQuery

*------------------------------------------------------------------------------*
Function SqlDoCommand ( nHandle , cQuery )
*------------------------------------------------------------------------------*
Local ar

	if __mvexist ('SqlTrace')
		If SqlTrace == .T.
			msginfo(cQuery)
		EndIf
	endif

	SqlQuery ( nHandle , cQuery ) ; SqlErrorCheck ( nHandle )

	ar := SqlAffRows(nHandle)  ; SqlErrorCheck ( nHandle )

Return ar

*------------------------------------------------------------------------------*
Procedure SqlErrorCheck ( nHandle )
*------------------------------------------------------------------------------*
Local SqlCurrentError

	SqlCurrentError := SqlGetErr ( nHandle )

	If .Not. Empty ( SqlCurrentError )

		SqlQuery ( nHandle , 'UNLOCK TABLES')
		SqlClose( nHandle )

		MsgStop (SqlCurrentError ,"MiniSql")		

	EndIf

Return

#ifndef __XHARBOUR__

#pragma BEGINDUMP

#define HB_OS_WIN_32_USED

#include "hbapi.h"

HB_FUNC_EXTERN( MYSQL_GET_SERVER_VERSION      ); HB_FUNC( SQLVERSION      ) { HB_FUNC_EXEC( MYSQL_GET_SERVER_VERSION      ); }
HB_FUNC_EXTERN( MYSQL_REAL_CONNECT            ); HB_FUNC( SQLCONNECT      ) { HB_FUNC_EXEC( MYSQL_REAL_CONNECT            ); }
HB_FUNC_EXTERN( MYSQL_CLOSE                   ); HB_FUNC( SQLCLOSE        ) { HB_FUNC_EXEC( MYSQL_CLOSE                   ); }
HB_FUNC_EXTERN( MYSQL_COMMIT                  ); HB_FUNC( SQLCOMMIT       ) { HB_FUNC_EXEC( MYSQL_COMMIT                  ); }
HB_FUNC_EXTERN( MYSQL_ROLLBACK                ); HB_FUNC( SQLROLLBACK     ) { HB_FUNC_EXEC( MYSQL_ROLLBACK                ); }
HB_FUNC_EXTERN( MYSQL_SELECT_DB               ); HB_FUNC( SQLSELECTD      ) { HB_FUNC_EXEC( MYSQL_SELECT_DB               ); }
HB_FUNC_EXTERN( MYSQL_QUERY                   ); HB_FUNC( SQLQUERY        ) { HB_FUNC_EXEC( MYSQL_QUERY                   ); }
HB_FUNC_EXTERN( MYSQL_STORE_RESULT            ); HB_FUNC( SQLSTORER       ) { HB_FUNC_EXEC( MYSQL_STORE_RESULT            ); }
HB_FUNC_EXTERN( MYSQL_USE_RESULT              ); HB_FUNC( SQLUSERES       ) { HB_FUNC_EXEC( MYSQL_USE_RESULT              ); }
HB_FUNC_EXTERN( MYSQL_FREE_RESULT             ); HB_FUNC( SQLFREER        ) { HB_FUNC_EXEC( MYSQL_FREE_RESULT             ); }
HB_FUNC_EXTERN( MYSQL_FETCH_ROW               ); HB_FUNC( SQLFETCHR       ) { HB_FUNC_EXEC( MYSQL_FETCH_ROW               ); }
HB_FUNC_EXTERN( MYSQL_DATA_SEEK               ); HB_FUNC( SQLDATAS        ) { HB_FUNC_EXEC( MYSQL_DATA_SEEK               ); }
HB_FUNC_EXTERN( MYSQL_NUM_ROWS                ); HB_FUNC( SQLNROWS        ) { HB_FUNC_EXEC( MYSQL_NUM_ROWS                ); }
HB_FUNC_EXTERN( MYSQL_FETCH_FIELD             ); HB_FUNC( SQLFETCHF       ) { HB_FUNC_EXEC( MYSQL_FETCH_FIELD             ); }
HB_FUNC_EXTERN( MYSQL_FIELD_SEEK              ); HB_FUNC( SQLFSEEK        ) { HB_FUNC_EXEC( MYSQL_FIELD_SEEK              ); }
HB_FUNC_EXTERN( MYSQL_NUM_FIELDS              ); HB_FUNC( SQLNUMFI        ) { HB_FUNC_EXEC( MYSQL_NUM_FIELDS              ); }
HB_FUNC_EXTERN( MYSQL_FIELD_COUNT             ); HB_FUNC( SQLFICOU        ) { HB_FUNC_EXEC( MYSQL_FIELD_COUNT             ); }
HB_FUNC_EXTERN( MYSQL_LIST_FIELDS             ); HB_FUNC( SQLLISTF        ) { HB_FUNC_EXEC( MYSQL_LIST_FIELDS             ); }
HB_FUNC_EXTERN( MYSQL_ERROR                   ); HB_FUNC( SQLGETERR       ) { HB_FUNC_EXEC( MYSQL_ERROR                   ); }
HB_FUNC_EXTERN( MYSQL_LIST_DBS                ); HB_FUNC( SQLLISTDB       ) { HB_FUNC_EXEC( MYSQL_LIST_DBS                ); }
HB_FUNC_EXTERN( MYSQL_LIST_TABLES             ); HB_FUNC( SQLLISTTBL      ) { HB_FUNC_EXEC( MYSQL_LIST_TABLES             ); }
HB_FUNC_EXTERN( MYSQL_AFFECTED_ROWS           ); HB_FUNC( SQLAFFROWS      ) { HB_FUNC_EXEC( MYSQL_AFFECTED_ROWS           ); }
HB_FUNC_EXTERN( MYSQL_GET_HOST_INFO           ); HB_FUNC( SQLHOSTINFO     ) { HB_FUNC_EXEC( MYSQL_GET_HOST_INFO           ); }
HB_FUNC_EXTERN( MYSQL_GET_SERVER_INFO         ); HB_FUNC( SQLSRVINFO      ) { HB_FUNC_EXEC( MYSQL_GET_SERVER_INFO         ); }
HB_FUNC_EXTERN( MYSQL_ESCAPE_STRING           ); HB_FUNC( DATATOSQL       ) { HB_FUNC_EXEC( MYSQL_ESCAPE_STRING           ); }
HB_FUNC_EXTERN( MYSQL_ESCAPE_STRING_FROM_FILE ); HB_FUNC( FILETOSQLBINARY ) { HB_FUNC_EXEC( MYSQL_ESCAPE_STRING_FROM_FILE ); }

#pragma ENDDUMP

#endif
If it is the program you are talking about, I am also uploading the folder containing this file also.
MiniSql.zip
(5.48 KiB) Downloaded 333 times
I found it from Minigui\Samples\Advansed\Minisql folder. And I hope this can be easily compiled with HMG official with some minor changes.

With best regards.

Sudip
Last edited by sudip on Mon Sep 14, 2009 5:36 pm, edited 1 time in total.
With best regards,
Sudip

User avatar
Roberto Lopez
HMG Founder
Posts: 3966
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 23 times
Been thanked: 161 times

Post by Roberto Lopez » Mon Sep 14, 2009 5:34 pm

sudip wrote:Hello Master Roberto,

Is the following code you are talking about?
<...>
Yes it is.

You'll must find a '.ch' file, a sample and some little docs too.

Could be you so kind to upload all the files (if available) ?

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

User avatar
sudip
Posts: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Mon Sep 14, 2009 5:37 pm

Yes Master Roberto,

I am uploading it.
MiniSql.zip
(5.48 KiB) Downloaded 329 times
With best regards.

Sudip
With best regards,
Sudip

User avatar
Rathinagiri
Posts: 5263
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 169 times
Been thanked: 172 times
Contact:

Post by Rathinagiri » Mon Sep 14, 2009 6:11 pm

Oh! Nice. It is a treasure to dig in. :)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Roberto Lopez
HMG Founder
Posts: 3966
Joined: Wed Jul 30, 2008 6:43 pm
Has thanked: 23 times
Been thanked: 161 times

Post by Roberto Lopez » Mon Sep 14, 2009 6:16 pm

sudip wrote:Yes Master Roberto,

I am uploading it.
MiniSql.zip
With best regards.

Sudip
OOPS!!!

Sorry, I've not seen it :)

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)

Post Reply