(true = 1) returns false?

J

Jason

I am fairly new to ASP--I have been using it about 2 months. I did these
tests (below), and it doesn't make sense to me. False is equal to 0, and
that's fine. True should be equal to 1, but it's not. Actually, True
should be equal to anything but False, null, and 0. Is there a workaround
for this? Or do I need to change all my comparisons to = 1 instead of =
true?

response.write True = 1 'prints False
response.write True = 0 'prints False
response.write False = 1 'prints False
response.write False = 0 'prints True

Just fyi, I am trying to convert our database from Access to MySQL. Access
stores boolean as true/false, and MySQL stores booleans as 1 and 0, which is
why I have this problem.

Thanks in advance for your responses.

--Jason
Webmaster
Substitute Teaching Institute,
Utah State University
 
B

Bob Barrows [MVP]

Jason said:
I am fairly new to ASP--I have been using it about 2 months. I did
these tests (below), and it doesn't make sense to me. False is equal
to 0, and that's fine. True should be equal to 1,

Why do you think that?
See
http://msdn.microsoft.com/library/en-us/script56/html/38045e3b-d4fb-4958-b6c3-1e500872d64f.asp

but it's not. Actually, True should be equal to anything but False, null,
and 0.

No, that's not the way it works. In vbscript, if an expression evluates
to -1 then the statement is true. Otherwise, it is false
Is there a workaround for this? Or do I need to change all my
comparisons to = 1 instead of = true?

Huh? Why would you need to do that? Give me an example of something that is
failing you because you are saying "= true" instead of "= -1"
response.write True = 1 'prints False
response.write True = 0 'prints False
response.write False = 1 'prints False
response.write False = 0 'prints True

Try:
Response.Write CBool(-1)
Response.Write CBool(0)
Response.Write CBool(1)
Response.Write CBool(null)
Just fyi, I am trying to convert our database from Access to MySQL.

You're familiar with Access? True is not even equal to 1 in Access!

Where did you get the idea that it was equal to 1 in Access?
Access stores boolean as true/false,

In actuality, Access (Jet) stores 0 for false/off/no and -1 for true/on/yes.
This is easy to find out on your own. Create a table with a single Yes/No
field, put some data inot it, then run this sql statement:

select boolean_column, cint(boolean_column) as int_equivalent
from yourtable
and MySQL stores booleans as 1
and 0, which is why I have this problem.

I don't work with MySQL but I don't think MySQL has a boolean datatype; at
least it doesn't if it's similar to SQL Server, which has a bit datatype,
not boolean. What's the difference, you ask? A boolean can have two values:
true or false. A bit can have 3 possible values: true, false or null.

The OLE DB provider commonly handles conversions between vbscript and sql.
So if you run a query against Access, you treat the data returned as if it
was a native vbscript boolean subtype. The same thing will occur if you go
against mysql or sql server: the bit datatype will be correctly translated
to the vbscript boolean value.
 
J

Jason

Bob Barrows said:
No, that's not the way it works. In vbscript, if an expression evluates
to -1 then the statement is true. Otherwise, it is false

I should have worded my question differently. That's what I was asking
though, if VBscript followed the traditional definitions for true and false.
Huh? Why would you need to do that? Give me an example of something that
is failing you because you are saying "= true" instead of "= -1"

MySQL stores true as 1, not -1.
You're familiar with Access? True is not even equal to 1 in Access!

Not really that familiar with Access, no.
Where did you get the idea that it was equal to 1 in Access?

I didn't say that, but it's irrelevant now.
I don't work with MySQL but I don't think MySQL has a boolean datatype; at
least it doesn't if it's similar to SQL Server, which has a bit datatype,
not boolean. What's the difference, you ask? A boolean can have two
values: true or false. A bit can have 3 possible values: true, false or
null.

MySQL stores booleans as an integers (unsigned I think), one byte big. 0 is
false, 1 is true. I haven't tested this, but I think MySQL treats any value
other than 0 as true. However, when you store a value as true, it's stored
as 1.
The OLE DB provider commonly handles conversions between vbscript and sql.
So if you run a query against Access, you treat the data returned as if it
was a native vbscript boolean subtype. The same thing will occur if you go
against mysql or sql server: the bit datatype will be correctly translated
to the vbscript boolean value.

That's the problem then, it's not being translated correctly from MySQL.
I'm not sure if I am using OLE DB or not.
This is the code I use to connect:

Dim sConnection, mysqlConn
sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;
DATABASE=myDB; UID=user;PASSWORD=pass; OPTION=3"
Set mysqlConn = Server.CreateObject("ADODB.Connection")
mysqlConn.Open(sConnection)

Is that using the OLE DB provider?
 
P

Patrice

The relevant part would be rather how you pass those data to MySQL rather
than the connection string...

You way want to use parametized queries instead of stuffing text into an SQL
statement (if this is the problem). It will take care of this as well as
from possible problem with date formats and decimal values whose text is
country dependant...

