BorlandTalk.com

dominique · Guest

Problem in setting bind variable with borland cpp and oracle 9.2

case #1:

TQuery *qy=NewQuery(Application);
qy->SQL->Text=" SELECT use_employee_num ";
qy->SQL->Add("FROM t_user ");
qy->SQL->Add("WHERE use_employee_num=:prmEmpNum ");
qy->ParamByName("prmEmpNum")->AsString=aEmployeeNum;
qy->Open();

the result of this query is the same as this one : Select use_employee_num From t_user where use_employee_num=' '
The bind variable prmEmpNum seems not to be set.

Case #2
TQuery *qy=NewQuery(Application);
qy->SQL->Text=" SELECT use_employee_num ";
qy->SQL->Add("FROM t_user ");
qy->SQL->Add("WHERE use_employee_num='" + aEmployeeNum + "' ");
qy->Open();

the result of this query is the result of : Select use_employee_num From t_user where use_employee_num='aEmployeeNum'
and aEmployeeNum is not an empty string

does anyone know why there is difference in the result between these 2 queries and why the bind variable seems not to be set ?

Thanks in advance

dominique · Guest

Hello Jayme,

Yes I run with Debug.
aEmployeeNum is an AnsiString and its value is a valid employee num.
I execute the syntax #1 with Informix and there is no pb but with oracle there is no result.
Do you think I have to declare the bind variable before ?

Thanks for your advice regarding ODAC, I will try it as soon as I can..

Dominique

"Jayme Jeffman Filho" <jjeffman (AT) cpovo (DOT) net> wrote:

Jayme Jeffman Filho · Guest

Hello,

Did you run the application with debug ? What is the type of "aEmployeeNum" ? What is the value of "aEmployeeNum" ?

You should try ODAC components (http://crlab.com/odac/) , they are very good and cheap !

Jayme.

"dominique" <dominique.hugot (AT) hp (DOT) com> wrote

Jayme Jeffman Filho · Guest

Hello,

I think you can try set the ParamType and DataType of the parameter and call the Prepare() method before doing that. So insert the line "if(!qy->Prepared) qy->Prepare();" after the "where" clause and before execute the query.

HTH

Jayme.

"dominique" <dominique.hugot (AT) hp (DOT) com> wrote

dominique · Guest

Hello,

I tried this code but it's still not working, an idea ?

TQuery *qy=NewQuery(Application);
TParam *prm = NULL;
if ( qy ){
try{
prm = qy->Params->CreateParam(ftString, "use_employee_num",ptInput);
qy->SQL->Add("SELECT use_employee_num ");
qy->SQL->Add("FROM t_user ");
qy->SQL->Add("WHERE use_employee_num = :use_employee_num ");
qy->ParamByName("use_employee_num")->AsString = "toto";
//paramstring help me to debug
AnsiString paramstring = qy->ParamByName("use_employee_num")->AsString;
if(!qy->Prepared) qy->Prepare();
qy->Open();

And there is still no result... but if i replace the where clause with ..."WHERE use_employee_num='" + toto +"'" there is one result returned..

Thanks for the previous messages...

"Jayme Jeffman Filho" <jjeffman (AT) cpovo (DOT) net> wrote:

Jayme Jeffman Filho · Guest

Hello,

Try this one :

TQuery *qy=NewQuery(Application);
// TParam *prm = NULL;
if ( qy ){
try{
//prm = qy->Params->CreateParam(ftString, "use_employee_num",ptInput);
qy->SQL->Add("SELECT use_employee_num ");
qy->SQL->Add("FROM t_user ");
qy->SQL->Add("WHERE use_employee_num = :use_employee_num ");
// I've changed the line order
if(!qy->Prepared) qy->Prepare();
qy->ParamByName("use_employee_num")->AsString = "toto";
//paramstring help me to debug
AnsiString paramstring = qy->ParamByName("use_employee_num")->AsString;
qy->Open();

HTH

Jayme.

"dominique" <dominique.hugot (AT) hp (DOT) com> wrote

dominique · Guest

Hello,
I tried it but it's still not working.
I use Cpp builder version 5.0 , do you think I could try to updgrade cpp builder or bde release ?
I think I could try to change my oracle odbc driver, what do you think about that ?

"Jayme Jeffman Filho" <jjeffman (AT) cpovo (DOT) net> wrote:

Jayme Jeffman Filho · Guest

Hello,

Did you try the same kind of query using another field in the where clause, a number field for example ?
I use the same structure as you but with Oracle 8i (8.1.7) and it works very well. What ODBC driver are you using ? The Oracle or Micrsoft ? If Microsoft try Oracle .

HTH

Jayme.

"dominique" <dominique.hugot (AT) hp (DOT) com> wrote

dominique · Guest

Hello,
You're right the same kind of query with a number field works very well. Is there a particular thing to do with string fields ?

FYI: I use Oracle 9.2 and Oracle ODBC driver distributed with the Oracle 9.2 client....

Your advices help me to understand step to step my problem...

Dominique

"Jayme Jeffman Filho" <jjeffman (AT) cpovo (DOT) net> wrote:

Jayme Jeffman Filho · Guest

Hello,

It seems that the AsString property is not working. Lets try to set the parameter ParamType and DataType properties after preparing the query and then pass the bind value as a string.

TQuery *qy= NewQuery(Application);
if ( qy ){
try{
qy->SQL->Add("SELECT use_employee_num ");
qy->SQL->Add("FROM t_user ");
qy->SQL->Add("WHERE use_employee_num =3D3D :use_employee_num ");
if(!qy->Prepared) qy->Prepare();
qy->ParamByName("use_employee_num")->ParamType = ptInput; // ==>> new line
qy->ParamByName("use_employee_num")->DataType = ftString ; // ==>> new line
qy->ParamByName("use_employee_num")->AsString = "toto";
//paramstring help me to debug
AnsiString paramstring = qy->ParamByName("use_employee_num")->AsString;
qy->Open();

If this code still not work try exchange the AsString property by the Value property :
qy->ParamByName("use_employee_num")->Value = "toto";

I've played all the cards I had on my hand. If it don't workout it should be an Oracle 9.2 version issue with BDE components. Try to download a demo version of ODAC components ([url]http://www.crlab.com/odac)[/url], they work with Oracle from 8.04 to the 9th version.

HTH

Jayme.

"dominique" <dominique.hugot (AT) hp (DOT) com> wrote