SQL Connector with variables in query
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...
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
Comments
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 ;-)
Comments
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
Another point: it would be easier to investigate with the error message from the log file(See Help -> Show log engine)
Jérôme