Inserted Records disappears…

Databases
1

I have Add Record and Batch Import Record…

Both store data tinto the .sqlite file (it seems); my Display window shows those data into its ListBox, but when I quit the application / run back, these records disappears.

I followed the “App.Open” Event (where the app working folder is created… and nothing weird happens.

I locked the .sqlite file there, run and no complains…

It looks like if the data base file is a ghost; I looked at the wDisplay file code that add the data into the ListBox, and the FolderItem for the .sqlite file (used in that code) shows the file where I think it is.

I am starting to be wonky…

M1 MacBook Pro
Xojo 2021r2.1
Monterey 12.6

The batch Import uses


INSERT INTO Books("<list of columns>") "VALUES (?, ?, ?, ?, ?, ?)"

as well as Add Record…

No error, no exception…

2

you have " really around values?

3

I do not understand.

The used code comes from the documentation, and was expanded (because the documentation used only one column and I have 6 columns to insert data).

I read the inserted data and display them in a ListBox… using the same kind of code (still from the documentation).

4

it should look like

INSERT INTO Books (fielda,fieldb,fieldc) VALUES (?, ?, ?)

i don’t know where you find your example from documentation.

if you add data via .AddRow maybe you need a commit.

5 
SQL_Cmd = "INSERT INTO Books(Author_Name_First, Author_Name_Family, Book_Name, "+_
"Release_Date, Summary, Read) VALUES (?, ?, ?, ?, ?, ?)"

Then


App.gDB.ExecuteSQL(SQL_Cmd, aRecord.NthField(Chr(9),1), aRecord.NthField(Chr(9),2), aRecord.NthField(Chr(9),3), aRecord.NthField(Chr(9),4), aRecord.NthField(Chr(9),5), aRecord.NthField(Chr(9),6))

Documentation:
https://documentation.xojo.com/api/databases/sqlitedatabase.html

SelectSQL for example for reading…

I do not found the code with INSERT, but ExecuteSQL have the same kind of code, but with UPDATE…

6

are you sure the database it open/connected? there was a issue if not.

7

Yes, and I get no error nor anyException.

The code is in Try … Catch … End Try…

I still do not found the INSERT code in the LR…

8

what database do you use?

9

The answer was some lines above: SQLite.

I just checked if there is a FolderItem passed to App.gDB.DatabaseFile, and there is one.

I add:


System.DebugLog App.gDB.DatabaseFile.NativePath

After the Commit line and I get the lines (in the batch import) with the path of the .sqlite file; but that file modification date does not change (nearly 3 hours ago) and its size is still 8192 Bytes.

10

hmm, try to separate it,
close other db designer tools.
xojo, open db instacne with folder item, connect, execute a simple insert statement in one method.

11

I do not use db designer tool.

I will go back home asap and then I will wrote standard SQL code (no injection measures code) and watch what happens.

Sometimes a simple shutdown / reboot make changes…

Thanks.

PS: I do not know why, but I have failed to reach from where the INSERT code I use comes from in the Documentation.

And I searched before eat time and after…

12

Just tell you do not found something and it comes to you…

Here’s the link:
https://docs.xojo.com/UserGuide:Database_Operations#INSERT_SQL

13

The answer was simple: the database was created in memory.

The error was probably a wrong line of code order…

DB.DatabaseFile = f

was certainly not were it belong.

I’ve done so many changes that I cannot be certain, and the culprit was when I coded the read from the .sqlite file using API1 and “direct” code (in the display window, I opened the .sqlite file and open it from that FolderItem / API1 db code to read its contents).

I was certainly tired, but once I was back home, I wrote new read code and that was clear to me where to search.

1 Like
14

passed to all of us :slight_smile:

i usually sub class this database and it looks like:


Public Sub Constructor()
  // Calling the overridden superclass constructor.
  Super.Constructor
  
  Self.DatabaseFile = File()
  
  Self.Connect
End Sub

Public Sub Destructor()

  Self.Close
  
End Sub

Public Function File() As FolderItem
  Var f As FolderItem = SpecialFolder.Documents.Child("Database").Child("SomeDB.Xojo.sqlite")
  
  System.DebugLog f.NativePath
  
  Return f
  
End Function
15

Yes.

In App.Open, I call methods that creates a folder for the application in the ApplicationSupport folder and creates the Data Base file / add TABLE(s), and store the References in App Properties.

Then in Modules, I place code to Add Record, Import from txt, Modify, Delete, Export, Display, etc…

Each one its own food :wink:

… and sometimes it is harder than other times…

1 Like