MySql tinyint(1) return 1 for any value

[Paolo Broccardo]

Hi

When making a column in MySql tinyint(1) it returns 1 to my Railo
application for any value in the database (for example: 2,3,4, etc)

When I run the stored proc in MySql, the value is definitely 2.
But once it reaches the Railo application, it is 1.

I have seen other issues logged here:
http://groups.google.com/group/railo/browse_thread/thread/1fe5cfbaf52771cd/0e81721af6722a09

Also I have seen a related JBoss ticket:
https://jira.jboss.org/browse/RAILO-656?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel

They say that this is fixed. I see related version to the fix is
3.1.2.008
I am running 3.1.2.001 final

I don't see any way to upgrade via the server admin to 3.1.2.008 so
I'm guessing I have to wait for a later release.

Is this correct?

Thanks

[Todd Rafferty]

No, you can upgrade to bleeding edge today, but keep in mind, this is bleeding edge stuff and you should test out the server on development first and make sure that nothing else has broken.

You have to log into your "global server" context:

http://{hostname}/railo-context/admin/server.cfm?action=services.update

Make sure the URL says: http://dev.getrailo.org/

Then, execute the patch and that will bring you up to 3.1.2.018

--
~Todd Rafferty ** Volunteer Railo Open Source Community Manager ** http://getrailo.org/

[Paolo Broccardo]

Great - thanks Todd.

On Jul 24, 1:26 pm, Todd Rafferty <t...@getrailo.org> wrote:
> No, you can upgrade to bleeding edge today, but keep in mind, this is
> bleeding edge stuff and you should test out the server on development first
> and make sure that nothing else has broken.
>
> You have to log into your "global server" context:
> http://{hostname}/railo-context/admin/server.cfm?action=services.update
>
> Make sure the URL says:http://dev.getrailo.org/
>
> Then, execute the patch and that will bring you up to 3.1.2.018
>
> On Sat, Jul 24, 2010 at 4:49 AM, Paolo Broccardo <ad...@cheekyonline.com>wrote:
>
>
>
> > Hi
>
> > When making a column in MySql tinyint(1) it returns 1 to my Railo
> > application for any value in the database (for example: 2,3,4, etc)
>
> > When I run the stored proc in MySql, the value is definitely 2.
> > But once it reaches the Railo application, it is 1.
>
> > I have seen other issues logged here:
>

> >http://groups.google.com/group/railo/browse_thread/thread/1fe5cfbaf52...

>
> > Also I have seen a related JBoss ticket:
>

> >https://jira.jboss.org/browse/RAILO-656?page=com.atlassian.jira.plugi...

[Paolo Broccardo]

Hi guys

So I have upgraded to developer release: 3.1.2.019

This still seems to be an issue though. I have had a look at the JIRA
tickets (RAILO-617, RAILO-656) and can't seem to see what the
resolution was. If anything, it looks like the issue could not be
duplicated?

I am still having the problem after upgrading.

In my database (MySql 5.1.34) I set the value of the tinyint(1) column
to 3
When I select in MySql it returns 3 for that column.

In Coldfusion, the value is returned as 1.

The MySql documentation defines tinyint as:
A very small integer. The signed range is -128 to 127. The unsigned
range is 0 to 255.

I see something was mentioned about the JBDC driver that converts the
result to a boolean.
Can someone confirm this is the case, and if so, is the solution to
make and non boolean fields tinyint(2) going forward?

Perhaps Micha can clarify?

Thanks

On Jul 24, 5:41 pm, Paolo Broccardo <ad...@cheekyonline.com> wrote:
> Great - thanks Todd.
>
> On Jul 24, 1:26 pm, Todd Rafferty <t...@getrailo.org> wrote:
>
> > No, you can upgrade to bleeding edge today, but keep in mind, this is
> > bleeding edge stuff and you should test out the server on development first
> > and make sure that nothing else has broken.
>
> > You have to log into your "global server" context:
> > http://{hostname}/railo-context/admin/server.cfm?action=services.update
>
> > Make sure the URL says:http://dev.getrailo.org/
>
> > Then, execute the patch and that will bring you up to 3.1.2.018
>
> > On Sat, Jul 24, 2010 at 4:49 AM, Paolo Broccardo <ad...@cheekyonline.com>wrote:
>
> > > Hi
>

> > > When making a column in MySqltinyint(1) it returns 1 to my Railo

[Todd Rafferty]

Wait, your statement makes no sense. What did you want the column to return? A boolean?

[Paolo Broccardo]

No - the value. 3. Now it returns 0 for 0 (correct), but for any value
above 1 it returns 1. So 2,3,4,5,6,7 all return 1.

