Cannot access my database file!

hover2gb · August 24, 2012, 6:32pm

I am a very basic database user - not too advanced.

I downloaded LibreOffice a few days ago and created a basic database file.
It simply consisted of a single table with approximately 20 columns, a
couple of basic queries and a basic entry form.

Today I added an "image" column to the table and a corresponding "image"
field to the form.

It appeared to work fine, but then I closed the form, which In had already
done a couple of times, and from that point on I receive an error and cannot
access anything.

I can close LibreOffice and open it. I can open the ODB file I created, but
if I attempt to even click on the Tables icon or if I attempt to open any of
my queries or the form, I receive the following error: "The connection to
the data source "DW Sponsorships Database" could not be established. Out
of Memory".

If I click on "More," I see the following additional info: "SQL Status:
S1000 Error code: -72 Out of Memory"

It is as if the Table has disappeared (probably corrupted).

This is a work file that my boss is expecting me to use TODAY - ouch!

Can anyone help?

Thanks

elderdanlewis · August 24, 2012, 6:58pm

hover2gb wrote:

I am a very basic database user - not too advanced.

I downloaded LibreOffice a few days ago and created a basic database file.
It simply consisted of a single table with approximately 20 columns, a
couple of basic queries and a basic entry form.

Today I added an "image" column to the table and a corresponding "image"
field to the form.

It appeared to work fine, but then I closed the form, which In had already
done a couple of times, and from that point on I receive an error and cannot
access anything.

I can close LibreOffice and open it. I can open the ODB file I created, but
if I attempt to even click on the Tables icon or if I attempt to open any of
my queries or the form, I receive the following error: "The connection to
the data source "DW Sponsorships Database" could not be established. Out
of Memory".

If I click on "More," I see the following additional info: "SQL Status:
S1000 Error code: -72 Out of Memory"

It is as if the Table has disappeared (probably corrupted).

This is a work file that my boss is expecting me to use TODAY - ouch!

Can anyone help?

Thanks

      You need to make some changes in how LO uses memory:
Tools -> Options -> LibreOffice -> Memory.
      First change Number of steps to 20.
      Change the Graphics cache for LO to at least 256 MB, much more if you have some large images.
      Change the memory per object to more than the largest image you have. 1 MB should work if all the images are less than 1 MB.
      Change the remove from memory to at least 1 hour (1:00)
      You may need to change the Number of objects as well.

--Dan

hover2gb · August 24, 2012, 7:28pm

Dan,

Thanks. I have just attempted to do everything you mentioned, but it does
not seem to help.

For what it's worth, I forgot to mention that I am on a MAC, so I access the
settings through the Preferences menu, but everything appears the same once
inside.

I changed all settings to your recommendations, but still received the
error. I closed and restarted LO, but still the error. I then altered the
various settings up and down but still received the error in all instances.

I would like to understand what has happened, so I can access the DB and use
images, but if I could at least delete the images (don't know if that is
really the problem or not), maybe I could then use the file.

-Greg

hover2gb · August 24, 2012, 7:35pm

It may help for me to mention that I only added 2 images to the DB before I
began having issues. And the images were 1.7MB and 7 MB, so only a total of
8.7MB. I wouldn't think this should create any kind of memory issue

-Greg

Mark_Stanton · August 25, 2012, 9:45pm

I suspect Dan is saying that memory per object needs to be bigger
than the biggest picture you've got in there.

For future reference, it's not a good idea to store big binary data,
or, imnvho, any binary data, in database tables, certainly not in the
hsqldb tables that LO uses as "internal". Store names of files not
the files themselves. You can get trouser pockets big enough for
beach balls, but it's not a great idea.

Regards
Mark Stanton
One small step for mankind...

iplaw67 · August 25, 2012, 10:59pm

Hi,

It may help for me to mention that I only added 2 images to the DB before I
began having issues. And the images were 1.7MB and 7 MB, so only a total of
8.7MB. I wouldn't think this should create any kind of memory issue

No, this shouldn't have corrupted your database on its own, however, I
fear you have fallen victim to a bug in the 3.6.0.4 version of
LibreOffice which corrupts ODB files after they have been opened in this
version and then removes part of the file when that version of LO is
closed or the file is saved.

Alex

Villeroy · August 26, 2012, 7:14am

You must not embed images in your embedded database. Any form with a picture control will try to load the whole field. I think this is the true bug in this affair and it was sitting there ever since.
Use a text field with relative links to picture files.
Bind a form's picture control to the text field. The pictures will be loaded one by one.

hover2gb · August 27, 2012, 2:34pm

Andreas,

Thanks for the info. I believe you are correct, but I do not know what to
do now. I am not able to open the table, form or anything to remove the
embedded image. Can you help with this?

-Greg

