SQLite administrator

[JoaoAfonso]

Hail!

Not sure if this post fits here... but I guess if not, then it lacks another forum below Creative Basic general forum

Lately I am working with CB and SQLite. It works well and fast, but it is difficult, when you have too much data and tables, to understand your tables correctly. I then googled for a SQLite free graphical client, and found out the following site, which seems the best graphical free SQLite client:

http://sqliteadmin.orbmu2k.de/

You will see your tables in SQLite much like Access to their databases.

Hope this is usefull

[Doc]

Thanks, JoÃÆ'Ã,£o!
...this should come in handy.

Lately I am working with CB and SQLite.

CB and SQLite, now you have my full attention...
What version of SQLite are you currently working with?

-Doc-

[JoaoAfonso]

I guess it sill exists SQLite 3, as most sqlite clients are based on sqlite3, but I am using the version before the 3... Well, the one posted in IB STD forum.

[tbohon]

Any chance you can post some sample code using CB and SQLite?  I'm also interested in using it ... but have no experience with SQLite at all so it's a bit of a challenge.

Thanks in advance.

Tom

[JoaoAfonso]

Ok, here is the main program I use to edit my databases in SQL. It is basically the address book sample that comes with CB with one or two functions more made by me, to my needs. It is messy, as I use it to shape my databases in every program I use SQL. I guess, though, that if you look at it calmly you can figure out how everything works.

Code Select Expand

'INICIO DE FUNÃÆ'ââ,¬Â¡ÃƒÆ'ââ,¬Â¢ES DO SQLITE
type SQLParams
def DB_Handle:int
def ArPo:int
def TabXM:int
def TabYM:int
endtype
declare "!sqlite.dll",sqlite_open(fn:string,mode:int,emess:pointer),int
declare "!sqlite.dll",sqlite_close(hnd:int)
declare "!sqlite.dll",sqlite_freemem(addr:int)
declare "!sqlite.dll",sqlite_get_table(hnd:int,sql:string,res:pointer,nrow:pointer,ncolumn:pointer,emess:pointer),int
declare "!sqlite.dll",sqlite_get_table_vprintf(hnd:int,sql:string,res:pointer,nrow:pointer,ncolumn:pointer,emess:pointer,va_list:int),int
declare "!sqlite.dll",sqlite_free_table(addr:int)
declare "kernel32",lstrcpyA(lpstring1:string,lpstring2:int),int
declare "kernel32",RtlMoveMemory(dest:pointer,source:int,length:int),int
declare SqlOpen(FileName:string,mode:int,ErrorText:string,DBHnd:SQLParams)
declare SqlClose(DBHnd:SQLParams)
declare SqlCmd(DBHnd:SQLParams,SQL_Statement:string,ErrorText:string)
declare SqlDisplayLV(DBHnd:SQLParams,wd:window,cid:int)
DECLARE SqlSacaCampo(FileName:string,SQL_str:string,a:int)
DECLARE SqlApoioSacaCampo(DBHnd:SQLParams,a:int)
declare SqlGetField(DBHnd:SQLParams,x:int,y:int,field:string)
declare SqlCheckForTable(db:SQLParams,tabname:string)
declare Read_DB_LV(FileName:string,wd:window,cid:int,SQL_str:string)
def dbname:string
def err:string
def mydb:SQLParams
'var debaixo ÃÆ'Ã,© para gravar todos os valores das colunas de um sqlsacacamp
def camposacado[20]:string
'FIM DE FUNÃÆ'ââ,¬Â¡ÃƒÆ'ââ,¬Â¢ES DO SQLITE


def path,pathdadosrede:string
pathdadosrede=getstartpath+"\dadosrede\"
def gen1:file
def temp,temp0,temp1,temp2,temp3,temp4,temp5,temp6:string
def dir,dir2,count,a,pos:int
def nim,dirfile,dirfile2,nome,apelido,posto,classe,datanasc,funcao,subun,biofile,fotofile:string

dbname = pathdadosrede+"aga.db"


def main:window
WINDOW main,100,100,530,370,@size|@minbox|@maxbox,0,"Adressbook - adresses.db", mainroutine
setwindowcolor main, rgb(192,192,192)
setfont main, "ms sans serif", 8, 400, 0
CONTROL main, "LV, , 10, 35, 500, 300, @VSCROLL|@LVSreport|@LVSSINGLESEL|@border, 1"
move main, 10,5 : print main, "Press '1' for info about the database"
move main, 10,20: print main, "Press '2' for the list of adresses"

run=1
Read_DB_LV(dbname,main,1,"SELECT * FROM cursos ORDER BY designacao ASC")
waituntil run=0
closewindow main
end


sub mainroutine
select @class
case @IDCLOSEWINDOW
  'closes the window