[Kym Kovan]

On 23/08/2010 04:36, Paolo Broccardo wrote:
>
> I see something was mentioned about the JBDC driver that converts the
> result to a boolean.
> Can someone confirm this is the case, and if so, is the solution to
> make and non boolean fields tinyint(2) going forward?

I recollect bumping into this once and it was because mySQL did not have
a boolean field so the TinyInt one was used as a substitute.

It was a while back so I don't know if that applies to the current versions.

--

Yours,

Kym Kovan
mbcomms.net.au

[Michael Offner]

Tinyint(1) is a boolean, the (1) Stands for the Size of the tinyint.
In this Case 1 bit (0 or 1).
See "Boolean" in this page
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

/micha

P.s. Railo never rewrite data from jdbc drivers, Railo only convert if
needed, for example clob to string, but numbers are always untouched
by Railo.

2010/8/23, Kym Kovan <dev-...@mbcomms.net.au>:

[Paolo Broccardo]

Hi Michael

I am trying not to argue here, merely understand.

In MySQL, given:
profileTinyIntColumn tinyint(1) default 3

and you type:
select profileTinyIntColumn * 2 as theValue

You get 6.

In the application, profileTinyIntColumn returns 1. Therefore the
calculations are inconsistent between application and database.

What (I THINK) the documentation is saying is that you CAN use
tinyint(1) as a boolean, because 0 can be used as false and anything
GTE 1 can be sued as true.

For example, if your tinyint(1) column was called dishesWashed and
stored the number of dishes washed (let's say its 6 in this case), you
could use it like a bool:
<cfif query.dishesWashed>...</cfif> (would evaluate to true like a
boolean)

BUT you should still also be allowed to view the value, such as:
Dishes washed: #dishesWashed# (would display 6)

Does that not make sense or am I missing the boat here?
The MySql documentation says: These types (BOOL, BOOLEAN) are synonyms
for TINYINT(1)
It doesn't say: TINYINT(1) is a synonym for BOOL, BOOLEAN

To me that states that tinyint(1) CAN Be used as a boolean, but not IS
a boolean.

Hope I have explained my issue a little more clearly :)

Thanks Micha

On Aug 23, 9:02 am, Michael Offner <mich...@getrailo.com> wrote:
> Tinyint(1) is a boolean, the (1) Stands for the Size of the tinyint.
> In this Case 1 bit (0 or 1).

> See "Boolean" in this pagehttp://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

>
> /micha
>
> P.s. Railo never rewrite data from jdbc drivers, Railo only convert if
> needed, for example  clob to string, but numbers are always untouched
> by Railo.
>

> 2010/8/23, Kym Kovan <dev-li...@mbcomms.net.au>:

[Peter Boughton]

> Tinyint(1) is a boolean, the (1) Stands for the Size of the tinyint.
> In this Case 1 bit (0 or 1).

That's not entirely correct - the 1 is a byte, not a bit, so you can
have 0..255 or -128..127 in a TINYINT(1) column.

( See here: http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html )

As Paolo says, TINYINT is often *used* for storing booleans (a BOOL is
an alias for it when creating a table), but it is still an 8-bit
integer field, not a 1-bit boolean.

( Also see: http://dev.mysql.com/doc/refman/5.5/en/other-vendor-data-types.html
)

So, if a tinyint column only returns 1 or 0 then there's a bug
somewhere, but it sounds likely the bug is in the JDBC driver?

[Michael Offner]

ok then i was wrong here, sorry i have not spend to much time for this.

still the ticket 617 has the status "resolved - cannot reproduce bug"  with a coding example that works as expected (since march!),

feel free to modifie the testcode in the ticket to show the problem, then we will fix it.

i'm a 100% sure there is going something wrong in your environment, but as long i cannot reproduce it, i can not help.

ticket:https://jira.jboss.org/browse/RAILO-617

/micha

2010/8/23 Peter Boughton <boug...@gmail.com>

[Paolo Broccardo]

Hi Michael

Example updated as requested!

Thanks
Paolo

On Aug 23, 8:56 pm, Michael Offner <mich...@getrailo.com> wrote:
> ok then i was wrong here, sorry i have not spend to much time for this.
> still the ticket 617 has the status "resolved - cannot reproduce bug"  with
> a coding example that works as expected (since march!),
> feel free to modifie the testcode in the ticket to show the problem, then we
> will fix it.
> i'm a 100% sure there is going something wrong in your environment, but as
> long i cannot reproduce it, i can not help.
>
> ticket:https://jira.jboss.org/browse/RAILO-617
>
> /micha
>

> 2010/8/23 Peter Boughton <bought...@gmail.com>