On Mon, Mar 24, 2008 at 5:07 PM, Ferindo Middleton <
[EMAIL PROTECTED]> wrote:
> Well, maybe my problem isn't necessarily on user input. My app does check
> for this and not allow empty strings. I guess the problem more so exists
> because I get files from the client to LOAD into the database. I have
> several SQL scripts in a library that I choose from to load the data,
> different scripts will be run based on the various formats the client sends
> to me to load the data.
> Maybe I should do a better job examining and tweaking my various scripts I
> have for checking and handling such empty string values when loading files.
> But I just wanted to build some kind of constraint into the database itself
> so just in case I forget certain details, maybe the constraint would be
> there within the database itself. I also plan to hand over such operations
> to other admins eventually and I wanted the constraint to be in the
> structure of the tables where necessary. I used to use PostgreSQL and I
> think this feature was easier to command using CREATE TABLE.
> I imagine this kind of feature would be an enhancement that could
> otherwise be avoided by a more aggressive quality control by a db admin but
> I think it would be a good idea to have some kind of "NOT EMPTY" constraint
> on a database considering NULL and an empty string ( '' ) are separate
> values. In the real world they really mean the same thing and the solution
> seems obvious to put such a constraint at the database creation level (in
> the structure) when you think about it in practical terms.
> That way you could have a safeguard so developers and db admins wouldn't
> have to worry about, especially when some them have very large sets of data
> to manage and pass among to different organizations when trying to integrate
> data between different systems, all of which, again, share the practical
> idea that an empty string is equal in value to a NULL value.
An alternative that I do not like much, but should work for you:
CREATE TABLE `t` (
`c1` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`c2` VARCHAR( 255 ) NOT NULL
);
DROP PROCEDURE IF EXISTS t_insert;
DELIMITER |
CREATE PROCEDURE t_insert(IN val VARCHAR(255))
BEGIN
IF LENGTH(REPLACE(val,' ','')) =0 THEN
SET val = NULL;
END IF;
INSERT INTO t(`c2`)VALUES (val);
END
mysql> call t_insert('stuff');
Query OK, 1 row affected (0.00 sec)
mysql> call t_insert('');
ERROR 1048 (23000): Column 'c2' cannot be null
mysql> call t_insert(' ');
ERROR 1048 (23000): Column 'c2' cannot be null
mysql> call t_insert(' blah ');
Query OK, 1 row affected (0.00 sec)
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
