ODBC / Database support in Torque
by Andrew Dubinsky · 01/01/2002 (9:42 am) · 22 comments
Download Code File
These files add basic ODBC support to the Torque engine. ODBC should allow you to use any vendor's database and run with little changes in both Windows and Unix. I am working on integrating Unix support using the Open ODBC libs.
This is pretty early code, but there are so many groups that need persistant data I decided to submit it. My goal is to improve both error handling and allow for a very flexible data map to the db columns.
There are a number of steps involved in this process, as well as a few requirements.
1. You need to have a database running some type of SQL. I have tested this with MS SQLServer, but it should work with other vendor's databases.
2. You need a table with the following structure (for this code to work).
Table name: users
UID - numeric
MaxHealth - numeric
For this example, set the values to: uid = 1, maxhealth = 55.
3. Create a datasource on the Server machine. There is no data connection on the client. Make certain that the default database is the one you need. The code does not issue a use database call.
4. At line 77 in odbcQuery.h, change these variables to reflect your datasource, database user and password.
DSource = "datasource";
UName = "username";
PWord = "password";
5. Add the two files, odbcQuery.h and odbcQuery.cc to your torque VC project. I recommend creating a new directory called odbc under your /torque dir.
6. Update VC6 to include the odbc libs on link. Under Project Settings | Link Tab
Add ODBC32.lib ODBCCP32.lib to the Object/library modules: box
If you do not have these files, download and install the Microsoft Data Access Components (MDAC) 2.6 SDK.
7. Edit the file load order in VC to reference the SDK files before the default libs. If not, the SDK header files will be out of version. Under Tools | Options | Directories tab, add this directory (where it is installed on your machine) C:\PROGRAM FILES\MICROSOFT DATA ACCESS SDK\INC include to the list at the top. These files only include header files for ODBC, so it can't break your build.
8. Edit the server script files to connect and query the database. I have chose fps\server\scripts\player.cs and fps\server\init.cs to run the tests. I found that creating the connection to the database incurs the highest overhead, so I only run it when the server starts. After that point, all queries will use the same connection, so the overhead drops.
9. Edits to fps\server\scripts\player.cs around line 550. Change the line maxDamage = 100 to the following:
10. Edits to fps\server\init.cs after line 43
From here, it's pretty easy to see how to extend the support to other variables, just copy and rename the method GetMaxHealth to whatever you want. I will work on making the code a little slicker and more flexible. I am far from an expert, so feel free to give me some tips. =)
These files add basic ODBC support to the Torque engine. ODBC should allow you to use any vendor's database and run with little changes in both Windows and Unix. I am working on integrating Unix support using the Open ODBC libs.
This is pretty early code, but there are so many groups that need persistant data I decided to submit it. My goal is to improve both error handling and allow for a very flexible data map to the db columns.
There are a number of steps involved in this process, as well as a few requirements.
1. You need to have a database running some type of SQL. I have tested this with MS SQLServer, but it should work with other vendor's databases.
2. You need a table with the following structure (for this code to work).
Table name: users
UID - numeric
MaxHealth - numeric
For this example, set the values to: uid = 1, maxhealth = 55.
3. Create a datasource on the Server machine. There is no data connection on the client. Make certain that the default database is the one you need. The code does not issue a use database call.
4. At line 77 in odbcQuery.h, change these variables to reflect your datasource, database user and password.
DSource = "datasource";
UName = "username";
PWord = "password";
5. Add the two files, odbcQuery.h and odbcQuery.cc to your torque VC project. I recommend creating a new directory called odbc under your /torque dir.
6. Update VC6 to include the odbc libs on link. Under Project Settings | Link Tab
Add ODBC32.lib ODBCCP32.lib to the Object/library modules: box
If you do not have these files, download and install the Microsoft Data Access Components (MDAC) 2.6 SDK.
7. Edit the file load order in VC to reference the SDK files before the default libs. If not, the SDK header files will be out of version. Under Tools | Options | Directories tab, add this directory (where it is installed on your machine) C:\PROGRAM FILES\MICROSOFT DATA ACCESS SDK\INC include to the list at the top. These files only include header files for ODBC, so it can't break your build.
8. Edit the server script files to connect and query the database. I have chose fps\server\scripts\player.cs and fps\server\init.cs to run the tests. I found that creating the connection to the database incurs the highest overhead, so I only run it when the server starts. After that point, all queries will use the same connection, so the overhead drops.
9. Edits to fps\server\scripts\player.cs around line 550. Change the line maxDamage = 100 to the following:
maxDamage = $Server::PlayerHealth;
10. Edits to fps\server\init.cs after line 43
echo("\n--------- Connecting to DB: Twitch ---------");
//This sets up the connection
$Server::ConnectObject = new ODBCConnection();
$Server::ConnectObject.Connect();
// This sets the player values to global vars.
$Server::PlayerHealth = $Server::ConnectObject.GetMaxHealth();From here, it's pretty easy to see how to extend the support to other variables, just copy and rename the method GetMaxHealth to whatever you want. I will work on making the code a little slicker and more flexible. I am far from an expert, so feel free to give me some tips. =)
#2
01/03/2002 (7:28 pm)
I uploaded the wrong odbcQuery.h file. It should be fixed now. =)
#4
05/14/2003 (6:55 pm)
This went in very easily. You've done a great job.
#5
S32 ODBCConnection::Connect()
{
DSource = "DividedDominion";
UName = "DividedDominion";
PWord = "DividedDominion";
S32 mretcode = 0;
if (!m_bIsConnected)
{
if (SQL_SUCCESS == (mretcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv) ) )
{
if (SQL_SUCCESS ==(mretcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0) ) )
{
if (SQL_SUCCESS == (mretcode = SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &m_hdbc) ) )
{
// 05-15-2003 RFB -> Fix, comment out incorrect logic
//if (SQL_SUCCESS_WITH_INFO == (mretcode = SQLConnect(m_hdbc, (SQLCHAR*) DSource, SQL_NTS,(SQLCHAR*) UName, SQL_NTS,(SQLCHAR*) PWord, SQL_NTS) ) )
//{
// 05-15-2003 RFB -> Fix, replace logic with
mretcode = SQLConnect(m_hdbc, (SQLCHAR*) DSource, SQL_NTS,(SQLCHAR*) UName, SQL_NTS,(SQLCHAR*) PWord, SQL_NTS);
//
if ( (SQL_SUCCESS == mretcode) || (SQL_SUCCESS_WITH_INFO == mretcode) )
{
m_bIsConnected = TRUE;
}
//}
// <- RFB
}
}
}
}
return mretcode;
}
05/15/2003 (4:17 pm)
Found one small problem, i changed Connect() as follows. . .S32 ODBCConnection::Connect()
{
DSource = "DividedDominion";
UName = "DividedDominion";
PWord = "DividedDominion";
S32 mretcode = 0;
if (!m_bIsConnected)
{
if (SQL_SUCCESS == (mretcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv) ) )
{
if (SQL_SUCCESS ==(mretcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0) ) )
{
if (SQL_SUCCESS == (mretcode = SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &m_hdbc) ) )
{
// 05-15-2003 RFB -> Fix, comment out incorrect logic
//if (SQL_SUCCESS_WITH_INFO == (mretcode = SQLConnect(m_hdbc, (SQLCHAR*) DSource, SQL_NTS,(SQLCHAR*) UName, SQL_NTS,(SQLCHAR*) PWord, SQL_NTS) ) )
//{
// 05-15-2003 RFB -> Fix, replace logic with
mretcode = SQLConnect(m_hdbc, (SQLCHAR*) DSource, SQL_NTS,(SQLCHAR*) UName, SQL_NTS,(SQLCHAR*) PWord, SQL_NTS);
//
if ( (SQL_SUCCESS == mretcode) || (SQL_SUCCESS_WITH_INFO == mretcode) )
{
m_bIsConnected = TRUE;
}
//}
// <- RFB
}
}
}
}
return mretcode;
}
#6
06/26/2003 (7:43 pm)
Wow, I was looking into making a RPG in torque, and this will REALLY help. Thanks a lot!
#7
12/11/2003 (3:52 pm)
This is great! I have been trying to implement ODBC into my game for some time. This will save me a lot of time!
#9
06/29/2004 (6:49 am)
ODBC(open database connectivity)is just a way of 'talking' too an SQL database, you can query the database and do all sorts using the code. 
#10
I'm working with MySQL and Rob Davidson is working on MS-SQL.
We were a little stressed until we found this.
It works in the 1.3 HEAD perfectly.
Installing this is like getting a neck rub after 18 hours of code.
I download the MySQL ODBC 3.5 drivers for windows...
And BANG!
We were able to use both types SQL servers.
Thank you Andrew!
12/12/2004 (12:21 am)
OMG!I'm working with MySQL and Rob Davidson is working on MS-SQL.
We were a little stressed until we found this.
It works in the 1.3 HEAD perfectly.
Installing this is like getting a neck rub after 18 hours of code.
I download the MySQL ODBC 3.5 drivers for windows...
And BANG!
We were able to use both types SQL servers.
Thank you Andrew!
#11
03/05/2005 (7:13 pm)
I am using the odbcquery code I found on the site. It works great, except I want to use essentually pref::player::Name in the SQL statement instead of UID = 1. How would I assign the variable in odbcquery.h to use $user where $user = pref::plater:: name instead? Anyone with ideas? 
#12
04/24/2005 (5:13 pm)
#13
04/24/2005 (5:23 pm)
#14
04/24/2005 (5:30 pm)
#15
I would be interested in any instructions or pointers anyone has
05/19/2005 (8:22 am)
Has anyone set this up in the TBE?I would be interested in any instructions or pointers anyone has
#16
I originally asked a question about how to modify this to return strings and such. What I've done is taken the example function provided and modified it so that it can return more diverse types from the database given a SQL string input.
Here's my code:
const char * ODBCConnection::GetResultString(const char* statement) {
int h_nCount = 0;
int rcode = 0;
int returnvar;
retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
m_Connection = m_hdbc;
ExecBuffer2 = statement;
//ExecBuffer2 = const_cast(statement);
retcode = SQLExecDirect(m_hstmt, (SQLCHAR*) ExecBuffer2, SQL_NTS);
m_bNeverExecutedSQL = FALSE;
rcode = SQLNumResultCols(m_hstmt, &mCols);
m_nNumberOfCols = mCols;
SQLBindCol(m_hstmt, 1, SQL_C_CHAR, &ResultString2, sizeof(ResultString2), &ResultReturn);
m_nNumberOfRecordsFetched = 0;
rr= SQLFetch(m_hstmt);
const char* temp2 = reinterpret_cast(ResultString2);
//return const_cast(temp);
Con::printf(temp2);
return temp2;
}
Notes:
You'll need these at the top of odbcQuery.h with your other variables:
SQLINTEGER sMaxHealth, cbMaxHealth, ResultReturn, ResultString;
UCHAR ResultString2[128];
const char* ExecBuffer2;
This allows you to hit console and do $Server::ConnectObject.GetResultString("SELECT Whatever From WhicheverTable WHERE Condition etc");
...Inserting just about any SQL you can think of.
I hope that's useful to someone, it meets my needs.
(You might want to take that con::printf out of it, it's kinda there for debugging purposes)
07/25/2005 (6:45 am)
-Edit- I originally asked a question about how to modify this to return strings and such. What I've done is taken the example function provided and modified it so that it can return more diverse types from the database given a SQL string input.
Here's my code:
const char * ODBCConnection::GetResultString(const char* statement) {
int h_nCount = 0;
int rcode = 0;
int returnvar;
retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
m_Connection = m_hdbc;
ExecBuffer2 = statement;
//ExecBuffer2 = const_cast
retcode = SQLExecDirect(m_hstmt, (SQLCHAR*) ExecBuffer2, SQL_NTS);
m_bNeverExecutedSQL = FALSE;
rcode = SQLNumResultCols(m_hstmt, &mCols);
m_nNumberOfCols = mCols;
SQLBindCol(m_hstmt, 1, SQL_C_CHAR, &ResultString2, sizeof(ResultString2), &ResultReturn);
m_nNumberOfRecordsFetched = 0;
rr= SQLFetch(m_hstmt);
const char* temp2 = reinterpret_cast
//return const_cast
Con::printf(temp2);
return temp2;
}
Notes:
You'll need these at the top of odbcQuery.h with your other variables:
SQLINTEGER sMaxHealth, cbMaxHealth, ResultReturn, ResultString;
UCHAR ResultString2[128];
const char* ExecBuffer2;
This allows you to hit console and do $Server::ConnectObject.GetResultString("SELECT Whatever From WhicheverTable WHERE Condition etc");
...Inserting just about any SQL you can think of.
I hope that's useful to someone, it meets my needs.
(You might want to take that con::printf out of it, it's kinda there for debugging purposes)
#17
Add ODBC32.lib ODBCCP32.lib to the Object/library modules: box
If you do not have these files, download and install the Microsoft Data Access Components (MDAC) 2.6 SDK.
i did not have the file so i download it. but i have a problem, do i install the Microsoft Data Access Components (MDAC) 2.6 SDK in my computer or in the server where i kept the database?
chunwei (student of tp)
08/10/2005 (8:52 pm)
step 6 of the resource say: Update VC6 to include the odbc libs on link. Under Project Settings | Link TabAdd ODBC32.lib ODBCCP32.lib to the Object/library modules: box
If you do not have these files, download and install the Microsoft Data Access Components (MDAC) 2.6 SDK.
i did not have the file so i download it. but i have a problem, do i install the Microsoft Data Access Components (MDAC) 2.6 SDK in my computer or in the server where i kept the database?
chunwei (student of tp)
#19
02/25/2006 (11:37 am)
is it somewhat easier ot save info liek this than to make a save load script for torque games? 
Torque Owner David Dougher
Pariah Games
:\torque\engine\odbc\odbcquery.h(55) : error C2252: 'h_nCount' : pure specifier can only be specified for functions
c:\torque\engine\odbc\odbcquery.h(56) : error C2252: 'rcode' : pure specifier can only be specified for functions
c:\torque\engine\odbc\odbcquery.h(113) : error C2065: 'rcode' : undeclared identifier