Hi, I have been using the SQLiteDatabase for a while now and recently found a bug was found that was unexpected. When a user imports new data in my application, on rare occasions the data is not deleted. I tracked it down to there be a huge number of records in the database.
I use
dataRS = mDatabase.SQLSelect("SELECT * FROM " + tableName)
I then check for a DB error, which there is none.
I then check if the recordset (dataRS) is nil and it is.
There are 321995 records in the database where I am seeing the RecordSet equal nil. If I do
dataRS = mDatabase.SQLSelect"SELECT rowID FROM " + tableName)
It returns a RecordSet correctly. I am suspecting that perhaps there is not enough memory to return all the records, but should I expect a database error as a nil RecordSet has been returned ? Is there a limit to how much can be returned in a RecordSet ?
In other areas of the application, I never get all the records as only specific records are used matching certain criteria. It is only when I want to select all records to delete them that I see this.
Using Xojo 2016 r1.1 (it is the same in R2016 r2). Running Mac OS X. There are about 400 fields per record. The database is a file.
Well, you must ALWAYS check for the database error after every database operation. It will always be true if recordset is nil. The error message will tell you what’s wrong.
Design:
400 columns per record is probably not a good design. 15 years of doing this for clients all over the world says that you’ve probably not designed it properly. I’m sure you have perfectly valid reasons for doing so, but just saying that I’d look at this with much attention.
RowID: In my opinion you would be better off using an Auto Increment primary key field. RowID is not guaranteed to never change whereas the primary key will always be unique.
may be you have one or more fields that have a name that is a reserved sqlite keyword.
so selecting * gives some error, and selecting rowid returns a valid recordset.
that is 128 Meg of data assuming that each field as only ONE byte in size with ZERO overhead (which of course is impossible)
avg of 16 bytes hits the 2 GIG mark
I think it might be time to reengineer not only your thinking, but your database, and the programs that manipulate it
simply delete the SQLite file and create a brand new one, add the Tables.
That said, people can be right. Are-you sure you cannot create some other Table to store data in and call the data from the main Table (index to another Table data) ?
It is late, I forgot the names and how to do that, I only recall that it is possible to store a reference to data stored in a different Table (so intead of 400 Columns with data in a Record, you will have some reference to other Tables where the real data is stored).
a database with a single table that has lots of rows and lots of columns and more than likely duplicates lots of data between rows and columns if basically a flat normalized inefficient storage option.
What is needed is a multi-table, non duplicated, indexed and keyed relational database, where each table contains as unique a set of data as possible, and any record that “could” exist in the above “flat” table, can be constructed via the proper “relations” between the smaller faster tables.
Unfortunatly, nobody here is going to be able to tell you “exactly” how to do that without knowing specifics about your existing table, the manner is which data is inserted, deleted, updated and queried.
Thanks for your comments. I had a feeling I would get some flack about the 400 fields. My application was my first project in Xojo and my first time using a database (started over 5 years ago). I have learned a lot in that time and will redesign the database for v2.0 of my application. The way I search for data in the database, using 400 fields made it easy. Now I am more experienced with Xojo and databases I will come up with a more efficient design.
The point though is that the database did not give me an error (I check right after the SQLSelect) and I get a nil recordset is more of a concern.
a nil recordset for me has always been a syntax error in the sql select.
I get the parameter of the sqlselect command, and try it in another sql app if I don’t have any explicit error.
if you have a sql reserved keyword in one of your field, you don’t always have an explicit error message.
if you moved recently to a xojo 2016, and got the error, then look at the new sql keywords xojo has updated the sqlite engine. https://www.sqlite.org/lang_keywords.html
amen! or in PostgeSQL there is a “serial” type which will give a unique ID everytime. Either way. I can tell you that RowID in SQLite does change as the recods in the table changes.
Of course your sql could update it but then its your fault
Other than that vacuum should be the only way the rowid changes in sqlite https://www.sqlite.org/lang_vacuum.html
