Cannot connect to Informix database using NodeJS API

I am trying to build an API using NodeJS 20.15.1 to our Informix 12.10.FC16 Databases, but can't get it to work. I tried using "npm install ibm_db" but it won't connect.

I've been trying for weeks. I can successfully create an ODBC (C: Windowssystem32odbcad32.exe) connection. I can also successfully connect using ODBC DSN via PowerShell, but not NodeJS.

The Databases are on Windows 2008 and the NodeJS is on Windows 2022. Not sure if the ibm_db package is outdated? I did see something about Wire Listener, but not sure if that applies to my scenario since it seems to want a MongoDB? Any insight/help is appreciated.

If there's a better way than ODBC it doesn't matter to me as long as it works.

ConnectTest Demo: Server: coloradodb
Host: virtualserver1
Service: 9088
Protocol: olsoctcp
User name: webdev
Password: password123
Database: mydatabase

ODBC: DSN: COLORADODSN
Server Name: coloradodb
Host Name: virtualserver1
Service: 9088
Protocol: onsoctcp
User name: webdev
Password: password123
Database Name: mydatabase

PowerShell Script for Testing:

$connectionString = "DSN=COLORADODSN;UID=webdev;PWD=password123;"$connection = New-Object System.Data.Odbc.OdbcConnection$connection.ConnectionString = $connectionStringtry {$connection.Open()Write-Output "Connection successful!"} catch {Write-Output "Connection failed: $($_.Exception.Message)"} finally {$connection.Close()}

Connection successful!

NODEJS CONNECTION TEST FILE:

"use strict";

import ibmdb from "ibm_db"; // Ensure ibmdb is imported


// Function to create a connection string
const createConnectionString = () => {
  const connectionString = `SERVER=coloradodb;DATABASE=mydatabase;HOST=virtualserver1;SERVICE=9088;PROTOCOL=onsoctcp;UID=webdev;PWD=password123;Client_Locale=en_us.8859-1;DB_LOCALE=en_us.819;`;
  console.log("Generated Connection String:", connectionString);
  return connectionString;
};


// Test the connection
ibmdb.open(createConnectionString(), (err, conn) => {
  if (err) {
    console.error("Detailed Error:", err);
  } else {
    console.log("Connection successful!");
    conn.close();
  }
});

ERROR

node testConnection.js

Generated Connection String:

SERVER=coloradodb;DATABASE=mydatabase;HOST=virtualserver1;SERVICE=9088;PROTOCOL=onsoctcp;UID=webdev;PWD=password123;Client_Locale=en_us.8859-1;DB_LOCALE=en_us.819;;

Detailed Error:

[Error: [IBM][CLI Driver] SQL1042C  An unexpected system error occurred.  SQLSTATE=58004] {
error: '[ibm_db] SQL_ERROR',
sqlcode: -1042,
state: 'HY000'
}

DIFFERENT NODEJS TEST:

import ibmdb from "ibm_db"; // Ensure ibmdb is imported
var connStr =
  "DATABASE=mydatabase;HOSTNAME=virtualserver1;UID=webdev;PWD=password123;PORT=9088;PROTOCOL=olsoctcp";

ibmdb.open(connStr, function (err, conn) {
  if (err) return console.log(err);

  conn.query("select * from clubconfig", function (err, data) {
    if (err) console.log(err);
    else console.log(data);

    conn.close(function () {
      console.log("done");
    });
  });
});
ibmdb.open(connStr).then(
  (conn) => {
    conn.query("select * from clubconfig").then(
      (data) => {
        console.log(data);
        conn.closeSync();
      },
      (err) => {
        console.log(err);
      }
    );
  },
  (err) => {
    console.log(err);
  }
);

ERRROR:

[Error: [IBM][CLI Driver] SQL1013N  The database alias name or database name "" could not be found.  SQLSTATE=42705] {
error: '[ibm_db] SQL_ERROR',
sqlcode: -1013,
state: '08001'
}

I have tried using the ODBC DSN (that tests successfully in ODBC) in the connection string and I have tried manually entering all the information.

I installed the IBM Client SDK on the NodeJS server. I can use the ConnectTest Demo successfully.

