| View previous topic :: View next topic |
| Author |
Message |
TroveLord
Joined: 22 Nov 2006
Posts: 136
Location: Italy
|
 Posted: Mon Aug 20, 2007 18:01 Post subject: SELECT INTO OUTFILE and windows |
 |
|
Greetings,
I'm trying to export a table contents into tab spaced text file using SELECT INTO OUTFILE command, yet if I try to specify the absolute path it ignores the backslash, returning an error.
I've tried to:
-double (and actually even triple) the backslash. e.g. C:\\logs\\mylogs\\
-use slashes. e.g. C:/logs/mylogs/
-search the internet for a solution.
The xp_mysql.txt log reads this:
! SQL Error: Can't create/write to file 'C:logsmylogslog_items.txt' (Errcode: 13).
Any help?
TIA. |
|
| Back to top |
|
 |
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Tue Aug 21, 2007 0:19 Post subject: Re: SELECT INTO OUTFILE and windows |
|
|
| TroveLord wrote: | Greetings,
I'm trying to export a table contents into tab spaced text file using SELECT INTO OUTFILE command, yet if I try to specify the absolute path it ignores the backslash, returning an error.
I've tried to:
-double (and actually even triple) the backslash. e.g. C:\\logs\\mylogs\\
-use slashes. e.g. C:/logs/mylogs/
-search the internet for a solution.
The xp_mysql.txt log reads this:
! SQL Error: Can't create/write to file 'C:logsmylogslog_items.txt' (Errcode: 13).
Any help?
TIA. |
The backslash character gets dropped when passed to NWNX (in NWN2 at any rate). In my plugins I had to resort to character substitution to get the backslashes working.
-I can't remember clearly- but I think the following has better success (for when you can't code the plugin)
SetLocalString(obj,"MyString","C:\file.txt");
string str = GetLocalString(obj,"MyString");
nwnxFunction(..., str, ...) <--pass the variable, not the text.
NWN2 is doing some jiggery-pokery with escaping strings. It manages them internally, so the 'true' value of a string cannot be accessed until NWN2 has 'mapped' the memory of the string after a -read-. Possibly something to do with the encoding conversion (UTF-8 -> ASCII -> UTF-8)
Cheers
Gryphyn |
|
| Back to top |
|
|
TroveLord
Joined: 22 Nov 2006
Posts: 136
Location: Italy
|
| Posted: Tue Aug 21, 2007 0:55 Post subject: |
|
|
| Thanks for the explanation Gryphyn, I'd curse the engine now but I'm too busy finding a way to solve this, unfortunately the variable suggestion didn't work, I'm still in need of help. Thanks. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Tue Aug 21, 2007 5:29 Post subject: |
|
|
| TroveLord wrote: | | Thanks for the explanation Gryphyn, I'd curse the engine now but I'm too busy finding a way to solve this, unfortunately the variable suggestion didn't work, I'm still in need of help. Thanks. |
You can get the character substitution stuff from xp_spawn plugin. (nwnx1.08)
-- EscapeString(string)
Changes all instances of "|" character to "\" character.
You can use this function even if you don't use the rest.
This bit works, as I need it to define 'path' strings. (As you're trying to do)
Once you've escaped it, it remains this way - even when passed back to the plugin (a good thing).
Cheers
Gryphyn |
|
| Back to top |
|
|
TroveLord
Joined: 22 Nov 2006
Posts: 136
Location: Italy
|
| Posted: Tue Aug 21, 2007 10:58 Post subject: |
|
|
Thanks for your time and patience, I think I'm almost there. I used your plugin and it's great but I still get half of a error.
This is my nwnx log
| Quote: | * Executing: SELECT * INTO OUTFILE 'C:\Thesk\logs\log_items.log' FROM log_items
! SQL Error: Can't create/write to file 'C:Thesklogslog_items.log' (Errcode: 22).
* Executing: DELETE FROM log_items |
This is the function that generates it
| Code: | void ExportSQLLogFile(string sTable, string sPath = "C:|Thesk|logs|")
{
sPath = SpawnEscape(sPath);
SQLExecDirect("SELECT * INTO OUTFILE '"+sPath+sTable+".log' FROM "+sTable+"");
SQLExecDirect("DELETE FROM "+sTable+""); //reset table for new usage
} |
This function is called in the On Module Load event.
So, as you can see it passes the backslash to the database but it attempts the file creation without.
What I'm doing wrong?
Thanks. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Tue Aug 21, 2007 14:22 Post subject: |
|
|
Yep, that all makes sense...
but what is happening is the nwn2 constructed string is being passed not the Escaped string. (escape the entire SQL statement)
sSQL = "SELECT * INTO OUTFILE '"+sPath+sTable+".log' FROM "+sTable;
sSQL = SpawnEscape(sSQL);
SQLExecDirect(sSQL);
*the delete should be fine as it is.
if this doesn't work,
try assigning the return value to a fresh string (ie not sSQL)
I'll go look over the spawn plugin and see exactly what I've done there.
Cheers
Gryphyn |
|
| Back to top |
|
|
TroveLord
Joined: 22 Nov 2006
Posts: 136
Location: Italy
|
| Posted: Tue Aug 21, 2007 15:04 Post subject: |
|
|
Still the same, I've tried like this.
| Code: | void ExportSQLLogFile(string sTable, string sPath = "C:|Thesk|logs|")
{
string sSQL = "SELECT * INTO OUTFILE '"+sPath+sTable+".log' FROM "+sTable+"";
string sSQL2 = SpawnEscape(sSQL);
SQLExecDirect(sSQL2);
SQLExecDirect("DELETE FROM "+sTable+""); //reset table for new usage
} |
The logfile shows exactly the same as before.
Delete from statement indeed works. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Wed Aug 22, 2007 0:30 Post subject: |
|
|
That's me done for a quick solution... sorry.
I'm not sure what's going on, I'll have to re-test as this was working (as they say, on my machine).
I believe MySQL may allow you to specify a different path-separator, you could try that.
Depending on your MySQL version, you could write a stored-proc to do the job. --you'd have to have a fixed path embedded in the stored-proc.
Cheers
Gryphyn |
|
| Back to top |
|
|
TroveLord
Joined: 22 Nov 2006
Posts: 136
Location: Italy
|
| Posted: Wed Aug 22, 2007 10:07 Post subject: |
|
|
I'll try that, thanks so kindly for the help so far.  |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
