PureBasic Forums - English

Code: Select all

UseSQLiteDatabase()


*Buffer = AllocateMemory(10)
FillMemory(*Buffer, 4, $32)

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
If DB
  SQL$ = "Create table records (id integer, t1 text, t2 text, b1 blob, b2 blob, b3 blob)"
  If DatabaseUpdate(DB, SQL$)
    SetDatabaseBlob(DB, 0, *Buffer, 4)
    SQL$ = "Insert into records (id, t2, b2) values (1, 'Hello', ?);"
    If DatabaseUpdate(DB, SQL$)
      FreeMemory(*Buffer)
      SQL$ = "Select id, t2, b1, b2, b3 From records Where id = 1;"
      If DatabaseQuery(DB, SQL$)
        If NextDatabaseRow(DB)
          Debug GetDatabaseLong(DB, 0)
          Debug GetDatabaseString(DB, 1)
          ; ignore b1 (DB, 2)
          BlobSize = DatabaseColumnSize(DB, 3)
          *Buffer = AllocateMemory(BlobSize)
          GetDatabaseBlob(DB, 3, *Buffer, BlobSize)
          ShowMemoryViewer(*Buffer, BlobSize)
          Debug PeekS(*Buffer, -1, #PB_Ascii)
          FreeMemory(*Buffer)
          ; ignore b3 (DB, 4)
        EndIf
        FinishDatabaseQuery(DB)
      EndIf
    EndIf
  EndIf
  CloseDatabase(DB)
EndIf

Code: Select all

UseSQLiteDatabase()
UseJPEGImageDecoder()

Enumeration
  #MainWindow
  #img1
  #txt1
  #txt2
  #btn1
  #btn2
  #dbID
EndEnumeration

Global dbFileName.s = "sample.db", currentRecord = 1

Procedure createSampleDatabase()
  If CreateFile(0, dbFileName)
    CloseFile(0)
    
    If OpenDatabase(#dbID, dbFileName, "", "")
      DatabaseUpdate(#dbID, "CREATE TABLE bio (idx INT, name CHAR(20), picture BLOB, dob CHAR(20))")
      Read.s name$
      Read.s dob$
      
      SetDatabaseBlob(#dbID, 0, ?picAlbert, ?picIsaac - ?picAlbert)
      queryString.s = "INSERT INTO bio (idx, name, picture, dob) values (1, '" + name$ + "', ?, '" + dob$ + "')"
      DatabaseUpdate(#dbID, queryString)
      
      Read.s name$
      Read.s dob$
      SetDatabaseBlob(#dbID, 0, ?picIsaac, ?picLeo - ?picIsaac)
      queryString.s = "INSERT INTO bio (idx, name, picture, dob) values (2, '" + name$ + "', ?, '" + dob$ + "')"
      DatabaseUpdate(#dbID, queryString)
      
      Read.s name$
      Read.s dob$
      SetDatabaseBlob(#dbID, 0, ?picLeo, ?binaryEnd - ?picLeo)
      queryString.s = "INSERT INTO bio (idx, name, picture, dob) values (3, '" + name$ + "', ?, '" + dob$ + "')"
      DatabaseUpdate(#dbID, queryString)
      
      CloseDatabase(#dbID)
    EndIf
    
  EndIf
EndProcedure

Procedure getNextRecord()
  If DatabaseQuery(#dbID, "SELECT * FROM bio WHERE idx = '" + currentRecord + "'")
    
    While NextDatabaseRow(#dbID)
      SetGadgetText(#txt1, GetDatabaseString(#dbID, 1))
      pictureSize = DatabaseColumnSize(#dbId, 2) 
      *picture = AllocateMemory(pictureSize)
      GetDatabaseBlob(#dbID, 2, *picture, pictureSize)
      CatchImage(1, *picture, pictureSize)
      ResizeImage(1, 300, 300)
      SetGadgetState(#img1, ImageID(1))
      SetGadgetText(#txt2, GetDatabaseString(#dbID, 3))
    Wend      
    
    FinishDatabaseQuery(#dbID)
    FreeMemory(*picture)
    
  EndIf
EndProcedure

createSampleDatabase()

If OpenDatabase(#dbID, dbFileName, "", "")
  wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
  OpenWindow(#MainWindow, 0, 0, 320, 390, "Database Example", wFlags)
  ImageGadget(#img1, 10, 10, 300, 300, 0)
  TextGadget(#txt1, 80, 320, 160, 30, "", #PB_Text_Center)
  TextGadget(#txt2, 80, 350, 160, 30, "", #PB_Text_Center)
  ButtonGadget(#btn1, 10, 320, 60, 30, "<< PREV")
  ButtonGadget(#btn2, 250, 320, 60, 30, "NEXT >>")  
  getNextRecord()
EndIf

Repeat
  Select WaitWindowEvent()
    Case #PB_Event_CloseWindow
      appQuit = 1
    Case #PB_Event_Gadget
      Select EventGadget()
        Case #btn1
          currentRecord - 1
          If currentRecord < 1 
            currentRecord = 3
          EndIf
          getNextRecord()
        Case #btn2
          currentRecord + 1
          If currentRecord > 3  
            currentRecord = 1
          EndIf
          getNextRecord()
      EndSelect
  EndSelect
Until appQuit = 1 

CloseDatabase(#dbID)

DataSection
  bio:
  Data.s "Albert Einstein", "March 14, 1879", "Isaac Newton", "January 4, 1643", "Leonardo da Vinci", "April 15, 1452"
  picAlbert: 
  IncludeBinary "albert.jpg"
  picIsaac: 
  IncludeBinary "isaac.jpg"
  picLeo: 
  IncludeBinary "leonardo.jpg"
  binaryEnd:
EndDataSection