Using IRIS via ADO to insert - <MaxString> Error while inserting row with large Base64 coded data

Question

Question

Christoph Abraham · Mar 29, 2023

Our ERP solution runs on different sql databases (such as ms sql and posgre). We are using the same code for all plattforms, doing database-specific changes in the db driver for each type of database.

There is one function in our program, where the user can store long text to discribe something, which is used in print and web. This text is treated/stored as html and can include pictures. The pictures in the html are stored base64 coded inside the html and the html is stored as text in a column. In other database systems, this is treated as TEXT or BLOB, and workings just fine.

But with IRIS, we get a "MAXSTRING" error, if the images in the text are in total above a certain limit (like 2.5MB). We see this error

The column in the sql table is of type TEXT, and is translated to %Stream.GlobalCharacter in the for the table generated IRIS class.
As we understand, the cached query for this can't have more characters then maxString. And the 2.5MB mentioned above sum up to the 3.1 million characters of MAXSTRING.

We are looking for a solution, without changing the general infrastructure of our application, because we need to keep the flexibility to use different types of database engines. Shrinking large pictures is allready done before storing and doesn't prevent problems for ever, because users can insert as much pictures and text they want.

Is it possible while sending an insert/Update to database via the ado driver for iris, that this SQL is executed without creating a cached query? Is there any way to execute sql statments, having more characters than maxString?

At the moment, in iris db driver, we are using irisCommand.ExecuteNonQuery to send Insert/Update/Delete commands to database.

We found the section Storing and Using Stream Data (BLOBs and CLOBs) | Using InterSystems SQL | InterSystems IRIS Data Platform 2022.3. But it seems, we cant do this via ADO. All examples are based on functions inside of IRIS. Is there a way, to insert large streams via ADO driver? If so, we would try to split the insert, if it contains a large amount of base64 coded data while/after inserting the basic row in the database.

Product version: IRIS 2022.1

$ZV: RIS for Windows (x86-64) 2022.1.2 (Build 574)

Discussion (6)1

Log in or sign up to continue

score 0 · Answer 1 · 2023-03-30T16:14:33-04:00

just a minor part:
Is it possible while sending an insert/Update to database via the ado driver for iris, that this SQL is executed without creating a cached query?
NO:
In IRIS and also the latest of Caché / Ensemble every query is cached.
real non-cached queries date back by ~ 10 years. It wasn't just that visible.

As you describe it, it seems the LONGVARCHAR or LONGVARBINARY
are not honored correctly when coming from ADO.

pls. Check the related class definition.
And be aware that once created as VARCHAR it can't be changed
dynamically to LONGVARCHAR on the fly. This type is a static definition.

score 0 · Answer 2 · 2023-03-30T16:33:38-04:00

SQL type TEXT maps correctly to %Stream.GlobalCharacter,
- anyhow this gets not granted on the way from ADO to IRIS-

it would be useful to check if the behavior is the same with a normal ODBC tool.
I tested recently the opposite direction from IRIS to WinSQL and that was OK.

Since transmitting streams always requires some chopping and assembling
at both ends not to break the buffers, it is important if the error persists outside ADO.

score 0 · Answer 3 · 2023-03-31T09:43:56-04:00

Thank you for your suggestions.

What do you mean by "normal ODBC Tool"? When i try to execute the same (long insert statement with more than 3.1 million characters) via iris odbc (on Windows), i get the same MAXString-errormessage as doing it via ado.

If you say, all queries are treated as cached queries, seams this is also true for odbc connections for iris.

score 0 · Answer 4 · 2023-03-31T06:58:25-04:00

I wrote a small test on VBS, but you can easily redo this code for your language. For details on working with streams for ADO, see the documentation.

Code on Visual Basic Script

adVarChar=200
adLongVarChar=201
adLongVarBinary=205

adParamInput=1
adCmdText=1
adExecuteNoRecords=128

Set cn=Createobject("ADODB.Connection")

cn.ConnectionString="DRIVER={InterSystems IRIS ODBC35}; SERVER=127.0.0.1; PORT=1972; DATABASE=USER; UID=_system; PWD=SYS;"
cn.open

Set cmd = Createobject("ADODB.Command")

with cmd
  .ActiveConnection = cn

  .CommandText = "CREATE TABLE dc.test(Name VARCHAR(50) NOT NULL,Notes TEXT,Photo IMAGE)"
  .Execute ,,adCmdText + adExecuteNoRecords

  .CommandText = "insert into dc.test(Name,Notes,Photo)values(?,?,?)"

  .Parameters.Append .CreateParameter("pName", adVarChar, adParamInput, 50, "blablabla")
  .Parameters.Append .CreateParameter("pNotes", adLongVarChar, adParamInput, 2147483647, ReadTextFile("C:\bigText.txt","Windows-1251"))
  .Parameters.Append .CreateParameter("pPhoto", adLongVarBinary, adParamInput, 2147483647, ReadBinaryFile("C:\bigImage.png"))

  .Execute ,,adCmdText + adExecuteNoRecords

end with


WScript.Echo "Succesfully!"

Function ReadBinaryFile(FileName)
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To get binary data.
  BinaryStream.Type = adTypeBinary

  'Open the stream
  BinaryStream.Open

  'Load the file data from disk To stream object
  BinaryStream.LoadFromFile FileName

  'Open the stream And get binary data from the object
  ReadBinaryFile = BinaryStream.Read
End Function

Function ReadTextFile(FileName, CharSet)
  Const adTypeText = 2

  'Create Stream object
  Dim BinaryStream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To get binary data.
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  If Len(CharSet) > 0 Then
    BinaryStream.CharSet = CharSet
  End If

  'Open the stream
  BinaryStream.Open

  'Load the file data from disk To stream object
  BinaryStream.LoadFromFile FileName

  'Open the stream And get binary data from the object
  ReadTextFile = BinaryStream.ReadText
End Function

score 0 · Answer 5 · 2023-03-31T10:31:17-04:00

Thank you for this snippet. I tried to insert a 4.3 million character stream (html with pictures as base64 included) and other data via insert using parameters and it worked just fine.

this could be a way, to make this work. But we would need to change the general behavior at some points, because at the moment the db specific updatemaker-Class of a db driver creates an sql, which is then sent to db by executeNonQuery-function of the specific db driver. Might be a huge breaking change.

score 0 · Answer 6 · 2023-03-31T13:08:43-04:00

There is nothing specific to IRIS here, except for the connection string.
The same code with minimal changes should work with other DBMS: Oracle, SQL Server, PostgreSQL. etc.
The Internet is full of examples of how to insert streams into the database via ADO.