SQLite integration error

#1 Try this resource by Dreamer, I've been using it in a few different projects now and it works.
www.garagegames.com/index.php?sec=mg&mod=resource&page=view&qid=10905 .

#2 Really? I copied and pasted all the code I could, downloaded the rest, and I'm getting errors in files that I didn't change! Could it be a problem with Visual Studio? That's the only explination I can come up with.

Edit -> I found a place that I forgot to comment, and fixed that. I still get errors though:

*Error 1 error LNK2019: unresolved external symbol _sqlite3_errmsg referenced in function "public: bool __thiscall SQLiteObject::OpenDatabase(char const *)" (?OpenDatabase@SQLiteObject@@QAE_NPBD@Z) SQLiteObject.obj 

*Error 2 error LNK2019: unresolved external symbol _sqlite3_open referenced in function "public: bool __thiscall SQLiteObject::OpenDatabase(char const *)" (?OpenDatabase@SQLiteObject@@QAE_NPBD@Z) SQLiteObject.obj 

*Error 3 error LNK2019: unresolved external symbol _sqlite3_exec referenced in function "public: int __thiscall SQLiteObject::ExecuteSQL(char const *)" (?ExecuteSQL@SQLiteObject@@QAEHPBD@Z) SQLiteObject.obj 

*Error 4 error LNK2019: unresolved external symbol _sqlite3_close referenced in function "public: void __thiscall SQLiteObject::CloseDatabase(void)" (?CloseDatabase@SQLiteObject@@QAEXXZ) SQLiteObject.obj 

*Error 5 error LNK2019: unresolved external symbol _sqlite3_free referenced in function "public: void __thiscall SQLiteObject::ClearErrorString(void)" (?ClearErrorString@SQLiteObject@@QAEXXZ) SQLiteObject.obj

#3 Figured it out: I needed to add SQLite3.lib to my project. It works now.

#4 No, it doesn't work! I can build the exe, and execute queries, but they only return 1 if succesful, and 0 if not! Has anyone figured this out?

#5 You need to look at the example queries. They are not accessed the way you are trying it. Here is an example:

function queryDB(%cmdQuery, %print)
{
   if(%print $= "" || %print == true)
      %print = true;
   else
      %print = false;

   // Start transaction
   %database = $database::current;
   %temp = $database::current;
   %query = $database::query::beginTrans;
   %result = %database.query(%query, 0, %temp@"_cmdLineTrans");
   if(%result == 0)
   {
      echo("Error: Could not intiate transaction on database:" SPC $database::current);
      return "";
   }
   %database.clearResult(%result);

   // actual query
   %query = %cmdQuery;
   %result = %database.query(%query, 0);
   if(%result == 0)
   {
      echo("Error: Could not query database:" SPC $database::current SPC "with query:" SPC %cmdQuery);
      %query = $database::query::rollbackTrans;
      %result = %database.query(%query, 0, %temp@"_cmdLineTrans");
      return "";
   }
   %colList = "";

   for(%count = 0; %count < %database.numColumns(%result); %count++)
   {
      %colList = trim(%colList SPC %database.getColumnName(%result,%count+1));
   }
   //echo(%colList);

   %allList = trim(%colList) @ "\n";
   while(!%database.endOfResult(%result))
   {
      %valList = "";
      for(%count = 0; %count < %database.numColumns(%result); %count++)
      {
         %valList = trim(%valList SPC %database.getColumn(%result,%count+1));
      }
      if(%valList !$= "")
         %allList = %allList @ trim(%valList) @ "\n";
      %database.nextRow(%result);
   }

   if(%print == true)
      echo(%allList);

   %database.clearResult(%result);

   // End transaction
   %query = $database::query::endTrans;
   %result = %database.query(%query, 0, %temp@"_object");
   if(%result == 0)
   {
      echo("Error: Could not end transaction on database:" SPC $database::current);
      %query = $database::query::rollbackTrans;
      %result = %database.query(%query, 0, %temp@"_cmdLineTrans");
      return "";
   }
   %database.clearResult(%result);

   return %allList;
}

$database::current is set to the current database object opened previously and stored in a list of databases. I recommend creating a list that has all the databases in it that are opened during the game. Then set a method onRemove on the list that will close all the databases. I use a function that opens all my databases and checks for a specific table with database information.

Also, I have found some nasty bugs that will crash the engine if you use the code above. Go to this thread to see what I did to fix them:
www.garagegames.com/index.php?sec=mg&mod=resource&page=view&qid=7281

#6 Thanks, I wish that was stated more clearly in the resources. I hope it works now...

#7 If you have trouble then send me an email. You can get it from my profile. You should also hop over to the sqlite homepage and look up the syntax for stuff. I go there quite often to figure out how to do stuff.

#8 Is there anyway to have this be a C++ ConsoleFunction? I'll try to figure it out, but if anyone has already done this...

#9 Nathan,

Here is the defines for the transactions:

$database::query::beginTrans = "BEGIN TRANSACTION ?";
$database::query::rollbackTrans = "ROLLBACK TRANSACTION ?";
$database::query::endTrans = "END TRANSACTION ?";

Go here to understand what they do:
www.sqlite.org/lang.html

I would suggest reading the source code for the sqlite Torque module: SQLiteObject.h and SQLiteObject.cc

It will explain how the queries work a bit better. Also, go find a program that can access an sqlite database and use it to test queries with. There are a lot of them out there. I use sqliteman but I think it is a Linux based program only.

With the code above I attempted to make an easy program to do queries from the command line. It works fairly well. It lets me do queries to check out the databases or to maintain them without having to open another program. If your looking for something easier I don't think it exists.

#10 Thanks for all the help, but the current problem isn't SQL, I know the syntax for that, the problem is that I'm not understanding the C++/C code for SQLite. I understand your TorqueScript code, but I'm having trouble making that into a C++ function.


Edit-> I think I might have found away to do this:

ConsoleMethod(SQLiteObject, sQuery, const char*, 1, 1, "(query) Queries and returns the result in the form of an array with the colum values seperated by ~. Supports up to 10 rows") {
	char result[9];
	int worked;
	int i;
	int n;
	sqlite_resultset* pResultSet;
	sqlite_resultrow* pRow;

	if (argc == 1)
		worked = object->ExecuteSQL(argv[1]);
	else
			return "0";

	if (worked != 0) {
		return "0";
	}

	pResultSet = object->GetResultSet(worked);
	n = 0;
	while (!object->EndOfResult(worked) || n > 9) {
		for(i = 0; i <= pResultSet->iNumCols; i++) {
				if (pResultSet)   {
					pRow = pResultSet->vRows[pResultSet->iCurrentRow];
					if (!pRow)
						return "-1";
					if (pRow->vColumnValues[i])
						result[n] = result[n] + "~" + pRow->vColumnValues[i];
					else
						i = object->getColumns();
					pResultSet->iCurrentRow++;
				}
				else
					return "-1";
		}
		n++;
	}

	return result;

}

#11 Ok, I've given up trying to do this in C++. So here's some TorqueScript that I'm thinking should work, but isn't. Anyone see some errors?

function SQLiteObject::rQuery(%query) {
	%result[9];
	%n = 0;
	
	%worked = sqlite.query(%query);
	
	if(%worked == 0) {
		return 0;
	}
	
	while (!sqlite.endOfResult(%worked)) {
		for (%i = 1; %i <= sqlite.numColums(%worked); %i++) {
			%result[%n] = %result[%n] + sqlite.getColumn(%worked, %i);
		}
		%n++;
	}
	
	return %result;
}

#12 Edit3 -> Problem solved, I'll post it in the resource page.