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 
 
What is wrong here? The results are incomplete...

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



Joined: 01 Jul 2009
Posts: 10
PostPosted: Wed Jul 01, 2009 7:10    Post subject: What is wrong here? The results are incomplete... Reply with quote
I use the query:

SQL = "SELECT * FROM weapon_recipes WHERE weapon_recipes.id >= 1 AND weapon_recipes.id <= 50" ;

and it returns only the first 9 results and then stops doing anything.

The code I use, which is sloppy atm, is:

Code:

#include "aps_include"
#include "lok_inc_arrays"
void DL_LoadRecipeOnObject(object oObject, string sTable,  int iRecipeID = 0, string sRecipeName = "", int iBaseItem = 0, int iReqCraftingLvl = 0,
                            int iRecipeType = 0, string sBaseItemDescr = "", string sItemDescr = "", string sItemResRef = "", int iItemQuanity = 0,
                            string sMaterial1 = "", int iQntyMaterial1 = 0, string sMaterial2 = "", int iQntyMaterial2 = 0, string sMaterial3 = "",
                            int iQntyMaterial3 = 0, string sMaterial4 = "", int iQntyMaterial4 = 0, string sMaterial5 = "", int iQntyMaterial5 = 0,
                            string sMaterial6 = "", int iQntyMaterial6 = 0, string sMaterial7 = "", int iQntyMaterial7 = 0, string sMaterial8 = "",
                            int iQntyMaterial8 = 0, string sCraftObject = "NONE");

string DL_SQL_GetNextRecipeTable(string sCurrentTable)
{
    string sNextTable = "";

    if(sCurrentTable == "weapon_recipes"){sNextTable = "armor_recipes";WriteTimestampedLogEntry("Processing table: " + sNextTable);}
    else if(sCurrentTable == "armor_recipes"){sNextTable = "trap_recipes";WriteTimestampedLogEntry("Processing table: " + sNextTable);}
    else if(sCurrentTable == "trap_recipes"){sNextTable = "mundane_recipes";WriteTimestampedLogEntry("Processing table: " + sNextTable);}
    else if(sCurrentTable == "mundane_recipes"){sNextTable = "alchemic_recipes";WriteTimestampedLogEntry("Processing table: " + sNextTable);}
    else if(sCurrentTable == "alchemic_recipes"){sNextTable = "craftcomponent_recipes";WriteTimestampedLogEntry("Processing table: " + sNextTable);}
    else if(sCurrentTable == "craftcomponent_recipes"){sNextTable = "metalurgy_recipes";WriteTimestampedLogEntry("Processing table: " + sNextTable);}//Just to make sure.
    else if(sCurrentTable == "metalurgy_recipes"){sNextTable = "FINISHED";WriteTimestampedLogEntry("Finished Loading Recipe Data.");}//Just to make sure.

    else {WriteTimestampedLogEntry("GetNextRecipeTable ERROR. sCurrentTable = " + sCurrentTable); sNextTable = "";}

    return sNextTable;
}

string DL_GetRecipePrefixByTable(string sTable)
{
    string sReturn = "";

    if(sTable == "weapon_recipes"){sReturn = "Weapon_";}
    else if(sTable == "armor_recipes"){sReturn = "Armor_";}
    else if(sTable == "trap_recipes"){sReturn = "Trap_";}
    else if(sTable == "mundane_recipes"){sReturn = "Mundane_";}
    else if(sTable == "alchemic_recipes"){sReturn = "Alchemic_";}
    else if(sTable == "craftcomponent_recipes"){sReturn = "CraftComponent_";}
    else if(sTable == "sheild_recipes"){sReturn = "Sheild_";}
    else if(sTable == "ranged_recipes"){sReturn = "Ranged_";}
    else if(sTable == "missle_recipes"){sReturn = "Missle_";}
    else if(sTable == "metalurgy_recipes"){sReturn = "Metalurgy_";}
    return sReturn;
}

