cursor 건 수를 배열로 받아오는 예제
빌드방법은 다음과 같습니다.
proc SQLCHECK=SEMANTICS iname=dyn4.pc userid=hr/password@ORCLPDB1 INCLUDE=$ORACLE_HOME/rdbms/public
gcc -o dyn4 dyn4.c -L$ORACLE_HOME/lib -I$ORACLE_HOME/precomp/public -I/opt/oracle/product/19c/dbhome_1/rdbms/public -lclntsh -lpthread -ldl -lm
아래 FETCH_SIZE 를 5로 했으므로 한번에 5건씩 가져오는 구조입니다.
#include <stdio.h>
#include <sqlca.h>
#include <sqlda.h>
#include <string.h>
#include <sqlcpr.h>
#include <stdlib.h>
#define MAX_SELECT_ITEMS 8
#define FETCH_SIZE 5 /* Fetch in 5-row chunks. */
#define MAX_CHARS 10
#define MAX_NAME_SIZE 8 /* Maximum size of a select-list item name. */
SQLDA *selda;
/* Data buffer. */
char c_data[MAX_SELECT_ITEMS][FETCH_SIZE][MAX_CHARS];
void print_rows(n) int n;
{
int row, sli;
for (row = 0; row < n; row++)
{
for (sli = 0; sli < selda->N; sli++)
{
printf("%.10s ", c_data[sli][row]);
}
printf("\n");
}
printf("-----------------------------------------------------------\n");
}
EXEC SQL BEGIN DECLARE SECTION;
int array_size = FETCH_SIZE; /* needs to be a host var for FOR */
char username[20] = "hr";
char password[20] = "password";
char *stmt = "select first_name, employee_id, salary, hire_date from employees";
VARCHAR connect_string[200];
EXEC SQL END DECLARE SECTION;
void sql_error()
{
char msgbuf[512];
size_t msgbuf_len, msg_len;
msgbuf_len = sizeof(msgbuf);
sqlglm(msgbuf, &msgbuf_len, &msg_len);
printf("\n\n%.*s\n", msg_len, msgbuf);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_FAILURE);
}
void main()
{
int row_count;
int sli; /* select-list item */
strcpy(connect_string.arr, "//localhost:1521/ORCLPDB1");
connect_string.len = strlen(connect_string.arr);
/* 데이터베이스 연결 */
EXEC SQL CONNECT : username IDENTIFIED BY : password USING : connect_string;
if (sqlca.sqlcode == 0)
printf("Connected.\n");
else
{
printf("Cannot connect as SCOTT.\n");
exit(EXIT_FAILURE);
}
EXEC SQL WHENEVER SQLERROR DO sql_error();
selda = sqlald(MAX_SELECT_ITEMS, MAX_NAME_SIZE, 0);
EXEC SQL PREPARE S FROM : stmt;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO selda;
selda->N = selda->F; /* Assumed not negative. */
for (sli = 0; sli < selda->N; sli++)
{
/* Set addresses of heads of the arrays in the V element. */
selda->V[sli] = c_data[sli][0];
/* Convert everything to varchar on output. */
selda->T[sli] = 1;
/* Set the maximum lengths. */
selda->L[sli] = MAX_CHARS;
}
for (row_count = 0;;)
{
EXEC SQL FOR : array_size FETCH C USING DESCRIPTOR selda;
print_rows(sqlca.sqlerrd[2] - row_count);
row_count = sqlca.sqlerrd[2];
if (sqlca.sqlcode == 1403)
break;
}
printf("\n%d rows retrieved\n", sqlca.sqlerrd[2]);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_SUCCESS);
}
