Unable to getBool from mysql

[DOSADERN]

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]

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

Strange yeah...

[Lapo]

Please what's the SFS2X version that you use?

[foxboy]

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]

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.

[Lapo]

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.

[hellopaso]

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)

[Lapo]

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

[hellopaso]

ok, i changed to tinyint(2) and now works fine

[Lapo]

Good to know

[JimmiQR]

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!

[Lapo]

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...
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]

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...
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.