issues/FIXING_SCRIPTS.md

Fixing Scripts

Useful notes for fixing scripts

Fixing Scripts

You can modify scripts however you want on the live/test server for testing/development purposes but if you want your changes to persist, you need to make the changes on the live server.

In order to make those changes without breaking things on the live server, you can enclose your modifications like below.

  void main()
  {
      // This code runs in all cases
      SendMessageToPC(OBJECT_SELF, "A message for everyone!");

      // #NWNEE-START#
      // // This code will only run on the EE server
      // PrintString("This server is running NWNEE");
      // #NWNEE-END#

      // #DIAMOND-START#
      // // This code will only run on the 1.69 server
      PrintString("This server is running 1.69.");
      // #DIAMOND-END#
  }

VERY IMPORTANT:

• Code that is specific to NWN-EE should be enclosed in the // #NWNEE-START# and // #NWNEE-END# tags AND it must be commented out so the 1.69 compiler ignores it.
• The 1.69 specific code should be enclosed in the // #DIAMOND-START# and // #DIAMOND-END# tags and should NOT be commented out so the 1.69 compiler can see it.

The NWNEE compiler has been modified so it will comment out the code within the Diamond blocks, and uncomment the code within the NWNEE blocks.

SQL Queries

We switched from MySQL to PostgreSQL. That means the SQL syntax has changed slightly and a lot of SQL queries will be broken as a result.

You can usually just copy paste the query into ChatGPT or similar and ask them to convert it from MySQL to PostgreSQL and they will get it right 9/10 times.

Beyond that, the functions for working with the database has changed. The existing functions are still there for compatibility reasons but we should update any scripts we find to use the following instead: (Note this does not apply to scripts using the Get/SetCampaign functions - those can and should remain as is)

// Prepare a SQL query (does not actually run the query yet)
// Example: dbresult MyQuery = SqlPrepareQuery("SELECT * FROM characters WHERE player_id = $1 AND name = $2);
// IMPORTANT: Do not use string concatenation to build the queries anymore. We use parameters instead (The $1, and $2 in the example above)
// The parameters are bound by calling the SqlBind* functions. $1 is bound to the first SqlBind* value, $2 is bound to the second etc.
dbresult SqlPrepareQuery(string sQuery);

// Use these to bind values to the SQL query
void SqlBindInt(dbresult sqlQuery, int nValue);
void SqlBindFloat(dbresult sqlQuery, float fFloat);
void SqlBindString(dbresult sqlQuery, string sString);
void SqlBindVector(dbresult sqlQuery, vector vVector);
void SqlBindObject(dbresult sqlQuery, object oObject, int bSaveObjectState = FALSE);
void SqlBindJson(dbresult sqlQuery, json jValue);

// The first time this is called, it runs the query and if there are rows returned it returns TRUE.
// Every time it is called after the first, if there are more rows of data it advances to them
// If there are no more rows of data it returns FALSE
// Note that for queries which do not return anything, it will always return FALSE even though the query succeeded. See SqlGetError
int SqlStep(dbresult sqlQuery);

// Gets the value from the current row. nIndex is the column index of the results, starting from 0
// Note that Sinfar's current query results are indexed from 1 so most queries will need to be adjusted
int SqlGetInt(dbresult sqlQuery, int nIndex);
float SqlGetFloat(dbresult sqlQuery, int nIndex);
string SqlGetString(dbresult sqlQuery, int nIndex);
vector SqlGetVector(dbresult sqlQuery, int nIndex);
object SqlGetObject(dbresult sqlQuery, int nIndex, location lSpawnAt, object oInventory = OBJECT_INVALID, int bLoadObjectState = FALSE);
json SqlGetJson(dbresult sqlQuery, int nIndex);

// Gets the error message (if any) from the query
string SqlGetError(dbresult sqlQuery);

// Full example of querying the database from nboard_listposts.nss
#include "nboard_inc_post"

void main()
{
    object oSelf = OBJECT_SELF;
    object oBoard = GetLocalObject(oSelf, "NBOARD");
    int nStartIndex = GetLocalInt(OBJECT_SELF, "NBOARD_POST_START_INDEX");
    
    // Prepare the query
    dbresult dbResult = SqlPrepareQuery("SELECT notice_id,pc_id,creation_time,title,content FROM notices WHERE board_tag=$1 AND creation_time > $2 ORDER BY creation_time DESC LIMIT 6 OFFSET $3");
    
    SqlBindString(dbResult, GetTag(oBoard)); // Binds to $1
    SqlBindInt(dbResult, GetBoardExpireTime(oBoard)); // Binds to $2
    SqlBindInt(dbResult, nStartIndex); // Binds to $3
    
    int nPostCount = 0;
    while (SqlStep(dbResult)) // Run the query and returns TRUE if any rows remain to be processed
    {
        string sPostIndex = IntToString(nPostCount);
        SetLocalInt(oSelf, "NBOARD_POST_ID"+sPostIndex, SqlGetInt(dbResult, 0)); // Get an int from the 0th column
        SetLocalInt(oSelf, "NBOARD_POST_PC_ID"+sPostIndex, SqlGetInt(dbResult, 1)); // Get an int from the 1st column
        SetLocalInt(oSelf, "NBOARD_POST_TIME"+sPostIndex, SqlGetInt(dbResult, 2)); // Get an int from the 2nd column
        string sTitle = SqlGetString(dbResult, 3); // Get a string from the 3rd column
        SetCustomToken(21500+nPostCount, sTitle);
        SetLocalString(oSelf, "NBOARD_POST_TITLE"+sPostIndex, sTitle);
        SetLocalString(oSelf, "NBOARD_POST_CONTENT"+sPostIndex, SqlGetString(dbResult, 4)); // Get a string from the 4th column
        nPostCount++;
    }
    nStartIndex += 6;
    SetLocalInt(oSelf, "NBOARD_POST_START_INDEX", nStartIndex);
    SetLocalInt(oSelf, "NBOARD_POST_COUNT", nPostCount);
}

Multi-line strings / Raw strings

The NWNEE compiler now supports multiline strings or raw strings which means you can write strings which span multiple lines and don't need to concatenate them or anything crazy like that:

    dbresult dbResult = SqlPrepareQuery(
        R"SELECT notice_id,pc_id,creation_time,title,content 
          FROM notices 
          WHERE board_tag=$1 
            AND creation_time > $2 
          ORDER BY creation_time 
          DESC LIMIT 6 OFFSET $3");

Simply prepend the string with R" instead of the regular " and end it with a regular "