Home » Developer & Programmer » Precompilers, OCI & OCCI » query does not work
| query does not work [message #112681] |
Mon, 28 March 2005 13:08  |
nemaria
Messages: 7
Registered: March 2005
|
Junior Member |
|
|
There is a problem when running my program.
It connects to database all right, but doesn't print a query result but many blank rows instead(in loop, I sujest). Here is my code(copied from tutorial example. I run it on Visual Studio .Net):
#include "stdafx.h"
static OCIEnv *p_env;
static OCIError *p_err;
static OCISvcCtx *p_svc;
static OCIStmt *p_sql;
static OCIDefine *p_dfn = (OCIDefine *) 0;
static OCIBind *p_bnd = (OCIBind *) 0;
struct OCIEnv { };
struct OCIError { };
struct OCISvcCtx { };
struct OCIStmt { };
struct OCIDefine { };
struct OCIBind { };
void main()
{
int p_bvi;
char p_sli[20];
int rc;
char errbuf[100];
int errcode;
rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, /* Initialize OCI */
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0 );
/* Initialize evironment */
rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );
/* Initialize handles */
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0);
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0);
/* Connect to database server */
rc = OCILogon(p_env, p_err, &p_svc, (OraText*)"scott", 5, (OraText*)"tiger", 5, (OraText*)"d458_nat", 8);
if (rc != 0) {
OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, (OraText*)errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
printf("Error - %.*s\n", 512, errbuf);
exit(8);
}
/* Allocate and prepare SQL statement */
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
rc = OCIStmtPrepare(p_sql, p_err, (OraText*)"select ename from emp where deptno=:x",
(ub4) 137, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
/* Bind the values for the bind variables */
p_bvi = 10; /* Use DEPTNO=10 */
rc = OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":x",
-1, (dvoid *) &p_bvi, sizeof(int), SQLT_INT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
/* Define the select list items */
rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) &p_sli,
(sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, OCI_DEFAULT);
/* Execute the SQL statment */
rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
while (rc != OCI_NO_DATA) { /* Fetch the remaining data */
printf("%s\n",p_sli);
rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);
}
rc = OCILogoff(p_svc, p_err); /* Disconnect */
rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT); /* Free handles */
rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);
return;
}
----------------------------------------------------------
Why? What's wrong? What can I do? I just want to print the query result.
The parameter length of OCIStmtPrepare function is 137, because 37 gives an error 'Exception.toString() failed'. Why?
Thanks in advance.
|
|
|
|
|
|
| Re: query does not work [message #112692 is a reply to message #112684] |
Mon, 28 March 2005 14:31  |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
Hi
rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) &p_sli,
(sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, OCI_DEFAULT);
The &p_sli should be just plain p_sli because you should be passing a pointer p_sli memory location, not pointer to a point of the memory location.
I'm surpised this wasn't picked up by your compiler.
Aso "(sword) 20" should be replaced with (sb4)sizeof(p_sli).
Your second question with regard to the size definition of the SQL string. Don't forget the "sql statement" has a \0 at the end of the string. So the string of 37 characters is infact a string of 38 characters.
You need to get out of the habit of hard coding char lenghs. Alsways use strlen() because it will guarentee you always send the correct string length to your function calls.
Michael Hartley.
|
|
|
|
 Re: query does not work [message #112755 is a reply to message #112692] |
Tue, 29 March 2005 02:22 |
nemaria
Messages: 7
Registered: March 2005
|
Junior Member |
|
|
Michael,
I tried to change the code as you wrote, but it is still the same problem. The OCIStmtExecute function returns -1.
Concerning the length, the minimum value when it works is 41, not 38.
Would you help me pleeease.
Nemaria.
[Updated on: Tue, 29 March 2005 04:20] Report message to a moderator |
|
|
|
| Re: query does not work [message #113064 is a reply to message #112755] |
Wed, 30 March 2005 17:23 |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
Hi,
suggest you wrap each of the OCI statements with a call to the checker function defined below:
STATICF void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
text errbuf[512];
ub4 buflen;
ub4 errcode;
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
(void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
(void) printf("Error - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
(void) printf("Error - OCI_NODATA\n");
break;
case OCI_ERROR:
(void) OCIErrorGet (errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
(void) printf("Error - %s\n", errbuf);
break;
case OCI_INVALID_HANDLE:
(void) printf("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
(void) printf("Error - OCI_STILL_EXECUTE\n");
break;
default:
break;
}
}
eg.
checkerr(ctx->errhp, OCIStmtExecute(ctx->svchp, ctx->stmthp, ctx->errhp, (ub4)1 , (ub4) 0, (CONST OCISnapshot *) NULL, OCISnapshot *) NULL ,OCI_DEFAULT));
There are lots of examples in the $ORACLE_HOME/oci/examples directory, in particular: ociaqdemo02.c
Michael Hartley.
|
|
|
|
| Re: query does not work [message #113187 is a reply to message #113064] |
Thu, 31 March 2005 08:14 |
nemaria
Messages: 7
Registered: March 2005
|
Junior Member |
|
|
Hi Michael!
Thank yuo very much for your help.
I changed my code the following way:
#include "stdafx.h"
static OCIEnv *p_env;
static OCIError *p_err;
static OCISvcCtx *p_svc;
static OCIStmt *p_sql;
static OCIDefine *p_dfn = (OCIDefine *) 0;
static OCIBind *p_bnd = (OCIBind *) 0;
struct OCIEnv { };
struct OCIError { };
struct OCISvcCtx { };
struct OCIStmt { };
struct OCIDefine { };
struct OCIBind { };
struct OCISnapshot { };
/* Function prototypes for locally defined functions but with comments */
static void checkerr(OCIError *errhp, sword status);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
void main(/*_ int argc, char *argv[] _*/);
static sword status;
void main()
{
int p_bvi;
char p_sli[20];
int rc;
char errbuf[100];
int errcode;
checkerr(p_err, OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, /* Initialize OCI */
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0 ));
/* Initialize evironment */
checkerr(p_err, OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 ));
/* Initialize handles */
checkerr(p_err, OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0));
checkerr(p_err, OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0));
/* Connect to database server */
checkerr(p_err, OCILogon(p_env, p_err, &p_svc, (OraText*)"north8", 6, (OraText*)"demo", 4, (OraText*)"astusr", 6));
/* Allocate and prepare SQL statement */
checkerr(p_err, OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
text *sqlstmt = (text *)"select ename from emp where deptno=:x" ;
deptno=:x" ;
checkerr(p_err, OCIStmtPrepare(p_sql, p_err, sqlstmt,
(ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
/* Bind the values for the bind variables */
p_bvi = 10; /* Use DEPTNO=10 */
checkerr(p_err, OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":x",
-1, (dvoid *) &p_bvi, sizeof(int), SQLT_INT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
/* Define the select list items */
checkerr(p_err, OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) &p_sli,
(sb4)sizeof(p_sli), SQLT_STR, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, OCI_DEFAULT));
/*checkerr(p_err, OCIDefineByPos(p_sql, &p_dfn, p_err, (ub4) 1, (dvoid *) &p_sli,
(sb4) sizeof(p_sli), (ub2) SQLT_STR, (dvoid *) 0, (ub2 *) 0,
(ub2 *) 0, (ub4) OCI_DEFAULT));*/
/* Execute the SQL statment */
checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
while(rc != OCI_NO_DATA) { /* Fetch the remaining data */
printf("%s\n",&p_sli);
rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);
}
checkerr(p_err, OCILogoff(p_svc, p_err)); /* Disconnect */
checkerr(p_err, OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT)); /* Free handles */
checkerr(p_err, OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX));
checkerr(p_err, OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR));
return;
}
void checkerr(OCIError *errhp, sword status)
{
text errbuf[512];
sb4 errcode = 0;
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
(void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
(void) printf("Error - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
(void) printf("Error - OCI_NODATA\n");
break;
case OCI_ERROR:
(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
(void) printf("Error - %.*s\n", 512, errbuf);
//exit(8);
break;
case OCI_INVALID_HANDLE:
(void) printf("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
(void) printf("Error - OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
(void) printf("Error - OCI_CONTINUE\n");
break;
default:
break;
}
}
Now the problem is that I get the following message:
"Unhandled Exception:
System.NullReferenceException: Object reference not set to an instance of an object.
At OCIStmtExecute(OCISvcCtx*, OCIStmt*, OciErr*, UInt32, UInt32,
OCISnapshot *, OCISnapshot *, UInt32)"
The message appears when I try to execute the select statement that returns something. If I run the Update statement, it executes OK. What can be wrong?
|
|
|
|
| Re: query does not work [message #113252 is a reply to message #113187] |
Thu, 31 March 2005 17:59 |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
Hi,
I'm surprised the code compiles, see the following lines:
--cut
text *sqlstmt = (text *)"select ename from emp where deptno=:x" ;
deptno=:x" ;
checkerr(p_err, OCIStmtPrepare(p_sql, p_err, sqlstmt,
(ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
--cut
However, assuming this is a copy/paste error and that you code compiled and executed...
Have you tried executing the code without the need for bind variables?
eg. change
text *sqlstmt = (text *)"select ename from emp where deptno=:x" ;
to
text *sqlstmt = (text *)"select ename from emp where deptno=10" ;
(substitute your own value from deptno), and don't forget to comment out the bind variable declaration and OCI calls.
Michael.
|
|
|
|
| Re: query does not work [message #113320 is a reply to message #113252] |
Fri, 01 April 2005 06:36 |
nemaria
Messages: 7
Registered: March 2005
|
Junior Member |
|
|
Yes, Michael, I tried different queries, also without binding variables, like "select 5 from dual", "select ename from emp where deptno=10", etc. If the query returns no rows, I get message that there was no data selected. If I write update statement like "update emp set ename='olly lukoye' where deptno=10"(then I don't run OCIDefineByPos function, of cause - don't define the select list items), it updates OK. But when the query returns some value, there is the mentioned problem.
I don't know why it happens - may be because the program fails to put the selected values to the select list, may be, for some other reason. I can't find reference to which object the Exception is talking about. Anyway, I'm stuck.
I run the program on Visual Studio .Net. Maybe there is some bug on it? Or I should add some libraries.
There was one strange thing that happened before I added
"struct OCISnapshot { }" to my code. When I was running the update or query that returned no rows, it was OK(like now), but if I was running the query that returned some value(values) I was getting exception that OCISnapshot was not bound(or something like that). Strange, isn't it? It shows, as far I understand it, that the Execute function uses OCISnapshot struct only when I run the query statement that returns value, and not update statement. Maybe, understanding of this will help to solve this problem?
Nemaria.
|
|
|
|
|
|
|
|
| Re: query does not work [message #115269 is a reply to message #112681] |
Fri, 08 April 2005 12:40 |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
Hi,
Sorry for the delay - been dealing with my own hectic life first.
Here's the fix to your problem. Notice that I've parameterised the username/password/database as OraText, the same with the sql statement
But most importantly I used strlen() functions to pass the correct length of the strings to each of the OCI calls, in particular you failed to catch the invalid parse return because you have been passing a value of 137 as the string length of your query.
Additionally, you were getting the garbled output because you were only checking for != NO_DATA rather than checking for success. There are many ways of a fetch/execute failing and NO_DATA is only one of them.
See oci.h line 1079 "Error Return Values".
Working and tested code below. You will need to change the username/password/database strings to your own values.
regards
Michael Hartley
http://www.openfieldsolutions.co.uk
#include <oci.h>
static OCIEnv *p_env;
static OCIError *p_err;
static OCISvcCtx *p_svc;
static OCIStmt *p_sql;
static OCIDefine *p_dfn = (OCIDefine *) 0;
static OCIBind *p_bnd = (OCIBind *) 0;
struct OCIEnv { };
struct OCIError { };
struct OCISvcCtx { };
struct OCIStmt { };
struct OCIDefine { };
struct OCIBind { };
struct OCISnapshot { };
/* Function prototypes for locally defined functions but with comments */
static void checkerr(OCIError *errhp, sword status);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);
static sword status;
int main()
{
int p_bvi;
char p_sli[20];
sword rc;
char errbuf[100];
int errcode;
checkerr(p_err, OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, /* Initialize OCI */
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0 ));
/* Initialize evironment */
checkerr(p_err, OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 ));
/* Initialize handles */
checkerr(p_err, OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0));
//checkerr(p_err, OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0));
if ( rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0) )
{
printf("FAILED: OCIHandleAlloc() on svchp, RC = %d\n", rc);
exit(OCI_ERROR);
}
/* Connect to database server */
OraText * username = "un";
OraText * password = "pw";
OraText * database = "db";
checkerr(p_err, rc = OCILogon(p_env, p_err, &p_svc, username, strlen(username), password, strlen(password), database, strlen(database)));
if (rc != 0) {
OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, (OraText*)errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
printf("Error - %.*s\n", 512, errbuf);
exit(8);
}
printf("log: Logged on Ok\n");
/* Allocate and prepare SQL statement */
checkerr(p_err, OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
printf("log: allocated\n");
OraText *sqlstmt = (OraText *)"select sid from v$session where sid>:x" ;
checkerr(p_err, OCIStmtPrepare(p_sql, p_err, sqlstmt,(ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
printf("log: prepared sql statement\n");
/* Bind the values for the bind variables */
p_bvi = 1; /* Use DEPTNO=10 */
checkerr(p_err, OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":x",
-1, (dvoid *) &p_bvi, sizeof(int), SQLT_INT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
printf("log: bind the values completed\n");
/* Define the select list items */
checkerr(p_err, OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) &p_sli,
(sb4)sizeof(p_sli), SQLT_STR, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, OCI_DEFAULT));
printf("log: define by pos completed\n");
/*checkerr(p_err, OCIDefineByPos(p_sql, &p_dfn, p_err, (ub4) 1, (dvoid *) &p_sli,
(sb4) sizeof(p_sli), (ub2) SQLT_STR, (dvoid *) 0, (ub2 *) 0,
(ub2 *) 0, (ub4) OCI_DEFAULT));*/
/* Execute the SQL statment */
checkerr(p_err, rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
printf("log: stmtexecute completed\n");
printf("fetch return code value = %d\n",rc);
while(rc == OCI_SUCCESS ||
rc == OCI_SUCCESS_WITH_INFO) { /* Fetch the remaining data */
printf("Row Fetch Result = %s\n",&p_sli);
rc = OCIStmtFetch(p_sql, p_err, 1, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
printf("fetch return code value = %d\n",rc);
printf("log: loop fetch completed\n");
}
checkerr(p_err, OCILogoff(p_svc, p_err)); /* Disconnect */
printf("log: logged off ok\n");
if ( p_sql ) {
checkerr(p_err, OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT)); /* Free handles */
printf("log: freed statement handle ok\n");
}
if ( p_svc) {
checkerr(p_err, OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX));
printf("log: freed service handle ok\n");
}
if ( p_err) {
OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);
printf("log: freed error handle ok\n");
}
return 0;
}
void checkerr(OCIError *errhp, sword status)
{
text errbuf[512];
sb4 errcode = 0;
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
(void) printf("Error - OCI_NEED_DATA");
break;
case OCI_NO_DATA:
(void) printf("Error - OCI_NODATA");
break;
case OCI_ERROR:
(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
(void) printf("Error - %.*s\n", 512, errbuf);
//exit(8);
break;
case OCI_INVALID_HANDLE:
(void) printf("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
(void) printf("Error - OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
(void) printf("Error - OCI_CONTINUE\n");
break;
default:
break;
}
}
|
|
|
|
Goto Forum:
Current Time: Tue Dec 17 22:59:54 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
I'm looking forward for your help.