SQL Advice

Post your Gambas programming questions here.
Post Reply
AndyGable
Posts: 142
Joined: Wednesday 02nd December 2020 12:11am
Location: England
Contact:

SQL Advice

Post by AndyGable » Tuesday 27th July 2021 7:13pm

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?

User avatar
Quincunxian
Posts: 136
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: SQL Advice

Post by Quincunxian » Tuesday 27th July 2021 9:46pm

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.
Cheers - Quin.
I code therefore I am

sadams54
Posts: 83
Joined: Monday 09th July 2018 3:43am

Re: SQL Advice

Post by sadams54 » 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

AndyGable
Posts: 142
Joined: Wednesday 02nd December 2020 12:11am
Location: England
Contact:

Re: SQL Advice

Post by AndyGable » Monday 16th August 2021 9:40pm

sadams54 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
Thanks Sadams54 I shall try that and come back to you

Post Reply