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
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’
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.
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.
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.
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.