At 08:13 AM 8/04/2004 +0000, you wrote:

> > >
> > >update EMPLOYEE set LAST_NAME='XX'
> > >where ((LAST_NAME=:old_last_name1) or
> > > ((:old_last_name2 is null) and (LAST_NAME is null)))
> > >
> > >
> > >I get -804 Sql error code and Data type unknown message. I tried
> > >FIBPlus and IBX, both failed.
> > >I found that the problematic part is "(:old_last_name2 is null)"
> > >expression.
> > >If I remove that there's no trouble. What can I do?
> >
> > If you have a column name old_last_name2, then just take the colon
> > off. You can't parameterise a column name, only a value, i.e.
> > column_name = :avalue
> >
>
>Old_last_name2 isn't a columname, it's really a parameter. It's an
>autogenerated sql statement, which I want to use in a 3-tier
>application, when I'm updating the database in the middle-tier using
>the deltas send by the clients. Naturally its where-clause contains
>more criterias (fields) but they have the same structure. It's aiming
>to avoid that a user can overwrite changes written by another user. In
>other words: update can be successful only if fieldvalues in the db
>are the same as the oldvalues of the delta or both are nulls. I named
>old_last_name1 and old_last_name2 parameters differently, but they
>would contain the same value,



>if I could prepare the sql statement.
>I think the question is the following: Is Firebrid able to prepare and
>execute a statement using a parameter with/in an IS NULL expression?

No, that's not the question. A parameterised search condition has to be
where columnname = value

When you use Delphi parameters, its the "value" part that gets substituted,
not the column name. At the interface, it gets translated to

where columnname = ?

There is no provision/structure/whatever to treat column names as
variables, which is what you want to do.

You will have to take it back a step and construct "stub" statements with
no where clauses. At run time, collect both the search columns and the
values from the user interface and construct a where clause to append to
the "stub".

Incidentally, the "value" part (which IS legally replaceable) is not a
variable either. It's a placeholder which is legal at Prepare time and
illegal at all other times. The actual statement parameters are always
resolved to constants when the statement instance is submitted to the server.

btw, a prepared query can only stay prepared if you don't change its
structure. If you change the columns in the search clause, you change the
structure of the query, so there is no choice but to reprepare the statement.

/heLen

>Thanks in advance
> Tamas Varga
>
>
>P.S. Sorry for my English, I hope you understand me.
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>