UDK - DLLBind: Embedded database (SQLite,MySQL) v3.1.0.2

[BlackHornet80]

For all those are intersted in my project. It is an embedded SQLite databases with capabilities to connect to an external MySQL database, realised via DLLBind.

Download last release: SQLProject3102.zip (version: 3.1.0.2)
Download sources: DLLBindProjects.zip (version: 3.1.0.2)
Sources are available under the NewBSD license.

Download older releases:
- SQLProject3101.zip (version: 3.1.0.1)
- SQLProject2010.zip (version: 2.0.1.0)
- SQLProject1051.zip (version: 1.0.5.1)

The zipped release packages contains a small example how to use the interface functions.

Core features:
- Common statement syntax from SQLite including prepared statements, savepoints, transactions, rollbacks (see http://www.sqlite.org for further informations)
- Multiple databases in memory
- Load database from file into memory
- Save database from memory to file
- Supported types on UScript side: int, float, string

Note: Please check out READ_ME_FIRST.txt before usage


CHANGELOG:
Version 3.1.0.1 to 3.1.0.2:
- Fixed MySQL Driver Bugs (thx to Dan Cowell)

Version 2.0.1.3 to 3.1.0.1:
- Added MySQL Driver, allows connection to a remote database (need to be open for remote access)

- Huge refactoring, older code won't compile with this version due to function signature changes
- Adding DataProviders to make lot of things easier
- Handling multiple result sets
- Added function wrappers in DB_DLLAPI allowing to have multiple database drivers instantiate (for example if you want to

connect to 2 different MySQL databases on 2 different servers)
- Various new functions for general table management:

dllimport final function int SQL_getTableCount();

dllimport final function SQL_getTableName(int aTableIdx, string aValue);

dllimport final function int SQL_getColumnCount(int aResultIdx);

dllimport final function SQL_getColumnInfo(int aResultIdx, int aColumnIdx, out string aColumnName, out int aColumnType, out int aColumnDetail);

- untested: 128-bit TEA file encryption
- MD5 Hash implementation

Version 2.0.1.2 to 2.0.1.3:
- Hotfix: Crash when restarting map PIE

Version 2.0.1.0 to 2.0.1.2:
- Fixed directoryDeletion using IO_DeleteDirectory

Version 2.0.0.0 to 2.0.1.0:
- Added additional function to get last inserted row of selected database:

int SQL_lastInsertID();

Version 1.0.5.1 to 2.0.0.0:
- Release under NewBSD license

Version 1.0.5.0 to 1.0.5.1:
- Added additional function to access query result values:

SQL_getIntVal(string aParamName, out int aValue);

SQL_getFloatVal(string aParamName, out float aValue);

SQL_getStringVal(string aParamName, out string aValue);

All these functions work on the column name of a result defined my the query expression (aParamName is case in-sensitive).

- Previous getter functions are still available but marked as deprecated, being removed in a future release.

Version 1.0.4.0 to 1.0.5.0:
- Added function for directory and file access:

bool IO_directoryExists(string aDirectoryPath);
Return true if given path is a valid directory.

bool IO_createDirectory(string aDirectoryPath);Return true if for given path a directory is created successfully.

bool IO_deleteDirectory(string aDirectoryPath, int aRecursive);
If aRecursiv is 1, all subdirectories and containing files will be removed to, regardless of read-only file attribute.
Return true if deletion of given path was successful.

bool IO_fileExists(string aFilePath);
Return true if given path is a valid file.

bool IO_deleteFile(string aFilePath);
Return true if given file was successfully removed.

Kind regards,
BlackHornet

[UT2007]

Handsome Work ! Now our MMO can take shape ......

[razvanab]

UDK April Build

C:\UDK\UDK-2010-04\Development\Src\SQLProject\Classes\SQLProject_E xample.uc(44) : Error, Bad or missing expression in 'If'

[BlackHornet80]

Copy/Paste devil ... and i thought i checked everything before -.-

should be mDLLAPI of course

/**
* Function to test several imported DLLBind functions of UDKProjectDLL.
* - Used to create a SQL Driver, which automatically creates a in-memory database.
* - Create a table and fill it with data.
* - Save database to disc
* - Query table content and print on console
* 
* After initalising the SQLDriver it would be possible to load a presaved database from disc, its content
* would be load into the current in-memory database.
* @see bool SQL_loadDatabase(string aFilename)
*/
function TestDatabase(){
	local string lName, lCountry;
	local int lID;
	local int lSecondDB;

	mDLLAPI.SQL_initSQLDriver(SQLDrv_SQLite);
	lSecondDB = mDLLAPI.SQL_createDatabase();
	if(lSecondDB > 0){
		if(mDLLAPI.SQL_selectDatabase(lSecondDB)){
			mDLLAPI.SQL_queryDatabase("CREATE TABLE Carproducer (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,Name VARCHAR(30) NOT NULL,Country VARCHAR(30))");
			mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Volkswagen', 'Germany');");
			mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Opel', 'Germany');");
			mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Ford', 'Germany');");
			mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('BMW', 'Germany');");
			mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Audi', 'Germany');");
			
			mDLLAPI.SQL_saveDatabase("test2ndDB.db");
		}
	}
	
	if(mDLLAPI.SQL_selectDatabase(0)){
		mDLLAPI.SQL_queryDatabase("CREATE TABLE Carproducer (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,Name VARCHAR(30) NOT NULL,Country VARCHAR(30))");
		mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Volkswagen', 'Germany');");
		mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Opel', 'Germany');");
		mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Ford', 'Germany');");
		mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('BMW', 'Germany');");
		mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Audi', 'Germany');");
		
		mDLLAPI.SQL_saveDatabase("testDB.db");
	}
	
	if(lSecondDB > 0){
		if(mDLLAPI.SQL_selectDatabase(lSecondDB)){
			mDLLAPI.SQL_queryDatabase("INSERT INTO Carproducer (Name, Country) VALUES ('Toyota', 'Japan');");
			
			mDLLAPI.SQL_saveDatabase("test2ndDB.db");
		}
	}
	
	mDLLAPI.SQL_selectDatabase(0);
	mDLLAPI.SQL_saveDatabase("testDB.db");
	if(mDLLAPI.SQL_queryDatabase("SELECT * FROM Carproducer;")){
		while(mDLLAPI.SQL_nextResult()){
			lName = class'SQLProject_Defines'.static.initString(30);
			lCountry = class'SQLProject_Defines'.static.initString(30);

			mDLLAPI.SQL_getValueInt(0, lID);
			mDLLAPI.SQL_getValueString(1, lName);
			mDLLAPI.SQL_getValueString(2, lCountry);
			`log("Carproducer: Id: "$lID$", Name: "$lName$", Country: "$lCountry);
		}
	}

	if(mDLLAPI.SQL_selectDatabase(lSecondDB)){
		if(mDLLAPI.SQL_queryDatabase("SELECT * FROM Carproducer;")){
			while(mDLLAPI.SQL_nextResult()){
				lName = class'SQLProject_Defines'.static.initString(30);
				lCountry = class'SQLProject_Defines'.static.initString(30);

				mDLLAPI.SQL_getValueInt(0, lID);
				mDLLAPI.SQL_getValueString(1, lName);
				mDLLAPI.SQL_getValueString(2, lCountry);
				`log("Carproducer: Id: "$lID$", Name: "$lName$", Country: "$lCountry);
			}
		}
	}
}

