I’m storing a large amount (not huge) in ascii text using a blob field in SQLite.
I’m using longblob in MySQL and bytea in Postgres. All work well.
Using ODBC to connect with MS SQL Server it gets clipped to 8,000 characters.
Is there a known limitation?
Is there a workaround?
no responses so I’m adding onto this with the hopes that someone has overcome this.
I had been testing with ODBC and with MBS to connect from my Mac to MSSQL server and am not able to insert more than 8,000 characters regardless of what kind of column I try to make. I’ve tried all options.
I decided to try using the XOJO plugin on Windows and it’s worse. I’m only able to summit 255 characters.
Someone must have used MSSQL with Xojo and needed to store more than 255 characters.
Anyone?
Anyone?
Bueller?
I had this issue on Windows and MSSQL to store the HTML text of an email. I had to change the field format to …MAX and use a plain INSERT/UPDATE command with the field value inside quote marks. Naturally I also had to escape the text first. I was constantly getting errors using my normal MBS Prepared Statement method. There may have been a better way, but this worked.
For MBS SQL Plugin, when binding data for varchar(max), please use kTypeClob as data type.
With normal text/string type, you get limited length.
I may tune the plugin for that case to use kTypeClob automatically in some cases.
I tried it here:
dim t as string = "Hello World "
while len(t) < 9000
t = t + "xxx "
wend
dim p as PreparedSQLStatement = con.Prepare("UPDATE TestTable SET pLongText=? WHERE pInt=1")
p.Bind(0, t)
p.BindType(0, SQLPreparedStatementMBS.kTypeClob)
p.SQLExecute
dim sql as string = "SELECT pLongText From TestTable;"
dim r as RecordSet = con.SQLSelect(Sql)
if r<>nil then
dim x as string = r.IdxField(1).StringValue
MsgBox str(len(x))
else
MsgBox "Failed to query."
end if
When I let plugin decide type automatically, I get this error:
HY104 [Microsoft][SQL Server Native Client 11.0]Invalid precision value
so 9000 characters don’t fit in a text data type.
But if I use the BindType line with SQLPreparedStatementMBS.kTypeClob, it works.
What type did you set the column to on the server?
it’s a varchar(max)
Next version of plugin will automatically switch from String/Bytes to CLOB/BLOB for > 8000 characters/bytes.
That’s very helpful. Thanks for that and for all the offline help.