Problem Inserting Bytea in postgresql

Question

I did this update to update a column in my company table, my column in the database is like bytea and I’m saving an image as byte[] ... in my company class, I have the variable Photo that is of the type Byte [] Foto

 public int Update(clsEmpresa E)
  {
     int r = 0;
     string sql = @"update empresa set emp_figura = ?;";

 string connString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
 clsConfigBanco.SERVERNAME, clsConfigBanco.PORT, clsConfigBanco.USERNAME, clsConfigBanco.PASSWORD, clsConfigBanco.DATABASENAME);

 using (NpgsqlConnection conexao = new NpgsqlConnection(connString))
 {
     conexao.Open();
     using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conexao))
     {
        NpgsqlParameter param = new NpgsqlParameter("emp_figura", 
        NpgsqlTypes.NpgsqlDbType.Bytea); 
        param.Value = (E.Foto == null ? null : (E.Foto.Length == 0 ? null : E.Foto));
        cmd.Parameters.Add(param);
        r = cmd.ExecuteNonQuery();
     }
     conexao.Close();
 }

     return r;
}

When I run this method the following error occurs ERROR: 42601: syntax error at or near ";" when I do the cmd.ExecuteNonQuery;

Would the error refer to using the NpgsqlTypes.NpgsqlDbType.Bytea related to the type of my parameter? What would be the correct way to save this byte Array in the database?

Answer 1

change

"update empresa set emp_figura = ?;"

for

"update empresa set emp_figura = :emp_figura;"

I’d put a where there with the primary key of the company as well. Even if you only have one, I believe it is better.

the ? is used when using an ODBC driver, at least on postgresql. Already with the Npgsql, you name the parameter in the query, with the prefix :