| View previous topic :: View next topic |
| Author |
Message |
darwinscusp
Joined: 19 Jan 2005
Posts: 19
|
 Posted: Wed Jan 19, 2005 5:06 Post subject: looping over SQLFetch() |
 |
|
I'm attempting to find a way to loop over multiple row returns from a query.
Ok, this is probably a ridiculously simple question to answer, but I can't seem to figure it out, or find it posted anywhere.
If I build a SELECT query, that returns multiple rows, how do I access the multiple rows? So far, I've got the following process, but it doesn't seem to return more than one row.
| Code: |
string sSQL = "SELECT * from spell_table;";
SQLExecDirect(sSQL);
while (SQLFetch())
{
int nSpellID = StringToInt(SQLGetData(1));
int nSPower = StringToInt(SQLGetData(2));
SendMessageToPC(oPC,"Found " + StringToInt(nSpellID));
}
|
Any suggestions? I'm basically trying to find a way to iterate over each row that's returned from the query.
Thanks! |
|
| Back to top |
|
 |
Lokey
Joined: 02 Jan 2005
Posts: 158
|
| Posted: Wed Jan 19, 2005 8:26 Post subject: |
|
|
Wasn't SQLGetData() an older version function? Don't have the standard distribution of the db functions in NWScript (made some changes), but remember reading something about that in the comments of the database script set.
Thinking this should work as you expect (you're right about SQLFetch() changing rows and the other function changing columns):
| Code: |
string sSQL = "SELECT * from spell_table;";
SQLExecDirect(sSQL);
while (SQLFetch())
{
int nSpellID = StringToInt(SQLGetField(1));
int nSPower = StringToInt(SQLGetField(2));
SendMessageToPC(oPC,"Found " + StringToInt(nSpellID));
}
|
Edit: oh, how large is the table of information you're trying to access?
_________________
Neversummer PW NWNx powered mayhem  |
|
| Back to top |
|
|
darwinscusp
Joined: 19 Jan 2005
Posts: 19
|
| Posted: Thu Jan 20, 2005 0:58 Post subject: |
|
|
It's not particularly large. I think it's 807 rows max. It's just a custom spell-tracking list, that contains various other data (spell power level, etc) that I use for a PW's custom rest system. After looking around, I'm guessing I have a lingering 'limit 1' in the query or something stupid.
Thanks for taking a look. I'll post my findings later this evening. |
|
| Back to top |
|
|
Mr-XXS
Joined: 14 Jan 2005
Posts: 8
|
| Posted: Thu Jan 20, 2005 3:47 Post subject: |
|
|
| I've had some problems with long results to requests. In fact it seems that the result max length is around 400-450 rows. So I suggest you to limit your request by using : "LIMIT 0,300", "LIMIT 300,300" etc... So you will have 300row's long results. |
|
| Back to top |
|
|
darwinscusp
Joined: 19 Jan 2005
Posts: 19
|
| Posted: Thu Jan 20, 2005 4:40 Post subject: |
|
|
Ok, after further examination, I'm even more stuck. I ran the query manually, and in my test case, it returns 9 rows. However, the code is still only processing one row. Here's the actual code:
| Code: | string sSQL = "SELECT power,total_uses,spell_tracking.spell_id FROM " +
"spell_tracking,spell_power WHERE " +
"spell_tracking.spell_id=spell_power.spell_id AND " +
"spell_tracking.pc_id='" + sPCID + "'" +
"ORDER BY power;";
SQLExecDirect(sSQL);
float fPower;
while(SQLFetch() == SQL_SUCCESS)
{
fPower = StringToFloat(SQLGetData(1));
string sUses = SQLGetData(2);
string sSpellID = SQLGetData(3);
string sMessage = "Removing " + sSpellID + ". " + FloatToString(fSPToRestore) + ".";
SendMessageToPC(oPC,sMessage);
DeleteOneSpellInstance(sPCID,sSpellID,sUses);
fSPToRestore -= fPower;
if (fSPToRestore <= 0.0f) return;
} |
Any ideas? |
|
| Back to top |
|
|
Papillon
x-man
Joined: 28 Dec 2004
Posts: 1060
Location: Germany
|
| Posted: Thu Jan 20, 2005 9:22 Post subject: |
|
|
| Mr-XXS wrote: | | I've had some problems with long results to requests. In fact it seems that the result max length is around 400-450 rows. So I suggest you to limit your request by using : "LIMIT 0,300", "LIMIT 300,300" etc... So you will have 300row's long results. |
There is no such limit, unless it is a bug.
| Code: |
Any suggestions? I'm basically trying to find a way to iterate over each row that's returned from the query.
|
As always in cases like this, what does the log say ?
_________________
Papillon |
|
| Back to top |
|
|
Primogenitor
Joined: 08 Jan 2005
Posts: 88
|
| Posted: Fri Feb 04, 2005 14:35 Post subject: |
|
|
I seem to have hit a limit as well  . Basically, I have a table that is a replicate of the portraits.2da file (1099 lines long, 7 columns), and I have the following NWscript to access it:
| Code: |
string SQL = "SELECT row, BaseResRef FROM cached2da_portraits WHERE (BaseResRef <> '' )";
SQLExecDirect(SQL);
while(SQLFetch() != SQL_ERROR)
{
PrintString(SQLGetData(1)+" "+SQLGetData(2));
}
PrintString("END OF FILE");
|
So you would expect that to return 1065 lines, ending with
1065 el_f_17_
END OF FILE
However, instead I get:
599 ****
600 PLC_C10_
at the end in nwserverlog1.txt and
o Sent response (8 bytes): 598¬****
o Sent response (8 bytes): 599¬****
o Sent response (12 bytes): 600¬PLC_C10_
o Sent response (15 bytes): 601¬cat_mpanth_
at the end of nwnx_odbc.txt It could be a script TMI error however, I dont know because Im at work so its running just on the dedicated server (windows), NWNX version V.2.6.1 ODBC plugin version V.0.9.2.3 (SQLite DB). I will try doing several queries using LIMIT to break it down into blocks of 500 and see if that helps.
EDIT: Okay, this code seems to work:
| Code: |
void DoOutput(int i=0)
{
string SQL = "SELECT row, BaseResRef FROM cached2da_portraits WHERE (BaseResRef <> '' ) LIMIT "+IntToString(i)+" , "+IntToString(500);
SQLExecDirect(SQL);
int nCounter;
while(SQLFetch() != SQL_ERROR)
{
PrintString(SQLGetData(1)+" "+SQLGetData(2));
nCounter++;
}
if(nCounter < 500)
PrintString("END OF FILE");
else
DelayCommand(0.01, DoOutput(i+500));
}
|
Looks like it was a script TMI error after all. |
|
| Back to top |
|
|
JeroenB
Joined: 31 Dec 2004
Posts: 228
Location: Netherlands
|
| Posted: Fri Feb 04, 2005 18:12 Post subject: |
|
|
| This could be the scripting limit of NWN. There is a maximum number of operations that may be performed in one script. I think you have reached that that number, as > 1000 lines results in way to many operations. Splitting this up with LIMIT will indeed solve that problem. |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
