Thank you for your help in advance. I’ve got a simple SP in Postgres:

CREATE OR REPLACE PROCEDURE TEST_SP(INOUT _result_one refcursor = 'rs_resultone')
LANGUAGE plpgsql 
AS $$
BEGIN
  open _result_one for 
   SELECT * 
   FROM (values (1,2,3, 'fruit', current_timestamp - INTERVAL '5 seconds'), (4,5,6, 'veggie', current_timestamp)) as t(a,b,c,d,e);
END;
$$;

I’m able to call it in pgAdmin without any problem.

CALL test_get_data_single();
FETCH ALL FROM "rs_resultone";

"a","b","c","d","e"
1,2,3,"fruit","2024-06-24 15:39:54.641594+00"
4,5,6,"veggie","2024-06-24 15:39:59.641594+00"

However, I’ve got issue with fetching data in C code:

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <stdlib.h>

#define STORED_PROCEDURE "TEST_SP" 

void check_sql_code(int sql_code, char *message, SQLHDBC dbc, SQLHENV env, SQLHSTMT stmt) {
  if (sql_code != SQL_SUCCESS) {
    SQLINTEGER   error_code;
    SQLSMALLINT  message_length;
    SQLCHAR      sql_state[6];
    SQLCHAR      error_message[1024];
    SQLRETURN    ret;
    SQLINTEGER   i = 0;

    printf("Err[%d]: %s", sql_code, message);

    do {
      ret = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, ++i, sql_state, &error_code, error_message, sizeof(error_message), &message_length);
      printf("%s:%d:%d:%sn", sql_state, i, error_code, error_message);
    } while( ret == SQL_SUCCESS );    
    
    if (stmt != NULL) SQLFreeStmt(stmt, SQL_CLOSE);
    SQLDisconnect(dbc);
    SQLFreeConnect(dbc);
    SQLFreeEnv(env);
    exit(1);
  }
}

int main() {
  SQLHENV env;
  SQLHDBC dbc;
  SQLHSTMT stmt;
  SQLRETURN ret; /* ODBC API return status */
  SQLCHAR outstr[1024];
  SQLSMALLINT outstrlen;
  SQLHSTMT cursor_stmt; // Handle for cursor statement
  SQLSMALLINT num_cols;
  SQLCHAR col_name[128]; // Buffer for column name
  SQLSMALLINT col_name_len;  
  char sql_statement[256];

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);                /* Allocate an environment handle */
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);  /* We want ODBC 3 support */
  SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);                            /* Allocate a connection handle */
  ret = SQLDriverConnect(dbc, NULL, "DSN=TEST_DB;", SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE); /* Connect to the DSN mydsn */
  if (SQL_SUCCEEDED(ret)) {
    printf("Connectedn");
    if (ret == SQL_SUCCESS_WITH_INFO) {
      printf("Driver reported the following diagnosticsn");
      check_sql_code(ret, "SQLDriverConnect", dbc, env, NULL);
    }
  } else {
    fprintf(stderr, "Failed to connectn");
    check_sql_code(ret, "SQLDriverConnect", dbc, env, NULL);
  }

  printf("Allocate statement handlen");
  ret = SQLAllocStmt(dbc, &stmt);
  check_sql_code(ret, "Error allocating statement handlen", dbc, env, stmt);

  snprintf(sql_statement, sizeof(sql_statement), "CALL %s(1);", STORED_PROCEDURE);

  printf("Execute the SQL statement (to open the cursor)n");
  ret = SQLExecDirect(stmt, (unsigned char*)sql_statement, SQL_NTS);
  check_sql_code(ret, "Error executing stored proceduren", dbc, env, stmt);

  printf("Allocate a statement handle for fetching data from the cursorn");
  ret = SQLAllocStmt(dbc, &cursor_stmt);
  check_sql_code(ret, "Error allocating cursor statement handlen", dbc, env, cursor_stmt);
  
  printf("Prepare the cursor statementn");
  ret = SQLPrepare(cursor_stmt, (unsigned char*)"FETCH ALL IN "rs_resultone";", SQL_NTS);
  check_sql_code(ret, "Error preparing cursor statementn", dbc, env, cursor_stmt);

  ret = SQLExecute(cursor_stmt);
  check_sql_code(ret, "Error preparing cursor statementn", dbc, env, cursor_stmt);
  
  // Fetch data row by row
   while ((ret = SQLFetch(cursor_stmt)) != SQL_NO_DATA_FOUND) {
    check_sql_code(ret, "Error fetchingn", dbc, env, cursor_stmt);
  }

  printf("Disconnect from drivern");
  SQLDisconnect(dbc);
  check_sql_code(-1, "Disconnecting from databasen", dbc, env, cursor_stmt);
}

I’d like to add that I don’t want to use functions, etc. I’d like to just have the equivalent in C code. Any help is highly appreciated.

Thanks,
Radek

The output of the program:

Connected
Allocate statement handle
Execute the SQL statement (to open the cursor)
Allocate a statement handle for fetching data from the cursor
Prepare the cursor statement
Err[-1]: Error preparing cursor statement
34000:1:1:ERROR: cursor "rs_resultone" does not exist;
Error while executing the query
00000:2:1:ERROR: cursor "rs_resultone" does not exist;
Error while executing the query