void DL_SQL_LoadRecipeData(string sTable,int iCount = 0 )
{
    object oCraftingContainer = GetObjectByTag("MOD_RECIPE_MANAGER");
    int iRecipeTotal = GetLocalInt(oCraftingContainer,sTable);

    iCount = iCount + 1;
    int iNextMax = iCount + 50;

    string sRecipePrefix;
    string SQL;
    string sRecipeID;

    if(iNextMax > iRecipeTotal) iNextMax = iRecipeTotal;

    if(iCount > iRecipeTotal)
        {
            sTable = DL_SQL_GetNextRecipeTable(sTable);
            if(sTable == "FINISHED" || sTable == "")
                {
                    WriteTimestampedLogEntry("Loading Resource Node Table");
                    ExecuteScript("lok_load_nodes",GetModule());
                }
            else
                {
                    DelayCommand(1.0f,DL_SQL_LoadRecipeData(sTable,0));
                }
        }
    else
        {
            SQL = "SELECT * FROM " + sTable + " WHERE " + sTable + ".id >=  " + IntToString(iCount) + " AND "+ sTable + ".id <=  " + IntToString(iNextMax) + "" ;
           // SQL = "SELECT * FROM `" + sTable + "` WHERE " + sTable + ".id >=  '" + IntToString(iCount) + "' AND "+ sTable + ".id <=  '" + IntToString(iNextMax) + "'" ;


            string sRecipeID,sRecipeName,sBaseItem,sRecipeLevel,sRecipeType,sBaseItemDesc,sItemDesc,sItemResRef,sItemQuanity,sMaterial1,sQntyMaterial1,
            sMaterial2,sQntyMaterial2,sMaterial3,sQntyMaterial3,sMaterial4,sQntyMaterial4,sMaterial5,sQntyMaterial5,sMaterial6,sQntyMaterial6,
            sMaterial7,sQntyMaterial7,sMaterial8,sQntyMaterial8,sCraftObject;

            int iRecipeID,iBaseItem,iRecipeLevel,iRecipeType,iItemQuanity,iQntyMaterial1,iQntyMaterial2,iQntyMaterial3,iQntyMaterial4,iQntyMaterial5,
            iQntyMaterial6,iQntyMaterial7,iQntyMaterial8;

            SQLExecDirect(SQL);

            int rc = SQLFetch();
            if(rc != SQL_SUCCESS) {DelayCommand(1.0f,DL_SQL_LoadRecipeData(sTable,iCount)); return;}
            while (rc == SQL_SUCCESS)
                {
                    iRecipeID = StringToInt(SQLGetData(1));
                    sRecipeName = SQLGetData(2);
                    iBaseItem = StringToInt(SQLGetData(3));
                    iRecipeLevel = StringToInt(SQLGetData(4));
                    iRecipeType = StringToInt(SQLGetData(5));
                    sBaseItemDesc = SQLGetData(6);
                    sItemDesc = SQLGetData(7);
                    sItemResRef = SQLGetData(8);
                    iItemQuanity = StringToInt(SQLGetData(9));
                    sMaterial1 = SQLGetData(10);
                    iQntyMaterial1 = StringToInt(SQLGetData(11));
                    sMaterial2 = SQLGetData(12);
                    iQntyMaterial2 = StringToInt(SQLGetData(13));
                    sMaterial3 = SQLGetData(14);
                    iQntyMaterial3 = StringToInt(SQLGetData(15));
                    sMaterial4 = SQLGetData(16);
                    iQntyMaterial4 = StringToInt(SQLGetData(17));
                    sMaterial5 = SQLGetData(18);
                    iQntyMaterial5 = StringToInt(SQLGetData(19));
                    sMaterial6 = SQLGetData(20);
                    iQntyMaterial6 = StringToInt(SQLGetData(21));
                    sMaterial7 = SQLGetData(22);
                    iQntyMaterial7 = StringToInt(SQLGetData(23));
                    sMaterial8 = SQLGetData(24);
                    iQntyMaterial8 = StringToInt(SQLGetData(25));
                    sCraftObject = SQLGetData(26);
                    WriteTimestampedLogEntry("Recipe: " + sRecipeName + " was loaded.");

                    DL_LoadRecipeOnObject(oCraftingContainer,sTable, iRecipeID,sRecipeName,iBaseItem,iRecipeLevel,iRecipeType,sBaseItemDesc,sItemDesc,sItemResRef,
                                    iItemQuanity,sMaterial1,iQntyMaterial1,sMaterial2,iQntyMaterial2,sMaterial3,iQntyMaterial3,sMaterial4,iQntyMaterial4,
                                    sMaterial5,iQntyMaterial5,sMaterial6,iQntyMaterial6,sMaterial7,iQntyMaterial7,sMaterial8,iQntyMaterial8,sCraftObject);

                    rc = SQLFetch();
                }

            DelayCommand(1.0f,DL_SQL_LoadRecipeData(sTable,iNextMax));
        }
}

