Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » MySql, boolean values and SqlArray
MySql, boolean values and SqlArray [message #45927] Wed, 27 January 2016 10:00 Go to next message
Giorgio is currently offline  Giorgio
Messages: 207
Registered: August 2015
Experienced Member
Hi there,
MySql rdbms do not have a boolean value type. According to the current documentation BOOL or BOOLEAN "types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true" and that should be used to store boolean values. I noticed that this introduce a little problem. For instance, I have a table containing machinists IDs and a bunch of tinyint fields: if the field is "true" (i.e. nonzero value) the machinist can operate a certain machine. I tested it putting some values:

ID Name M1 M2 M3
'001' 'Titus' '1' '0' '2'
'002' 'Caius' '0' '1' '1'
'003' 'Sempronius' '1' '0' '0'

According to the documentation the following query should return 001 and 002: "Select ID, From MACHINISTS Where M3 = true;", but actually it returns only 002. If I use the following query: "Select ID, From MACHINISTS Where M3 is true;" I get correct results, i.e. 001 and 002. So MySql is picky, but is coherent.

Get back to Ultimate++ now. I have a SqlArray control that manages that table. I need to edit the M1, M2, M3 fields. So I used lines like the following:

EditInt m01abil, m02abil, m03abil;
[...]
tblMachinist.AddColumn(M1, "Abil. M01",10).Edit(m01abil);


And that's fine, but when I query the db:

query * Select(ID, NAME).From(MACHINISTS).Where(M! == true);
while (query.Fetch())
{
	Oper1.Set(i, (String)query[ID], (String)query[NAME]);
	i++;
}


I get just 002. So if the user puts 2 instead of 1 there are problem. To solve that I modified the code managing the SqlArray. Now it reads (note the type of variables):

Option m01abil, m02abil, m03abil;
[...]
tblMachinist.AddColumn(M1, "Abil. M01",10).Edit(m01abil);


I tried to declare in the .sch files the M1 fields both as BOOL and INT, but every time I have problem querying the db: if M3 is not 1 it is considered false.
Using an option type to me solved the issue, as users can not put values different from zero or one.
I do not know if ultimate++ can manage this kind of issue, but as I spent some time to figure this out I decided to share my solution.
Regards,
Giorgio
Re: MySql, boolean values and SqlArray [message #45930 is a reply to message #45927] Thu, 28 January 2016 06:24 Go to previous messageGo to next message
deep is currently offline  deep
Messages: 227
Registered: July 2011
Location: Bangalore
Experienced Member
Giorgio wrote on Wed, 27 January 2016 14:30
Hi there,
According to the documentation the following query should return 001 and 002: "Select ID, From MACHINISTS Where M3 = true;", but actually it returns only 002. If I use the following query: "Select ID, From MACHINISTS Where M3 is true;" I get correct results, i.e. 001 and 002. So MySql is picky, but is coherent.


Have you tried this which you have stated. Which is working.
query * Select(ID, NAME).From(MACHINISTS).Where(M1 is true);


It may be good idea to check first with "select *" and confirm your table is updated.


Warm Regards

Deepak
Re: MySql, boolean values and SqlArray [message #45931 is a reply to message #45930] Thu, 28 January 2016 08:58 Go to previous messageGo to next message
Giorgio is currently offline  Giorgio
Messages: 207
Registered: August 2015
Experienced Member
deep wrote on Thu, 28 January 2016 06:24

Have you tried this which you have stated. Which is working.
query * Select(ID, NAME).From(MACHINISTS).Where(M1 is true);



Uhm, it does not compile, I get an error.

error C2146: syntax error: ')' missing before the identifier 'is'


Regards,
Gio

[Updated on: Thu, 28 January 2016 09:14]

Report message to a moderator

Re: MySql, boolean values and SqlArray [message #45938 is a reply to message #45931] Thu, 28 January 2016 18:21 Go to previous messageGo to next message
deep is currently offline  deep
Messages: 227
Registered: July 2011
Location: Bangalore
Experienced Member
Try some thing like this

query.Execute( select ID, NAME from MACHINISTS where M1 is true);


Warm Regards

Deepak
Re: MySql, boolean values and SqlArray [message #45961 is a reply to message #45927] Sun, 31 January 2016 20:29 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13051
Registered: November 2005
Ultimate Member
bool types in SQL are bit of mess. That is why I am usually using single char value there.

However, this should work fine. I believe that the issue here is that

query * Select(ID, NAME).From(MACHINISTS).Where(M1 == true);


gets converted to

query * Select(ID, NAME).From(MACHINISTS).Where(M1 == 1);


Fix is easy, use

query * Select(ID, NAME).From(MACHINISTS).Where(M1 > 0);


(Note that when developing SQL, it is always a good idea to have activated SQL log at least in debug mode - SqlSession::SetTrace())
Re: MySql, boolean values and SqlArray [message #45967 is a reply to message #45961] Mon, 01 February 2016 08:59 Go to previous messageGo to next message
Giorgio is currently offline  Giorgio
Messages: 207
Registered: August 2015
Experienced Member
Hi Mirek,
thank you for your answer; my humble proposal is that Ultimate++ converts automatically "Where(M1 == True )" to "Where(M1 > 0)" in case the variable is defined as boolean in the .sch file.
Regards,
Gio
Re: MySql, boolean values and SqlArray [message #45971 is a reply to message #45967] Mon, 01 February 2016 18:31 Go to previous message
mirek is currently offline  mirek
Messages: 13051
Registered: November 2005
Ultimate Member
I understand this is frustrating, unfortunately there does not seem to be a good way around SQL bools at the moment. What you suggest would likely bomb other expression forms.
Previous Topic: Problem with SqlUpdate
Next Topic: How to close a sqlite session
Goto Forum:
  


Current Time: Sat Jan 23 17:23:35 CET 2021

Total time taken to generate the page: 0.01263 seconds