CODEWITHSUNDEEP
c#.netodbchana
Sara Menoncin
Sara Menoncin

Reputation: 93

Parameter/Column (2) not bound Exception when connecting C# application to SAP HANA database

I'm creating a C# application which connects to SAP HANA 2.0 database.

In my pc I installed HDBODBC and HDBODBC32 driver, both of 2.02 version.

What I'm trying to do is to create a query in which there is a parameter which is referenced in different occasions.

Here's a sample code:

string query = "SELECT :p0, :p0 FROM DUMMY";
IDbCommand dbCommand = hanaConnection.CreateCommand();

dbCommand.CommandText = query;

IDataParameter param = dbCommand.CreateParameter();
param.Value = "A";
param.ParameterName = ":p0"; 

dbCommand.Parameters.Add(param);

hanaConnection.Open();

IDataReader dataReader = dbCommand.ExecuteReader();

while (dataReader.Read())
{
    ReadSingleRow((IDataRecord)dataReader);
}

Just to make it clearer, object hanaConnection implements IDbConnection.

When I execute the statement IDataReader dataReader = dbCommand.ExecuteReader(); an exception is raised:

Sap.Data.Hana.HanaException

HResult=0x80004005

Message=Parameter/Column (2) not bound.

Source=SAP HANA Data Provider for Microsoft ADO.NET

StackTrace:

at Sap.Data.Hana.HanaCommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader)

at Sap.Data.Hana.HanaCommand.ExecuteReaderBatch(CommandBehavior behavior)

at Sap.Data.Hana.HanaCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

...

I tried to create execute a pure SQL command in HANA Studio in order to understand if it's possible to reference a parameter more than once in a SELECT statement and I got a correct result.

This is the SQL statement I executed:

CREATE PROCEDURE PROC (IN var1 INT, IN var2 INT)
AS
BEGIN
   SELECT :var1, :var2, :var2 FROM DUMMY;
END;

CALL PROC (var1=>1, var2=>2);

Why an exception occurs if I use .NET ODBC interface? Is it the expected behaviour? Do I have to create necessarily more then one parameter even if they are of the same type and value?

Thanks

Upvotes: 1

Views: 2488

Answers (1)

Lars Br.
Lars Br.

Reputation: 10388

The SAP HANA ADO.net driver does not support named parameters for SQL statements. Instead, it uses positional parameters that show up in the statement text as question marks (?).

See the reference documentation on that SAP HANA Data Provider for Microsoft ADO.NET API Reference/HanaParameter class/ParameterName property.

For your requirement, that means, that you have to bind the same value for each occurrence of the parameter in the statement that should have the same value.

Note that SQLScript does support only named parameters, which is why your procedure example code works without problems.
BTW: if you just want to quickly try out SQLScript code, you can use anonymous blocks instead of creating procedures.

Upvotes: 4

Related Questions