ODBC Database Access for torque

#1 Post it as a resource?

nm, I see it as a resource now :)

#2 It has been a while, but I just posted a slightly updated version at the above link. This version works great with mySQL as well as MS SQL Server.

Josh

#3 Has anyone tried to use this in vc7? Im having difficulties compling this. I was able to fix the errors im getting by including right above the includes...

But I really shouldnt have to do this...

Here are a few of the errors im getting...


An few example errors are:
d:\development\vs.net\Vc7\PlatformSDK\Include\SqlTypes.h(275): error C2146: syntax error : missing ';' before identifier 'Data1'

d:\development\vs.net\Vc7\PlatformSDK\Include\SqlTypes.h(275): error C2501: 'tagSQLGUID::DWORD' : missing storage-class or type specifiers

d:\development\vs.net\Vc7\PlatformSDK\Include\SqlTypes.h(275): error C2501: 'tagSQLGUID::Data1' : missing storage-class or type specifiers

Here is the code this i coming from:

DWORD Data1;

.. in ..

typedef struct tagSQLGUID
{
DWORD Data1;
WORD Data2;
WORD Data3;
BYTE Data4[ 8 ];
} SQLGUID;

Any help would be appreciated... I dont want to leave it with just including ..

#4 I ran into similar problems. For some reason, MS felt the need to add some of their datatypes to sql.h. #including windows.h should nto have to be done, but I think it will not cause any other problems. If anyone has any suggestions on how to avoid it, please let me know.

Josh

#5 I added the #include but still have problems compiling:

Compiling...
DatabaseConnection.cc
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1812) : error C2061: syntax error : identifier 'SQLLEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1829) : error C2061: syntax error : identifier 'SQLULEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1836) : error C2061: syntax error : identifier 'SQLLEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1872) : error C2061: syntax error : identifier 'SQLULEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1908) : error C2061: syntax error : identifier 'SQLSETPOSIROW'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1937) : error C2061: syntax error : identifier 'SQLULEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(2006) : error C2061: syntax error : identifier 'SQLLEN'
DatabaseResult.cc
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1812) : error C2061: syntax error : identifier 'SQLLEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1829) : error C2061: syntax error : identifier 'SQLULEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1836) : error C2061: syntax error : identifier 'SQLLEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1872) : error C2061: syntax error : identifier 'SQLULEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1908) : error C2061: syntax error : identifier 'SQLSETPOSIROW'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(1937) : error C2061: syntax error : identifier 'SQLULEN'
c:\program files\microsoft visual studio\vc98\include\sqlext.h(2006) : error C2061: syntax error : identifier 'SQLLEN'
f:\cvs\torque\engine\console\databaseresult.cc(411) : error C2660: 'SQLSetPos' : function does not take 4 parameters
Error executing cl.exe.

Any help on fixing this will be appreciated.

#6 Hi everyone,

I ran into the same issue as Loonatik. I'm on VS.NET 2003. In doing a little searching I found a reference to migration_guide.doc (In the VC7 folder). In that doc, it pointed to a wtypes.h in dealing with migrating apps to managed code.

In DatabaseResults.h and DatabaseConnection.h I added the following:

#ifdef WIN32
// hopefully other platforms don't need this, windows needs it
// cause ms has things like SQLHWND and whatnot
#include <objbase.h>
#include <tchar.h>
#endif

[b]#include <wtypes.h>[/b]
#include <sql.h>
#include <sqlext.h>

That let me compile. I haven't tested actually connecting.
-Joe

#7 It's late, so excuse my ignorance ;)

should'nt

#include

actualy be inside the #ifdef #endif ???

#8 Ron, your absolutely right. So it should now look like this.

In DatabaseResults.h and DatabaseConnection.h:

#ifdef [b]TORQUE_OS_WIN32[/b]
// hopefully other platforms don't need this, windows needs it
// cause ms has things like SQLHWND and whatnot
[b]#include <wtypes.h>[/b]
#include <objbase.h>
#include <tchar.h>
#endif

