OCI in Binding poblem. 1

[TheMillionDollarMan]

hi.
I'm using
MSVC6.0, Windows 2k, Ora9. OCI8.

This is the code that binds the input varibale:
CIBindByName(stmthp, &bndhp, errhp, (text *) ":RCRD_ID",
strlen(":RCRD_ID"), (ub1 *) cid, cidlen+1, SQLT_CHR , (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)

It completes successfully however when I execute the Sql statement it always returns for "No Data found." can any one help me identify the problem?

Thanks
Dave

 

[Cagliostro]

I think NoDataFound you get there because you have somewhere a null value.
look there:
OCIBindByName(stmthp, &bndhp, errhp, (text *) ":RCRD_ID",
strlen(":RCRD_ID"), (ub1 *) cid, cidlen+1, SQLT_CHR , (dvoid *) 0, (ub2 *) 0, (ub2) 0, u(ub4) 0, (ub4 *) 0, OCI_DEFAULT);

you should give an indicator
int ind;
OCIBindByName(stmthp, &bndhp, errhp, (text *) ":RCRD_ID",
strlen(":RCRD_ID"), (ub1 *) cid, cidlen+1, SQLT_CHR , (dvoid *) &ind, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
If indicator equals to -1 then there is a NULL value, so NoDataFound is just a warning what value of bound buffer have not changed but there is no value.


Ion Filipski

 

[TheMillionDollarMan]

Thanks Ion. I found the root cause.
When in SQL Plus
Select * from myTable where TextField='Dave';
in OCI

Select * from myTable where TextField=:TextFieldVariable;
char TextFieldVariable[9];
sprintf(TextFieldVariable,"Dave");

NO SINGLE QUOTES ON THE TEXT FIELD. One of those frustrating little things. A good reason for sharing with this forum.
Enjoy summer if you live in the northen hemisphere.

OCIBindByName(stmthp, &bndhp, errhp, (text *) ":TextFieldVariable",
strlen(":TextFieldVariable"), (ub1 *) TextFieldVariable, TextFieldVariablelen+1, SQLT_STR , (dvoid *) 0,
(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)



D

 

[Cagliostro]

don't forget about indicator variable hilighted in the potst above with red color.

Let's suppose following situation:
Select * from myTable where TextField=:TextFieldVariable;

you would pass in :TextFieldVariable a NULL. So, you should set indicator to -1. If you don't use indicator you will not be able to pass null.

Ion Filipski

 

[TheMillionDollarMan]

ooohhhh. hey thats a great tip.
I just adjusted my sql statement from
Select OtherTextField from myTable where TextField=:TextFieldVariable;

to
Select NVL(OtherTextField,' ') as Txt1 from myTable where TextField=:TextFieldVariable;

Thanks Ion!

 

[Cagliostro]

I'd say, you shouldn't changr NULL to something else with NVL. NoDataFound is a warning, not an error, so you can fetch results on after you get this. Only you should do (but you are not obligated to do) is to see which one of result is a null.

Ion Filipski