SQL Server - How to write column with empty string instead of NULL
I have a class that writes to an external SQL Server database. When the field is empty, it does not write the column and the column has the value NULL. In some cases, I want the column to be the value of the empty string instead of NULL.
{
Property MessageType As %String
}
The message that is passed to this class shows:
<MessageType></MessageType>
Which writes the field as NULL with:
tSQL = "insert into myTable (MessageType) values (?)"
set tSC = ..Adapter.ExecuteUpdate(.tNumberOfRowsUpdated,tSQL,myRequestClass.MessageType)
I've tried setting to <MessageType>""</MessageType> as well as <MessageType>''</MessageType>. But as expected, that sets the column to be quotations / apostrophes.
I've tried change the data value in the class to be "" before inserting the row:
myRequestClass.MessageType = ""
but the column value is still NULL - not an empty string.
These also do not work - as the datalength of the column results in 1 not 0 (which is the length of an empty string):
myRequestClass.MessageType = "''"
myRequestClass.MessageType = """"
Creating the insert statement in the class as:
tSQL = "insert into myTable (MessageType) values ('')"
Does seem to work, but I have numerous columns and various combinations. It must work as a variable.