You are here: Download - Contributions - Gryphyn

NWNX^2 SQLServer 2.0a

Well it's here... a SQLServer plugin for NWNX^2

There has been some major rework of the back-end hence the separate plugin, and not an extra option for the existing ODBC plugin.

Requirements:
You need to have an installation of MS SQLServer. – 2005 or later This includes (and defaults to) SQLServer Express.

MS SQLServer 2008 available...

An addition to the SQLServer.ini is nativeclient=? (2005 or 2008) and the appropriate client will be used. (see configuration below)

Installation:
Copy nwnx_sqlserver.dll & nwnx_sqlserver.ini to the root folder of NWN1. This should also be the folder where nwnx2.exe & nwnx-module.dll are located.

Database: (*I’ll assume you know more if not using the defaults)
Open MS SQLServer Management Studio.
Connect to Server…this will normally be <localhost>\SQLEXPRESS
In the ‘Object Explorer’ panel
Right-Click on the Databases node, select [New Database…]
Type “nwn” (without the quotes) in the Database name field
(*you’ll see some filenames appear – ignore these they should be fine)
Select [OK] (you’re done)

Now that you have a database called “nwn”
Choose File->Open->File… (from the menu bar)
Locate the file SQLServer.sql and open it.
[Execute] this script (your database is now ready to use)

Configuration:
Accessing the database is as simple as setting the instance name in the configuration file (see below) NB: .\SQLEXPRESS indicates SQLServer-Express on the local host. nwnx_sqlserver.ini

[SQLSERVER]

;++

;Connection

; Required information for creating a connection string

; if trusted=true windows authentication is used,

; otherwise you need a valid username and password

;--

nativeclient=2005

instance=.\SQLEXPRESS

database=nwn

trusted=true

username=your_name

password=your_password

;++

;Logging ; 0=Nothing ; 1=Errors ; 2=Everything

;--

logging=1

Game Inclusion:Import the contents of SQLServer.erf into your module. The function SQLServer() should be added to your modules OnLoad() script to activate the plugin.

Includes Scripts:

nwnx_sqlserver: generic database access (using SQLServer Native Client) and and advanced access to ODBC API style commands.

1. Parameters are now available, add a parameter indicator ('?') to your SQL Statement and this will be replaced by the value of a SQLBind...() function.

SQLPrepare("{call [stored_proc] (?)}");

// or SQLPrepare("select value from table where key=?");

SQLBindString("MyKey");

2. Objects and other types can be returned in the same result set. No need for a seperate query to get the Object data.

int i = SQLGetInt(1);

object o = SQLGetObject(2);

string s = SQLGetString(3);

will all work from the same result-set (columns must be accessed in sequence)

3. SQLContext(), up to 4 seperate SQL queries can be active and any given time. This enables multiple 'open' result sets - SQLCommit one will not commit any other, Likewise a new SQLPrepare()/SQLExecute() will not close result-sets from another context.

4. SQLQuoteString() makes string safe for database use. (csv quoting)

5. SQLQuoteName() special to SQLServer where database object names have square brakets - eg SQLQuoteName("My Table") -> "[My Table]"

nwnx_sqlmode: a simple alternative to APS functions

nwnx_apsmode: backwards compatibility with existing APS functions.

nwnx_sqlserver includes

This includes file contains the core SQL functions that you need for the SQLServer plugin

// private

void SQLServer();

string NWNXRequest(string sRequest);

void SQLRequest(string sRequest, string sParameters);

string SQLAnswer(string sRequest);

string SQLTrim(string sString);

string SQLVectorToString(vector vVector);

vector SQLStringToVector(string sVector);

string SQLLocationToString(location lLocation);

location SQLStringToLocation(string sLocation);

int SQLBind(string sValue);

// public

void SQLMessage(string sMessage);

int SQLPrepare(string sSQLCommand);

int SQLBindString(string sValue);

int SQLBindInt(int iValue);

int SQLBindFloat(float fValue);

int SQLBindLocation(location lValue);

int SQLBindVector(vector vValue);

int SQLBindObject(object oValue);

int SQLExecute(string sSQLCommand = sqlVOID);

string SQLGetString(int iColumn);

int SQLGetInt(int iColumn);

float SQLGetFloat(int iColumn);

location SQLGetLocation(int iColumn);

vector SQLGetVector(int iColumn);

object SQLGetObject(int iColumn, object oOwner);

int SQLResult();

void SQLCommit();

string SQLQuoteString(string sString);

string SQLQuoteName(string sString);

int SQLRows(object oOwner);

int SQLColumns(object oOwner);

int SQLContext(int iContext = 0);

int SQLMore();

int SQLFetch(int iNext = 0);

 nwnx_sqlmode includes

