
平台论坛博客文库

› 论坛 › 数据库技术 › Oracle › 帮忙看看这个OCI程序

帮忙看看这个OCI程序 [复制链接]

nsinit

白手起家

论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2004-06-23 16:21 |只看该作者 |倒序浏览

写了个OCI程序，简单的对数据库执行SELECT查询，程序执行时出错信息如下：
sql_init_socket ok
select nasname from nas
Fail ORA-00942: table or view does not exist

sql_select_query fail

程序代码如下：

#include <stdio.h>;
#include <sys/stat.h>;
#include <sys/time.h>;
#include <stdlib.h>;
#include <string.h>;
#include <oci.h>;
#define MAX_DATASTR_LEN 64
typedef struct oracle_sock_t {
OCIEnv *env;
OCIError *errHandle;
OCISvcCtx *conn;
OCIStmt *queryHandle;
sb2 *indicators;
char **results;
} oracle_sock_t;
typedef struct ora_config {
char *sql_server;
char *sql_login;
char *sql_password;
char *sql_db;
int sqltrace;
} ORA_CONFIG;
char *sql_error(oracle_sock_t *oracle_sock, ORA_CONFIG *config) {
static char msgbuf[512];
sb4 errcode = 0;
if (!oracle_sock) return "ocilib: no connection to db";
memset((void *)msgbuf, (int)'\0', sizeof(msgbuf));
OCIErrorGet((dvoid *)oracle_sock->;errHandle, (ub4)1, (text *)NULL,
&errcode, msgbuf, (ub4)sizeof(msgbuf), (ub4)OCI_HTYPE_ERROR);
if (errcode) {
return msgbuf;
}
else {
return NULL;
}
}
oracle_sock_t* sql_init_socket(ORA_CONFIG *config) {
oracle_sock_t *oracle_sock = NULL;
oracle_sock = (oracle_sock_t *)malloc(sizeof(oracle_sock_t));
if (oracle_sock == NULL)
return NULL;
if ( OCIEnvCreate(&oracle_sock->;env, OCI_DEFAULT|OCI_THREADED, (dvoid *)0,
(dvoid* (*)(dvoid*, size_t))0,
(dvoid* (*)(dvoid*, dvoid*, size_t))0,
(void (*)(dvoid*, dvoid *))0,
0, (dvoid **)0) ) {
fprintf(stderr,"ocilib: Couldn't init Oracle OCI environment (OCIEnvCreate())");
return NULL;
}
if (OCIHandleAlloc((dvoid *)oracle_sock->;env, (dvoid **)&oracle_sock->;errHandle,
(ub4)OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0))
{
fprintf(stderr, "ocilib: Couldn't init Oracle ERROR handle (OCIHandleAlloc())");
return NULL;
}
/* Allocate handles for select and update queries */
if (OCIHandleAlloc((dvoid *)oracle_sock->;env, (dvoid **)&oracle_sock->;queryHandle,
(ub4)OCI_HTYPE_STMT, (CONST size_t)0, (dvoid **)0))
{
fprintf(stderr,"ocilib: Couldn't init Oracle query handles: %s", sql_error(oracle_sock, config));
return NULL;
}
if (OCILogon(oracle_sock->;env, oracle_sock->;errHandle, &oracle_sock->;conn,
config->;sql_login, strlen(config->;sql_login),
config->;sql_password, strlen(config->;sql_password),
config->;sql_db, strlen(config->;sql_db)))
{
fprintf(stderr, "ocilib: Oracle logon failed: '%s'", sql_error(oracle_sock, config));
sql_close(oracle_sock, config);
return NULL;
}
return oracle_sock;
}
int sql_num_fields(oracle_sock_t *oracle_sock, ORA_CONFIG *config) {
ub4 count;
if (OCIAttrGet((dvoid *)oracle_sock->;queryHandle,
(ub4)OCI_HTYPE_STMT,
(dvoid *)&count,
(ub4 *)0,
(ub4)OCI_ATTR_PARAM_COUNT,
oracle_sock->;errHandle)) {
fprintf(stderr, "ocilib: Error retrieving column count in sql_num_fields: %s", sql_error(oracle_sock, config));
return -1;
}
return count;
}
int sql_select_query(oracle_sock_t *oracle_sock, ORA_CONFIG *config, char *querystr) {
int x;
int y;
int colcount;
OCIParam *param;
OCIDefine *define;
ub2 dtype;
ub2 dsize;
char **rowdata=NULL;
sb2 *indicators;
if (config->;sqltrace)
fprintf(stderr, "%s\n", querystr);
if (oracle_sock->;conn == NULL) {
fprintf(stderr, "ocilib: not logoned");
return -1;
}
if (OCIStmtPrepare(oracle_sock->;queryHandle, oracle_sock->;errHandle,
querystr, strlen(querystr),
OCI_NTV_SYNTAX, OCI_DEFAULT)) {
fprintf(stderr,"ocilib: prepare failed in sql_select_query: %s",sql_error(oracle_sock, config));
return -1;
}
/* Query only one row by default (for now) */
x = OCIStmtExecute(oracle_sock->;conn,
oracle_sock->;queryHandle,
oracle_sock->;errHandle,
(ub4)0,
(ub4)0,
(OCISnapshot *)NULL,
(OCISnapshot *)NULL,
(ub4)OCI_DEFAULT);
if (x == OCI_NO_DATA) {
fprintf(stderr, "NO DATA\n");
return 0; // Nothing to fetch
}
else if (x != OCI_SUCCESS) {
fprintf(stderr, "Fail %s\n", sql_error(oracle_sock, config));
return -1; // query failed
}
/*
* Define where the output from fetch calls will go
*
* This is a gross hack, but it works - we convert
* all data to strings for ease of use. Fortunately, most
* of the data we deal with is already in string format.
*/
colcount = sql_num_fields(oracle_sock, config);
/*
* FIXME: These malloc's can probably go, as the schema
* is fixed...
*/
rowdata=(char **)malloc(sizeof(char *) * (colcount+1));
memset(rowdata, 0, (sizeof(char *) * (colcount+1)) );
indicators = (sb2 *) malloc(sizeof(sb2) * (colcount+1));
memset(indicators, 0, sizeof(sb2) * (colcount+1));
for (y=1; y <= colcount; y++) {
x=OCIParamGet(oracle_sock->;queryHandle, OCI_HTYPE_STMT,
oracle_sock->;errHandle,
(dvoid **)&param,
(ub4)y);
if (x != OCI_SUCCESS) {
fprintf(stderr, "ocilib: OCIParamGet() failed in sql_select_query: %s", sql_error(oracle_sock, config));
return -1;
}
x=OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM,
(dvoid*)&dtype, (ub4*)0, OCI_ATTR_DATA_TYPE,
oracle_sock->;errHandle);
if (x != OCI_SUCCESS) {
fprintf(stderr, "ocilib: OCIAttrGet() failed in sql_select_query: %s", sql_error(oracle_sock, config));
return -1;
}
dsize=MAX_DATASTR_LEN;
/*
* Use the retrieved length of dname to allocate an output
* buffer, and then define the output variable (but only
* for char/string type columns).
*/
switch(dtype) {
#ifdef SQLT_AFC
case SQLT_AFC: /* ansii fixed char */
#endif
#ifdef SQLT_AFV
case SQLT_AFV: /* ansii var char */
#endif
case SQLT_VCS: /* var char */
case SQLT_CHR: /* char */
case SQLT_STR: /* string */
x=OCIAttrGet((dvoid*)param, (ub4)OCI_DTYPE_PARAM,
(dvoid*)&dsize, (ub4 *)0, (ub4)OCI_ATTR_DATA_SIZE,
oracle_sock->;errHandle);
if (x != OCI_SUCCESS) {
fprintf(stderr, "ocilib: OCIAttrGet() failed in sql_select_query: %s", sql_error(oracle_sock, config));
return -1;
}
rowdata[y-1]=malloc(dsize+1);
break;
case SQLT_DAT:
case SQLT_INT:
case SQLT_UIN:
case SQLT_FLT:
case SQLT_PDN:
case SQLT_BIN:
case SQLT_NUM:
rowdata[y-1]=malloc(dsize+1);
break;
default:
dsize=0;
rowdata[y-1]=NULL;
break;
}
indicators[y-1] = 0;
x=OCIDefineByPos(oracle_sock->;queryHandle,
&define,
oracle_sock->;errHandle,
y,
(ub1 *)rowdata[y-1],
dsize+1,
SQLT_STR,
&indicators[y-1],
(dvoid *)0,
(dvoid *)0,
OCI_DEFAULT);
/*
* FIXME: memory leaks of indicators & rowdata?
*/
if (x != OCI_SUCCESS) {
fprintf(stderr, "ocilib: OCIDefineByPos() failed in sql_select_query: %s", sql_error(oracle_sock, config));
return -1;
}
}
oracle_sock->;results=rowdata;
oracle_sock->;indicators=indicators;
return 0;
}
int sql_close(oracle_sock_t *oracle_sock, ORA_CONFIG *config) {
if (oracle_sock->;conn) {
OCILogoff(oracle_sock->;conn, oracle_sock->;errHandle);
}
if (oracle_sock->;queryHandle) {
OCIHandleFree((dvoid *)oracle_sock->;queryHandle, (ub4) OCI_HTYPE_STMT);
}
if (oracle_sock->;errHandle) {
OCIHandleFree((dvoid *)oracle_sock->;errHandle, (ub4) OCI_HTYPE_ERROR);
}
if (oracle_sock->;env) {
OCIHandleFree((dvoid *)oracle_sock->;env, (ub4) OCI_HTYPE_ENV);
}
oracle_sock->;conn = NULL;
free(oracle_sock);
return 0;
}
int main()
{
oracle_sock_t *oracle_sock = NULL;
ORA_CONFIG *config = NULL;
char *querystr = "select nasname from nas where id=1";
config = malloc(sizeof(ORA_CONFIG));
if ( !config ) {
fprintf(stderr, "malloc out of memory\n");
exit(-1);
}
config->;sql_server = "ylei";
config->;sql_login = "system";
config->;sql_password = "manager";
config->;sql_db = "nsinit";
config->;sqltrace = 1;
oracle_sock = sql_init_socket(config);
if ( oracle_sock != NULL ) {
fprintf(stderr, "sql_init_socket ok\n");
}
else {
fprintf(stderr, "sql_init_socket failure\n");
exit(-1);
}
if (sql_select_query(oracle_sock, config, "select nasname from nas") != 0) {
fprintf(stderr, "sql_select_query fail\n");
exit(-1);
}
fprintf("nasname: %s", oracle_sock->;results[0]);
exit(0);
}