I am using prepared statements to insert & update records on a 2012 Database Engine. Where the fields allow nulls & I don’t write those fields or I do write those fields but with a “” random data is appearing in those fields.
Is this a Xojo plugin issue or MS SQL server native client?
I have just stumbled upon this issue too. My app works flawlessly with SQLite, MySQL and PostGRE (both MySQL and PostGRE are remote databases). My first experiences with SQL Server as a remote database are somewhat painfull. Blank fields apparing populated with gibberish… I can’explain it at this moment… Will have to figure it out. Client uses SQL Server as company standard.
I ended up working around the issue by writing Chrb(255) into the empty field, then checked for and removed it when reading back. That makes it difficult when using reporting services etc., but it does now work in my app.
Another thing you should be aware of is that when writing data using a prepared statement only the first 255 characters will be written. Data is truncated at that point. You can inject the full data using SQL injection, but that leaves you open to attack.
I only really use MS SQL and through bitter experience, my advice is :-
use the MBS sql plugin.
Do you mean you don’t bind values to these fields in the prepared statement or do you mean the prepared statement’s SQL string doesn’t contain these fields?
The prepare statement’s SQL string does not contain those fields.