Is SQL Remote passthrough mode picky about DML statement delimiters?

[VolkerBarth]

When using DML statements within a SQL Remote passthrough session with 12.0.1.4314, these DML statements were rejected with SQLCODE -707 ("Statement is not allowed in passthrough mode").

Initially, I was surprised because we have eventually used such DML statements within passthrough mode in older versions without problems (*), and I wondered whether I had noticed a possible limitation in v12 I had not been aware of.

A closer look revealed that the error messaged appeared when two INSERT or UPDATE statements followed each other without a statement delimiter, such as

insert into MyTable values ( 1, 'Value 1')
insert into MyTable values ( 2, 'Value 2')
...
go

Adding a semicolon after each statement fixed the problem easily.

Outside passthrough mode, such DML statements are accepted without delimiters (as long as they remain unambiguous, of course), I guess as T-SQL syntax.

So is that need for a delimiter a known restriction in SQL Remote Passthrough mode?

---

(*) Aside: One might ask why we use DML statements in a passthrough session and do not simply run them "normally" and let SQL Remote distribute the values. - Well, it's handy when you are about to alter an existing, non-empty table to add a NOT NULL column and want to handle that within one passthrough session, such as

PASSTHROUGH FOR SUBSCRIPTION TO ...;
SETUSER ...;
ALTER TABLE MyTable ADD MyNewColumn INT NULL; -- can't be NOT NULL while empty
UPDATE MyTable SET MyNewColumn = <whatever>;
ALTER TABLE MyTable ALTER MyNewColumn NOT NULL;
...
COMMIT;
PASSTHROUGH STOP;

[Former Member · Accepted solution]

Without the semicolons, the 2 inserts are treated as a SQL Batch and that is the source of the issue you are seeing. Putting GO statements between and after them would also work to correct this.

In one sense this is covered by the description of that error

[VolkerBarth · Accepted solution]

OK, that's understood. So I guess older versions were less strict w.r.t. batches and delimiters...

[Former Member · Accepted solution]

More correctly ... it is a documented limitation as well

[VolkerBarth · Accepted solution]

Well, as stated, I was more surprised because it had worked with older versions like v8, and I'm quite sure the documented limitations have not changed in-between...