Austin,
What are you using for a record reader and schema for
PutDatabaseRecord? In order to execute SQL using PutDatabaseRecord,
you have to specify a "Field containing SQL", and the incoming
record(s) must have a field with that name. The value of that field
(for each record) will be executed.
What I've done in the past is to put the whole SQL statement in a JSON
doc: {"query": "INSERT INTO table (column1 ,column2, column3, column4)
VALUES() ON CONFLICT (rfidnumber) DO UPDATE"} then I set Field
Containing SQL to "query", and use a JsonPathReader specifying a
"query" field with a path of $.query, or just a JsonTreeReader, either
reader using a schema with a single string field called "query".
IMO an improvement would be nice to add a SQLReader that could split
on newlines or semicolons or whatever, and each "record" would contain
a SQL statement with the specified field name. That would save the
trouble of having to temporarily convert your SQL statement(s) into a
format that an existing Reader can recognize.
Regards,
Matt
On Tue, Aug 22, 2017 at 11:12 AM, Austin Duncan
<[email protected]> wrote:
> In my flow I am pulling data from a Json, splitting the Json and then
> inserting that into a postgres table using the putdatabaserecord processor.
> I have been using the insert statement option and it has been working fine
> but now I am trying to figure out how to do a INSERT INTO table ON CONFLICT
> UPDATE statement. I have the statement.type attribute set to SQL and am
> trying to do the query:
>
> INSERT INTO table (column1 ,column2, column3, column4)
>
> VALUES()
>
> ON CONFLICT (rfidnumber) DO UPDATE;
>
> I am getting the error 'Record schema does not contain filed containing
> SQL'. So two th Any help would be appreciated.
>
> Thanks,
>
> Austin
