Matt,
I am using JsonPathReader and using the 'Schema Text' Property with a
schema defined in there. I could never figure out how to use any of the
other Access Strategies. It's an inventory system so we are extracting the
Json and splitting it into a json file for each row. Then using this
schema: {
"name": "insertSql",
"type": "record",
"fields": [
{
"name": "RfidNumber",
"type": "string"
},
{
"name": "CabinetName",
"type": "string"
},
{
"name": "ItemNumber",
"type": "string"
},
{
"name": "LotNumber",
"type": "string"
},
{
"name": "PurchaseOrderNumber",
"type": "string"
},
{
"name": "PurchaseOrderPrice",
"type": "float"
},
{
"name": "SupplierId",
"type": "string"
},
{
"name": "SupplierName",
"type": "string"
},
{
"name": "updatedate",
"type": "string"
}
]
}
I inserted the data into the table. I am not 100% on the uses of schemas so
I am not quite sure what you mean by using a schema to define the query.
On Tue, Aug 22, 2017 at 11:23 AM, Matt Burgess <[email protected]> wrote:
> 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
>
