How to work with SQLite

[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

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

[huangchenmin]

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

[huangchenmin]

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

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

[huangchenmin]

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

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

[huangchenmin]

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

[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 */
}

[huangchenmin]

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