Unable to getBool from mysql

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

DOSADERN
Posts: 5
Joined: 25 Oct 2011, 01:33

Unable to getBool from mysql

Postby DOSADERN » 25 Oct 2011, 01:55

I have a mysql user database setup. I set the type to bool, which switches to tinyint(1) I can't find a way around that I think its just a mysql thing.

When I try to getBool from the results everything goes wrong.

24 Oct 2011 20:13:54,828 INFO [com.smartfoxserver.v2.controllers.ExtensionController-1] protocol.serialization.DefaultSFSDataSerializer - Skipping Unsupported SQL TYPE: -7, Column:gamestate
24 Oct 2011 20:13:54,843 ERROR [com.smartfoxserver.v2.controllers.ExtensionController-1] v2.controllers.ExtensionController -
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Exception: java.lang.NullPointerException
Message: *** Null ***
Description: Error while handling client request in extension: { Ext: Logont1Extension, Type: JAVA, Lev: ZONE, { Zone: USNLOGIN }, {} }
Extension Cmd: getgameinfo
+--- --- ---+
Stack Trace:
+--- --- ---+
sfs2x.extension.ggwarfare.handler.getgameinfoReqHandler.handleClientRequest(getgameinfoReqHandler.java:67)
com.smartfoxserver.v2.extensions.SFSExtension.handleClientRequest(SFSExtension.java:192)
com.smartfoxserver.v2.controllers.ExtensionController.processRequest(ExtensionController.java:137)
com.smartfoxserver.bitswarm.controllers.AbstractController.run(AbstractController.java:96)
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


Code: Select all

package sfs2x.extension.ggwarfare.handler;

import com.smartfoxserver.v2.core.ISFSEvent;
import com.smartfoxserver.v2.entities.User;
import com.smartfoxserver.v2.entities.data.ISFSObject;
import com.smartfoxserver.v2.entities.data.SFSArray;
import com.smartfoxserver.v2.entities.data.SFSDataWrapper;
import com.smartfoxserver.v2.entities.data.SFSObject;
import com.smartfoxserver.v2.exceptions.SFSException;
import com.smartfoxserver.v2.extensions.BaseClientRequestHandler;
import com.smartfoxserver.v2.extensions.BaseServerEventHandler;
import com.smartfoxserver.v2.extensions.SFSExtension;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.logging.Level;
import java.util.logging.Logger;


public class getgameinfoReqHandler extends BaseClientRequestHandler{
 
    public int ggamenumber;
    public int gamestate;

       
    @Override
    public void handleClientRequest(User sender, ISFSObject params)
    {
        String username = params.getUtfString("user");
       
        try
        {
            Connection conn = getParentExtension().getParentZone().getDBManager().getConnection();

            // This will strip potential SQL injections
            PreparedStatement sql = conn.prepareStatement("SELECT gamenumber FROM users WHERE username = ?");
            sql.setString(1, username);



            //Obtain ResultSet as integers
            ResultSet result = sql.executeQuery();

            //Put the result into an SFSobject array
            SFSArray row = SFSArray.newFromResultSet(result);   

            ggamenumber = row.getSFSObject(0).getInt("gamenumber");

            PreparedStatement sql2 = conn.prepareStatement("SELECT gamestate, players, gametype FROM gamehead WHERE idGame1 = ?");
            sql2.setInt(1, ggamenumber);
       
            //Obtain ResultSet as integers
            ResultSet result2 = sql2.executeQuery();

            //Put the result into an SFSobject array
            SFSArray row2 = SFSArray.newFromResultSet(result2);
               
            //Populate dem variables
            gamestate = row2.getSFSObject(0).getBool("gamestate");

         
            // Create a response object
            ISFSObject resObj = SFSObject.newInstance();
            resObj.putInt("gamestate", gamestate);
     
            // Send it back
            send("getgameinfo", resObj, sender);
       
       
        //make sure you close the database connection when you're done with it, especially if you've
        //set a low number of maximum connections
        //conn.close();
       
       } catch (SQLException ex) {
            Logger.getLogger(getstatsReqHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}



In the log I see the unsupported SQL type -7. I tried changing the getBool to getInt, being mysql is storing the column as a tinyint. but get the same or a similar error. Is there a way to use boolean with smartfox and mysql?

I could use and integer to do nearly the same thing. I am just thinking that sending a bool is a smaller object then sending an Int, correct me if I am wrong there. I'm new at this so could have done everything wrong.
Robbilie
Posts: 190
Joined: 04 Sep 2010, 19:48
Location: Ratingen, Germany
Contact:

Postby Robbilie » 25 Oct 2011, 06:59

switch it to int and getInt was my workaroun...

Strange yeah...
User avatar
Lapo
Site Admin
Posts: 21287
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 27 Oct 2011, 06:36

Please what's the SFS2X version that you use?
Lapo
--
gotoAndPlay()
...addicted to flash games
User avatar
foxboy
Posts: 110
Joined: 12 May 2011, 02:47
Location: Optimal Solution Pte. Ltd.

Re: Unable to getBool from mysql

Postby foxboy » 28 Oct 2011, 19:10

DOSADERN wrote:I have a mysql user database setup. I set the type to bool, which switches to tinyint(1) I can't find a way around that I think its just a mysql thing.

When I try to getBool from the results everything goes wrong.




I've fix this by the doing the following:


// Make bool and int converters
public boolean intToBool(int value)
{
return (value != 0) ? true : false;
}

public int boolToInt(Boolean value)
{
return (value) ? 1 : 0;
}

// FETCH FROM MYSQL
// myBool is of type int(2) on MySQL
sfsObject.putBool("MyBool", intToBool(row.getInt("myBool")));

// TO SAVE BACK TO MYSQL
// just use the boolToInt() method


NOTE: Use int(2) instead of int(1) to avoid the error you experiencing
DOSADERN
Posts: 5
Joined: 25 Oct 2011, 01:33

Postby DOSADERN » 28 Oct 2011, 22:36

Version 2.0.0-RC3 is the version.

I think I will end up using the inverter. The problem is on the MySQL side of things, because of how it stores booleans. The only thing smartfox could do is add a converter into their code, but adding my own will work just as well.
User avatar
Lapo
Site Admin
Posts: 21287
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 29 Oct 2011, 07:16

Thanks for the info.
Adding a specific MySQL converter is not possible, we need to keep the code generic in order to make it work with any DB.
Lapo

--

gotoAndPlay()

...addicted to flash games
hellopaso
Posts: 43
Joined: 10 Dec 2011, 12:41

Re: Unable to getBool from mysql

Postby hellopaso » 24 Jul 2012, 06:50

Lapo, is this bug fixed?

I get

Code: Select all

protocol.serialization.DefaultSFSDataSerializer |     | Skipping Unsupported SQL TYPE: -7, Column:is_active


MYSQL type is:

tinyint(1)
User avatar
Lapo
Site Admin
Posts: 21287
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Unable to getBool from mysql

Postby Lapo » 24 Jul 2012, 07:33

This is not exactly a bug, as I already have explained in other similar post. The problem arises from the fact that JDBC field types don't map exactly to what each DB calls their field types.

In other words JDBC's TINY_INT doesn't match the MySQL version of TINY_INT.
Possible solutions:

1) Change your DB field to INT or another common field type
2) Access the JDBC connection directly via getConnection() and access the DB directly, experimenting with other types

For example Coldfusion has the same problem -> http://nil.checksite.co.uk/index.cfm/20 ... L-TinyInt1
which boils down to different implementations of the DB type. I guess a real standard in this field doesn't exist :(
Lapo

--

gotoAndPlay()

...addicted to flash games
hellopaso
Posts: 43
Joined: 10 Dec 2011, 12:41

Re: Unable to getBool from mysql

Postby hellopaso » 24 Jul 2012, 09:33

ok, i changed to tinyint(2) and now works fine
User avatar
Lapo
Site Admin
Posts: 21287
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Unable to getBool from mysql

Postby Lapo » 24 Jul 2012, 09:48

Good to know :)
Lapo