I tried creating C:\Program Files\Informix Client-SDK\etc\sqlhosts and C:\Program Files\Informix Client-SDK\etc\db2cli.in (don't think these were needed but tried).

%INFORMIXDIR%: C:\Program Files\Informix Client-SDK. %DB2CLIINIPATH%: C:\Program Files\Informix Client-SDK\etc. %PATH%: Added C:\Program Files\Informix Client-SDK\bin

Tried adding DRIVER={IBM INFORMIX ODBC DRIVER}; to the NodeJS connectiong strings.

Tried protocols: olsoctcp, onsoctcp, TCPIP

Tried running Visual Code as Administrator.

Tried turning on Tracing within the ODBC, but nothing gets generated to the log from nodejs, but does get logged from powershell.

Solution

I am answering my own question as I finally got it to work. I tried working with IBM support, ChatGPT, and a million searches, but could not find the resolution. After doing an internet search for "[IBM][CLI Driver] SQL1013N" (nothing with Node or Informix), I came across this GitHub article concerning Python connecting to DB2. https://github.com/ibmdb/python-ibmdb/issues/873

Putting all the resources together, here is how you can connect to IBM Informix using Node.js:

Informix needs to use the DRDA connection, not ODBC, not Wire Listener as others have mentioned in other postings. Wire Listener is only for DB2. Your connection string needs to point to your DRDA service on the Informix server, the PROTOCOL must be TCPIP, PORT needs to use the DRDA port number that is Listening on the Informix Server, and it must be told to use AUTHENTICATION=SERVER.

Example for my system configuration:

SERVER=svc_drda;DATABASE=mydatabase;HOSTNAME=virtualserver1;AUTHENTICATION=SERVER;PORT=9089;PROTOCOL=TCPIP;UID=webdev;PWD=password123;

I confirmed this is working with: Informix 12.10 on Windows 2008 Node.js on Windows 2022

Step 1: Find the DRDA Service and Identify the Listening Port

Locate the DRDA Service:

The DRDA service in Informix is usually configured in the sqlhosts file, which can typically be found in the Informix installation directory ($INFORMIXDIR/etc/sqlhosts on Unix/Linux or C:\Informix\etc\sqlhosts on Windows). Open the sqlhosts file and look for entries that specify drda as the protocol. An example entry might look like:

myserver   drda   hostname   port_number

Here, hostname is the address of the server hosting Informix, and port_number is the listening port for DRDA connections.

Verify the Listening Port:

You may also verify the listening port using command-line utilities. For example, on Linux, you could use netstat to check if the port is open:

netstat -an | grep port_number

On Windows, Open Services:

Press Win + R to open the Run dialog box, then type services.msc and press Enter. In the Services window, look for a service related to Informix. Common names may include:

InformixDRDAListener or InformixDRDA IBM Informix Dynamic Server

Check Properties:

Right-click on the Informix-related service and select Properties. In the Properties window, check the Path to executable. This path might include configuration files or options that specify the port number.

Check for Port Assignment in the DRDA Service:

Some Informix installations use a service file within Windows to specify the DRDA port (mine had this). Open the services file located here:

C:\Windows\System32\drivers\etc\services

In the services file, look for entries labeled drda or informixdrda. An entry might look like:

drda    9088/tcp    # IBM Informix DRDA listener

The port number (9088 in this example) is the DRDA listening port.

Step 3: Verify the DRDA Port is Open and Listening

Use Command Prompt:

Open Command Prompt as Administrator. Type the following command, replacing 9088 with the port number you found in the

netstat -an | find "9088"

If the port is open and listening, you’ll see an entry like this:

TCP    0.0.0.0:9088     0.0.0.0:0       LISTENING

Use PowerShell (Alternative):

Open PowerShell as Administrator. Run the following command to see if the DRDA port is in use:

Get-Process -Id (Get-NetTCPConnection -LocalPort 9088).OwningProcess

This will confirm that the DRDA port is active and associated with the Informix service.

This confirms that the Informix DRDA service is running and listening on the specified port.

Step 4: Install the ibm_db Package on the Node.js Server

Install npm and node.js (if not already installed):

Make sure Node.js and npm are installed. You can download them from https://nodejs.org.

Verify the installations:

node -v
npm -v

Install ibm_db:

Open a terminal or command prompt and navigate to your project directory. Install the ibm_db package via npm:

npm install ibm_db

This package provides the necessary drivers and libraries to connect to Informix using DRDA. I had previously installed the IBM Client SDK on the Node server, but now I'm not sure this is necessary since the ibm_db package seems to include the drivers and we are not using ODBC.

Step 5: Configure the Connection String

Format the Connection String:

Use the following connection string format to specify the necessary details:

"DATABASE=<dbname>;HOSTNAME=<hostname>;PORT=<port_number>;PROTOCOL=TCPIP;UID=<username>;PWD=<password>;AUTHENTICATION=SERVER"

Replace the placeholders:

dbname: Name of the Informix database.
hostname: The hostname or IP address where the Informix server is running.
port_number: Port number for the DRDA service.
username and password: Your Informix username and password.

Example Connection String:

Here’s a sample connection string for reference:

"DATABASE=mydb;HOSTNAME=myserver.company.com;PORT=9088;PROTOCOL=TCPIP;UID=myuser;PWD=mypassword;AUTHENTICATION=SERVER"

Step 6: Create a Test Connection Script in Node.js

Set Up a Connection Script:

Create a new file, testConnection.js, in your project directory. Copy and paste the following code, replacing the placeholders in the connection string with your database details:

"use strict";

import ibmdb from "ibm_db"; // Ensure ibmdb is imported

const ibmdb = require("ibm_db");

// Define the connection string
const connStr = "DATABASE=mydb;HOSTNAME=myserver.company.com;PORT=9088;PROTOCOL=TCPIP;UID=myuser;PWD=mypassword;AUTHENTICATION=SERVER";

// Establish the connection
ibmdb.open(connStr, (err, conn) => {
  if (err) {
    return console.error("Connection error: ", err.message);
  }

  console.log("Connected to Informix database successfully!");

  // Sample query to verify the connection
  conn.query("SELECT FIRST 1 * FROM systables", (err, data) => {
    if (err) {
      console.error("Query error: ", err.message);
    } else {
      console.log("Sample Query Result:", data);
    }

    // Close the connection
    conn.close((err) => {
      if (err) {
        return console.error("Error closing connection: ", err.message);
      }
      console.log("Connection closed successfully.");
    });
  });
});

Run the Test Script:

In the terminal, navigate to your project directory and run the script:

node testConnection.js

Check the Output:

If the connection is successful, you should see:

Connected to Informix database successfully!
Sample Query Result: [ ...data from systables... ]
Connection closed successfully.

Any connection or query errors will be displayed in the console for troubleshooting.