postgres bytea sql with parameter error on inserting and/or updating with one sql script c# .net

Question

I have problems on inserting/updating a bytea stream into a postgres table.

insert and update is working fine if I try this seperately ..

Both together ... an error occurs ()..

Here the working example...

//INSERT ... is working! 
string MySqlInsertUpdateCommand = $"INSERT INTO ic_plc_streams(plcidx, stream, mytimestamp, 
streamlength) VALUES('{PlcIdx}',@stream,'{DateTime.Now}','{MyStream.Length}'); ";

//UPDATE ... is working!
string MySqlInsertUpdateCommand = $"UPDATE ic_plc_streams SET stream = @stream, 
mytimestamp='{DateTime.Now}', streamlength='{MyStream.Length}' WHERE plcidx='{PlcIdx}'; ";
<br>
//runs twice...
using (var command = new NpgsqlCommand(MySqlInsertUpdateCommand, conn))
{
    NpgsqlParameter param = command.CreateParameter();
    param.ParameterName = "@stream";
    param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea;
    param.Value = MyStream;
    command.Parameters.Add(param);
    conn.Open();
    command.ExecuteNonQuery();
}


<br>
//not WORKING



//insert or update!
string MySqlInsertUpdateCommand = $"do $$ begin UPDATE ic_plc_streams SET stream = @stream,         
mytimestamp='{DateTime.Now}', streamlength='{MyStream.Length}' WHERE plcidx='{PlcIdx}'; " +
$"if not found Then INSERT INTO ic_plc_streams(plcidx, stream, mytimestamp, streamlength)     
VALUES('{PlcIdx}',@stream,'{DateTime.Now}','{MyStream.Length}'); " +
$"End if; end$$";




using (var command = new NpgsqlCommand(MySqlInsertUpdateCommand, conn))
{
    NpgsqlParameter param = command.CreateParameter();
    param.ParameterName = "@stream";
    param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea;
    param.Value = MyStream;
    command.Parameters.Add(param);
    conn.Open();
    command.ExecuteNonQuery();
}

Errormessage: (postgres 9.4)
{"42883: Operator existiert nicht: @ bytea"}
(UPDATE ic_plc_streams SET stream = @stream, mytimestamp='23.10.2023 14:27:54', streamlength='28' WHERE plcidx='0'
)
Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.

Maybe someone can help...?

Answer 1

There is no parameters in a DO block. You can create a real function or procedure. You can also write an upsert statement that will either insert or update the record.