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 
 
SQLFetch() - What am I doing wrong?

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



Joined: 15 Aug 2008
Posts: 3
PostPosted: Fri Aug 15, 2008 3:06    Post subject: SQLFetch() - What am I doing wrong? Reply with quote
So, the scripts I am writing often have to receive multiple rows from queries, and all of them are not working right. I've tried using SQLFetch() in several places and ways: inside and outside of loops, with and without == SQL_SUCCESS conditional testing, and with and without the "NEXT" parameter, all to no avail.

Here is one of my scripts:
Code:
void main()
{
   object oPC = GetItemActivator();
   object oQuestLog = GetItemPossessedBy(oPC, QUEST_LOG_TAG);
   int nCounter = 0;
   int nPlayerId = GetPlayerId(oPC);
   string sQuery = "SELECT IsComplete, QuestId FROM PlayerQuestLogsTbl WHERE PlayerId = '" + IntToString(nPlayerId) + "'";
   
   SQLExecDirect(sQuery);
   
   DisplayGuiScreen(oPC, "SCREEN_QUEST_LOG", TRUE, "quest_log.xml");
      
   // Got the player's quest log, now let's do this!
   SetGUIObjectText(oPC, "SCREEN_QUEST_LOG", "txtQuestDesc", -1, "Click on a quest name to have the quest description displayed here.");
   SendMessageToPC(oPC, "PlayerId = " + IntToString(nPlayerId));
   
   SQLFetch();

   while((nCounter < 5))
   {
      string sIsComplete = SQLGetData(1);
      string sQuestId = SQLGetData(2);
      string sName = GetQuestName(StringToInt(sQuestId));
      SendMessageToPC(oPC, "Quest: " + sQuestId);
      
      if(sIsComplete == "1")
      {
         // This quest is complete, so ignore it.
      }
      else
      {
         SetGUIObjectText(oPC, "SCREEN_QUEST_LOG", "txtQuest" + IntToString(nCounter++), -1, sName);
      }

      SQLFetch();
   }
   
   SetLocalInt(oQuestLog, "nQuestLogPage", 0);
}


What this script should do is open the player's quest log, and populate it with the quest information for the quests that player is currently on (with a maximum of 5 being displayed per page). Gets the first quest fine, but if the player has more than one in their quest log, it does not work.

My latest xp_mysql.txt contains the following:
Code:
NWNX MySQL Plugin V.0.0.8
(c) 2007 by Ingmar Stieger (Papillon)
visit us at http://www.nwnx.org
(built using mysql-5.0.27 source)

* Log level set to 2 (everything)
* Connecting to server localhost
* Plugin initialized.
* Registering under function class SQL
* Executing: SELECT MaxToDrop FROM CreatureTbl WHERE CreatureTag = 'n_aldanon'
* Executing: SELECT CreatureId, HasItems FROM CreatureTbl WHERE Creaturetag = 'n_aldanon'
* Executing: SELECT Gold FROM CreatureTbl WHERE CreatureTag = 'n_aldanon'
* Executing: SELECT SettingValue FROM SettingsTbl WHERE SettingName = 'GOLD_DROP_RATE'
* Returning: 1.0 (column 0)
* Executing: INSERT INTO PlayerTbl (PlayerName, AccountName) VALUES ('Adaur Harbor', 'jabbedxorz')
* Executing: SELECT SettingValue FROM SettingsTbl WHERE SettingName = 'DISPLAY_MOTD'
* Returning: 1 (column 0)
* Executing: SELECT SettingValue FROM SettingsTbl WHERE SettingName = 'MOTD'
* Returning: Welcome to <color=red>&&SERVER_NAME&&</color>.  Please abide by the rules and enjoy your stay here. (column 0)
* Executing: SELECT SettingValue FROM SettingsTbl WHERE SettingName = 'SERVER_NAME'
* Returning: My Server (column 0)
* Executing: SELECT PlayerId FROM PlayerTbl WHERE AccountName = 'jabbedxorz' AND PlayerName = 'Adaur Harbor'
* Returning: 6 (column 0)
* Executing: SELECT IsComplete, QuestId FROM PlayerQuestLogsTbl WHERE PlayerId = '6'
* Returning: 0 (column 0)
* Returning: 1 (column 1)
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '1'
* Returning: Bob's Bad Quest (column 0)
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT IsComplete, QuestId FROM PlayerQuestLogsTbl WHERE PlayerId = '6'
* Returning: 0 (column 0)
* Returning: 1 (column 1)
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '1'
* Returning: Bob's Bad Quest (column 0)
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT IsComplete, QuestId FROM PlayerQuestLogsTbl WHERE PlayerId = '6'
* Returning: 0 (column 0)
* Returning: 1 (column 1)
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '1'
* Returning: Bob's Bad Quest (column 0)
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'
* Executing: SELECT QuestName FROM QuestTbl WHERE QuestId = '0'


The query works fine - confirmed this in both the MySQL console and by enclosing the columns with count() to verify that there are two rows, but I can only access the first row.
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431
PostPosted: Fri Aug 15, 2008 3:34    Post subject: Reply with quote
You have another SQL query within your loop.
GetQuestName() runs SELECT QuestName FROM QuestTbl WHERE QuestId = ?
This in effect stops remembering the first query. You SQLFetch() within the loop is looking for the second row of the 'QuestName' query.

You want to get all your data with only 1 query.

SELECT a.IsComplete, a.QuestId, b.QuestName
FROM PlayerQuestLogsTbl a, QuestTbl b
WHERE a.PlayerId = ?
AND a.QuestId = b.QuestId

then string sName = SQLGetData(3);
and no need for an secondary query.

Cheers
Gryphyn
Back to top
View user's profile Send private message
jabs



Joined: 15 Aug 2008
Posts: 3
PostPosted: Fri Aug 15, 2008 15:15    Post subject: Reply with quote
Thanks. I'll try this out when I get home from work.
Back to top
View user's profile Send private message
jabs



Joined: 15 Aug 2008
Posts: 3
PostPosted: Sat Aug 16, 2008 2:15    Post subject: Reply with quote
Sure as Shirley, it worked.
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