A MySQL forum might be a better place (for example if this something
specific to the dat type you used for your column, is this defined as a
boolean or as an integer ?)...
 
B

Bob Barrows [MVP]

Jason said:
I should have worded my question differently. That's what I was
asking though, if VBscript followed the traditional definitions for
true and false.

I guess I'm having trouble with the word "traditional". But I think the
question is answered.
MySQL stores true as 1, not -1.

I'm not sure this is relevant, but given that I've never used MySQL, it may
be.

Could you show me an example where this causes a problem for you? Maybe we
can suggest something if the MySQL ODBC driver is not doing what it's
supposed to do.
That's the problem then, it's not being translated correctly from
MySQL. I'm not sure if I am using OLE DB or not.
This is the code I use to connect:

Dim sConnection, mysqlConn
sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;
DATABASE=myDB; UID=user;PASSWORD=pass; OPTION=3"
Set mysqlConn = Server.CreateObject("ADODB.Connection")
mysqlConn.Open(sConnection)

Is that using the OLE DB provider?

With ADO, you are always using an OLE DB provider. In this case you are
using the MSDASQL provider, which is the Provider for ODBC, i.e., it
provides the interface between ODBC drivers and ADO.
 
J

Jason


Just an fyi, I did a test, and MySQL does not treat any value besides 0 as
true, so sorry about that bad info.
I'm not sure this is relevant, but given that I've never used MySQL, it
may be.

Could you show me an example where this causes a problem for you? Maybe we
can suggest something if the MySQL ODBC driver is not doing what it's
supposed to do.

Sure. Here's a test table I created.

create table test (
id int not null primary key,
name char(30),
isMale bool
);

Which generates this table:

+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id int(11) PRI 0
| name char(30) YES NULL
| isMale tinyint(1) YES NULL
+--------+------------+------+-----+---------+-------+

so you can see the bool is stored as a 1 byte int.

Then I insert two rows:
insert into test VALUES(1, 'Jack', true);
insert into test VALUES(2, 'Jill', false);

The resulting table rows are:
+----+------+--------+
| id | name | isMale |
+----+------+--------+
| 1 Jack 1
| 2 Jill 0
+----+------+--------+

1 for true, 0 for false.

This is some sample code to demonstrate the problem:
'--------
strquery = "SELECT * FROM test"
set rsobj = objconn.execute(strquery)

while NOT rsobj.eof

if rsobj("isMale") = true then
response.write rsobj("name") & "<br />"
end if

rsobj.MoveNext
wend
'---------

Nothing prints. If I change the IF statement to ' if rsobj("isMale") =
false then ' then it will print ' Jill '. Or if I change the value of
isMale for Jack to -1, then it will also work as intended, but MySQL doesn't
recognize -1 as true. I realize I may have to change my code, but I would
like to avoid that if possible becuase it would take a lot of changes.

It must store bools as signed ints, instead of unsigned ints, since it let
me change it to -1, so sorry about that bad info before also.

Thank you for your replies.

--Jason
 
J

Jason

Patrice said:
The relevant part would be rather how you pass those data to MySQL rather
than the connection string...

You way want to use parametized queries instead of stuffing text into an
SQL statement (if this is the problem). It will take care of this as well
as from possible problem with date formats and decimal values whose text
is country dependant...

A MySQL forum might be a better place (for example if this something
specific to the dat type you used for your column, is this defined as a
boolean or as an integer ?)...

To be honest, I am not real familiar with parametized queries. Would using
parametized queries require a code change in every place that I query the db
and get bool results back?

Thanks for your reply.

--Jason
 
P

Patrice

Yes, it would require changes...

Let's take things the other way round. My first thought would be that MySQL
doesn't really have boolean columns (I thought of this because you said that
MySQL consider as true any non 0 value. If the column was really a boolean
you could only have 3 possible values).

If MySQL doesn't provide an explicit boolean datatype the db layer won't
have any kind of translation... You could easily then transmit a wrong
value.

Don't assume anything about the value of boleans and use something like
IIf(Condition,1,0)so that you explicity code the value used by MySQL in your
SQL statements (of course you could use your own small function so that you
can eventually change this one day). It will also require changes... Finally
you could just transform -1 to 1 server side if possible (would require just
a small server side change ?)
 
J

John Fullmer

Good example of this is the .checked value of a checkbox. it will
return a -1 if checked (vbscript). Easy to work around with a
CBool(obj.checked) or cint(obj.checked) * -1 depending on the desired
output.

~ John Fullmer
 
J

Jason

You're right, MySQL doesn't really have boolean data types. They are just
ints.

I could change all the 1's to -1's in the database, but then queries like
"SELECT * FROM table WHERE isOpen = true" would fail. MySQL only sees 1 as
true, 0 as false, and anything else is neither.

