PureBasic Forums - English

Posted: **Thu Mar 12, 2026 3:26 pm**

Hi.

I have very small experience working with databases. Could someone tell me how to do it better ? The code below will show an approximate table structure. There is a column that stores the value of the Date() command. I store a number, not text. This is done for speed. Will this be enough to make a filter in the future ? For example, I want to retrieve records only for a specific time period: a selection from the entire table, but only for specific hours or a date range. Or both. That is, I want to show only evening records from a specific date range. I would also like to be able to further filter the results by a specific type (this field will have values: 1, 2, 3, etc.). I know these are complex queries, so any help would be appreciated. I admit that it will take several steps.

Also the table has a column with the type. When the table is created, its type is specified as INTEGER, but in the rest of the code the variable is of type LONG (see structure 'table'). This is done because there is only Get(Set)Database Long(), but not INTEGER. Is this normal ?

Also I adhere to the concept of working with an in-memory database. To save the results of program work, I have a separate db-file.

Here's what I have at the moment:

Code: Select all

Structure table
  sometime.q
  FileType.l
  event$
  Compressed$
EndStructure

Procedure LoadDB(dbf$, *str.string)
  Protected Result = #False
  Protected dbm = OpenDatabase(#PB_Any, ":memory:", #Empty$, #Empty$, #PB_Database_SQLite)
  
  If dbm
    Debug "Open database in memory - success."
    If FileSize(dbf$) > 0
      Debug "A previously saved database was found."
      If DatabaseUpdate(dbm, "ATTACH DATABASE '" + dbf$ + "' AS disk")
        Debug "Attach database file - success."
        If DatabaseUpdate(dbm, "CREATE TABLE main.notes AS SELECT * FROM disk.notes")
          Debug "Data loaded from file into RAM."
          If DatabaseUpdate(dbm, "DETACH DATABASE disk")
            Debug "Detach database file - success."
            *str\s = "read table"
            Result = dbm
          Else
            *str\s = "Detach database file error."
          EndIf
        Else
          *str\s = "Copy database into memory error."
        EndIf
      Else
        *str\s = "Unable to attach database file."
      EndIf
    Else
      If DatabaseUpdate(dbm, "CREATE TABLE notes (id INTEGER PRIMARY KEY ASC, dt INTEGER, type INTEGER, event TEXT, msg TEXT)")
        Debug "A clean database has been created in RAM."
        Result = dbm
      Else
        *str\s = "Сan't create a new database."
      EndIf
    EndIf
  Else
    *str\s = "Can't open database in mem !"
    Debug DatabaseError()
  EndIf

  ProcedureReturn Result
EndProcedure

Procedure SaveDB(dbm, dbf$, *str.string)
  Protected Result = #False
  
  If IsDatabase(dbm)
    If FileSize(dbf$) >= 0 : DeleteFile(dbf$) : EndIf
    If DatabaseUpdate(dbm, "VACUUM INTO '" + dbf$ + "'")
      Result = #True
      Debug "Backup completed successfully !"
    Else
      *str\s = "Backup error !"
    EndIf
  EndIf
  ProcedureReturn Result
EndProcedure

Procedure Read_table(dbm, Array MyArray.s(1))
  Protected Result = #False
  Protected tmp$, n.i
  
  If DatabaseQuery(dbm, "SELECT COUNT(*) FROM notes")
    If NextDatabaseRow(dbm)
      tmp$ = GetDatabaseString(dbm, 0)
      Debug "Records in DB: " + tmp$
      n = Val(tmp$)
    EndIf
    FinishDatabaseQuery(dbm)
  EndIf

  If n > 0
    ReDim MyArray(n-1) : n=0
    If DatabaseQuery(dbm,"SELECT dt, event, msg FROM notes;"); LIMIT 1;")
      While NextDatabaseRow(dbm)
        sometime = GetDatabaseLong(dbm, 0)
        tmp$ = FormatDate("%dd.%mm.%yyyy  %hh:%ii:%ss", sometime) + #TAB$
        tmp$ = tmp$ + GetDatabaseString(dbm, 1) + #TAB$
        tmp$ = tmp$ + GetDatabaseString(dbm, 2)
        Debug tmp$
        MyArray(n) = GetDatabaseString(dbm, 2)
        n+1
      Wend
      FinishDatabaseQuery(dbm)
      Result = #True
    EndIf
  EndIf
  ProcedureReturn Result
EndProcedure
  
Procedure AddRecord(dbm, *data.table)
  Protected Result = #False

  With *data
    SetDatabaseQuad(dbm,   0, \sometime)
    SetDatabaseLong(dbm,   1, \FileType)
    SetDatabaseString(dbm, 2, \event$)
    SetDatabaseString(dbm, 3, \Compressed$)
  EndWith
  If DatabaseUpdate(dbm,"INSERT INTO notes (dt, type, event, msg) VALUES (?, ?, ?, ?);")
    Result = #True
  EndIf
  ProcedureReturn Result
EndProcedure


UseSQLiteDatabase()

Define DataBase.i, DB_File$ = "d:\data.db"
Define error.string, *err = @error
Define table.table, *data = @table

Dim msg.s(0)
Define.i i,n

DataBase = LoadDB(DB_File$, *err)
If DataBase = #False
  MessageRequester("Error", error\s)
  End
EndIf

If error\s = "read table"
  Debug ""
  Debug "Read records from a table..."
  If Read_table(DataBase, msg()) = #True
    n = ArraySize(msg())
    Debug "ArraySize:  n = " + Str(n)
    For i = 0 To n
      Debug msg(i)
    Next
  Else
    MessageRequester("Error", "Can't read the table.")
    End
  EndIf
  Debug ""
EndIf

; Adding a new record To a table
table\sometime    = Date()
table\FileType    = 1
table\event$      = "some event..."
table\Compressed$ = "some data..."

If AddRecord(DataBase, *data) = #True
  Debug "New record was successfully added."
Else
  MessageRequester("Error", "Can't execute the query: " + DatabaseError())
EndIf

If SaveDB(DataBase, DB_File$, *err) = #False
  MessageRequester("Error", error\s)
  End
EndIf
CloseDatabase(DataBase)

Who will have any comments or recommendations ?
Can someone help me with implementing a complex filter query ?

Thank you in advance !

Added:
How to hide this piece of code in a procedure ?

Code: Select all

DataBase = LoadDB(DB_File$, *err)
If DataBase = #False
  MessageRequester("Error", error\s)
  End
EndIf

If error\s = "read table"
  Debug ""
  Debug "Read records from a table..."
  If Read_table(DataBase, msg()) = #True
    n = ArraySize(msg())
    Debug "ArraySize:  n = " + Str(n)
    For i = 0 To n
      Debug msg(i)
    Next
  Else
    MessageRequester("Error", "Can't read the table.")
    End
  EndIf
  Debug ""
EndIf

so that in the main code only the following:

Code: Select all

DataBase = InitDB(DB_File$)

Posted: **Thu Mar 12, 2026 7:24 pm**

Databases?
Do you mean poor AIs forcibly interpreting data in a biased way?

Posted: **Thu Mar 12, 2026 7:29 pm**

Code: Select all

Procedure.i InitDB(DB_File$, Array msg.s(1))
  
  Protected DB.i
  Protected error.string
  
  
  DB = LoadDB(DB_File$, @error)
  If DB = #False
    MessageRequester("Error", error\s)
    End
  EndIf
  
  If error\s = "read table"
    Debug ""
    Debug "Read records from a table..."
    If Read_table(DB, msg())
      n = ArraySize(msg())
      Debug "ArraySize:  n = " + Str(n)
      For i = 0 To n
        Debug msg(i)
      Next
    Else
      MessageRequester("Error", "Can't read the table.")
      End
    EndIf
    Debug ""
  EndIf
  
  ProcedureReturn DB
  
EndProcedure

Posted: **Thu Mar 12, 2026 8:20 pm**

infratec

Thank you for your reply. Unfortunately, I won’t be able to answer today, only tomorrow evening.
Anyway... here's my new version:

Code: Select all

Structure table
  sometime.q
  FileType.l
  event$
  Compressed$
EndStructure

Procedure LoadDB(dbf$, *str.string)
  Protected Result = #False
  Protected dbm = OpenDatabase(#PB_Any, ":memory:", #Empty$, #Empty$, #PB_Database_SQLite)
  
  If dbm
    Debug "Open database in memory - success."
    If FileSize(dbf$) > 0
      Debug "A previously saved database was found."
      If DatabaseUpdate(dbm, "ATTACH DATABASE '" + dbf$ + "' AS disk")
        Debug "Attach database file - success."
        If DatabaseUpdate(dbm, "CREATE TABLE main.notes AS SELECT * FROM disk.notes")
          Debug "Data loaded from file into RAM."
          If DatabaseUpdate(dbm, "DETACH DATABASE disk")
            Debug "Detach database file - success."
            *str\s = "read table"
            Result = dbm
          Else
            *str\s = "Detach database file error."
          EndIf
        Else
          *str\s = "Copy database into memory error."
        EndIf
      Else
        *str\s = "Unable to attach database file."
      EndIf
    Else
      If DatabaseUpdate(dbm, "CREATE TABLE notes (id INTEGER PRIMARY KEY ASC, dt INTEGER, type INTEGER, event TEXT, msg TEXT)")
        Debug "A clean database has been created in RAM."
        *str\s = "not read"
        Result = dbm
      Else
        *str\s = "Сan't create a new database."
      EndIf
    EndIf
  Else
    *str\s = "Can't open database in mem !"
    Debug DatabaseError()
  EndIf

  ProcedureReturn Result
EndProcedure

Procedure SaveDB(dbm, dbf$, *str.string)
  Protected Result = #False
  
  If IsDatabase(dbm)
    If FileSize(dbf$) >= 0 : DeleteFile(dbf$) : EndIf
    If DatabaseUpdate(dbm, "VACUUM INTO '" + dbf$ + "'")
      Result = #True
      Debug "Backup completed successfully !"
    Else
      *str\s = "Backup error !"
    EndIf
  EndIf
  ProcedureReturn Result
EndProcedure

Procedure Read_table(dbm, Array MyArray.s(1))
  Protected Result = #False
  Protected tmp$, n.i
  
  If DatabaseQuery(dbm, "SELECT COUNT(*) FROM notes")
    If NextDatabaseRow(dbm)
      tmp$ = GetDatabaseString(dbm, 0)
      Debug "Records in DB: " + tmp$
      n = Val(tmp$)
    EndIf
    FinishDatabaseQuery(dbm)
  EndIf

  If n > 0
    ReDim MyArray(n-1) : n=0
    If DatabaseQuery(dbm,"SELECT dt, event, msg FROM notes;"); LIMIT 1;")
      While NextDatabaseRow(dbm)
        sometime = GetDatabaseLong(dbm, 0)
        tmp$ = FormatDate("%dd.%mm.%yyyy  %hh:%ii:%ss", sometime) + #TAB$
        tmp$ = tmp$ + GetDatabaseString(dbm, 1) + #TAB$
        tmp$ = tmp$ + GetDatabaseString(dbm, 2)
        Debug tmp$
        MyArray(n) = GetDatabaseString(dbm, 2)
        n+1
      Wend
      FinishDatabaseQuery(dbm)
      Result = #True
    EndIf
  EndIf
  ProcedureReturn Result
EndProcedure
  
Procedure AddRecord(dbm, *data.table)
  Protected Result = #False

  With *data
    SetDatabaseQuad(dbm,   0, \sometime)
    SetDatabaseLong(dbm,   1, \FileType)
    SetDatabaseString(dbm, 2, \event$)
    SetDatabaseString(dbm, 3, \Compressed$)
  EndWith
  If DatabaseUpdate(dbm,"INSERT INTO notes (dt, type, event, msg) VALUES (?, ?, ?, ?);")
    Result = #True
  EndIf
  ProcedureReturn Result
EndProcedure

Procedure InitDB(dbf$, Array MyArray.s(1), *str.string)
  Protected Result = LoadDB(dbf$, *str)

  If Result = #False
    ProcedureReturn 0
  EndIf
  
  If *str\s = "read table"
    Debug ""
    Debug "Read records from a table..."
    If Read_table(Result, MyArray()) = #False
      *str\s = "Can't read the table."
    EndIf
  EndIf
  ProcedureReturn Result
EndProcedure


UseSQLiteDatabase()

Define DataBase.i, DB_File$ = "d:\data.db"
Define error.string, *err = @error
Define table.table, *data = @table

Dim msg.s(0)
Define.i i,n

DataBase = InitDB(DB_File$, msg(), *err)
If DataBase = #False
  MessageRequester("Error", error\s)
  End
EndIf

; Checking
If error\s = "read table"
  n = ArraySize(msg())
  Debug "ArraySize:  n = " + Str(n)
  For i = 0 To n
    Debug msg(i)
  Next
  Debug ""
EndIf

; Adding a new record To a table
table\sometime    = Date()
table\FileType    = 1
table\event$      = "some event..."
table\Compressed$ = "some data..."

If AddRecord(DataBase, *data) = #True
  Debug "New record was successfully added."
Else
  MessageRequester("Error", "Can't execute the query: " + DatabaseError())
EndIf

If SaveDB(DataBase, DB_File$, *err) = #False
  MessageRequester("Error", error\s)
  End
EndIf
CloseDatabase(DataBase)

Of course I will have more questions about filters.
I would be grateful for any examples applicable to this code. Filter by date range, time range (or both), and message type (in any combination).

Thanks in advance !

Posted: **Thu Mar 12, 2026 8:34 pm**

Code: Select all

SQL$ = "SELECT * FROM notes WHERE datetime(dt, 'unixepoch') BETWEEN '2026-03-10 00:00:00' AND '2026-03-15 23:59:59' And time(dt, 'unixepoch') BETWEEN '19:00:00' And '19:30:00'"
  If DatabaseQuery(DataBase, SQL$)
    While NextDatabaseRow(DataBase)
      Debug FormatDate("%dd.%mm.%yyyy  %hh:%ii:%ss", GetDatabaseQuad(DataBase, 1)) + " " + GetDatabaseString(Database, 3) + " " + GetDatabaseString(Database, 4)
    Wend
  Else
    Debug DatabaseError()
  EndIf

PureBasic Forums - English

Simple SQL db questions.

Simple SQL db questions.

Re: Simple SQL db questions.

Re: Simple SQL db questions.

Re: Simple SQL db questions.

Re: Simple SQL db questions.