PureBasic Forums - English

Code: Select all

  UseSQLiteDatabase()

  Filename$ = OpenFileRequester("Choose a file name", "PureBasic.sqlite", "*.sqlite|*.sqlite", 0)
  
  If OpenDatabase(0, Filename$, "", "")
    Debug "Connected to PureBasic.sqlite"
    If DatabaseUpdate(0, "CREATE TABLE info____ (test VARCHAR(255));")
      Debug "Table created"
    EndIf
  EndIf

Code: Select all

Enumeration 
  #windowMain
  #btnCreate
  #btnQuery
  #listResults
  #optRed
  #optGreen
  #optBlue
  #optYellow
  #labelOptions
  #dbaseID
EndEnumeration

UseSQLiteDatabase()
Global databaseFilename.s

Procedure createSampleDatabase()
  databaseFilename = OpenFileRequester("Select database file:", GetCurrentDirectory(), "", 0)
  
  If FileSize(databaseFilename) > 0 And
     MessageRequester("PureBasic Database Example:",
                      "The database already exists. Overwrite?", 
                      #PB_MessageRequester_YesNo) = #PB_MessageRequester_No
    ProcedureReturn       
  EndIf
  
  If databaseFilename And CreateFile(0, databaseFilename)
    CloseFile(0)
    If OpenDatabase(#dbaseID, databaseFilename, "", "")
      DatabaseUpdate(#dbaseID, "CREATE TABLE fruits (name CHAR(20), colour CHAR(20))")
      creationResults.s = "The following database has been created:" + 
                          #CRLF$ + #CRLF$ + "NAME, COLOUR" + #CRLF$
      Restore createDB
      For dataRead = 1 To 7
        Read.s fruitName.s
        Read.s fruitColour.s
        creationResults + fruitName + ", " + fruitColour + #CRLF$
        queryString.s = "INSERT INTO fruits (name, colour) VALUES ('" + 
                        fruitName + "', '" + fruitColour +"')"
        DatabaseUpdate(#dbaseID, queryString)
      Next
      CloseDatabase(#dbaseID)
      MessageRequester("Database Created", creationResults)
    EndIf
  EndIf
EndProcedure

Procedure queryDatabase(valueName.s, valueData.s)
  If OpenDatabase(#dbaseID, databaseFilename, "", "")
    queryString.s = "SELECT * FROM fruits WHERE " + valueName + " = '" + valueData + "'"
    If DatabaseQuery(#dbaseID, queryString)
      While NextDatabaseRow(#dbaseID)
        found = 1
        AddGadgetItem(#listResults, -1, "- " + GetDatabaseString(#dbaseID, 0))
      Wend
      If Not found
        AddGadgetItem(#listResults, -1, "- no matching results -")
      EndIf
      FinishDatabaseQuery(#dbaseID)
    EndIf
    CloseDatabase(#dbaseID)
  EndIf
EndProcedure

Procedure.s selectedColour()
  For colour = #optRed To #optYellow
    If GetGadgetState(colour)
      ProcedureReturn Trim(GetGadgetText(colour))
    EndIf
  Next
EndProcedure

wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
OpenWindow(#windowMain, 0, 0, 300, 60, "PureBasic Database Example", wFlags)
ButtonGadget(#btnCreate, 20, 15, 260, 30, "OPEN/CREATE DATABASE FILE")
ButtonGadget(#btnQuery, 20, 120, 260, 30, "QUERY DATABASE")
ListViewGadget(#listResults, 20, 165, 260, 90)
OptionGadget(#optRed, 25, 90, 50, 15, "red")
OptionGadget(#optGreen, 80, 90, 50, 15, "green")
OptionGadget(#optBlue, 160, 90, 50, 15, "blue")
OptionGadget(#optYellow, 225, 90, 50, 15, "yellow")
TextGadget(#labelOptions, 20, 60, 260, 25, "List all fruits with the colour:")

Repeat
  Select WaitWindowEvent()
    Case #PB_Event_CloseWindow
      appQuit = 1
    Case #PB_Event_Gadget
      Select EventGadget()
        Case #btnCreate
          createSampleDatabase()
          DisableGadget(#btnCreate, 1)
          If Not size1
            size1 = 1
            ResizeWindow(#windowMain, #PB_Ignore, WindowY(#windowMain) - 50, #PB_Ignore, 165)
          EndIf
        Case #btnQuery
          ClearGadgetItems(#listResults)
          AddGadgetItem(#listResults, -1, "Fruits that are " + selectedColour() + " in colour...")
          queryDatabase("colour", selectedColour())
          If Not size2
            size2 = 1
            ResizeWindow(#windowMain, #PB_Ignore, WindowY(#windowMain) - 60, #PB_Ignore, 280)
          EndIf
      EndSelect
  EndSelect
Until appQuit = 1

DataSection
  createDB:
  Data.s "apple", "red", "pear", "green", "banana", "yellow", "cherry"
  Data.s "red", "lemon", "yellow", "lime", "green", "strawberry", "red"
EndDataSection