Data not fetched properly

[Kagu-chan]

Hi,

we're using the lib with TypeScript 3.8 and node12. We've written a simple connection class which executes queries for us.

import { Connection, createConnection } from 'firebird';
import { Injectable } from '@nestjs/common';
import { ConfigService } from '../../config/config.service';
import { LoggingService } from '../../logging/logging.service';

import type { FBStatement } from 'firebird';
import type { DatabaseOptions } from '../../config/database-options.interface';

@Injectable()
export class ConnectionService {
  private fbConfig: DatabaseOptions;
  private connectionHandle: Connection;

  /**
   * @var {Firebird.Connection} Database Connection
   */
  get connection(): Connection {
    return this.connectionHandle;
  }

  constructor(
    private readonly config: ConfigService,
    private readonly logger: LoggingService,
  ) {
    this.fbConfig = this.config.config.firebird;
  }

  /**
   * Get the connection.
   *
   * If no connection is established, build a new one before returning
   * @returns {Firebird.Connection}
   */
  getConnection(): Connection {
    if (!this.connectionHandle) {
      // Small shorthand to have shorter template strings
      const c: DatabaseOptions = this.fbConfig;

      this.connectionHandle = createConnection();
      this.connectionHandle.connectSync(
        `${c.host}/${c.port}:${c.database}`,
        c.username,
        c.password,
        '',
      );
    }

    return this.connectionHandle;
  }

  //#region Low Level

  /**
   * Executes a query and fetches the result afterwards
   *
   * @param {string} query
   * @param {any[]} args[]
   * @returns {any[]}
   */
  fetchQuery(query: string, ...args: any[]): any[] {
    const result = this.query(query, ...args);

    return result.fetchSync('all', true);
  }

  /**
   * Executes a query and commits afterwards
   *
   * @param {string} query
   * @param {any[]} args[]
   * @returns {void}
   */
  commitQuery(query: string, ...args: any[]): void {
    this.query(query, ...args);
    this.connection.commitSync();
  }

  //#endregion
  //#region private

  /**
   * Executes a prepared statement
   *
   * @param {string} query
   * @param {any[]} args[]
   * @returns {Firebird.FBStatement}
   */
  private query(query: string, ...args: any[]): FBStatement {
    this.getConnection();

    try {
      const result: FBStatement = this.connection.prepareSync(query);

      result.execSync(...(args || []));

      return result;
    } catch (err) {
      this.logger.error(['ERROR'], 'Query failed', [query, args]);

      throw err;
    }
  }

  //#endregion
}

Using this code, we have the following problem:

• [node] We read data from Database SELECT * FROM TABLE WHERE ID = ?
• [other] We change the Data within other software
• [node] We read the exact same data from Database - data is unchanged
• [node] We restart the process - so a new connection is established
• [node] We read the exact same data again - data is correct

Little hack to try things out:

  private query(query: string, ...args: any[]): FBStatement {
    delete this.connectionHandle; // See here!
    this.getConnection();

    try {
      console.log(query);
      const result: FBStatement = this.connection.prepareSync(query);

      result.execSync(...(args || []));

      return result;
    } catch (err) {
      this.logger.error(['ERROR'], 'Query failed', [query, args]);

      throw err;
    }
  }

This way, it works, but i havent found a function to close connections

• [node] We read data from Database SELECT * FROM TABLE WHERE ID = ?
• [other] We change the Data within other software
• [node] We read the exact same data from Database (new con) - data is correct
• [firebird] SELECT * FROM MON$ATTACHMENTS shows a shitload of open connections - i didnt find a close connection function...

Thanks in advance for any help you can provide (:

[xdenser]

If you do not call startSync or start explicitly it is called implicitly when you do the query. So until you call commit or rollback or make new connection you are in the same transaction and it has snapshot of data at the momment of start.

[NicoAiko]

Just as a follow-up question: After commiting / rolling back the transaction, is it necessary to do something specific with the default transaction? Or is the transaction result automatically cleared?

[Kagu-chan]

@xdenser If i try to commit or rollback afterwards, i get an error about an unset cursor.

[NicoAiko]

I got it working by calling fetchSync before commitSync, so basically

prepareSync
execSync
fetchSync
commitSync

With this order, the latest data is fetched, the transaction is properly closed and there is no Cursor is not open crash.

@xdenser Can you confirm this order being the intended way of usage? Is it maybe possible to update the documentation cases?

[xdenser]

Yes correct. Default transaction is the transaction associated with connection object. If you call 'commit' on connection object - you are commiting default transaction. Check 'inTransaction' property to see if the transaction is started.