hover2gb · August 27, 2012, 2:57pm

<For future reference, it's not a good idea to store big binary data,
<or, imnvho, any binary data, in database tables, certainly not in the
<hsqldb tables that LO uses as "internal". Store names of files not
<the files themselves. You can get trouser pockets big enough for
<beach balls, but it's not a great idea.

<Mark Stanton

Mark,

I have definitely learned my lesson. Is there a way I can access the
database table or form to remove the images? I am not able to get anything
to open at all once the DB is opened.

Thanks,

-Greg

Villeroy · August 27, 2012, 3:49pm

Am 27.08.2012 16:34, hover2gb wrote:

Andreas,

<You must not embed images in your embedded database. Any form with a
<picture control will try to load the whole field. I think this is the
<true bug in this affair and it was sitting there ever since.
<Use a text field with relative links to picture files.
<Bind a form's picture control to the text field. The pictures will be
<loaded one by one. >

Thanks for the info. I believe you are correct, but I do not know what to
do now. I am not able to open the table, form or anything to remove the
embedded image. Can you help with this?

-Greg

--
View this message in context: http://nabble.documentfoundation.org/Cannot-access-my-database-file-tp4003433p4003841.html
Sent from the Users mailing list archive at Nabble.com.

Try Tools>SQL...
ALTER TABLE "TableName" DROP COLUMN "Pics";
SHUTDOWN COMPACT;
Execute, close and reopen the database document.

If this does not help, You can extract the script file out of the document archive, remove all references to that picture column and rebuild the database from script.

hover2gb · August 27, 2012, 4:13pm

Try Tools>SQL...
ALTER TABLE "TableName" DROP COLUMN "Pics";
SHUTDOWN COMPACT;
Execute, close and reopen the database document.

If this does not help, You can extract the script file out of the
document archive, remove all references to that picture column and
rebuild the database from script.

OK. I cannot even access the Tools/SQL menu without receiving the original
error. So I extracted the script file and removed the "Image" references.
Then I can save the script file as whatever I want to call it. But at this
point I do not know how to "rebuild the database from script" as you
mentioned above.

Can you describe the process?

Thanks,

hover2gb · August 27, 2012, 4:59pm

SOLVED - I figured out how to modify the script and then just archive it back
into the database with a ZIP program. And it worked. I now have access to
all of my data, and everything works perfectly.

Thanks, thanks, THANKS!

elderdanlewis · August 27, 2012, 5:03pm

hover2gb wrote:

Try Tools>SQL...
ALTER TABLE "TableName" DROP COLUMN "Pics";
SHUTDOWN COMPACT;
Execute, close and reopen the database document.

If this does not help, You can extract the script file out of the
document archive, remove all references to that picture column and
rebuild the database from script.

OK. I cannot even access the Tools/SQL menu without receiving the original
error. So I extracted the script file and removed the "Image" references.
Then I can save the script file as whatever I want to call it. But at this
point I do not know how to "rebuild the database from script" as you
mentioned above.

Can you describe the process?

Thanks,

CREATE CACHED TABLE "Table1"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, "Pictures" LONGVARBINARY, "Words" VARCHAR(100), "Slang" VARCHAR(75))

After removing the image field, the SQL becomes:

CREATE CACHED TABLE "Table1"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, "Words" VARCHAR(100), "Slang" VARCHAR(75))

      This is an example of the SQL for defining a table in the script file. The field containing the images is "Pictures", and its field type is LONGVARBINARY. Both of these should be removed from the SQL statement. Remember that this is a list separated by commas. You might need to remove a comma to maintain the list structure. If there is a right parenthesis following it, leave it in place.
      If you have used Archive Manager (file roller) to extract the script file, you can modify the script file without extracting the file.
      Hopefully you have already made a copy of the database file and are working with the copy rather than the original. Change the extension of the copy from .odb to .zip. Use Nautilus to browse to the location of the .zip file. Double click it to open file roller. Then double click the database folder in the file roller window. Double click the script file to open it in gedit. Remove the field and its field type and a comma if it follows "LONGVARBINARY". Save the file in gedit. When file roller pops up a window about updating the scrip file, click the Update button. You have now changed the database file. Rename the extension to .odb from .zip. Then open this database file to see if it is working properly.

--Dan

Villeroy · August 27, 2012, 7:27pm

Am 27.08.2012 18:59, hover2gb wrote:

SOLVED - I figured out how to modify the script and then just archive it back
into the database with a ZIP program. And it worked. I now have access to
all of my data, and everything works perfectly.

Thanks, thanks, THANKS!

--
View this message in context: http://nabble.documentfoundation.org/Cannot-access-my-database-file-tp4003433p4003865.html
Sent from the Users mailing list archive at Nabble.com.

Well done. You did all that with a copy of the original file, didn't you?