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 --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org