Hello,

We are using HanaDataAdaper to execute queries in our asp.net solution. The code was working fine. However we ran into a scenario, If variables are used with the SQL script then the sql queries are not executed properly and throw an exception. The same query works fine if we remove the variables from the script. Please refer the example below.

Also, the same query with variables works fine on HANA Studio. But it throws error when used in our application. Since we have used this kind of query at multiples places we want to make minimal changes to the handle this.

We have two questions:

1) Is there a way to execute the SQL script on Hana database with variables in it. If yes, can you give us an example?

2) If #1 is not possible, What are the alternatives/workarounds to achieve the above requirement with minimal changes?

Example:

SQL Query with variable “USR_ID”. below is the sample code snipped.

string sql= @"DO

BEGIN

DECLARE USR_ID INT;

USR_ID=1;

SELECT * FROM SYS_USR WHERE USR_ID= :USR_ID;

END";

System.Data.Common.DbCommand cmd = null;

DataSet myDataSet = newDataSet();

HanaConnection con = newHanaConnection(connectionString);

HanaDataAdapter myDataAdapter = newHanaDataAdapter(sql, con);

con.Open();

myDataAdapter.Fill(myDataSet);

con.Close();

Error: identifier must be declared: 1: line 6 col 53 (at pos 162). Refer attachment error.png

Thanks