Database-js Wrapper for ADODB

Common errors

• TypeError: mysql.open is not a function

  cause The standalone example in the README contains a typo, erroneously referencing `mysql.open` instead of the correct `database-js-adodb` connection method.
  fix When using `database-js-adodb` in standalone mode, instantiate a new connection object directly: `const adodb = require('database-js-adodb'); const connection = new adodb(connectionString);` (or `new adodb.ADODBConnection(connectionString)`).

• Error: Cannot find module 'database-js2'

  cause `database-js2` is a peer dependency for the primary integration use case but is not automatically installed with `database-js-adodb`.
  fix Install `database-js2` explicitly: `npm install database-js2`.

• Error: [ADODB] Provider cannot be found. It may not be properly installed.

  cause The necessary ADO provider (e.g., Microsoft.Jet.OLEDB.4.0 for Access .mdb, or Microsoft.ACE.OLEDB.12.0 for .accdb and newer Excel) is not installed or correctly configured on the Windows system. This often occurs on 64-bit systems trying to use a 32-bit provider, or missing redistributables.
  fix Ensure the correct Microsoft Access Database Engine Redistributable (or Office components) matching the bitness of your Node.js process and desired database file type is installed on the Windows machine. For older Access `.mdb` files, `Microsoft.Jet.OLEDB.4.0` is usually sufficient; for newer `.accdb` and Excel files, `Microsoft.ACE.OLEDB.12.0` or later is needed.

Warnings

• gotcha This package is strictly Windows-only. It relies on ActiveX Data Objects (ADO) and the Windows Script Host (`cscript`) for its functionality, meaning it will not run on Linux, macOS, or other non-Windows environments.
  Fix: Ensure the application using `database-js-adodb` is deployed and executed exclusively on a Windows operating system. Consider alternative database drivers or architectures for cross-platform requirements.
• gotcha The package is CommonJS-only, meaning it uses `require()` for module imports. Attempting to use ES module `import` syntax will result in errors.
  Fix: Migrate your project to use CommonJS `require()` statements for `database-js-adodb` and `database-js2`, or use a bundler (like Webpack or Rollup) configured to handle CommonJS modules within an ESM project.
• gotcha When using `database-js-adodb` with Excel files, the connection string's 'Extended Properties' (e.g., `'Excel 8.0;HDR=Yes;IMEX=1;'`) are crucial for correct parsing and data interpretation. These properties vary based on Excel version and desired behavior (e.g., header row, mixed data types).
  Fix: Carefully construct the 'Extended Properties' within your connection string based on your Excel file's format and requirements. Consult Microsoft ADO documentation for specific property values. Ensure proper escaping of quotes within the connection string.

Install

• npm install database-js-adodb npm
• yarn add database-js-adodb yarn
• pnpm add database-js-adodb pnpm

Imports

• adodb
  wrong:

  import adodb from 'database-js-adodb';

  correct:

  const adodb = require('database-js-adodb');

  This package is CommonJS-only, requiring `require()`. When imported this way, `adodb` refers to the `ADODBConnection` class constructor for standalone use.
• ADODBConnection
  wrong:

  import { ADODBConnection } from 'database-js-adodb';

  correct:

  const { ADODBConnection } = require('database-js-adodb'); const connection = new ADODBConnection(connectionString);

  The package's main export is the `ADODBConnection` class. While you can destructure it, it's more common to assign the module export to a variable like `adodb` and instantiate from there (e.g., `new adodb(connectionString)`).
• Database
  wrong:

  import Database from 'database-js2';

  correct:

  const Database = require('database-js2');

  For the primary integrated usage pattern, you will also need to import the `Database` class from `database-js2`. This package is also CommonJS-only and requires `require()`.

Quickstart

Demonstrates connecting to both an Access database and an Excel spreadsheet using `database-js-adodb` through the `database-js2` abstraction layer, performing parameterized queries, and closing connections.

const Database = require('database-js2'); // database-js2 is a peer dependency for primary use case

(async () => {
    let connection, statement, rows;
    const dbPath = process.env.DB_PATH || 'C:\\Users\\me\\Desktop\\access_db.mdb'; // Example path, adjust as needed
    const connectionString = `database-js-adodb:///${dbPath}`;

    try {
        // Connect to the ADODB source via database-js
        connection = new Database(connectionString);
        
        // Prepare and execute a parameterized query
        statement = await connection.prepareStatement("SELECT * FROM tablea WHERE user_name = ?");
        rows = await statement.query('not_so_secret_user');
        console.log("Query Results for Access DB:", rows);

        // Example for Excel (version 8) connection
        const excelPath = process.env.EXCEL_PATH || 'C:\\Users\\me\\Desktop\\excel_file.xls'; // Example path, adjust as needed
        // Note: Extended Properties must be correctly escaped within the connection string
        const excelConnectionString = `database-js-adodb:///${excelPath}?Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';`;
        const excelConnection = new Database(excelConnectionString);
        const excelStatement = await excelConnection.prepareStatement("SELECT * FROM [Sheet1$A1:C52] WHERE user_name = ?");
        const excelRows = await excelStatement.query('another_user');
        console.log("Excel Query Results:", excelRows);
        await excelConnection.close();

    } catch (error) {
        console.error("An error occurred:", error);
    } finally {
        if (connection) {
            await connection.close();
        }
    }
})();