INSERT on Oracle with error. What is the right way to do?

When attempting to insert into Oracle using the Adonis Database, an error is generated for all fields of type string.

JSON Submitted:
{
“codepi”: “1”,
“codoem”: “5”,
“datcer”: “01/01/2007”,
“cerapr”: “2586”,
“unimed”: “Size”,
“medepi”: “P”,
“desepi”: “Ear Protector 01”
}

Code in Controller:
‘use strict’

const Database = use (‘Database’)

class EstoqueEpiController {
async store ({response, request}) {
const data = request.only ([
‘codepi’,
‘codoem’,
datcer,
‘cerapr’,
‘unimed’,
‘medepi’,
‘desepi’,
])

response = await Database.connection ('oracle')
  .table ('USU_T096EPI')
  .insert ({
    USU_CodEpi: data.codepi,
    USU_CodOem: date.codoem,
    USU_DatCer: data.datcer,
    USU_CerApr: data.cerapr,
    USU_UniMed: data.unimed,
    USU_MedEpi: data.medepi,
    USU_DesEpi: data.desepi,
  })

Database.close ()

return response

}
}

module.exports = EpiController

Error returned:
“message”: "insert into " USU_T096EPI \ “(” USU_CerApr \ ", " USU_CodEpi \ ", " USU_CodOem \ ", " USU_DatCer \ ", " USU_DesEpi \ ", " USU_MedEpi \ ", \ "USU_UniMed ") values ​​(: 1,: 2,: 3,: 4,: 5,: 6,: 7) - ORA-00904: \ "USU_UniMed ": invalid identifier "

How do I INSERT in Oracle with a string field?

Try to google about how to insert strings with quotes into an Oracle database.

Well it seems trick to me because the way that you insert a register is out of the patterns. Before you do anything, please make sure that adonis migration works and after that create a model and then try wether insertion works or not.

@willmartinsmg Welcome to community! (I’m late for party :frowning: )

I hope you got your problem solved by now :slight_smile: If so it would be great if you could post solution here.

If not, here’s my 2c

Quotation marks in your sent JSON are off, instead of ", but it might be only in here.
Same thing in error message, does it actually have in there? Some reason might start to unfold from there.

Another idea is that log out (or use debugger) data that you are about to insert.

const insertData = {
    USU_CodEpi: data.codepi,
    USU_CodOem: date.codoem,
    USU_DatCer: data.datcer,
    USU_CerApr: data.cerapr,
    USU_UniMed: data.unimed,
    USU_MedEpi: data.medepi,
    USU_DesEpi: data.desepi,
  }
// Log or debug it out to see if it is correct
console.log(insertData)

response = await Database.connection('oracle')
  .table('USU_T096EPI')
  .insert(insertData)

Hello!

Thanks so much for the help.

In the application debug I found the error.

At the time of insert is being informed the column with quotes:

I created a table with only one field to do the test

`sql insert into "USU_TTESTE" ("usu_numemp") values (99) `

How do I give Adonis a direct command without going through Query Builder or Lucid?

I believe that solves my problem.

1 Like
const Database = use('database')

await Database.raw('raw query in here')