Hi Everyone,
I have a slight problem that I am not sure what to do about
When i use a SQL call that has a tinyint field it shows up in Gambas a T (for 1) and F (for 0) but I need it to be the number
Is this by design in Gambas or it this MySQL sending this to me
I need the number as it control a option in my software and the software does not under stand the T or F (and I can not really change it as it would effect about 30 other Functions with in the software.
Does anyone know of any SQL command that would force it to be returned as 0 and 1 or do I need to start to capture the field and manually check before adding it to the database?
SQL Advice
- Quincunxian
- Posts: 173
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: SQL Advice
Hi Andy,
Is there any reason why this needs to be a TinyInt?
From what I can understand on the SqLite web site, an Integer and a TinyInt storage space on disk is a byte more.
Unless you are transacting M's of records, then it should not make that much of a difference.
If transaction time is critical/real time, then there would be a compound effect, but again, only if you are doing M's of transactions.
If you are stuck with someone else's database design, then I'd try a CInt({field})
If you do this with a 'real' Gambas boolean True and False you get -1 & 0 respectively.
You would need to test with your read assignment statement to see what happens.
Is there any reason why this needs to be a TinyInt?
From what I can understand on the SqLite web site, an Integer and a TinyInt storage space on disk is a byte more.
Unless you are transacting M's of records, then it should not make that much of a difference.
If transaction time is critical/real time, then there would be a compound effect, but again, only if you are doing M's of transactions.
If you are stuck with someone else's database design, then I'd try a CInt({field})
If you do this with a 'real' Gambas boolean True and False you get -1 & 0 respectively.
You would need to test with your read assignment statement to see what happens.
Cheers - Quin.
I code therefore I am
I code therefore I am
Re: SQL Advice
We often overlook the simple solution. In this case it is "Do it yourself" I have many boolean or tinyint I use in my point of sale. What I did was made a function that takes whatever the value is and converts it to a straight out boolean. So it accepts, "T", "F", "TRUE","FALSE" etc and returns a boolean which is the 1 or 0 you seek. You will spend valuable time looking for a way to do this in sql or some other way only to have it change when somebody upgrades things. Best thing is to handle it yourself so you know it is right every time. example below
Public Function TorF(Stuff As String) As Boolean
If Upper(Left(Stuff,1)) = "T" Then
Return True
Else
Return False
Endif
End
Public Function TorF(Stuff As String) As Boolean
If Upper(Left(Stuff,1)) = "T" Then
Return True
Else
Return False
Endif
End
-
- Posts: 363
- Joined: Wednesday 2nd December 2020 12:11am
- Location: Northampton, England
- Contact:
Re: SQL Advice
Thanks Sadams54 I shall try that and come back to yousadams54 wrote: ↑Monday 16th August 2021 9:32pm We often overlook the simple solution. In this case it is "Do it yourself" I have many boolean or tinyint I use in my point of sale. What I did was made a function that takes whatever the value is and converts it to a straight out boolean. So it accepts, "T", "F", "TRUE","FALSE" etc and returns a boolean which is the 1 or 0 you seek. You will spend valuable time looking for a way to do this in sql or some other way only to have it change when somebody upgrades things. Best thing is to handle it yourself so you know it is right every time. example below
Public Function TorF(Stuff As String) As Boolean
If Upper(Left(Stuff,1)) = "T" Then
Return True
Else
Return False
Endif
End