Error messages in Base files

[jazzlunatique]

Welcome beginner. What is your question or comment?
Please try to briefly and clearly tell us: What you want, What you tried, and What happened.
-----------------------------------------------------------------------------------------------------------

I am a beginner user in Open Office Base. I am trying to update a database table and add a record. I attempt to add and then save the record and I receive an error message that says "Error inserting the new record." I do not know what this means or what this indicates so that I can correct it. Where can I go to find this out or does anyone know what this means and how I can correct the record.

thank you,

[r4zoli]

Error codes for Base and HSQLDB are weak points of OOo Base, not so much information, near to zero, about it.

Your problem can be connected to missing Primary Key in your database, if your file based on connected text, dBase, mdb(without PK) or Calc file, these read only databases. To avoid this create new odb file and copy tables into new file and create during copy process PKs.

[wizard]

Not necessarily true. I created a new database, and got the same error message, even tho I did have a primary key(created when I got an error message saying I needed one), when I tried to add a new record.

In addition to the above, I had another problem with this database. I had a numeric field with 8 places, but when I tried to enter 200809, it kept changing the 9 to a 7. When I added 2 more '0's, it registered as 20080900, which is acceptable, but annoying.

[arfgh]

I love OO, but it is a know fact that BASE is the OpenOffice weak point.

Even actually with AOO 4.0.1 it is very common to obtain a corrupt .odt database file for unknown causes.
I always have tought that is some issue with the file compression....

Usually when we are on the form editition mode, the .odt file rise so much its own file size, and then... CRAAAAAAASSSSSSSHHHHHH !!!!

... we cant use the database never more.

[RPG]

Hello

I think it is not good to use the database in embedded mode. The same is working good when you have split the database.

read this
viewtopic.php?f=83&t=17567

Edit: and this https://forum.openoffice.org/en/forum/v ... 83&t=61183 and this is for downloading the example file http://www.mediafire.com/?59iq0esx071zcic

DACM did have made an example what makes it easy to make a database in embedded mode.

Romke

[arfgh]

what exactly means to have the db in embedded mode ?

[Hagar Delest]

In embedded mode, the db is inside the .odb file, which is dangerous with AOO, according to the Base gurus here.
Better have your own database and use AOO as a front end.

[arfgh]

is that possible ? how ?

[RPG]

Hello

I have added now two links before this post.

The last link is direct for downloading of an example file of DACM.

I have only one time test the example of DACM. That makes: I find it better then my explanations and for that reason I point to the the post of DACM on the Dutch forum.

Romke

[DACM]

Base is particularly buggy and underdeveloped. Most Base crashes during form or macro development are recoverable, although any unsaved form or macro development work is lost. Saving your front-end development work is apparently a two-step process. You must save the current window followed by the main Base window; in other words you must save the current document followed by saving the Base container file (.odb).

None of this affects your database tables or user-data, unless the associated back-end data files are actively "embedded" within your Base (.odb) file -- otherwise known as an "embedded database." This configuration leads to data-corruption if Base crashes while closing the .odb file. Unfortunately, "embedded databases" are the default in Base.

The user-community offers an alternative to the Base wizards in the form of preset templates, which generate a traditional "split database" for data-safe operation. In this 'split database' configuration, Base can no longer corrupt your data in the event of a crash. You can lose up to 60 seconds of your current session data-input in the event of a Base crash, but the threat of full database corruption has been virtually eliminated. And you can reduce this 60 second timer to zero or a more reasonable 1 second as necessary once you've adopted a split database configuration with Base, but this timer is rarely an issue either way.

You can extract "embedded database" files from your Base (.odb) file for permanent "split database" operation. We have various step-by-step instructions across this forum but here's a tutorial reflecting the latest options/steps:

[Tutorial] Splitting an "embedded database"

Here's more information on a possible fix for the Base default through core-development: http://en.libreofficeforum.org/node/6936
...

[arfgh]

thx so much to explain about that but tell me a thing...
an existant db, is it possible to split to use this safe way to work ?

[DACM]

...is it possible to split...an existent [embedded] db ?

Yes. I've added a tutorial and modified the link above with: [Tutorial] Splitting an "embedded database"
...

[arfgh]

DACM thx so much for the info you give, you cant figure the bad moments i have passed off when base crash.
Anyways, why is actually 'base' underfeveloped in a so long way that has OO ? cant understand the reasson because the other parts of OO works amazing all of them.

[arfgh]

is it also possible to split the forms ?
sometimes when AOO crash, also the forms are affected.

How to split them ?

[MTP]

You can make standalone forms that are saved independently of the Base file. [Tutorial] Standalone Forms / Switchboard

[arfgh]

Ok, and another thing that i cant perform with the splitted database.
I need to modify the datafield types, etc. With the splittled database, base doesnt allow me to do that so, how can i do it now ?

thx in advance

[Villeroy]

<where you installed HSQL>/bin/runManager.bat

[arfgh]

i have used DACM tutorial to split my database. https://forum.openoffice.org/en/forum/v ... 83&t=65980

