Looking for an SQLite working example

Neverwinter Nights 1 Toolset, Scripting, NWNX
1

I have an application where I want to use the recent SQLite functionality in NWN:EE. But, though I think I know what I want to do on the SQL side of things, I am not having much luck setting up the NWN side of things - queries, binding, etc.

As an example, here is what I want (roughly) on the SQL side
CREATE TABLE equipped_names (obj_name TINYTEXT);
INSERT INTO equipped_names (obj_name) VALUES ("Watchman's Helm");
INSERT INTO equipped_names (obj_name) VALUES ("Adamantine Breastplate");
INSERT INTO equipped_names (obj_name) VALUES ("Gauntlets of Ogre Power");
INSERT INTO equipped_names (obj_name) VALUES ("Large Shield +3");
And, then if I were to run the following query:
SELECT obj_name from equipped_names;
I would expect to see
Watchman's Helm
Adamantine Breastplate
Gauntlets of Ogre Power
Large Shield +3

(In fact, the above is pretty much cut-and-pasted from an sqlite3 session I was using for testing.)

I would be happy to see any (fairly simple) working example of setting up the SQL table, populating it, and retrieving some data from it.

Sadly, Beamdog’s notes aren’t especially illustrative. E.g. they don’t show how to set up the table, so I am kind of assuming that I want something like
sqlquery qPC = SqlPrepareQueryObject(oPC, "CREATE TABLE equipped_names (@obj_name TINYTEXT);");
But, guessing incorrectly is a bit painful. Similar on the binding. I have been thinking that the binding commands take care of the INSERT INTO part of the SQL code above. But, I may be off on the concept or maybe my variable naming is off, etc. This is the sort of thing where a working example would be key.

2

I am new to this myself and I started reworking my nwnx-based persistency using mysql onto this internal db in nwn ee.

Made a quick script for a testing purpose and it worked. Check this:

Script:
void main()
{

sqlquery sql = SqlPrepareQueryCampaign("arkhdb","CREATE TABLE `accdata` (`account` varchar(45) NOT NULL, `varname` varchar(45) NOT NULL, `value` varchar(45) DEFAULT NULL, PRIMARY KEY (`account`,`varname`));");
int retVal = SqlStep(sql);
WriteTimestampedLogEntry("query1 returned: "+IntToString(retVal));
sql = SqlPrepareQueryCampaign("arkhdb","INSERT INTO `accdata` VALUES (@player,@varname,@val);");
SqlBindString(sql,"@player","Aved");
SqlBindString(sql,"@varname","solditems");
SqlBindInt(sql,"@val",4);
retVal = SqlStep(sql);
WriteTimestampedLogEntry("query2 returned: "+IntToString(retVal));
sql = SqlPrepareQueryCampaign("arkhdb", "select account,value from accdata;");
 while (SqlStep(sql))
 {
 WriteTimestampedLogEntry("print1 Value1: " + SqlGetString(sql, 0)+", Value2: "+ SqlGetString(sql, 1));
 }
sql = SqlPrepareQueryCampaign("arkhdb","INSERT INTO `accdata` VALUES ('faxes','solditems','5');");
retVal = SqlStep(sql);
WriteTimestampedLogEntry("query3 returned: "+IntToString(retVal));
sql = SqlPrepareQueryCampaign("arkhdb","INSERT INTO `accdata` VALUES ('Linx','solditems','2'),('miniork2','solditems','2'),('moduka','solditems','1'),('****','solditems','1'),('****','solditems','1'),('****','solditems','5'),('****','solditems','1'),('****','solditems','13'),('****','solditems','5'),('****','solditems','9');");
retVal = SqlStep(sql);
WriteTimestampedLogEntry("query4 returned: "+IntToString(retVal));
sql = SqlPrepareQueryCampaign("arkhdb", "select account,value from accdata;");
 while (SqlStep(sql))
 {
 WriteTimestampedLogEntry("print2 Value1: " + SqlGetString(sql, 0)+", Value2: "+ SqlGetString(sql, 1));
 }
}

and result from log:

