A little Database Application

byo · Post by **byo** » Sat Apr 07, 2007 7:43 pm

Hi.

I'm working on a little DB application which I built for my personal use.
I may develop something more complex in the future but this was my way of learning to use SQLite with Purebasic.

It's pretty much basic right now (no spun intended) but it has some functionality. Unfortunately, it's in Portuguese as of now. It has no printing, viewing, sorting features.

Some features:

- Create a new database
- Create a backup (not encrypted)
- Restore a backup
- Inserting/editing
- Can search clients from the Search Window

Thanks.

<EDIT> The download link is: http://rapidshare.com/files/24821902/by ... 5.zip.html
Thanks to the fellow poster below.

SFSxOI · Post by **SFSxOI** » Sat Apr 07, 2007 8:00 pm

byo wrote:Hi.

I'm working on a little DB application which I built for my personal use.
I may develop something more complex in the future but this was my way of learning to use SQLite with Purebasic.

It's pretty much basic right now (no spun intended) but it has some functionality. Unfortunately, it's in Portuguese as of now. It has no printing, viewing, sorting features.

Some features:

- Create a new database
- Create a backup (not encrypted)
- Restore a backup
- Inserting/editing
- Can search clients from the Search Window

I want to find a place to post it but I don't know where. Do you know of any file hosting website that I could use?

Thanks.

just put it in a .zip and put it up on rapid share.

byo · Post by **byo** » Sat Apr 07, 2007 8:05 pm

Thanks for the tip, SFSxOI.

Here it is:
http://rapidshare.com/files/24821902/by ... 5.zip.html

Please, keep in mind that I'm still learning.

SFSxOI · Post by **SFSxOI** » Sun Apr 08, 2007 3:53 pm

Looks kind of neat. Are you going to do an english version? and maybe the source code also? (pretty please

)

byo · Post by **byo** » Sun Apr 08, 2007 7:15 pm

Oh yes, of course.

Thanks for your comments.

Code: Select all

IncludeFile "wPrincipal.pb"

Declare ApagarDB()
Declare CriarDB()
Declare wProd_OK()
Declare wProd_Cancelar()
Declare wProd_Incluir()
Declare wProd_Editar()
Declare wProd_Excluir()
Declare wProd_Mode(estado.c)
Declare wProd_LimparCampos()
Declare wProd_Init()
Declare wProd_Pular(index.s)
Declare wProd_Mostrar()
Declare.l wProd_Pesquisar()
Declare wProd_Next()
Declare wProd_Previous()
Declare wProd_First()
Declare wProd_Last()
Declare wPesq_Init()
Declare wPesq_AjustarTamanho()
Declare wPesq_Filtrar()
Declare wBk_CriarBackup()
Declare wRest_Restaurar()
Declare cliCod_Enter()
Declare sqlDebug()
Declare sqlMsg()
Declare Finalizar()

Structure s_RecordSet
  BOF.l
  EOF.l
  Handle.l
  Rows.l
  Cols.l
  CurrentPos.l
  sValue.s
EndStructure

Global sqlDB.l, dbnome.s, mode.b, RS.s_RecordSet, bkDestino.s, ArquivoDB.s, cod.l, restArq.s
Global tempNome.s, ultimoCod.s

dbnome = "dados.db"
ArquivoDB = GetPathPart(ProgramFilename()) + "dados\"
bkDestino = ArquivoDB

If SQLite3_InitLib("sqlite3.dll")
	sqlDB = SQLite3_OpenDatabase(ArquivoDB + dbnome)
EndIf

If ExamineDirectory(0, ArquivoDB, "*.*") = 0
	CreateDirectory(ArquivoDB)
EndIf