run=0
case @idkeydown
if @code=49:'key 1
'show the content of the database
Read_DB_LV(dbname,main,1,"SELECT type, name, rootpage, sql FROM sqlite_temp_master UNION ALL SELECT type, name, rootpage, sql FROM sqlite_master")
endif
if @code=50:'key 2
'show the content of the table "adresses"
' Read_DB_LV(dbname,main,1,"SELECT * FROM adrlist")

SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,"UPDATE actividades SET data"+chr$(61)+"'02092007' WHERE data"+chr$(61)+"'01092007'",err)
' SqlCmd(mydb,"UPDATE actividades SET data"+chr$(61)+"'02092007' WHERE nÃÆ'Ã,ºmero"+chr$(61)+"'2'",err)
SqlClose(mydb)
'SqlSacaCampo(dbname,"SELECT * FROM cursos",1)
'for a=0 to 19
' messagebox 0,camposacado[a],"Ni"
'next a

Read_DB_LV(dbname,main,1,"SELECT * FROM actividades ORDER BY nÃÆ'Ã,ºmero ASC")
endif
if @code=51:'key 3
'pessoalausencias - NIM, data_inicial, data_final, motivo
'materiaisausencias - nÃÆ'Ã,ºmero, data_inicial, data_final, motivo
Read_DB_LV(dbname,main,1,"SELECT * FROM pessoalausencias")

SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,"DROP TABLE pessoalausencias",err)
SqlCmd(mydb,"CREATE TABLE pessoalausencias (NIM TEXT, data_inicial TEXT, data_final TEXT, motivo TEXT, dijulian TEXT, dfjulian TEXT)",err)

count=CONTROLCMD(main,1,@LVGETCOUNT)
if count<>0
for a=0 to count-1
temp="'"+CONTROLCMD(main,1,@LVGETTEXT,a,0)+"','"+CONTROLCMD(main,1,@LVGETTEXT,a,1)+"','"+CONTROLCMD(main,1,@LVGETTEXT,a,2)+"','"
temp=temp+CONTROLCMD(main,1,@LVGETTEXT,a,3)+"','"+str$(julian(CONTROLCMD(main,1,@LVGETTEXT,a,1)))+"','"+str$(julian(CONTROLCMD(main,1,@LVGETTEXT,a,2)))+"'"
SqlCmd(mydb,"INSERT INTO pessoalausencias VALUES ("+temp+")",err)
next a
endif
SqlClose(mydb)





'materiaisausencias - nÃÆ'Ã,ºmero, data_inicial, data_final, motivo
Read_DB_LV(dbname,main,1,"SELECT * FROM materiaisausencias")

SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,"DROP TABLE materiaisausencias",err)
SqlCmd(mydb,"CREATE TABLE materiaisausencias (nÃÆ'Ã,ºmero TEXT, data_inicial TEXT, data_final TEXT, motivo TEXT, dijulian TEXT, dfjulian TEXT)",err)
SqlClose(mydb)

' SqlOpen(dbname,0,err,mydb)
' SqlCmd(mydb,"DROP TABLE actividades",err)
' SqlCmd(mydb,"CREATE TABLE actividades (NÃÆ'Ã,ºmero INTEGER PRIMARY KEY, Data TEXT, Hora TEXT, IE TEXT, DesignaÃÆ'Ã,§ÃÆ'Ã,£o TEXT, Tipo TEXT, Entidade TEXT, Efectivo TEXT, Material TEXT, Data_Pedido TEXT, Doc_Autorizador TEXT, Link_1 TEXT, Link_2 TEXT, Link_3 TEXT, JulianData TEXT)",err)
' SqlCmd(mydb,"INSERT INTO documentos VALUES ('NEP da AMM','NEP.pdf')",err)
' SqlCmd(mydb,"INSERT INTO documentos VALUES ('Mapa AGI e AEI','MAPAAGIAEI.pdf')",err)
' SqlCmd(mydb,"INSERT INTO documentos VALUES ('Mapa LEI','MAPALEI.pdf')",err)
' SqlCmd(mydb,"INSERT INTO documentos VALUES ('Mapa ÃÆ'Ã,¡reas CMEFD','MAPACMEFD.pdf')",err)
' SqlClose(mydb)
' Read_DB_LV(dbname,main,1,"SELECT * FROM materiaisausencias ORDER BY nÃÆ'Ã,ºmero ASC")
' Read_DB_LV(dbname,main,1,"SELECT * FROM pessoal WHERE classe"+chr$(61)+"'Sargentos' ORDER BY apelido ASC")
' Read_DB_LV(dbname,main,1,"SELECT * FROM Cursos ORDER BY NÃÆ'Ã,ºmero ASC")
endif
if @code=52:'key 4
' SqlOpen(dbname,0,err,mydb)
' SqlCmd(mydb,"DELETE FROM Actividades WHERE nÃÆ'Ã,ºmero"+chr$(61)+"'2'",err)
' SqlClose(mydb)
'nÃÆ'Ã,ºmero,data,hora,ie,designaÃÆ'Ã,§ÃÆ'Ã,£o,tipo,entidade,efectivo,material,data_pedido,doc_autorizador,link_1,link_2,link_3
' Read_DB_LV(dbname,main,1,"SELECT * FROM pessoalcursos WHERE nim"+chr$(61)+"'12229400'")
Read_DB_LV(dbname,main,1,"SELECT * FROM pessoalausencias")
' Read_DB_LV(dbname,main,1,"SELECT * FROM Actividades ORDER BY NÃÆ'Ã,ºmero ASC")
endif
if @code=53:'key 5
Read_DB_LV(dbname,main,1,"SELECT * FROM actividades")
endif
if @code=54:'key 6
SqlOpen(dbname,0,err,mydb)
dir = FINDOPEN(pathdadosrede+"convertido\*.*")
IF(dir)
    DO
        dirfile = FINDNEXT(dir)