[razvanab]

yes it's working now

[Chyros]

Nice. Does it support calling of Stored Procedure? That would be a sweet feature in terms of performance. Also, a support for Date/Time data type.

[BlackHornet80]

Due to limitations of SQLite, stored procedures are NOT stored in database, only during runtime, as long as the database is opened => prepared statements!

see http://www.sqlite.org/datatype3.html for further informations about datatypes in SQLite

[Chyros]

I see. So SQLite is more of an single application's database rather than a client/server enterprise setup. Also, SQLite won't be able to handle MMOs with large-scale data efficiently.

[ThePriest909]

Thank! This is gon'a be very useful!!!

[Sir. Polaris]

Very nice, I had this slated for a few months out - you gone and saved this community a whole pile of trouble.

[Blade[UG]]

The performance of SQLite is rather surprising - I work with a group of people doing poker database stuff, and for databases of under a million records, it's usually outperforming mySQL and very close with PGSQL. But, none of us in that group are database experts, either.

[TheAgent]

So a savegame and load feature can be done with this? : D

[Blade[UG]]

Sure, if you design a way to save and restore your state as much as you need to

[BlackHornet80]

not an addition to the SQL part, but within the upcoming version 1.0.5.0 i will add some file/directory functions...

bool IO_directoryExists(string aDirectoryPath);
bool IO_createDirectory(string aDirectoryPath);
bool IO_deleteDirectory(string aDirectoryPath, int aRecursive);
bool IO_fileExists(string aFilePath);
bool IO_deleteFile(string aFilePath);

I'm currently setting up my database account/savegame management...so a game could have several accounts, and each account can have its own savegames, everything stored in database

[Chaosnet]

UDK April Build

C:\UDK\UDK-2010-04\Development\Src\SQLProject\Classes\SQLProject_E xample.uc(44) : Error, Bad or missing expression in 'If'

After correction I still have the same problem at line 44. How can I fix this error.