- Since databases other than Oracle do not coerce empty strings into
- null, this code has different semantics depending on the
- underlying database (i.e., the row that gets inserted may not have null as
- its column values), which defeats the purpose of SQL abstraction.
-
Therefore, the Database Access API provides a database-independent way to
- represent null (instead of the Oracle-specific idiom of the
- empty string): db_null.
Use it instead of the empty string whenever you want to set a column value
- explicitly to null, e.g.:
set bar [db_null] -set baz [db_null] - -db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)" -# -# sets the values for both the "bar" and "baz" columns to null@@ -545,25 +524,6 @@
Technically it's equivalent to using a code block on the end of your db_multirow.
-
- db_null
-
-
-
-
-db_null
-
-
-
- Returns a value which can be used in a bind variable
- to represent the SQL value
- null.
- See Nulls and
- Bind Variables above.
-
@@ -885,51 +845,7 @@
}
-
-
-
- db_nullify_empty_string
-
-
-
-
-db_nullify_empty_string string
-
- For true SQL purists, we provide the convenience function
- db_nullify_empty_string, which returns
- [db_null] if its string argument is the empty string
- and can be used to encapsulate another Oracle quirk:
-
-
-
-set baz ""
-
-# Clean out the foo table
-#
-db_dml unused {delete from foo}
-db_dml unused {insert into foo(baz) values(:baz)}
-
-set n_rows [db_string unused "select count(*) from foo where baz is null"]
-#
-# $n_rows is 1; in effect, the "baz is null" criterion is matching
-# the empty string we just inserted (because of Oracle's coercion
-# quirk)
-
-
-
-
- To balance out this asymmetry, you can explicitly set baz to
- null by writing:
-
-
-
-
-
-db_dml foo_insert {insert into foo(baz) values(:1)} {[db_nullify_empty_string $baz]}
-
-
-