Obtain the REF CURSOR data type

2024-01-12 07:05:42  Updated

OceanBase Connector/ODBC supports the REF CURSOR data type in Oracle mode to achieve cursor functionality.

The sys_refcursor field in the following example indicates that the output data type is the REF CURSOR data type.

ODBC_TEST(test_proc_cursor)
{
  SQLRETURN ret = 0;
  SQLLEN outLen = 0;
  char *createpl = "create or replace procedure Proc_Test(PO_CURSOR out sys_refcursor, S_CUR out sys_refcursor, sss out varchar2)\n"
    "is\n"
    "begin\n"
    "  sss:='111111';\n"
    "  OPEN PO_CURSOR FOR select '123',32,'dfab' from dual;\n"
    "  OPEN S_CUR FOR select 12,23,45,44 from dual;\n"
    "end Proc_Test;";

  CHECK_STMT_RC(Stmt, ret = SQLPrepare(Stmt, (SQLCHAR*)createpl, (SQLINTEGER)strlen((char*)createpl)));
  CHECK_STMT_RC(Stmt, ret = SQLExecute(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  SQLEndTran(SQL_HANDLE_DBC, Connection, SQL_COMMIT);

  SQLRETURN rcode;
  char *pCall = (char*)"{ call PROC_TEST(?,?,?)}";
  char buf1[100] = { 0 };
  char buf2[100] = { 0 };
  SQLLEN inOut = SQL_NTS;


  rcode = SQLPrepare(Stmt, (SQLCHAR*)pCall, strlen(pCall));
  CHECK_STMT_RC(Stmt, rcode);
  rcode = SQLBindParameter(Stmt, 1, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, buf1, 100, &inOut);
  rcode = SQLBindParameter(Stmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, buf1, 100, &inOut);
  rcode = SQLBindParameter(Stmt, 3, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, buf2, 100, &inOut);
  rcode = SQLExecute(Stmt);
  CHECK_STMT_RC(Stmt, rcode);
  printf("buf2=%s\n", buf2);

  char c1[100] = { 0 };
  char c2[100] = { 0 };
  double d1 = 0, d2= 0, d3=0, d4=0;
  rcode = SQLBindCol(Stmt, 1, SQL_C_CHAR, &c1, 100, NULL);
  rcode = SQLBindCol(Stmt, 2, SQL_C_DOUBLE, &d2, 8, NULL);
  rcode = SQLBindCol(Stmt, 3, SQL_C_CHAR, &c2, 100, NULL);
  rcode = SQLBindCol(Stmt, 4, SQL_C_DOUBLE, &d4, 8, NULL);
  CHECK_STMT_RC(Stmt, rcode = SQLFetch(Stmt));
  printf("%s,%d,%s,%d\n", c1, (int)d2, c2, (int)d4);
  rcode = SQLFetch(Stmt);
  is_num(rcode, 100);

  rcode = SQLMoreResults(Stmt);
  CHECK_STMT_RC(Stmt, rcode);
  rcode = SQLBindCol(Stmt, 1, SQL_C_DOUBLE, &d1, 8, NULL);
  rcode = SQLBindCol(Stmt, 2, SQL_C_DOUBLE, &d2, 8, NULL);
  rcode = SQLBindCol(Stmt, 3, SQL_C_DOUBLE, &d3, 8, NULL);
  rcode = SQLBindCol(Stmt, 4, SQL_C_DOUBLE, &d4, 8, NULL);
  CHECK_STMT_RC(Stmt, rcode = SQLFetch(Stmt));
  printf("%d,%d,%d,%d\n", (int)d1, (int)d2, (int)d3, (int)d4);
  rcode = SQLFetch(Stmt);
  is_num(rcode, 100);

  rcode = SQLMoreResults(Stmt); //return SQL_NO_DATA;
  is_num(rcode, 100);

  CHECK_STMT_RC(Stmt, SQLFreeStmt(Stmt, SQL_CLOSE));
  return OK;
}

Contact Us