cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

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

codeexplorer
New Contributor II

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.

4 REPLIES 4

Debayan
Esteemed Contributor III
Esteemed Contributor III

Hi, What was the error thrown?

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

codeexplorer
New Contributor II

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;
        }

ExecuteNonQuery

Atanu
Esteemed Contributor
Esteemed Contributor

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
New Contributor II

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;
 
}
 
}

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.