Updated ODBC support for TGE 1.4, TGB 1.1 and TSE ms4

Download Code File

In this section I'll show you how to install the TGE Mac/Lin/Win MySQL ODBC resource and updating it to include support for Remote MySQL, SQL Server 2000/2005, and Oracle. Thanks to OneST8 for the original resource found here: www.garagegames.com/index.php?sec=mg&mod=resource&page=view&qid=7890. Additional credits are in the source code files. YOU DO NOT complete the aformentioned resource. This is meant to replace it.


*********************************************************************************
UPDATE:: I also have two .sql files included: MySQL.sql and SQLServer2005.sql. You can run them from their respective database to get a basic database layout to test with.

UPDATE 2-3-06: I have now updated this resource to include stored procedure support. Simply copy the new utils.cs file over the one you have to update.

UPDATE 3-2-06: I have added the ability to clear the results set as well as a row counter. I've also added stored procedure support. I have NOT tested sprocs on MySQL, only on SQL Server 2005. If someone could test that'd be great.

UPDATE 7-8-06: I have updated the resource to account for the CTD issue when closing the game as well as random other issues (int issue) and some bug fixing. This now also works on TGE 1.4 and TSE ms3.5.

UPDATE 7-8-06b: Few more small fixes and updated to work on Mac OS now!

UPDATE 7-9-06: Added more steps to cover things such as the SQL libs and MySQL ODBC Drivers, etc.

UPDATE 7-28-06: Changed all global results variables to local. Thanks Dreamer!

UPDATE 9-1-06: Few small fixes and addition of prepared statements thanks to David Michael! Updated the test script to allow testing of both prepared and non-prepared statements. Set $db_test_prepared to true to test prepared statements.

UPDATE 10-17-06: A few more bug fixes.

This is confirmed working for Linux, Mac OS X and Windows. DB wise it has been confirmed working on dedicated and local instances with MySQL 5.0+ and on MS SQL Server 2005 express and pro.

I still need some testers for other DB systems to verify functionality, please email me or post here if you have any results to add.
*********************************************************************************



#1 - (download zip) Download the attached zip file. Extract the following files into your engine/console folder:

databaseConnection.cc
databaseConnection.h
databaseResult.cc
databaseResult.h
databasePrepared.cc
databasePrepared.h

#1.2 - Copy the 'db' directory into your 'starter.rpg/server/scripts/' folder.

#1.3 - Make sure you have the ODBC library linked in your project Windows users should already have this on their computer. Linux users just do a search for it on the web and download from there (someone wanna post a link for this?)

#2 - (Edit Scripts) Open up 'server/scripts/game.cs' and below the exec("./aiPlayer.cs"); line add exec("./db/init.cs");

#2.1 - Open up 'server/defaults.cs' and at the bottom add:

//-----------------------------------------------------------------------------
// ODBC Database connection prefs
//-----------------------------------------------------------------------------

$Pref::Server::DB::Software = 0; // 0 - MySQL Local, 1 - MySQL Remote, 2 - SQL Serv 2000, 3 - SQL Serv 2005, 4 - Oracle
$Pref::Server::DB::Port = ""; // DB Port number
$Pref::Server::DB::Server = "localhost"; // Server's url or ip
$Pref::Server::DB::Database = "starter_rpg"; // Database name
$Pref::Server::DB::User = "TESTER"; // username
$Pref::Server::DB::Password = "password"; // password

//check platform for which driver to use
if ($platform $= "windows")
{
//check database they are using
switch($Pref::Server::DB::Software)
{
case 0:
$Pref::Server::DB::Driver = "MySQL ODBC 3.51 Driver";
case 1:
$Pref::Server::DB::Driver = "MySQL ODBC 3.51 Driver";
case 2:
$Pref::Server::DB::Driver = "SQL Server";
case 3:
$Pref::Server::DB::Driver = "SQL Native Client";
case 4:
$Pref::Server::DB::Driver = "Microsoft ODBC for Oracle";
default:
$Pref::Server::DB::Driver = "ERROR: UNKNOWN DATABASE:" SPC $Pref::Server::DB::Database;
}
}
else
{
//check database they are using
switch($Pref::Server::DB::Software)
{
case 0:
$Pref::Server::DB::Driver = "MySQL";
case 1:
$Pref::Server::DB::Driver = "MySQL";
case 2:
$Pref::Server::DB::Driver = "ERROR: SQL SERVER NOT SUPPORTED BY LINUX";
case 3:
$Pref::Server::DB::Driver = "ERROR: SQL SERVER NOT SUPPORTED BY LINUX";
case 4:
$Pref::Server::DB::Driver = "Oracle";
default:
$Pref::Server::DB::Driver = "ERROR: UNKNOWN DATABASE:" SPC $Pref::Server::DB::Database;
}
}

#2.2 - Make sure you install ODBC drivers for the database you are going to use. MySQL for all platforms can be found at: dev.mysql.com/downloads/connector/odbc/3.51.html. If you are going to use MS SQL Server, the drivers will already be installed on your machine.

FOR LINUX ONLY:

#2.3 - Open engine/targets.torque.mk in the section for dedicated server build replace:

$(DIR.OBJ)/$(EXE_DEDICATED_NAME)$(EXT.EXE): LINK.LIBS.GENERAL = \

with

$(DIR.OBJ)/$(EXE_DEDICATED_NAME)$(EXT.EXE): LINK.LIBS.GENERAL = -lodbc \

END LINUX ONLY BIT

#3 - (compile!) In solution explorer browse to Torque Demo->console and right-click and select add->existing item. Browse to your engine/console folder and select the four database files we extracted in step 1. Once done, compile the solution.

That's it! Now we're ready to set the database up. On the next tutorial I'll show you how to set up our database structure for MySQL and SQL Server.