Update record in databricks sql table from C#.Net in visual studio 2022 using ODBC

[codeexplorer]

I am trying to make a backend method call work which connects to database and updates the record in a table. The method call works perfectly and it is not throwing any error but at the same time it does not update any record in the table either.

Note:

1. SELECT query returns the data as expected.
2. It is insert and update that does not work as expected.

Here is my method snippet which connects the database and tries to execute update query with ExecuteNonQuery() method.

public void Update(Insights ins)

{

string DbConnection = ConnectionSetting.ConnectDatabricks();

string query = @"UPDATE Archery SET Insight = '?', Title = '?' WHERE Subject = '?'";

try

{

using (OdbcConnection connection = new OdbcConnection(DbConnection))

{

connection.Open();

OdbcCommand command = new OdbcCommand(query, connection);

command.CommandText = query;

command.Parameters.Add("@insight", OdbcType.NVarChar).Value = ins.Insight;

command.Parameters.Add("@title", OdbcType.NVarChar).Value = ins.Title;

command.Parameters.Add("@subject", OdbcType.NVarChar).Value = ins.Subject;

command.ExecuteNonQuery();

}

}

catch (Exception)

{

throw;

}

}

The table schema is as below:

--------------------------------------------------------------------------

Column Name Type

--------------------------------------------------------------------------

InsightId BigInt (primary key)

Insight String

Title String

Subject String

--------------------------------------------------------------------------

Is there anything more to it? Kindly suggest.

[Debayan]

Hi, What was the error thrown?

Please tag @Debayan​ with your next response which will notify me. Thank you!

[codeexplorer]

Hi @Debayan Mukherjee​ ,

Thank you for responding. when my debug point reaches command.ExecuteNonQuery(); it does not throw any error. Instead it returns -1, meaning no row has affected.

Moreover, I am sharing my connection parameters if there is any difference (it is very unlikely as I can retrieve data without any issue)

public static string ConnectDatabricks()
        {
            OdbcConnectionStringBuilder odbcConnectionStringBuilder;
            odbcConnectionStringBuilder = new OdbcConnectionStringBuilder
            {
                Driver = "Simba Spark ODBC Driver"
            };
            odbcConnectionStringBuilder.Add("Data Source Name", "sparkdb");
            odbcConnectionStringBuilder.Add("Host", "adb-blah.azuredatabricks.net");
            odbcConnectionStringBuilder.Add("Port", "443");
            odbcConnectionStringBuilder.Add("SSL", "1");
            odbcConnectionStringBuilder.Add("ThriftTransport", "2");
            odbcConnectionStringBuilder.Add("AuthMech", "3");
            odbcConnectionStringBuilder.Add("UID", "token");
            odbcConnectionStringBuilder.Add("PWD", "somepassword");
            odbcConnectionStringBuilder.Add("HTTPPath", "/sql/1.0/warehouses/12345678");
 
            return odbcConnectionStringBuilder.ConnectionString;
        }

[Atanu]

Do you see any log/output/err on cluster driver log (https://docs.databricks.com/clusters/clusters-manage.html#view-cluster-logs) @Binoy K​ 

[codeexplorer]

I found a temporary work around. Instead of passing the value through the parameters, I passed the value directly in query like below. I know it is not the ideal way but at this time this is working. If I do not pass the value as shown below, the logs in databricks reads question mark (?) as values passing in update statement.

public void Update(Insights ins)
 
{
 
string DbConnection = ConnectionSetting.ConnectDatabricks();
 
string query = @"UPDATE Archery SET Insight = '{ins.Insight}', Title = '{ins.Title}' WHERE Subject = '{ins.Subject}'";
 
 
 
try
 
{
 
using (OdbcConnection connection = new OdbcConnection(DbConnection))
 
{
 
connection.Open();
 
OdbcCommand command = new OdbcCommand(query, connection);
 
command.CommandText = query;
 
 
 
command.ExecuteNonQuery();
 
}
 
}
 
catch (Exception)
 
{
 
throw;
 
}
 
}