Open_wPrincipal()
SetWindowState(#wPrincipal, #PB_Window_Maximize)
StatusBarText(#stBar, 1, FormatDate("%dd/%mm/%yyyy  %hh:%ii", Date()), #PB_StatusBar_Center)

Repeat
	evento = WaitWindowEvent()
			
	Select evento
		Case #WM_KEYDOWN			
			key.l = EventwParam()
			If key = #VK_RETURN
				If (GetActiveGadget() = #stCod) And (Trim(GetGadgetText(#stCod)) <> "")
					cliCod_Enter()
				EndIf
				If EventWindow() = #wPesq
					wPesq_Filtrar()
				EndIf 
			EndIf
		Case #WM_SIZE
			If EventWindow() = #wPesq
				wPesq_AjustarTamanho()
			EndIf
		Case #PB_Event_CloseWindow
			Select EventWindow()
				Case #wPrincipal
					Sair = #True
				Case #wPesq
					DisableWindow(#wProd, #False)
					CloseWindow(#wPesq)
				Default
				CloseWindow(EventWindow())
				DisableWindow(#wPrincipal, #False)
				SetActiveWindow(#wPrincipal)
			EndSelect
		Case #PB_Event_Menu
			objeto = EventMenu()
			Select objeto
				Case #mnSair
					Sair = #True
				Case #mnProd
					If sqlDB > 0
						DisableWindow(#wPrincipal, #True)
						Open_wProd()
						wProd_Mode(0)
						wProd_Init()
					Else
						MessageRequester(SQLite3_GetLastMessage(), "Não existe um banco de dados ativo no sistema neste momento." + Chr(13) + "Crie ou restaure um banco de dados primeiro.")
					EndIf					
				Case #mnBackup	
					DisableWindow(#wPrincipal, #True)				
					Open_wBackup()
					If sqlDB = 0
						DisableGadget(#btBackup_wBk, #True)
					Else
						DisableGadget(#btBackup_wBk, #False)
					EndIf
					tempNome = "dados_" + FormatDate("%dd%mm%yyyy", Date())
					SetGadgetText(#stDestino_wBk, bkDestino)
					SetGadgetText(#stArq_wBk, tempNome)
				Case #mnCriarDB
					If MessageRequester("Aviso!", "Esse procedimento criará um novo banco de dados! Tem certeza que deseja fazer isso?", #PB_MessageRequester_YesNo) = #PB_MessageRequester_Yes
						CriarDB()
					EndIf
				Case #mnApagarDB
					If MessageRequester("Aviso!", "Esse procedimento apagará o banco de dados atual! Tem certeza que deseja fazer isso?", #PB_MessageRequester_YesNo) = #PB_MessageRequester_Yes
						ApagarDB()
					EndIf
				Case #mnRestDB
					DisableWindow(#wPrincipal, #True)
					Open_wRest()
					If Trim(GetGadgetText(#stRest)) = ""
						DisableGadget(#btRest_wRest, #True)
					EndIf
			EndSelect
		Case #PB_Event_Gadget
			objeto = EventGadget()
			Select EventType() ;ao focalizar campos string, selecionar o texto todo do campo
				Case #PB_EventType_Focus
					If (GadgetType(objeto) = #PB_GadgetType_String) Or (GadgetType(objeto) = #PB_GadgetType_Editor)
						SendMessage_(GadgetID(objeto), #EM_SETSEL, 0, -1)
						If (EventWindow() = #wProd) And (objeto <> #stCod)
							Select mode
								Case 0
									SendMessage_(GadgetID(objeto), #EM_SETREADONLY, #True, 0)
									SetGadgetAttribute(#edObs, #PB_Editor_ReadOnly, #True)
								Default
									SendMessage_(GadgetID(objeto), #EM_SETREADONLY, #False, 0)
									SetGadgetAttribute(#edObs, #PB_Editor_ReadOnly, #False)
							EndSelect
						EndIf
					EndIf
			EndSelect				
			Select objeto						
				Case #btPesq_wProd
					ultimoCod = GetGadgetText(#stCod)
					DisableWindow(#wProd, #True)
					Open_wPesq()
					SetWindowState(#wPesq, #PB_Window_Maximize)
					wPesq_Init()
				Case #btIncl_wProd
					wProd_Incluir()
				Case #btEd_wProd
					wProd_Editar()
				Case #btExc_wProd
					wProd_Excluir()
				Case #btOK_wProd
					wProd_OK()
				Case #btCancel_wProd
					wProd_Cancelar()
				Case #btSair_wProd
					DisableWindow(#wPrincipal, #False)
					CloseWindow(#wProd)
				Case #btNext_wProd
					wProd_Next()
				Case #btPrev_wProd
					wProd_Previous()
				Case #btFirst_wProd
					wProd_First()
				Case #btLast_wProd
					wProd_Last()
				Case #btBackup_wBk
					wBk_CriarBackup()
				Case #btSair_wBk
					DisableWindow(#wPrincipal, #False)
					CloseWindow(#wBackup)					
				Case #btSair_wRest
					DisableWindow(#wPrincipal, #False)
					CloseWindow(#wRest)
				Case #btPesq_wRest
					restArq = OpenFileRequester("Escolha o backup para restaurar", ArquivoDB, "Backup zipado | *.zip", 0)
					If restArq <> ""
						SetGadgetText(#stRest, restArq)
						DisableGadget(#btRest_wRest, #False)
					EndIf
				Case #btRest_wRest
					wRest_Restaurar()				
				Case #btFiltrar_wPesq
					wPesq_Filtrar()
				Case #lstCli_wPesq
					If EventType() = #PB_EventType_LeftDoubleClick
						wProd_Init()
						wProd_Pular(GetGadgetItemText(#lstCli_wPesq, GetGadgetState(#lstCli_wPesq), 0))
						wProd_Mostrar()
						DisableWindow(#wProd, #False)
						CloseWindow(#wPesq)					
					EndIf
				Case #btSel_wPesq
					wProd_Init()
					If GetGadgetState(#lstCli_wPesq) > -1
						wProd_Pular(GetGadgetItemText(#lstCli_wPesq, GetGadgetState(#lstCli_wPesq), 0))
					Else
						wProd_Pular(ultimoCod)
					EndIf
					wProd_Mostrar()
					DisableWindow(#wProd, #False)
					CloseWindow(#wPesq)				
				Case #btSair_wPesq
					wProd_Init()
					wProd_Pular(ultimoCod)
					wProd_Mostrar()
					DisableWindow(#wProd, #False)
					CloseWindow(#wPesq)
			EndSelect			
			sqlDebug()
			StatusBarText(#stBar, 1, FormatDate("%dd/%mm/%yyyy  %hh:%ii", Date()), #PB_StatusBar_Center) 
	EndSelect
Until Sair
Finalizar()

Procedure ApagarDB()
	If sqlDB > 0
		SQLite3_CloseDatabase(sqlDB)
		If DeleteFile(ArquivoDB + dbnome) > 0
			MessageRequester("", "Banco de dados excluído com sucesso!")
			sqlDB = 0
			SQLite3_ReleaseRecordset(@RS)
		EndIf
	Else
		MessageRequester("ERRO", "Não há banco de dados a ser apagado!")
	EndIf
EndProcedure
Procedure CriarDB()
	If FileSize(ArquivoDB + dbnome) = -1
		sqlDB = SQLite3_CreateDatabase(ArquivoDB + dbnome, #True)
		SQLite3_Execute("CREATE TABLE CLIENTES (cliCod integer primary key, cliNome varchar, cliEnd varchar, cliComp varchar, cliCidade varchar, cliCEP varchar, cliEstado varchar, cliPais varchar, cliFone1 varchar, cliFone2 varchar, cliFax varchar, cliWeb varchar, cliEmail1 varchar, cliEmail2 varchar, cliObs varchar)", sqlDB)
		MessageRequester(SQLite3_GetLastMessage(), "Um novo banco de dados foi criado com sucesso!")
	Else
		MessageRequester("Erro", "Já existe um banco de dados criado! Apague-o antes de criar um novo.")
	EndIf
EndProcedure
Procedure wProd_OK()
	If (Trim(GetGadgetText(#stNome)) = "")
		MessageRequester("Nome inválido", "O NOME é um campo obrigatório!")
		SetActiveGadget(#stNome)
	Else
		If (Trim(GetGadgetText(#stEnd)) = "")
			MessageRequester("Endereço inválido", "ENDEREÇO é um campo obrigatório!")
			SetActiveGadget(#stEnd)
		Else
			If (Trim(GetGadgetText(#stFone1)) = "")
				MessageRequester("Fone inválido", "FONE1 é um campo obrigatório!")
				SetActiveGadget(#stFone1)
			Else
				Select mode
					Case 1
						If Not SQLite3_Execute("INSERT INTO CLIENTES(cliCod, cliNome, cliEnd, cliComp, cliCidade, cliCEP, cliEstado, cliPais, cliFone1, cliFone2, cliFax, cliWeb, cliEmail1, cliEmail2, cliObs) VALUES('" + GetGadgetText(#stCod) + "','" + GetGadgetText(#stNome) + "','" + GetGadgetText(#stEnd) + "','" + GetGadgetText(#stComp) + "','" + GetGadgetText(#stCidade) + "','" + GetGadgetText(#stCEP) + "','" + GetGadgetText(#stEstado) + "','" + GetGadgetText(#stPais) + "','" + GetGadgetText(#stFone1) + "','" + GetGadgetText(#stFone2) + "','" + GetGadgetText(#stFax) + "','" + GetGadgetText(#stWeb) + "','" + GetGadgetText(#stEmail1) + "','" + GetGadgetText(#stEmail2) + "','" + GetGadgetText(#edObs) + "')", sqlDB)
							MessageRequester(SQLite3_GetLastMessage(), "Não foi possível incluir um novo registro!")
						EndIf
					Case 2
						SQLite3_GetRecordsetValueByName("cliCod", @RS)
						If Not SQLite3_Execute("UPDATE CLIENTES SET cliNome = '" + GetGadgetText(#stNome) + "', cliEnd = '" + GetGadgetText(#stEnd) + "', cliComp = '" + GetGadgetText(#stComp) + "', cliCidade = '" + GetGadgetText(#stCidade) + "', cliCEP = '" + GetGadgetText(#stCEP) + "', cliEstado = '" + GetGadgetText(#stEstado) + "', clipais = '" + GetGadgetText(#stPais) + "', cliFone1 = '" + GetGadgetText(#stFone1) + "', cliFone2 = '" + GetGadgetText(#stFone2) + "', cliFax = '" + GetGadgetText(#stFax) + "', cliWeb = '" + GetGadgetText(#stWeb) + "', cliEmail1 = '" + GetGadgetText(#stEmail1) + "', cliEmail2 = '" + GetGadgetText(#stEmail2) + "', cliObs = '" + GetGadgetText(#edObs) + "' WHERE cliCod = '" + RS\sVAlue + "'", sqlDB) 
							MessageRequester(SQLite3_GetLastMessage(), "Houve um erro na atualização dos campos!")
						EndIf
				EndSelect
				wProd_Pular(GetGadgetText(#stCod))
				wProd_Mostrar()
				wProd_Mode(#False)
				cod = 0
				mode = 0			
			EndIf
		EndIf
	EndIf				
EndProcedure
Procedure wProd_Cancelar()
	wProd_Pular(ultimoCod)
	wProd_Mostrar()
	wProd_Mode(#False)	
	mode = 0
	cod = 0
EndProcedure
Procedure wProd_Incluir()
	ultimoCod = GetGadgetText(#stCod)
	SQLite3_GetRecordset("SELECT * FROM CLIENTES", sqlDB, @RS)
	If cod = 0		
		SQLite3_RecordsetMoveLast(@RS)
		SQLite3_GetRecordsetValueByName("cliCod", @RS)
		cod = Val(RS\sValue)+1
	EndIf
	wProd_LimparCampos()
	SetGadgetText(#stCod, Str(cod))
	wProd_Mode(#True)
	SetActiveGadget(#stNome)	
	mode = 1
EndProcedure
Procedure wProd_Editar()
	ultimoCod = GetGadgetText(#stCod)
	wProd_Mode(#True)
	DisableGadget(#stCod, #True)
	SetActiveGadget(#stNome)
	mode = 2
EndProcedure
Procedure wProd_Excluir()
	SQLite3_GetRecordsetValueByName("cliCod", @RS)
	If MessageRequester("Confirmação de exclusão", "Deseja excluir o registro?", #PB_MessageRequester_YesNo) = #PB_MessageRequester_Yes
		If RS\Handle > 0
			SQLite3_Execute("DELETE FROM clientes WHERE cliCod = '" + RS\sValue + "'", sqlDB)
			wProd_LimparCampos()
			wProd_Init()
			DisableGadget(#btPrev_wProd, #False) : DisableGadget(#btNext_wProd, #False)
		Else
			MessageRequester("Erro", "O código atual é inválido!")
		EndIf
	EndIf
EndProcedure
Procedure wProd_Mode(x.c)
	DisableGadget(#btIncl_wProd, x)
	DisableGadget(#btEd_wProd, x)
	DisableGadget(#btExc_wProd, x)
	DisableGadget(#btSair_wProd, x)
	DisableGadget(#btFirst_wProd, x)
	DisableGadget(#btPrev_wProd, x)
	DisableGadget(#btNext_wProd, x)
	DisableGadget(#btLast_wProd, x)
	DisableGadget(#btCancel_wProd, 1-x)
	DisableGadget(#btOK_wProd, 1-x)
	DisableGadget(#btPesq_wProd, x)
	DisableGadget(#stCod, x)
EndProcedure
Procedure wProd_Init()
	SQLite3_GetRecordset("select * from clientes", sqlDB, @RS)
	If SQLite3_GetRecordsetValueByName("cliCod", @RS) : SetGadgetText(#stCod, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliNome", @RS) : SetGadgetText(#stNome, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEnd", @RS) : SetGadgetText(#stEnd, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliComp", @RS) : SetGadgetText(#stComp, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliCidade", @RS) : SetGadgetText(#stCidade, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliCEP", @RS) : SetGadgetText(#stCEP, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEstado", @RS) : SetGadgetText(#stEstado, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliPais", @RS) : SetGadgetText(#stPais, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliFone1", @RS) : SetGadgetText(#stFone1, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliFone2", @RS) : SetGadgetText(#stFone2, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliFax", @RS) : SetGadgetText(#stFax, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliWeb", @RS) : SetGadgetText(#stWeb, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEmail1", @RS) : SetGadgetText(#stEmail1, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEmail2", @RS) : SetGadgetText(#stEmail2, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliObs", @RS) : SetGadgetText(#edObs, RS\sValue) : EndIf
	
	SetGadgetColor(#stNome, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#stEnd, #PB_Gadget_BackColor, RGB(250,250,255))
	SetGadgetColor(#stCEP, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#stComp, #PB_Gadget_BackColor, RGB(250,250,255))
	SetGadgetColor(#stCidade, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#stFone1, #PB_Gadget_BackColor, RGB(250,250,255))
	SetGadgetColor(#stFone2, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#stFax, #PB_Gadget_BackColor, RGB(250,250,255))
	SetGadgetColor(#stWeb, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#stEmail1, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#stEmail2, #PB_Gadget_BackColor, RGB(250,250,255))
	SetGadgetColor(#stCod, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#edObs, #PB_Gadget_BackColor, RGB(242,242,255))
	SetGadgetColor(#stEstado, #PB_Gadget_BackColor, RGB(250,250,255)) : SetGadgetColor(#stPais, #PB_Gadget_BackColor, RGB(250,250,255))
	
	SetGadgetColor(#stNome, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#stEnd, #PB_Gadget_FrontColor, RGB(0,0,150))
	SetGadgetColor(#stCEP, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#stComp, #PB_Gadget_FrontColor, RGB(0,0,150))
	SetGadgetColor(#stCidade, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#stFone1, #PB_Gadget_FrontColor, RGB(0,0,150))
	SetGadgetColor(#stFone2, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#stFax, #PB_Gadget_FrontColor, RGB(0,0,150))
	SetGadgetColor(#stWeb, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#stEmail1, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#stEmail2, #PB_Gadget_FrontColor, RGB(0,0,150))
	SetGadgetColor(#stCod, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#edObs, #PB_Gadget_FrontColor, RGB(0,0,150))
	SetGadgetColor(#stEstado, #PB_Gadget_FrontColor, RGB(0,0,150)) : SetGadgetColor(#stPais, #PB_Gadget_FrontColor, RGB(0,0,150))
	
	If GetGadgetText(#stCod) = ""
		DisableGadget(#btEd_wProd, #True)
		DisableGadget(#btExc_wProd, #True)
	EndIf
	DisableGadget(#btPrev_wProd, #False)
	DisableGadget(#btNext_wProd, #False)
	mode = 0
EndProcedure
Procedure wProd_LimparCampos()
	SetGadgetText(#stCod, "")
	SetGadgetText(#stNome, "")
	SetGadgetText(#stEnd, "")
	SetGadgetText(#stComp, "")
	SetGadgetText(#stCidade, "")
	SetGadgetText(#stCEP, "")
	SetGadgetText(#stEstado, "")
	SetGadgetText(#stFone1, "")
	SetGadgetText(#stFone2, "")
	SetGadgetText(#stFax, "")
	SetGadgetText(#stWeb, "")
	SetGadgetText(#stEmail1, "")
	SetGadgetText(#stEmail2, "")
	SetGadgetText(#edObs, "")
	SetActiveGadget(#stCod)	
EndProcedure
Procedure wProd_Pular(index.s)
	SQLite3_GetRecordset("SELECT * FROM CLIENTES", sqlDB, @RS)
	While RS\EOF = 0
		SQLite3_GetRecordsetValueByName("cliCod", @RS)
		If RS\sValue = index
			Break
		EndIf
		SQLite3_RecordsetMoveNext(@RS)		
	Wend
	cod = 0	
EndProcedure
Procedure wProd_Mostrar()
	If SQLite3_GetRecordsetValueByName("cliCod", @RS) : SetGadgetText(#stCod, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliNome", @RS) : SetGadgetText(#stNome, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEnd", @RS) : SetGadgetText(#stEnd, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliComp", @RS) : SetGadgetText(#stComp, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliCidade", @RS) : SetGadgetText(#stCidade, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliCEP", @RS) : SetGadgetText(#stCEP, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEstado", @RS) : SetGadgetText(#stEstado, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliPais", @RS) : SetGadgetText(#stPais, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliFone1", @RS) : SetGadgetText(#stFone1, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliFone2", @RS) : SetGadgetText(#stFone2, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliFax", @RS) : SetGadgetText(#stFax, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliWeb", @RS) : SetGadgetText(#stWeb, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEmail1", @RS) : SetGadgetText(#stEmail1, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliEmail2", @RS) : SetGadgetText(#stEmail2, RS\sValue) : EndIf
	If SQLite3_GetRecordsetValueByName("cliObs", @RS) : SetGadgetText(#edObs, RS\sValue) : EndIf
EndProcedure
Procedure wProd_Next()
	SQLite3_RecordsetMoveNext(@RS)
	wProd_Mostrar()
	If RS\EOF = 1
		DisableGadget(#btNext_wProd, #True)
	EndIf
	DisableGadget(#btPrev_wProd, #False)
EndProcedure
Procedure wProd_Previous()
	SQLite3_RecordsetMovePrevious(@RS)
	wProd_Mostrar()
	If RS\BOF = 1
		DisableGadget(#btPrev_wProd, #True)
	EndIf
	DisableGadget(#btNext_wProd, #False)
EndProcedure
Procedure wProd_First()
	SQLite3_GetRecordset("select * from clientes", sqlDB, @RS)
	SQLite3_RecordsetMoveFirst(@RS)
	DisableGadget(#btPrev_wProd, #True)
	DisableGadget(#btNext_wProd, #False)	
	wProd_Mostrar()
EndProcedure
Procedure wProd_Last()
	SQLite3_GetRecordset("select * from clientes", sqlDB, @RS)
	SQLite3_RecordsetMoveLast(@RS)
	DisableGadget(#btNext_wProd, #True)
	DisableGadget(#btPrev_wProd, #False)
	wProd_Mostrar()
EndProcedure
Procedure wPesq_Init()
	AddGadgetItem(#cmbOrd_wPesq, 0, "Nome")
	AddGadgetItem(#cmbOrd_wPesq, 1, "Endereço")
	AddGadgetItem(#cmbOrd_wPesq, 2, "Cidade")
	AddGadgetItem(#cmbOrd_wPesq, 3, "Estado")
	AddGadgetItem(#cmbOrd_wPesq, 4, "País")
	SetGadgetState(#cmbOrd_wPesq, 0)
	
	SQLite3_GetRecordset("select * from clientes", sqlDB, @RS)
	count = 0
	Repeat
		AddGadgetItem(#lstCli_wPesq, count, "")
		SQLite3_GetRecordsetValueByName("cliCod", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 0)
		SQLite3_GetRecordsetValueByName("cliNome", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 1)
		SQLite3_GetRecordsetValueByName("cliEnd", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 2)
		SQLite3_GetRecordsetValueByName("cliCidade", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 3)
		SQLite3_GetRecordsetValueByName("cliEstado", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 4)
		SQLite3_GetRecordsetValueByName("cliPais", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 5)
		SQLite3_RecordsetMoveNext(@RS)
		count = count + 1
	Until RS\EOF = 1
EndProcedure
Procedure wPesq_AjustarTamanho()
	ww = WindowWidth(#wPesq) : wh = WindowHeight(#wPesq)
	wx = WindowX(#wPesq) : wy = WindowY(#wPesq)

	ResizeGadget(#lstCli_wPesq, 10, 40, ww-103, wh-51)
	ResizeGadget(#stPesq_wPesq, 260, 10, ww-412, 20)
	ResizeGadget(#btFiltrar_wPesq, GadgetX(#stPesq_wPesq)+GadgetWidth(#stPesq_wPesq)+4, 9, 56, 22)
	ResizeGadget(#painel_wPesq, 4, -2, ww-8, wh-2)
	ResizeGadget(#btSel_wPesq, ww-85, 39, 73, 50)
	ResizeGadget(#btTodos_wPesq, ww-85, 100, 73, 50)
	ResizeGadget(#btSair_wPesq, ww-85, wh-60, 73, 50)
EndProcedure
Procedure wPesq_Filtrar()
	stQuery.s = GetGadgetText(#stPesq_wPesq)
	stPesq.s = GetGadgetText(#stPesq_wPesq)
	Select GetGadgetState(#cmbOrd_wPesq)
		Case 0
			SQLite3_GetRecordset("select * from clientes where cliNome like '%" + stPesq + "%'", sqlDB, @RS)
		Case 1
			SQLite3_GetRecordset("select * from clientes where cliEnd like '%" + stPesq + "%'", sqlDB, @RS)
		Case 2
			SQLite3_GetRecordset("select * from clientes where cliCidade like '%" + stPesq + "%'", sqlDB, @RS)
		Case 3
			SQLite3_GetRecordset("select * from clientes where cliEstado like '%" + stPesq + "%'", sqlDB, @RS)
		Case 4
			SQLite3_GetRecordset("select * from clientes where cliPais like '%" + stPesq + "%'", sqlDB, @RS)
	EndSelect
	count = 0
	ClearGadgetItemList(#lstCli_wPesq)
	Repeat
		AddGadgetItem(#lstCli_wPesq, count, "")
		SQLite3_GetRecordsetValueByName("cliCod", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 0)
		SQLite3_GetRecordsetValueByName("cliNome", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 1)
		SQLite3_GetRecordsetValueByName("cliEnd", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 2)
		SQLite3_GetRecordsetValueByName("cliCidade", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 3)
		SQLite3_GetRecordsetValueByName("cliEstado", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 4)
		SQLite3_GetRecordsetValueByName("cliPais", @RS)
		SetGadgetItemText(#lstCli_wPesq, count, RS\sValue, 5)
		SQLite3_RecordsetMoveNext(@RS)
		count = count + 1
	Until RS\EOF = 1
EndProcedure
Procedure wBk_CriarBackup()
	tempArq.s = "dados.bk"
	CopyFile(ArquivoDB + dbnome, tempArq)
	bkDestino = GetGadgetText(#stDestino_wBk)
	
	If Trim(GetGadgetText(#stArq_wBk)) <> ""
		tempNome = Trim(GetGadgetText(#stArq_wBk))
	EndIf
	
	f = PureZIP_Archive_Create(bkDestino + "/" + tempNome + ".zip", #APPEND_STATUS_CREATE)
	If f > 0
		PureZIP_Archive_Compress(tempArq, #False)
		PureZIP_Archive_Close()
		DeleteFile(tempArq)
		MessageRequester("Aviso", "Backup criado com sucesso!")
	Else
		MessageRequester("Erro", "Houve um problema com a PASTA DE DESTINO! Confira se essa pasta existe antes de fazer um backup.")
	EndIf
EndProcedure
Procedure wRest_Restaurar()
	If FileSize(restArq) > -1
		If sqlDB > 0
			SQLite3_CloseDatabase(sqlDB)
		EndIf
		DeleteFile(ArquivoDB + dbnome)
		If FileSize(ArquivoDB + "dados.bk") > -1 : DeleteFile(ArquivoDB + "dados.bk") : EndIf
		If PureZIP_ExtractFile(restArq, 0, ArquivoDB, #False)
			RenameFile(ArquivoDB + "dados.bk", ArquivoDB + dbnome)
			DeleteFile(ArquivoDB + "dados.bk")
			MessageRequester("", "Arquivo restaurado com sucesso!")
		EndIf
		sqlDB = SQLite3_OpenDatabase(ArquivoDB + dbnome)
		SQLite3_GetRecordset("SELECT * FROM CLIENTES", sqlDB, @RS)
	EndIf
EndProcedure
Procedure cliCod_Enter()
	SQLite3_GetRecordset("SELECT * FROM CLIENTES WHERE cliCod = '" + GetGadgetText(#stCod) + "'", sqlDB, @RS)
	SQLite3_GetRecordsetValueByName("cliCod", @RS)
	If RS\sValue <> ""
		cod = Val(RS\sValue)
		wProd_Pular(Str(cod))
		wProd_Mostrar()	
	Else
		If MessageRequester("Aviso", "O código não existe. Deseja cadastrar um novo cliente com o código?", #PB_MessageRequester_YesNo) = #PB_MessageRequester_Yes
			cod = Val(GetGadgetText(#stCod))
			wProd_Incluir()
		Else
			wProd_Mode(#False)
			wProd_Init()
		EndIf
	EndIf
	SendMessage_(GadgetID(#stCod), #EM_SETSEL, 0, -1)
EndProcedure
Procedure sqlDebug()
	StatusBarText(#stBar, 0, ": " + SQLite3_GetLastMessage())
EndProcedure
Procedure sqlMsg()
	MessageRequester("SQLite", "SQL : " + SQLite3_GetLastMessage())
EndProcedure
Procedure Finalizar()
	SQLite3_ReleaseRecordset(@RS)
	SQLite3_CloseDatabase(sqlDB)
	SQLite3_End()	
EndProcedure

The code is a little mess right now and has no comments but I'll change that when I feel it's worthy.

brunes · Post by **brunes** » Tue Apr 10, 2007 4:23 pm

byo

Belo artigo, muito bom mesmo.

Voce poderia postar o arquivo com a declaração das chamadas da DLL.

Grato

Brunes.

byo · Post by **byo** » Tue Apr 17, 2007 5:07 pm

Brunes: Desculpe a demora para responder. Esqueci desse meu post.

O arquivo de declarações está na lib PBOSL.
Procure em http://www.purearea.net essa Userlib.

Pylpa · Post by **Pylpa** » Wed Dec 05, 2007 5:44 pm

@ Byo
I'm interested in database and i've found your little database very nice.
For studying the behavior, coud you share the code of the wprincipal.pb

Thank 's a lot for your answer

yrreti · Post by **yrreti** » Wed Dec 05, 2007 11:29 pm

@ Byo
I'm interested in learning from your database also, but it won't
run without the code of the wprincipal.pb . I also would appreciate your sharing that code too if it's possible.
Thanks

byo · Post by **byo** » Thu Dec 06, 2007 2:52 am

Hi, guys.

Unfortunately, the code in wPrincipal.pb has changed a lot since I last posted here and it's not compatible anymore with the code in my first post.

I have a more complex example than that (starting with that one above) and I'll post it as soon as I find my backup DVDs (I had to format my computer since then).

Sorry for the inconvenience.
ASAP, I'll post the example. Thanks for the interest.

PS.: Also take a look at pdwyer's examples. He's very experienced in SQLite.

Pylpa · Post by **Pylpa** » Thu Dec 06, 2007 8:14 am

Hello byo,

Thank you for your answer

, I'll wait for a new (and surely better) example and try to look at pdwyer's examples according to your advice.

Have a good day

yrreti · Post by **yrreti** » Thu Dec 06, 2007 1:31 pm

Likewise, thank you for your answer, and thanks for sharing your code. Smile

PureBasic Forums - English

A little Database Application

A little Database Application

Re: A little Database Application