This includes file contains the basic persistence functions that provide the equivalence of GetLocal…, SetLocal… & DeleteLocal… optimized for SQLServer

 // Get an Unique Identity for the object

// if a new player(object) joins the server (local RETURNVISIT=1)

int SQLIdentity(object oOwner);

// Get types

string SQLFetchString(object oOwner, string sKey);

int SQLFetchInt(object oOwner, string sKey);

float SQLFetchFloat(object oOwner, string sKey);

location SQLFetchLocation(object oOwner, string sKey);

vector SQLFetchVector(object oOwner, string sKey);

object SQLFetchObject(object oOwner, string sKey);

// Set types

void SQLStoreString(object oOwner, string sKey, string sValue);

void SQLStoreInt(object oOwner, string sKey, int iValue);

void SQLStoreFloat(object oOwner, string sKey, float fValue);

void SQLStoreLocation(object oOwner, string sKey, location lValue);

void SQLStoreVector(object oOwner, string sKey, vector vValue);

void SQLStoreObject(object oOwner, string sKey, object oObject);

// Delete types

void SQLClearString(object oOwner, string sKey);

void SQLClearInt(object oOwner, string sKey);

void SQLClearFloat(object oOwner, string sKey);

void SQLClearVector(object oOwner, string sKey);

void SQLClearLocation(object oOwner, string sKey);

void SQLClearObject(object oOwner, string sKey);

 nwnx_apsmode includes

This includes file contains the basic persistence functions that provide the equivalence of GetLocal…, SetLocal… & DeleteLocal… for backwards compatibility with other scripted systems… (and the same pwdata / pwobjdata tables)

void SQLExecDirect(string sSQL);

// Set types

void SetPersistentString(object oObject, string sVarName, string sValue, int iExpiration = 0, string sTable = "pwdata");

void SetPersistentInt(object oObject, string sVarName, int iValue, int iExpiration = 0,

 string sTable = "pwdata");

void SetPersistentFloat(object oObject, string sVarName, float fValue, int iExpiration = 0, string sTable = "pwdata");

void SetPersistentLocation(object oObject, string sVarName, location lLocation, int iExpiration = 0, string sTable = "pwdata");

void SetPersistentVector(object oObject, string sVarName, vector vVector, int iExpiration = 0, string sTable = "pwdata");

void SetPersistentObject(object oObject, string sVarName, object oObject2, int iExpiration = 0, string sTable = "pwobjdata");

// Get types

string GetPersistentString(object oObject, string sVarName, string sTable = "pwdata");

int GetPersistentInt(object oObject, string sVarName, string sTable = "pwdata");

float GetPersistentFloat(object oObject, string sVarName, string sTable = "pwdata");

location GetPersistentLocation(object oObject, string sVarname, string sTable = "pwdata");

vector GetPersistentVector(object oObject, string sVarName, string sTable = "pwdata");

object GetPersistentObject(object oObject, string sVarName, object oOwner = OBJECT_INVALID, string sTable = "pwobjdata");

// Delete types

void DeletePersistentVariable(object oObject, string sVarName, string sTable = "pwdata");

string SQLEncodeSpecialChars(string sString);

string SQLDecodeSpecialChars(string sString);

	SQLServer2008.rar	27 K
	nwnx_sqlserver2008.rar	74 K

NWNX^2 RealTime v1.2

Here's a little fresh retro... A RealTime plugin for NWNX2.

Rather the getting the server time via Database calls (SQL) here's a plugin that goes via the windows API (using wxWidgets wxDateTime class.)

Here's what's available

string GetServerLoadTime() -> yyyy-mm-dd hh:mm:ss (when Plugin loads)
string GetServerDate() -> yyyy-mm-dd
string GetServerTime() -> hh:mm:ss
string GetServerDateTime() -> yyyy-mm-dd hh:mm:ss
string GetServerDayName(int iFullname = TRUE) eg Monday or Mon
string GetServerMonthName(int iFullname = TRUE) eg January or Jan
int GetServerYear()
int GetServerMonth()
int GetServerDay()
int GetServerHour()
int GetServerMinute()
int GetServerSecond()
int GetServerUpDays()
int GetServerUpHours()
int GetServerUpMinutes()
int GetServerUpSeconds()
string GetServerUptime() -> hh:mm:ss

VS2008 - vcredist  (incase of 14001 errors)

Download 1.2

	nwnx_realtime.rar
	RealTime.rar

SQLServer 106.108.0.9 (CTP)

before you ask Community Technical Preview

Well here it is, sufficiently padded out to be of use.

Most of the documentation is pending the ability to get a dedicated web-page, but here's a bit to get you started.

