Skip to Content
-1

Executing dynamic SQL from .NET error

Apr 05 at 06:23 PM

43

avatar image
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;");
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.

0

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.

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

2 Answers

Sunil Negi Apr 05 at 08:42 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Sunil Negi Apr 06 at 05:00 AM
0

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 (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx) 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.

0

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.

0