Skip to Content
avatar image
-1
Former Member

Executing dynamic SQL from .NET error

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))
{
    conn.Open();
    int ret = (int) cmd.ExecuteScalar();
    return ret;
}

> Code to build the SQL to execute:

StringBuilder sql = new StringBuilder();
sql.AppendFormat(
  @"do
      begin
      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;
      else
         select 1 from dummy;
      end if;

    end;");
Add comment
10|10000 characters needed 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.

  • Get RSS Feed

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.

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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.