Subject RE: [IBO] SQLWhereItems->Add - Problem
Author Daniel Jimenez
Hi Helen,

It is Version 4.3 Aa purchased within the last 2 weeks.

I actually completed some more testing, and have made the following
observations.

(1)
If I place a WHERE statement in the TIB_Cursor SQL String such as:

SELECT *
FROM
LOGPAS
WHERE R_CREATED_BY = :CURRENT_USER

The parser automatically will add an (AND) to the end regardless. Thus when
I use the

SQLWhereItems->Add()

If it happens to start with an (OR) the parser will not remove the (AND), if
it does not start with an (OR) the parser will add another (AND)

(2)
If I do not place a WHERE statement in the TIB_Cursor SQL, but build it
using the

SQLWhereItems->Add

Such as:

crsCVDBCursor->SQLWhereItems->Add("R_CREATED_BY = :CURRENT_USER");
crsCVDBCursor->SQLWhereItems->Add("OR");
crsCVDBCursor->SQLWhereItems->Add("R_SHARED = 1");

The parser will place and (AND) prior to the (OR) and another after the (OR)
so it looks like:

SELECT *
FROM
LOGPAS
WHERE (
(
R_CREATED_BY = ?/*CURRENT_USER*/
)
AND OR
)
AND R_SHARED = 1
FOR UPDATE

(3)
If I follow the same procedure as in point (2), but add the following:

crsCVDBCursor->SQLWhereItems->Add("(");
....
crsCVDBCursor->SQLWhereItems->Add(")");

Prior two lines to my code, the parse behaves correctly.

(4)
On the other hand, if I add the ( to the first line of the
SQLWhereItems->Add() containing a string, as well as to the last line such
as:

crsCVDBCursor->SQLWhereItems->Add("(R_CREATED_BY = :CURRENT_USER");
crsCVDBCursor->SQLWhereItems->Add("OR");
crsCVDBCursor->SQLWhereItems->Add("R_SHARED = 1)");

The parser will place an (AND) prior to the (OR) and another after the (OR)
so it looks like:

SELECT *
FROM
LOGPAS
WHERE (
(
(R_CREATED_BY = ?/*CURRENT_USER*/
)
AND OR
)
AND R_SHARED = 1)
FOR UPDATE

Which is the same problem as in point (2) but with the added ( at the start
and ) at the end of the SQl which was build on the fly.

As you can see the main problem is that I am not able to add a where clause
to the TIB_Cursor Sql string and then use SQLWhereItems->Add to build the
rest of the SQL on the fly.

Thank you

Daniel

____________________________
Comvision Pty. Ltd.

www.comvision.net.au


You might have found a bug. Could you let us know what version of IBO you
are using? If I have the same version here, I can look at it in about an
hour.

Helen