How to distribute database with game

[united121]

Hi,

I am creating a game where I need to work with database. I was trying to work with online free MySQL database (but after few week “free” time expired and I had to pay or lost my data) … then I was developing with local database ( using XAMPP and localhost ) but I am little confused how to distribute my program with database. Is there any approach that will help me. I need to copy database into a files ? or something like that (every program will have some basic data and other data it create separately)

Thanks ( I hope that somebody understand my poor English … )

[CopyableCougar4]

In my opinion the best option would be to rent a public server and run a database on it.

CopyableCougar4

[basil]

if you use java, u could use H2 DB.

run the sql server inside the jvm you run the game. the database is just a file, so you could distribute it just like any other resource.

[united121]

H2 DB look exactly like what i need but am I get it right that i need install it on every conputer where I want to use my app ? Because quick look on its website say so … It isnt confortable

[basil]

no no, you just “run” the sever when you start the jvm (the game). they call it “embedded mode”.

include H2 jars to classpath and use the API as described here : http://www.h2database.com/html/tutorial.html#using_server

import org.h2.tools.Server;
...
// start the TCP Server
Server server = Server.createTcpServer(args).start();
...
// stop the TCP Server
server.stop();

the “weird” thing about it is, on one hand you simply use a jdbc connection with the [icode]org.h2.Driver[/icode] but on the other, creating a database works usually by connecting to a non-existing database, which is then created for you.

try starting the server in a simple java application, and then connect through your IDE. eclipse, netbeans or idea have nice build-in jdbc/database support. it’s really easy once you got used to it a bit

[united121]

It support classic SQL ( how I can send SQL command - I dont see that options ? ) and how I can create database or how I can distrube it with this program ? sounds like I am noob but I think that you are more familier with it and can help me

I have written this code already

Server serverH2 = Server.createTcpServer("-tcpPort", "8888", "-tcpAllowOthers").start();
        
        conn = DriverManager.getConnection("jdbc:h2:~/test", "root", "");
        if (conn == null) {
            throw new IllegalAccessException("Error in connection");
        }

and of cource disconnect statement.
and then the same code like with my local MySQL - correct aproach ?

or in code a need call this ? - dont try to get meaning of this words (it is Czech language)

CREATE TABLE IF NOT EXISTS `herogame_hero` (
`ID_hero` int(11) NOT NULL,
  `jmeno` varchar(255) COLLATE utf8_bin NOT NULL,
  `zivot` int(11) NOT NULL,
  `mana` int(11) NOT NULL,
  `vydrz` int(11) NOT NULL,
  `degMana` int(11) NOT NULL,
  `degVydr` int(11) NOT NULL,
  `jmenoUtoku1` varchar(255) COLLATE utf8_bin NOT NULL,
  `utok1` int(11) NOT NULL,
  `jmenoUtoku2` varchar(255) COLLATE utf8_bin NOT NULL,
  `utok2` int(11) NOT NULL,
  `jmenoObrany` varchar(255) COLLATE utf8_bin NOT NULL,
  `obrana` int(11) NOT NULL,
  `jmenoSpecUtoku` varchar(255) COLLATE utf8_bin NOT NULL,
  `specUtok` int(11) NOT NULL,
  `picture` varchar(255) COLLATE utf8_bin NOT NULL,
  `username` varchar(255) COLLATE utf8_bin NOT NULL,
  `password` varchar(255) COLLATE utf8_bin NOT NULL
)

[basil]

no worries

the args could be something like :

int port = 7778;
String baseDir = "path_where_to_store_databases";

String[] args =
{
  "-tcp",
  "-tcpPort", Integer.toString(port),
  "-baseDir", baseDir,
  "-tcpDaemon",
  "-tcpAllowOthers"
  // "-ifExists" // do not create new databases
};

when you start the server, it’s done. connecting to the server is done the same way you would connect a mysql server from java.