[Thu Jul 8 21:53:23] query1 returned: 0
[Thu Jul 8 21:53:23] query2 returned: 0
[Thu Jul 8 21:53:23] print1 Value1: Aved, Value2: 4
[Thu Jul 8 21:53:23] query3 returned: 0
[Thu Jul 8 21:53:23] query4 returned: 0
[Thu Jul 8 21:53:23] print2 Value1: Aved, Value2: 4
[Thu Jul 8 21:53:23] print2 Value1: faxes, Value2: 5
[Thu Jul 8 21:53:23] print2 Value1: Linx, Value2: 2
[Thu Jul 8 21:53:23] print2 Value1: miniork2, Value2: 2
[Thu Jul 8 21:53:23] print2 Value1: moduka, Value2: 1
[Thu Jul 8 21:53:23] print2 Value1: ****, Value2: 1
[Thu Jul 8 21:53:23] print2 Value1: ****, Value2: 1
[Thu Jul 8 21:53:23] print2 Value1: ****, Value2: 5
[Thu Jul 8 21:53:23] print2 Value1: ****, Value2: 1
[Thu Jul 8 21:53:23] print2 Value1: ****, Value2: 13
[Thu Jul 8 21:53:23] print2 Value1: ****, Value2: 5
[Thu Jul 8 21:53:23] print2 Value1: ****, Value2: 9

Note that **** were public CD keys of my players. I changed this to **** here for safety reasons. Otherwise that would be invalid query since each pair of account and varname can have only one value.

Anyway, so from this quick test I can tell that.

SqlStep cannot be used as an indicator if the query was executed without error. It will probably return TRUE only when it pulls some data rows.

Difference between SqlPrepareQueryCampaign and SqlPrepareQueryObject is that the object creates database for each object you use it at (verification required). While the campaign allows to manually select database name. In singleplayer the latter can be of a benefit as it allows to store player data thorough various single player modules. For a typical server, either use campaign and input the same value all the time like “mypwdatabase” or use object version on GetModule() as object parameter passed into it. I think I will create a wrappers so I can exclude the first parameter.

And lastly, SqlBind* function are not rreally needed. You can insert values into SQL statement manually. TBH I don’t see why would I want to use these functions at all, seems to be overly complicated compared to manually writing the values into SQL query as we are used to from nwnx. But maybe there is an use case, if I figure out I let you know.

3

@Shadooow, thanks for that reply. Last night I figured out that I didn’t need the @ symbols for my table creation. And, I was using the bind functions incorrectly, thinking they sent data to the DB, instead of just doing string interpolation on the query string. That sort of opened things up and I have made progress since. Your example code will be helpful.

FWIW, I got the impression from the Beamdog doc that their SqlBind*() functions are intended as a safety feature, mostly, to prevent SQL code injection if someone is using the SQL functionality to store things that players might be able to use themselves. For that use, it wouldn’t be that hard for a player to inject SQL commands that muck up the DB. The SqlBind*() functions are supposed to properly escape strings that might result in unintended SQL injection.

In addition, I am guessing that SqlBindObject() will serialize an NWN object (e.g. placeable or item), which will save some effort if one is looking to store objects (not by reference).

4

Ah that makes sense. Because other than this, it just makes the whole sql system more cumbersome and less convinient to script with. Even with that explanation, I don’t think it was needed, if someone is reading player input and putting that into sql queries, then it is his responsibility to safeguard it against sql code injection. And it actually solves nothing because this will only work if the scripter will use the Bind commands, which as we both figured out is not a must. And because it is this annoying to use, I don’t think there will be many coders doing that. So basically, Beamdog could just give us function to strip string data from possible code injection and the rest of the functions would be much easier to use. But as always, they choose the hard way… w/e

5

We did. They’re called SqlBind(). FWIW, these mirror the C sqlite API, and that API looks that way for a reason.

You can’t prevent people from constructing their own sql strings short of introducing a new type/builder for them and that’d be even more cumbersome. And injecting ints this way is fine, but even with floats you might already run into issues.

All you can do is document it, and educate people to get into the habit of using the binders for consistency.

As an upside, SqlBind() allows you to inject object data into queries (e.g. StoreCampaignObject). That’s not possible with string injection.

1 Like