Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed May 24, 2017 12:51 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: How to save a boolean?
PostPosted: Mon Apr 08, 2013 4:29 am 
Offline

Joined: Sun Feb 19, 2006 4:00 pm
Posts: 1282
Location: Heidelberg, Germany
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


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Mon Apr 08, 2013 4:56 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
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.


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Mon Apr 08, 2013 5:40 am 
Offline

Joined: Sun Feb 19, 2006 4:00 pm
Posts: 1282
Location: Heidelberg, Germany
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


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Mon Apr 08, 2013 5:53 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Unfortunately, every db engine treats booleans differently. SQL defines a standard, but everyone uses their own standard.


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Mon Apr 08, 2013 6:10 am 
Offline

Joined: Sun Feb 19, 2006 4:00 pm
Posts: 1282
Location: Heidelberg, Germany
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.


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Mon Apr 08, 2013 8:36 am 
Offline
User avatar

Joined: Fri Mar 03, 2006 5:57 pm
Posts: 61
Location: Elgin, Scotland
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.

_________________
Mark
[Real Studio 2011r4 Pro on OSX 10.8.2 targeting OSX]
MarkSpotCode - My trials and tribulations in the world of computer programming.


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Mon Apr 08, 2013 12:35 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
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.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Mon Apr 08, 2013 1:59 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
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.


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Thu Apr 11, 2013 6:50 pm 
Offline
User avatar

Joined: Sun Aug 12, 2007 10:10 am
Posts: 1086
Location: Boiling Springs, SC
Side Humor: There are 10 types of people in the world...those who understand binary, and those that don't :-)

010011010110111101110011011101000010000001110111011011110110111000100111011101000010000001110101011011100110010001100101011100100111001101110100011000010110111001100100001000000110100101110100001011100010111000101110

Do you? :-D

_________________
Matthew A. Combatti
Real Studio 2012 r1.2

Visit Xojo Developer's Spot!
Systems I Use:
Windows XP/Windows Vista/Windows Server 2008 r2/Windows 7/Windows 8
Mac OSX 10.5/Mac OSX 10.6/Mac OSX Server/Ubuntu/Debian/Suse/Red Hat/
Windows Server 2011/CentOS 5.4 /ReactOS/SimOS

~All Xojo Compatible~


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Thu Apr 11, 2013 7:37 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
True.

01000010011101010111010000100000011100110110111101101101011001010010000001110111011010010110110001101100


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Thu Apr 11, 2013 8:21 pm 
Offline
User avatar

Joined: Sun Aug 12, 2007 10:10 am
Posts: 1086
Location: Boiling Springs, SC
timhare wrote:
True.

01000010011101010111010000100000011100110110111101101101011001010010000001110111011010010110110001101100



00111010 0010110100101001

_________________
Matthew A. Combatti
Real Studio 2012 r1.2

Visit Xojo Developer's Spot!
Systems I Use:
Windows XP/Windows Vista/Windows Server 2008 r2/Windows 7/Windows 8
Mac OSX 10.5/Mac OSX 10.6/Mac OSX Server/Ubuntu/Debian/Suse/Red Hat/
Windows Server 2011/CentOS 5.4 /ReactOS/SimOS

~All Xojo Compatible~


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Fri Apr 12, 2013 2:20 am 
Offline

Joined: Sun Feb 19, 2006 4:00 pm
Posts: 1282
Location: Heidelberg, Germany
To summarize: nobody knows how to properly save a boolean value into a database with REALbasic.

Sad.


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Fri Apr 12, 2013 10:18 am 
Offline
User avatar

Joined: Sun Aug 12, 2007 10:10 am
Posts: 1086
Location: Boiling Springs, SC
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 :-))

_________________
Matthew A. Combatti
Real Studio 2012 r1.2

Visit Xojo Developer's Spot!
Systems I Use:
Windows XP/Windows Vista/Windows Server 2008 r2/Windows 7/Windows 8
Mac OSX 10.5/Mac OSX 10.6/Mac OSX Server/Ubuntu/Debian/Suse/Red Hat/
Windows Server 2011/CentOS 5.4 /ReactOS/SimOS

~All Xojo Compatible~


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Sun Apr 14, 2013 11:45 am 
Offline
Site Admin
User avatar

Joined: Fri Sep 30, 2005 9:35 am
Posts: 987
Location: South Portland, Maine
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.

_________________
Paul Lefebvre
Developer Evangelist
Xojo, Inc.


Top
 Profile  
 
 Post subject: Re: How to save a boolean?
PostPosted: Sun Apr 14, 2013 12:34 pm 
Offline

Joined: Sun Feb 19, 2006 4:00 pm
Posts: 1282
Location: Heidelberg, Germany
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???


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: Bing [Bot] and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group