| View previous topic :: View next topic |
| Author |
Message |
Epitaffio
Joined: 13 Jul 2010
Posts: 29
Location: Italy
|
 Posted: Tue Dec 13, 2011 16:10 Post subject: Odbc multiple resultset |
 |
|
Hi all, i've searched this along the forum, but with any results..
If i need to do two o more SQLFetch() in a while structure, like this:
| Code: |
sSQL = "SELECT * FROM table1 ";
SQLExecDirect(sSQL);
while(SQLFetch()==SQL_SUCCESS){
sSQL = "SELECT * FROM table2 ";
SQLExecDirect(sSQL);
while(SQLFetch()==SQL_SUCCESS){
//Other Code Line
}
}
|
and i can't or don't want to do a join, how i can accomplish that?
I've read some pappillon's post where he say that the Hashset plugin is for this, but the plugin is for windows only and i'm using linux : (
Any hint? Thanks in advance. |
|
| Back to top |
|
 |
virusman
Joined: 30 Jan 2005
Posts: 1020
Location: Russia
|
| Posted: Tue Dec 13, 2011 23:25 Post subject: |
|
|
Why can't you use a JOIN?
_________________
In Soviet Russia, NWN plays you! |
|
| Back to top |
|
|
Epitaffio
Joined: 13 Jul 2010
Posts: 29
Location: Italy
|
| Posted: Wed Dec 14, 2011 13:58 Post subject: |
|
|
Too many sql and while structure, not linked table or different function in the same while:
| Code: |
string test1(){
string sSQL = "SELECT * from table";
SQLExecDirect(sSQL);
while(SQLFetch()==SQL_SUCCESS){
return SQLGetData(1);
}
}
string test2(){
string sSQL = "SELECT * from table";
SQLExecDirect(sSQL);
while(SQLFetch()==SQL_SUCCESS){
return SQLGetData(1);
}
}
sSQL = "SELECT * FROM table";
SQLExecDirect(sSQL);
while(SQLFetch()==SQL_SUCCESS){
if(){
test1();
}else{
test2();
}
}
|
something like this.. |
|
| Back to top |
|
|
virusman
Joined: 30 Jan 2005
Posts: 1020
Location: Russia
|
| Posted: Wed Dec 14, 2011 15:36 Post subject: |
|
|
Doing nested queries in a loop is a bad practice: you shold avoid this. If one resultset is not enough for you, you're probably doing something wrong. I've yet to see a task that really requires multiple resultsets and can't be optimized to use only one.
If join must depend on some field, just do two left joins, one of them will be NULL. Having extra fields in the resultset won't cost you too much, while nested queries will.
_________________
In Soviet Russia, NWN plays you! |
|
| Back to top |
|
|
Epitaffio
Joined: 13 Jul 2010
Posts: 29
Location: Italy
|
| Posted: Wed Dec 14, 2011 16:55 Post subject: |
|
|
Hum, yes it's what i'm doing at this time, optimizing query, table and code to use only one resultset.. but sometimes the query become too long or/and difficult to manage and it's more simple to do another fetch in another resultset.
So, only for talking, there aren't any ways to do multiple resultset? : (
Ps. the problems for doing multiple fetch are memory related or what? |
|
| Back to top |
|
|
virusman
Joined: 30 Jan 2005
Posts: 1020
Location: Russia
|
| Posted: Wed Dec 14, 2011 19:15 Post subject: |
|
|
No, NWNX database plugin currently doesn't support multiple resultsets. It's not a matter of memory, it just wasn't designed for that because there was no need for this.
If you don't want to have all these long SQL queries in the script, create a view and use it as a simple table.
Hundreds of extra queries will affect performance and cause lag.
_________________
In Soviet Russia, NWN plays you! |
|
| Back to top |
|
|
Epitaffio
Joined: 13 Jul 2010
Posts: 29
Location: Italy
|
| Posted: Wed Dec 14, 2011 20:06 Post subject: |
|
|
| Ok, thanks a lot virusman, kind as everytime! |
|
| Back to top |
|
|
mariasam
Joined: 24 Sep 2013
Posts: 1
|
| Posted: Tue Sep 24, 2013 13:00 Post subject: |
|
|
Thank you very much! Now it works perfectly  
_________________
http://prep4sure.com |
|
| Back to top |
|
|
elven
Joined: 28 Jul 2006
Posts: 259
Location: Germany
|
| Posted: Sat Sep 28, 2013 22:14 Post subject: |
|
|
| Alternatively, use cursors. |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
