New Query from within a while loop

[Baaleos]

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.

[Baaleos]

new code looks like this

[Baaleos]

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.

[Gryphyn]

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.

[Baaleos]

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?

[Gryphyn]

[Baaleos]

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

[Baaleos]

[Gryphyn]

[Baaleos]

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.

[Gryphyn]

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.

[Asparius]

Hmmm...
Maybe syntax like

[Gryphyn]

[Asparius]

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...

[Gryphyn]

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