I think that i havent used a file called /bin/runManager.bat

[DACM]

OVERVIEW: ...[blah, blah]...However, the default 'embedded database' solution remains easier during initial table development due to better GUI support in Base. Conversely, with a 'split database' you can utilize the interactive table-creation tools provided by the Base GUI, but after saving the results you'll need to use SQL or a GUI workaround in order to modify existing field properties...

...


LIMITATIONS: While this template attempts to overcome some glaring deficiencies with Base, it scarcely addresses the holes in the Base GUI with respect to DDL tasks (database schema development) when connected through standard ODBC/JDBC drivers. Specifically, the Base Table Designer exhibits an anomaly when connected to HSQLDB in a split-database configuration. The Table Designer can be used to create Tables and Fields in the normal fashion. But after saving the results, existing field-attributes appear frozen in the Table Designer. So existing attributes can't be modified using this tool. The Table Designer also fails to setup Default field values for most data-types. These deficiencies are limited to the table-design phase, when field-attribute modifications are essential but rare. So when modifying existing table-structures with a split-database of any kind, even novice users should resort to SQL, perhaps utilizing the Base SQL console (Tools > SQL...) or an alternative GUI database manager. However, some GUI workarounds do exist:


Base limitations as a database manager:


The Frozen 'Field Property' Workaround (adjusting existing Table structures with Base including AutoValue Primary Keys and Default values)
Upgrade to HSQLDB 2.3.x (a fix for the AutoValue setup issue in Base)
Alternative SQL database managers (free, cross-platform, cross-database, administration tools)

[arfgh]

DACM, i am a bit lost with all that information.
What i have to try in order to edit the splitted database datafield values ? 'alternative GUI database manager' ?

someone of the follow programs ?

Here's some other free, cross-platform, cross-database, SQL front-ends:
SQL Workbench/J
SQirreL SQL
SQL Developer
Execute Query
iSQl-Viewer
and the amazing, but not free RazorSQL
among others

[Villeroy]

You are not working with Base. You are working with a database software. It is perfectly well documented in a file guide.pdf.

<where you installed HSQL>/bin/runManager.bat

Run that batch program and see.

[arfgh]

Villeroy, i havent installed nothing. I have use the DACM tutorial in order to split my database.

So i dont have that bat file that you say.

[RPG]

Hello

Your problem was:

Ok, and another thing that i cant perform with the splitted database.
I need to modify the datafield types, etc. With the splittled database, base doesnt allow me to do that so, how can i do it now

Study in your SQL manual how to alter table and alter you column.

This can possible be done with the SQL tool in OOo-base.

Romke

[arfgh]

explain a bit more, please.

Anyways exist standalone managers to perfomr the same edition that i can do with base ?

[arfgh]

help

[F3K Total]

As was written to you, you need to use SQL language to modify the database in a split version.
E.g. you want to modify a currently as VARCHAR(50) defined column "Name" in "Table1" to have a lenght of 100 characters, then execute this command via Tools/SQL ...

Code: Select all   Expand viewCollapse view

ALTER TABLE "Table1" ALTER COLUMN "Name" VARCHAR(100)

or shorter

Code: Select all   Expand viewCollapse view

ALTER TABLE "Table1" ALTER "Name" VARCHAR(100)

You can

Code: Select all   Expand viewCollapse view

ALTER TABLE "Table1" ALTER COLUMN "Name" VARCHAR(100)
ALTER TABLE "Table1" DROP COLUMN "Name"
ALTER TABLE "Table1" ADD COLUMN "Name2" VARCHAR(100)
ALTER TABLE "Table1" ADD COLUMN "Name2" VARCHAR(100) before "Name"

to define new columns position in "Table1"
...
HTH R
EDIT: last two commands corrected, thanks to Villeroy.

[Villeroy]

ALTER TABLE "Table1" ADD COLUMN "Name2"

The column type is mandatory:

Code: Select all   Expand viewCollapse view

ALTER TABLE "Table1" ADD COLUMN "Name2" VARCHAR(100)

Code: Select all   Expand viewCollapse view

ALTER TABLE "Table1" ADD COLUMN "Name2" VARCHAR(100) DEFAULT 'My default text'

Code: Select all   Expand viewCollapse view

ALTER TABLE "Table1" ADD COLUMN "Name2" VARCHAR(100) NOT NULL

Code: Select all   Expand viewCollapse view

ALTER TABLE "Table1" ADD COLUMN "Name2" VARCHAR(100) DEFAULT 'My default text' NOT NULL

[arfgh]

i prefer some gui to perform these operations. Exist someone ?

thx in advance


Also, what operations i must perform in order to embed again the splittled database ? Just the reverse operations.

[DACM]

i prefer some gui to perform these operations. Exist someone ?

RazorSQL ($99.95) features extensive graphical SQL tools for HSQLDB, to include an Alter Table GUI.
...

[arfgh]

ok DACM

and some free good solution ?
also, i have tried to inverse the way to embed again the files, and for some reasson i cant edit the database files....

why ?