xp_sqlserver.dll is a SQLServer^[1] plugin for NWNX4 1.08 (or later)

for the CTP things are simple, there is a xp_sqlserver.sql script to run on your database instance, it will create the database^[2] AND create the 'core' tables^[2] and procedures required by the SQLServer plugin. The only thing you may need to do is set the SQLServer instance name in xp_sqlserver.ini

It is different to what you have been use to, Investigate the demo mod, check through the SQL script, most of your answers will be there.

For the time being, post questions on the forums.

Cheers

Gryphyn

 

^[1]This includes the free version (SQLServer Express - the default)

^[2]If it doesn't already exist.

SQLServer_106.108.0.9__CTP_.rar

713 K

Spawn Plugin v122.109.1.1

Requires NWNX4 v1.09

The Spawn Plugin gives your server access to many command options.

You can run a Program, execute a command or even look at your environment settings.

SpawnEnvironment(string sEnvVariable)

SpawnProgram(string sProgramName)

SpawnCommand(string sCommand)

but to do this there are some 'tricks' that you need to master...

SpawnEscape(string sString)

- changes the '|' character to a '\' character so you can enter PATH strings

- eg SpawnEscape("c:|temp") = "c:\temp"

SpawnQuote(string sString)

- provides double quotes around a string (with double-ups)

- eg SpawnQuote("string") = "string" (literally, with quote characters)

It will take some time to master, especially getting the quotes right, but once you have it...

*30Aug07 - xp_spawn.dll has be recompiled, and no longer gives "Error 14001" (*Thanks Carter_DC*)

*13Apr09 - xp_spawn.dll recompiled with NWN2 v1.22 NWNX v1.09 VS2008 wxWidgets v2.8.10 (*ulord*)

Downloads

	nwnx_demo_spawn.rar	632 K
	spawn.rar	3.9 K
	xp_spawn.erf	1.1 K
	xp_spawn.dll	200 K

VS-SP1 (aka Error 14001)

If you get an "Error 14001", it's most likely due to the plugin being compiled under Service Pack 1 of Visual Studio.  <this was done prior to knowing it was an issue>

So here's a link that will get the missing bits... (vcredist_x86.exe)

http://www.microsoft.com/downloads/details.aspx?familyid=32bc1bee-a3f9-4c13-9c99-220b62a191ee&displaylang=en

 

NWNX4 SQLServer plugin

More a proof-of-concept, here is a working plug-in for SQLServer.

*This includes SQLServer Express - which is the default instance.

 

The major functionality is all covered Connect(), Execute(), Fetch() and GetData().

 

GetEscapeString() has been turned OFF as this was crashing the server

GetAffectedRows() is still there, but it's worth is dubious given multi-result sets.

 

