Skip to Content
0

What are the alternatives to make SQL Query with variables to work on HANA database?

Dec 26, 2016 at 01:54 PM

69

avatar image

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

error.png (14.1 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Jakub Holasek Dec 26, 2016 at 02:03 PM
0

Hello,

check this out, it might be helpful, especially the part about placeholder for input parameters:

http://saphanatutorial.com/sap-hana-variables-and-input-parameters/

Also check the comments below the article

Share
10 |10000 characters needed characters left characters exceeded