As my database is growing I’m plagued with the database being reported as corrupt when opening the file. Letting BASE fix it destroys the file. I’ve been left with created multiple backup copies. What would cause this type of behavior? there are only 84 records on the database, each record has about 50 items. Running on Win 7 64-bit machine.
Hard to say for sure, but IMHO, the error reporting for SQL errors is, shall we say, primitive at best. SQL errors with simple syntax errors can throw “corrupt database” errors. This is very misleading.
So before you assume your data is corrupt, drill down on your SQL and simplify it and see if you can find what might be wrong with it.
I have moved to connecting w/ MariaDB rather than using the Embedded HSQLDB for most things. While the error messages are still just as problematic, it becomes easier to figure out what might be wrong because you can use external tools like HeidiSQL or phpMyAdmin to see if your data is ok.
Edit: One other thought concerning database corruption in general, and one way database integrity can be maintained…
Some years back I used btrieve for several projects. It had a great logging feature, that would log transactions. If something crashed and the database was damaged, you could start with an older backup version and roll the transaction log forward to recreate the data and indexes.
I’m sure there must be the same w/ MariaDB but have not used it.
Base crashes can happen for a variety of reasons. Most often it is some bad macro code. However based upon what you wrote, I don’t think this is the problem. Embedded DB suffer when zipped/unzipped especially when containing certain data. This leads to corruption. You can easily duplicate the problem by creating a DB using pictures. Instead of linking to the pictures, store them as a BLOB type. The first couple go OK. Get to about 5 and you have problems.
In your case it may be because of the amount of fields/data in a single record. No way to tell from here but the best bet for you right now is to get away from the embedded DB. This can easily be done by converting to a ‘split’ DB. In this scenario, the data is removed from the zipped .odb file and potential problems are eased. In the case just mentioned, the same Base file split containing BLOB’s had no further problem.
As an additional benefit, when moving to a split DB the actual DB is upgraded and provides additional benefits especially in the area of SQL.
See this post for tools & instructions to generate a split DB - click here.
I do have photos in the database, I’m not sure if you were implying having photos in the database would be an issue or not. I didn’t have issues until I started adding photos so perhaps that is the real issue. In any case I did down load split took a quick look at it. There doesn’t seem to be a way to import my database to use split. I have several hours of dev work on the my current database and would rather not start from scratch.
There are multiple ways of doing this. I can usually do the following in 10-15 minutes time. Use a backup copy of your .odb (seems you have experience in this). Now create a split DB with mentioned method in post. Open new split DB & old DB. From main screen of each. Old DB Tables section, right click first table & select Copy. New DB, blank area of Tables section, right click & select Paste. Do this for each item in each section. At most table relations may need to be reestablished.
This will of course leave you old embedded DB unscathed. Also, if the photos are Blob objects, that is certainly a possibility. If they are links not so much. Links may be preferable since they don’t bloat the DB. The loading of each can be quite different depending upon the DB used (MySQL in my case).
I migrated my base to split. For the most part it wall worked except for photos. Even though there are links, when I create a report the photos never show up. I did an a/b comparison to the old version and the split version and all seems the same but the split version reports don’t seem to link photos. In my case this is a membership list with photos of members, these are the one that don’t appear… pictures (Icons/logos and such do appear in the report however…
I need further clarification first. It is still not clear as to how you are storing “photos” in the DB. Are they stored within the DB as Blob’s or is it a file location such as “C:\MyPhotos…”? Your last comment suggests it is a file location. Also this is the first time you are mentioning printing. Did this work prior to converting to split?
Since you haven’t responded yet I’ll add to the question. The Icons/logos which do appear in the report, what field type are they in the table definition? This is addition to the member photo type asked for already.
When I add the photo to a persons record I point to a file location. This worked perfectly (except for the occasional db crash prior to split). So to answer the question, the picture is a file location. Images and icons ARE stored in the database and they do show up post and pre split. For example our club ICON shows up in the report at the top of each page I print. The photo of each member however doesn’t show up in the report, but did in the pre-split version.
First, I hope you crashes have stopped. Next as far as the printing, since my last comment I have tried this at lease half a dozen times without fail using links. I have tried this with HSQL v1.8, 2.2.x & 2.3.4 (all split) so they all work. The only thing I can conceive is that the links are not being found in the expected location, possibly because of new location of DB and relative location of actual photos… You would have even move problems if the pictures were stored as Blobs.
These are all jpg pictures… in the original db the data type weren’t blobs but in split that’s all they can be…
Progress… I gave up on just using the imported database/forms/reports. I’m now using all the fields without pictures intact from the original non-split database/forms/reports, just copied and pasted to the new split database. For the photo portion of the record I added a new field to the table and made this a text(varchar) field (the imported photo field was a BLOB and that couldn’t be edited … actually no imported fields could be edited). With the text(varchar) photo field all works
well. My reports have the photos showing up . Obviously there is something up with BLOB fields in when having photos in them. The form worked fine but reports didn’t. I think I’m on my way now.
Glad to hear it is working. Your original field type was probably “LONGVARBINARY” which is Blob in v1.8 and this is the cause of crashes. You are still vulnerable to problems with the embedded DB. The split is much better. Table fields can be added or deleted without a problem but changes needed to be made through SQL. The problem with printing Blobs in Report Builder is a bug but works with other sources such as MySQL.