logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
NWNX2 and HCR2 Version 1.5

 
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules
View previous topic :: View next topic  
Author Message
Ponfyr



Joined: 18 Apr 2009
Posts: 20
PostPosted: Mon Apr 20, 2009 19:58    Post subject: NWNX2 and HCR2 Version 1.5 Reply with quote
Before I dive into this, I am wondering if anyone has written the h2_nwnx2_c include file mentioned in the h2_persistence_c file to replace h2_biowaredb_c?

Second question is: Are you willing to share? If not, how about some useful advice?


Thanks in advance!


Ponfyr
Back to top
View user's profile Send private message
ShadowDragon311007



Joined: 15 Jul 2009
Posts: 12
PostPosted: Sat Oct 17, 2009 11:40    Post subject: Reply with quote
Is this what your looking for, I did however rename the tables so I would not overwrite old ones...

Code:

/*
Filename:           hcr2_mysqldb_c
System:             core  (NWNX-MySQL database persistence)
Author:             Jim Canup (Urlord@WizardStorm.com)
Date Created:       Dec 27th, 2006.
Summary:
HCR2 core external database function user-configuration script.
This script should be consumed by h2_persistence_c as an include directive, if
the builder desires to use NWNX with a MySQL database as their means of campaign database storage.

-----------------
Revision: v1.02 by 0100010
Added code to InitializeDatabase to auto create needed tables if they don't exist.
Added Packing of Camapaign Database since object storage still uses that.
Adjusted the h2_DeleteExternalVariable to delete from campaign database as well
in case the item was an object.
Added h2_LogIn and h2_LogOut functions.

Revision: v1.03
Added include directive for hcr2_constants_i
Fixed SQL statement errors in SetExternal* functions.

Revision: v1.05
Added some logging.
Fixed Get & SetPlayerState error, when possessing companions
*/

//This file is the core include file from the NWNX application.
//You must have NWNX and MySQL 5.0 installed for these functions to work.
#include "nwnx_sql"
#include "nwnx_include"

//Date & Time functions
#include "wstg_date_time_inc"

//This file is the core include file for the HCR2 Constants
#include "hcr2_debug_i"

//A user defined constant that specifies the name of the database system being used.
//Examples: "Default Database",  "NWNX-MySQL",   etc..
const string H2_PERSISTENCE_SYSTEM_NAME = "NWNX-MySQL";

//A user defined constant that specifies the name of the campaign associated
//with external database variable storage. This value will be used whenever
//any of the h2_Get\SetExternal functions are called and the campaign name is not specified.
const string H2_DEFAULT_CAMPAIGN_NAME = "hcr2_pwdata";


int doesTableExist(string tablename)
{
   SQLExecDirect("SELECT count(*) FROM " + tablename);
   if (SQLFetch() != SQL_SUCCESS)
      return FALSE;   
   string val = SQLGetData(1);
   if (val == "0" || StringToInt(val) > 0)
      return TRUE;
   return FALSE;
}


void createMissingTable(string sTableName, string sSQL)
{
   if (!doesTableExist(sTableName))
   {
      SQLExecDirect(sSQL);
      if (!doesTableExist(sTableName))
      {
         WriteTimestampedLogEntry("ERROR: The MySQL plugin could not set up the table '" + sTableName + "'.  Please make sure that the mysql userid in xp_mysql.ini has sufficient permissions to create a new table.");   
         PrintString("CREATE " + sTableName + " failed.");
         return;
      }
   }
}


