[source code] SQLite Database programmmig

Share your advanced PureBasic knowledge/code with the community.
Post Reply
amif
New User
New User
Posts: 3
Joined: Wed Jun 19, 2013 3:16 pm

[source code] SQLite Database programmmig

Post 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
Last edited by amif on Wed Jun 26, 2013 1:56 pm, edited 1 time in total.
Top
User avatar
VB6_to_PBx
Enthusiast
Enthusiast
Posts: 627
Joined: Mon May 09, 2011 9:36 am

Re: [source code] SQLite Database programmmig

Post by VB6_to_PBx »

Thank you very much for Posting this example !
 
PureBasic .... making tiny electrons do what you want !

"With every mistake we must surely be learning" - George Harrison
Top
User avatar
Kiffi
Addict
Addict
Posts: 1504
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: [source code] SQLite Database programmmig

Post by Kiffi »

doesn't work with Conan O'Brien ;-)

Greetings ... Kiffi
Hygge
Top
User avatar
Rings
Moderator
Moderator
Posts: 1435
Joined: Sat Apr 26, 2003 1:11 am

Re: [source code] SQLite Database programmmig

Post by Rings »

neither

Pete o'File


works :)
SPAMINATOR NR.1
Top
amif
New User
New User
Posts: 3
Joined: Wed Jun 19, 2013 3:16 pm

Re: [source code] SQLite Database programmmig

Post by amif »

source code has been updated
add some features

happy coding
:D :D :D
Top
davido
Addict
Addict
Posts: 1890
Joined: Fri Nov 09, 2012 11:04 pm
Location: Uttoxeter, UK

Re: [source code] SQLite Database programmmig

Post by davido »

Interesting!

Thank you for sharing. :D
DE AA EB
Top
User avatar
Kiffi
Addict
Addict
Posts: 1504
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: [source code] SQLite Database programmmig

Post by Kiffi »

not good:
amif wrote:

Code: Select all

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

Code: Select all

ReplaceString(GetGadgetText(#yourGadget), "'", "''")
Greetings... Kiffi
Hygge
Top
amif
New User
New User
Posts: 3
Joined: Wed Jun 19, 2013 3:16 pm

Re: [source code] SQLite Database programmmig

Post 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
Top
User avatar
Mindphazer
Enthusiast
Enthusiast
Posts: 460
Joined: Mon Sep 10, 2012 10:41 am
Location: Savoie

Re: [source code] SQLite Database programmmig

Post by Mindphazer »

You should replace all your "messagebox_" API calls by MessageRequester, so that your code will be multi OS...

:wink:
MacBook Pro 16" M4 Pro - 24 Gb - MacOS 15.4.1 - Iphone 15 Pro Max - iPad at home
...and unfortunately... Windows at work...
Top
kvitaliy
Enthusiast
Enthusiast
Posts: 162
Joined: Mon May 10, 2010 4:02 pm

Re: [source code] SQLite Database programmmig

Post by kvitaliy »

amif wrote:source code has been updated
add some features
Thanks for the code.
And you can add the "Search" button?
Top
Post Reply

Return to “Tricks 'n' Tips”