before doing that, it’s good to test if you can connect at all. which IDE do you use? as mentioned, it probably comes with a buildin tool which can connect databases.

to create a new database, you simple connect it. if the database is not existing, it will be created by the server. then it’s empty.

once connected you can issue all the usual [icode]create table …; select * from …; etc etc[/icode] statements.

or you have a .sql file written before and load it into the database : http://www.h2database.com/html/grammar.html?highlight=SOURCE_DATA_TYPE&search=source#runscript

if you’re used to mysql - this grammar is a bit more strict. it’s basically sql99 standard. http://en.wikipedia.org/wiki/SQL:1999

tho’ if you connect using jdbc : http://www.h2database.com/html/tutorial.html#connecting_using_jdbc you can pass a few hints to the server http://www.h2database.com/html/features.html#compatibility

a simple jdbc url could look like

String host = "localhost";
int port = 7779;

String database = "my_database";

String params = ";IFEXISTS=TRUE;MODE=MySQL"; // mimic mysql.

String url = "jdbc:h2:tcp://" + host + ":" + port + "/" + database + params;

after connecting this, you should see a [icode]my_database[/icode] file under the [icode]baseDir[/icode] directory.

[basil]

oh, you’ve been faster than me … it “should” work.

[united121]

So now it look like this ->

String baseDir = "path_where_to_store_databases"; -> I dont know where I have to use it as argument

        String[] args
                = {
                    "-tcp",
                    "-tcpPort", Integer.toString(8888),
                    "-baseDir", baseDir,
                    "-tcpDaemon",
                    "-tcpAllowOthers"
                    "-ifExists" // do not create new databases
                };

        serverH2 = Server.createTcpServer(args).start();



String database = "my_database";

String params = ";IFEXISTS=TRUE;MODE=MySQL"; // mimic mysql.
String url = "jdbc:h2:tcp://" + host + ":" + port + "/" + database + params;

        conn = DriverManager.getConnection(url); -> I think that I need to use parameters that I am 'root' user
        if (conn == null) {
            throw new IllegalAccessException("Error in connection");
        }
    }

and i have data.sql scrip which is similar to this:

