How to execute queries from Squirrel which has input vaiables

[ragava28]

Hi All,

I am trying to execute below lines of code from Squirrel Tool  by connecting to VDB(which has only 1 data source),Which always fails saying expecting something ,but if we package this into store procedure in VDB it works fine. I am not sure is going wrong while executing from Squirrel.

1.     Can we execute queries from Squirrel which has input parameters as variables? as shown in below code. So that I can build complete query in Squirrel & paste it as it is to the VDB store procedure.

Reason for my question. every time  I build long queries in Squirrel & converting them to store procedure is taking more time. A good example of this would be SQL server management Studio

Please share your suggestions on this.

======================================================

BEGIN

  DECLARE string VARIABLES.varUserName = 'Test';

  SELECT * FROM AspNetUsers Where UserName=VARIABLES.varUserName;

END;

=======================================================

[rareddy]

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.

[shawkins]

You are seeing squirrel breaking up the query around the ; delimiter.  You need to change to use a different delimiter under session properties -> sql

[ragava28]

Steven,

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?

[rareddy]

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.

[ragava28]

Thanks Steven & Ramesh. that worked. this will save lot of time in development