[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 o...@openoffice.org Thu Jan 6 12:00:10 + 2011 --- JFI: The default value which you enter in the table design window is different to the default value you define in the SQL statement. The default value in the table design defines the value which should be shown in the control when you want to insert new values. - 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 in, an error is thrown. As long as a value is
entered, the record will be created properly. (See form sqlTable Both
[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 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 none. - 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 User r4zoli changed the following: What|Old value |New value CC|''|'r4zoli' Assigned to|dbaneedsconfirm |oj Ever confirmed| |1 Status|UNCONFIRMED |NEW OS/Version|Mac OS X |All Platform|Macintosh |All Target milestone|--- |OOo 3.x --- Additional comments from r4z...@openoffice.org Sat Sep 4 12:25:23 + 2010 --- I can confirm it in OOO330m6 on win7, changed OS, platform. I tested more with Villeroy example from: http://user.services.openoffice.org/en/forum/viewtopic.php?f=61t=33816 CREATE TABLE testTable (ID INT PRIMARY KEY, testBool BOOLEAN DEFAULT TRUE NOT NULL); Opened table data view, added data, only into the ID filed. First record inserted default value, I used ID=1. Second record with ID=2 gives error: Error inserting the new record More on error: SQL Status: 23000 Error code: -10 Attempt to insert null into a non-nullable column: column: testBool table: testTable1 in statement [INSERT INTO testTable1 ( ID,testBool) VALUES ( ?,?)] No changes after closing reopening odb file, or restart OOo. Set target, assign developer. - 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 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 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 atjen...@openoffice.org Sat Sep 4 14:47:51 + 2010 --- Created an attachment (id=71557) bog doc example - 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 atjen...@openoffice.org Sat Sep 4 14:46:26
+ 2010 ---
Using Ubuntu 10.04, OOo 3.2 (go-oo) and 3.2.1 (vanilla) I find no difference -
Inserts via direct SQL with a default setting is working.
Inserts via data controls under Base not working.
Open the attached file -
To see that SQL inserts are working open the SQL window and enter
INSERT INTO BOOLS (Alpha) values ('yet another' )
After executing if you open the bools table or form you will see that the
inserted record has the default of TRUE set for the boolean field.
Now open either the table dataview or bools form and try to enter a new record -
you can do so without entering anything in the second boolean field - because
the setting on the datasource is set NOT to check for required fields, since the
boolean field is also defaulted NOT NULL.
-
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 atjen...@openoffice.org Sat Sep 4 14:59:33 + 2010 --- Sorry for splitting posts - last paragraph(s) should be You can not enter record via the dataview without entering explicitly a value for the boolean field, and the controls do not pick up the default value - but that has always been true I think. In the form you can enter a record without touching the boolean field but then the, when you do so the record is set with a FALSE in the boolean field - BUT - this is because the checkbox field on the form is set to be for TRI-STATE = FALSE - with this setting it is not picking up the default setting from the database. If you open that form and change the setting for that control to TRI-STATE equals TRUE then the control will correctly pick up the default value TRUE and you see at runtime that it will not accept the NULL state, since the table does not accept it - try with a field set to default TRUE and don't add the NOT NULL and you will see the difference in the form is picked up. So there really are two issues here - one the checkbox control does not pickup the table default setting when tri-state = FALSE Two - the checkbox control in the dataview grid is not tri-state = TRUE At least that's how it looks to me. - 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 User atjensen changed the following: What|Old value |New value CC|'r4zoli' |'atjensen,r4zoli' - 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 ville...@openoffice.org Sat Sep 4 19:38:59 + 2010 --- In this context I discovered that any field type except Boolean can be defined as DEFAULT NULL NOT NULL. The grids and the forms will leave the field blank and Base will complain one way or the other when the user tries to store without filling the empty field. Try this with a boolean field. For a mandatory boolean field (NOT NULL) Base will always put a false False into the grid or form control respectively. - 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 atjen...@openoffice.org Sun Sep 5 03:18:34 + 2010 --- @villeroy - I can't do that here - using 3.2.1 (vanilla) on linux if I try to apply, using the SQL window, your example: ALTER TABLE BOOLS ADD COLUMN ABOOLS BOOLEAN DEFAULT NULL NOT NULL I get this result: 1: Attempt to insert null into a non-nullable column: column: ABOOLS table: BOOLS in statement [ALTER TABLE BOOLS ADD COLUMN ABOOLS BOOLEAN DEFAULT NULL NOT NULL] Which is correct. In fact here the only statement it will take, without a default setting is: ALTER TABLE BOOLS ADD ABOOL BOOLEAN And then yup your right - thee is no way to get a null with the form controls - or display a null atually it seems - attached another file - 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 atjen...@openoffice.org Sun Sep 5 03:21:36 + 2010 --- Created an attachment (id=71564) changed boolean field - and form control is set to tri-state true - 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
