Real Software Forums
http://forums.realsoftware.com/

How to save a boolean?
http://forums.realsoftware.com/viewtopic.php?f=3&t=47539
Page 1 of 2

Author:  Markus Winter [ Mon Apr 08, 2013 4:29 am ]
Post subject:  How to save a boolean?

Hi all,

I'm having a mental block, but how do I save a boolean into a database?

I have set up a column "Registered" as BOOLEAN in the database, and I use

// db.SQLExecute("CREATE TABLE Products (Name varchar, Price double, InStock BOOLEAN)")

db.sqlexecute ("Insert into Products (Name, Price, InStock) Values "_
+"( 'Mac', 999, '" + Str( ItemAvailable ) + "' )")


but that seems wrong. If I save the boolean as a stringvalue then why do I bother setting up a boolean column?

I'm obviously missing something here, and the LR is no help.

TIA

Markus

Author:  timhare [ Mon Apr 08, 2013 4:56 am ]
Post subject:  Re: How to save a boolean?

All interaction with the database (*most* actually) has to be in textual form. That's the genius of SQL. The specifics of each database may differ slightly regarding how they handle boolean values, but generally, you can use True and False in an SQL statement. The database engine is free to store that value in any way it chooses. It might be as compact as a single bit, or as verbose as the literal value "False", requiring 5 bytes to store. As far as RB is concerned, Str() will resove to either True or False, so you should be ok with your code, except you shouldn't put quotes around the value. TRUE and FALSE should be reserved words in the database engine, so they don't have to be quoted. In fact, quoting them changes their meaning.

Author:  Markus Winter [ Mon Apr 08, 2013 5:40 am ]
Post subject:  Re: How to save a boolean?

If I don't quote the boolean then I get an error "No such column: True". That's why I went with Str( ItemAvailable ) in the first place, and as it is a string it needs the quotes.

Full code is

dim ItemAvailable as Boolean = True

If db.Connect() then

// db.SQLExecute("CREATE TABLE Products (Name varchar, Price double, InStock BOOLEAN)")

db.sqlexecute ("Insert into Products (Name, Price, InStock) Values "_
+"( 'Mac', 999, " + Str( ItemAvailable ) + " )")
If db.error then
MsgBox db.errormessage
else
db.Commit
End if
else
Beep
MsgBox "The database couldn't be opened."
end if

Author:  timhare [ Mon Apr 08, 2013 5:53 am ]
Post subject:  Re: How to save a boolean?

Unfortunately, every db engine treats booleans differently. SQL defines a standard, but everyone uses their own standard.

Author:  Markus Winter [ Mon Apr 08, 2013 6:10 am ]
Post subject:  Re: How to save a boolean?

timhare wrote:
Unfortunately, every db engine treats booleans differently. SQL defines a standard, but everyone uses their own standard.

Strings need to be quoted in SQL. Don't think that has anything to do with the databases.

Author:  Pengwin [ Mon Apr 08, 2013 8:36 am ]
Post subject:  Re: How to save a boolean?

I know it's not the 'proper' way to deal with booleans in a database, but if I am designing and building the database myself, I generally use a SmallInt for the boolean field and then use 0 for False and 1 for True.

Author:  HMARROQUINC [ Mon Apr 08, 2013 12:35 pm ]
Post subject:  Re: How to save a boolean?

Pengwin wrote:
I know it's not the 'proper' way to deal with booleans in a database, but if I am designing and building the database myself, I generally use a SmallInt for the boolean field and then use 0 for False and 1 for True.


That is my approach too. I hate dealing with how different database servers treat boolean values so I stick to 0 and 1.

Author:  timhare [ Mon Apr 08, 2013 1:59 pm ]
Post subject:  Re: How to save a boolean?

Markus Winter wrote:
timhare wrote:
Unfortunately, every db engine treats booleans differently. SQL defines a standard, but everyone uses their own standard.

Strings need to be quoted in SQL. Don't think that has anything to do with the databases.

Yes, but some db's recognize TRUE as a reserved word, while others do not. TRUE <> 'TRUE'. One is a boolean constant, the other is a string.

Author:  simulanics [ Thu Apr 11, 2013 6:50 pm ]
Post subject:  Re: How to save a boolean?

Side Humor: There are 10 types of people in the world...those who understand binary, and those that don't :-)

010011010110111101110011011101000010000001110111011011110110111000100111011101000010000001110101011011100110010001100101011100100111001101110100011000010110111001100100001000000110100101110100001011100010111000101110