A curious thing is that SQLServer, for all intents and purposes (and as I've implemented it) is an ODBC plugin - my work hardcodes the SQLServer driver. Some can add an .ini parameter to read in a driver name if they want access to different database engines.

 

I thought I'd get this out and give people a start with SQLServer, before the 1.05 improvements get incorporated. This is to give anyone who wants to work on a generic ODBC solution something to work with.

 

I won't be doing any further work on this code-stream, as I find the current implementation of the dbplugin restrictive when targeting a specific database. An advanced version will be available later that provides far more database intergration and improvements to usablility and performance.

 

I've used wxWidgets-2.8.3 so I'm providing the source (.h & .ccp) rather than the project. (I'll answer any queries in the forums)

 

 

Files: Code

	sqlserver.h	1.9 K
	sqlserver.cpp	16.1 K

Files: Plugin

	xp_sqlserver.ini
	xp_sqlserver.dll

NWNX Junior

As you all know NWNX4 is the best thing since sliced bread., why else would you be here?, but...

The core NWNX4 functionality assumes that you have some familiarity with the roles of the Database Administrator and Database Programmer. In particular you have to be familiar with SQL syntax and also the techniques for encasing this SQL code into NWN Scripting strings. As has been expressed in the forums numerous times many of you do not have this technical expertise.

NWNX Junior is here to help. It has taken the essential parts of NWNX4 and packaged them. Once set up all you need to know are the NWN scripting commands that NWNX Junior provides. The rest is done for you.

Installation

I'm assuming you've read all the How-To instructions for NWNX4. I have too, for without NWNX4 installed NWNX Junior will not work.  So here goes...

1. Install NWNX4.
2. Copy the file xp_pwdata.dll into the same folder where you installed NWNX4
3. Open the NWN2 Toolset (Create or Open your module)
4. File->Import...  nwnx_pwdata.erf
5. Add #include "nwnx_pwdata" into any script where you want to use NWNX Junior

What's with the "pwdata" you're asking. This is a bit of the core NWNX4 functionality. In essence it's the name of the default table. Just one of the many requirements of NWNX4 that have been wrapped up in the package.

Your first time

^{no not that one}

There is some administration stuff that happens when you execute NWNX Junior functions. A one-time action is to create a file called xp_pwdata.data in your NWNX4 folder. This file is your database, so deleting it will remove everything you have saved to date. On the plus-side, the file will be re-created the next time you run your module.

NWNX Scripting

I'll keep this simple... Each Local variable function has an equivalent Data

variable function.

1. SetLocal...()        -> SetData...( ... ,int iExpire=0)
2. GetLocal...()        -> GetData...()
3. DeleteLocal...()   -> DeleteData...()

For example: SetDataString(object oObj, string sKey, string sValue, int iExpire=0) looks and acts just like SetLocalString(object oObj, string sName, string sValue) except that the former will persist between module restarts, and an optional iExpire parameter. See the following section on using iExpire.

Note: Get/Set/Delete Object functions are not supported at this time; GetDataObject() returns OBJECT_INVALID

DeleteData(object oObject, string sKey)

A generic delete function for any for deleting a value. Specifying the type of the data being deleted is not required, even though each data type has it's own DeleteData...() function.

ExpireData(int iExpire=0)

As mentioned the SetData variable functions have an optional parameter iExpire. By default this is set to 0 (Zero) meaning that this data is to be kept forever. Sometime you might want to only keep information for a number of days, this is the value you set iExpire to. To delete this old data you have your script call the ExpireData() function.

Old data is removed if it has been given a iExpire date and that period has been exceeded since the last time the data was accessed (Get or Set). This is done with ExpireData(0). You can also override the iExpire values that you have set (in-case you forgot them, or want an extra cleanup) by setting the iExpire days, for example ExpireData(7) will expire any data marked with an expiry period of 7 days or more - even if the data was set to expire in a greater number of days - as well as data set to expire sooner (normally).

At any rate you shouldn't need to include this function in your scripts, this can be set to happen automatically by the Administration options

DataShutdown(int iExpire=1, int iShrink=1)

First off iExpire here is NOT a number of days, but I'm ahead of myself...

This function is used as a flag to signal for some administration tasks to happen when you shut down your module; these tasks won't happen if your module crashes. You set these tasks to run, or not, simple you executing the DataShutdown() function to 1 for the task in question. A value of 0 (zero) and the task will not happen.

The Tasks;

1. iExpire=1 This will execute ExpireData(0) when your module shuts down.
2. iShrink=1 This will compress your database when your module shuts down. That is it will recover any filespace that is unused due to data being deleted.

While not essential I would recommend that DataShutdown() be called during your modules OnLoad() event script. Just one more thing you don't have to worry about.

For the incorrigible

NWNX Junior uses an embedded SQLite3.3.10 database engine. While this may be similar to the sqlite plug-in already available, NWNX Junior focuses exclusively on the core database functions needed to manage the pwdata table solution. The solution that is used as the base for all NWNX4 database plug-ins.

In fact you can run the sqlite plug-in and NWNX Junior together in the same module. You may even want to do this as NWNX Junior provides some performance improvements over it's big brother. If you do attempt run both plug-ins make sure that they are using different databases. NWNX Junior requires exclusive access to it's own.

Scripting functions

void SetDataString(object oObject, string sKey, string sValue, int iExpire = 0);

void SetDataFloat(object oObject, string sKey, float fValue, int iExpire = 0);
void SetDataInteger(object oObject, string sKey, int iValue, int iExpire = 0);
void SetDataVector(object oObject, string sKey, vector vValue, int iExpire = 0);
void SetDataLocation(object oObject, string sKey, location lValue, int iExpire = 0);
void SetDataObject(object oObject, string sKey, object oValue, int iExpire = 0);
 

string GetDataString(object oObject, string sKey);
float GetDataFloat(object oObject, string sKey);
int GetDataInteger(object oObject, string sKey);
vector GetDataVector(object oObject, string sKey);
location GetDataLocation(object oObject, string sKey);
object GetDataObject(object oObject, string sKey);
 

void DeleteDataString(object oObject, string sKey);
void DeleteDataFloat(object oObject, string sKey);
void DeleteDataInteger(object oObject, string sKey);
void DeleteDataVector(object oObject, string sKey);
void DeleteDataLocation(object oObject, string sKey);
void DeleteDataObject(object oObject, string sKey);
void DeleteData(object oObject, string sKey);
 

void ExpireData(int iExpire = 0);
void DataShutdown(int iExpire = 1, int iShrink = 1);

Download:

	nwnx_pwdata.erf	12.0 K
	xp_pwdata.dll	508 K

NWNX Junior

NWNX_Junior.rar