Knex with official oracledb driver

[tejzpr]

Hi, Seems the official oracledb driver support was there in knex from version 0.11.8 knex/knex#990. I was able to connect to Oracle 12 by installing oracledb nodejs driver in my AdonisJS project using the following configuration:

oracle: {
    client: 'oracledb',
    connection: {
      timeout: 10,
      host: 'localhost:1521',
      //port:'1521',                **//Declaring port here does not work**
      user: 'username',
      password: 'password',
      database: 'mydb'
    },
    debug: true
  }

But even after the connection got through, any queries made to OracleDB through the AdonisJS model resulted in an error. I am using a pre-defined table called 'USERS' (notice uppercase) with associated Model called Users. When the query is made to OracleDB it goes like this:
Error: select * from "users" - ORA-00942: table or view does not exist
Seems passing table names in quotes does not work with Oracle.. Is it possible to fix this issue from lucid so that we can add node/oracledb driver support to AdonisJS?

[thetutlage]

Is this the oracle db support issue? Or the table name needs to be fixed?

[tejzpr]

Table name passes inside double quotes to the DB like this: select * from "users"
Instead, it should pass like this : select * from users
Passing table names in quotes does not work with Oracle.

[thetutlage]

Does it work fine with knex?

[tejzpr]

Just tested with latest Knex v0.12.6, and the issue exists in Knex library. Meanwhile knex.raw("select * from USER"); works fine with NodeJS/OracleDB driver., Strong-oracle driver does not retrieve any data.

[thetutlage]

@tejzpr Seems to be an issue with knex. Would you mind reporting this issue with them?

[tejzpr]

Done #1785 ! :) Also please do add that OracleDB driver also supported in AdonisJS docs.

[thetutlage]

@tejzpr Does it only happens when table name case is mis-matched or everytime?

[tejzpr]

This issue occurs only if table name or field name case mismatch occurs. If table name & field name is of the same case in DB and Model then everything works fine. Verified this by overriding table name in model by using the following code.
static get table () { return 'USER' }

[tejzpr]

A possible solution to this issue could be that any Table name or Field name generated for Oracle data source be converted to uppercase before passing on to Knex query builder since Oracle internally saves table names & field names in upper case. i.e. even if a table is created in lower case like user_list in oracle it can still be accessed by running select * from USER_LIST;. But if a table is created in upper case like "USER_LIST" it cannot be accessed by running select * from user_list;

[thetutlage]

Nope the end user should be responsible for setting up right database table name, instead of framework deciding it

[thetutlage]

Since it's not an issue with lucid, neither with knex, closing the issue

[tejzpr]

Hi according to comment from the knex issue that I raised some days back, the case sensitivity to Oracle table names should be handled while creating queries. But according to this thread case support should be added to knex oracledb soon, hopefully :)

[thetutlage]

Since Adonis does not know whether the table name is lowercase or uppercase it seems to be hard to transform names on the fly. The best way is to define the tablename on the model.

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.