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...?
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.