SQL Advice

Post your Gambas programming questions here.
Post Reply
AndyGable
Posts: 349
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

SQL Advice

Post by AndyGable »

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: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: SQL Advice

Post by Quincunxian »

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
User avatar
sadams54
Posts: 139
Joined: Monday 9th July 2018 3:43am
Contact:

Re: SQL Advice

Post by sadams54 »

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: 349
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: SQL Advice

Post by AndyGable »

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