I had an old program that used an sqlite3 database. To achieve this, I used an OBDC connector that had to be installed on each computer in order to use the program. This connector, "sqliteodbc.exe," comes from the website:
http://www.ch-werner.de/sqliteodbc/
Some clients were reluctant to install third-party software on their computers. Therefore, in an attempt to access sqlite3 databases natively, I have created two classes, which I am attaching along with a small example of usage. However, I haven't figured out a good way to display the records in xbrowse. I convert them to an Array, and it works fine, but it's not the cleanest solution. If anyone has any ideas on how to improve this, I would appreciate it.
You need to link "hbsqlit3.hbc," which comes with Harbour, and have "hbsqlit3.ch" accessible, which also comes with Harbour.
The sqlite functions can be found here:
https://www.sqlite.org/c3ref/funclist.html
test.prg
- Code: Select all Expand view RUN
- #include "fivewin.ch"
#include "hbsqlit3.ch"
static oWnd
function test()
local oBar
local aArray
local oCn
local cCreate
cCreate := 'CREATE TABLE t1( '+;
'id INTEGER PRIMARY KEY AUTOINCREMENT, '+ ;
'name TEXT, '+ ;
'age INTEGER '+ ;
') ;'
cCreate += "CREATE UNIQUE INDEX id on t1 (id); "
cCreate += "BEGIN TRANSACTION;" + ;
"INSERT INTO t1( name, age ) VALUES( 'Bob', 52 );" + ;
"INSERT INTO t1( name, age ) VALUES( 'Fred', 40 );" + ;
"INSERT INTO t1( name, age ) VALUES( 'Sasha', 25 );" + ;
"INSERT INTO t1( name, age ) VALUES( 'Ivet', 28 );" + ;
"COMMIT;"
oCn := aq_Sqlite():new( "test.db" , cCreate )
if oCn == nil
return nil
endif
// oCn:executeStatement( 'ALTER TABLE t1 ADD address TEXT' )
define Window oWnd MDI title "TEST"
DEFINE BUTTONBAR oBar 3D OF oWnd size 40,60 2007
define button of obar name "" Prompt "Test1"+CRLF action ( test1() , oWnd:Tile(.f.) )
oWnd:bKeyDown := { | nKey | iif( nKey==VK_ESCAPE , oWnd:end() , nil ) }
activate window oWnd on init test1() ;
return nil
// ------------------------------------------------------------------------------------ //
static function test1()
local oWnd2
local oBrw
local oBar
local cSeek
local aData
local nRowSel
define Window oWnd2 MDICHILD of oWnd title "Test1"
DEFINE BUTTONBAR oBar 3D OF oWnd2 size 40,60 2007
define button of obar name "" Prompt "Add"+CRLF action edit(oBrw , .t. )
define button of obar name "" Prompt "Edit"+CRLF action edit(oBrw )
define button of obar name "" Prompt "Del"+CRLF action del_record(oBrw )
oWnd2:bKeyDown := { | nKey | iif( nKey==VK_ESCAPE , oWnd2:end() , nil ) }
oWnd2:bGotFocus := { | | nRowSel := oBrw:nArrayAt ,;
cSeek:= oBrw:cSeek ,oBrw:seek(''), oBrw:SetArray( requery(oBrw) ) , oBrw:seek(cSeek) ,;
oBrw:Bookmark(nRowSel) , oBrw:refresh() }
activate window oWnd2 on init oBrw:=create_browse(oWnd2,aData) valid ( .t. )
return nil
// ---------------------------------------------------------------------------------------//
static function requery(oBrw)
local aData
aData := oCn():getarray( "SELECT id,name FROM t1 " )
if empty(aData)
aData := array(len(oBrw:aCols))
aFill(aData,nil)
aData := {aData}
endif
return aData
// ---------------------------------------------------------------------------------------//
static function create_browse(oWnd2,aData)
local oCol
local bdclick
local oBrw
oBrw := TXBrowse():New( oWnd2 )
bdclick:={ || edit(oBrw) }
oBrw:nMarqueeStyle := 5
oBrw:nColDividerStyle := LINESTYLE_BLACK
oBrw:nRowDividerStyle := LINESTYLE_BLACK
oBrw:lColDividerComplete := .t.
oBrw:nHeaderLines := 1
oBrw:nDataLines := 1
oBrw:lfooter := .t.
oBrw:nfooterLines := 2
oBrw:L2007:=.t.
oBrw:lAllowRowSizing:=.f.
oCol := oBrw:AddCol()
oCol:bStrData := { || oBrw:aArrayData[ oBrw:nArrayAt , 1 ] }
oCol:cHeader := "Recn"
ocol:nDataStrAlign:=2
oCol:nHeadStrAlign:=2
oCol:nWidth:=60
oCol:blDClickData:= bdclick
oCol := oBrw:AddCol()
oCol:bStrData := { || oBrw:aArrayData[ oBrw:nArrayAt , 2 ] }
oCol:cHeader := "name"
ocol:nDataStrAlign:=0
oCol:nHeadStrAlign:=2
oCol:nWidth:=250
oCol:blDClickData:= bdclick
if empty(aData)
aData := array(len(oBrw:aCols))
AFill(aData,nil)
aData := {aData}
endif
oBrw:SetArray(aData)
oBrw:lseekbar := .t.
oBrw:lSeekWild := .t.
oBrw:lIncrFilter := .t.
oBrw:cFilterFld := 'name'
oBrw:lautosort := .t.
oBrw:bClrEdits := { || { CLR_BLACK, CLR_YELLOW } }
oBrw:oFilterCol:=oBrw:aCols[2]
oBrw:CreateFromCode()
oWnd2:oClient := oBrw
oBrw:setfocus()
oBrw:SetArray( requery(oBrw) )
oBrw:refresh()
return oBrw
// ---------------------------------------------------------------------------------------//
static function del_record(oBrw)
local cSql
local cSeek
local nRow
if !msgyesno('Delete record?'+CRLF+CRLF+oBrw:aArrayData[ oBrw:nArrayAt , 2 ])
return nil
endif
nRow := oBrw:nArrayAt
cSql := "DELETE FROM t1 WHERE id='"+hb_ValToStr( oBrw:aArrayData[oBrw:nArrayAt,1] )+"' "
oCn():executeStatement( cSql )
cSeek:= oBrw:cSeek
oBrw:seek('')
oBrw:SetArray( requery(oBrw) )
oBrw:seek(cSeek)
oBrw:KeyNo(nRow-1)
return nil
// ---------------------------------------------------------------------------------------//
static function edit(oBrw , lAppend )
local nBookMark
local cSeek
local oEdit
local oDlg
local oFont,oFont2
local lSave := .f.
default lAppend := .f.
oEdit := aq_SqliteDataRow():new( 't1','id',oBrw:aArrayData[ oBrw:nArrayAt , 1 ])
if lAppend
oEdit:blank()
else
oEdit:load()
endif
nBookMark := oBrw:nArrayAt
oEdit:name := PadR( oEdit:name , 30)
DEFINE FONT oFont NAME "Arial" SIZE 0, 18 BOLD
DEFINE FONT oFont2 NAME "Arial" SIZE 0, 16 BOLD
define dialog oDlg size 600,400 pixel Title "Test: "+iif(lAppend,'Adding','Recn: '+str(nBookMark) )
@ 20 , 10 say "Name:" size 200 , 20 pixel of oDlg font oFont
@ 20 , 40 get oEdit:name size 150 , 15 pixel of oDlg font oFont2
@ 50 , 10 say "Age:" size 200 , 20 pixel of oDlg font oFont
@ 50 , 40 get oEdit:age picture '999' size 150 , 15 pixel of oDlg font oFont2
@ 180 , 200 button "Cancel" size 40 , 10 pixel of oDlg font oFont action oDlg:end()
@ 180 , 250 button "Save" size 40 , 10 pixel of oDlg font oFont action (lSave:=.t.,oDlg:end())
activate dialog odlg centered ;
valid( iif( oEdit:modified() , iif(lsave, .t., msgyesno("Exit without saving?") ) , .t. ) )
if lSave
oEdit:name := alltrim(oEdit:name)
if lAppend
nBookMark := oEdit:insert()
oBrw:seek('')
oBrw:SetArray( requery(oBrw) )
oBrw:Bookmark(nBookMark)
else
if oEdit:modified()
oEdit:save()
cSeek:= oBrw:cSeek
oBrw:seek('')
oBrw:SetArray( requery(oBrw) )
oBrw:seek(cSeek)
oBrw:Bookmark := nBookMark
endif
endif
oBrw:refresh()
endif
oEdit:end()
return nil
aq_Sqlite.prg
- Code: Select all Expand view RUN
- #include "fivewin.ch"
#include "hbsqlit3.ch"
class aq_Sqlite
DATA db
DATA dbfile
METHOD New( dbfile , cCreate )
METHOD getArray(cSql)
METHOD getFields(cTable)
METHOD getTypes(cTable)
METHOD executeStatement(cSql)
METHOD End()
ENDCLASS
// ----------------------------------------------------------------------------- //
METHOD New( dbfile , cCreate ) CLASS aq_Sqlite
default dbfile:= 'test.db'
default cCreate := 'CREATE TABLE t1( '+;
'id INTEGER PRIMARY KEY AUTOINCREMENT, '+ ;
'name TEXT '+ ;
')'
::dbfile := dbfile
if !file( dbfile )
if !msgyesno("File does not exist, create?"+CRLF+CRLF+dbfile)
return nil
endif
::db := sqlite3_open( dbfile , .t. )
sqlite3_exec( ::db, "PRAGMA auto_vacuum=0" )
sqlite3_exec( ::db, "PRAGMA page_size=4096" )
sqlite3_exec( ::db, cCreate )
else
::db := sqlite3_open( dbfile )
endif
oCn(Self)
RETURN Self
// ----------------------------------------------------------------------------- //
METHOD executeStatement( cSql ) CLASS aq_Sqlite
local stmt
local result
result := sqlite3_exec( ::db, cSql )
if result != SQLITE_OK
msginfo("Error: "+hb_sqlite3_errstr_short(result)+CRLF+CRLF+cSql)
endif
return nil
// ----------------------------------------------------------------------------- //
METHOD getArray( cSql ) CLASS aq_Sqlite
local aArray:= {}
local aArray1
local stmt
local nFor
local nCol
local aFields := {}
local cField
cursorwait()
stmt := sqlite3_prepare( ::db, cSql )
nCol := sqlite3_column_count(stmt)
for nFor :=1 to nCol
cField := sqlite3_column_decltype( stmt, nFor )
cField := standard_cField( cField )
aadd(aFields,cField)
next nFor
DO WHILE sqlite3_step( stmt ) == SQLITE_ROW
aArray1:= {}
for nFor :=1 to nCol
do case
case aFields[nFor] == 'INTEGER'
aadd(aArray1, sqlite3_column_int(stmt, nFor) )
case aFields[nFor] == 'REAL'
aadd(aArray1, sqlite3_column_double(stmt, nFor) )
case aFields[nFor] == 'TEXT'
aadd(aArray1, sqlite3_column_text(stmt, nFor) )
case aFields[nFor] == 'BLOB'
aadd(aArray1, sqlite3_column_blob(stmt, nFor) )
otherwise
aadd(aArray1, 'error' )
endcase
next nFor
aadd( aArray , aArray1 )
enddo
sqlite3_clear_bindings( stmt )
sqlite3_finalize( stmt )
cursorarrow()
return aArray
// ----------------------------------------------------------------------------- //
METHOD getFields( cTable ) CLASS aq_Sqlite
local stmt
local nFor
local nCol
local cField
local aFields := {}
stmt := sqlite3_prepare( ::db, 'SELECT * FROM '+cTable+' LIMIT 1' )
nCol := sqlite3_column_count(stmt)
for nFor :=1 to nCol
cField := upper( sqlite3_column_name( stmt, nFor ) )
aadd(aFields,cField)
next nFor
sqlite3_clear_bindings( stmt )
sqlite3_finalize( stmt )
return aFields
// ----------------------------------------------------------------------------- //
METHOD getTypes( cTable ) CLASS aq_Sqlite
local stmt
local nFor
local nCol
local cField
local aFields := {}
stmt := sqlite3_prepare( ::db, 'SELECT * FROM '+cTable+' LIMIT 1' )
nCol := sqlite3_column_count(stmt)
for nFor :=1 to nCol
cField := upper( sqlite3_column_decltype( stmt, nFor ) )
aadd(aFields,{ cField , standard_cField( cField) } )
next nFor
sqlite3_clear_bindings( stmt )
sqlite3_finalize( stmt )
return aFields
// ----------------------------------------------------------------------------- //
METHOD End( ) CLASS aq_Sqlite
return nil
// ----------------------------------------------------------------------------- //
static function standard_cField( cField )
cField := alltrim(upper( cField ))
if 'CHAR'$cField .or. "TEXT"$cField
cField := "TEXT"
elseif substr( cField , 1, 3 ) == 'INT'
cField := "INTEGER"
elseif cField == 'FLOAT' .or. cField == 'REAL'
cField := "REAL"
elseif substr( cField , 1, 3 ) == 'NUM'
cField := "REAL"
elseif 'BLOB'$cField
cField := "BLOB"
else
msginfo('Error standard_cField: '+cField)
endif
return cField
// ----------------------------------------------------------------------------- //
function oCn(oCn)
static oConn
if oConn == nil
oConn:= oCn
endif
return oConn
aq_SqliteDataRow.prg
- Code: Select all Expand view RUN
- #include "fivewin.ch"
#include "hbsqlit3.ch"
class aq_SqliteDataRow
DATA cTable
DATA cIndex
DATA row
DATA aFields
DATA aTypes
DATA aValues
DATA aValues_ini
METHOD New(cTable, cIndex , row )
METHOD blank()
METHOD load()
METHOD save()
METHOD insert()
METHOD modified()
METHOD End()
ERROR HANDLER OnError( uParam1 )
ENDCLASS
// ----------------------------------------------------------------------------- //
METHOD New( cTable, cIndex , row ) CLASS aq_SqliteDataRow
default row := 0
::cTable := cTable
::cIndex := cIndex
::row := row
::aFields := oCn():getFields(cTable)
::aTypes := oCn():getTypes(cTable)
RETURN Self
// ----------------------------------------------------------------------------- //
METHOD blank( ) CLASS aq_SqliteDataRow
local nFor
local xValue
local cad
::aValues := { 0 }
for nFor := 2 to len( ::aFields )
xValue := nil
if ::aTypes[nFor,2]=='INTEGER' .or. ::aTypes[nFor,2]=='REAL'
xValue := 0
elseif ::aTypes[nFor,2]=='BLOB'
xValue := ''
elseif 'CHAR'$::aTypes[nFor,1] .and. '('$::aTypes[nFor,1]
cad := SubStr( ::aTypes[nFor,1] , at('(', ::aTypes[nFor,1] )+1 )
cad := SubStr( cad , 1 , at(')', cad) - 1 )
xValue := Replicate(' ',val( cad ) )
elseif 'TEXT'==::aTypes[nFor,2]
xValue:= space( 30 )
else
msginfo('Error blank: '+::aTypes[nFor,1] )
xValue:= space( 30 )
endif
aadd( ::aValues , xValue )
next nFor
::aValues_ini := AClone( ::aValues )
return nil
// ----------------------------------------------------------------------------- //
METHOD load( ) CLASS aq_SqliteDataRow
::aValues := oCn():getArray( "SELECT * FROM "+::cTable+" WHERE "+::cIndex+"='"+ ;
alltrim(hb_ValToStr(::row))+"' LIMIT 1")[1]
::aValues_ini := AClone( ::aValues )
return nil
// ----------------------------------------------------------------------------- //
METHOD save( ) CLASS aq_SqliteDataRow
local cSql
local cad := ''
local nFor
if ::modified()
for Nfor :=1 to len(::aValues)
if !( ::aValues[nFor] == ::aValues_ini[nfor] )
cad += ::aFields[nFor]+"='"+alltrim(hb_ValToStr(::aValues[nFor]))+"',"
endif
next nFor
cad:= subs(cad,1,len(cad)-1)
cSql := 'UPDATE '+::cTable+' SET '+cad+' WHERE '+::cIndex+"='"+ ;
alltrim(hb_ValToStr(::row))+"'"
oCn():executeStatement( cSql )
endif
return nil
// ----------------------------------------------------------------------------- //
METHOD insert( ) CLASS aq_SqliteDataRow
local cSql
local fields := ''
local values := ''
local nFor
local nlast := 0
for Nfor :=2 to len(::aValues)
fields += ::aFields[nFor]+','
values += "'"+alltrim(hb_ValToStr(::aValues[nFor]))+"',"
next nFor
fields:= subs(fields,1,len(fields)-1)
values:= subs(values,1,len(values)-1)
cSql := 'INSERT INTO '+::cTable+' ('+fields+') VALUES ('+values+') '
oCn():executeStatement( cSql )
nlast := sqlite3_last_insert_rowid( oCn():db )
return nlast
// ----------------------------------------------------------------------------- //
METHOD modified( ) CLASS aq_SqliteDataRow
local nFor
for Nfor :=1 to len(::aValues)
if valtype( ::aValues[nFor]) =='C'
::aValues[nFor] := alltrim( ::aValues[nFor] )
endif
if !( ::aValues[nFor] == ::aValues_ini[nfor] )
return .t.
endif
next nFor
return .f.
// ----------------------------------------------------------------------------- //
METHOD End( ) CLASS aq_SqliteDataRow
::aFields := nil
::aValues := nil
::aValues_ini := nil
return nil
// ----------------------------------------------------------------------------- //
METHOD OnError( uParam1 ) CLASS aq_SqliteDataRow
local cMsg := __GetMessage()
local nError := If( SubStr( cMsg, 1, 1 ) == "_", 1005, 1004 )
local nField
cMsg = Upper( cMsg )
if SubStr( cMsg, 1, 1 ) == "_"
if( ( nField := AScan( ::aFields,;
{ | cField | SubStr( cMsg, 2 ) == ;
RTrim( SubStr( cField, 1, 25 ) ) } ) ) != 0 ) // era 9 en lugar de 25
::aValues[nField] := uParam1
else
_ClsSetError( _GenError( nError, ::ClassName(), SubStr( cMsg, 2 ) ) )
endif
else
if( ( nField := AScan( ::aFields,;
{ | cField | cMsg == ;
RTrim( SubStr( cField, 1, 25 ) ) } ) ) != 0 ) // era 9 en lugar de 25
return ::aValues[nField]
else
_ClsSetError( _GenError( nError, ::ClassName(), cMsg ) )
endif
endif
return nil
Best regards.
Alvaro
