[dba-issues] [Issue 114304] Default not being set pro perly when creating a boolean field via SQL
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:30:38 + 2010 --- Created an attachment (id=71576) aws-null-test-v2.odb - Demonstrates issues between default values defined in SQL and those defined in the GUI - 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
[dba-issues] [Issue 114304] Default not being set pro perly when creating a boolean field via SQL
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
+ 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
[dba-issues] [Issue 114304] Default not being set pro perly when creating a boolean field via SQL
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 Sat Sep 4 14:41:58 + 2010 --- Based off the things Villeroy saw, I've opened another issue (http://www.openoffice.org/issues/show_bug.cgi?id=114309) that might be related to this one where Boolean fields created with NOT NULL that do not have a default value try to send NULL values when records are added. - 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
[dba-issues] [Issue 114309] NULL Boolean values being sent when NOT NULL specified in SQL Create Ta ble
To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=114309
Issue #|114309
Summary|NULL Boolean values being sent when NOT NULL specified
| in SQL Create Table
Component|Database access
Version|OOo 3.2.1
Platform|Macintosh
URL|
OS/Version|Mac OS X
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P3
Subcomponent|none
Assigned to|dbaneedsconfirm
Reported by|anotheralan
--- Additional comments from anothera...@openoffice.org Sat Sep 4 14:36:52
+ 2010 ---
If a table is created directly from SQL with a Boolean field that is set to NOT
NULL, errors arise when trying to insert new records where no changes are made
to the field.
(Note: there is a related bug
"http://www.openoffice.org/issues/show_bug.cgi?id=114304"; where DEFAULT values
for booleans are not being set properly, but this addresses a case where no
Default value is defined, but NOT NULL is. According to the HSQLDB docs
"http://hsqldb.org/doc/guide/ch09.html#create_table-section"; this should be a
valid definition).
Editing the table, the boolean field initially appears in an unchecked/FALSE/No
state, but if no change is made to it, a NULL value is sent when attempting to
create the record. This causes an error as described below.
For a walkthru example:
>From the Tools->SQL... Menu, run the following to create a new table with a
boolean value set to NOT NULL:
CREATE TABLE "testTable" ("pKey" INTEGER IDENTITY PRIMARY KEY, "testBool"
BOOLEAN NOT NULL, "testText" VARCHAR(50));
Run the View->Refresh Tables option to see the update table.
In the "Tables" view, double click on the "testTable" to open it directly.
The "testBool" field shows up as unchecked/FALSE.
Enter some text in the "testText" field and hit return or tab to add the
record.
An error occurs saying:
Error inserting the new record
Attempt to insert null into a non-nullable column: column: testBool table:
testTable in statement [INSERT INTO "testTable" ("testBool", "testText") VALUES
(?,?)]
Toggle the testBool to checked/TRUE and then back to unchecked/FALSE and try to
insert the record again and it works as expected.
-
Opening the testTable in edit mode, the GUI presents the default value for
"testBool" as "No". However, this does not correspond with the behavior seen
above. If the Default value in the GUI is toggled to "Yes" and then back to "No"
and saved, data entry directly into the table as described above works as
expected with no errors. (Note, you may have to click out of the Default value
field after changing to "Yes" in order for it to be seen as a change.)
-
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
[dba-issues] [Issue 114304] Default not being set pro perly when creating a boolean field via SQL
To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=114304
Issue #|114304
Summary|Default not being set properly when creating a boolean
| field via SQL
Component|Database access
Version|OOo 3.2.1
Platform|Macintosh
URL|
OS/Version|Mac OS X
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P3
Subcomponent|none
Assigned to|dbaneedsconfirm
Reported by|anotheralan
--- Additional comments from anothera...@openoffice.org Sat Sep 4 11:06:02
+ 2010 ---
Using OpenOffice 3.2.1 (Build: 9502) on Mac OSX 10.6.4.
When attempting to use a direct SQL Create Table statement to set a default
value for a boolean field (via the Tools->SQL menu), the field is created
properly, but the default does not get applied.
Based off the documentation here:
http://hsqldb.org/doc/guide/ch09.html#create_table-section
If this statement is run:
CREATE TABLE "testTable" ("testBool" BOOLEAN DEFAULT TRUE NOT NULL);
a new table should be created that has a "testBool" field the defaults to TRUE
(which would show up as "Yes" in the Base display). However, with the above
statement the default is actual set to FALSE (which shows up as "No" in the Base
display).
(Note: after running an SQL command to create a table, you need to run the
View->Refresh Tables menu option to see new tables.)
It does not appear that the default is being set improperly, but rather that the
default is being ignored all together.
For some further examples, running the following:
CREATE TABLE "testTableB" (
"pKey" INTEGER NOT NULL IDENTITY PRIMARY KEY,
"test01" BOOLEAN DEFAULT TRUE NOT NULL,
"test02" BOOLEAN DEFAULT True NOT NULL,
"test03" BOOLEAN DEFAULT true NOT NULL,
"test04" BOOLEAN DEFAULT 1 NOT NULL,
"test05" BOOLEAN DEFAULT FALSE NOT NULL,
"test06" BOOLEAN DEFAULT False NOT NULL,
"test07" BOOLEAN DEFAULT false NOT NULL,
"test08" BOOLEAN DEFAULT 0 NOT NULL,
"test09" BOOLEAN DEFAULT TRUE,
"test10" BOOLEAN DEFAULT True,
"test11" BOOLEAN DEFAULT true,
"test12" BOOLEAN DEFAULT 1,
"test13" BOOLEAN DEFAULT FALSE,
"test14" BOOLEAN DEFAULT False,
"test15" BOOLEAN DEFAULT false,
"test16" BOOLEAN DEFAULT 0
);
Will produce a table where all the fields with "NOT NULL" end up with a default
value of "No" regardless of if "TRUE" or "FALSE" was requested. Any fields
without the "NOT NULL" spec end up with a default of .
-
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
