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 
 
storing/retreiving object ok in sqlite but not mysql

 
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related
View previous topic :: View next topic  
Author Message
Lucky Day



Joined: 07 Aug 2011
Posts: 3
PostPosted: Sun Aug 07, 2011 1:52    Post subject: storing/retreiving object ok in sqlite but not mysql Reply with quote
Noob here on using SQL databases with NWN (always used persistent objects on the character instead). I was looking for a similar topic but I can't find it. Maybe you can point me in the right direction if this has been asnwered before.

In the aps_demo module I can get the sqlite DB to store and retrieve an object fine but not with mysql.

I'm using mysql 5.5.8 on Win7 x64. Retrieving a variable works fine in mysql. Viewing the table in sqliteCC looks fine but in mysql there's a lot of junk in there, at least from the command line (here it is from phpMyAdmin)

    player tag name val expire last
    ~ Chest1 Item_0 [BLOB - 1.7KiB] 0 2011-08-06 16:12:24
    ~ Chest1 Item_1 [BLOB - 1.8KiB] 0 2011-08-06 16:12:24
    ~ Chest1 Item_2 [BLOB - 2.9KiB] 0 2011-08-06 16:12:24


sqlite looks identical but it says UTI V3.28 under va

I've updated to Zebranky's versions but the same issue is happening. Is there a problem with the create table in the demo mod?

thanks
Back to top
View user's profile Send private message
Terra_777



Joined: 27 Jun 2008
Posts: 216
Location: Sweden
PostPosted: Tue Aug 09, 2011 16:54    Post subject: Reply with quote
If I'm not mistaken "blobs" are regarded as files in MySQL and requires global file privileges for the user handling them. You could also change the variable type to TEXT(or LONGTEXT), could work if you don't feel like tossing file privs around.

Quote:
ALTER TABLE 'tablename' CHANGE 'columnname' 'columnname' LONGTEXT NULL DEFAULT NULL


"LONGTEXT" in this statement is the variable type you want. If you're specifying a variable that requires a length you do that with brackets. IE VARCHAR(123).
_________________
I dun have any signature, I'm happy anyway.
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Lucky Day



Joined: 07 Aug 2011
Posts: 3
PostPosted: Thu Aug 11, 2011 19:29    Post subject: Reply with quote
thanks for getting back to me.

I'm giving NWNX root for the time being to avoid security problems.

in demo_obj_create I changed the type from a blob to a LONGTEXT but got the same result.

Code:

    // For MySQL

    /*
    SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwobjdata' for MySQL...");
    SQLExecDirect("CREATE TABLE pwobjdata (" +
        "player varchar(64) NOT NULL default '~'," +
        "tag varchar(64) NOT NULL default '~'," +
        "name varchar(64) NOT NULL default '~'," +
        "val blob," +
        "expire int(11) default NULL," +
        "last timestamp NOT NULL default CURRENT_TIMESTAMP," +
        "PRIMARY KEY  (player,tag,name)" +
        ") ENGINE=MyISAM DEFAULT CHARSET=latin1;");
     */

    SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwobjdata' for MySQL...");
    SQLExecDirect("CREATE TABLE pwobjdata (" +
        "player varchar(64) NOT NULL default '~'," +
        "tag varchar(64) NOT NULL default '~'," +
        "name varchar(64) NOT NULL default '~'," +
        "val LONGTEXT," +
        "expire int(11) default NULL," +
        "last timestamp NOT NULL default CURRENT_TIMESTAMP," +
        "PRIMARY KEY  (player,tag,name)" +
        ") ENGINE=MyISAM DEFAULT CHARSET=latin1;");


the logs don't show an error

Code:

o Got request: CREATE TABLE pwobjdata (player varchar(64) NOT NULL default '~',tag varchar(64) NOT NULL default '~',name varchar(64) NOT NULL default '~',val LONGTEXT,expire int(11) default NULL,last timestamp NOT NULL default CURRENT_TIMESTAMP,PRIMARY KEY  (player,tag,name)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
o Got request: SELECT player FROM pwobjdata WHERE player='~' AND tag='Chest1' AND name='Item_0'
o Sent response (0 bytes):
o Got request (scorco): INSERT INTO pwobjdata (player,tag,name,val,expire) VALUES('~','Chest1','Item_0',~s,0)
o Got request: SELECT player FROM pwobjdata WHERE player='~' AND tag='Chest1' AND name='Item_1'
o Sent response (0 bytes):
o Got request (scorco): INSERT INTO pwobjdata (player,tag,name,val,expire) VALUES('~','Chest1','Item_1',~s,0)
o Got request: SELECT player FROM pwobjdata WHERE player='~' AND tag='Chest1' AND name='Item_2'
o Sent response (0 bytes):
o Got request (scorco): INSERT INTO pwobjdata (player,tag,name,val,expire) VALUES('~','Chest1','Item_2',~s,0)
o Got request (scorco): SELECT val FROM pwobjdata WHERE player='~' AND tag='Chest1' AND name='Item_0'


I can manually type in " SELECT val FROM pwobjdata WHERE player='~' AND tag='Chest1' AND name='Item_0';" from the mysql command and I get what looks like a legitimate response.

I've used both versions of the demo_obj_loadval code (here's the first if you haven't memorized it by now)

Code:

    int iItem;
    int bContinue = TRUE;
    object oCreated;
    object oChest1 = GetObjectByTag("Chest1");
    object oChest2 = GetObjectByTag("Chest2");

    /* Method 1: GetPersistentObject
     *
     * Use this method for simplicity.
    */

    while (bContinue)
    {
        oCreated = GetPersistentObject(oChest1, "Item_" + IntToString(iItem), oChest2);
        if (!GetIsObjectValid(oCreated))
            bContinue = FALSE;
        else
            iItem++;
    }
...


it looks like RetreiveCampaignObject creates an object type from the information then? Am I right in saying there's some issue between StoreCampaignObject and RetreiveCampaignObject?

(I'm going to assume the val type BLOB and TEXT/LONGTEXT doesn't matter as NWN reads all info as text?)

It would be nice to confirm inside NWN the results of what RetrieveCampaignObject came back with in text and not just an object.

Where would be the difference then with how SQLite stores and retrieves object data compared MySQL since SQLite works? Is there possibly a corruption?

thanks
Back to top
View user's profile Send private message
Lucky Day



Joined: 07 Aug 2011
Posts: 3
PostPosted: Tue Aug 16, 2011 23:19    Post subject: Reply with quote
I've tried it at work on my Win7 32 bit machine and I get the same results

No errors in the logs. Text value is fine; its the object that's not being read problem.

Is there a problem with the source code I'm using? I used the ODBC2 on the downloads at this site and tried it, then updated it with Zerbanky's with the same result.
Back to top
View user's profile Send private message
maddogfargo



Joined: 03 Nov 2009
Posts: 49
PostPosted: Thu Dec 01, 2011 19:10    Post subject: Reply with quote
It's been a while since I used mySQL so all I can suggest is this basic check:

Verify the config settings for NWNx, mySQL and in your module scripts are all set correctly and any timeouts or size limitations are large enough to allow full storage of the object.

Verify the account running NWN & NWNx has rights to the mySQL database.

Sorry, that's all I can think of right now. Hope it helps.
_________________
* illegible scribble *
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related 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