Problems with MSSQL Prepared Statement

Using 2020r2.1 Web
So far i’ve only tried chrome/edge

Still testing as I am writing this.

This 100% could be a user error and I hope it is. I’ve tried this with two different MSSQL databases. Two different update statements (different tables). I am about to test in Web 1.0.

Try
  var sqlserverupdate as String
  sqlserverupdate = "UPDATE tblservers SET servername = ?," + _
  " description = ?," + _
  " dnsname = ?," + _
  " os = ?,"+ _
  " notes = ?,"+ _
  " servicetag = ?,"+ _
  " ipaddress = ?,"+ _
  " servertype = ?,"+ _
  " datatype = ?,"+ _
  " locationname = ?"+ _
  " WHERE serverid = ?"
  
  Session.glodb.ExecuteSQL(sqlserverupdate,txtname.Text,txtdescription.Text,txtdns.Text,popos.SelectedRowValue,txtnotes.Text,txtservicetag.Text,txtip.Text,popservertype.SelectedRowValue,popdatatype.SelectedRowValue,poplocation.SelectedRowValue,serverid)
  
  
Catch err as DatabaseException
  MessageBox("Error: " + err.Message)
End Try

The statement runs and does not have any errors, but if a field has nothing in it I get weird symbols in the database fields.

Examples:
DNS was blank on the webpage. After running the command the dns field value is:

://http://127.0.0.1:8080
Referer: http://127.0.0.1:8080

I removed the value, saved the record again and got:

info":{“left”:20.0,“top”:173.0,“width”:225.

Here are a few field values from a separate application, separate database, and different field types. Every blank field has some weird characters:

Phonenumber

y":false,"capt

Pager

µ

I’ve tried changing the field types (char,nvarchar,varchar). I’ve tried binding the types. I’ve tried declaring variables = field.text and then using the variables instead of field.text. I confirmed that before the SQL execute they are indeed blank values and not weird characters. It should also be noted that a non-prepared statement works like a charm.

Last night I tried with postgresql and it seemed to work fine. I couldn’t replicate it.

EDIT: Just tried with 1.0 (2019r3.2)

Unhandled RuntimeException
Message: Encountered invalid character.

Stack:
Xojo.Data.GenerateJSON%y%x
WebResponse._Render%s%o
WebSession._HandleEvent%%oso<_HTTPServer.HTTPRequestContext>
WebSession._HandleRequest%i4%oso<_HTTPServer.HTTPRequestContext>
WebApplication._HandleHTTPRequest%%oo<_HTTPServer.HTTPRequestContext>
_HTTPServer.HTTPRequestThread.Event_Run%%o<_HTTPServer.HTTPRequestThread>
REALbasic._CallFunctionWithExceptionHandling%o%pp

DNS Value:

ç2˜ß͵ôd_dƒþ)p~âØŽ³‚tº“TFAóÿtHëeϸ5ËJ
+Žû€NHP¥ptŒ;æúû[ ëK–׸È

Second EDIT:

Looks like if I just replace the blanks with a space it doesn’t input strange symbols/characters

If stringtext = "" Then
  stringtext = " "
End If

Return stringtext

I also get the error with SQL INSERT command. SELECT is working properly.

Did you check the encodings for the strings? Just to be sure.

It looks like you are getting data from a response/request into your fields somehow.

The message says " Encountered invalid character."
Could it be that you have line endings in the text?
If so, convert them to something else and back. Perhaps it’s a xojo bug or not but you may be able to workaround it

WOW. Thank you. I assumed MSSQL supported UTF-8… After doing a little reading looks like it just came with SQL Server 2019. I was losing my mind over this.

Hey I spent some time today trying to figure this out. I’ve tried everything I could think of. I changed the Collation of the entire DB to Latin1_General_100_BIN2_UTF8. I’ve tried converting the strings in xojo.

I am starting to wonder if it isn’t a encoding issue

This error can easily be replicated. I created a new DB with a new table. Made a new app with 3 text fields and a button. The button inserts into the table. I put random numbers in textfield1 and left the other two textfields blank. After hitting the button a few times my results show:
image

EDIT: Just testing with Desktop same results.

Try disconnecting and reconnecting the database connection just before this ExecuteSQL and see if the problem magically goes away?

If that doesn’t fix it then it looks like a different bug to the one I’m thinking of, if you can make a simple demo project of it, create a new feedback ticket for the bug and hopefully xojo can provide you with a workaround until its fixed.

image

Similar result. I entered “closeopen” in the first text field.

Yes it looks like some corruption between the data being returned from the web framework and making its way into the database.

Out of interest, if you system.debuglog testfield1 (or whatever you call it on the web form) do you see the corrupted data or the real data? that should at least tell you if its a web framework issue or a database plugin issue which would then lead to you to try the depricated mssql sqlexecute instead and see if that shows the same issue.

Here is the debuglog: image

I put single quotes around the field to confirm they are blank.

I tried the sqlexecute with the same result. Surely I am not the first one to use MSSQL prepared statements with the new framework. I would’ve thought someone would have brought this up.

EDIT: forgot to mention I tested it with 2020r2.1 desktop, I got the same results.
Second EDIT: I just saw my typo in the debuglog. I confirmed the fields displaying are correct. I just can’t type.

Do you have a little test project you can share? I don’t see the issue here when testing with a web project.

Yeah I’ll try to get one uploaded tonight. Out of curiosity what version of SQL are you using? Are you converting the encoding?

EDIT: I should note I am using MSSQL 2019 Dev

My test sql here is on sqlexpress 2012 SP3 - 11.0.6020

No encoding, just using strings as they are encoded in Xojo, UTF-8.

I just tried with 2016 sql, still getting the same issue. It must be my code if it works for you. Here is a link to a test project. I left all the methods I’ve tried.

https://drive.google.com/drive/folders/1bxqfLmtqUH0ZbUOekOBRfjaBG7SGpStT?usp=sharing

What are the types of your columns in the database nvarchar?

I’ve tried varchar, nvarchar and char.

Do you know what collation you are using?

I’m on default collation, Latin1_General_CI_AS

Have you tried reinstalling Xojo, it could be that your MSSQL plugin is corrupt.

I’m really concerned that you’re seeing the correct data via system.debuglog but corruption into the database.

My code is working fine with your database? Let me see if I can get my hands on a 2012 sql copy. I will try reinstalling. I tested on two different computers but won’t hurt to try again. I am going to try to build the app and test with a phone or something.

Also try it in 32 bit just for giggles

I built the app and put it on the same server as the DB. Here is the value of one of the fields after trying with a iphone/safari. Not sure if this helps.

;q=0.5
User-Agent: Mozilla/5.0 (iPhone; CPU iPhone OS 14_0 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0 Mobile/15E148 Safari/604.1
Accept-Language: en-us
Referer: http://s-bl-sql-v03:6043/
Accept-Encoding: gzip, deflate

Uninstalled all versions of Xojo. Restarted. Installed again. Using the same project, here are the results:
image

I am going to spin up a new virtual machine and test with that.

EDIT: the 32 bit test was record 1. The 64 bit test was record 2 and 3.
EDIT2: I will test on my personal PC in a couple hours and attempt to recreate the issue.

Spun up a virtual machine with 2016 SQL Express. Tested and received the same results. I guess I am going to put in a feedback case.

Edit: 64208