--

gotoAndPlay()

...addicted to flash games
JimmiQR
Posts: 39
Joined: 03 Jan 2009, 01:08
Location: Boston, Massachusetts, United States of America
Contact:

Re: Unable to getBool from mysql

Postby JimmiQR » 24 Jul 2012, 18:01

I just tested all the Integers, and TINYINT is "skipped", but SMALLINT, MEDIUMINT, INTEGER are valid. The docs say TINYINT, SMALLINT, and INTEGER are supported (no mention of MEDIUMINT).
http://docs2x.smartfoxserver.com/api-do ... nager.html

So is TINYINT out, and MEDIUMINT is in?

I have seen conflicting answers in the FS2X forums about this, and it may just be because some of these threads were written during RC development, so I am just looking for a definitive answer, and possible Document API update to reflect it.
viewtopic.php?t=8890
This thread ends with "Coming in the next release", dated May, 2011

(Not sure if there is data munging on MEDIUMINT or others, because I didn't test larger values)

Thanks SFS Team!
User avatar
Lapo
Site Admin
Posts: 21287
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Unable to getBool from mysql

Postby Lapo » 24 Jul 2012, 18:30

I just tested all the Integers, and TINYINT is "skipped", but SMALLINT, MEDIUMINT, INTEGER are valid. The docs say TINYINT, SMALLINT, and INTEGER are supported (no mention of MEDIUMINT).

The problem here is what you are referring to... :D
The JDBC TINYINT type is supported. But, as I explained, it isn't 100% certain that the JDBC type maps precisely to a specific DB type.
(e.g. not necessarily TINYINT is the same thing under MySQL, MSSQL and Postgres)

So the types we have listed as supported refer to JDBC types.
Hope it's clear.
Lapo

--

gotoAndPlay()

...addicted to flash games
User avatar
Lapo
Site Admin
Posts: 21287
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Unable to getBool from mysql

Postby Lapo » 24 Jul 2012, 18:31

Lapo wrote:
I just tested all the Integers, and TINYINT is "skipped", but SMALLINT, MEDIUMINT, INTEGER are valid. The docs say TINYINT, SMALLINT, and INTEGER are supported (no mention of MEDIUMINT).

The problem here is what you are referring to... :D
The JDBC TINYINT type is supported. But, as I explained, it isn't 100% guaranteed that the JDBC type maps precisely to a specific DB type.
(e.g. not necessarily TINYINT is the same thing under MySQL, MSSQL and Postgres)

So the types we have listed as supported refer to JDBC types.
Hope it's clear.
Lapo

--

gotoAndPlay()

...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: Aldenea, DavinVicy, DerikFent, JerodSa and 14 guests