//Begin generic external DB functions.
void h2_InitializeDatabase()
{
   //TODO: remove this when NWNX4 supports object saving.
   PackCampaignDatabase(H2_DEFAULT_CAMPAIGN_NAME);
   
   //Check if NWNX is installed.
   if (!NWNXInstalled())
   {
      WriteTimestampedLogEntry("ERROR: NWNX4 has not been installed.  Before proceeding with mysql installation, you'll need to download and install NWNX4.");
      PrintString("No NWNX4");
      return;
   }
   string plugin = NWNXGetPluginSubClass("SQL");
   WriteTimestampedLogEntry("Plugin class: " + plugin);
   if (GetStringUpperCase(plugin) != "MYSQL")
   {
      WriteTimestampedLogEntry("ERROR: The MySQL plugin is not available.  If you have both xp_mysql.dll and xp_sqlite.dll in your NWN2 folder, please remove xp_sqlite.dll. \n\nIf you don't have xp_mysql.dll in your NWN2 install directory, please copy it there.");
      PrintString("Wrong Plugin Subclass");
      return;
   }
      
   SQLExecDirect("SHOW DATABASES");
   if (SQLFetch() != SQL_SUCCESS)
   {
      WriteTimestampedLogEntry("ERROR: The MySQL plugin is not available.  ");
      PrintString("SHOW DATABASES failed.");
      return;
   }   
      
   //Create default tables if they dont exist
   //Create table pwdata
   string sSQL = "CREATE TABLE hcr2_pwdata (" +
           "player varchar(64) NOT NULL default '~'," +
           "tag varchar(64) NOT NULL default '~'," +
           "name varchar(64) NOT NULL default '~'," +
           "val text," +
           "expire int(11) default NULL," +
           "last timestamp NOT NULL default CURRENT_TIMESTAMP," +
           "PRIMARY KEY  (player,tag,name)" +
           ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";
   createMissingTable("hcr2_pwdata", sSQL);      
   
   //Create table pwobjdata
   sSQL =    "CREATE TABLE hcr2_pwobjdata (" +
           "player varchar(64) NOT NULL default '~'," +
           "tag varchar(64) NOT NULL default '~'," +
           "name varchar(64) NOT NULL default '~'," +
           "val text," +
           "expire int(11) default NULL," +
           "last timestamp NOT NULL default CURRENT_TIMESTAMP," +
           "PRIMARY KEY  (player,tag,name)" +
           ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";
   createMissingTable("hcr2_pwobjdata", sSQL);   
   
   //Create table cdkeys
   sSQL =    "CREATE TABLE  hcr2_players(" +
           "CDKey varchar(20) NOT NULL default ''," +
           "Player varchar(64) NOT NULL default ''," +
           "IPAddress varchar(32) NOT NULL default ''," +
           "Ban tinyint(1) NOT NULL default '0'," +
           "TimeStamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"  +
           "PRIMARY KEY  (Player,CDKey,IPAddress)" +
         ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";
   createMissingTable("HCR2_PLAYERS", sSQL);
            
   //Create table pc
   sSQL =    "CREATE TABLE  hcr2_characters(" +
           "PCID int(10) unsigned NOT NULL auto_increment," +
           "FirstName char(64) NOT NULL default ''," +
           "LastName char(64) NOT NULL default ''," +
           "Player char(64) NOT NULL default ''," +
           "CDKey char(20) NOT NULL default ''," +
           "DateCreated datetime NOT NULL default '0000-00-00 00:00:00'," +
           "Online tinyint(1) NOT NULL default '0'," +
           "DM tinyint(1) NOT NULL default '0'," +
           "Retired tinyint(1) NOT NULL default '0'," +
           "TimeStamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP," +
           "PRIMARY KEY  (CDKey,Player,LastName,FirstName)," +
           "KEY `ndx_pcid` (PCID)" +
         ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";
   createMissingTable("HCR2_CHARACTERS", sSQL);
   
   //Create table pcstate
   sSQL =    "CREATE TABLE  hcr2_pcstate (" +
           "PCID int(10) unsigned NOT NULL default '0'," +
           "Location varchar(255) NOT NULL default ''," +
           "PlayerState int(11) NOT NULL default '0'," +           
           "TimeStamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP," +
           "PRIMARY KEY  (PCID)" +
         ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";
   createMissingTable("hcr2_pcstate", sSQL);
   
   //Create table pcdata
   sSQL =    "CREATE TABLE  hcr2_pcdata (" +
           "PCID int(10) NOT NULL default '0'," +
           "VarName varchar(64) NOT NULL default ''," +
           "Value varchar(255) NOT NULL default ''," +
           "TimeStamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP," +
           "PRIMARY KEY  (VarName,PCID)" +
         ") ENGINE=MyISAM DEFAULT CHARSET=latin1;";
   createMissingTable("hcr2_pcdata", sSQL);         
}

string GetExternalPCID(object oPC)
{
   string sPCID = GetLocalString(oPC, "H2_SQL_PCID");
   
   if(sPCID == "")
   {
      string sFirstName    = GetFirstName(oPC);
      string sLastName   = GetLastName(oPC);
      string sPlayer      = GetPCPlayerName(oPC);
      string sCDKey      = GetPCPublicCDKey(oPC);
      string sSQL;
      
      sSQL = "SELECT PCID FROM HCR2_CHARACTERS WHERE FirstName='"+sFirstName+"' AND LastName='"+sLastName+"' AND Player='"+sPlayer+"' AND CDKey='"+sCDKey+"'";
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
         sPCID = SQLGetData(1);         
      SetLocalString(oPC, "H2_SQL_PCID", sPCID);
   }   
   return sPCID;
}

float h2_GetExternalFloat(string sVarOrColumnName, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   float ReturnValue;
   
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "SELECT Value from hcr2_pcdata WHERE VarName='"+sVarOrColumnName+"' AND PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
         ReturnValue = StringToFloat(SQLGetData(1));
      else
         WriteTimestampedLogEntry("Error executing: " + sSQL);
   }
   else
   {
      ReturnValue = GetPersistentFloat(oPlayer, sVarOrColumnName);
   }
    return ReturnValue;
}

