Subject | RE: [IBO] SQLWhereItems->Add - Problem |
---|---|
Author | Daniel Jimenez |
Post date | 2004-10-12T23:18:13Z |
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
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