#include <sql.h>
#include <sqlext.h>

Also notice the change of using the TORQUE_OS_WIN32. It makes it more consistant with other OS specific parts of the code.

I also noticed another bug. The code doesn't seem to handle empty sets being returned, and throws an error into the console log. While I'm not familiar with ODBC coding, this change produced the effect I wanted of removing the error message.

In DatabaseResult.cc:

int DatabaseResult::CheckSQLError(SQLRETURN ret, const char *location)
{
  if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO [b]|| ret == SQL_NO_DATA[/b])
	return 1;

Now the "error" is ignored and the empty set gets handled in the FirstRow function.

As I said, there's probably a "right" way to handle this, but I don't know it.
-Joe

#9 To get this to build on Linux

#ifdef TORQUE_OS_WIN32
// hopefully other platforms don't need this, windows needs it
// cause ms has things like SQLHWND and whatnot
#include <wtypes.h>
#include <objbase.h>
#include <tchar.h>
#endif

#ifdef TORQUE_OS_LINUX
#include <string.h> // for strcpy
#include <stdlib.h> // for atoi, atof etc..
#endif

with the unixODBC to get SQL_WCHAR and friends defined you'll need to stick an

#include

somewhere in the includes.

and for the libs
-lodbc
to the library link lines to get it all linked up and working.

#10 Off by one error on linux and unixODBC?
DatabaseResult.cc has the following.

ret = SQLDescribeCol(mStateH, i + 1, (SQLCHAR *)colName, sizeof(colName), &colNameLen, &colType, &maxColSize, &dummy2, &nullable);

Now I have a column 'UserName' and its a char column.

On an empty result SQLDescribeCol is returning a maxColSize of 0.

well, needless to say, mCols[i].data = new char[maxColSize] happily gives you a 0 sized buffer (ie a NULL pointer) which the SimBase just isn't happy about. My question is does the MS ODBC stuff do a similar thing? IE return the exact size of whatever is in the column or does it include space for the terminating char? In any case doing a mCols[i].data = new char[maxColSize + 1]; shouldn't hurt anything.

#11 Here is an update for Linux users.

DataBase Mod:

Created files:
~/Torque/lib/targets.TorqueODBC.mk

Contents
BEGIN FILE:
TorqueODBC.SOURCE=\
	TorqueODBC/DatabaseConnection.cc \
	TorqueODBC/DatabaseResult.cc \


TorqueODBC.SOURCE.OBJ=$(addprefix $(DIR.OBJ)/, $(TorqueODBC.SOURCE:.c=$O))
SOURCE.ALL += $(TorqueODBC.SOURCE)
targetsclean += TORQUEclean

DIR.LIST = $(addprefix $(DIR.OBJ)/, $(sort $(dir $(SOURCE.ALL))))

$(DIR.LIST): targets.TorqueODBC.mk

$(DIR.OBJ)/TorqueODBC$(EXT.LIB): CFLAGS+=--lodbc
$(DIR.OBJ)/TorqueODBC$(EXT.LIB): $(DIR.LIST) $(TorqueODBC.SOURCE.OBJ)
	$(DO.LINK.LIB)

lungifclean:
ifneq ($(wildcard DEBUG.*),)
	-$(RM)  DEBUG*
endif
ifneq ($(wildcard RELEASE.*),)
	-$(RM)  RELEASE*
endif
END FILE:

Files Added to project

~/Torque/lib/TorqueODBC/DatabaseConnection.h
~/Torque/lib/TorqueODBC/DatabaseResult.h
~/Torque/lib/TorqueODBC/DatabaseConnection.cc
~/Torque/lib/TorqueODBC/DatabaseResult.cc
~/Torque/lib/TorqueODBC/dbTest.cs

Files modified...
~/Torque/lib/Makefile

Modifications:
Line 67 inserted
include targets.TorqueODBC.mk
BEFORE
include ../mk/conf.common.mk