int h2_GetExternalInt(string sVarOrColumnName, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   int   ReturnValue;
   
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "SELECT Value from hcr2_pcdata WHERE VarName='"+sVarOrColumnName+"' AND PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
         ReturnValue = StringToInt(SQLGetData(1));
      else
         WriteTimestampedLogEntry("Error executing: " + sSQL);   
   }
   else
   {
      ReturnValue = GetPersistentInt(oPlayer, sVarOrColumnName);
   }
    return ReturnValue;
}

location h2_GetExternalLocation(string sVarOrColumnName, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   location ReturnValue;
   
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "SELECT Value from hcr2_pcdata WHERE VarName='"+sVarOrColumnName+"' AND PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
         ReturnValue = SQLStringToLocation(SQLGetData(1));
      else
         WriteTimestampedLogEntry("Error executing: " + sSQL);   
   }
   else
   {
      ReturnValue = GetPersistentLocation(oPlayer, sVarOrColumnName);
   }
    return ReturnValue;
}

string h2_GetExternalString(string sVarOrColumnName, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   string ReturnValue;
   
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "SELECT Value from hcr2_pcdata WHERE VarName='"+sVarOrColumnName+"' AND PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
         ReturnValue = SQLGetData(1);      
      else
         WriteTimestampedLogEntry("Error executing: " + sSQL);
   }
   else
   {
      ReturnValue = GetPersistentString(oPlayer, sVarOrColumnName);
   }
    return ReturnValue;
}

vector h2_GetExternalVector(string sVarOrColumnName, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   vector ReturnValue;
   
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "SELECT Value from hcr2_pcdata WHERE VarName='"+sVarOrColumnName+"' AND PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
         ReturnValue = SQLStringToVector(SQLGetData(1));   
      else
         WriteTimestampedLogEntry("Error executing: " + sSQL);
   }
   else
   {
      ReturnValue = GetPersistentVector(oPlayer, sVarOrColumnName);
   }
    return ReturnValue;
}

object h2_GetExternalObject(string sVarOrColumnName, location locLocation, object oOwner = OBJECT_INVALID, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
    return RetrieveCampaignObject(sCampaignOrTableName, sVarOrColumnName, locLocation, oOwner, oPlayer);
}

void h2_DeleteExternalVariable(string sVarOrColumnName, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{   
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "DELETE FROM hcr2_pcdata WHERE VarName='"+sVarOrColumnName+"' AND PCID="+sPCID;
      SQLExecDirect(sSQL);
   }
   else
   {
      DeletePersistentVariable(oPlayer, sVarOrColumnName);
   }
   //TODO: remove this when NWNX4 supports object saving.
   DeleteCampaignVariable(sCampaignOrTableName, sVarOrColumnName, oPlayer);
}

void h2_SetExternalFloat(string sVarOrColumnName, float flFloat, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "REPLACE INTO hcr2_pcdata (PCID,VarName,Value) VALUES("+sPCID+",'"+sVarOrColumnName+"','"+FloatToString(flFloat)+"')";
      SQLExecDirect(sSQL);
   }
   else
   {
      SetPersistentFloat(oPlayer, sVarOrColumnName, flFloat);
   }
}

void h2_SetExternalInt(string sVarOrColumnName, int nInt, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "REPLACE INTO hcr2_pcdata (PCID,VarName,Value) VALUES("+sPCID+",'"+sVarOrColumnName+"','"+IntToString(nInt)+"')";
      SQLExecDirect(sSQL);
   }
   else
   {
      SetPersistentInt(oPlayer, sVarOrColumnName, nInt);
   }
}

void h2_SetExternalLocation(string sVarOrColumnName, location locLocation, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "REPLACE INTO hcr2_pcdata (PCID,VarName,Value) VALUES("+sPCID+",'"+sVarOrColumnName+"','"+SQLLocationToString(locLocation)+"')";
      SQLExecDirect(sSQL);
   }
   else
   {
      SetPersistentLocation(oPlayer, sVarOrColumnName, locLocation);
   }
}

void h2_SetExternalString(string sVarOrColumnName, string sString, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "REPLACE INTO hcr2_pcdata (PCID,VarName,Value) VALUES("+sPCID+",'"+sVarOrColumnName+"','"+sString+"')";
      SQLExecDirect(sSQL);
   }
   else
   {
      SetPersistentString(oPlayer, sVarOrColumnName, sString);
   }
}

void h2_SetExternalVector(string sVarOrColumnName, vector vVector, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   if(oPlayer != OBJECT_INVALID)
   {
      string   sPCID = GetExternalPCID(oPlayer);
      string    sSQL;
      sSQL = "REPLACE INTO hcr2_pcdata (PCID,VarName,Value) VALUES("+sPCID+",'"+sVarOrColumnName+"','"+SQLVectorToString(vVector)+"')";
      SQLExecDirect(sSQL);
   }
   else
   {
      SetPersistentVector(oPlayer, sVarOrColumnName, vVector);
   }
}

