SQL Execution

/**
 * Executes a SQL statement
 * @param sql - SQL statement to execute
 * @param binds - Optional bind parameters
 * @param options - Optional execution options
 * @returns Promise resolving to execution result
 */
execute(sql: string, binds?: BindParameters, options?: ExecuteOptions): Promise<Result>;

type BindParameters = BindParametersObject | BindParametersArray;
type BindParametersObject = {[key: string]: BindValue};
type BindParametersArray = BindValue[];

interface BindValue {
  val?: any;
  dir?: number;
  type?: number;
  maxSize?: number;
  maxArraySize?: number;
}

interface ExecuteOptions {
  autoCommit?: boolean;
  fetchArraySize?: number;
  fetchInfo?: {[key: string]: FetchInfo};
  fetchTypeHandler?: FetchTypeHandler;
  keepInStmtCache?: boolean;
  maxRows?: number;
  outFormat?: number;
  prefetchRows?: number;
  resultSet?: boolean;
}

interface Result {
  rows?: any[][];
  metaData?: Metadata[];
  outBinds?: {[key: string]: any} | any[];
  rowsAffected?: number;
  lastRowid?: string;
  resultSet?: ResultSet;
  implicitResults?: ResultSet[];
  warning?: ExecuteWarning;
}

interface Metadata {
  name: string;
  fetchType?: number;
  dbType?: number;
  byteSize?: number;
  precision?: number;
  scale?: number;
  nullable?: boolean;
}

type FetchTypeHandler = (metadata: Metadata) => any;

interface FetchInfo {
  type?: number;
  converter?: (value: any) => any;
}

interface ExecuteWarning {
  message: string;
  offset: number;
}

const oracledb = require('oracledb');

// Simple query
const result = await connection.execute(
  'SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30'
);
console.log(result.rows);

// Query with bind parameters (positional)
const result = await connection.execute(
  'SELECT * FROM employees WHERE department_id = :1 AND salary > :2',
  [30, 5000]
);

// Query with bind parameters (named)
const result = await connection.execute(
  'SELECT * FROM employees WHERE department_id = :dept AND salary > :sal',
  { dept: 30, sal: 5000 }
);

// DML with OUT binds
const result = await connection.execute(
  `BEGIN
     INSERT INTO employees (employee_id, first_name, last_name) 
     VALUES (employee_seq.NEXTVAL, :fname, :lname)
     RETURNING employee_id INTO :id;
   END;`,
  {
    fname: 'John',
    lname: 'Doe',
    id: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER }
  }
);
console.log('New employee ID:', result.outBinds.id);

// Configure result format
const result = await connection.execute(
  'SELECT employee_id, first_name FROM employees',
  [],
  { outFormat: oracledb.OUT_FORMAT_OBJECT }
);
console.log(result.rows[0].FIRST_NAME);

/**
 * Executes a statement multiple times with different bind sets
 * @param sql - SQL statement to execute
 * @param bindParams - Array of bind parameter sets or number of iterations
 * @param options - Optional execution options
 * @returns Promise resolving to execution result
 */
executeMany(sql: string, bindParams: BindParameters[] | number, options?: ExecuteManyOptions): Promise<ResultMany>;

interface ExecuteManyOptions {
  autoCommit?: boolean;
  bindDefs?: BindDefinition[];
  batchErrors?: boolean;
  dmlRowCounts?: boolean;
  keepInStmtCache?: boolean;
}

interface BindDefinition {
  dir?: number;
  type: number;
  maxSize?: number;
  maxArraySize?: number;
}

interface ResultMany {
  rowsAffected?: number[];
  outBinds?: any[][];
  batchErrors?: BatchError[];
  dmlRowCounts?: number[];
  warning?: ExecuteWarning;
}

interface BatchError {
  error: Error;
  offset: number;
}

// Bulk insert
const data = [
  ['John', 'Doe', 50000],
  ['Jane', 'Smith', 60000],
  ['Bob', 'Johnson', 55000]
];

const result = await connection.executeMany(
  'INSERT INTO employees (first_name, last_name, salary) VALUES (:1, :2, :3)',
  data,
  { autoCommit: true, dmlRowCounts: true }
);
console.log('Rows inserted:', result.rowsAffected);