Do you? :-D

Author:  timhare [ Thu Apr 11, 2013 7:37 pm ]
Post subject:  Re: How to save a boolean?

True.

01000010011101010111010000100000011100110110111101101101011001010010000001110111011010010110110001101100

Author:  simulanics [ Thu Apr 11, 2013 8:21 pm ]
Post subject:  Re: How to save a boolean?

timhare wrote:
True.

01000010011101010111010000100000011100110110111101101101011001010010000001110111011010010110110001101100



00111010 0010110100101001

Author:  Markus Winter [ Fri Apr 12, 2013 2:20 am ]
Post subject:  Re: How to save a boolean?

To summarize: nobody knows how to properly save a boolean value into a database with REALbasic.

Sad.

Author:  simulanics [ Fri Apr 12, 2013 10:18 am ]
Post subject:  Re: How to save a boolean?

Markus Winter wrote:
To summarize: nobody knows how to properly save a boolean value into a database with REALbasic.

Sad.


I made a little demo for you...

It's actually quite easy...but the documentation is slightly misguiding...try the demo below and view the code..then once you re-read the docs they'll make sense :-)

http://www.realdevspot.com/boolean.rar

Use a databaserecord and:

row.BooleanColumn("Registered") = BooleanValue(cmbBoolean.Text)

booleanvalue is a function in the demo that converts "True","1","Yes" to a boolean True and "False","0","No" to a boolean false... the database class will save the boolean as "true" or "false" but when invoked from the class a boolean...return a boolean True or False :-)

01001001 01110100 00100111 01110011 00100000 01100001 01101100 01101100 00100000 01100001 01100010 01101111 01110101 01110100 00100000 01100010 01101001 01101110 01100001 01110010 01111001 00100000 01101101 01111001 00100000 01100110 01110010 01101001 01100101 01101110 01100100 :-) (I broke up the characters to make it easier to translate...counting numbers is a pain :-))

Author:  Paul Lefebvre [ Sun Apr 14, 2013 11:45 am ]
Post subject:  Re: How to save a boolean?

Markus Winter wrote:
To summarize: nobody knows how to properly save a boolean value into a database with REALbasic.

Sad.

This has little to do with Real Studio, it's entirely dependent on the database you are using.

Some databases, such PostgreSQL, have a Boolean data type. So you can write:

UPDATE table SET boolColumn = TRUE


More about PostgreSQL Boolean here: http://www.postgresql.org/docs/9.0/stat ... olean.html

SQLite does not have a Boolean data type. Since Real Studio does have a Boolean data type, it has to be converted to something SQLite can handle. The two choices are to use an INTEGER column and store 0 or 1 or to use a TEXT column and save the text as "true" or "false".

To learn more about SQLite data types: http://www.sqlite.org/datatype3.html

MySQL also does not have a Boolean data type.

Author:  Markus Winter [ Sun Apr 14, 2013 12:34 pm ]
Post subject:  Re: How to save a boolean?

Paul Lefebvre wrote:
[SQLite does not have a Boolean data type. Since Real Studio does have a Boolean data type, it has to be converted to something SQLite can handle. The two choices are to use an INTEGER column and store 0 or 1 or to use a TEXT column and save the text as "true" or "false".

And THAT's what annoys me so immensly about the documentation. Why can't it simply say that? Instead it contains:

Quote:
Boolean Values
When using the DatabaseField.BooleanValue property to retrieve a column value, 0 is treated as False and 1 is treated as True. Other values are undefined when retrieved using BooleanValue.
DatabaseField.StringValue, on the other hand, should be able to retrieve the original data if it can't be identified as a Boolean. If the Real SQL Database can identify the value as a boolean, however, then "False" will always return False and "True" will always return True, regardless of how those values are stored in the database.
This is a side effect of the way in which database engines interact with the database API in the Real Studio framework. Note that this behavior only applies when you store non-Boolean data in a column defined as Boolean.

plus
Quote:
Data Types
The following table contains information about the data types used by SQLite
...
Boolean Stores the values of TRUE or FALSE.
When retrieved by DatabaseField.BooleanValue, "0" and "False" are interpreted as False and "1" and "True" are interpreted as True. All other values are not defined. When retrieved by DatabaseField.StringValue, other non-Boolean values in a Boolean field may retrieved successfully.
...

plus
no example of a boolean value at all anywhere.

Thanks for finally an answer. But really, WHO wrote that documentation???

Page 1 of 2 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/