Caché SQL maximum supported length ?

Question

Question

wei ren · Sep 3, 2020

#SQL #Caché

Caché2010

Caché2016

Discussion (6)1

Log in or sign up to continue

Danny Wijnschenk · Sep 3, 2020

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

0 0

David Van De Griek · Sep 4, 2020

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))

1 0

score 1 · Answer 1 · 2020-09-03T02:53:45-04:00

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

score 0 · Answer 2 · 2020-09-03T03:46:46-04:00

wei ren · Sep 3, 2020

Thanks,

but i mean SQL statement length

0 0

score 0 · Answer 3 · 2020-09-03T04:45:36-04:00

Eduard Lebedyuk · Sep 3, 2020

Probably 3,641,144.

Calling @Dan Pasco

0 0

score 0 · Answer 4 · 2020-09-03T16:45:49-04:00

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.