Seeking advice on internal database

[DMG]

I am trying to learn LibreOffice Base, version 7.5 on a Windows 10 Desktop (64-bit), with a HSQLDB embedded database.

Many years ago, I created a database in Microsoft Access… but because I want to reduce my reliance on Microsoft products, I’ve been struggling the past few months to learn LibreOffice Base. I exported/imported 1,200 records from the Access database into the HSQLDB database.

Unfortunately, things have been very “buggy” … and searches for data via my form seem to take a very long time. So I’m wondering whether the database became corrupted somehow during the import process.

As a result, I’m thinking about:

1. Uninstalling 7.5 version of LibreOffice, and reinstalling an earlier version that has been tested a little longer. Any advice as to which version? Would this be a simple uninstall… or do I need to take any extra steps to remove lingering pieces?

2. I’m also considering STARTING ALL OVER, and creating a brand new database from scratch (instead of trying to import the data). Are there any recommendations as to which internal database I should use (HSQLDB or Firebird) if I do start over? I do have a few fields that need many lines of text, and I’m currently using the Memo field type… not sure if Firebird will allow this.

Thanks for any advice!

[Ratslinger]

Hello,

@DMG

Unfortunately, things have been very “buggy” … and searches for data via my form seem to take a very long time.

.
1200 records is not a lot in any database. No way to tell the problem without a sample.
.

@DMG

Uninstalling 7.5 version of LibreOffice, and reinstalling an earlier version

.
Don’t see a need for this. Not much has happened in Base to warrant this. If unusual problems, it may be due to an issue in the user profile. See → LibreOffice user profile - The Document Foundation Wiki
.
As for which database? That is a tough question and really depends upon your needs. I avoid HSQLDB embedded for personal use. Good for quick posts & learning. It is old. I like Firebird embedded (many others do not) but it also has baggage. Search this site for Firebird.
.
You may want to look at split HSQLDB. Has much newer versions than the embedded in Base. The data not within Base. See:
.
Apache OpenOffice Community Forum - [Wizard] Create a new 'split' HSQL 2.x database - (View topic)
.
and
.
How do I create a new 'split' HSQL database using Base? - Apache OpenOffice Wiki
.
There are also MariaDB, MySQL, PostgreSQL (my preferred DB) and SQLite to name a few.
.

@DMG

I do have a few fields that need many lines of text

You will find most other databases will do what HSQLDB embedded does and much more since they have more recent updates to them.

[DMG]

Thank you, @Ratslinger ! I truly appreciate the pointers.
.
It’s very good to know that 1,200 isn’t considered too many records to weigh down the system… so it must be something else. I will check out the Profile issue you mentioned. Thanks again!

[Wanderer]

@DMG

1,200 isn’t considered too many records to weigh down the system

I’m using a Sqlite-database with properies of 80.000 image-files (not the actual photos) on a Core-I5 without noticing any delay.
.
Delays can be caused by complex sub-selects and sometimes cured by creating special index…
.
Another point: How do you search? Is this a filtering select or some search in the GUI or do you “search” by a macro?

[Villeroy]

Do you have thousands of pictures stored in the database and things become laggy when working with a form?

[DMG]

