{"id":1888,"date":"2021-07-29T15:00:05","date_gmt":"2021-07-29T15:00:05","guid":{"rendered":"https:\/\/smartfoxserver.com\/blog\/?p=1888"},"modified":"2021-09-10T08:18:31","modified_gmt":"2021-09-10T08:18:31","slug":"using-a-database-in-overcast-part-3","status":"publish","type":"post","link":"https:\/\/smartfoxserver.com\/blog\/using-a-database-in-overcast-part-3\/","title":{"rendered":"Using a database in Overcast (part 3)"},"content":{"rendered":"\n

In the third (and last) part of this series we are taking a look at using the JDBC API<\/strong> to access a database via SFS2X Extensions. While this approach takes a bit more coding compared to the DBManager API<\/strong> (which we have explored in part 2<\/a>) it also provides more sophisticated features such as working with metadata and advanced data types.<\/p>\n\n\n\n\n\n\n\n

If you are totally unfamiliar with the JDBC API, we highly recommend to take an introductory tour<\/a> before proceeding.<\/p>\n\n\n\n

Finally, in the last part of the article, we’ll briefly touch on local and remote development using Overcast and how to import\/export databases from your dev machine to your remote server(s).<\/p>\n\n\n\n

\u00bb Accessing the database via JDBC directly<\/h3>\n\n\n\n

When we use JDBC in an SFS2X Extension we don’t need to setup the driver and connection details via code, which is typically the first step to get started. These steps are already done through the DBManager<\/strong> configuration in SFS2X, as we have seen in part one<\/a> of this article series.<\/p>\n\n\n\n

The DBManager<\/strong> class provides us with a pool of reusable connections that we can use to communicate with our database, regardless of the API in use.<\/p>\n\n\n\n

Here’s the first SQL query example using JDBC:<\/p>\n\n\n

\nprivate void queryJDBC()\n{\n\tdbMan = getParentZone().getDBManager();\n\tConnection conn = null;\n\t\n\ttry\n\t{\n\t\tconn = dbMan.getConnection();\n\t\tPreparedStatement stmt = conn.prepareStatement("SELECT username FROM highscores");\n\t\tResultSet res = stmt.executeQuery();\n\t\t\n\t\twhile (res.next())\n\t\t{\n\t\t\tSystem.out.println(res.getString("username"));\n\t\t}\n\t}\n\t\n\tcatch(SQLException ex)\n\t{\n\t\ttry\n\t\t{\n\t\t\ttrace("Unexpected error while querying the DB: " + ex.getMessage());\n\t\t\tif (conn != null)\n\t\t\t\tconn.close();\n\t\t}\n\t\t\n\t\tcatch(SQLException sqlex)\n\t\t{\n\t\t\ttrace("Unexpected error while closing connection: " + sqlex.getMessage());\n\t\t}\n\t}\n}\n<\/pre>\n\n\n\n
The main difference here is that we’re managing the life-cycle of the connection ourselves, with special care to closing it when we’ve completed our work. Failing to do so (by mistake or due to an Exception) will cause a connection leak which in turn will impact both memory and performance (i.e. memory leaks and connection starvation).<\/p>\n\n\n\n
The reason for this is that database connections are pooled (i.e. borrowed and returned from a pool of existing connections) to improve the performance and they must be returned at the end of each activity. The “returning” part is simply done by invoking the close() <\/strong>method on the connection itself.<\/p>\n\n\n\n
The recommended approach is to use a try\/catch\/finally<\/strong> block to deal with any potential errors and close the connection in the finally<\/strong> section, which is guaranteed to run at the exit of our method call.<\/p>\n\n\n\n
We’re not going into the details of how the PreparedStament<\/strong> and ResultSet<\/strong> classes work as it is outside of the scope of this article. If you need to refresh your memory please make sure to check the links at the end of the article.<\/p>\n\n\n\n
We will however take a look at another example using external parameters and how to properly sanitize them to avoid SQL injection issues.<\/p>\n\n\n
\nprivate void findScore(String username)\n{\n\tdbMan = getParentZone().getDBManager();\n\tConnection conn = null;\n\t\n\ttry\n\t{\n\t\tconn = dbMan.getConnection();\n\t\tPreparedStatement stmt = conn.prepareStatement("SELECT * FROM highscores WHERE username=?");\n\t\tstmt.setString(1, username);\n\n\t\tResultSet res = stmt.executeQuery();\n\t\t\n\t\twhile (res.next())\n\t\t{\n\t\t\tint score = res.getInt("score");\n\t\t\tSystem.out.println("Score: " + score);\n\t\t}\n\t}\n\t\n\tcatch(SQLException ex)\n\t{\n\t\ttry\n\t\t{\n\t\t\ttrace("Unexpected error while querying the DB: " + ex.getMessage());\n\t\t\tif (conn != null)\n\t\t\t\tconn.close();\n\t\t}\n\t\t\n\t\tcatch(SQLException sqlex)\n\t\t{\n\t\t\ttrace("Unexpected error while closing connection: " + sqlex.getMessage());\n\t\t}\n\t}\n}\n<\/pre>\n\n\n\n
As we have seen in part two<\/a> we can use “?” placeholders in the SQL expression and populate them them separately. In the case of the PreparedStatement<\/strong> class we use the setString()<\/strong> method which uses indexes to specify which placeholder we want to replace (NOTE<\/strong>: parameter indexes start at 1 and not zero, as it happens for arrays and lists).<\/p>\n\n\n\n
It’s worth to note that the class supports a large number of data types and you’ll find methods such as setInt<\/strong>, setDouble<\/strong>, setBoolean<\/strong>, setBlob<\/strong>, etc.<\/p>\n\n\n\n
\u00bb Using metadata<\/h3>\n\n\n\n
Metadata in a database doesn’t represent the content of the records but rather the records themselves, with all their properties, such as column names, types, length etc.<\/p>\n\n\n\n
This is akin to the reflection API in Java (and similar programming languages) which allows for self-inspection of the data types.<\/p>\n\n\n
\nprivate void testMetadata()\n{\n\tdbMan = getParentZone().getDBManager();\n\tConnection conn = null;\n\t\n\ttry\n\t{\n\t\tconn = dbMan.getConnection();\n\t\tDatabaseMetaData databaseMetaData = conn.getMetaData();\n\t\t\n\t\tResultSet resultSet = databaseMetaData.getColumns(null,null, "highscores", null); \n\t\t\n\t\twhile(resultSet.next()) \n\t\t{ \n\t\t\tString name = resultSet.getString("COLUMN_NAME");\n\t\t\tString type = resultSet.getString("DATA_TYPE");\n\t\t    String size = resultSet.getString("COLUMN_SIZE");\n\t\t    String isNullable = resultSet.getString("IS_NULLABLE");\n\t\t    \n\t\t    trace("Name: %s, Type:%s, Size: %s, isNullable: %s\\n", name, type, size, isNullable);\n\t\t}\n\t}\n\t\n\tcatch(SQLException ex)\n\t{\n\t\ttry\n\t\t{\n\t\t\ttrace("Unexpected error while querying the DB: " + ex.getMessage());\n\t\t\tif (conn != null)\n\t\t\t\tconn.close();\n\t\t}\n\t\t\n\t\tcatch(SQLException sqlex)\n\t\t{\n\t\t\ttrace("Unexpected error while closing connection: " + sqlex.getMessage());\n\t\t}\n\t}\n}\n<\/pre>\n\n\n\n
The snippet above is an example of how to inspect a database table and extract column name, type, length and nullable properties. This can be useful when we need to dynamically work with tables without knowing all the details of their structure in advance.<\/p>\n\n\n\n
\u00bb Testing locally and remotely<\/h3>\n\n\n\n
When working on an Overcast-based application with a database it can help to be able to test locally in order to build the DB tables and Extensions needed.<\/p>\n\n\n\n
In the Overcast cloud we use MySQL so the best choice is to mirror the same setup (SFS2X + MySQL) in your local environment, possibly using the same database version that was deployed in the cloud, or at least the same major version (you can check the Overcast DB version via the HQ server details page).<\/p>\n\n\n\n
How to create a local setup<\/h4>\n\n\n\n
A quick way to setup a local MySQL<\/strong> and PHPMyAdmin<\/strong> (the web-based admin tool) is to use a pre-packaged LAMP stacks such as  XAMPP<\/a> or MAMP<\/a>. <\/p>\n\n\n\n
These tools provide all the necessary components to get started and they can be installed locally alongside SmartFoxServer 2X.<\/p>\n\n\n\n
NOTE:<\/strong> in the case of XAMPP the database installed is MariaDB<\/a><\/strong>, rather than MySQL<\/strong>, which can be considered a drop-in replacement for MySQL. Generally speaking you should have no problems developing with it and then moving back to MySQL in the cloud. If you want to learn more about this aspect please consult their documentation here<\/a>.<\/p>\n\n\n\n
How to import\/export a database<\/h4>\n\n\n\n
If you’re creating and developing the database tables locally you will need a way to export them to a file format and re-import them in the remote Overcast DB.<\/p>\n\n\n\n
This is easily achieved via the import\/export function<\/strong> in PHPMyAdmin.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this case we have selected a single table, but we could have also done it with the whole database. Next we select the Export<\/strong> tab, leave the default settings (Quick Export, in SQL format) and proceed. <\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This will generate a local .sql file<\/strong> containing both the structure and data of the table we’ve just selected. <\/p>\n\n\n\n
Now we can move to the Overcast side, select our database server in the HQ and click the admin tool icon:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This will bring us to the remote<\/strong> PHPMyAdmin<\/strong> where we can start by creating a database with the same name as the one used locally, in this case: overcast_tutorial<\/strong><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Once this is done, we move to the Import tab<\/strong>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Here we simply specify the previously created .sql file from our local filesystem and upload it. The table(s) will be recreated remotely and we should be good to go.<\/p>\n\n\n\n
A quick check shows that we have indeed recreated our table and the relative data in the Overcast MySQL DB.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\u00bb More JDBC resources<\/h3>\n\n\n\n
If you want to read more about the JDBC API<\/strong> we’d like to recommend several articles and tutorials that will help you learn the ins and outs of the library.<\/p>\n\n\n\n