| View previous topic :: View next topic |
| Author |
Message |
Belsirk
Joined: 04 Sep 2009
Posts: 16
|
 Posted: Mon Dec 14, 2009 9:46 Post subject: Trying to convert Code for SQLite to MySQL... |
 |
|
I was using a SQLite but see i should leave the otpion to use MySQL on the code (Not at same time, but using a simple Boolean i choice which lines to execute, for take on care the special MySQL language like MyIsam and others )
Right now, the DB work fine as should be, but when i'm trying to get a display of all the info on the DB mean a GUI i'm getting a problem
The code by default work fine with SQLite so , I'm having problem with MySQL code only
Everything begin with this line:
| Code: | SELECT count(*) FROM (SELECT * FROM antimagic_DB GROUP BY owner) AS group_antimagic_DB
* Returning: 2 (column 0) |
Until here, everything it's fine, but when begin to work with that begin the problems
| Code: | if (SQLFetch() == SQL_SUCCESS)
{
//This is the total Owners on the DB
iFilas = StringToInt( SQLGetData(1) );
SetLocalInt(oInventario, Owners_Number, iFilas);
}
sSQL = "SELECT owner FROM " + sqliteDB_Antimagic + " GROUP BY owner";
SQLExecDirect(sSQL);
// The Index is already there then we have an error.
if (SQLFetch() == SQL_SUCCESS)
{
for ( iC2 = 0; iC2 < iFilas; iC2++)
{
//We only take a ONE VALUE from those columns
sSQL = SQLGetData(1);
//Code using the sSQL result ...
/// more code...
//Next row
SQLFetch("NEXT");
}
}
|
Here, the For get killed... because the only thing i'm getting from MySQL
it's the next:
That last line | Code: | * Executing: SELECT owner FROM antimagic_DB GROUP BY owner
* Returning: SERVERVAULTBelSirknamlihakenk (column 0) |
Where i only get one of two values... now, i already pick that line (The select) and put on the MySQL Query Browser (Download on the MySQL 5.0 site) for see which was the problem but... i don't see any problem, using that thing i get two values instead only one ...
I must be forgetting something but after 4 hours with the same error and being midnight i already can't get the solution  |
|
| Back to top |
|
 |
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Dec 14, 2009 13:09 Post subject: |
|
|
try using
SELECT DISTINCT owner FROM <table>
from the looks of it your missing some code before your 'snippet'
it also looks like your running SQL within SQL (which won't work in NWNX)
how does iFilas relate to your SELECT owner query?
Cheers
Gryphyn |
|
| Back to top |
|
|
Belsirk
Joined: 04 Sep 2009
Posts: 16
|
| Posted: Mon Dec 14, 2009 21:34 Post subject: |
|
|
| Gryphyn wrote: | try using
SELECT DISTINCT owner FROM <table> |
The same problem, on game i'm only getting the first entrie instead of all them (For the example only two). Reading the Iffucuak MySQL manual seem the reason it's the next:
| Quote: | DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY
will in many cases also need a temporary table. |
Again, i should get two resulset from that command but from the game i'm only getting one (the first one)
| Quote: |
from the looks of it your missing some code before your 'snippet'
it also looks like your running SQL within SQL (which won't work in NWNX)
how does iFilas relate to your SELECT owner query?
Cheers
Gryphyn |
iFilas take his value from the resulset of : "SELECT count(*) FROM (SELECT * FROM antimagic_db GROUP BY owner) AS group_antimagic_db which is a number (Total of differents Owner)
With that, i know how many rulesets i should get after executing : SELECT DISTINCT owner FROM antimagic_db
With SQLite that work perfectly but with MySQL it's giving me problem
By the way, i'm new with MySQL (i just download the MySQL manual , read it and begin to work) so ... what do you mean with " running SQL within SQL " ??? |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Tue Dec 15, 2009 0:56 Post subject: |
|
|
Every time SQLExec gets executed a new result set is created, and the old one discarded. So you can't loop though an outer query, and use it for an inner query. (the outer one gets broken)
SQL comes in many forms...you need to use the right form for your chosen database. |
|
| Back to top |
|
|
Belsirk
Joined: 04 Sep 2009
Posts: 16
|
| Posted: Tue Dec 15, 2009 6:23 Post subject: |
|
|
| Code: |
SQLExecDirect("SELECT count(*) FROM (SELECT * FROM " + sqliteDB_Antimagic + " GROUP BY owner) AS group_" + sqliteDB_Antimagic );
//We get how mnay rows we have
if (SQLFetch() == SQL_SUCCESS)
{
//This is the total Owners on the DB
iFilas = StringToInt( SQLGetData(1) );
}
else
{ return "";
}
sSQL = "SELECT DISTINCT owner FROM " + sqliteDB_Antimagic;
SQLExecDirect(sSQL);
// The Index is already there then we have an error.
if (SQLFetch() == SQL_SUCCESS)
{
for ( iC2 = 0; iC2 < iFilas; iC2++)
{
//We only take a ONE VALUE from those columns
sSQL = SQLGetData(1);
//sAux += sSQL;
//Next ruleset
SQLFetch("NEXT");
if (SQLFetch() == SQL_ERROR)
return "";
}
}
return "SUCCESS";
} |
The First SQLExec ("SELECT count(*) FROM (SELECT * FROM ...) work fine, return to me the number of different Owners i have on the DB. (on my tests, the DB have two different owners) and it's saved on iFilas avoiding lost the value on the next SQLExec
The second SQLExec (the one with the distinc) should return me the same number of resulset as the value i have on iFilas, but only return me the first one ...
There is not a third SQLExec, only SQLFetchs(Next) for advance to next resulset (which is still valid because there aren't any other SQLExec) inside from a For which use the value of iFilas.
All the problem I'm having it's with the second SQLExec because only it's returning the first resulset, now this don't happens on the MySQL Query Browser neither when using SQLite
I found some post on this forum about something similar but are almost from 3 years ago, and was saying NWNX 1.6 should support the multiple rulesets ... |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
