Page 1 of 2
How to work with SQLite
Posted: Fri Jun 11, 2010 1:25 am
by huangchenmin
Dear all:
I would like to work CA-Clipper with SQLite. Following are couples of question which make me confuse.
1.Sould I use SQLite command or CA-Clipper command to comprehend table ?
2.How to open,create table in .prg ?
3.How to get record ? VariableName := Table->FileldName ?
Could anyone provide me web link or coding?
Best Regards
Chen Min
Re: How to work with SQLite
Posted: Fri Jun 11, 2010 3:39 am
by Rathinagiri
Hi Chen Min,
I think I can help you in this regard.
We have coded some user defined functions to connect, retrieve, and update SQLite databases.
Now, there are four functions.
1. Connect2DB(cDBname,lCreate) -> oDBO :To Connect to the database file. First parameter is the filename. Second parameter is logical whether to create a new database if not exists. This function returns the DB object which can be used for other queries.
2. SQL(oDBO,cQuery) -> aResult : To send 'select' query to the database. This function returns a two dimensional array of results.
3. MiscSQL(oDBO,cQuery) -> lOk : To send all other queries (create, drop, insert, update, delete etc) except 'select'. This function returns logical value whether the query is successfully executed or not.
4. C2SQL(xUnParsedValue) -> cParsedValue : This function is used to convert all the values to SQLite compatible. This function returns a character string compatible with SQLite values.
See this example:
dDate := date()
oDBO := connect2db("c:\samples\sample.sqlite",.f.)
aResult := sql(oDBO,"select * from table1 where date = "+c2sql(dDate))
I think it will be useful to you.
Re: How to work with SQLite
Posted: Fri Jun 11, 2010 5:15 pm
by huangchenmin
rathinagiri wrote:Hi Chen Min,
I think I can help you in this regard.
We have coded some user defined functions to connect, retrieve, and update SQLite databases.
.........
Now, there are four functions.
I think it will be useful to you.
Rathinagiri you the best!
Those program code do me a big big favor. Although I have not practice it by myself.
Meanwhile I long to know where/how could get those info rather than bothering you.
Next I could study on my own instead of silly question on this forum.
Best Regard.
Chen Min
Re: How to work with SQLite
Posted: Wed Feb 22, 2012 9:34 am
by huangchenmin
rathinagiri wrote:Hi Chen Min,
I think I can help you in this regard.
We have coded some user defined functions.......
I think it will be useful to you.
Dear rathinagiri:
You have been sharded your user define routin with me.
I study about SQLite more than before and find there are many SQLite C/C++ interface in you routines.
Should I include somewhat *.h file in prg file if I would like to somekind routin like yours ?
Best Regards
chen min
Re: How to work with SQLite
Posted: Wed Feb 22, 2012 9:52 am
by Rathinagiri
I think it is not needed.
However you can refer contrib/hbsqlite folder of Harbour sourcecode. I will clarify when I come back home... I am out of station as of now.
Re: How to work with SQLite
Posted: Thu Feb 23, 2012 3:15 pm
by huangchenmin
rathinagiri wrote:I think it is not needed.
However you can refer contrib/hbsqlite folder of Harbour sourcecode. I will clarify when I come back home... I am out of station as of now.
Dear rathinagiri:
Thanks for helping me about this issue. I try my first program to connect to SQLite database and it is done.
But I am still confusing about using SQLite database.
1.Should I call sqlite3_open_v2() if I would like to specify the mode(ex. READONLY/readwrite) ?
2.How to assign 3th argument of sqlite_open_v2() with combination of 『SQLITE_OPEN_FULLMUTEX』and 『SQLITE_OPEN_READWRITE』.
3.How to lock a record to prevent someone from updating it in netwrok environment ?
Best Regards
chen min
Re: How to work with SQLite
Posted: Thu Feb 23, 2012 5:05 pm
by Rathinagiri
Hi Chen Min,
Regarding SQLite Database, you always work with a record set. The ways and means of accessing the database using HBSQLite bridge is utterly different from the conventional DBF system. You need not worry about the lock in a network environment because, the lock is created by the system itself.
And, SQLite, as the name suggests, it doesn't suit for a huge network traffic database. However, it can handle a small network (like less than 20-30 connections writing. Any number of connections reading at the same time will not be a problem).
If you want to use SQLite just like DBF, try to use contrib/sddsqlt3 library.
Re: How to work with SQLite
Posted: Fri Feb 24, 2012 2:05 am
by huangchenmin
rathinagiri wrote:Hi Chen Min,
Regarding SQLite Database, you always work with a record set. .......
If you want to use SQLite just like DBF, try to use contrib/sddsqlt3 library.
Dear rathinagir:
Thanks for replying to me and it is helpful for me to undersatand more about SQLite API.
There are one thing I couldn't figure out is the parameter of SQLite API. Take sqlite3_open() for example, the handle of connection to databse is returned in 2nd parameter as SQLite web document description. In fact, the variable on thr right of :=sqlite3_open(par1,par2) accept the connection handle instead of par2. In your routine, you pass true/false in 2nd parameter.
I am really get confusing! Please help!
Best Regards
chen min
Re: How to work with SQLite
Posted: Fri Feb 24, 2012 3:37 am
by Rathinagiri
Dear Chen Min
I wish to draw your kind attention to harbour/contrib/hbsqlit3/core.c. This is the harbour-SQLite bridge. All harbour functions are converted into SQLite 'C' functions. In that file around line number 700 we have the following code for sqlite3_open function. What we send as logical (.t. or .f.) as the second parameter is used to determine whether we want to create the file or use it.
So, sqlite3_open in 'c' API is different from the Harbour sqlite3_open() function. We can call only those functions defined as HB_FUNC( ) from inside Harbour.
Code: Select all
/**
Opening( creating ) A New Database Connection
sqlite3_open( cDatabace, lCreateIfNotExist ) -> return pointer to Db
or NIL if error occurs
sqlite3_open_v2( cDatabace, nOpenMode ) -> return pHbSqlite3 or NIL
*/
HB_FUNC( SQLITE3_OPEN )
{
sqlite3 * db;
char * pszFree;
const char * pszdbName = hb_fsNameConv( hb_parcx( 1 ), &pszFree );
if( hb_fsFileExists( pszdbName ) || hb_parl( 2 ) )
{
if( sqlite3_open( pszdbName, &db ) == SQLITE_OK )
{
HB_SQLITE3 * hbsqlite3;
hbsqlite3 = ( HB_SQLITE3 * ) hb_xgrab( sizeof( HB_SQLITE3 ) );
hb_xmemset( hbsqlite3, 0, sizeof( HB_SQLITE3 ) );
hbsqlite3->db = db;
hb_sqlite3_ret( hbsqlite3, HB_SQLITE3_DB );
}
else
{
sqlite3_close( db );
hb_retptr( NULL );
}
}
else
{
HB_TRACE( HB_TR_DEBUG, ( "sqlite3_open(): Database doesn't exist %s", pszdbName ) );
hb_retptr( NULL );
}
if( pszFree )
hb_xfree( pszFree );
}
HB_FUNC( SQLITE3_OPEN_V2 )
{
#if SQLITE_VERSION_NUMBER >= 3005000
sqlite3 * db;
char * pszFree;
const char * pszdbName = hb_fsNameConv( hb_parcx( 1 ), &pszFree );
if( sqlite3_open_v2( pszdbName, &db, hb_parni( 2 ), NULL ) == SQLITE_OK )
{
HB_SQLITE3 * hbsqlite3;
hbsqlite3 = ( HB_SQLITE3 * ) hb_xgrab( sizeof( HB_SQLITE3 ) );
hb_xmemset( hbsqlite3, 0, sizeof( HB_SQLITE3 ) );
hbsqlite3->db = db;
hb_sqlite3_ret( hbsqlite3, HB_SQLITE3_DB );
}
else
{
sqlite3_close( db );
hb_retptr( NULL );
}
if( pszFree )
hb_xfree( pszFree );
#else
hb_retptr( NULL );
#endif /* SQLITE_VERSION_NUMBER >= 3005000 */
}
Re: How to work with SQLite
Posted: Sat Feb 25, 2012 1:18 pm
by huangchenmin
rathinagiri wrote:Dear Chen Min
I wish to draw your kind attention to harbour/contrib/hbsqlit3/core.c. ........
So, sqlite3_open in 'c' API is different from the Harbour sqlite3_open() function. We can call only those functions defined as HB_FUNC( ) from inside Harbour.
[/code]
Dear rathinagiri:
This really knock on my head. I am totaly understand the mechanism, which harbour works with SQLite.
Dose harbour works alone with SQL express server in the same way ? with different routines, right?
Best Regards
chen min