logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
New Query from within a while loop
Goto page 1, 2  Next
 
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related
View previous topic :: View next topic  
Author Message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Sat Aug 15, 2009 15:14    Post subject: New Query from within a while loop Reply with quote

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
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Sat Aug 15, 2009 15:41    Post subject: Still getting the empty result error Reply with quote

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
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Sat Aug 15, 2009 20:49    Post subject: Syntax Reply with quote

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
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Sun Aug 16, 2009 0:25    Post subject: Reply with quote

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
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Sun Aug 16, 2009 2:19    Post subject: wow Reply with quote

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
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Sun Aug 16, 2009 2:45    Post subject: Re: wow Reply with quote

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
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Sun Aug 16, 2009 2:55    Post subject: Reply with quote

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
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Sun Aug 16, 2009 3:05    Post subject: using this Reply with quote

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
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Sun Aug 16, 2009 3:19    Post subject: Re: using this Reply with quote

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 Wink )

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
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Sun Aug 16, 2009 4:21    Post subject: works but not works?? Reply with quote

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
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Sun Aug 16, 2009 5:22    Post subject: Reply with quote

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
View user's profile Send private message
Asparius



Joined: 18 Sep 2007
Posts: 52

PostPosted: Sun Aug 16, 2009 5:39    Post subject: Reply with quote

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
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Sun Aug 16, 2009 5:40    Post subject: Reply with quote

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
View user's profile Send private message
Asparius



Joined: 18 Sep 2007
Posts: 52

PostPosted: Sun Aug 16, 2009 5:51    Post subject: Reply with quote

Just got that idea but you were faster Smile 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
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Sun Aug 16, 2009 6:12    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related All times are GMT + 2 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
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