Table not Accepting Empty String Value

[kwpsd]

Servoy Version: 5.2.9 - build 1020
Sybase SQL Anywhere Version 11.0.1.2044

I have a table column delcared as TEXT (varchar) and marked as NOT NULL. If I attempt to save an empty string in this column, the following error is emitted:

> com.servoy.j2db.dataprocessing.DataException: SQL Anywhere Error -195: Column 'user_name' in table 'user_record' cannot be NULL
Validation failed for 'user_name', with value:
Wrapped java.lang.IllegalArgumentException: Validation failed for 'user_name', with value:

Here is the code that causes the error message:

Code: Select all

var fs = databaseManager.getFoundSet( 'arm_data', 'user_record' )
   
fs.loadAllRecords()

var newRecord = fs.getRecord( fs.newRecord() ) 

newRecord.user_name = ''

databaseManager.saveData( newRecord )

The Sybase document (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks258.htm) states the following for SQL Error -195:

You have not supplied a value where a value is required. The column definition prohibits NULL values or the column is part of a NOT NULL foreign key.

It appears that a NULL is being passed to SQL Anywhere, but it should be an empty string value. Would someone be kind enough to explain to me what is happening?

Thanks!

[jcompagner]

empty strings and null are tricky.. For example oracle as far as i know see those really as the same thing
inserting an empty string there (when that is allowed) will set that column to NULL.

I don't know how sybase works with this situation exactly, but you should just threat null and empty the same it really both nothing..

[kwpsd]

I don't know how sybase works with this situation exactly...

Sybase says this (http://manuals.sybase.com/onlinebooks/group-pbarc/conn5/sqlug/@Generic__BookTextView/33271;pt=33136):

Character data is placed in the database using the exact binary representation that is passed from the application.

To prove Sybase's statement, using Sybase Central only, I created a table called 'test' that contains a single character column called 'test_char' marked as NULL. Using SQL INSERT statements, I successfully wrote a non-empty string, empty string, and NULL to the table. Then, using the SQL ALTER statement, I added the NOT NULL constraint and tried the INSERT statements again. The table accepted the non-empty and empty strings but rejected the NULL. Below is the SQL code:

Code: Select all

CREATE TABLE test (test_char varchar(20) )

INSERT INTO test (test_char) VALUES ('Hello, World!')
INSERT INTO test (test_char) VALUES ('')
INSERT INTO test (test_char) VALUES (NULL)        <--- ACCEPTED

Code: Select all

ALTER TABLE TABLE test
ALTER test_char varchar(20) NOT NULL

INSERT INTO test (test_char) VALUES ('Hello, World!')
INSERT INTO test (test_char) VALUES ('')
INSERT INTO test (test_char) VALUES (NULL)        <--- REJECTED

It's only when writing an empty string to a NOT NULL column in Servoy that the problem occurs. Isn't this a bug?

...you should just threat null and empty the same it really both nothing.

Okay, now I have a philosophical question regarding SQL database design. You are correct that Oracle treats the NULL and empty string the same, but Oracle appears to be the only database engine that does this. To keep our solution database agnostic, should we then mark all character columns to accept NULL? How do other Servoy developers handle the NULL vs. empty string conumdrum? What is the best practice?

[jcompagner]

Best practice is what i am saying, threat empty the same as null, both is just nothing..

else try once a workaround:

newRecord()
record.mynotnullcolumn = "something";
record.mynotnullcolumn = "";
saveData()

[kwpsd]

NULL it is! Thanks, Johan.