void DL_LoadRecipeOnObject(object oObject, string sTable, int iRecipeID = 0, string sRecipeName = "", int iBaseItem = 0, int iReqCraftingLvl = 0,
                            int iRecipeType = 0, string sBaseItemDescr = "", string sItemDescr = "", string sItemResRef = "", int iItemQuanity = 0,
                            string sMaterial1 = "", int iQntyMaterial1 = 0, string sMaterial2 = "", int iQntyMaterial2 = 0, string sMaterial3 = "",
                            int iQntyMaterial3 = 0, string sMaterial4 = "", int iQntyMaterial4 = 0, string sMaterial5 = "", int iQntyMaterial5 = 0,
                            string sMaterial6 = "", int iQntyMaterial6 = 0, string sMaterial7 = "", int iQntyMaterial7 = 0, string sMaterial8 = "",
                            int iQntyMaterial8 = 0, string sCraftObject = "NONE")
{
    string sRecipeID = DL_GetRecipePrefixByTable(sTable) + IntToString(iRecipeID);

    if(sRecipeName != "")
        {
            SetLocalArrayInt(oObject,sRecipeID,1,iRecipeID);
            SetLocalArrayString(oObject,sRecipeID,2,sRecipeName);
            SetLocalArrayInt(oObject,sRecipeID,3,iBaseItem);
            SetLocalArrayInt(oObject,sRecipeID,4,iReqCraftingLvl);
            SetLocalArrayInt(oObject,sRecipeID,5,iRecipeType);
            SetLocalArrayString(oObject,sRecipeID,6,sBaseItemDescr);
            SetLocalArrayString(oObject,sRecipeID,7,sItemDescr);
            SetLocalArrayString(oObject,sRecipeID,8,sItemResRef);

            //if(iItemQuanity = 0) iItemQuanity = 1;

            SetLocalArrayInt(oObject,sRecipeID,9,iItemQuanity);
            SetLocalArrayString(oObject,sRecipeID,10,sMaterial1);
            SetLocalArrayInt(oObject,sRecipeID,18,iQntyMaterial1);
            SetLocalArrayString(oObject,sRecipeID,11,sMaterial2);
            SetLocalArrayInt(oObject,sRecipeID,19,iQntyMaterial2);
            SetLocalArrayString(oObject,sRecipeID,12,sMaterial3);
            SetLocalArrayInt(oObject,sRecipeID,20,iQntyMaterial3);
            SetLocalArrayString(oObject,sRecipeID,13,sMaterial4);
            SetLocalArrayInt(oObject,sRecipeID,21,iQntyMaterial4);
            SetLocalArrayString(oObject,sRecipeID,14,sMaterial5);
            SetLocalArrayInt(oObject,sRecipeID,22,iQntyMaterial5);
            SetLocalArrayString(oObject,sRecipeID,15,sMaterial6);
            SetLocalArrayInt(oObject,sRecipeID,23,iQntyMaterial6);
            SetLocalArrayString(oObject,sRecipeID,16,sMaterial7);
            SetLocalArrayInt(oObject,sRecipeID,24,iQntyMaterial7);
            SetLocalArrayString(oObject,sRecipeID,17,sMaterial8);
            SetLocalArrayInt(oObject,sRecipeID,25,iQntyMaterial8);
            SetLocalArrayString(oObject,sRecipeID,26,sCraftObject);
            WriteTimestampedLogEntry("Recipe: " + sRecipeName + " was saved as array " + sRecipeID);
        }
}
void DL_SQL_LoadRecipeTotals()
{
    object oCraftingContainer = GetObjectByTag("MOD_RECIPE_MANAGER");
    string SQL = "SELECT * FROM `recipe_totals`";
    SQLExecDirect(SQL);
    if(SQLFetch() == SQL_SUCCESS)
        {
            SetLocalInt(oCraftingContainer,"trap_recipes",StringToInt(SQLGetData(2)));
            SetLocalInt(oCraftingContainer,"mundane_recipes",StringToInt(SQLGetData(3)));
            SetLocalInt(oCraftingContainer,"weapon_recipes",StringToInt(SQLGetData(4)));
            SetLocalInt(oCraftingContainer,"armor_recipes",StringToInt(SQLGetData(5)));
            SetLocalInt(oCraftingContainer,"enchanting_recipes",StringToInt(SQLGetData(6)));
            SetLocalInt(oCraftingContainer,"wand_recipes",StringToInt(SQLGetData(7)));
            SetLocalInt(oCraftingContainer,"staff_recipes",StringToInt(SQLGetData(8)));
            SetLocalInt(oCraftingContainer,"rod_recipes",StringToInt(SQLGetData(9)));
            SetLocalInt(oCraftingContainer,"alchemic_recipes",StringToInt(SQLGetData(10)));
            SetLocalInt(oCraftingContainer,"craftcomponent_recipes",StringToInt(SQLGetData(11)));
            SetLocalInt(oCraftingContainer,"spellcomponent_recipes",StringToInt(SQLGetData(12)));
            SetLocalInt(oCraftingContainer,"wondrous_recipes",StringToInt(SQLGetData(13)));
            SetLocalInt(oCraftingContainer,"engineering_recipes",StringToInt(SQLGetData(14)));
            SetLocalInt(oCraftingContainer,"tailoring_recipes",StringToInt(SQLGetData(15)));
            SetLocalInt(oCraftingContainer,"metalurgy_recipes",StringToInt(SQLGetData(16)));
            WriteTimestampedLogEntry("Trap Totals = " + SQLGetData(2));
            WriteTimestampedLogEntry("Mundane Totals = " + SQLGetData(3));
            WriteTimestampedLogEntry("Weapon Totals = " + SQLGetData(4));
            WriteTimestampedLogEntry("Armor Totals = " + SQLGetData(5));
            WriteTimestampedLogEntry("Enchanting Totals = " + SQLGetData(6));
            WriteTimestampedLogEntry("Wand Totals = " + SQLGetData(7));
            WriteTimestampedLogEntry("Staff Totals = " + SQLGetData(8));
            WriteTimestampedLogEntry("Rod Totals = " + SQLGetData(9));
            WriteTimestampedLogEntry("Alchemic Totals = " + SQLGetData(10));
            WriteTimestampedLogEntry("Crafting Component Totals = " + SQLGetData(11));
            WriteTimestampedLogEntry("Spell Component Totals = " + SQLGetData(12));
            WriteTimestampedLogEntry("Wondrous Object Totals = " + SQLGetData(13));
            WriteTimestampedLogEntry("Engineering Totals = " + SQLGetData(14));
            WriteTimestampedLogEntry("Tailoring Totals = " + SQLGetData(15));
            WriteTimestampedLogEntry("Metalurgy Totals = " + SQLGetData(16));
        }
     DelayCommand(1.0f,DL_SQL_LoadRecipeData("weapon_recipes",0));
}
void main(){DL_SQL_LoadRecipeTotals();  }


