Caché SQL maximum supported length ?


The maximum length of a column is defined by the MAXLEN parameter in the class definition.
The maximum string length is 32,767 and 3,641,144 if long strings are enabled.
If you use Streams as data type, you don't have a limit and can use Substring(column, from, maxlen) in SQL


but i mean SQL statement length

When i use %SQL.Statement, and I pass sql to the %Prepare method as an array, it will start to fail  around 11,400 characters

There are a number of limitations on the size of an SQL statement I think, maximum string length is one of those limitations. It is based on the normalized "signature", including the statement text, as a single string, even when you pass the source statement as an array. I haven't worked in this area for several years so I can't speak to the details.

Can you be a little more specific? Are you are asking about embedded SQL, dynamic sql, ODBC, JDBC,  or limits for each interface?

The answer is different for each, although I'm not sure there is an exact length limit.  For embedded SQL, I don't believe there is any limit other than the maximum size of the code you can define for a .MAC routine or class method.

I believe for dynamic SQL the max length should be pretty close to the maximum string length supported by your Caché instance.  I was able to Prepare a using %SQL.Statement that was a bit over 3,600,000 characters in length (using IRIS, I don't have immediate access to a Caché instance))