int h2_SetExternalObject(string sVarOrColumnName, object oObject, object oPlayer=OBJECT_INVALID, string sCampaignOrTableName=H2_DEFAULT_CAMPAIGN_NAME, string sRowKey = "", string sKeyValue = "")
{
   if (GetStringLength(sVarOrColumnName) > 32)
      WriteTimestampedLogEntry("Warning: External variable name " + sVarOrColumnName + "will be truncated to 32 characters.");   

    int bSuccess = StoreCampaignObject(sCampaignOrTableName, sVarOrColumnName, oObject, oPlayer);
    if (!bSuccess)
        WriteTimestampedLogEntry("StoreCampaignObject failed on object " + GetResRef(oObject) + " to variable " + sVarOrColumnName);
    return bSuccess;
}
//end generic external DB functions

//Begin Custom defineable core function constants
//If you want to seperate the HCR2 core PW data into multiple DB files
//for this special information then change the value of the following constants.
//By default they are set equal to the existing default campaign name
const string H2_SERVER_DATE_INFO       = H2_DEFAULT_CAMPAIGN_NAME;
const string H2_BANNED_LIST_INFO       = H2_DEFAULT_CAMPAIGN_NAME;

//These constant merely represent variable names, you can alter their values if you wish but don't need to.
const string H2_PLAYER_STATE          = "H2_PLAYER_STATE";
const string H2_SAVED_PC_LOCATION       = "H2_SAVED_PC_LOCATION";
const string H2_REGISTERED_CHARS       = "_H2_REGISTERED_CHARS";
const string H2_CURRENT_DATE_TIME      = "H2_CURRENT_DATE_TIME";
const int     H2_BAN_BY_CDKEY           = 0;
const int     H2_BAN_BY_PLAYER_NAME       = 1;
const int     H2_BAN_BY_IPADDRESS       = 2;
const string H2_BANNED_CD_KEY          = "H2_BANNED_CD_KEY_";
const string H2_BANNED_NAME          = "H2_BANNED_NAME_";
const string H2_BANNED_IPADDRESS       = "H2_BANNED_IPADDRESS_";

//Begin core custom definable specialty functions
int h2_GetPlayerState(object oPC)
{
   if (GetIsOwnedByPlayer(oPC))
   {
      int ReturnValue;   
      string   sPCID = GetExternalPCID(oPC);
      if (sPCID == "")
         return 0;   
      string sSQL = "SELECT PlayerState from hcr2_pcstate WHERE PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
         ReturnValue = StringToInt(SQLGetData(1));   
      else   
         WriteTimestampedLogEntry("Error executing: " + sSQL);            
      return ReturnValue;   
   }
   string sCompanionName = GetTag(oPC) + "_" + GetName(oPC);
   return h2_GetModLocalInt(H2_PLAYER_STATE + sCompanionName);
}

void h2_SetPlayerState(object oPC, int nPlayerState)
{
   if (!GetIsObjectValid(oPC))
      return;      
   if (GetIsOwnedByPlayer(oPC))
   {
      string   sPCID = GetExternalPCID(oPC);
      string    sSQL;
      int      nCount;   
      
      sSQL = "SELECT count(PCID) from hcr2_pcstate WHERE PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
      {
         nCount = StringToInt(SQLGetData(1));         
         sSQL = "UPDATE hcr2_pcstate SET PlayerState="+IntToString(nPlayerState)+" WHERE PCID="+sPCID;   
         if(nCount == 0)
            sSQL = "INSERT INTO hcr2_pcstate (PCID,PlayerState) VALUES("+sPCID+","+IntToString(nPlayerState)+")";
         SQLExecDirect(sSQL);
         h2_LogPlayerState( oPC, nPlayerState);
      }   
      else
         WriteTimestampedLogEntry("Error executing: " + sSQL);
      
      if(nPlayerState == H2_PLAYER_STATE_RETIRED)
      {
         sSQL = "UPDATE HCR2_CHARACTERS SET Retired=1 WHERE PCID="+sPCID;
         SQLExecDirect(sSQL);      
         sSQL = "DELETE FROM hcr2_pcstate WHERE PCID="+sPCID;
         SQLExecDirect(sSQL);      
         sSQL = "DELETE FROM hcr2_pcdata WHERE PCID="+sPCID;
         SQLExecDirect(sSQL);
      }
      return;
   }
   string sCompanionName = GetTag(oPC) + "_" + GetName(oPC);
   h2_SetModLocalInt(H2_PLAYER_STATE + sCompanionName, nPlayerState);
}

location h2_GetSavedPCLocation(object oPC)
{
   location ReturnValue;   
   string   sPCID = GetExternalPCID(oPC);
   string    sSQL;
   sSQL = "SELECT Location from hcr2_pcstate WHERE PCID="+sPCID;
   SQLExecDirect(sSQL);
   if(SQLFetch() == SQL_SUCCESS)
      ReturnValue = SQLStringToLocation(SQLGetData(1));   
   else
      WriteTimestampedLogEntry("Error executing: " + sSQL);
      
   return ReturnValue;   
}