I am not sure what you mean by server side--the problem happens before the
client ever sees any html. The root of the problem is the mysql definition
of true vs the vbscript definition of true, where mysql defines trues as 1,
and vbscript defines true as -1. So I can either change the values in the
database, or I can change my code, and I don't want to change the database
for the reason stated above. So basically, I am left with changing code,
and that wouldn't be a quick change--there are lots of places I would have
to change, but I don't see another option. I think the best option would be
to use the CBool() function, so that is what I am going to do.

thanks for your replies

--Jason
 
J

Jason

Yeah, and I don't really see a better option than using CBool(). It won't
be quick but at least it will work.

--Jason
 
R

roger

while NOT rsobj.eof

if rsobj("isMale") = true then
response.write rsobj("name") & "<br />"
end if

rsobj.MoveNext
wend


How about...

if rsobj("isMale") then
response.write rsobj("name") & "<br />"
end if

Does that do what you want?
 
J

Jason

roger said:
"Jason" <bigwheels16 hotmail> wrote in message


How about...

if rsobj("isMale") then
response.write rsobj("name") & "<br />"
end if

Does that do what you want?

Well, if that worked, then yes :) but that doesn't work, which is the
problem.
 
R

roger

Well, if that worked, then yes :) but that doesn't work, which is the
problem.

Perhaps I am not understanding the problem

dim b

b = 1
if b then Response.Write "true = 1"
if b = true then Response.Write "but this doesn't work"

gives me the result...

true = 1

Isn't that what you want?
 
J

Jason

roger said:
Perhaps I am not understanding the problem

dim b

b = 1
if b then Response.Write "true = 1"
if b = true then Response.Write "but this doesn't work"

gives me the result...

true = 1

Isn't that what you want?

Yes. That's exactly what I want, but your example doesn't work that way for
me when I do it. For me, b = 0 evaluates to false, b = -1 evaluates to
true, and if b equals any other number, then b is neither true nor false.
Are you sure that code works for you? I mean, did you test it? Because if
it does, I would be interested to know why it works for you and not for me.

--Jason
 
E

Evertjan.

Jason wrote on 06 jun 2006 in microsoft.public.inetserver.asp.general:
Yes. That's exactly what I want, but your example doesn't work that
way for me when I do it. For me, b = 0 evaluates to false, b = -1
evaluates to true, and if b equals any other number, then b is neither
true nor false. Are you sure that code works for you? I mean, did you
test it? Because if it does, I would be interested to know why it
works for you and not for me.

Then why use the internal evaluation?

Using your definition:

if b = 0 then
b = "is false"
elseif b=-1 then
b = "is true"
else
b = "is neither"
end if
 
J

Jason

Evertjan. said:
Then why use the internal evaluation?

Using your definition:

if b = 0 then
b = "is false"
elseif b=-1 then
b = "is true"
else
b = "is neither"
end if
Yeah, I could do that....but I was looking for a solution that didn't
involve changing my code every place I used 'if b = true then...'

There is no solution that I am aware of so I did change my code from 'if
b=true then...' to 'if CBool(b) = true then...' in every spot.
 
E

Evertjan.

Jason wrote on 06 jun 2006 in microsoft.public.inetserver.asp.general:
Yeah, I could do that....but I was looking for a solution that didn't
involve changing my code every place I used 'if b = true then...'

There is no solution that I am aware of so I did change my code from
'if b=true then...' to 'if CBool(b) = true then...' in every spot.

Since if then tests for true/false itself,
I would not expect any difference in behavur between:

if b = true then

and

if b then

however,
if you want to test for "true", "false" and "neither",
my above code remains necassary, IMHO.
 
J

Jason

Since if then tests for true/false itself,
I would not expect any difference in behavur between:

if b = true then

and

if b then

however,
if you want to test for "true", "false" and "neither",
my above code remains necassary, IMHO.
I don't think you understand the problem. ASP defines 0 as false and -1 as
true, and everything else is neither. I wanted a way to make ASP define 1
also as true, but we couldn't come up with one except to change code
everywhere I needed that extra functionality.
 
E

Evertjan.

Jason wrote on 06 jun 2006 in microsoft.public.inetserver.asp.general:
I don't think you understand the problem.
True.

ASP defines 0 as false and
-1 as true, and everything else is neither.

Not true.

==============

ASP is just the platform, and does not define.

==============

ASP-VBscript does boolean TEST 0 as false and all other as true.
[You could call that "define"]

if 7 then response.write "TRUE"

if NOT 0 then response.write "FALSE"

VBscript does translate in a FORMULA true as -1, and false as 0

response.write true ' shows True

response.write 7 + true ' shows 6

response.write 7 + false ' shows 7

===============

ASP-JScript TESTS [boolean] 0 as false and other numbers as true,

if (7) response.write( 'TRUE' );

if (! 0) response.write( 'FALSE' );

and does translate in a FORMULA true as 1, and false as 0

response.write( 7 + true ); // 8
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top