Skip to Content
avatar image
Former Member

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

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Dec 26, 2016 at 02:03 PM

    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

    Add comment
    10|10000 characters needed characters exceeded