Files modified...
DatabaseConnection.h
DatabaseResult.h
Modifications:
#ifdef TORQUE_OS_WIN32
// hopefully other platforms don't need this, windows needs it
// cause ms has things like SQLHWND and whatnot
#include <wtypes.h>
#include <objbase.h>
#include <tchar.h>
#endif

#ifdef TORQUE_OS_LINUX
#include <string.h> // for strcpy
#include <stdlib.h> // for atoi, atof etc..
#endif



Files modified...
DatabaseResult.cc
Modifications:
Found:
int DatabaseResult::CheckSQLError(SQLRETURN ret, const char *location)
{
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
		return 1;

Replaced with:
int DatabaseResult::CheckSQLError(SQLRETURN ret, const char *location)
{
  if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO || ret == SQL_NO_DATA)
    return 1;

END Database Mod

The information above was gleaned from the previous poster and is my attempt to elaborate. I am currently unable to compile while I my system is updating. But the information should work pretty much as-is, if there are any glaring mistakes pls let me know.

*Update*
My system finished updating, so I decided to compile, based on the above and a Virgin 1.3 codebase.

I get a nice clean compile, only problem is the TorqueODBC does not appear to have compiled at all. It's not that there is an error, it's just that it doesn't seem to have bothered to show up to the ball game :( Any ideas?

*Update Again*
Ok I found the problem, seems I forgot to add the TorqueODBC to the default build. I also needed to make a couple changes to the make file.
I have updated and placed them above.
I haven't tested functionality, but it does appear to compile very cleanly.

#12 Following my own example above, and adding the script to the server/scripts directory, modifying game.cs to exec dbtest.cs I get the following in my console log...

Compiling starter.rpg/server/scripts/dbtest.cs...
Loading compiled script starter.rpg/server/scripts/dbtest.cs.
Warning: (console/consoleObject.cc @ 62) Couldn't find class rep for dynamic class: DatabaseConnection
starter.rpg/server/scripts/dbtest.cs (0): Unable to instantiate non-conobject class DatabaseConnection.
starter.rpg/server/scripts/dbtest.cs (0): Unable to find object: '0' attempting to call function 'connect'
Got error 2000 trying to connect to database

Any ideas what's going here?

#13 I have a working version of this for Linux and am working on porting my changes to Windows at which point I'll submit the whole shebang as a detailed resource with walkthrough/patch/zip.

Special thanks to Chunky_Ks and Dreamer.

#14 TGE 1.3.0 Lin/Win MySQL ODBC (Awaiting approval)

#15 Well it certainly has my approval! Congrats!

#16 Nice stuff, man! I've got a few changes to make it play nicer with OS X (which, IMHO, doesn't have such a great ODBC interface), add the ability to connect to DSNs, and fix some buffer overflow vulnerabilities. Would you like a patch?

#17 @David: I would love to have that patch so I could apply it to this resource TGE 1.3.0 Lin/Win MySQL ODBC.

#18 I'm having a strange issue, maybe someone knows the answer to this. I'm calling the fetch command like so:

%value = DB::Fetch( "password", "accounts", "account_name = \"" @ %username @ "\"");

That queries correctly, but if I then do an

echo (%value);

The last digit is always missing. For example if the password in the table is "mypassword" when I echo the %value it shows up as "mypasswor" without the final character. This is using the latest build of the ODBC resource (the one linked just above). I have checked and rechecked to make sure that the data is correct in the table, it's not corrupt, the query result form is somehow just losing that final character.

Anyone have any ideas what could be causing this problem?

#19 @ J.C. Smith: I believe that DB::Fetch is something from my resource that's based on this one. I'm going to see if I can replicate the issue you're having.

#20 Thanks a bunch. I've been looking over the code and having problems figuring out why exactly it's doing it. It's using the DB::Fetch from the util.cs file. All other queries are working fine for me (I also added some more helper functions such as Update to the util.cs file and they all work fine too) with the exception of this one, which has me completely stumped.