PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

[source code] SQLite Database programmmig

https://www.purebasic.fr/english/viewtopic.php?t=55142

Page 1 of 1

[source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 11:32 am
by amif
Hello everybody...
just want to share source code about SQLite, i hope this usefull :D

Code: Select all

; Basic SQLite Database programming
; Add, Edit, Save, Delete Data
; 
; By : Amif
Enumeration
  #lblHeader
  #frmMain
  #cmdShowSecondForm
  #cmdQuit
  #cmdNew
  #cmdEdit
  #cmdDelete
  #cmdSave
  #cmdCancel
  #grdData
  #lblNo
  #txtNo
  #lblName
  #txtName
  #lblAddress
  #txtAddress
  #lblPhone
  #txtPhone
EndEnumeration

Declare ListDatabase()
Declare GetDataToTxt()
Declare Saving()
Declare ClearTxt()
Declare EditMode(stat.b)
Declare CheckDatabaseUpdate(Database, Query$)
Declare Editing()
Declare Delete()

UseSQLiteDatabase()

Global DatabaseFile.s = GetCurrentDirectory()+"/data.sqlite"

EditORAdd.s="Add"
Quit.b=#False

FontHeader = LoadFont(#PB_Any, "Arial",18, #PB_Font_Bold)

If OpenWindow(#frmMain, 0, 0, 550, 440, "Simple SQLite Database Programming", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  If UseGadgetList(WindowID(#frmMain))
    TextGadget(#lblHeader, 5, 10,540, 40, "SQLite Database Programming", #PB_Text_Center)
    SetGadgetFont(#lblHeader,FontID(FontHeader))
    ButtonGadget(#cmdNew,5, 400, 50, 25, "New") 
    ButtonGadget(#cmdEdit,55, 400, 50, 25, "Edit") 
    ButtonGadget(#cmdDelete,105, 400, 50, 25, "Delete") 
    ButtonGadget(#cmdSave,155, 400, 50, 25, "Save") 
    ButtonGadget(#cmdCancel,205, 400, 50, 25, "Cancel") 
    ButtonGadget(#cmdQuit,445, 400, 100, 25, "Quit") 
    ListIconGadget(#grdData, 5, 70, 540, 150, "No", 30, #PB_ListIcon_FullRowSelect | #PB_ListIcon_AlwaysShowSelection)
      AddGadgetColumn(#grdData, 1, "Name", 200)
      AddGadgetColumn(#grdData, 2, "Address", 200)
      AddGadgetColumn(#grdData, 3, "Phone", 105)
    TextGadget(#lblNo, 15, 230, 100, 20, "No")           :StringGadget(#txtNo, 95, 230, 50, 20, "")
    TextGadget(#lblName, 15, 250, 100, 20, "Name")       :StringGadget(#txtName, 95, 250, 200, 20, "")
    TextGadget(#lblAddress, 15, 270, 100, 20, "Address") :StringGadget(#txtAddress, 95, 270, 300, 20, "")
    TextGadget(#lblPhone, 15, 290, 100, 20, "Phone")     :StringGadget(#txtPhone, 95, 290, 100, 20, "",#PB_String_Numeric)
    SetGadgetAttribute(#txtNo,#PB_String_MaximumLength,3)
    SetGadgetAttribute(#txtName,#PB_String_MaximumLength,35)
    SetGadgetAttribute(#txtAddress,#PB_String_MaximumLength,50)
    SetGadgetAttribute(#txtPhone,#PB_String_MaximumLength,16)
        
        ;Main loop ------------------------------------------------------------------------
        Repeat
          
            MainWindowEvent.l    = WaitWindowEvent()
            MainEventGadget.l    = EventGadget()
            MainEvenType.l       = EventType()
            
            Select MainWindowEvent
              Case #PB_Event_Gadget
                Select MainEventGadget
                  Case #txtNo
                    If MainEvenType=#PB_EventType_Focus
                      SetGadgetColor(#txtNo,#PB_Gadget_BackColor,#Green)
                    ElseIf MainEvenType=#PB_EventType_LostFocus
                      SetGadgetColor(#txtNo,#PB_Gadget_BackColor,#White)
                    EndIf 
                  Case #txtName 
                    If MainEvenType=#PB_EventType_Focus
                      SetGadgetColor(#txtName,#PB_Gadget_BackColor,#Green)
                    ElseIf MainEvenType=#PB_EventType_LostFocus
                      SetGadgetColor(#txtName,#PB_Gadget_BackColor,#White)
                    EndIf 
                  Case #txtAddress
                    If MainEvenType=#PB_EventType_Focus
                      SetGadgetColor(#txtAddress,#PB_Gadget_BackColor,#Green)
                    ElseIf MainEvenType=#PB_EventType_LostFocus
                      SetGadgetColor(#txtAddress,#PB_Gadget_BackColor,#White)
                    EndIf 
                  Case #txtPhone
                    If MainEvenType=#PB_EventType_Focus
                      SetGadgetColor(#txtPhone,#PB_Gadget_BackColor,#Green)
                    ElseIf MainEvenType=#PB_EventType_LostFocus
                      SetGadgetColor(#txtPhone,#PB_Gadget_BackColor,#White)
                    EndIf 
                  Case #cmdNew
                    EditORAdd="Add"
                    EditMode(#True)
                    ClearTxt()
                    SetActiveGadget(#txtNo)
                  Case #cmdEdit
                    EditORAdd="Edit"
                    EditMode(#True)
                    DisableGadget(#txtNo,1)
                  Case #cmdDelete
                    If MessageBox_(#Null,"Are you sure delete this data ? ","Confirmation",#MB_YESNO|#MB_ICONQUESTION)=#IDYES
                      Delete()
                      ListDatabase()
                      SetGadgetItemState(#grdData,0,1)
                      GetDataToTxt()  
                      EditMode(#False) 
                    EndIf
                  Case #cmdSave
                    If EditORAdd="Add"
                      Saving()
                    ElseIf EditORAdd="Edit"
                      Editing()
                    EndIf    
                    ListDatabase()
                    SetGadgetItemState(#grdData,0,1)
                    GetDataToTxt()  
                    EditMode(#False)
                  Case #cmdCancel
                    SetGadgetItemState(#grdData,0,1)
                    GetDataToTxt()  
                    EditMode(#False)
                  Case #grdData
                    Select EventType()
                      Case #PB_EventType_Change
                        If GetGadgetState(#grdData)>-1
                          GetDataToTxt()
                        EndIf
                    EndSelect
                  Case #cmdQuit
                    Quit=#True
                EndSelect
              Case #PB_Event_ActivateWindow
                If ListDatabase()
                  SetGadgetItemState(#grdData,0,1)
                  GetDataToTxt()
                  EditMode(#False)
                EndIf
            EndSelect
        
        Until MainWindowEvent = #PB_Event_CloseWindow Or Quit=#True
        ;Main loop ------------------------------------------------------------------------ 
        CloseDatabase(0)
  EndIf
EndIf
End


Procedure EditMode(stat.b)
 Protected TxtStat.b
  If stat=#True
    DisableGadget(#cmdNew,1)
    DisableGadget(#cmdEdit,1)
    DisableGadget(#cmdDelete,1)
    DisableGadget(#cmdSave,0)
    DisableGadget(#cmdCancel,0)
    TxtStat=0
    DisableGadget(#grdData,1)
  Else
    DisableGadget(#cmdNew,0)
    DisableGadget(#cmdEdit,0)
    DisableGadget(#cmdDelete,0)
    DisableGadget(#cmdSave,1)
    DisableGadget(#cmdCancel,1)
    TxtStat=1    
    DisableGadget(#grdData,0)  
  EndIf
  DisableGadget(#txtNo,TxtStat)
  DisableGadget(#txtName,TxtStat)
  DisableGadget(#txtAddress,TxtStat)
  DisableGadget(#txtPhone,TxtStat)
EndProcedure


Procedure ClearTxt()
  SetGadgetText(#txtNo,"")
  SetGadgetText(#txtName,"")
  SetGadgetText(#txtAddress,"")
  SetGadgetText(#txtPhone,"")  
EndProcedure

Procedure GetDataToTxt()
  SetGadgetText(#txtNo,GetGadgetItemText(#grdData,GetGadgetState(#grdData),0))
  SetGadgetText(#txtName,GetGadgetItemText(#grdData,GetGadgetState(#grdData),1))
  SetGadgetText(#txtAddress,GetGadgetItemText(#grdData,GetGadgetState(#grdData),2))
  SetGadgetText(#txtPhone,GetGadgetItemText(#grdData,GetGadgetState(#grdData),3))  
EndProcedure

Procedure Delete()
  Protected mSQL.s
  mSQL="DELETE FROM person "
  mSQL=mSQL+"WHERE no='"+GetGadgetText(#txtNo)+"';"
  CheckDatabaseUpdate(0, mSQL)
EndProcedure

Procedure Editing()
  Protected mSQL.s
  mSQL="UPDATE person "
  mSQL=mSQL+"SET name='"+ReplaceString(GetGadgetText(#txtName), "'", "`")+"',"
  mSQL=mSQL+"address='"+ReplaceString(GetGadgetText(#txtAddress), "'", "`")+"',"
  mSQL=mSQL+"phone='"+ReplaceString(GetGadgetText(#txtPhone), "'", "`")+"' WHERE no='"+GetGadgetText(#txtNo)+"';"
  CheckDatabaseUpdate(0, mSQL)
EndProcedure

Procedure Saving()
  Protected mSQL.s
  mSQL="INSERT INTO person (no,name,address,phone) VALUES ( "
  mSQL=mSQL+"'"+ReplaceString(GetGadgetText(#txtNo), "'", "`")+"',"
  mSQL=mSQL+"'"+ReplaceString(GetGadgetText(#txtName), "'", "`")+"',"
  mSQL=mSQL+"'"+ReplaceString(GetGadgetText(#txtAddress), "'", "`")+"',"
  mSQL=mSQL+"'"+ReplaceString(GetGadgetText(#txtPhone), "'", "`")+"')"
  CheckDatabaseUpdate(0, mSQL)
EndProcedure

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure
 
Procedure ListDatabase()
  Protected msg.s
  
  ClearGadgetItems(#grdData)
  If OpenDatabase(0, DatabaseFile, "", "") ; if database is found
    
   If DatabaseQuery(0, "SELECT * FROM person ORDER BY no") 
      
     
     While NextDatabaseRow(0)
       msg= GetDatabaseString(0, 0) + Chr(10) + GetDatabaseString(0, 1) + Chr(10) + GetDatabaseString(0, 2) + Chr(10) + GetDatabaseString(0, 3)
       AddGadgetItem(#grdData, -1, msg)
     Wend
     FinishDatabaseQuery(0)
     
   EndIf
   ProcedureReturn #True
   
 Else ; if database is not found, we create it
   MessageBox_(#Null,"No database found, so we will create it for you :) !!! "+ #CRLF$ +"With several data, of course","Information",#MB_OK|#MB_ICONEXCLAMATION)
   
      If CreateFile(0, DatabaseFile)
         CloseFile(0)
         
         If OpenDatabase(0, DatabaseFile, "", "")
         
            CheckDatabaseUpdate(0, "CREATE TABLE person (no CHAR(3), name CHAR(35), address CHAR(50), phone CHAR(16))")
      
            CheckDatabaseUpdate(0, "INSERT INTO person (no,name,address,phone) VALUES ('01','Mariah Carey','New York','9387382746273')")
            CheckDatabaseUpdate(0, "INSERT INTO person (no,name,address,phone) VALUES ('02','Madonna','Las Vegas','726376263621')")
            CheckDatabaseUpdate(0, "INSERT INTO person (no,name,address,phone) VALUES ('03','Britney Spears','Hollywood','746362747362')")
            CheckDatabaseUpdate(0, "INSERT INTO person (no,name,address,phone) VALUES ('04','Antonio Banderas','Mexico','8476378283847')")
            CheckDatabaseUpdate(0, "INSERT INTO person (no,name,address,phone) VALUES ('05','Jimmy Carter','Texas','8736473628948')")
            
            CloseDatabase(0)
         Else
           MessageBox_(#Null,"Can't open database !","Warning",#MB_OK|#MB_ICONERROR)
         EndIf
       Else
         MessageBox_(#Null,"Can't create the database file !","Warning",#MB_OK|#MB_ICONERROR)
      EndIf      
   
   ProcedureReturn #False
  EndIf 
EndProcedure
happy coding....
:D :D :D

Update :
validation for user input
can accept character ( ' ) like name O'Bryan
focus and lost focus on the text box when user typing data

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 12:08 pm
by VB6_to_PBx
Thank you very much for Posting this example !

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 12:45 pm
by Kiffi
doesn't work with Conan O'Brien ;-)

Greetings ... Kiffi

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 1:44 pm
by Rings
neither

Pete o'File


works :)

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 1:58 pm
by amif
source code has been updated
add some features

happy coding
:D :D :D

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 2:10 pm
by davido
Interesting!

Thank you for sharing. :D

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 2:34 pm
by Kiffi
not good:
amif wrote:

Code: Select all

ReplaceString(GetGadgetText(#yourGadget), "'", "`")
better:

Code: Select all

ReplaceString(GetGadgetText(#yourGadget), "'", "''")
Greetings... Kiffi

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 2:40 pm
by amif
Kiffi wrote:not good:
amif wrote:

Code: Select all

ReplaceString(GetGadgetText(#yourGadget), "'", "`")
better:

Code: Select all

ReplaceString(GetGadgetText(#yourGadget), "'", "''")
Greetings... Kiffi
thanks for your advice, it is work well.
:D :D :D

Re: [source code] SQLite Database programmmig

Posted: Wed Jun 26, 2013 8:40 pm
by Mindphazer
You should replace all your "messagebox_" API calls by MessageRequester, so that your code will be multi OS...

:wink:

Re: [source code] SQLite Database programmmig

Posted: Thu Jun 27, 2013 4:45 am
by kvitaliy
amif wrote:source code has been updated
add some features
Thanks for the code.
And you can add the "Search" button?
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited