When inserting a record into an MS SQL Server database, I check if the controls contain any data or not. If not, I would like to insert NULL values into the related columns in the database.
For example, if WebTextField = “”, then insert NULL literal into the database.
I would like to execute the SQL like in the following link, where parameters are passed into the ExecuteSQL method to prevent SQL injection.
This is actually a pretty good question. In .NET there is a special singleton class DBNull, with a Value property, so you pass DBNull.Value as the “value” to insert for columns where you want NULL. How does this work in Xojo?
I did find in the docs that you can set a rowSet’s value to Nil and it will be passed to the DB as NULL. Which implies that if you’re rolling your own SQL, you can pass Nil to the SqlExecute() or SqlSelect() methods.
Technically nil / null / nothing within a language is not the same thing as NULL in a database but it’s an okay convention to use one to signify the other as long as it’s consistently understood / followed.
Disclaimer: I haven’t had to do this yet so haven’t personally tried it but it looks like that’s the answer. I will have to address this issue soon in some of my CRUD screens because an empty string is definitely different in significance to NULL on those screens.
It would just be in the SQL rather than a parameter placeholder. So in Sql Server:
INSERT INTO SomeTable (FirstName,LastName,DateCreated) VALUES (?,?,GETDATE())
… and you would just have two parameter values to pass to ExecuteSql().
If you’re not rolling your own SQL then I don’t know if there’s a provided Xojo mechanism for this kind of thing. In that case you can set the Default value of the column definition to GETDATE() on the Sql Server side of things and then just ignore the field in your queries. When an INSERT is done, GETDATE() will be put in automatically, just as is done by a field with IDENTITY defined. This works fine for INSERT, but it doesn’t address UPDATEs (e.g., a DateUpdated column). There, AFAIK you’re going to have to roll your own UPDATE SQL.
It IS best practice to use server time for dates, since various clients can be a little (or a lot) out of sync and you are removing time zones from the equation, expressing everything relative to server time (or better still, GMT / UCT).
In MySQL the default value will always be null unless you set the default to be some other value to be the default. When inserting or updating if you want the value to remain null you don’t reference the field in your insert or update.
Below is a view of a table design from an application called SQL Manager for MySQL by EMS Software, ( https://www.sqlmanager.net)
You can see the default is set to null for most fields, which is the default default, and I have set one field to CURRENT_TIMESTAMP and another field to 0. There is no default for the Primary Key field which is set to Not Null and autoincrement.
This is great, but if you want to have a generic INSERT or – especially – UPDATE statement that would potentially put either NULL or some non-NULL value into a column, then you don’t have the luxury of just leaving columns out of your SQL. I have exactly this scenario in a couple of tables I’m dealing with. They are rule tables defining how certain values are to be altered under certain conditions. If you put an empty string in the rule, that value is to be changed to the empty string. If you pass NULL, it is to be left alone.
I build my sql statements on the fly depending on what columns I want to write to the db engine. An update statement that includes nulls would look something like: db.ExecuteSQL("UPDATE contacts SET name = ?, address = Null WHERE id = ?;", "ABC123", 55)
That works. However, every time Sql Server sees a different query, it compiles it. It may be that in practice, this is not a big deal. A handful of users on a CRUD app, would not matter. A lot of users on a CRUD app or any sort of batch process, likely it would not scale well.
@Bob_Grommes Thanks, you understand well what I am trying to do using a generic query where values could be NULL.
I have set objects values to “NULL” (string) if a control does not contain a value e.g. a WebDatePicker. I then map column name to values in a Dictionary. If the value for a column is “NULL”, then I write NULL to the value parameter in the ExecuteSQL method.
However, when trying to run the Insert query from the web app, a DatabaseException is raised.
ErrorNumber: 1
Message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work has been done.
Reason: Multiple-step OLE DB operation generated errors. C
Any ideas what could be causing this to occur? The parameters being passed into ExecuteSQL are all either String or Integer type so database should be interpreting them fine.
Google say check it the source string len fit into the target field size.
or u used smalldatetime and not datetime in your ms sql table and have a source datetime field in your crud system.
I think I have identified the issue. I am passing too many arguments into ExecuteSQL as I have already specified NULL in VALUES in sql insert statement for many of those parameters. Is it possible to conditionally pass in arguments to ExecuteSQL for paramaters that are not NULL? I can’t simply pass in a comma separated string of all desired parameters into EexecuteSQL so not sure how I can achieve what I want?
@Bob_Grommes fyi, this is following your suggested method so not sure how you have made this work?
I realised I could pass in parameters as an array of values into ExecuteSQL, which I have tried. But now I am getting the default Session interrupt message:
We are having trouble communicating with the server. Please wait a moment while we attempt to reconnect.
The issue here related to the session interrupt message has been solved. This is because passing an array to ExecuteSQL containing at least one empty string does not work. The bug was identified here.
I meant to suggest passing the same arguments for the same columns each time – just that when you want them to be NULL on the database side, pass Nil instead of the normal value. You will then need parameter substitutions in the SQL for all columns.