void h2_SavePCLocation(object oPC)
{
   if (!GetIsObjectValid(oPC) || !GetIsPC(oPC))
      return;
   if (GetIsObjectValid(GetArea(oPC)))
   {
      location lLocation    = GetLocation(oPC);
      string    sPCID       = GetExternalPCID(oPC);
      string    sLocation    = SQLLocationToString(lLocation);
      string   sSQL;
      int       nCount;   
      
      sSQL = "SELECT count(PCID) from hcr2_pcstate WHERE PCID="+sPCID;
      SQLExecDirect(sSQL);
      if(SQLFetch() == SQL_SUCCESS)
      {
         nCount = StringToInt(SQLGetData(1));
         sSQL = "UPDATE hcr2_pcstate SET Location='"+sLocation+"' WHERE PCID="+sPCID;
         if(nCount == 0)
            sSQL = "INSERT INTO hcr2_pcstate (PCID,Location) VALUES("+sPCID+",'"+sLocation+"')";
         SQLExecDirect(sSQL);
      }
      else
         WriteTimestampedLogEntry("Error executing: " + sSQL);   
   }
   else
      DelayCommand(0.1, h2_SavePCLocation(oPC));      
}

int h2_GetRegisteredCharCount(object oPC)
{
   string    sCDKey = GetPCPublicCDKey(oPC);
   string    sSQL;
   int      nCount = -1;
   
   sSQL = "SELECT count(PCID) from HCR2_CHARACTERS WHERE CDKey='"+sCDKey+"' AND Retired=0";
   SQLExecDirect(sSQL);
   if(SQLFetch() == SQL_SUCCESS)
      nCount = StringToInt(SQLGetData(1));   
   else
      WriteTimestampedLogEntry("Error executing: " + sSQL);      
   return nCount;
}

void h2_SetRegisteredCharCount(object oPC, int nRegisteredChars)
{
   //The reason this function does nothing is because
   //the act of logging in always creates a record entry for a character in
   //the CHARACTERS table if one did not yet exist.
   //h2_GetRegisteredCharCount is always accurate
   //because it simply counts the number of non-retired character records
   //for the player in the CHARACTERS table.
}

void h2_SaveCurrentCalendar(int nYear = -1, int nMonth = -1, int nDay = -1, int nHour = -1, int nMinute = -1)
{
   object   oMod        = GetModule();
   float     fTimeFactor = 60/(HoursToSeconds(1)/60);
   
   if (nYear == -1)
      nYear = GetCalendarYear();
   if (nMonth == -1)
      nMonth = GetCalendarMonth();
   if (nDay == -1)
      nDay = GetCalendarDay();
   if (nHour == -1)
      nHour = GetTimeHour();
   if (nMinute == -1)
   {
      nMinute  = GetTimeMinute();
       nMinute  = FloatToInt(nMinute * fTimeFactor);
   }                     

   string sDateTime = GetDateTimeString(nYear,nMonth,nDay,nHour,nMinute,0);
   SetPersistentString(oMod, H2_CURRENT_DATE_TIME, sDateTime);
}

void h2_RestoreSavedCalendar()
{
   object    oMod       = GetModule();
   float     fTimeFactor = 60/(HoursToSeconds(1)/60);
   string    sDateTime    = GetPersistentString(oMod, H2_CURRENT_DATE_TIME);
    int    iCurYear    = ParseDateTimeString(sDateTime, "year");
    int    iCurMonth    = ParseDateTimeString(sDateTime, "month");
    int    iCurDay    = ParseDateTimeString(sDateTime, "day");
    int    iCurHour    = ParseDateTimeString(sDateTime, "hour");
   int    iCurMin    = FloatToInt(ParseDateTimeString(sDateTime, "minute") / fTimeFactor);
   
    if(sDateTime != "")
   {
        SetTime(iCurHour, iCurMin, 0, 0);
        SetCalendar(iCurYear, iCurMonth, iCurDay);
    }
}

void h2_BanPC(object oPCToBan, object oBannedBy, int nBanMethod = H2_BAN_BY_CDKEY)
{
   string sMessage    = GetPCPlayerName(oPCToBan) + "_" + GetName(oPCToBan) + " banned by: " + GetPCPlayerName(oBannedBy) + "_" + GetName(oBannedBy);
   string sPlayer      = GetPCPlayerName(oPCToBan);
   string sCDKey      = GetPCPublicCDKey(oPCToBan);
   string sIPAddress   = GetPCIPAddress(oPCToBan);
   string sSQL;
   
   switch (nBanMethod)
   {
      case H2_BAN_BY_CDKEY: 
         sSQL = "UPDATE HCR2_PLAYERS SET Ban=1 WHERE CDKey='"+sCDKey+"'";
         sMessage = "CDKey Ban: " + sMessage;
         break;
      case H2_BAN_BY_PLAYER_NAME:
         sSQL = "UPDATE HCR2_PLAYERS SET Ban=1 WHERE Player='"+sPlayer+"'";
         sMessage = "Player Ban: " + sMessage;
         break;
      case H2_BAN_BY_IPADDRESS:   
         sSQL = "UPDATE HCR2_PLAYERS SET Ban=1 WHERE IPAddress='"+sIPAddress+"'";
         sMessage = "IPAddress Ban: " + sMessage;
         break;
   }
   SendMessageToAllDMs(sMessage);
    WriteTimestampedLogEntry(sMessage);
      BootPC(oPCToBan);
   SQLExecDirect(sSQL);   
}

