jw@pan.UUCP (Jamie Watson) (09/16/88)
Given a database with a field declared as char(1), and a form to input that field with the following specifications: include = ("Y","N"," "), default = " " When a 4gl program does input with this form, the default value is not accepted. That means that attempting to terminate input produces the message "This value is not among the valid possibilities". Using the *identical* form with the Informix-SQL Perform form processor everything works correctly. In fact what is apparently happening is that the field is getting a null value by default, instead of a space, because adding null to the include list causes the form to accept the default value under 4gl. jw
brianc@daedalus (Brian Colfer) (09/19/88)
In article <473@pan.UUCP> jw@pan.UUCP (Jamie Watson) writes: > >Using the *identical* form with the Informix-SQL Perform form processor >everything works correctly. There are a number of important differences between SQL and 4GL forms... > >In fact what is apparently happening is that the field is getting a null >value by default, instead of a space, because adding null to the include >list causes the form to accept the default value under 4gl. I think you're right about this being a problem, that is, always converting a char column with only spaces to NULL. It is obvious why Informix did this but it would be nice to override the effect. I wonder what the WITHOUT NULL INPUT feature described on page R3-26. I now do ALL my data checking and defaulting in my INPUT section to be consistent and to gain the option for finer control. For thoes unfamiliar with 4gl this is what I mean: Form testing method: # screen . . . f1 = table_test.col_one, include= ("Y","N"," "), default= " ", upshift; #or . . . f1 = table_test.col_one, include= ("Y","N",NULL), upshift; # Null is not needed because the database defaults empty columns # to NULL anyways ------- # 4gl modual . . . INPUT BY NAME table_test.* ------------------------------------------- I would do the following: # screen . . . f1 = table_test.col_one,upshift; -------- INPUT BY NAME table_test.* AFTER col_one If ( pr_tbl_test.col_one != "Y" OR pr_tbl_test.col_one != "N" OR pr_tbl_test.col_one IS NOT NULL ) THEN ERROR " Not a valid value: Please enter Y, N or leave blank" END IF #and if you want col_one to be " " and not a NULL If ( pr_tbl_test.col_one IS NULL ) THEN LET pr_tbl_test.col_one = " " END IF END INPUT Now use pr_tbl_test.col_one as the value for your insert statement. Hope this helps... =============================================================================== Brian : UC San Francisco :...!{ucbvax,uunet}!daedalus.ucsf.edu!brianc Colfer : Dept. of Lab. Medicine : brianc@daedalus.ucsf.edu : PH. 415-476-2325 : BRIANC@UCSFCCA.BITNET ===============================================================================
aland@infmx.UUCP (Dr. Scump) (09/24/88)
The following is my own personal followup and does not represent the position of Informix. This and subsequent followups are in defense of certain personal attacks. In article <473@pan.UUCP>, jw@pan.UUCP (Jamie Watson) writes: > > Given a database with a field declared as char(1), and a form to input > that field with the following specifications: > > include = ("Y","N"," "), default = " " > > When a 4gl program does input with this form, the default value is not > accepted. That means that attempting to terminate input produces the > message "This value is not among the valid possibilities". A default value of " " (space) is properly accepted iff the column is defined NOT NULL and WITHOUT NULL INPUT is used in the form header. The inability to control this behavior on a field-by-field basis is a deficiency, recorded as a bug (#2279); e.g., you cannot have a field with nothing in it interpreted as space (vs. NULL) unless you use WITHOUT NULL INPUT on the form as a whole in the current release. Using the above combination causes "non-values" to be treated as blanks, not nulls. Otherwise, they are nulls. > Using the *identical* form with the Informix-SQL Perform form processor > everything works correctly. Not true. [E1] SPERFORM and 4GL hold the same rules. I just verified this in the current release (2.10.03 / 1.10.03). The only way that this could "work correctly" in Perform is if you have WITHOUT NULL INPUT and a NOT NULL column. If you had the same in 4GL, the behavior would be the same. > In fact what is apparently happening is that the field is getting a null > value by default, instead of a space, because adding null to the include > list causes the form to accept the default value under 4gl. Also not true. [E2] The default of <space> is not "being accepted"; the input variable value is NULL, not space. By adding NULL to the INCLUDE list you are allowing the NULL to come through. Try the following sample: (sorry for the bandwidth, but I felt an example was important to reduce disinformation): -------------------------------------------------------------------- {schema} create database jw; create table tab2 (col1 integer, col2 char(1), col3 char(1) NOT NULL); {jw14.per} database jw without null input screen { col1 [f000 ] col2 [a] col3 [b] } end tables tab2 attributes f000 = tab2.col1; a = tab2.col2, include = ("Y", "N", " ", NULL), default = " "; b = tab2.col3, include = ("Y", "N", " "), default = " "; end {jw1.4gl} database jw main define r1 record like tab2.*, c2s char(5), c3s char(5) open form jw1 from "jw14" display form jw1 input by name r1.* if r1.col2 is null then let c2s = "NULL" else let c2s = "BLANK" end if if r1.col3 is null then let c3s = "NULL" else let c3s = "BLANK" end if message "col 2 is ", c2s, " col 3 is ", c3s sleep 2 end main -------------------------------------------------------------------- To verify that Perform does the same thing: Using the same form (recompile in ISQL) add a row, spacing through both col2 and col3. Using RDSQL, select * from tab2 where col2 IS NULL -- you will see the row. Select where col3 IS NULL -- you will not see the row. Col3 gets <space> because it was defined NOT NULL and WITHOUT NULL INPUT was used in the form. > jw -- Alan S. Denney | Informix Software, Inc. | {pyramid|uunet}!infmx!aland Disclaimer: These opinions are mine alone. If I am caught or killed, the secretary will disavow any knowledge of my actions. Santos' 4th Law: "Anything worth fighting for is worth fighting *dirty* for"
jw@pan.UUCP (Jamie Watson) (09/30/88)
In article <462@infmx.UUCP> aland@infmx.UUCP (Dr. Scump) writes: >> Using the *identical* form with the Informix-SQL Perform form processor >> everything works correctly. > >Not true. [E1] SPERFORM and 4GL hold the same rules. Well, I have a form here that can be run with 4gl, and fails, and can be run with SQL, and works. It would appear that regardless of whether Informix *thinks* or *claims* they "hold the same rules", they don't. >> In fact what is apparently happening is that the field is getting a null ^^^^^^^^^^^^^^^^^ >> value by default, instead of a space, because adding null to the include ^^^^^^^^^^^^^^^^ >> list causes the form to accept the default value under 4gl. > >Also not true. [E2] The default of <space> is not "being accepted"; >the input variable value is NULL, not space. By adding NULL to the >INCLUDE list you are allowing the NULL to come through. Which is *EXACTLY* what I said was happening. >Try the >following sample: (sorry for the bandwidth, but I felt an example >was important to reduce disinformation): So far, I haven't seen *any* "disinformation" in MY posting... jw
aland@infmx.UUCP (Dr. Scump) (10/06/88)
In article <487@pan.UUCP>, jw@pan.UUCP (Jamie Watson) writes: | In article <462@infmx.UUCP> aland@infmx.UUCP (Dr. Scump) writes: | >> (complaint that I4GL and Perform handle null/blank | >> defaults differently) | >> Using the *identical* form with the Informix-SQL Perform form processor | >> everything works correctly. | > | >Not true. [E1] SPERFORM and 4GL hold the same rules. | | Well, I have a form here that can be run with 4gl, and fails, and can | be run with SQL, and works. It would appear that regardless of whether | Informix *thinks* or *claims* they "hold the same rules", they don't. | I honestly cannot reproduce this. The example that I posted did the functions you described and worked fine for me. If you email me your example, I will check it out. | >Try the | >following sample: (sorry for the bandwidth, but I felt an example | >was important to reduce disinformation): | | So far, I haven't seen *any* "disinformation" in MY posting... THAT figures. :-] Seriously though, did you at least *try* my example on your system? What were the results? | | jw -- Alan S. Denney | Informix Software, Inc. | {pyramid|uunet}!infmx!aland Disclaimer: These opinions are mine alone. If I am caught or killed, the secretary will disavow any knowledge of my actions. Santos' 4th Law: "Anything worth fighting for is worth fighting *dirty* for"