if (dirfile<>".")&(dirfile<>"..")
dir2 = FINDOPEN(pathdadosrede+"convertido\"+dirfile+"\*.*")
IF(dir2)
    DO
        dirfile2 = FINDNEXT(dir2)
if (right$(dirfile2,4)=".sup")
if (openfile(gen1,pathdadosrede+"convertido\"+dirfile+"\"+dirfile2,"R")=0)
do
if(read(gen1,designacao) = 0)
read(gen1,tipo)
read(gen1,entidade)
read(gen1,efectivo)
read(gen1,material)
read(gen1,mateq)
read(gen1,datapedido)
read(gen1,docautorizador)
read(gen1,pessoal)
read(gen1,horaactividade)
read(gen1,ie)
read(gen1,fileapoio1)
read(gen1,fileapoio2)
read(gen1,fileapoio3)
'NÃÆ'Ã,ºmero INTEGER PRIMARY KEY
'Data TEXT
'Hora TEXT
'IE TEXT
'DesignaÃÆ'Ã,§ÃÆ'Ã,£o TEXT
'Tipo TEXT
'Entidade TEXT
'Efectivo TEXT
'Material TEXT
'Data_Pedido TEXT
'Doc_Autorizador TEXT
'Link_1 TEXT
'Link_2 TEXT
'Link_3 TEXT
'JulianData TEXT
temp="NULL,'"+dirfile+"','"+left$(dirfile2,2)+"','"+mid$(dirfile2,3,2)+"','"+designacao+"','"+tipo+"','"+entidade+"','"
temp=temp+efectivo+"','"+mateq+"','"+datapedido+"','"+docautorizador+"','"+fileapoio1+"','"+fileapoio2+"','"+fileapoio3
temp=temp+"','"+str$(julian(dirfile))+"'"
SqlCmd(mydb,"INSERT INTO actividades VALUES ("+temp+")",err)
endif
until eof(gen1)
closefile gen1
endif
endif
UNTIL dirfile2 = ""
FINDCLOSE dir2
ENDIF
endif
UNTIL dirfile = ""
FINDCLOSE dir
ENDIF

SqlClose(mydb)
Read_DB_LV(dbname,main,1,"SELECT * FROM actividades")
endif
if @code=55:'key 7
SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,"CREATE TABLE ActividadesPessoal (NÃÆ'Ã,ºmeroActividade TEXT, NIM TEXT)",err)
temp="1','12229400"
SqlCmd(mydb,"INSERT INTO ActividadesPessoal VALUES ('"+temp+"')",err)
temp="1','13842099"
SqlCmd(mydb,"INSERT INTO ActividadesPessoal VALUES ('"+temp+"')",err)
Read_DB_LV(dbname,main,1,"SELECT * FROM ActividadesPessoal ORDER BY NÃÆ'Ã,ºmeroActividade ASC")
endif

endselect
return


'SqlCmd(mydb,"CREATE TABLE material (nomenc FOREIGN)",err)
'NESTE FOREIGN, COLOCAR: numero TEXT PRIMARY KEY, nna TEXT, preco TEXT, carga TEXT, estado TEXT, observ TEXT, ausencias FOREIGN
'SqlCmd(mydb,"INSERT INTO material (nomenc) VALUES ('')",err)
'ACTIVIDADES!!!!!!!!!!!!!!!!



'_________________________________________________________________
sub Read_DB_LV(FileName:string,wd:window,cid:int,SQL_str:string)
if SqlOpen(filename,0,err,mydb)
SETCONTROLTEXT RS_win,2011,err
endif
SqlCmd(mydb,SQL_str,err)
SqlDisplayLV(mydb,wd,CID)
SqlClose(mydb)
Return

'_________________________________________________________________
sub SqlOpen(FileName:string,mode:int,ErrorText:string,DBHnd:SQLParams)
def et:int
DBHnd.DB_Handle=sqlite_open(FileName,mode,et)
if DBHnd.DB_Handle=0
lstrcpyA(ErrorText,et)
sqlite_freemem(et)
else
ErrorText=""
endif
return len(ErrorText)

'_________________________________________________________________
sub SqlClose(DBHnd:SQLParams)
if DBHnd.ArPo<>0
sqlite_free_table(DBHnd.ArPo)
DBHnd.Arpo=0
DBHnd.TabXM=0
DBHnd.TabYM=0
endif
if DBHnd.DB_Handle<>0
sqlite_close(DBHnd.DB_Handle)
DBHnd.DB_Handle=0
endif
return

'____________________________________________________________________
sub SqlCmd(DBHnd:SQLParams,SQL_Statement:string,ErrorText:string)
def et,ec:int
if DBHnd.ArPo<>0 then sqlite_free_table(DBHnd.ArPo)
ec=sqlite_get_table(DBHnd.DB_Handle,SQL_Statement,DBHnd.ArPo,DBHnd.TabYM,DBHnd.TabXM,et)
if et<>0
lstrcpyA(ErrorText,et)
sqlite_freemem(et)
else
ErrorText=""
endif
return ec

'______________________________________________________________________
sub SqlGetField(DBHnd:SQLParams,x:int,y:int,field:string)
def stra:int
RtlMoveMemory(stra,DBHnd.ArPo+((y*DBHnd.TabXM+x)*4),4)
lstrcpyA(field,stra)
return

'______________________________________________________________________
sub SqlDisplayLV(DBHnd:SQLParams,wd:window,cid:int)
def x,y:int
def dat[100]:istring
def straddr:int
def cur:int
controlcmd wd,cid,@lvdeleteall
while sendmessage (wd,4124,0,0,cid)
endwhile
if DBHnd.TabXM>0
sendmessage wd,4143,DBHnd.TabYM,0,cid : 'Turns updating display off...
sendmessage wd,11,0,0,cid
cur=DBHnd.ArPo
for x=0 to DBHnd.TabXM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
controlcmd wd,cid,@lvinsertcolumn,x,dat
next x
if DBHnd.TabYM>0
for y=0 to DBHnd.TabYM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
controlcmd wd,cid,@lvinsertitem,y,dat
for x=1 to DBHnd.TabXM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
controlcmd wd,cid,@LVSETTEXT, y,x,dat
next x
next y
else
endif
'LVSCW_AUTOSIZE,-1 (Autosized columns)
for x=0 to DBHnd.TabXM-1
controlcmd wd,cid,@LVSETCOLWIDTH,x,-1
next x
'Turns updating of display back on......
sendmessage wd,11,1,0,cid
else
controlcmd wd,cid,@lvinsertcolumn,0,"Use Execute SQL button to create a entry"
controlcmd wd,cid,@lvinsertitem,1,"Empty fields"
endif
SQL_tab_cols=DBHnd.TabXM
SQL_tab_rows=DBHnd.TabYM
return

'______________________________________________________________________
sub SqlCheckForTable(db:SQLParams,tabname:string)
def rows:int
def err,s:string
s="select * from sqlite_master '"+tabname+"'" :'creates the table if non existant
SqlCmd(db,s,err)
if db.TabYM<>0 then return 1
rows=db.TabYM
return rows


'_________________________________________________________________
sub SqlSacaCampo(FileName:string,SQL_str:string,a:int)
def resultado:string
SqlOpen(filename,0,err,mydb)
SqlCmd(mydb,SQL_str,err)
SqlApoioSacaCampo(mydb,a)
SqlClose(mydb)
Return

'______________________________________________________________________
sub SqlApoioSacaCampo(DBHnd:SQLParams,a:int)
def x,y:int
def dat[100]:istring
def straddr:int
def cur:int
def resultado:string
for b=0 to 19
camposacado[b]=""
next b
if DBHnd.TabXM>0
cur=DBHnd.ArPo
for x=0 to DBHnd.TabXM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
next x
if DBHnd.TabYM>0
for y=0 to DBHnd.TabYM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
if y=a then camposacado[0]=dat
for x=1 to DBHnd.TabXM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
if y=a then camposacado[x]=dat
next x
next y
endif
endif
return

Hope it is usefull. Feel free to ask anything (I know something about it, but for sure others know more. Anyway feel free).