| View previous topic :: View next topic |
| Author |
Message |
eeriegeek
Joined: 07 Jan 2008
Posts: 59
|
 Posted: Mon Nov 15, 2010 5:49 Post subject: SQLFetch with RCO |
 |
|
I am attempting to retrieve a series of objects from a table. The RCO hook seems geared toward retrieving a single object. Is it possible to prepare a select statement like the following:
| Code: | | select my_object from my_object_table where my_key='X' |
and then loop throught the resulting list of objects with SQLFetch? (I'm working with the Linux version of NWNX2.)
Thanks for any help! |
|
| Back to top |
|
 |
virusman
Joined: 30 Jan 2005
Posts: 1020
Location: Russia
|
| Posted: Mon Nov 15, 2010 10:11 Post subject: |
|
|
The current Linux version has separate resultset for SCO/RCO queries, so they don't break the main loop. Check your nwnx_odbc version and update if needed.
Also, there is an undocumented feature: if you pass "FETCHMODE" as variable name, RCO will act as SQLFetch() and use the query from SQLExec().
Example:
| Code: | SQLExecDirect("SELECT data, foo, bar FROM table");
object oObject = RetrieveCampaignObject("NWNX", "FETCHMODE", lLocation, OBJECT_INVALID);
while(GetIsObjectValid(oObject))
{
int nFoo = StringToInt(SQLGetData(2));
int nBar = StringToInt(SQLGetData(3));
oObject = RetrieveCampaignObject("NWNX", "FETCHMODE", lLocation, OBJECT_INVALID);
} |
_________________
In Soviet Russia, NWN plays you! |
|
| Back to top |
|
|
eeriegeek
Joined: 07 Jan 2008
Posts: 59
|
| Posted: Mon Nov 15, 2010 17:52 Post subject: |
|
|
| Thanks for the quick reply virusman, I'll give it a try. |
|
| Back to top |
|
|
Zunath
Joined: 06 Jul 2006
Posts: 183
|
| Posted: Sun Jan 08, 2012 8:14 Post subject: |
|
|
EDIT: I'm using NWNX2 for Windows.
My SQL statement is set up like this (example):
SELECT Item1, Item2 FROM `Storage` WHERE ID=2
I get Item1 just fine, but how do I get Item2? My code looks like this:
| Code: |
object oItem = RetrieveCampaignObject("NWNX", "FETCHMODE", lLocation, oContainer);
while(GetIsObjectValid(oItem))
{
oItem = RetrieveCampaignObject("NWNX", "FETCHMODE", lLocation, oContainer);
}
|
I'm guessing it's moving to the next row by doing that but I don't know how to get the next blob column. Any help would be really appreciated!  |
|
| Back to top |
|
|
virusman
Joined: 30 Jan 2005
Posts: 1020
Location: Russia
|
| Posted: Sun Jan 08, 2012 13:20 Post subject: |
|
|
RCO always uses the first column, you'll have to do a separate query for another column.
_________________
In Soviet Russia, NWN plays you!
Last edited by virusman on Sun Jan 08, 2012 18:59; edited 1 time in total |
|
| Back to top |
|
|
Zunath
Joined: 06 Jul 2006
Posts: 183
|
| Posted: Sun Jan 08, 2012 15:24 Post subject: |
|
|
Gotcha, thanks again Virusman. Helpful as always
Got another one for ya while it's sort of on topic: Is it inefficient to get each value one at a time like this?
For example, doing this:
| Code: |
string sValue1 = "SELECT Value1 FROM Table1 WHERE ID=1;"
SQLExecDirect(sValue1);
if(SQLFetch() == SQL_SUCCESS)
{
sValue1 = SQLGetData(1);
}
string sValue2 = "SELECT Value2 FROM Table1 WHERE ID=1;"
SQLExecDirect(sValue2);
if(SQLFetch() == SQL_SUCCESS)
{
sValue1 = SQLGetData(1);
}
string sValue3 = "SELECT Value3 FROM Table1 WHERE ID=1;"
SQLExecDirect(sValue3);
if(SQLFetch() == SQL_SUCCESS)
{
sValue1 = SQLGetData(1);
}
|
As opposed to this:
| Code: |
string sSQL = "SELECT Value1, Value2, Value3 FROM Table1 WHERE ID=1;";
SQLExecDirect(sSQL);
if(SQLFetch() == SQL_SUCCESS)
{
sValue1 = SQLGetData(1);
sValue2 = SQLGetData(2);
sValue3 = SQLGetData(3);
}
|
I try to stick to the second way since it makes sense that it's less CPU intensive but I might just be making stuff up. The only reason I ask about the first way is because it's sometimes easier to get each one value at a time (by way of separate functions).
So my question: Is the first way less efficient than the second way? If so, is it enough to make a noticeable difference?
Thanks. |
|
| Back to top |
|
|
virusman
Joined: 30 Jan 2005
Posts: 1020
Location: Russia
|
| Posted: Sun Jan 08, 2012 18:57 Post subject: |
|
|
Every query has significant overhead, so I recommend using as much SQL power (JOINs, calculated fields, etc.) as possible to minimize queiry count.
RCO just doesn't support multiple fields - that's a limitation, doesn't mean it's optimal.
Also, if you need to fetch a set of rows for predefined number of IDs, write something like this:
| Code: | | SELECT field1, field2, field3 FROM table WHERE id IN (1, 2, 3, 444, 555) |
_________________
In Soviet Russia, NWN plays you! |
|
| Back to top |
|
|
Zunath
Joined: 06 Jul 2006
Posts: 183
|
| Posted: Sun Jan 08, 2012 19:52 Post subject: |
|
|
Excellent information.
I've got NWScript nailed down, but I'm still learning the best way to use SQL. So I really appreciate your help. Thanks once again |
|
| Back to top |
|
|
Script Wrecked
Joined: 15 Apr 2012
Posts: 4
|
| Posted: Mon Apr 16, 2012 13:41 Post subject: |
|
|
As per this post, in the example above, the priming read should use "-" rather than "FETCHMODE":
| Code: | SQLExecDirect("SELECT data, foo, bar FROM table");
object oObject = RetrieveCampaignObject("NWNX", "-", lLocation, OBJECT_INVALID);
while(GetIsObjectValid(oObject))
{
int nFoo = StringToInt(SQLGetData(2));
int nBar = StringToInt(SQLGetData(3));
oObject = RetrieveCampaignObject("NWNX", "FETCHMODE", lLocation, OBJECT_INVALID);
} |
|
|
| Back to top |
|
|
|
|
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
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
