[TypeScript] Type issue with ODBCStatement?

[gdelory]

Hi, I'm not sure it's really the right place to open this issue since it concerns the TypeScript type definition, but I believe they are also maintained by this team? If not I can open the issue against DefinitelyTyped.

I think there is an issue on the return type of ODBCStatement.execute in the type definition, it doesn't return the same thing than the real code seems to return (ODBCResult I believe), and also doesn't match the example given in the documentation in the quick example which shows:

main();
async function main() {
  try {
    let conn = await ibmdb.open(cn);
    await conn.query("drop table mytab").catch((e) => {console.log(e);});
    await conn.query("create table mytab(c1 int, c2 varchar(10))");
    await conn.query("insert into mytab values (?, ?)", [3, 'ibm']);
    let stmt = await conn.prepare("select * from mytab");
    let result = await stmt.execute();
    data = await result.fetchAll(); // <== fetchAll doesn't exist on { result: any[]; outparams: any }
    console.log("result = ", data);
    await result.close();
    await stmt.close();
    await conn.close();
  } catch(e) {
      console.log(e);
  }
}

result being of type { result: any[]; outparams: any }, this doesn't copile in TypeScript.

I can make it work either by switching to the Sync version, which seems correct, or force a cast like:

const result = await stmt.execute([369]) as unknown as ODBCResult

But even when doing this, then apparently fetchAll except an options parameter, which is not used in the example either (and I did't use it in my code either before moving to TypeScript, so I don't think it is needed).

Am I missing something here?

Please provide below information while opening an issue to understand your problem

• Operating System Name where ibm_db is installed:
• Target Db2 Server Version or output of db2level command from Db2 database system:

For non-Windows system, output of below commands from terminal:

uname uname -m node -v npm ls ibm_db db2level echo $IBM_DB_HOME echo $PATH echo $LD_LIBRARY_PATH $DYLD_LIBRARY_PATH

Results:

 uname
  uname -m
  node -v
  npm ls ibm_db
  db2level
  echo $IBM_DB_HOME
  echo $PATH
  echo $LD_LIBRARY_PATH $DYLD_LIBRARY_PATH
Linux
x86_64
v18.17.1
translation-hub@1.0.0 /home/gui/thub
└── ibm_db@3.2.2

db2level: command not found

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/usr/lib/wsl/lib:/mnt/c/Program Files/Dyadic/ekm-client/bin:/mnt/c/WINDOWS/system32:/mnt/c/WINDOWS:/mnt/c/WINDOWS/System32/Wbem:/mnt/c/WINDOWS/System32/WindowsPowerShell/v1.0/:/mnt/c/WINDOWS/System32/OpenSSH/:/mnt/c/Applis/PuTTY/:/mnt/c/Applis/Git/cmd:/mnt/c/Program Files/Dyadic/ekm-client/bin:/mnt/c/Program Files/dotnet/:/mnt/c/Users/A16640693/AppData/Roaming/nvm:/mnt/c/Applis/nodejs:/mnt/c/Users/A16640693/AppData/Local/Microsoft/WindowsApps:/mnt/c/Applis/VSCode/bin:/mnt/c/Applis/VirtualBox:/mnt/c/Applis/openshift-client:/mnt/c/Applis/Notepad++:/mnt/c/Applis/Gradle/5.6/bin:/mnt/c/Applis/Java/jdk-11.0.2/bin:/mnt/c/Users/A16640693/AppData/Roaming/npm:/mnt/c/Applis/1Password:/mnt/c/Users/A16640693/AppData/Roaming/nvm:/mnt/c/Applis/nodejs:/snap/bin

For other issues

• Test script to reproduce the problem.

My code:

  const query = `SELECT * FROM ${schema}."${dbnames.cus}" WHERE "id" = ?;`

  const conn = await pool.open(cn)
  const stmt = await conn.prepare(query)
  const result = await stmt.execute([369])
  const fetched = await result.fetchAll()
  await conn.close()
}
main()

Breaks with Property 'fetchAll' does not exist on type '{ result: any[]; outparams: any; }'.ts(2339)

And trying this:

const main = async () => {
  const query = `SELECT * FROM ${schema}."${dbnames.cus}" WHERE "id" = ?;`

  const conn = await pool.open(cn)
  const stmt = await conn.prepare(query)
  const result = await stmt.execute([369]) as unknown as ODBCResult
  const fetched = await result.fetchAll()
  await conn.close()
}
main()

Break with:

Expected 2 arguments, but got 0.ts(2554)
index.d.ts(203, 14): An argument for 'option' was not provided.

Last thing from the example, ODBCResult doesn't have a close function in the type definition, so the following line from the example also doesn't work, same for ODBCStatement

await result.close(); <-- doesn't exist
await stmt.close(); <-- doesn't exist

[gdelory]

Hi all, no news on that?

For now I worked around the issue by overwriting some of the ibm_db TypeScript types. I create the file src/@types/ibm_db/index.d.ts with the following content:

// src/@types/ibm_db/index.d.ts

import { ODBCResult, Options } from 'ibm_db'

declare module 'ibm_db' {
  interface ODBCStatement {
    execute(params: any[]): Promise<ODBCResult>
    close(): Promise<false>
  }
  interface ODBCResult {
    fetch<T = any>(options?: Options): Promise<T>
    fetchAll<T = any>(): Promise<T[]>
    close(): Promise<false>
  }
}

export {}

I'll be happy to submit a PR but I would like the ibm_db team's opinion first

[bimalkjha]

