Subject Re: [IBO] SQLWhereItems->Add - Question
Author Helen Borrie
At 05:07 PM 12/10/2004 +1000, you wrote:

>I am trying to complete a search on a single table of a DB through the use
>of a TIB_Cursor and a TIB_CursorGrid.
>The TIB_Cursor SQL statement is:
>Now, when the application user tries a default search such as "Search All" I
>set the CURRENT_USER param...
> if( !crsCVDBCursor->Prepared )
> crsCVDBCursor->Prepare();
> crsCVDBCursor->ParamByName("CURRENT_USER")->AsString = FCurrentUser;
>I am also making use of:
> onPrepareSQL(TIB_Statement *Sender)
> {
> if( !FPrivateRecords )
> {
> crsCVDBCursor->SQLWhereItems->Add("OR");
> crsCVDBCursor->SQLWhereItems->Add("R_SHARED = 1");
> }
> }

Change this to
crsCVDBCursor->SQLWhereItems->Add("OR (R_SHARED = 1)");

>The following is the SQL send to the DB, as displayed by:
> IB_Monitor1MonitorOutputItem(TObject *Sender, const AnsiString NewString)
> {
> ShowMessage(NewString);
> }
> (
> )
> )

Yes, as you see, each line in WhereItems TStrings is ANDED unless it starts
with the keyword OR.

>The question/problem is,
>Why/Who is placing the (AND) immediately infront of the (OR) which I am
>adding though the use of the

The parser is necessarily specific about the syntax and semantics for
SQLWhereItems. This is not free-form string snippets like the SQL
property. It's one line per Whereitem. If SQLOrder has been defined also,
then the WHERE clause is not the last thing in the statement. Also, as you
observe, the FOR UPDATE clause may need to be added.

>Why/Who is placing the (FOR UPDATE) at the end of the SQL statement.

The parser, because it is an ib_cursor. Jason uses the FOR UPDATE clause to
make the server-side cursor return exactly one row per fetch (which is how
ib_cursor emulates a server-side cursor).