How store an empty DatabaseField?

Hi, I work with RealStudio 2010r3 and the according RealSQLDatabase.
How can I assure that a Sting-Databasefield has no content?

When I store data from a multi-dimensional Variant-Array to the database sometimes (not allways) an empty Array-field is stored as asc(0) in the string database-field. So it’s not empty (has any content).
I tried:
dim rec as DatabaseRecord
rec.Column(“Feintrag”) = “” //sometimes stored as asc(0)
rec.Column(“Feintrag”) = Nil //parameter not compatible to this function

thanks, Christian Hahn.

Great tips on database considerations including what you’re looking for (check out #21)

… Don

rec.Column("Feintrag") = Nil //parameter not compatible to this function

BTW … it’s my understanding that you can NEVER set anything equal to Nil

Just don’t assign anything to that column and it will be NULL in the database.

Thank you, Don, for your hint, it’s allways helpfull do reconsider the difference between values und properties: “Null doesn’t exist”.
Thank you, Eli, that’s a good idea. I had in mind that but unfortunately there are oftentimes values in the databasefield which should be blanked. I’m not explicitly interested in NULL but in an empty databasefield that is retrieved as “” without any risk. Has anybody an idea?
… Christian Hahn.

If what you want is “” then set the value to an empty string
NULL is of course a very different thing - its literally no value of any kind assigned where “” is a value that has no contents


Possibly there is a confusion between Xojo syntax and RDBMS syntax here.

In a relational database, there has to be a way to express ‘there was nothing there’
So a list of people has 5 rows.
A list of people joined to a table holding the name of their pet dogs may hold 3 rows.
An inner join would return only 3 rows: only the rows where people own dogs.
An OUTER join returns 5 people, 3 of them have a dog name, and 2 return NULL

A database NULL isn’t a Xojo Nil. (A Nil is an object that hasn’t been initialised yet… a pointer that points nowhere useful)

But even within a returned row from a database, a field can be NULL or empty, or populated.

So for a string field, it can have a value of ‘Hello’, a value of ‘’ or a value of NULL (if that was allowed when the table was defined)

Where a field is allowed to be NULL, you can set it to be NULL with SQL

Update Mytable set FIELD1 = NULL where ID = 6;

Thats not the same as

Update Mytable set FIELD1 = '' where ID = 6;

Now, assume you have some rows which are populated, some which are ‘’, and some which are NULL
Assume NULL is going to cause a problem if it gets returned by a query.

The solution is COALESCE in most databases.
What that does is to say ‘if the field is null, give me a default value instead’

You use that like this:

Select COALESCE(FIELD1,'') from MyTable;

Hope that helps.

Jeff, my point was I’m under the understanding that you can conditionally check to see if something equals Nil (e.g., if f <> Nil then …), but you cannot ‘set’ anything to Nil (e.g., f = Nil). Furthermore, I was attempting to differentiate the definition of NULL versus Nil.

Am I under the wrong understanding???

… Don

I think I’ve got it. And sorry that I caused confusion.
Dim rs as RecordSet
rs.Field(“Feintrag”).Value = nil //works

dim rec as DatabaseRecord
rec = New DatabaseRecord
rec.Column(“Feintrag”) = “”

rec.Column is only needed when creating a new DatabaseRecord.
And in that case there isn’t yet a content to be blanked.
It’s actually enough to skip the data to leave the databasefield empty.

Thank you for your help, Christian Hahn.

You are. It is permissible, and often desirable, to set a variable to Nil to destroy the object.

Much appreciated, Tim … I try to be real careful with what I suggest on the forum (and for that reason, I haven’t suggested much until recently) because although I have written code ever since the days of Fortran, I’m not nearly as tenured a Xojo programmer as folks like you, Norman, Paul, and the list goes on. But, for some reason, I thought I had just seen that info about ‘Nil’ as part of a discussion I was reading about checking for file existence (part of the old what comes first when conditionally checking? f.exists? or f <> Nil).

Obviously, I got that part wrong and I appreciate you helping me learn the right way … thank you for closing that loop for me.

… Don

The MS JET database would always throw up the ‘Invalid use of a Null’ error if you tried to read from a null field. The way to get around this problem was to use the syntax x$=RS(3)+"" where x$ was a string variable and RS(3) was field 4 in a recordset.