Opening, creating sqlite database

[johnorourke1351]

Hi,

I'm wondering if someone can point me to a solution for opening sqlite databases.

I want an routine that will open the database "example.s3db" say in the default file area. If it can't be found there then the program offers the request/browse screen and/or an option to create a new empty database.

Thanks for any info

[firace]

Have you seen this example in the help file? Should be rather easy to adapt to your needs.

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

[TI-994A]

...routine that will open the database "example.s3db" say in the default file area. If it can't be found there then the program offers the request/browse screen and/or an option to create a new empty database.

Hi John. This simple example does something similar, and might just get you started:

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

Hope it helps.

[johnorourke1351]

Wow!

An embarrassment of answers. Thanks so much. This is a great birthday present!

[SeregaZ]

probably time is come for this question "database". F1 is very nice, but i have another language - not english - and many things i cant understand. so my question is:
can this database command work with internet sites? for example i have EasyPHP - it have MySql part. can i make PB and this MySql love each other? as i understand OpenDatabase(#Database, DatabaseName$, User$, Password$ [, Plugin]) work only with "offline" files.

and what it is this MySql by simple words? it like big excel file, that opened all time for write and read info?



ops... UsePostgreSQLDatabase() is answer now i need to find how to make some user in MySql for make autorization by OpenDatabase