int h2_GetIsBanned(object oPC)
{
   string sPlayer      = GetPCPlayerName(oPC);
   string sCDKey      = GetPCPublicCDKey(oPC);
   string sIPAddress   = GetPCIPAddress(oPC);
   string sSQL;
   int nBan = FALSE;

   sSQL = "SELECT max(Ban) FROM HCR2_PLAYERS WHERE CDKey='"+sCDKey+"' OR Player='"+sPlayer+"' OR IPAddress='"+sIPAddress+"'";
   SQLExecDirect(sSQL);
   if(SQLFetch() == SQL_SUCCESS)
      nBan = StringToInt(SQLGetData(1));   
   else
      WriteTimestampedLogEntry("Error executing: " + sSQL);   
   
   if (nBan > 0)
      nBan = TRUE;
   
   return nBan;      
}

//Updates appropriate persistent data on PC log-in.
void h2_LogInPC(object oPC)
{
   string    sCDKey      = GetPCPublicCDKey(oPC);
   string    sPlayer      = GetPCPlayerName(oPC);
   string    sIPAddress   = GetPCIPAddress(oPC);
   string    sFirstName    = GetFirstName(oPC);
   string    sLastName   = GetLastName(oPC);
   string    sIsDM      = IntToString(GetIsDM(oPC));   
   string    sSQL, sPCID;
   int      nCount;   
   
   sSQL = "SELECT count(CDKey) FROM HCR2_PLAYERS WHERE CDKey='"+sCDKey+"' AND Player='"+sPlayer+"' AND IPAddress='"+sIPAddress+"'";
   SQLExecDirect(sSQL);
   if(SQLFetch() == SQL_SUCCESS)
   {
      nCount = StringToInt(SQLGetData(1));
      if(nCount == 0)
      {
         sSQL = "INSERT INTO HCR2_PLAYERS (CDKey,Player,IPAddress) VALUES('"+sCDKey+"','"+sPlayer+"','"+sIPAddress+"')";
         SQLExecDirect(sSQL);
      }   
   }   
   else
   {
      WriteTimestampedLogEntry("Error executing: " + sSQL);   
      return;
   }
   
   sSQL = "SELECT count(PCID) FROM HCR2_CHARACTERS WHERE FirstName='"+sFirstName+"' AND LastName='"+sLastName+"' AND Player='"+sPlayer+"' AND CDKey='"+sCDKey+"'";
   SQLExecDirect(sSQL);
   if(SQLFetch() == SQL_SUCCESS)
   {
      nCount = StringToInt(SQLGetData(1));
      if(nCount == 0)
      {
         sSQL = "INSERT INTO HCR2_CHARACTERS (FirstName,LastName,CDKey,Player,DM,Online,DateCreated) VALUES('"+sFirstName+"','"+sLastName+"','"+sCDKey+"','"+sPlayer+"',"+sIsDM+",1,CURRENT_TIMESTAMP)";                  
         SQLExecDirect(sSQL);
         h2_SetPlayerState(oPC, H2_PLAYER_STATE_ALIVE);         
      }
      else
      {
         sSQL = "UPDATE HCR2_CHARACTERS SET Online=1 WHERE FirstName='"+sFirstName+"' AND LastName='"+sLastName+"' AND Player='"+sPlayer+"' AND CDKey='"+sCDKey+"'";
         SQLExecDirect(sSQL);
      }
      
   }
   else
      WriteTimestampedLogEntry("Error executing: " + sSQL);   
}

//Updates appropriate persistent data on PC log-out.
void h2_LogOutPC(object oPC)
{
   string    sCDKey      = GetLocalString(oPC, H2_PC_CD_KEY);
   string    sPlayer      = GetLocalString(oPC, H2_PC_PLAYER_NAME);
   string    sFirstName    = GetFirstName(oPC);
   string    sLastName   = GetLastName(oPC);
   string    sSQL = "UPDATE HCR2_CHARACTERS SET Online=0 WHERE FirstName='"+sFirstName+"' AND LastName='"+sLastName+"' AND Player='"+sPlayer+"' AND CDKey='"+sCDKey+"'";   
   SQLExecDirect(sSQL);      
}


