topic Re: Update record in databricks sql table from C#.Net in visual studio 2022 using ODBC in Data Engineering

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

codeexplorer — Mon, 03 Apr 2023 22:16:57 GMT

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:

SELECT query returns the data as expected.
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.

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

codeexplorer — Tue, 04 Apr 2023 15:10:33 GMT

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

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

Atanu — Tue, 04 Apr 2023 18:05:18 GMT

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

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

codeexplorer — Wed, 12 Apr 2023 21:49:02 GMT

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

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

Debayan — Tue, 04 Apr 2023 05:16:27 GMT

Hi, What was the error thrown?

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