That is neat idea, when working with XML/DDL based vdbs, in terms of development.
Teiid does support execution of anonymous procedure blocks DML Commands · Teiid Documentation for me this looks like bug.
You are seeing squirrel breaking up the query around the ; delimiter. You need to change to use a different delimiter under session properties -> sql
I didnt get it , In Session properties => SQL ,I have statement Separator as semicolon(;). I hope that is correct. Can you please suggest whats needs to be changed?
While thinking about suggested change. I got one more question. Can you please help me in understanding this.
VDB is unified platform to connect/access/update to different datasource using common Teiid SQL syntax. i.e teiid SQL Syntax is converted in to native source syntax at runtime using translator(JDBC Specifications). If my understanding is correct. Codes in my post are working fine in Store procedure. which means it should be working fine form Squirrel also, with out any syntax change?
yes, the understanding is right. What Steve is saying is, SquirreL when you executed the anonymous procedure is splitting the query into multiple queries and executing, where as Teiid expects that to be a single statement. The way to do it is making SquirreL think that the whole procedure is single block, since it splits at ";" change that preference to something else, then it should work correctly as you expect.
Thanks Steven & Ramesh. that worked. this will save lot of time in development