Solved

UpdateCommand.Parameters have wrong value in ADO.NET Provider for Presto

  • 28 November 2023
  • 4 replies
  • 77 views

Userlevel 3
Badge

Following the example code for Update, we have this against one of our Hive transactional datastores:

 PrestoDataAdapter dataAdapter = new PrestoDataAdapter("SELECT _id, name FROM hive.transactional_schema.[TableName]", connection);

dataAdapter.UpdateCommand = new PrestoCommand(
"UPDATE hive.transactional_schema.[TableName] SET name = @name " +
"WHERE _id = @_id", connection);

dataAdapter.UpdateCommand.Parameters.Add(new PrestoParameter("@name", "name", DbType.String));
dataAdapter.UpdateCommand.Parameters.Add(new PrestoParameter("@_id", "_id", DbType.String));
dataAdapter.UpdateCommand.Parameters[1].SourceVersion = DataRowVersion.Original;

DataTable table = new DataTable();
dataAdapter.Fill(table);

DataRow firstrow = table.Rows[0];
firstrow["name"] = firstrow["name"] + "-Updated";

dataAdapter.Update(table);

firstrow shows the updated name property, but the query that gets sent to Trino is:

UPDATE "hive"."transactional_schema".[TableName] SET "name" = 'name' WHERE "_id" = '_id'

so I guess I don’t understand how PrestoParameter is meant to work...from the example it looked like it was meant to map the property value (e.g. “name-Updated”) to the query parameter (e.g. @name), but it looks like it’s just passing in the 2nd param of PrestoParameter (e.g. “name”) as the value in the query.

icon

Best answer by Luçjano Capo 8 December 2023, 10:55

View original

4 replies

Userlevel 7
Badge

Hi @rocketmaniac,

 

Thank you for reaching out.

 

Our development team is already aware of this issue and actively working on a fix.

 

I'll make sure to update you as soon as there are any developments, and we anticipate resolving this shortly.

Userlevel 7
Badge

Hi @rocketmaniac,

 

After discussing this case with the development team, we concluded that there has been a documentation bug with this procedure:

dataAdapter.UpdateCommand.Parameters.Add(new PrestoParameter("@name", "name", DbType.String));

This is one of the ways we can call a new Presto Parameter, using these arguments → the name of the variable, a value for that variable and the data type. This method allows you to set a value directly to your variable and this defies the purpose of changing the value in this next part:

firstrow["name"] = firstrow["name"] + "-Updated";

Another way we can call a new Presto Parameter is this way:

dataAdapter.UpdateCommand.Parameters.Add(new PrestoParameter("@name", DbType.String, "name"));

Using these parameters → the name of the variable, the data type and the source column. This way, you will map the source column and will be able to change the value below.

 

Please try this method and let me know how it goes. 

Userlevel 3
Badge

That worked...thanks to you and the team for looking into this, Luçjano!

Userlevel 7
Badge

I am glad I was able to help @rocketmaniac!

Reply