SQLExecDirect & INSERT INTO not working

[Pyridon]

Okay, I am an NWNX noob, not so noob what comes to NWN scripting and databases. I am using SQLite and I made my own player data table and tried to insert into it, but that didn't succeed, and I don't know why. I tried the SQL scripts in SQLite command line client and they worked okay, but not from my module script.

Here's the table creation command:

[Pyridon]

Gee, I found nwnx logfile. It states this:

o Got request: SELECT pcid FROM pcdata WHERE pubcdkey='XXXXXXXX' AND pcname='Fallon Woods';
! SQL Error: malformed database schema - near "AUTOINCREMENT": syntax error
o Sent response (0 bytes):

Now why does nwnx complain of this ? The schema is okay, like I said... I can do inserts outside NWN just fine.

[Acrodania]

You have 6 columns in your database, your SQL statement only has 4 of them.....

With your INSERT statement you must account for all columns.

You are getting THAT error because when the insert runs you are trying to put the public CDKey in column one. The CDKey is alpha-numeric; autoincrement can only work with integers.....

You also don't physically put the trailing ; in the SQL string, NWNX will add it for you automatically when it runs. By putting that in you are cutting your SQL string off at that point as NWScript views it as its own end....

[Pyridon]

[Acrodania]

It is a valid statement, but NOT for the table you listed!!!!

[Gryphyn]

Your not terminating your values (...)
You need

[Pyridon]

[Papillon]

NWNX ODBC2 does nothing special to the SQL string. If it works in whatever program you use but not in NWN, there might be a version mismatch.
_________________
Papillon

[Acrodania]

[Gryphyn]

[Lokey]

[odenien]

If you guys are still talking about the insert statement, Gryphyn was correct, it is not valid. Insert statements have to have both the column list and the data list surrounded by parentheis. Auto increment, if a database supports it is not added the insert list, nor is the data.

Normally, what I do is add a DebugPrint right before each sql statement that prints out to the logs. If something does not work, I will copy the statement to ODBCTest on windows and run the same statement.

[monezz]

The column list is optional if you provide all values.

example:
create table test (id int primary key auto_increment, value int);

valid insert statement:
insert into test values (1,1);
insert into test values (null,2); -- null will be replaced by the auto_increment value
insert into test (id,value) values (3,3);
insert into test (value) values (4); -- id will get the value from auto_increment

[odenien]

One other thing to note, if you do supply a column list, all NOT NULL columns without defaults must be supplied. Sort of obvious if you do this for a living, but not to everyone.

[Pyridon]