· Mar 29, 2023

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

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

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?
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.

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.

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.

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

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.