在IBM i中使用RPGLE存储过程调用SQL查询数据并返回结果集

本示例代码演示了如何在IBM i (OS/400)操作系统上使用RPGLE存储过程调用SQL查询数据并返回结果集。通过本示例代码，可以灵活实现在IBM i上进行数据查询，方便进行后续业务处理。核心代码通过使用RPGLE语言中的相关函数来实现数据的查询和返回结果集操作。

// define module
ctl-opt Main(Directive:'OPTIONS(*SRCSTMT)');

dcl-pr SQLPrepare ExtProc('SQLPrepare');
  pstmt        pointer value
  stmt         char(32767) const options(*string)
  len          sqlu_int32 value
  endsqlext;
end-pr;

dcl-pr SQLExecute ExtProc('SQLExecute');
  pstmt        pointer value
  endsqlext;
end-pr;

dcl-pr SQLFetch ExtProc('SQLFetch');
  pstmt        pointer value
  endsqlext;
end-pr;

dcl-pr SQLNumResultCols ExtProc('SQLNumResultCols');
  pstmt        pointer value
  rc           sqlu_int16 value
  endsqlext;
end-pr;

// ...省略一些中间代码...

// main procedure
dcl-proc main;
  // declare variables
  dcl-s rc          sqlu_int16;
  dcl-s i           sqlu_int16;
  dcl-ds flds       likeds(QSYS/QADBXRFDS_t) dim(128);
  dcl-ds fldr       likeds(QSYS/QADBXREF_t);
  dcl-ds data       likeds(QSYS/QADBXRFDS_t) dim(1);
  dcl-ds col        likeds(QSYS/QADBXREF_t);
  dcl-s stmt       varchar(300);
  dcl-s error      varchar(500);
  dcl-s sqlca      SQLCA_t;
  dcl-s pstmt      pointer;
  dcl-s rows       sqlu_int32;

  // init variables
  stmt = 'SELECT * FROM MYTABLE';
  sqlca = *allx'00';

  // clear fields
  clear flds;
  clear fldr;
  clear data;

  // prepare statement
  pstmt = %alloc(%size(SQLDA));
  pstmt = SQLDA;
  callp SQLPrepare(pstmt: %addr(stmt): %len(stmt));

  // execute statement
  callp SQLExecute(pstmt);

  // get number of result columns
  rc = SQLNumResultCols(pstmt);

  // fetch result columns
  dow SQLFetch(pstmt) = 0;
    clear col;
    for i = 1 to rc;
      // get column information
      callp QDBRTVFD('MYLIB/MYTABLE': i: *omit: fldr);

      // copy values from field reference to column reference
      col.COLNAME = fldr.NAME;
      col.DATATYPE = fldr.TYPENAME;

      // get column value
      callp QDBRTVCI('MYLIB/MYTABLE': i: data: *omit: rows);
      col.COLVAL = data.FIELD;

      // add column to result set
      if %elem(rows) > 1;
        rows += 1;
      endif;
      flds(rows) = col;
    endfor;

    // process result set
    // ...省略一些中间代码...
  enddo;

  // clean up
  callp SQLFreeStmt(SQL_CLOSE);
  *inlr = *on;
end-proc;

免责申明：文章和图片全部来源于公开网络，如有侵权，请通知删除 server@dude6.com