CREATE TABLE IF NOT EXISTS `herogame_hero` (
`ID_hero` int(11) NOT NULL,
  `jmeno` varchar(255)  NOT NULL,
  `zivot` int(11) NOT NULL,
...

INSERT INTO `herogame_hero` (`...) VALUES
(1, 'aa', 100, 100, 50, 7, 4, 'sf', 15, 'fas', 35, 'fas', 10, 'sdaf', 50, 'OBR1', 'mag', 'mag'),

and I need to run this script to fill empty database - am I right ? I look at that link you send but i dont know how to use it I googled and found this http://www.h2database.com/javadoc/org/h2/tools/RunScript.html?highlight=run%2Cscrup&search=run%20scrup but the same problem i dont know how to use it

EDIT:
I found this

INIT=RUNSCRIPT FROM 'classpath:scripts/create.sql'"

so my params would look like this

String params = ";IFEXISTS=TRUE;MODE=MySQL;INIT=RUNSCRIPT FROM 'classpath:scripts/create.sql'";

Am I right when I think that this create new database with scripts in create.sql ( it database exist nothing gonna happen ) and it is ? But I still dont know how my program detect if the database already exist.

[united121]

My current code look like that :

        String baseDir = "path_where_to_store_databases";

        String[] args
                = {
                    "-tcp",
                    "-tcpPort", Integer.toString(8888),
                    "-baseDir", baseDir,
                    "-tcpDaemon",
                    "-tcpAllowOthers",
                    "-ifExists" // do not create new databases
                };
        
        serverH2 = Server.createTcpServer(args).start();
        
        String params = ";IFEXISTS=TRUE;MODE=MySQL;";

        String database = "~/test";
        try (Statement stat = conn.createStatement()) {
            stat.execute("runscript from 'init.sql'");
        }
        String params2 = ";IFEXISTS=TRUE;MODE=MySQL"; // mimic mysql.
        int port2 = 8888;
        String host = "localhost";
        String url = "jdbc:h2:tcp://" + host + ":" + port2 + "/" + database + params;

        

        //String url = "jdbc:mysql://" + server + ":" + port + "/" + db;
        //conn = DriverManager.getConnection(url, name, password);
        //conn = DriverManager.getConnection();
        conn = DriverManager.getConnection(url);
        if (conn == null) {
            throw new IllegalAccessException("Error in connection");
        }

but java gave my nullPointerException (I use Netbeans 8.0 , Windows 7 64bit, java jdk 8)

[basil]

remove [icode]-ifExists[/icode] from server args. it disables creation of new databases.

[united121]

I have this code

 String baseDir = "src/main/resources/scripts";

        String[] args
                = {
                    "-tcp",
                    "-tcpPort", Integer.toString(8888),
                    "-baseDir", baseDir,
                    "-tcpDaemon",
                    "-tcpAllowOthers", //"-ifExists" // do not create new databases
                };

        serverH2 = Server.createTcpServer(args).start();

        String params = ";IFEXISTS=TRUE;MODE=MySQL;";

        String database = "~/test4";

        String params2 = ";IFEXISTS=TRUE;MODE=MySQL"; // mimic mysql.
        int port2 = 8888;
        String host = "localhost";
        String url = "jdbc:h2:tcp://" + host + ":" + port2 + "/" + database + params;
        conn = DriverManager.getConnection(url);
        if (conn == null) {
            throw new IllegalAccessException("Error in connection");
        }
        try (Statement stat = conn.createStatement()) {
            stat.execute("runscript from 'init.sql'");
        }

but on the line

conn = DriverManager.getConnection(url);

I gave this error

org.h2.jdbc.JdbcSQLException: IO Exception: "C:/Users/United121/test4 outside C:/Users/United121/Documents/NetBeansProjects/HeroGame/src/main/resources/scripts" [90028-182]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

is it someting about permission or something like that ?

[basil]

that error doesn’t ring a bell for me. O_o

[united121]

Error was solve by changind this line :

String url = "jdbc:h2:tcp://" + host + ":" + port2 + "/" + database + params;

to

String url = "jdbc:h2:" + database;

even a init script was running but I cant find where the database was save … I dont need to distribute a game with a init.sql script where everybody can change data a see username and password

EDIT: I found the database there :

C:\Users\United121\test4.mv.db

It is a little bit unconfortable have a DB in different folder than rest code - I think I ll need install wizard in that way …

[basil]

that’s what the baseDir server param is for. you set the database root folder.

[united121]

I solved it ( I use linux statement for folder where we are … “./” and it generate my database correctly in folder where are my code

String database = "./testik/test4";

A lot of thanks for basil_

if someone have the same problem -> this is whole code of creating database and running init script to fill database

        String[] args
                = {
                    "-tcp",
                    "-tcpPort", Integer.toString(8888),
                    "-tcpDaemon",
                    "-tcpAllowOthers", //"-ifExists" // do not create new databases
                };

        serverH2 = Server.createTcpServer(args).start();

        //String params = ";IFEXISTS=TRUE;MODE=MySQL;"; // use later when the database is correct -> this doenst create new one
        String database = "./database/dat";
        String params2 = ";MODE=MySQL"; // mimic mysql.
        
        String url = "jdbc:h2:" + database + params2;
        conn = DriverManager.getConnection(url);
        if (conn == null) {
            throw new IllegalAccessException("Error in connection");
        }
        try (Statement stat = conn.createStatement()) {
            stat.execute("runscript from 'init.sql'");
        }

[basil]

sweet

[pjt33]

SQLite has JDBC drivers. No need to listen on ports: it just needs to be able to access a file, and it supports basic SQL.