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 
 
SQLExecDirect & INSERT INTO not working
Goto page Previous  1, 2
 
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related
View previous topic :: View next topic  
Author Message
Acrodania



Joined: 02 Jan 2005
Posts: 208
PostPosted: Tue Oct 11, 2005 23:59    Post subject: Reply with quote
That could be part of your problem, NWNX's system is based on 3.0.8

There are some changes between versions....
Back to top
View user's profile Send private message
monezz



Joined: 03 Jan 2005
Posts: 52
Location: The Netherlands
PostPosted: Wed Oct 12, 2005 2:08    Post subject: Reply with quote
from: http://www.sqlite.org/faq.html#q1
Quote:

(1) How do I create an AUTOINCREMENT field.

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

Here is the long answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. (If the largest possible integer key, 9223372036854775807, then an unused key value is chosen at random.) For example, suppose you have a table like this:

CREATE TABLE t1(
a INTEGER PRIMARY KEY,
b INTEGER
);

With this table, the statement

INSERT INTO t1 VALUES(NULL,123);

is logically equivalent to saying:

INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);


Seems like SQLite doesnt need the AUTOINCREMENT keyword.
Strange though you can use it to create an invalid table with it.
Could even be a SQLite bug in that case.

Edit:
Found some bugs in the SQLite bugtracker you could be dealing with.
http://www.sqlite.org/cvstrac/search?s=autoincrement&t=1&c=1&w=1
I don't know if its possible to upgrade the SQLite to a newer version, but if so you could try that too see if it solves you problem.
Back to top
View user's profile Send private message Visit poster's website
Pyridon



Joined: 07 Oct 2005
Posts: 9
PostPosted: Wed Oct 12, 2005 21:10    Post subject: Reply with quote
EDIT: success at last ! I created the table without explicit autoincrement but with 3.2.7 client. The implicit autoincrement still works in 3.2.7, and FINALLY my module performed the select phrase correctly, YAY !

Thanks to all involved... now I am brave enough to try inserting from NWN Smile

---------------------------------------

I am cursed...

Or at least my NWNX/ODBC attempts are doomed. Sad

Acrodania, you are correct: when I downloaded sqlite client version 2.8.16, I got the same message about syntax error I've been complaining about.

Monezz, you are correct: SQLite (version 2.8.16) handles the implicit autoincrement just the way your quoted code states. I'm just curious, why do you say "you can use it to create an invalid table with it" ? I can only think of two simultaneous inserts which both select the last existing id and create two records with same ID. But is this really possible, after all, NWN has gotta be single-threaded when it comes to executing scripts, doesn't it ?

But, my problems aren't still solved. So, I created the new database and new pcdata table with following sql:
Code:

CREATE TABLE pcdata(
 pcid INTEGER PRIMARY KEY,
 pubcdkey varchar(8) NOT NULL,
 playername varchar(64) NOT NULL,
 pcname varchar(64) NOT NULL,
 nvisits int(5) DEFAULT 1,
 lastlogin timestamp(14) DEFAULT NULL
);


Then I inserted a record:
Code:

INSERT INTO pcdata VALUES(NULL,'XXXXXXXX','Pyridon','Fallon Woods',1,NULL);

And then did a select, and got a result:
Code:

sqlite> select * from pcdata;
1|XXXXXXXX|Pyridon|Fallon Woods|1|


Now I tried my script, and stumbled into another error:
Code:

o Got request: SELECT pcid FROM pcdata WHERE pubcdkey='XXXXXXXX' AND pcname='Fallon Woods';
! SQL Error: file is encrypted or is not a database
o Sent response (0 bytes):


And now I am stumped again. Now, the file isn't damn sure encrypted, and how cannot it be a database ? Another version mismatch ?

The trouble is, that only client versions I can get from SQLite site are 2.8.16 and 3.2.7. Acrodania stated that the NWNX is based on version 3.0.8, which I cannot get.

Now what ? Sad
Back to top
View user's profile Send private message
Acrodania



Joined: 02 Jan 2005
Posts: 208
PostPosted: Wed Oct 12, 2005 21:36    Post subject: Reply with quote
Here is a link to the elusive 3.0.8....

http://nurrana.radcon.org/Downloads/sqlite.zip

Luck!
Back to top
View user's profile Send private message
Pyridon



Joined: 07 Oct 2005
Posts: 9
PostPosted: Wed Oct 12, 2005 21:40    Post subject: Reply with quote
*Pyridon pops a bottle of Champagne

It finally works ! Both select and insert work okay, AT LAST.

Now that the ice is broken, it's just work, work work... Smile

Once again thanks for you all who helped... I wouldn't have gotten this far without.
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431
PostPosted: Wed Oct 12, 2005 22:55    Post subject: Reply with quote
Not sure if this will have any effect, but in some databases when you define a PRIMARY KEY column, you also need to define the column as NOT NULL. This might be effecting how the AUTOINCREMENT is functioning. (just a guess)

[Ed: Your version issues where posted while I was typing]

From thue purist point of view having a PRIMARY KEY AUTOINCREMENT is bad.
A FULL_TABLE_SCAN is done on each INSERT as you are using different columns as your INSERT key. As each new player connects it takes longer and longer to insert each record.
Far better to have a PRIMARY KEY on your INSERT columns, and an UNIQUE KEY on your AUTOINCREMENT column.

PRIMARY KEY (pubcdkey,playername,pcname)
UNIQUE KEY (pcid)

You get the benefit that when a PC connects (with PRIMARY KEY info) you can later access the row using their id (with UNIQUE KEY info), both methods using an INDEX, rather than scanning through the entire table each time.

Rather than two table scans (PC exists?, Insert PC) you have two index lookups.
This will reduce the 'Client Connection' stresses that are already part of NWN.

You'll find tha same applies for most situations where you have some data that you want to access via an ID, but you don't have the ID to start with.

Cheers
Gryphyn
Back to top
View user's profile Send private message
Pyridon



Joined: 07 Oct 2005
Posts: 9
PostPosted: Thu Oct 13, 2005 7:16    Post subject: Reply with quote
Yeppers... I wasn't too concerned about the performance at this point, the most important thing was to get the whole thing working in the first place.

I'll add the unique key. Not that it's gonna matter much, as most likely it's never gonna be online... I like to try out things, but don't have enough drive or time to complete anything as big as a PW.
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 Previous  1, 2
Page 2 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