@gdelory The given quick example and your shared test file works fine using nodejs without any issue. I think you are writing a .ts file and converting .ts to .js using tsc and then executing the .js file using node? Please share your both .ts file and generated .js file to look into the problem. Better write a small test file to repro the problem if you don't have and then share here.

We can see the return type of await stmt.execute here. It returns an array [result, outparameter] if there is output parameter to return from an stored procedure, otherwise it return result only. i.e. await stmt.execute() will return data of type [ODBCResult, any[]] or just ODBCResult. I think return type of just result: any[] should work.

Check https://github.com/ibmdb/node-ibm_db/blob/master/test/test-sp-resultset-execute.js#L131 to see how we extract actual result in case of outparams and then call fetch. Thanks.

[gdelory]

Hi @bimalkjha

I'm not sure I really understand your comment, it's specifically a typescript issue (hence the title starting with [TypeScript]). Of course if I write this in JavaScript it works, that's the point of the issue, the TS types are invalid, hence I can't even compile/transpile what is valid JS code, because TS thinks the return types are not what they are 😉

Your linking to the javascript file, which was exactly my point and what I said in the original message, I do know what it returns, what I'm saying is that the TypeScript types say otherwise, which is the issue.

JavaScript -> Hard to say from the code, but from my test it seems to returns an instance of ODBCResult TypeScript -> Returns Promise<{ result: any[]; outparams: any }> 🟥

[gdelory]

Also for a small test file to reproduce, I can't do much shorter that what I gave above. You still need to create a connection and prepare the statement, since the point of the issue is a type issue on statements.

const main = async () => {
  const query = `SELECT * FROM ${schema}."${dbnames.cus}" WHERE "id" = ?;`

  const conn = await pool.open(cn)
  const stmt = await conn.prepare(query)
  const result = await stmt.execute([369])
  const fetched = await result.fetchAll()
  await conn.close()
}
main()

This works perfectly fine as .js file, and doesn't as .ts, because of: Property 'fetchAll' does not exist on type '{ result: any[]; outparams: any; }'.ts(2339)

Which IMO means the TS types are wring and don't reflect the JavaScript implementation.

[bimalkjha]

@gdelory The code that you shared is javascript code. I am looking for typescript code. I do not see { result: any[]; outparams: any; } in the shared code and do not know how to get same output as you are getting. So, please share your typescript test file that reproduces this error when I run tsc repro.ts command. Thanks.

[bimalkjha]

@gdelory Please verify this issue too after installing latest code of ibm_db using command npm install git+https://git@github.com/ibmdb/node-ibm_db.git. Latest commit has changed some code for execute() api too. Thanks.

[gdelory]

Sorry for the delay @bimalkjha , and thanks for the fix. Here is the full .ts file:

// test.ts

import { Pool } from 'ibm_db'

const dbName = process.env.DB2_NAME as string
const hostname = process.env.DB2_HOST as string
const db2user = process.env.DB2_USER as string
const pwd = process.env.DB2_PWD as string
const port = process.env.DB2_PORT as string
const ssl = process.env.DB2_SSL == 'true' ? ';Security=SSL;SSLClientKeystoredb=sslcert/Universal-trustore.kdb;SSLClientKeystash=sslcert/Universal-trustore.sth;' : ''
const poolInit = parseInt(process.env.DB2_POOL_INIT_SIZE || '1')

const pool = new Pool({autoCleanIdle: true})
const cn = `DATABASE=${dbName};HOSTNAME=${hostname};UID=${db2user};PWD=${pwd};PORT=${port};PROTOCOL=TCPIP${ssl}`

const resInit = pool.init(poolInit, cn)
if (resInit != true) {
  throw new Error('Error initializing pool: ' + JSON.stringify(resInit))
}

if (process.env.DB2_POOL_MAX_SIZE) {
  pool.setMaxPoolSize(parseInt(process.env.DB2_POOL_MAX_SIZE))
}

const main = async () => {
  const query = `SELECT * FROM THUB2_DVE."cus" WHERE "id" = ?;`

  const conn = await pool.open(cn)
  const stmt = await conn.prepare(query)
  const result = await stmt.execute([369])
  const fetched = await result.fetchAll() // <== This is where it gives the error in the IDE
  // Property 'fetchAll' does not exist on type '{ result: any[]; outparams: any; }'.ts(2339)
  await conn.close()
}
main()

Here is a screenshot of the error in VSCode:

Output of the tsc:

> test-db2@1.0.0 transpile
> tsc

test.ts:29:32 - error TS2339: Property 'fetchAll' does not exist on type '{ result: any[]; outparams: any; }'.

29   const fetched = await result.fetchAll() // <== This is where it gives the error in the IDE
                                  ~~~~~~~~

Found 1 error in test.ts:29

[bimalkjha]

@gdelory I have cloned the latest code of ibm_db and then opened your test program in vscode editor. I am able to see the type from fetchAll() as any in editor and do not see any issue while generating .js file or running the .js file from terminal. See the below screenshot: Probably, your system still has the old code of ibm_db. Please recheck it. Thanks.

[bimalkjha]

@gdelory Please verify after installation of ibm_db@3.2.3. I have also added ibm_db\example\test.ts with above example to verify it. Thanks.

[gdelory]

@bimalkjha sorry for the delay, we had an intense month here at work. This is still not fixed.

I have a strong feeling that your setup is incorrect since you're fetchAll resolve to any. You probably don't have the ibm_db types installed.

I will open an issue against the type on DefinitelyTyped since this is where it should be fixed anyway.

[gdelory]

For reference: https://github.com/DefinitelyTyped/DefinitelyTyped/discussions/67514