I am trying to load EOD stock data into a table using this method:
public async Task<long> BulkInsertEodData(IEnumerable<EodData> records, string symbol)
{
var recordsProcessed = 0L;
using (var conn = await OpenConnection())
using (var trans = conn.BeginTransaction())
using (var comm = _factory.CreateCommand())
{
try
{
comm.Connection = conn;
comm.Transaction = trans;
comm.CommandText = INSERT_EOD;
var ps = AddParametersToInsertEodQuery(comm);
foreach (var p in ps) comm.Parameters.Add(p);
comm.Prepare();
foreach (var record in records)
{
comm.Parameters["@date_id"].Value = record.DateId;
comm.Parameters["@symbol"].Value = symbol.ToUpper();
comm.Parameters["@eod_close"].Value = record.EodClose;
comm.Parameters["@eod_high"].Value = record.EodHigh;
comm.Parameters["@eod_low"].Value = record.EodLow;
comm.Parameters["@eod_volume"].Value = record.EodVolume;
comm.Parameters["@eod_open"].Value = record.EodOpen;
comm.Parameters["@eod_split"].Value = record.EodSplit;
comm.Parameters["@eod_dividend"].Value = record.EodDividend;
comm.Parameters["@last_modified"].Value = DateTime.UtcNow;
await comm.ExecuteNonQueryAsync();
recordsProcessed++;
}
trans.Commit();
}
catch (Exception ex)
{
_logger.LogError(ex, "BulkInsertEodData(IEnumerable<EodData>)");
trans.Rollback();
}
}
return recordsProcessed;
}
The query text is as follows:
INSERT INTO public.eod_datas(
date_id,
stock_id,
eod_open,
eod_close,
eod_low,
eod_high,
eod_volume,
eod_dividend,
eod_split,
last_modified_timestamp
)
values
@date_id,
(select s.id from stocks s where s.symbol = @symbol limit 1),
@eod_open,
@eod_clos,
@eod_low,
@eod_high,
@eod_volume,
@eod_dividend,
@eod_split,
current_timestamp
on conflict (date_id, stock_id)
do update set
eod_open = @eod_open,
eod_close = @eod_close,
eod_low = @eod_low,
eod_high = @eod_high,
eod_volume = @eod_volume,
eod_dividend = @eod_dividend,
eod_split = @eod_split,
last_modified_timestamp = current_timestamp;
It's not my first rodeo with prepared statements, but I am doing a couple things different this time around (.NET Core, using DbProviderFactory) and I'm getting odd results.
The first couple of times through this method, I get an error to the effect of Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$1"
which is fairly mystifying in itself but the most mysterious of all is that the error actually goes away after a couple of method calls, and I start getting Npgsql.PostgresException (0x80004005): 26000: prepared statement "_p1" does not exist
consistently afterwords.
Can someone explain this behavior? What am I doing wrong? Where do I get more details into what "$1" is all about?
You are missing brackets around values being inserted. Postgres sadly will not tell you that it is expecting bracket before $1.
Correct syntax:
values (
@date_id,
(select s.id from stocks s where s.symbol = @symbol limit 1),
@eod_open,
@eod_clos,
@eod_low,
@eod_high,
@eod_volume,
@eod_dividend,
@eod_split,
current_timestamp)
on conflict (date_id, stock_id)
User contributions licensed under CC BY-SA 3.0