// Bulk insert with bind definitions for performance
const result = await connection.executeMany(
  'INSERT INTO employees (first_name, last_name, salary) VALUES (:fname, :lname, :sal)',
  [
    { fname: 'John', lname: 'Doe', sal: 50000 },
    { fname: 'Jane', lname: 'Smith', sal: 60000 }
  ],
  {
    bindDefs: [
      { type: oracledb.STRING, maxSize: 50 },
      { type: oracledb.STRING, maxSize: 50 },
      { type: oracledb.NUMBER }
    ],
    autoCommit: true
  }
);

// Handle batch errors
const result = await connection.executeMany(
  'INSERT INTO employees (employee_id, first_name) VALUES (:1, :2)',
  [[1, 'John'], [1, 'Jane']],  // Second will fail due to duplicate key
  { batchErrors: true }
);

if (result.batchErrors) {
  for (const error of result.batchErrors) {
    console.log(`Error at offset ${error.offset}:`, error.error.message);
  }
}

/**
 * Creates a readable stream for query results
 * @param sql - SQL query statement
 * @param binds - Optional bind parameters
 * @param options - Optional stream options
 * @returns QueryStream instance
 */
queryStream(sql: string, binds?: BindParameters, options?: StreamOptions): QueryStream;

interface StreamOptions extends ExecuteOptions {
  fetchArraySize?: number;
}

interface QueryStream extends NodeJS.ReadableStream {
  destroy(): void;
  pause(): QueryStream;
  resume(): QueryStream;
}

const stream = connection.queryStream(
  'SELECT * FROM large_table WHERE date_col > :1',
  [new Date('2023-01-01')]
);

stream.on('data', (row) => {
  console.log('Row:', row);
});

stream.on('end', () => {
  console.log('Query completed');
});

stream.on('error', (err) => {
  console.error('Stream error:', err);
});

// Process large datasets without loading everything into memory
stream.on('data', (row) => {
  // Process each row individually
  processRow(row);
});

/**
 * Gets information about a SQL statement
 * @param sql - SQL statement to analyze
 * @returns Promise resolving to statement information
 */
getStatementInfo(sql: string): Promise<StatementInfo>;

interface StatementInfo {
  statementType: number;
  bindNames: string[];
  metaData?: Metadata[];
}

// Analyze a SQL statement
const info = await connection.getStatementInfo(
  'SELECT employee_id, first_name FROM employees WHERE department_id = :dept'
);

console.log('Statement type:', info.statementType);
console.log('Bind variables:', info.bindNames); // ['dept']
console.log('Metadata:', info.metaData);

// Check if statement is a SELECT
if (info.statementType === oracledb.STMT_TYPE_SELECT) {
  console.log('This is a SELECT statement');
}

interface ResultSet {
  close(): Promise<void>;
  getRow(): Promise<any>;
  getRows(numRows?: number): Promise<any[]>;
  toQueryStream(): QueryStream;
  metaData: Metadata[];
}

// Get a result set instead of all rows
const result = await connection.execute(
  'SELECT * FROM large_table',
  [],
  { resultSet: true }
);

const resultSet = result.resultSet;

// Fetch rows one at a time
let row;
while ((row = await resultSet.getRow())) {
  console.log('Row:', row);
}

// Or fetch in batches
const rows = await resultSet.getRows(100);
console.log('First 100 rows:', rows);

// Always close the result set
await resultSet.close();

// Convert to stream
const stream = resultSet.toQueryStream();
stream.on('data', (row) => console.log(row));

// Bind directions
const BIND_IN = 3001;
const BIND_INOUT = 3002;
const BIND_OUT = 3003;

// Common bind value patterns
interface SimpleBindValue {
  val: any;                    // The bind value
  dir?: number;               // Bind direction (default: BIND_IN)
  type?: number;              // Data type (auto-detected if not specified)
  maxSize?: number;           // Maximum size for OUT/INOUT binds
}

interface ArrayBindValue {
  val: any[];                 // Array of values for executeMany
  dir?: number;
  type?: number;
  maxArraySize?: number;      // Maximum array size
}

// Oracle database types
const DB_TYPE_VARCHAR = 1;
const DB_TYPE_NUMBER = 2;
const DB_TYPE_DATE = 12;
const DB_TYPE_TIMESTAMP = 187;
const DB_TYPE_CLOB = 112;
const DB_TYPE_BLOB = 113;
const DB_TYPE_JSON = 119;

// Type aliases
const STRING = DB_TYPE_VARCHAR;
const NUMBER = DB_TYPE_NUMBER;
const DATE = DB_TYPE_TIMESTAMP;
const CLOB = DB_TYPE_CLOB;
const BLOB = DB_TYPE_BLOB;