View previous topic :: View next topic |
Author |
Message |
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Sat Aug 15, 2009 15:14 Post subject: New Query from within a while loop |
|
|
I am re-making the artifact system in my mod, to use nwnx database, opposed to bioware database.
I have it all figured out, except that I am finding something odd happening.
Quote: |
o Got request: DELETE from ARTIFACT_POOL where RESREF = 'item096'
o Got request: Select * from ARTIFACT_POOL
o Sent response (20 bytes): 11rhun_artifact_50
o Got request: UPDATE ARTIFACT_POOL SET ART_NUMBER = 1 where id =1
o Empty set
|
Basically, the way my system is going to work, is that an artifact can be spawned on an npc/creature, and IF that artifact is eventually picked up by a player, it will remove that artifact from the artifact pool. As seen by the delete query.
But, after it is deleted, it is meant to perform a loop, which renumbers all the artifacts in the pool, so that they are numbered properly.
Eg - 50 artifacts in the system, 1 - 50,
if we acquire artifact 34, then it would be removed, but that would leave a gap, between 33 and 35, so, the re-numbering function, does a loop, to renumber them all, with a self incrementing int value.
so, the result should end up with numbering of 1-49, and picking up another artifact, will give 1-48 etc.
Code: |
void RenumberPOOL()
{
int iNum = 1;
string sSQL = "Select * from ARTIFACT_POOL";
SQLExecDirect(sSQL);
while(SQLFetch()==SQL_SUCCESS)
{
string sSQL2 = "UPDATE ARTIFACT_POOL SET ART_NUMBER = "+IntToString(iNum)+" where id ="+SQLGetData(1);
SQLExecDirect(sSQL2);
iNum++;
}
}
|
Im guessing that when I do the second SQLExecDirect, its wiping the results fom the first Exec.
To fix this, do I just have to split this into different functions? Eg - the Select * in this one, and then the re-numbering in a second one? |
|
Back to top |
|
|
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Sat Aug 15, 2009 15:41 Post subject: Still getting the empty result error |
|
|
new code looks like this
Code: |
void ReNumber2(int iNum,string id)
{
//string sID = IntToString(id);
string sNum = IntToString(iNum);
string sSQL2 = "UPDATE ARTIFACT_POOL SET ART_NUMBER = "+sNum+" where id ="+id;
SQLExecDirect(sSQL2);
}
void RenumberPOOL()
{
int iNum = 1;
string sSQL = "Select * from ARTIFACT_POOL";
SQLExecDirect(sSQL);
while(SQLFetch()==SQL_SUCCESS)
{
ReNumber2(iNum,SQLGetData(1));
//string sSQL2 = "UPDATE ARTIFACT_POOL SET ART_NUMBER = "+IntToString(iNum)+" where id ="+SQLGetData(1);
//SQLExecDirect(sSQL2);
iNum++;
}
}
|
Im trying now to change it from select * to select id - just incase the error is caused by the buffer getting filled.
But really it shouldnt be?
All that is getting returned with a select * query is
id = number
artifact number = number
resref = should be no more than 10-15 characters at most.
Anyone got any insight? |
|
Back to top |
|
|
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Sat Aug 15, 2009 20:49 Post subject: Syntax |
|
|
I know there is definitly nothing wrong with my syntax, cause I have managed to get it working now, using a php cron job for the artifact pool's maintenance.
Every 5 minutes, it will re-number the remaining artifacts.
Ideally, I would want this run off the server, but at the minute, cron job is all I can get working. |
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sun Aug 16, 2009 0:25 Post subject: |
|
|
Yep, it's the SQLExec within a SQLExec...
A re-sequence should be doable with a single update statement.
SQLServer allows for
set @ident=0;
update [nwTable] set @ident = [ident] = @ident + 1
For MySQL (5.1+)
near as I can make out you'd need something like
set @row:=0;
update table set (ident := @row),(@row:=@row+1) *or set ident=(@row:=(@row+1)) --from the examples I looked at the later could work.
order by ident
and with grouping
set @group:=0, @row:=0;
set ident=if(@group=(@group:=group),(@row:=@row+1),(@row:=1))
order by group, ident
Reference - comment re: grouping & ranking
Hope this helps. |
|
Back to top |
|
|
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Sun Aug 16, 2009 2:19 Post subject: wow |
|
|
Guessing the forum was having a db problem or something.
I kept having login issues there - kept returning me to the login screen.
anyhow
could you tell me if the syntax for using this command in nwnscript would be as the following?
Code: |
void RenumberPOOL()
{
int iNum = 1;
string sSQL = "set @ART_NUMBER:=1;
update ARTIFACT_POOL(ident := @ART_NUMBER),(@row:=@ART_NUMBER+1) ";
SQLExecDirect(sSQL);
}
|
Im guessing that I would have a 1 instead of a 0, as in your example, since I need to start from 1 and number upwards, and is row ok to be changed to the row name? |
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sun Aug 16, 2009 2:45 Post subject: Re: wow |
|
|
Baaleos wrote: | Guessing the forum was having a db problem or something.
I kept having login issues there - kept returning me to the login screen.
anyhow
could you tell me if the syntax for using this command in nwnscript would be as the following?
Code: |
void RenumberPOOL()
{
int iNum = 1;
string sSQL = "set @ART_NUMBER:=1;
update ARTIFACT_POOL(ident := @ART_NUMBER),(@row:=@ART_NUMBER+1) ";
SQLExecDirect(sSQL);
}
|
Im guessing that I would have a 1 instead of a 0, as in your example, since I need to start from 1 and number upwards, and is row ok to be changed to the row name? |
almost...
Code: | void RenumberPOOL()
{
string sSQL = "set @row:=1;update ARTIFACT_POOL set ART_NUMBER:=@row, @row:=@row+1 order by ART_NUMBER"
SQLExecDirect(sSQL);
} |
.1. @row could be @ART_NUMBER if you wanted. (substitute all occurrences)
.2. not sure if MySQL is self declaring - that is you might need to add 'declare @row int;' (or similar) to the start of sSQL.
.3. you don't nee iNum, as MySQL does all the work here (for every row)
.4. you NEED the 'order by', otherwise MySQL may cause 'duplicate' rows during the update.
.5. I don't have MySQL available, this is all based on the syntax as described in the online doco.
.6. "set @row:=0;update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER" may also work.
Cheers
Gryphyn |
|
Back to top |
|
|
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Sun Aug 16, 2009 2:55 Post subject: |
|
|
declare @row int;
set @row:=1;update ARTIFACT_POOL set ART_NUMBER:=@row, @row:=@row+1 order by ART_NUMBER
just tried feeding the following into mysql via phpmyadmin
got a syntax error at the declare, and when its taken out, it gets a syntax error elsewhere..
mysql server - Server version: 5.0.16-n
MySQL client version: 4.1.7
set @row:=1;update ARTIFACT_POOL set ART_NUMBER:=@row, @row:=@row+1 order by ART_NUMBER
gives me the following error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@row:=@row+1 order by ART_NUMBER' at line 1
had some progress with
set @row:=0;update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER
it didnt error out, instead, it said
Your SQL query has been executed successfully
SET @row :=0;# MySQL returned an empty result set (i.e. zero rows).
UPDATE ARTIFACT_POOL SET ART_NUMBER := ( @row := ( @row +1 ) ) ORDER BY ART_NUMBER# MySQL returned an empty result set (i.e. zero rows).
however, upon re-examination, it is working. its numbering all the rows incrementing by 1.
I tested it with @row+5 and it labelled them via increments of 5 - so its definitly working - thx |
|
Back to top |
|
|
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Sun Aug 16, 2009 3:05 Post subject: using this |
|
|
Code: |
void RenumberPOOL()
{
string sSQL = "set @row:=0;update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER";
SQLExecDirect(sSQL);
}
|
Using this - it should work
Thx again, I did have a look at the documentation for the mysql, but like alot of instruction manuals, its nothing compared to experienced users who actually know the answer to what we need. |
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sun Aug 16, 2009 3:19 Post subject: Re: using this |
|
|
Baaleos wrote: | Code: |
void RenumberPOOL()
{
string sSQL = "set @row:=0;update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER";
SQLExecDirect(sSQL);
}
|
Using this - it should work
Thx again, I did have a look at the documentation for the mysql, but like alot of instruction manuals, its nothing compared to experienced users who actually know the answer to what we need. |
Yes, the doco has many 'missing' explanations. I got the above from reading the comments posted below the 'update' syntax.
Yes, there is no result set. The SQLExecte (MySQL) returns a row count (affected rows)- I believe there is a NWNX option to get the row count - might be useful if you want an updated record count (to store as a local)
Code: | "set @row:=0;update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER";select row_count(); | should return a result set with the number of rows updated (all of them )
and a note on performance - 10,000+ rows and you might see some lag. Keep it in the 100-200 range and you should have no problems (even with several re sequences occurring.) |
|
Back to top |
|
|
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Sun Aug 16, 2009 4:21 Post subject: works but not works?? |
|
|
set @row:=0;update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER typed into phpmyadmin - works
however, the following are appearing in my odbc logs.
Quote: |
o Got request: DELETE from ARTIFACT_POOL where RESREF = 'ringsofwardin001'
o Got request: set @row:=0;update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER
! SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';update ARTIFACT_POOL set ART_NUMBER:=(@row:=(@row+1)) order by ART_NUMBER' at line 1
|
Im guessing it doesnt like the ; character infront of the update.
Do I need to somehow put in a new line character or something?? |
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sun Aug 16, 2009 5:22 Post subject: |
|
|
Not sure... Could be many things...
It is likely it's the multi-statement that's causing the issue.
you could put it into a stored proc, and call the stored proc.
As always what should/does work with the database, doesn't always work with NWNX as it uses 'client' code that may be from an older version, and doesn't allow the syntax. (although this shouldn't be the case)
you could always add it as an OnDelete trigger for you table
(resequence - rows => than the one deleted) avoids nwScript altogether.
EDIT: Default Behaviour
NWNX was written before these options were available.
20.9.12. C API Support for Multiple Statement Execution
By default, mysql_query() and mysql_real_query() interpret their statement string argument as a single statement to be executed, and you process the result according to whether the statement produces a result set (a set of rows, as for SELECT) or an affected-rows count (as for INSERT, UPDATE, and so forth).
MySQL 5.0 also supports the execution of a string containing multiple statements separated by semicolon (“;”) characters. This capability is enabled by special options that are specified either when you connect to the server with mysql_real_connect() or after connecting by calling` mysql_set_server_option().
20.9.3.64. mysql_set_server_option()
int mysql_set_server_option(MYSQL *mysql, enum enum_mysql_set_option option)
Description
Enables or disables an option for the connection. option can have one of the following values.
MYSQL_OPTION_MULTI_STATEMENTS_ON Enable multiple-statement support
MYSQL_OPTION_MULTI_STATEMENTS_OFF Disable multiple-statement support
If you enable multiple-statement support, you should retrieve results from calls to mysql_query() or mysql_real_query() by using a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section 20.9.12, “C API Support for Multiple Statement Execution”.
Enabling multiple-statement support with MYSQL_OPTION_MULTI_STATEMENTS_ON does not have quite the same effect as enabling it by passing the CLIENT_MULTI_STATEMENTS flag to mysql_real_connect(): CLIENT_MULTI_STATEMENTS also enables CLIENT_MULTI_RESULTS. If you are using the CALL SQL statement in your programs, multiple-result support must be enabled; this means that MYSQL_OPTION_MULTI_STATEMENTS_ON by itself is insufficient to allow the use of CALL. |
|
Back to top |
|
|
Asparius
Joined: 18 Sep 2007 Posts: 52
|
Posted: Sun Aug 16, 2009 5:39 Post subject: |
|
|
Hmmm...
Maybe syntax like
Code: |
SQLExecDirect("UPDATE ARTIFACT_POOL SET ART_NUMBER = ART_NUMBER - 1 WHERE ART_NUMBER > 34)
|
After deleting 34th row would be a good solution? |
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sun Aug 16, 2009 5:40 Post subject: |
|
|
Asparius wrote: | Hmmm...
Maybe syntax like
Code: |
SQLExecDirect("UPDATE ARTIFACT_POOL SET ART_NUMBER = ART_NUMBER - 1 WHERE ART_NUMBER > 34)
|
After deleting 34th row would be a good solution? |
still need the 'order by' but a workable alternative.
Last edited by Gryphyn on Sun Aug 16, 2009 5:54; edited 1 time in total |
|
Back to top |
|
|
Asparius
Joined: 18 Sep 2007 Posts: 52
|
Posted: Sun Aug 16, 2009 5:51 Post subject: |
|
|
Just got that idea but you were faster Though it should not hurt unless ART_NUMBER is a primary key.
In multi-user environment maybe locking table for this query would prevent problems... |
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sun Aug 16, 2009 6:12 Post subject: |
|
|
Personally, I use my SQLServer plugin.
I've got rid of all these 'restrictions'.
1. full multi-resultset support, (in-fact, added query context as well (up to 4 levels of results being used for further querying).
2. simplified stored procedure access.
3. parameter binding (including in-place binding of objects - no special needs)
In this case I'd just call a stored proc, that would do the random selection, deletion and re-sequence as a single nwScript call.
object pickArtifact() {...}
in-fact this is essential functionality in my vendor system.
Cheers
Gryphyn |
|
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
|