To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=114304
------- Additional comments from anothera...@openoffice.org Mon Sep 6 02:27:55
+0000 2010 -------
I was going to start doing some additional testing on Boolean fields, but wanted
to get a baseline with text fields first and discovered something interesting.
It looks like the defaults that are set in the Table Design GUI and those that
are defined via SQL don't talk to each other properly.
(Note: I'm attaching a file in the next comment that contains the following
examples for reference.)
##################################################
# Scenario 1
##################################################
If I create a new table (named "guiTable") via the "Create Table in Design
View..." GUI with the following:
Field 1 Name: pKey (set to primary key)
Field 1 Type: INTEGER
Field 1 AutoValue: Yes
Field 2 Name: notNullText
Field 2 Type: VARCHAR
Field 2 Entry required: Yes
Field 2 Default Value: DefaultText
Field 3 Name: nullableText
Field 3 Type: VARCHAR
Field 3 Entry required: No
(No Default value set)
I get the following results:
---
Table Data View:
As soon as I open the table grid in the Table Data View GUI, I see the
"DefaultText" already populated in the "notNullText" field where the next record
will go. As soon as one record is created, once again, the "DefaultText" shows
up in "notNullText". (Or, at a minimum, as soon as I start entering text into
the "nullableText" field the "notNullText" auto populates with "DefaultText").
Entering some text into "nullableText" and not messing with "notNullText" allows
for entries to be created with no issue.
---
SQL Insert Test 1 using the "Tools->SQL..." menu option to run:
INSERT INTO "guiTable" ("nullableText") VALUES ('SQL addition');
I get the error:
1: Attempt to insert null into a non-nullable column: column: notNullText table:
guiTable
---
SQL Insert Test 2 using the "Tools->SQL..." menu option to run:
INSERT INTO "guiTable" ("notNullText", "nullableText") VALUES ('SQL addition',
'SQL addition');
The command executes without error.
---
Form Test 1 - Only contains "nullableText" field.
I can add new records with no issue. Everything works as expected. Examining the
table in data view, each new entry has the requested "nullableText" and the
"notNullText" has the proper "DefaultText" string. (See form "guiTable Only
Nullable" in "aws-null-test-v2.odb" attached in the next comment.)
---
Form Test 2 - Contains both "notNullText" and "nullableText" fields.
As soon as the form is open to create a new record, the "notNullText" field
displays the "DefaultText" as specified via the Default Vaule in the GUI when
the table was created. Entering new records works without issue. It's possible
to raise an error by deleting the text from the "notNullText" field, but that is
expected. (See form "guiTable Both Fields" in "aws-null-test-v2.odb" attached in
the next comment.)
##################################################
# Scenario 2
##################################################
If I create an identical table (named "sqlTable") via SQL using the
"Tools->SQL..." menu option instead of the GUI. The SQL command for the talbe
creation is:
CREATE TABLE "sqlTable" (
"pKey" INTEGER NOT NULL IDENTITY PRIMARY KEY,
"notNullText" VARCHAR(50) DEFAULT 'DefaultText' NOT NULL,
"nullableText" VARCHAR(50)
);
I get the following results:
---
Table Data View:
Unlike the guiTable, when the table is opened under the Table Data View GUI, the
"notNullText" field *do not* have the "DefaultText" value already in place for
the next record that will be created.
I have to enter text into the "nullableText" field and then hit tab or return to
create the record before the "DefaultText" shows up.
I don't have any issue with entering data, even though it behaves differently.
---
SQL Insert Test 1 using the "Tools->SQL..." menu option to run:
INSERT INTO "sqlTable" ("nullableText") VALUES ('SQL addition');
The command executes without error. Even though no value was defined for the
"notNullText" field, when examining the table data directly, the "DefaultText"
value shows up as expected.
---
SQL Insert Test 2 using the "Tools->SQL..." menu option to run:
INSERT INTO "sqlTable" ("notNullText", "nullableText") VALUES ('SQL addition',
'SQL addition');
The command executes without error.
---
Form Test 1 - Only contains "nullableText" field.
I can add new records with no issue. Eveyrthing works as expected. Examining the
table in data view, each new entry has the requested "nullableText" and the
"notNullText" has the proper "DefaultText" string. (See form "sqlTable Only
Nullable" in "aws-null-test-v2.odb" attached in the next comment.)
---
Form Test 2 - Contains both "notNullText" and "nullableText" fields.
When the form is opened the "notNullText" field is empty. If you try to create a
new record without filling it in, an error is thrown. As long as a value is
entered, the record will be created properly. (See form " sqlTable Both Fields"
in "aws-null-test-v2.odb" attached in the next comment.)
##################################################
Comparison
##################################################
Here's a brief overview of the differences I see between the two types of
tables.
---
Table Data View:
In both scenarios, the "DefaultText" works properly when creating new records in
the Table Data View GUI, but they don't work the same. With a table that is
created with the "Create Table in Design View..." GUI the "DefaultText" appears
to get created before sending the request to the underlying database. In
contrast, the table created directly via SQL appears to populate the
"DefaultText" during/after record creation.
---
SQL Inserts:
For a table that is created via the GUI, it's not possible to send SQL without
an value for a field that is defined as require even if the GUI has a Default
value defined in it. Doing so would results in an error. I think this means that
it's not possible to use SQL to insert a new record that contains a default
value that was defined in the GUI. This strikes me as a pretty big issue if you
are expecting to be able to interact with the database via SQL commands.
In contrast, if SQL is used to define a default value, it is not necessary to
explicitly create a value for that field in the SQL command. This is the way I
would have expected both cases to work.
---
Forms:
The difference I see here is that a DEFAULT value that is defined via SQL does
not propagate to the form itself. Similar to before, this appears to mean that
you can't define a default via SQL that can be used by the GUI.
##################################################
# Conclusion
##################################################
>From what I see here, it looks like there are two mechanisms that deal with
default values for fields and they don't fully interact. Specifically, defaults
that are defined in the GUI and those that are defined via SQL are largely
invisible to each other. For example, if you define a default value in SQL for a
text field. This value does not show up in the Table Design View GUI. Likewise,
if you set a default value in the GUI, direct SQL commands cannot rely on it
when inserting data.
The examples here were all based on text fields, but based on what I've seen,
this is the same root cause behind the original issue I had with the Boolean
values.
This seems like a much larger issue than the symptom that originally sparked
creation of this ticket. The question becomes, should that issue be addressed in
this ticket, or should a new one be created?
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org
For additional commands, e-mail: issues-h...@dba.openoffice.org
