Updated ODBC support for TGE 1.4, TGB 1.1 and TSE ms4
by Jonathon Stevens · 02/01/2006 (5:01 pm) · 216 comments
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:
#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:
#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:
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.
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.
About the author
With a few casual games under his belt as CEO of Last Straw Productions, Jonathon created the increasingly popular Indie MMO Game Developers Conference.
#2
Did you add in support for ntext, nvarchar etc?
The old one would through an error when you tried to read from one of those fields... it really didn't like the memory abuse :).
Vince
02/01/2006 (8:13 pm)
Damn, this code gets re-written daily :)Did you add in support for ntext, nvarchar etc?
The old one would through an error when you tried to read from one of those fields... it really didn't like the memory abuse :).
Vince
#3
02/01/2006 (8:57 pm)
I'll have to look at the sqlheader files from microsoft sdk to verify, but i'm pretty sure those are being handled on MS end. From what I read, if I remember right, we were converting all of the data types into either int, varchar and bool or something like that. I don't know the reasoning yet, but I just quickly wanted an easy way to switch between several diff db providers so figured I'd throw this out. I'm going to add MS SQL integrated security next and then SPROC abilities after that.
#4
02/02/2006 (7:05 am)
the problem with text fields is that their max width is 2147483647. You can't create a char array that wide, so the system blows when trying to assign the memory to the pointer... The max width for a varchar is 4000... which is too small. To be honest I just want something around 10-20k but if I try to override the size it blows farther up... I'm gonna try and over ride this mess... but it can get fustrating :(
#5
02/03/2006 (7:40 am)
Should there be a BREAK; in each of those Case entries?
#6
02/03/2006 (7:49 am)
Negative, TS doesn't require it.
#7
02/03/2006 (10:53 am)
Jonathan, do you have any experience with SQL Server 2005 Express version? I see that its free, even for re-distribution (with license). I've never used SQL Server before but it seems like this might be a good opportunity to give it a shot using this resource.
#8
02/03/2006 (11:21 am)
Yes, SQL Server 2005 Express is pretty much the full version with some tools missing such as DTS and reporting services, etc. Just use the 2005 software preference to connect to it as it should be same connection string. You need to be careful with datatypes though. This resource only supports a small number of SQL Server's available datatypes. Along with Sproc support, I'm going to look at more support for more datatypes.
#9
02/03/2006 (12:02 pm)
Ok, thanks, I'm sure it will work fine for my purposes.
#10
02/03/2006 (2:05 pm)
Make sure you also download the 'SQL Server Management Studio 2005 Express' too.
#11
02/03/2006 (2:14 pm)
Oh, man, well maybe this won't work for what I'm trying to do. Seems like I maybe should stick with my embedded BerkeleyDB or SQLite. I'm looking for something that will run on the same hardware as the game server (Torque). MS SQL Server, even the free version, is probably overkill for what I want.
#12
02/03/2006 (3:34 pm)
Hmm? It's free.. so who cares if it's overkill. =P
#13
02/03/2006 (3:39 pm)
Well, true, I guess for test purposes its not going to hurt anything. :)
#14
****** driver: MySQL ODBC 3.51 Driver connectionstring server=localhost;port=3306;database=test;user=TESTER;password=PASSWORD;option=3;
game/server/scripts/db/init.cs (0): Unable to find object: '0' attempting to call function 'ConnectDriver'
I can't get this thing to let go of the 'server=localhost'
I have the default.cs setup to use 192.168.2.100. Which my other applications use fine. So I know the IP is working. I delete the DSOs everytime I restart. I'm at a loss for what I'm missing here.
02/03/2006 (5:21 pm)
***** software: 1****** driver: MySQL ODBC 3.51 Driver connectionstring server=localhost;port=3306;database=test;user=TESTER;password=PASSWORD;option=3;
game/server/scripts/db/init.cs (0): Unable to find object: '0' attempting to call function 'ConnectDriver'
I can't get this thing to let go of the 'server=localhost'
I have the default.cs setup to use 192.168.2.100. Which my other applications use fine. So I know the IP is working. I delete the DSOs everytime I restart. I'm at a loss for what I'm missing here.
#15
02/03/2006 (6:29 pm)
delete your prefs not your dso's, that should clear it.
#16
Now I'm back to the old error.
****** driver: MySQL ODBC 3.51 Driver connectionstring server=192.168.2.100;port=3306;database=test;user=TESTER;password=password;option=3;
DatabaseConnection::Connect - SQL Error - state(
02/03/2006 (7:36 pm)
Nice. A laps in sanity must have kept me from doing that. :)Now I'm back to the old error.
****** driver: MySQL ODBC 3.51 Driver connectionstring server=192.168.2.100;port=3306;database=test;user=TESTER;password=password;option=3;
DatabaseConnection::Connect - SQL Error - state(
#17
02/03/2006 (7:58 pm)
Ok guys, updated this to support stored procedures. The one thing to keep in mind is you must loop through all the results if more than one record is returned to then call another stored proc after that. I don't know a work around for this, so anyone is welcome to let me know of a solution.
#18
02/03/2006 (8:00 pm)
Hmm... Have you tried setting up a DSN through windows to make sure you can connect using that connection string? Make sure you didn't accidentally put the wrong uid, pw, db, port, etc.
#19
I was able to restore other DBs. So I know my commands are correct.
I know this isn't the place to ask this question, BUT what this supposed to mean?
at corresponds to your MySQL server version for the right syntax to use near '
02/03/2006 (8:26 pm)
I guess I have to manually create the database. I can't get any of the imports to work with mysql.I was able to restore other DBs. So I know my commands are correct.
I know this isn't the place to ask this question, BUT what this supposed to mean?
at corresponds to your MySQL server version for the right syntax to use near '
#20
02/03/2006 (9:48 pm)
What version of MySQL are you using? 
Torque Owner Josh Williams
Default Studio Name