Page 1 of 1

SQL Advice

Posted: Tuesday 27th July 2021 7:13pm
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?

Re: SQL Advice

Posted: Tuesday 27th July 2021 9:46pm
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.

Re: SQL Advice

Posted: Monday 16th August 2021 9:32pm
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

Re: SQL Advice

Posted: Monday 16th August 2021 9:40pm
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