Database Problem

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

Moderators: Lapo, Bax

genar
Posts: 137
Joined: 13 Jul 2017, 11:49

Database Problem

Postby genar » 09 Jan 2018, 21:24

Im honestly not sure if thats even a smartfoxserver iusse, but when i try to use a selfwritten extension to get one row from an table in my database it always returns null... But thats not possible, because when i execute the same in my SQL-Workbench i get exactly one row.

Code: Select all

if(chunkExists){
         
         String mySqlQuery = "SELECT chunkBuildings FROM chunks where chunkName = '"+chunkName+"'";
         
         Connection connection;

         connection = dbManager.getConnection();

                   PreparedStatement stmt = connection.prepareStatement(mySqlQuery);

              ResultSet result = stmt.executeQuery();
         
         
         if(result == null){
            
            // Sends MySQL exception details to the client
            sendISFSObject("ChunkDataCollector_Error","error", "Data receiving failed", user);
            
            
             trace("<ChunkDataCollector : MySQL Error +"+lastException.getMessage()+">");   
            
         }
         else{

            String buildingsCollection = "";
            
            if(result.next()){
               
               buildingsCollection = result.getString("chunkBuildings");
               
                trace("<ChunkDataCollector : "+buildingsCollection+">");
                trace("<ChunkDataCollector : "+chunkName+">");

                
            }   
            

            // Sends MySQL exception details to the client
            sendISFSObject("ChunkDataCollector_Sucess", "sucess", buildingsCollection, user);
            
            
             trace("<ChunkDataCollector : Data sucessfull received>");
          
         }
         
         
         connection.close();
         
      }


As you may see i try to send the string "buildingsCollection" to the client. I also trace it but the value is always empty. I also have two other extensions which are working with my database ( login & registration ) i havent got those iusses there. Maybe those extensions have a problem with each other ?
User avatar
Lapo
Site Admin
Posts: 23009
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Database Problem

Postby Lapo » 10 Jan 2018, 08:59

Hi,
I think the problem is more of how you use the JDBC resultset object. You need to either move the resultset's cursor to the first record or use a while loop as in this example, if you expect more than one row:
https://docs.oracle.com/javase/tutorial ... eving.html

Also, why not using the SmartFox DBManager class? It hides away the complexity of JDBC and, unless you need to do some lower level stuff, it should be sufficient for most queries.
http://smartfoxserver.com/blog/querying ... esultsets/

Cheers
Lapo
--
gotoAndPlay()
...addicted to flash games
genar
Posts: 137
Joined: 13 Jul 2017, 11:49

Re: Database Problem

Postby genar » 10 Jan 2018, 11:20

Oh i didnt even knew that smartfox Supports an easy way to acess the MySql database...

I think i Need to rewrite my Extension. I understand the example, but how do i just get one value out of the column to send it back to the Client

Code: Select all

public class GetPeopleHandler extends BaseClientRequestHandler
{
    @Override
    public void handleClientRequest(User sender, ISFSObject params)
    {
        IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
        String sql = "SELECT * FROM people";
         
        try
        {
            // Obtain a resultset
            ISFSArray res = dbManager.executeQuery(sql, new Object[] {});
             
            // Populate the response parameters
            ISFSObject response = new SFSObject();
            response.putSFSArray("people", res);
             
            // Send back to requester
            send("getPeople", response, sender);
        }
        catch (SQLException e)
        {
            trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
        }
    }
}


For example this part extracts the result and puts it into an Array.

Code: Select all

 // Obtain a resultset
            ISFSArray res = dbManager.executeQuery(sql, new Object[] {});
             
            // Populate the response parameters
            ISFSObject response = new SFSObject();
            response.putSFSArray("people", res);


Can i also get just one result out of the dbManager ?
genar
Posts: 137
Joined: 13 Jul 2017, 11:49

Re: Database Problem

Postby genar » 10 Jan 2018, 19:34

-- UPDATE --

So i just played a bit around with the api and got this now :

Code: Select all

String mySqlQuery = "SELECT * FROM chunks where chunkName = '"+chunkName+"'";
         
         trace("<ChunkDataCollector : ChunkName "+chunkName+">");
         
         
          // Obtain a resultset
            ISFSArray result = dbManager.executeQuery(mySqlQuery, new Object[] {});

            ISFSObject entry = result.getSFSObject(0);
           
           
            for(int index = 0; index < result.size(); index++){
               
               ISFSObject dbentry = result.getSFSObject(index);
               
             trace("<ChunkDataCollector : Data sucessfull received : "+dbentry.getUtfString("chunkBuildings")+" "+index+">");
               
            }


ISFSObject entry is still empty. And when i loop over the result set its empty too. No null pointer exception, no error. Its just empty. And i have no idea why. In the MYSQL-Workbench it return me one row with one column and exactly one value... The value inside it is from datatype utf8 varchar(200)...
User avatar
Lapo
Site Admin
Posts: 23009
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Database Problem

Postby Lapo » 11 Jan 2018, 08:04

Can you dump the result object after the query and check what's inside?

Code: Select all

trace(result.getDump())

Also can you show me the schema of your "chunks" DB table?

I would also suggest to use this code instead, to avoid SQL injection problems:

Code: Select all

String mySqlQuery = "SELECT * FROM chunks where chunkName = ?'";         

// Obtain a resultset
ISFSArray result = dbManager.executeQuery(mySqlQuery, new Object[] { chunkName });


cheers
Lapo

--

gotoAndPlay()

...addicted to flash games
genar
Posts: 137
Joined: 13 Jul 2017, 11:49

Re: Database Problem

Postby genar » 11 Jan 2018, 18:46

That solved it thanks ! :)

Return to “SFS2X Questions”

Who is online

Users browsing this forum: No registered users and 72 guests