SQL Connector with variables in query

Q&A
1

Hi,

Could someone please explain or point me in the right direction as to solve the following.

I have a MSSQL Server 2008 connector and I would like to be able to add variables to my sql statement. As it stands I have the following query:

  SELECT TOP 1 Document_Code FROM table

Which works fine but when I try to do the following through the script window:

  "SELECT TOP 1 Document_Code FROM table WHERE Document_Code = '" +MyVariable + "'"

it returns errors and will not work. In the script window do I just pass the string of the query as I am doing or do I have to actually write GROOVY/Java code to pass this as a query to the database?

 

Thanks in advance...

 

2

I have found that, to pass variables correctly in the query window without using a script you can push ctrl+space as the tip suggests and select your variable. For instace it will be something like: ${MyVariable}

 

I have managed to find this but can still not get it to work. I think there may be a bug where Bonita fails because of variables that I have declared.

My query looks as follows: 

DECLARE @retVal VARCHAR;
DECLARE @var1 VARCHAR
 SELECT @retVal = COUNT(*)
  FROM TABLE
   WHERE Document_Code = 'code1' 
   IF (@retVal > 0) 
   BEGIN 
   set @var1 = 1 
   END 
   ELSE 
   BEGIN 
   set @var1 = 0 
   END 
   SELECT @var1 as num

This should return 1 or 0 but instead NULLS are returned or just errors

~~~~~~~~~~~~~~~~~~~~~~EDIT~~~~~~~~~~~~~~~~~~~~

Hi Jerome,  I've managed to get it to work with the following: 

SELECT TOP 1
(SELECT Document_Code from table where Document_Code = '${documentCode}') as check
from etl_history_process

then in the next output page I make my global variable = 

String check;
String results;

while(resultset.next()){
    results = resultset.getString(1);
}

if(results == null){
    check = "1";
}
else{
    check = "0";
}
return check;

 

Hopefully this can help someone in the future

3

My 2 cents:

Our company guidelines specify that if the SQL query is not VERY basic (e.g., here a Studio var is involved), we must switch editor to script.

In such exemple we would have a script that would look like:

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

String myQuery = "SELECT count(*) as num ";
myQuery= myQuery +" FROM Constant ";
myQuery= myQuery +" WHERE ConstantName = '" +kpiName + "' ;";
return myQuery;

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

then in the 'output operation' page of the connector wizard,the process var 'takes value of':

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

resultset.next();
return Integer.valueOf(resultset.getInt("num"));
===============================

Note: The piece of code returns an Integer;

The purpose of this coding guideline (using a script to define the sql query) is to be able to evaluate it beforehand. Moreover, it is easier to debug.

I hope that helps.

Jérôme

4

Another point: it would be easier to investigate with the error message from the log file(See Help -> Show log engine)

Jérôme

 

5

Hi Dean,

SQL Syntax (I've checked with MySQL, not MSSQL, though)

a- the declare statement in SQL is to be used in Stored Procedure. In MySQL. So you shouldn't be using it in a query.

b- A ';' seems to be missing at line #2 (at the end of "DECLARE @var1 VARCHAR") and may be at the end of the whole query.

=> If you really need such a complexe SQL script, I suggest you define a Stored proc... which has nothing to do with Bonita ;-)