0

I'm trying to query an Oracle table with Loopback4 and TypeORM but i ORA-00942: table or view does not exist. Morover, after a while, i get another error like this:

'Cannot create a new connection named "default", because connection with such name already exist and it now has an active connection session.'

I'm new to Loopback4 and Nodejs - Typescript, so I called the createConnection method inside the controller.

Maybe i should call It somewhere centralized, but i don't know where and how to centralize this inside my project.

this is my entity

import { Entity, Column, PrimaryColumn, OneToOne } from "typeorm";

@Entity("Pratica")
export class Pratica {
  @PrimaryColumn()
  protocolloaci: number;

  @Column()
  codicepratica: string;

}

my controller method

@get('/pratiche/{id}', {
    responses: {
      '200': {
        description: 'Pratica model instance',
        content: { 'application/json': { schema: getModelSchemaRef(Pratica) } },
      },
    },
  })
  async findById(@param.path.number('id') id: number): Promise<Pratica> {

   return new Promise((resolve) => {

      createConnection({
        type: 'oracle',
        host: '10.64.2.226',
        port: 1521,
        sid: 'npra02s.svidbs003std',
        username: 'sua03e',
        password: 'iniziale',
        entities: [
          Pratica
        ],
        logging: true
      }).then(async connection => {
        let praticaRepository = connection.getRepository(Pratica);
        const pratica = await praticaRepository.find({ protocolloaci: id });
        resolve(pratica[0]); // return using resolve
      }).catch(error => console.log(error));
    });

  }

thank you

Yanosh
  • 368
  • 5
  • 15

1 Answers1

0

Does sua03e own the table you're trying to query? If yes, does the case of the table name match what's in the database (note that Oracle uppercases names by default)?

The "default" error message doesn't seem correct. Was the error prefixed with 'NJS-NNN'? If not, perhaps the error is coming from Loopback and not the driver.

You've put createConnection inside of a function that uses a connection to do work. I generally advise against this. It's better to create a pool in the initialization logic that's executed when you start the application. Then you only call getConnection on the pool inside the function that uses the connection. This is both more performant and easier to maintain.

Even though you're using Loopback, you might benefit from reading and working through this series on creating a REST API with Node.js and Oracle Database: https://jsao.io/2018/03/creating-a-rest-api-with-node-js-and-oracle-database/

Dan McGhan
  • 4,479
  • 1
  • 11
  • 15
  • Yes sua03e owns the table, but my known is that Oracle is not case sensitive, am I wrong? – Yanosh Sep 30 '19 at 21:14
  • There are no prefixes on the error message. Thank you for the suggestion about createConnection. I still don’t know where is the best place to do it with Loopback. – Yanosh Sep 30 '19 at 21:16
  • Oracle is not case sensitive by default. However, since Oracle supports case sensitive object naming, the tools that work with Oracle are usually case sensitive because they don't know what the user has done (they can't just uppercase something because they could then be working with the wrong object). – Dan McGhan Oct 01 '19 at 14:14
  • Another thing I just noticied is that you're combining async/await with promises. I'd recommend just using async/await if possible. For example, `findById` is async, so it returns a promise automatically. Within the function, you should use `await` to await the return of other functions that return promises so just `await createConnection(...`. See this for more info: https://jsao.io/2017/07/how-to-get-use-and-close-a-db-connection-using-async-functions/ – Dan McGhan Oct 01 '19 at 14:19
  • Sorry, I don't know enough about Loopback to help with problems there. – Dan McGhan Oct 01 '19 at 14:21