SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: root
To: [EMAIL PROTECTED]
Subject: mySQL handeling of NULL in insert and update on NOT NULL columns.
>Description:
This may be a problem in the functional specification not be a bug in the
code.
Regarless, I should be treated the same because it causes major problems
using
mySQL. Below, I intend to show you the problem as I see it and then explain
why it is important that the spec. and/or code be changed.
The problem is in converting NULL to the empty string '' when updating NOT
NULL
columns.
Try the following:
mysql> create table t(
-> c1 varchar(50) NOT NULL,
-> c2 varchar(50) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (NULL, NULL);
ERROR 1048: Column 'c1' cannot be null
This is good. We are prevented from inserting nothing in these columns.
Now try this:
mysql> insert into t values (1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+----+----+
| c1 | c2 |
+----+----+
| 1 | 2 |
+----+----+
1 row in set (0.00 sec)
Here we see that we were able to insert valid values in these columns. That
is
also good.
Now, here is the trouble.
Try this:
mysql> update t set c1=NULL, c2=NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 2
We have just aparently changed two NOT NULL columns to NULL. This is VERY
BAD!
that is what we where trying to prevent by declaring them as NOT NULL. I
would
assume that the 2 Warnings on that update above are telling me that the
NULLs
where converted to '' because of the following.
mysql> select * from t;
+----+----+
| c1 | c2 |
+----+----+
| | |
+----+----+
1 row in set (0.00 sec)
That's beside the point though because from a JDBC interface I can not see
what those warnings are. Even if I could, I wouldn't want to check my
warnings after every update look for NULLs converted to '', figure out what
the
value was before I inadvertently set it to '', replace the previous values,
and
then take the appropriate action needed for my program in the case that I
try to
set a NOT NULL column to NULL.
Why this is important:
Different databases handle NOT NULL in different ways. For instance, Oracle
will not allow NULL or '' in NOT NULL columns and will throw an error if you
try
to insert or update them. But in order to write database independent
applications the interaction with the database must be identical. This is
actually easier than you might think in most cases. You can get around the
question of whether '' is a NULL value or not in the following way.
Create a database object to handle interaction with a specified table. In
our
case the table is t so the object would be called T and go something like
the following pseudocode:
class T {
private String c1 = null;
private String c2 = null;
public void setC1(String s) {
if(s!="") {
c1 = s;
}
else c2 = null;
}
public void setC2(String s) {
if(s!="") {
c2 = s;
}
else c2 = null;
}
public String getC1() {
if(c1==null) return "";
return c1;
}
public String getC2() {
if(c2==null) return "";
return c2;
}
public void insertT() throws DatabaseException {
String sql = "insert into t values ("+c1+","+c2+")";
Database.executeSQL(sql);
}
public void updateT() throws DatabaseException {
String sql = "update t set c1="+c1+", c2="+c2;
Database.executeSQL(sql);
}
}
Setting the '' to null on setX() and getting '' instead of null on getX()
means
that we have a consistent interface through all databases. We simply create
a
JDBC connection to any Oracle, MS sqlServer, Postgre or DB2 database and our
application handles them all the same. Right now mySQL cannot be included
in
that list because it handles updates differently than it handles inserts.
This upsets me because 1) I really like mySQL and I want the enterprise
applications I work on to work with it and 2) because, as you've probably
guessed I'm a java developer and I want my code to be write once run
anywhere.
Those to things conflict in the current implementation of mySQL because I
can't
write one application that can use Oracle or mySQL.
I would appreciate a response at [EMAIL PROTECTED]
Thank you for your time,
Cavan Morris
>How-To-Repeat:
create table t (
c1 varchar(50) NOT NULL
);
insert into t values (1);
update t set c1=NULL;
>Fix:
Stop changeing NULL to '' on updates.
>Submitter-Id: <submitter ID>
>Originator: root
>Organization:
Prime Advantage Corp.
>MySQL support: none
>Synopsis: changes NULL to '' on update to NOT NULL columns.
>Severity: serious
>Priority: high
>Category: mysql
>Class: change-request
>Release: mysql-3.23.37 (Official MySQL binary)
>Server: /usr/local/mysql/bin/mysqladmin Ver 8.19 Distrib 3.23.37, for
pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.37
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 18 hours 3 min 40 sec
Threads: 2 Questions: 347 Slow queries: 0 Opens: 86 Flush tables: 1
Open tables: 15 Queries per second avg: 0.002
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux lin1 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-81)
Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc'
CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static'
LIBC:
lrwxrwxrwx 1 root root 13 May 8 04:57 /lib/libc.so.6 ->
libc-2.2.2.so
-rwxr-xr-x 1 root root 1236396 Apr 6 14:58 /lib/libc-2.2.2.so
-rw-r--r-- 1 root root 26350254 Apr 6 12:27 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 6 12:27 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql
'--with-comment=Official MySQL binary' --with-extra-charsets=complex
--enable-assembler --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --disable-shared
This email message is for the sole use of the intended recipient(s) and may
contain proprietary and confidential information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and destroy all copies
of the original message. Thank you
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
