[AccessD] db execute on linked vs local table

[Paul Wolstenholme]

Ryan,

There is general agreement that FALSE = 0.
In the absence of any generally accepted definition of TRUE (other than "<>
FALSE" or "NOT FALSE") I refuse to write code that contains "TRUE".  Maybe
it keeps me out of unnecessary trouble.
Sometimes TRUE gets turned into 1 and sometimes it gets turned into one of
the versions of -1 (with any number of bits).  Sometimes what you get isn't
what you need.

You haven't stated whether the back end table is Access or something else.
It can make a difference.
Some also believe in avoiding the yes/no field type in Access tables (
http://allenbrowne.com/NoYesNo.html) so perhaps you are using a numeric
Access field?

Paul Wolstenholme

On Tue, 1 Feb 2022 at 10:14, Ryan W <wrwehler at gmail.com> wrote:

> More info:
>
> If I set the column to not allow nulls, db.execute tries to set it as NULL
> but then a prepared statement comes in after the fact and does indeed set
> it to true.
>
> https://i.imgur.com/m8TSBN6.png
>
> On Mon, Jan 31, 2022 at 2:40 PM Ryan W <wrwehler at gmail.com> wrote:
>
> > Upon more testing it seems like FALSE (without quotes) works as well. It
> > just seems to be TRUE (no quotes) sets my remote bit column to NULL.
> >
> > Here's a SQL Profiler:
> >
> > https://i.imgur.com/rVx3vmX.png
> >
> >
> >
> >
> >
> > On Mon, Jan 31, 2022 at 1:48 PM Ryan W <wrwehler at gmail.com> wrote:
> >
> >> Is there a reason db.execute syntax is a bit different on a linked
> (ODBC)
> >> vs local (DAO?)
> >>
> >> db.execute "UPDATE tbl SET col_A = TRUE" works as expected locally.
> >>
> >> where run on a linked table, it NULLs out the bit field.
> >>
> >> but db.execute "UPDATE tbl set col_A = 'TRUE'"  or db.execute "UPDATE
> tbl
> >> set col_A = 1" works remotely and the numerical representation of
> >> true/false works both remote AND locally.
> >>
> >> However 'TRUE' or 'FALSE' with the single quotes on a local table gives
> a
> >> data type mismatch (which makes sense, because it's treating it as a
> >> literal and not converting it to 1/0.
> >>
> >>
> >> I guess my question is, is it the difference in the DAO engine vs the
> >> ODBC layer that is turning my BIT field NULL (linked) versus local?
> >>
> >>
> >>
> >>
> >>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>