No, @Villeroy . Although there is an Image Control on the form, it references the filename in a text field… I did not embed the actual image into the database. The size of this database of 1200 records is 7,534 KB.
.
Today I timed a search that I knew would find the last record in the database (record #1201). It took 40 seconds to go through all the records. That seems incredibly long to me…
.

• Is there an “Index” function that I need to perform for which I’m unaware?
• Also, Microsoft Access had a “Compact and Repair” function that I used occasionally as part of the routine maintenance… does Base have something like that?

THANKS!

[DMG]

Hi, @Wanderer
.
I did not know that I could create a special index. Is that complicated?
.
I don’t know how to search by a macro…
.
I am searching on a form by using the “Magnifying Glass” icon at the bottom left of the form. As I stated to Villeroy below, it takes about 40 seconds to search through all the records on the form, which seems like a long time.

[DMG]

Just to follow-up on this post, @Ratslinger … resetting the Profile didn’t solve the the random quirkiness I’ve seen nor the lag issues during search.
.
I was learning on this database, so I’ll look into how to create a Firebird or split HSQLDB on the next database I create. Hope it’s easier… Ha! Ha!

[Ratslinger]

@DMG
It would be unusual to search on every field in a table for an item. Would believe you are searching on a specific column. See:
.
https://help.libreoffice.org/6.2/en-US/text/shared/explorer/database/05010100.html?&DbPAR=SHARED&System=UNIX
.

Is there a way to create Indexes that will actually speed up record searches in LibreOffice Base?

Is there a way to create an index in LibreOffice base that will actually speed up the searches. I used: CREATE INDEX IDX_FName ON “Contacts”(“FirstName”) which was executed successfully (though required a re-start of Base due to an error message saying I was out of memory). However upon doing the exact same search as without the index the search took the exact same amount of time (A full 12 seconds, which will only increase as the database gets used more and acquires more records). I have wr…

.
Also, if one field why not use a filter on the form? Maybe more information about what you are actually doing might help.

[DMG]

It’s good to know there is an Index function… I’ll look at the link you posted within the next few days.
.
My form does have a lot of fields on it… perhaps too many for efficiency?
.

.
BUT… I’m only searching on ONE field (the fldRecipeName). I usually can’t remember the exact name of the recipe, so I’ll click on the “Find Record” icon at the bottom left of the screen and enter a phrase like “Pork Loin,” anywhere within the fldRecipeName. But this method of search takes 40 seconds to go through 1200 records on the fldRecipeName field.
.

.
I have also used the Form-Based filter to select CHECKBOXES for sets of recipes (ie., diabetic recipes, appetizers, crockpot collections, etc.). This method does work quickly. But how would I search for part of a recipe name? I thought that the following syntax would work, but it doesn’t seem to work:

LIKE’*Rice*’

.
Hopefully, the Index function will help…

[Ratslinger]

@DMG
Guessing this represents only two tables - main and ingredients.
.
Although not a picture, I would have the procedure in a separate table. Also seems like a lot of duplication in the Nutrition section. With a separate table set correctly it would be easier to search, sort by & list.
.
Probably other items but too busy for my liking; but then I am not using it.
.
For the search it is:

LIKE '%Rice%'

[Wanderer]

@DMG

but it doesn’t seem to work:

LIKE’*Rice*’

@Ratslinger

For the search it is:

LIKE '%Rice%'

@ DMG: Note there are two differences: The obvious is the SQL-% instead of the wildcard * we are all used to. But there is also accent vs vertical apostrophe.
.
Sometimes I use LIKE :Text with a parameter, so I can decide to enter %pork%loin% or pork%

[Villeroy]

Base is a tiny addition to this office suite. It is not a database development suite.
Connect a Base document to your existing Access-DB in order to use your Access-DB in the context of this office suite. Continue editing with MS Access.

[DMG]

@Ratslinger
Using the Form-Based Filter (instead of the “Find Records” function) FIXED the lag problem… no delays! I have a huge 50" monitor, so I also LOVE being able to view the “Data Source as a Table” above the form to see the entire result set.
.
Thanks for correcting the LIKE syntax to % (instead of *). It still didn’t work at first but, after reading @Wanderer 's post, I realized that my keyboard was typing the curly apostrophe instead of the “straight” version, so I fixed it. Works like a charm.

[DMG]

@Wanderer THANK YOU for the tip about the different versions of the apostrophe… I would not have figured that out in a million years!! My keyboard was, of course, typing the wrong one… but I found a way to fix that in AutoCorrect Options.

btw @Wanderer … does it also matter which type of double quotes are used around the field names in SQL??

[Ratslinger]

@DMG
Also * can be used to look for any character in a position such as:
.
Like ‘%R*ce%’
.
will find Rice, Race or Ryce.

[DMG]

Good to know, @Villeroy ! Yes… I was very used to Access, but I’ve invested several months of learning time into Base, but haven’t yet scratched the surface. I hope I don’t have to go crawling back to Microsoft. My problem is that I know enough to “try” things, but don’t know enough to get out of mess without a lot of time and help. Ha! Ha!
.
For example, it took me 30 minutes this morning to figure out how to change the “curly” apostrophe on my keyboard (which will not work in form-based filter) to the “straight” apostrophe. But I finally ran across your instructions (in another post) for changing the default in AutoCorrect Options… so THANK YOU!

[Wanderer]

@DMG

does it also matter which type of double quotes are used

Yes! Fancy quotes are for print only. Programming languages either use straight quotes " or ’ but can also give special functions to other chars. For example this site uses backticks ``` for blocks of code.

Can typographic quotes be switched off for ask.libreoffice.org?

I saw some code on this site, where typographic quotes replaced the original quotes. Example far down in this question: It contains the following block: SELECT * FROM “tArtist_Song” ORDER BY ( SELECT “Song_Title” FROM “tSongs” WHERE “ID” = “tArtist_Song”.“SongID” ) As this usually breaks the code, you can’t copy/ paste the code back. While I recommend to read anything before you paste, I don’t think it is useful on this site to have this replacement. I am assuming this smart replacement is…

[Villeroy]

@DMG

For example, it took me 30 minutes this morning to figure out how to change the “curly” apostrophe on my keyboard (which will not work in form-based filter) to the “straight” apostrophe. But I finally ran across your instructions (in another post) for changing the default in AutoCorrect Options…

Your keyboard sends the straight quotes as you type them. If you type into Writer, the program assumes human language and replaces straight quotes with typographic ones. Regarding code, any plain text editor with syntax highlighting does a better job than Writer.

[UnklDonald]

Try a parameter query something like

SELECT * FROM "YourTableName" WHERE LOCATE( :P, "fldRecipeName" ) > 0

Of course you can change the parameter label to something besides P. I use short labels like that because I normally use these queries in conjunction with a filter table and short labels are easier to type when setting up the Link.