Hope that helps, now all I have to do is figure out why characters in players names like *&^$(!._' screw up the database for some reason...
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431
PostPosted: Sat Oct 17, 2009 22:53    Post subject: Reply with quote
ShadowDragon311007 wrote:

Hope that helps, now all I have to do is figure out why characters in players names like *&^$(!._' screw up the database for some reason...


Don't forget you still NEED to SQLEncodeSpecialChars() & SQLDecodeSpecialChars() ALL of the strings into & out-of the database. It'll be the single quote killing from *&^$(!._'

*well maybe not all strings, just those where the source of the value is not trusted (ie player input).
Back to top
View user's profile Send private message
ShadowDragon311007



Joined: 15 Jul 2009
Posts: 12
PostPosted: Sun Oct 18, 2009 8:47    Post subject: Reply with quote
So to make a uneducated guess binary's code wrapped into the death system here.

Code:

string h2_StripStringOfIllegalCharacters(string inputString)
{
   string IllegalCharacters = " `~!@#$%^&*()-=+[]{};':<>?,.|/\\";
   int i = 0;
   for (i = 0; i < GetStringLength(IllegalCharacters); i++)
   {
      string illegalChar = GetSubString(IllegalCharacters, i, 1);
      int index = FindSubString(inputString, illegalChar);
      while (index != -1) //Remove the illegal char
      {
         inputString = GetStringLeft(inputString, index) + GetStringRight(inputString, GetStringLength(inputString) - index -1);
         index = FindSubString(inputString, illegalChar);
      }
   }
   if (GetStringLength(inputString) > 32)
      inputString = GetStringLeft(inputString, 32);
   return inputString;
}


then the function call in his other code is causing a issue with nwnx_sql's code to SQLEncodeSpecialChars() & SQLDecodeSpecialChars, already? perhaps?

because this function
sUniquePCID = h2_StripStringOfIllegalCharacters(sUniquePCID);

is called quite a few times in different places...
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431
PostPosted: Sun Oct 18, 2009 9:50    Post subject: Reply with quote
I wouldn't be using h2_StripStringOfIllegalCharacters()

It's a one-way process and can cause conflicts be creating duplicate entries when it shouldn't. "PC-One" becomes equal to "PCOne" and there's no way to get back to "PC-One".

With SQLEncode the special characters are 'substituted' rather than being 'eliminated' so it's possible to recover them later.

It's one of things that will work, but it's a real pain to track down an issue like a missing hyphen.
Back to top
View user's profile Send private message
ShadowDragon311007



Joined: 15 Jul 2009
Posts: 12
PostPosted: Sun Oct 18, 2009 12:47    Post subject: Reply with quote
Yep, that fixed the main issue everything works now... however is there a way to script out the database code to allow players to have characters in there name like ' " and not have any read write issues? I'm not that big on database coding so just wondering how I would go about getting it to work. If it would help I can dump all our database code on you here...haha
Back to top
View user's profile Send private message
ShadowDragon311007



Joined: 15 Jul 2009
Posts: 12
PostPosted: Sun Oct 18, 2009 13:00    Post subject: Reply with quote
Oh wait, could binary or me, whoever just add a wrapper function to SQLDecodeSpecalChar() to all the code that calls data from the database?

*drools at his noob-ness of database functions*
Back to top
View user's profile Send private message
Fireboar



Joined: 17 Feb 2008
Posts: 323
PostPosted: Sun Oct 18, 2009 14:03    Post subject: Reply with quote
That's my approach: call SQLDecodeSpecialChars as part of SQLFetch, and use SQLExecStatement or SQLPrepareStatement which themselves call SQLEncodeSpecialChars.

Code:
void SQLExecDirect(string sSQL)
{
    SetLocalString(GetModule(), "NWNX!ODBC!EXEC", sSQL);
}

string SQLPrepareStatement(string sSQL, string sStr0="",
            string sStr1="", string sStr2="", string sStr3="", string sStr4="",
            string sStr5="", string sStr6="", string sStr7="", string sStr8="",
            string sStr9="", string sStr10="", string sStr11="", string sStr12="",
            string sStr13="", string sStr14="", string sStr15="")
{
  int nPos, nCount = 0;

  string sLeft = "", sRight = sSQL;

  while ((nPos = FindSubString(sRight, "?")) != -1)
  {
    string sInsert;

    switch (nCount++)
    {
      case 0:  sInsert = sStr0; break;
      case 1:  sInsert = sStr1; break;
      case 2:  sInsert = sStr2; break;
      case 3:  sInsert = sStr3; break;
      case 4:  sInsert = sStr4; break;
      case 5:  sInsert = sStr5; break;
      case 6:  sInsert = sStr6; break;
      case 7:  sInsert = sStr7; break;
      case 8:  sInsert = sStr8; break;
      case 9:  sInsert = sStr9; break;
      case 10: sInsert = sStr10; break;
      case 11: sInsert = sStr11; break;
      case 12: sInsert = sStr12; break;
      case 13: sInsert = sStr13; break;
      case 14: sInsert = sStr14; break;
      case 15: sInsert = sStr15; break;
      default: sInsert = "*INVALID*"; break;
    }

    sLeft += GetStringLeft(sRight, nPos) + "'" + SQLEncodeSpecialChars(sInsert) + "'";
    sRight = GetStringRight(sRight, GetStringLength(sRight) - (nPos + 1));
  }

  return sLeft + sRight;
}

void SQLExecStatement(string sSQL, string sStr0="",
            string sStr1="", string sStr2="", string sStr3="", string sStr4="",
            string sStr5="", string sStr6="", string sStr7="", string sStr8="",
            string sStr9="", string sStr10="", string sStr11="", string sStr12="",
            string sStr13="", string sStr14="", string sStr15="")
{
  SQLExecDirect(SQLPrepareStatement(sSQL, sStr0, sStr1, sStr2, sStr3, sStr4, sStr5,
   sStr6, sStr7, sStr8, sStr9, sStr10, sStr11, sStr12, sStr13, sStr14, sStr15));
}

string _SQLFetch(string sString)
{
  int nPos = FindSubString(sString, "~");
  if (nPos == -1) return sString;

  string sReturn = "";
  while (nPos != -1)
  {
      sReturn += GetStringLeft(sString, nPos) + "'";
      sString = GetStringRight(sString, GetStringLength(sString) - (nPos + 1));
      nPos = FindSubString(sString, "~");
  }
  sReturn += sString;
  return sReturn;
}

int SQLFetch()
{
    string sRow;
    object oModule = GetModule();
    SetLocalString(oModule, "NWNX!ODBC!FETCH", GetLocalString(oModule, "NWNX!ODBC!SPACER"));
    sRow = GetLocalString(oModule, "NWNX!ODBC!FETCH");
    if (GetStringLength(sRow) > 0)
    {
        SetLocalString(oModule, "NWNX_ODBC_CurrentRow", _SQLFetch(sRow));
        return SQL_SUCCESS;
    }
    else
    {
        SetLocalString(oModule, "NWNX_ODBC_CurrentRow", "");
        return SQL_ERROR;
    }
}
Back to top
View user's profile Send private message
ShadowDragon311007



Joined: 15 Jul 2009
Posts: 12
PostPosted: Sun Oct 18, 2009 14:57    Post subject: Reply with quote
*blinks* thats a lot of code...


So yeah *rubs the back of his neck*

Where do I put it to make it all work... em can you break that down for me... heh
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431
PostPosted: Sun Oct 18, 2009 23:40    Post subject: Reply with quote
Not that it help you any...

That's why I re-wrote the core of NWNX for SQLServer.
All that string manipulation stuff is eliminated.

SQLPrepare("select ?")
SQLBind<type>(<value>) eg SQLBindString("MyValue")
SQLExecute()

All variables are passed as ODBC Parameter values. So I can pass them in-place without concern for special characters - they are all treated as null-terminated strings -- 0x00 is the only special character Wink

Oh, and the big plus - SQLBingObject() works in-place, and can be retrieved as a column in a result set - not restricted to the only column (and separate query)
Back to top
View user's profile Send private message
tinygiant



Joined: 17 Feb 2007
Posts: 3
PostPosted: Sun Jan 03, 2010 6:47    Post subject: Reply with quote
Is there, or will there be, a release in the near future that supports SQL Server as mentioned in the previous post?
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431
PostPosted: Mon Jan 04, 2010 11:12    Post subject: Reply with quote
Quote:
Is there, or will there be, a release in the near future that supports SQL Server as mentioned in the previous post?

Yes, it's been around for a while now... but only for NWNX2 (NWN1)
--the code is available for anyone to re-work it for NWNX4 (NWN2)
Back to top
View user's profile Send private message
MimCorals



Joined: 08 Feb 2012
Posts: 1
PostPosted: Wed Feb 08, 2012 18:03    Post subject: Reply with quote
I'm curious if anyone have made an adaption of this (h2_nwnx2_c) but for the SQLite database included with NWNX2?
Back to top
View user's profile Send private message
eeriegeek



Joined: 07 Jan 2008
Posts: 59
PostPosted: Wed Feb 08, 2012 19:13    Post subject: Reply with quote
Another solution to the single quote escaping problem is using the standard SQL escaping method which is: use two single quotes when you need one. I replaced the encode function in APS include with this:

Code:
string SQLEncodeSpecialChars(string sString)
{
    if (FindSubString(sString,"'") == -1) return sString;

    string sReturn = "";
    int nLen = GetStringLength(sString);
    string sChar;
    int i;
    for (i = 0; i < nLen; i++) {
        sChar = GetSubString(sString,i,1);
        if (sChar == "'")
            sReturn += "''";
        else
            sReturn += sChar;
    }
    return sReturn;
}

Two advantages of this method (over encoding with ~) are that the data are stored correctly in the database for other applications to use, and there is is no need to decode the data on retrieval, since there problem only occurs when parsing the data in the query itself. (Note: prepare/bind is probably better if it's available since it handles any weird data.)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group