Database in local-storage is not persistent

Just starting out? Need help? Post your questions and find answers here.
falsam
Posts: 280
Joined: Mon May 05, 2014 9:49 pm
Location: France
Contact:

Database in local-storage is not persistent

Post by falsam »

[2.20 Beta]

I was glad to see a database in local-storage but unfortunately it is not persistent.

To do this test, I took the code to peter

Code: Select all

If OpenDatabase(0)
  Debug "OpenDatabase(): OK"
  
  DatabaseUpdate(0, "Create Table SuperHeroes (Prename TEXT, Surname TEXT)")
  
  DatabaseUpdate(0, "Insert Into SuperHeroes (Prename, Surname) Values ('Peter', 'Parker')")
  DatabaseUpdate(0, "Insert Into SuperHeroes (Prename, Surname) Values ('Bruce', 'Wayne')")
  DatabaseUpdate(0, "Insert Into SuperHeroes (Prename, Surname) Values ('Clark', 'Kent')")
  
  If DatabaseQuery(0, "Select * From SuperHeroes Where Prename = 'Peter'")    
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 1) + "," + GetDatabaseString(0, 0)
    Wend
    FinishDatabaseQuery(0)    
  Else
    Debug "DatabaseQuery() failed"
    Debug DatabaseError() 
  EndIf
  CloseDatabase(0)
Else
  Debug "OpenDatabase() failed"
EndIf
ok the database is created.

Now I want to add another hero and list the contents of the database with this code.

Code: Select all

If OpenDatabase(0)
  Debug "OpenDatabase(): OK"
  
  ;Add new Hero
  DatabaseUpdate(0, "Insert Into SuperHeroes (Prename, Surname) Values ('Barry', 'Allen')")
  
  ;View all records
  If DatabaseQuery(0, "Select * From SuperHeroes")    
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 1) + "," + GetDatabaseString(0, 0)
    Wend
    FinishDatabaseQuery(0)
  Else    
    Debug "DatabaseQuery() failed"
    Debug DatabaseError() 
  EndIf
  CloseDatabase(0)
Else
  Debug "OpenDatabase() failed"
EndIf
Ooops doesn't work.
Debug wrote:OpenDatabase(): OK
DatabaseQuery() failed
no such table: SuperHeroes

➽ Windows 11 - JDK 1.8 - SB 2.40 - Android 13
http://falsam.com

Sorry for my poor english
User avatar
T4r4ntul4
Posts: 132
Joined: Wed May 21, 2014 1:57 pm
Location: Netherlands
Contact:

Re: Database in local-storage is not persistent

Post by T4r4ntul4 »

i tried it, in FireFox, but same results.
But it says with second time: 'OpenDatabase(): OK' so theres existing somewhere a database?
User avatar
useful
Posts: 116
Joined: Tue Feb 25, 2014 1:15 pm

Re: Database in local-storage is not persistent

Post by useful »

OpenDatabase every time creates a new instance in memory
2B or not 2B = FF
Fred
Site Admin
Posts: 1506
Joined: Mon Feb 24, 2014 10:51 am

Re: Database in local-storage is not persistent

Post by Fred »

You need to use ExportDatabaseMemory(), store the buffer in a file and read it back
User avatar
useful
Posts: 116
Joined: Tue Feb 25, 2014 1:15 pm

Re: Database in local-storage is not persistent

Post by useful »

useful wrote:OpenDatabase every time creates a new instance in memory
It doesn't example! Show me how to do it.
2B or not 2B = FF
falsam
Posts: 280
Joined: Mon May 05, 2014 9:49 pm
Location: France
Contact:

Re: Database in local-storage is not persistent

Post by falsam »

Fred wrote:You need to use ExportDatabaseMemory(), store the buffer in a file and read it back
What I mean, if I run the application the next day, to add a record I don't have access to the previous records.

➽ Windows 11 - JDK 1.8 - SB 2.40 - Android 13
http://falsam.com

Sorry for my poor english
Fred
Site Admin
Posts: 1506
Joined: Mon Feb 24, 2014 10:51 am

Re: Database in local-storage is not persistent

Post by Fred »

You need to store it yourself:

I just uploaded a new Database lib (Windows only) which fix an issue: https://www.spiderbasic.com/beta/Database

Code: Select all


Procedure CreateFileCallback(Status, Filename$, File, SizeRead)
  Select Status
    Case #PB_Status_Saved
      Debug "File saved: " + Filename$ + "(" + SizeRead + " bytes)"
      
    Case #PB_Status_Error
      Debug "Can't save the file: " + Filename$
  EndSelect
