Database Reference
In-Depth Information
Python
Python DB API programs represent
NULL
in result sets using
None
. The following ex‐
ample shows how to detect
NULL
values:
cursor
=
conn
.
cursor
()
cursor
.
execute
(
"SELECT name, birth, foods FROM profile"
)
for
row
in
cursor
:
row
=
list
(
row
)
# convert nonmutable tuple to mutable list
for
i
,
value
in
enumerate
(
row
):
if
value
is
None
:
# is the column value NULL?
row
[
i
]
=
"NULL"
print
(
"name:
%s
, birth:
%s
, foods:
%s
"
%
(
row
[
0
],
row
[
1
],
row
[
2
]))
cursor
.
close
()
The inner loop checks for
NULL
column values by looking for
None
and converts them
to the string
"NULL"
. The example converts
row
to a mutable object prior to the loop
because
fetchall()
returns rows as sequence values, which are nonmutable (read only).
Java
For JDBC programs, if it's possible for a column in a result set to contain
NULL
values,
it's best to check for them explicitly. The way to do this is to fetch the value and then
invoke
wasNull()
, which returns true if the column is
NULL
and false otherwise. For
example:
Object
obj
=
rs
.
getObject
(
index
);
if
(
rs
.
wasNull
())
{
/* the value's a NULL */
}
The preceding example uses
getObject()
, but the principle holds for other
get
XXX
()
calls as well.
Here's an example that prints each row of a result set as a comma-separated list of values,
with
"NULL"
printed for each
NULL
value:
Statement
s
=
conn
.
createStatement
();
s
.
executeQuery
(
"SELECT name, birth, foods FROM profile"
);
ResultSet
rs
=
s
.
getResultSet
();
ResultSetMetaData
md
=
rs
.
getMetaData
();
int
ncols
=
md
.
getColumnCount
();
while
(
rs
.
next
())
// loop through rows of result set
{
for
(
int
i
=
0
;
i
<
ncols
;
i
++)
// loop through columns
{
String
val
=
rs
.
getString
(
i
+
1
);
if
(
i
>
0
)
System
.
out
.
print
(
", "
);
if
(
rs
.
wasNull
())
System
.
out
.
print
(
"NULL"
);
else
System
.
out
.
print
(
val
);