Storing large text in MSSQL via SQLDatabaseMBS

Add-Ons
1

For @Christian Schmitz , and hopefully a solution for all to see.

I store my text in VARCHAR() fields with the INSERT Prepared Statement commands within SQLDatabaseMBS and this works OK. It stores up to 4000 characters.

I now need to store an email plain text message with a corresponding HTML equivalent. I am using the API 2.0 compatible SQLPreparedStatementMBS.ExecuteSQLMT statement to do the INSERT. I am having two issues:

  1. Even though the plain text field is VARCHAR(3999) and the plain text is about 3,500 characters, it gives the error HY104 [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value.
  2. I have defined the HTML field as NVARCHAR(Max) and the HTML text is about 5,000 characters. If I use BindType SQLPreparedStatementMBS.kTypeString I get an error that it cannot convert. If I use BindType SQLPreparedStatementMBS.kTypeLongChar it gives the error below. BTW there is no ‘image’ or weird non-ASCII characters in the HTML.

42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 22018 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: image is incompatible with nvarchar(max)

I can add the HTML text if I define the database field as VBINARY(MAX) and use BindType SQLPreparedStatementMBS.kTypeLongBinary, but it stores and retrieves as Base64. I have tried defining the database field as TEXT, but get the same Invalid precision value error.

Online searches suggest this may be an ODBC issue ie the CURL driver within SQLDatabaseMBS, but these ‘solutions’ were from years ago, so there must be a solution today.

How are you supposed to store medium-large text fields in MSSQL using SQLDatabaseMBS? Is SQLDatabaseMBS accidentally Binding SQLPreparedStatementMBS.kTypeLongChar as an ‘image’?

2

Should this field not be a CLOB to make room?

3

From MSDN Documentation

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes

4

SQL Server has no field data type CLOB, so I am using NVARCHAR(Max). My issue is that I get errors when trying to INSERT plain text into a NVARCHAR(Max) field.

5

After failing with Prepared statements, I tried the db.AddRow("", rec) method with both MSSQLServerDatabase and SQLDatabaseMBS, but these gave a strange error message (not sure if this is a Xojo error or MSSQL):

Unclosed quotation mark after the character string ')'.

and doing a db. COMMITTRANSACTION gave an exception error:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

In the end I tried a simple

db.ExecuteSQL("INSERT INTO (…) VALUES (…)")

command (after escaping) and that worked without issue, even with the db. COMMITTRANSACTION.
I am sure there is a reason people love MSSQL, but it certainly doesn’t like me!