I wanted to load all the recipe data into the module so a query isn't made every time the crafting system is used. Is this wrong, or....? I'm still fairly new-ish to this, and am open to suggestions and ideas.
Back to top
View user's profile Send private message
Kiri-Jolith



Joined: 01 Jul 2009
Posts: 10
PostPosted: Wed Jul 01, 2009 7:12    Post subject: Reply with quote
Under windows, it cycles through all the tables, only returning a max of 10 results for the queries. In linux, it stops on the first recipe table after returning the first 9 results.

Any ideas?
Back to top
View user's profile Send private message
Kiri-Jolith



Joined: 01 Jul 2009
Posts: 10
PostPosted: Wed Jul 01, 2009 9:10    Post subject: Reply with quote
Nm. The resulting string from the query was simply too long to be processed by NWN. By limiting the results returned each query (+50 to +5), everything executed properly.
Back to top
View user's profile Send private message
Disco



Joined: 06 Dec 2006
Posts: 152
PostPosted: Wed Jul 01, 2009 10:49    Post subject: Reply with quote
Yups. Got that as well. I have to break result sets in smaller parts now and them.
Back to top
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830
PostPosted: Wed Jul 01, 2009 15:22    Post subject: ODBC Limit Reply with quote
Note - This has been discussed before (assuming it is the ODBC limit)

A method of increasing the ODBC limit is explained in another thread - Think its called ODBC Limit.



Basically, you go into your aps_include script, and look for SQLInit() function.

It should look something like
Code:

void SQLInit()
{
int i;

//Placeholder for ODBC persistence
string sMemory

for (i = 0; i< 8; i++)    //reserve 8*128 bytes
       sMemory +=
               ".....................................................................................";
 SetLocalString(GetModule(), ""NWNX!ODBC!SPACER",sMemory);
}


Change the 8 at the
i < 8; into a 16, and you will have doubled the spacer limit. Meaning Double the amount of data capable of being retrieved. (16)
Double it again, to get 4x the amount of data retrieval. (32)

I have mine set to 16, which provides 16 x 128 - which provides 2048 character length per a query result. (in theory at least)

Using this method, solved my occurance of the ODBC limit being met.
Back to top
View user's profile Send private message
Kiri-Jolith



Joined: 01 Jul 2009
Posts: 10
PostPosted: Thu Jul 02, 2009 6:08    Post subject: Reply with quote
I'll try increasing the memory as you describe, and increase my query size to test. Will post the results shortly.
Back to top
View user's profile Send private message
Kiri-Jolith



Joined: 01 Jul 2009
Posts: 10
PostPosted: Thu Jul 02, 2009 9:37    Post subject: Reply with quote
Increasing the memory size works for me, though I still have to keep the querey results small, but not as small as before. Thanks for the help mate.
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