{"id":1814,"date":"2021-07-21T14:34:42","date_gmt":"2021-07-21T14:34:42","guid":{"rendered":"https:\/\/smartfoxserver.com\/blog\/?p=1814"},"modified":"2021-09-10T08:19:43","modified_gmt":"2021-09-10T08:19:43","slug":"using-a-database-in-overcast-part-2","status":"publish","type":"post","link":"https:\/\/smartfoxserver.com\/blog\/using-a-database-in-overcast-part-2\/","title":{"rendered":"Using a database in Overcast (part 2)"},"content":{"rendered":"\n

In the previous installment<\/a> of the this tutorial we have learned all the steps to create a database server in Overcast and connect to it from an existing SFS2X instance.<\/p>\n\n\n\n

In this new chapter we are going to explore the database API that can be used to query the database from server side, using SFS2X Extensions.<\/p>\n\n\n\n

If you are new to server side coding we highly recommend to get started with this article from our documentation<\/a>, before proceeding with the rest of the tutorial. <\/p>\n\n\n\n\n\n\n\n

\u00bb API Choices<\/h3>\n\n\n\n

Essentially all database access in SFS2X is done via the standard Java JDBC API<\/a>, which provides an abstraction for talking to all kinds of relational databases. <\/p>\n\n\n\n

In addition to JDBC<\/strong> we provide a simplified layer on top of it, called the DBManager<\/strong> API<\/strong>, <\/strong>that offers a more streamlined approach to querying and manipulating records.<\/p>\n\n\n\n

If you’re new to JDBC we highly recommend to start with the latter and eventually move to JDBC, if necessary. <\/p>\n\n\n\n

\u00bb Database Manager API Examples<\/h3>\n\n\n\n

Before looking at the code we assume, as a prerequisite, that you’ve already setup your database and configured SFS2X. If not, make sure to go back to part one<\/a> of this article and follow the steps there.<\/p>\n\n\n\n

We’ll start off with a basic query example:<\/p>\n\n\n

\npublic class OvercastDBExtension extends SFSExtension\n{\n\tprivate IDBManager dbMan;\n\t\n\t@Override\n\tpublic void init()\n\t{\n\t\tdbMan = getParentZone().getDBManager();\n\t\tString sql = "SELECT username FROM highscores";\n\t\ttrace("About to execute: " + sql);\n\t\t\n\t\ttry\n\t\t{\n\t\t\tISFSArray result = dbMan.executeQuery(sql);\n\t\t\ttrace(result.getDump());\n\t\t}\n\t\tcatch(SQLException ex)\n\t\t{\n\t\t\ttrace("Error executing query: " + ex.getMessage());\n\t\t}\n\t}\n}\n<\/pre>\n\n\n\n
In the init()<\/strong> method we get a reference to the DBManager<\/strong> object associated with the current zone. This is the entity that manages our connections and allows us to execute queries, inserts, updates and so on.<\/p>\n\n\n\n
Next we execute a basic SQL statement to extract all the user names from the table created in part one of this article. It is important to note that the execution is wrapped in a try\/catch<\/strong> block to catch any potential SQLException, which can occur if the database is offline or there’s an error in the SQL statement etc.<\/p>\n\n\n\n
The result of the operation is an SFSArray<\/strong> containing as many SFSObject<\/strong> instances are there are results. In other words the returned array is a collection of records<\/strong>, each containing all of the columns that we have selected<\/strong> (in this case only one).<\/p>\n\n\n\n
The last line:<\/p>\n\n\n
\ntrace(result.getDump());\n<\/pre>\n\n\n\n
dumps the content of the SFSArray in the standard output, which will look like this:<\/p>\n\n\n
\n(sfs_object) \n\t(utf_string) username: fozzie\n\t\t\n(sfs_object) \n\t(utf_string) username: gonzo\n\t\t\n (sfs_object) \n\t(utf_string) username: kermit\n<\/pre>\n\n\n\n
Here we find listed every SFSObject contained in the array and its key-value content. Because we only selected the username<\/strong> field in our SQL, we only get one key-value pair in each SFSObject. <\/p>\n\n\n\n
\u00bb Sanitizing external input<\/h3>\n\n\n\n
In the code above we have a simple SQL query with no parameters coming from outside but in a more realistic use case we would likely have several parameters coming from the client, which we cannot trust.<\/p>\n\n\n\n
To avoid any SQL injection shenanigans<\/a> we’ll use an overloaded version of the executeQuery method available it the DBManager API: executeQuery(String, Object[])<\/strong> which takes a second argument with the parameters we want to use.<\/p>\n\n\n\n
An example will clarify how this works:<\/p>\n\n\n
\n\npublic int findHighscore(String userName)\n{\n\tdbMan = getParentZone().getDBManager();\n\tString sql = "SELECT * FROM highscores WHERE username=?";\n\tint highscore = -1;\n\n\ttry\n\t{\n\t\tISFSArray result = dbMan.executeQuery(sql, new Object[]{ userName });\n\t\t\n\t\tif (result.size() &amp;amp;gt; 0)\n\t\t{\n\t\t\thighscore = result.getSFSObject(0).getInt("highscore");\n\t\t}\n\t}\n\tcatch(SQLException ex)\n\t{\n\t\ttrace("Error executing query: " + ex.getMessage());\n\t}\n\n\treturn highscore;\n}\n<\/pre>\n\n\n\n
Here we have slightly modified version of the previous code: we have created a method that searches for a username and returns his\/her high score, if it exists.<\/p>\n\n\n\n
Since we now have an external parameter (the user name) we need to sanitize whatever data has been sent by the client: you can notice that our SQL expression now contains a WHERE<\/strong> clause and its value is not specified. Instead it’s replaced by a placeholder (the question mark) and the actual value is passed separately in the 2nd argument of executeQuery()<\/strong>.<\/p>\n\n\n\n
If we needed to include more parameters in the SQL we could just add more “?” placeholders and provide the relative value in the Object[] array<\/strong>, making sure to keep the correct order.<\/p>\n\n\n\n
For instance:<\/p>\n\n\n
\nString sql = "SELECT * FROM highscores WHERE username=? AND highscore &amp;amp;gt; ?";\n\nISFSArray result = dbMan.executeQuery(sql, new Object[]{ userName, minScore });\n<\/pre>\n\n\n\n
\u00bb Inserting, updating and deleting records<\/h3>\n\n\n\n
It won’t come as a surprise that inserting and updating records follows a very similar approach, by calling a different method of the DBManager API<\/strong>.<\/p>\n\n\n\n
Let’s take a look at how to insert a new record first:<\/p>\n\n\n
\n\tpublic void testInsert(String username, int score)\n\t{\n\t\tdbMan = getParentZone().getDBManager();\n\t\tString sql = "INSERT INTO highscores (username, score) VALUES (?, ?)";\n\t\t\n\t\ttry\n\t\t{\n\t\t\tdbMan.executeInsert(sql, new Object[] { username, score });\n\t\t\ttrace("DB Insert success");\n\t\t} \n\t\t\n\t\tcatch (SQLException ex)\n\t\t{\n\t\t\ttrace("DB Insert failure: " + ex.getMessage());\n\t\t}\n\t}\n<\/pre>\n\n\n\n
which can be invoked like this:<\/p>\n\n\n
\ntestInsert("swedish cook", 1300);\n<\/pre>\n\n\n\n
You’ll notice that the example code is almost identical to the previous query snippets with the difference that we are calling executeInsert()<\/strong>, rather than executeQuery()<\/strong>.<\/p>\n\n\n\n
\u00bb Record update<\/h4>\n\n\n\n
Now let’s see how the update works:<\/p>\n\n\n
\n\tpublic void testUpdate(String username, int score)\n\t{\n\t\tdbMan = getParentZone().getDBManager();\n\t\tString sql = "UPDATE highscores SET score=? WHERE username=?";\n\t\t\n\t\ttry\n\t\t{\n\t\t\tdbMan.executeInsert(sql, new Object[] { score, username });\n\t\t\ttrace("DB Update success");\n\t\t} \n\t\t\n\t\tcatch (SQLException ex)\n\t\t{\n\t\t\ttrace("DB Update failure: " + ex.getMessage());\n\t\t}\n\t}\n<\/pre>\n\n\n\n
And we can invoke the method like this:<\/p>\n\n\n
testUpdate("swedish cook", 1500)<\/pre>\n\n\n\n
\u00bb Record deletion<\/h4>\n\n\n\n
Finally let’s take a look at deleting a record from an existing table:<\/p>\n\n\n
\n\tpublic void testDelete(String username)\n\t{\n\t\tdbMan = getParentZone().getDBManager();\n\t\tString sql = "DELETE FROM highscores WHERE username=?";\n\t\t\n\t\ttry\n\t\t{\n\t\t\tdbMan.executeInsert(sql, new Object[] { username });\n\t\t\ttrace("DB Delete success");\n\t\t} \n\t\t\n\t\tcatch (SQLException ex)\n\t\t{\n\t\t\ttrace("DB Delete failure: " + ex.getMessage());\n\t\t}\n\t}\n<\/pre>\n\n\n\n
And let’s invoke it:<\/p>\n\n\n
testDelete("swedish cook")<\/pre>\n\n\n\n
\u00bb Next steps<\/h3>\n\n\n\n
Combining the tutorials from part one<\/a><\/strong> and part two of this series you should be able to get cracking with SFS2X and server-side databases coding.<\/p>\n\n\n\n
In the next (and last) episode we will take look at more advanced ways of working with databases using the standard Java JDBC API<\/strong> and provide some tips on local and remote deployment and testing. (Go to part 3<\/a>)<\/p>\n\n\n\n
If you have any comment or questions let us know via our SmartFoxServer support forum<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the previous installment of the this tutorial we have learned all the steps to create a database server in Overcast and connect to it from an existing SFS2X instance. In this new chapter we are going to explore the database API that can be used to query the database from server side, using SFS2X […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[23],"tags":[56,21,136,135,132,137,7],"_links":{"self":[{"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/posts\/1814"}],"collection":[{"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/comments?post=1814"}],"version-history":[{"count":20,"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/posts\/1814\/revisions"}],"predecessor-version":[{"id":1971,"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/posts\/1814\/revisions\/1971"}],"wp:attachment":[{"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/media?parent=1814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/categories?post=1814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/smartfoxserver.com\/blog\/wp-json\/wp\/v2\/tags?post=1814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}