EndProcedure


Procedure PerformQuery(DB)
  If DatabaseQuery(DB, "Select * From SuperHeroes Where Prename = 'Peter'")
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 1) + "," + GetDatabaseString(DB, 0)
    Wend
    
    FinishDatabaseQuery(DB)
  Else
    Debug "DatabaseQuery() failed: " + DatabaseError()
  EndIf  
EndProcedure


Procedure ReadFileCallback(Status, Filename$, File, SizeRead)
  Select Status
    Case #PB_Status_Loaded
      Debug "File loaded: " + Filename$
      
      ; Get all the file as a new buffer 
      *Buffer = ExportFileMemory(File)
      Debug MemorySize(*Buffer)
      
      DB = OpenDatabase(#PB_Any, *Buffer)
      If DB
        Debug "OpenDatabase() read from file: OK"
        PerformQuery(DB)
      EndIf
      
    Case #PB_Status_Error
      Debug "Can't read the file: " + Filename$
      Debug "Creating a new database"
      
      DB = OpenDatabase(#PB_Any)
      If DB
        Debug "OpenDatabase(): OK"
        
        DatabaseUpdate(DB, "Create Table SuperHeroes (Prename TEXT, Surname TEXT)")
        
        DatabaseUpdate(DB, "Insert Into SuperHeroes (Prename, Surname) Values ('Peter', 'Parker')")
        DatabaseUpdate(DB, "Insert Into SuperHeroes (Prename, Surname) Values ('Peter', 'Jackson')")
        DatabaseUpdate(DB, "Insert Into SuperHeroes (Prename, Surname) Values ('Bruce', 'Wayne')")
        DatabaseUpdate(DB, "Insert Into SuperHeroes (Prename, Surname) Values ('Clark', 'Kent')")
        
        PerformQuery(DB)
        
        ; Now save the database to a persistent file
        ;
        *Buffer = ExportDatabaseMemory(DB)
        If CreateFile(0, "mydb.sqlite", @CreateFileCallback(), #PB_LocalStorage)
          WriteData(0, *Buffer, 0, MemorySize(*Buffer))
          CloseFile(0)
        EndIf
        
        CloseDatabase(DB)
      Else
        Debug "OpenDatabase() failed"
      EndIf
  EndSelect
EndProcedure

; Try to read the database if already present, or it will create a new one
;
ReadFile(0, "mydb.sqlite", @ReadFileCallback(), #PB_LocalStorage) 
falsam
Posts: 280
Joined: Mon May 05, 2014 9:49 pm
Location: France
Contact:

Re: Database in local-storage is not persistent

Post by falsam »

Yes very cool :The database is persistent in local-storage.

If I run another code, I have the result of the database created by the previous code of creation of the database.

Code: Select all

Procedure SelectRecords(DB)
  Protected ReqSQL.s = "Select * From SuperHeroes"
  
  If DatabaseQuery(DB, ReqSQL)    
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 1) + "," + GetDatabaseString(DB, 0)
    Wend
    FinishDatabaseQuery(DB)    
  Else
    Debug "DatabaseQuery() failed" + DatabaseError() 
  EndIf  
EndProcedure

Procedure ReadFileCallback(Status, Filename$, File, SizeRead)
  Select Status
    Case #PB_Status_Loaded
      Debug "File loaded: " + Filename$
      
      ; Get all the file as a new buffer 
      *Buffer = ExportFileMemory(File)      
      DB = OpenDatabase(#PB_Any, *Buffer)
      
      If DB
        Debug "OpenDatabase() read from file: OK"
        SelectRecords(DB)
      EndIf
      
    Case #PB_Status_Error
  EndSelect
EndProcedure

ReadFile(0, "mydb.sqlite", @ReadFileCallback(), #PB_LocalStorage)
Thanks Fred.

➽ Windows 11 - JDK 1.8 - SB 2.40 - Android 13
http://falsam.com

Sorry for my poor english
Fred
Site Admin
Posts: 1506
Joined: Mon Feb 24, 2014 10:51 am

Re: Database in local-storage is not persistent

Post by Fred »

You can also import any existing sqlite database as well (created with PureBasic for example).
Fred
Site Admin
Posts: 1506
Joined: Mon Feb 24, 2014 10:51 am

Re: Database in local-storage is not persistent

Post by Fred »

To understand how the database works: it's always created in memory and stay in memory. You have to use the regular file functions to persist it. You can persist any files now, not just database.
Post Reply