If your incoming data is already in fields (JSON, e.g.) and not a SQL statement, then the statement.type should be "insert" rather than "sql". The "sql" type is for passing in explicit SQL statements to execute rather than taking a record and generating the appropriate SQL statement from the fields and the statement.type.
If you are trying to generate your own SQL and execute that, then you'd want to try something like the solution I outlined before, where you put the SQL in a field such as "statement", set statement.type to "sql" and Field Containing SQL to "statement". Does that make sense? Or am I misunderstanding what you are trying to do? Regards, Matt On Tue, Aug 22, 2017 at 11:32 AM, Austin Duncan <[email protected]> wrote: > 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 > >
