Re: Possible replacement for SQLEncodeSpecialCharacters()

[Asmodae]

This info should still be relevant.

In setting up the database for Exodus, we have some pretty large strings many of which contain single quotes as appostrophies for dynamic conversations and such. I was leary about depending on a coversion routine that cycles through long character sets doing a find and replace function to AND from the database.

So I did a little digging, nwscript has no escape characters so that was out. What I found was intriguing. Take an object, the module for instance, but since a lot of things get stored on the module, a secondary hidden object is preferred. Lets use for this example an object with the tag DOUBLE_QUOTE , catchy right? Open up the toolset and manually set a variable on this object called "doublequotes" or some such. Assign it a single character value, one solitary doublequote.

At the beginning of your scripts create a global variable, I know its generally bad practice but its for performance reasons.

[Papillon]

Ah, very interesting! Recently, I was thinking about was to replace that function myself. This information might come in handy! Thanks.
_________________
Papillon

[Senalaya]

Sounds good, but might be a bit too 'techy' for some people. What about declaring a global string variable in the 'aps_include' and setting it to the double-quote char in SQLInit() with a "SELECT CHAR(34)"?

[Papillon]

I was thinking along those lines, yes. Probably I would just return a readily made up string with a special nwnx odbc function.
_________________
Papillon

[Senalaya]

Sorry, edited inbetween ...

[Asmodae]

Glad it could be of use. To be honest though I didn't discover the object trick. I found it on the scripting forums, and it turned out to be a huge boon for our database on Exodus... so I figured I ought to share with the database people.


quick question, any easy way to back up the database files? (using MySQL)

Just copy the data files to a new location seems the easiest for now, but curious if there are other methods.
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[Manuel]

I've always used mysqldump and set it to run via cron jobs. mysqldump should be in your /bin directory.

Years ago (99?), I wrote a Perl script to loop through all the dbs and tables and so forth. I was going to Open Source it but someone else beat me to it. Here's the project:
http://freshmeat.net/projects/mysqlbackup/

Here's one in PHP:
http://freshmeat.net/projects/phpmysqlbackup/

And mysqldump itself:
http://dev.mysql.com/doc/mysql/en/mysqldump.html

There's also another MySQL native utility called hotcopy or some such.

[JeroenB]

Why not calling the default MySQL functions for those special escape characters. I am pretty sure they know how to implement it fast and compatible with their database.

[Senalaya]

Question:

Is the "SELECT CHAR(34)" SQL command supported under MySQL and MS Access?
I know that MS-SQL supports it and it would be the most simple way, with the current NWNX code, to get the " char into the game as variable.

[Papillon]

[JeroenB]

Did I mention anything??

Alright, I will have a look for it.

[Senalaya]

I did some tests here already....

- CHAR(43)/CHR(34) is not transparent, most DBs and the ODBC standard support the latter, but MS-SQL insists on CHAR(), even via ODBC.

- Same goes for the use of double-quotes to encapsulate strings with single quotes. It's not transparent.

Another way could be to replace single ' in strings with '' (2x single quote), MS-SQL, MS-SQL via ODBC and MS-Access support that, but I'm not sure about MySQL and ODBC in general.

Example:

Character name: Emanuel d'Test
SQL string example: SELECT * FROM table WHERE name='Emanuel d''Test'
NWScript: sSQL = "SELECT * FROM table WHERE name='" + SQLEnc(name) + "'"; (SQLEnc() doubles the single quote in that name)

You don't have to worry about decoding it again, since the name will be stored and retrieved with only the single quote = Emanuel d'Test.

[dguntner]

Except that they're talking about testing with MySQL, and you tested MS-SQL, which is not the same thing...

--Dave

[Roland Rosier]

When I started writing scripts to pass Character names in and out of MySQL, I wrote a function which replaced all single quotes with two single quotes. In this fashion, the Character names in my database end up matching the character names directly used in the game.

That means I can retrieve a character name from the database and compare it against a character name from the game.

Now, I believe that the SQLEncodeSpecialChars and SQLDecodeSpecialChars have a mapping multiplicity issue. SQLEncodeSpecialChars replaces all single quotes with ~. At the same time, any ~ characters used in character names are left as ~. This means that if a character name is retrived from the database and run through SQLDecodeSpecialChars, any ~ characters are turned into single quotes. If the original character was a ~, this resulting string will not test for equality against the original character name because the ~ will now be a '.

Hence, the mapping of SQLEncodeSpecialChars is not 1:1.

Would it not be a better idea if SQLEncodeSpecialChars just replaces all instances of single quotes with two single quotes, thereby avoiding this problem?

Roland Rosier.

[dguntner]

I don't think MySQL would like two ' characters in a row, but I've never tested it. It uses a single quote to wrap around things that it's searching for. I'm not sure how it would take when given something like

select * from pwdata where player = 'some''string';

(that's two single quotes in the middle, not a double quote).

--Dave