Skip to Content

Executing dynamic SQL from .NET error

Apr 05 at 06:23 PM


avatar image
Former Member
Hi All,

I am trying to check if a table exists or not in HANA from .NET. So I write a dynamic SQL but executing it results in the error below:

identifier must be declared: 1: line 8 col 21 (at pos 222) at Sap.Data.Hana.HanaCommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader) at Sap.Data.Hana.HanaCommand.ExecuteScalar()

Here is the code I am trying to build:

> Code to execute the code

using (HanaCommand cmd = new HanaCommand(sql, conn))
    int ret = (int) cmd.ExecuteScalar();
    return ret;

> Code to build the SQL to execute:

StringBuilder sql = new StringBuilder();
      declare rowcount INT;
      select count(*) into rowcount from tables where table_name like 'my_table' and schema_name = 'my_schema';
      if :rowcount = 0 then
         select 0 from dummy;
         select 1 from dummy;
      end if;

10 |10000 characters needed characters left characters exceeded

Why do you use dynamic SQL here? A SELECT with parameters would be doing the job alright.

Former Member

The dynamic SQL is used here only as a test bench - my requirement is to create a table from .NET if it is missing.

This code I pasted is only to test part 1, the existence of the table.

Though unfortunately I am back to square 1. I again get "identifier must be declared" error.

* Please Login or Register to Answer, Follow or Comment.

2 Answers

avatar image
Former Member Apr 05 at 08:42 PM

I think I have a tentative answer to this. The way this SQL will be interpreted is by executing each line (delimited by ;) as its own command.

That might be a behaviour of the ExecuteScaler command. So all multi-line logic should be executed with ExecuteNonQuery instead. I will try that next.

10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 06 at 05:00 AM

Pretty interesting that the question gets downvoted. For something that SAP SQL cannot do :)

Show 2 Share
10 |10000 characters needed characters left characters exceeded

I downvoted it because the question is not well prepared and the approach you've shown so far looks rather overly complicated to me.

Looking at the ADO.NET reference here ( I don't see what's stopping you from adopting that approach to check for the existence of a DB object.

Without having a .NET environment to check this I would guess, that the code would look similar to this:

static public int countTables(string tableName, string connString)
    Int32 noOfTables = -1;
    string sql =
        "select count(*) as tablecnt from tables "
+" where table_name like ? and schema_name = 'my_schema';"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@tableName", SqlDbType.VarChar); cmd.Parameters["@tableName"].Value = tableName; try { conn.Open(); noOfTables = (Int32) cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return (int) noOfTables; }

To me, that looks straightforward.

Former Member

Yes it indeed is simpler. But please remember that I can achieve the same in even less # of lines by simply restricting myself to the following in my original code:

select count(*) from tables where table_name like 'my_table' and schema_name = 'my_schema';

Notice I simply removed the variable "rowcount"? That is all I had to do, if getting the existence of table or not was my purpose. But as I mentioned in my earlier comment. The goal is to actually create the table if it is not present.

This is why I need to add a script-like logic and execute via ExecuteNonQuery. The table definition is dynamic and based on configuration- but I will deal with that later once I find a way of conditionally creating the table itself.