data base aps sql errors

[Greyfort]

odbc log file error

NWNX ODBC2 plugin V.0.9.2.4
(c) 2005 by Ingmar Stieger (Papillon) and Jeroen Broekhuizen
visit us at http://www.nwnx.org

o Logfile maximum size limit is: 524288 bytes
o Log level: Everything will be logged.
o Using ODBC connection.
o Hooking SCO....hooked at 5d5830
o Hooking RCO....hooked at 5d5710
o Connect successful.
o Got request: SELECT month FROM AO_save_Calendar WHERE year='1' AND month='1'
o Sent response (1 bytes): 1
o Got request: SELECT year,month,day,hour,min,varunique FROM AO_save_Calendar WHERE val='CURRENT'
! SQL Error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
o Empty set
o Got request: SELECT year,month,day,hour,min,varunique FROM AO_save_Calendar WHERE val='CURRENT'
! SQL Error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
o Empty set
o Got request: SELECT playername FROM AO_save_PC_Stats WHERE cdkey='CHECK' AND playername='Y'
o Sent response (1 bytes): Y
o Got request: SELECT varvalue FROM AO_save_PC_Stats WHERE playername='Greyfort1' AND charname='dbhopeful' AND varname='LOCATION'
o Sent response (100 bytes): #AREA#vastdesert#POSITION_X# 19.111804962#POSITION_Y# 5.111123085#POSITION_Z# 0.000
o Disconnecting from database.

include file: base

The part of script that checks for a calender table:

// CHECK IF CALENDER EXISTS. IF NOT CREATE IT

void CheckCalendrTable(string sTable);



//=========================================================
// CalendarTable
// greyfort
//////////////////////////////////

// CHECK TO SEE IF TABLE EXISTS
void CheckCalendrTable(string sTable) {
object oDM = GetObjectByTag("SaveCountHolder");//GetModule();//GetObjectByTag("SaveCountHolder");
string sSQL;

if (GetLocalString(oDM, sTable) != "Y") {
// CHECK TABLE FOR POSITIVE ID
sSQL = "SELECT month FROM "+sTable+
" WHERE year='1' AND month='1'";
SQLExecDirect(sSQL);

if (SQLFetch() == SQL_ERROR) {
// CREATE A NEW TABLE
SQLExecDirect("DROP TABLE "+sTable);
SQLExecDirect("CREATE TABLE "+sTable+" (" +
"year text(4)," +
"month text(2)," +
"day text(2)," +
"hour text(2)," +
"min text(2)," +
"varunique text(10))");

// ADD POSITIVE CHECK RECORD
sSQL = "INSERT INTO " + sTable + " " +
"(year,month) VALUES('1','1')";
SQLExecDirect(sSQL);
}
SetLocalString(oDM, sTable, "Y");
}
}
// greyfort
//=========================================================

actual function that get/stores data

////////////////////////////////////////////////////////////////////////////////
//
// DATE AND TIME
//

//STORE THE CURRENT DATE AND TIME
void SetDBdatetime() {
object oDM = oSQLobj;
string sCalendarTable = sMOD_PREFIX + "Calendar";
string sSQL;

if (GetLocalString(oDM, "INIT_DATE") != "Y")
return;
string theYear = IntToString(GetCalendarYear());
string theMonth = IntToString(GetCalendarMonth());
string theDay = IntToString(GetCalendarDay());
string theHour = IntToString(GetTimeHour());
string theMin = IntToString(GetTimeMinute());
string theUnqe = IntToString(Random(7));

if (iPersistenceActive == FALSE)
return;

// SAVE DATE AND TIME TO THE DATABASE
if (iNWNX2active == TRUE) {
string sSQLheader = "UPDATE "+sCalendarTable+" SET ";
string sSQLfooter = "' WHERE val='CURRENT'";

SQLExecDirect(sSQLheader + "year='" + theYear +"'");
SQLExecDirect(sSQLheader + "month='" + theMonth +"'");
SQLExecDirect(sSQLheader + "day='" + theDay +"'");
SQLExecDirect(sSQLheader + "hour='" + theHour +"'");
SQLExecDirect(sSQLheader + "min='" + theMin +"'");
SQLExecDirect(sSQLheader + "varunique='" + theUnqe +"'");

}
}

//GET THE CURRENT DATE AND TIME
void GetDBdatetime() {
object oDM = oSQLobj;
string sCalendarTable = sMOD_PREFIX + "Calendar";
string sSQL;
int theYear = 0;
int theMonth = 0;
int theDay = 0;
int theHour = 0;
int theMin = 0;
int theUnqe = 0;
int iErr = FALSE;

if (iPersistenceActive == FALSE)
return;

// PULL DATA FROM SQL DATABASE IF IT IS ACTIVE
if (iNWNX2active) {
sSQL = "SELECT year,month,day,hour,min,varunique FROM " + sCalendarTable + " WHERE val='CURRENT'";
SQLExecDirect(sSQL);
if (SQLFetch() == SQL_SUCCESS){
theYear = StringToInt(SQLGetData(1));
theMonth = StringToInt(SQLGetData(2));
theDay = StringToInt(SQLGetData(3));
theHour = StringToInt(SQLGetData(4));
theMin = StringToInt(SQLGetData(5));
theUnqe = StringToInt(SQLGetData(6));

// SET THE DATE AND TIME IN THE MODULE
SetCalendar(theYear, theMonth, theDay);
SetTime(theHour, theMin, 0, 0);
} else {
iErr = TRUE;
}
}

// SAVE THE DATE AND TIME IF ERROR WAS FOUND
if (iErr == FALSE)
SetLocalString(oDM, "INIT_DATE", "Y");
}

What am i doing wrong in my check table function that will match with
void GetDBdatetime() and void SetDBdatetime()

also forgot im useing ms accsess i belive it comes with windows xp, I havent been able to find mysql or sqllite, and i would like to get it to work. though i dont have ms accsess at all. i do have mdb viewer plus 163

[Gryphyn]

// SAVE DATE AND TIME TO THE DATABASE
if (iNWNX2active == TRUE) {
string sSQLheader = "UPDATE "+sCalendarTable+" SET ";
string sSQLfooter = "' WHERE val='CURRENT'";

SQLExecDirect(sSQLheader + "year='" + theYear +"'");
SQLExecDirect(sSQLheader + "month='" + theMonth +"'");
SQLExecDirect(sSQLheader + "day='" + theDay +"'");
SQLExecDirect(sSQLheader + "hour='" + theHour +"'");
SQLExecDirect(sSQLheader + "min='" + theMin +"'");
SQLExecDirect(sSQLheader + "varunique='" + theUnqe +"'");


Looks like you're missing the sSQLfooter bit on your updates.
This may be updating more rows than you want. (?)

[Greyfort]

Thanks Gryphyn I added the footer to the area suggested here is what i got

NWNX ODBC2 plugin V.0.9.2.4
(c) 2005 by Ingmar Stieger (Papillon) and Jeroen Broekhuizen
visit us at http://www.nwnx.org

o Logfile maximum size limit is: 524288 bytes
o Log level: Everything will be logged.
o Using ODBC connection.
o Hooking SCO....hooked at 5d5830
o Hooking RCO....hooked at 5d5710
o Connect successful.
o Got request: SELECT month FROM AO_save_Calendar WHERE year='1' AND month='1'
! SQL Error: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'AO_save_Calendar'. Make sure it exists and that its name is spelled correctly.
o Empty set
o Got request: DROP TABLE AO_save_Calendar
! SQL Error: [Microsoft][ODBC Microsoft Access Driver] Table 'AO_save_Calendar' does not exist.
o Got request: CREATE TABLE AO_save_Calendar (year text(4),month text(2),day text(2),hour text(2),min text(2),varunique text(10))
o Got request: INSERT INTO AO_save_Calendar (year,month) VALUES('1','1')
o Got request: SELECT year,month,day,hour,min,varunique FROM AO_save_Calendar WHERE val='CURRENT'
! SQL Error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
o Empty set
o Disconnecting from database.

this is my log error

this is my check calander function
//
// CHECK IF CALENDER EXISTS. IF NOT CREATE IT

void CheckCalendrTable(string sTable);



//=========================================================
// CalendarTable
// CHECK TO SEE IF TABLE EXISTS

void CheckCalendrTable(string sTable) {
object oDM = GetObjectByTag("SaveCountHolder");//GetModule();//GetObjectByTag("SaveCountHolder");
string sSQL;

if (GetLocalString(oDM, sTable) != "Y") {
// CHECK TABLE FOR POSITIVE ID
sSQL = "SELECT month FROM "+sTable+
" WHERE year='1' AND month='1'";
SQLExecDirect(sSQL);

if (SQLFetch() == SQL_ERROR) {
// CREATE A NEW TABLE
SQLExecDirect("DROP TABLE "+sTable);
SQLExecDirect("CREATE TABLE "+sTable+" (" +
"year text(4)," +
"month text(2)," +
"day text(2)," +
"hour text(2)," +
"min text(2)," +
"varunique text(10))");

// ADD POSITIVE CHECK RECORD
sSQL = "INSERT INTO " + sTable + " " +
"(year,month) VALUES('1','1')";
SQLExecDirect(sSQL);
}
SetLocalString(oDM, sTable, "Y");
}
}
// greyfort
//=========================================================

these are my function for geting and setting db date what have I dong wrong or what am i missing?

////////////////////////////////////////////////////////////////////////////////
//
// DATE AND TIME
//

//STORE THE CURRENT DATE AND TIME
void SetDBdatetime() {
object oDM = oSQLobj;
string sCalendarTable = sMOD_PREFIX + "Calendar";
string sSQL;

if (GetLocalString(oDM, "INIT_DATE") != "Y")
return;
string theYear = IntToString(GetCalendarYear());
string theMonth = IntToString(GetCalendarMonth());
string theDay = IntToString(GetCalendarDay());
string theHour = IntToString(GetTimeHour());
string theMin = IntToString(GetTimeMinute());
string theUnqe = IntToString(Random(7));

if (iPersistenceActive == FALSE)
return;

// SAVE DATE AND TIME TO THE DATABASE
if (iNWNX2active == TRUE) {
string sSQLheader = "UPDATE "+sCalendarTable+" SET ";
string sSQLfooter = "' WHERE val='CURRENT'";

SQLExecDirect(sSQLheader + "year='" + theYear + sSQLfooter);
SQLExecDirect(sSQLheader + "month='" + theMonth + sSQLfooter);
SQLExecDirect(sSQLheader + "day='" + theDay + sSQLfooter);
SQLExecDirect(sSQLheader + "hour='" + theHour + sSQLfooter);
SQLExecDirect(sSQLheader + "min='" + theMin + sSQLfooter);
SQLExecDirect(sSQLheader + "varunique='" + theUnqe + sSQLfooter);

}
}

//GET THE CURRENT DATE AND TIME
void GetDBdatetime() {
object oDM = oSQLobj;
string sCalendarTable = sMOD_PREFIX + "Calendar";
string sSQL;
int theYear = 0;
int theMonth = 0;
int theDay = 0;
int theHour = 0;
int theMin = 0;
int theUnqe = 0;
int iErr = FALSE;

if (iPersistenceActive == FALSE)
return;

// PULL DATA FROM SQL DATABASE IF IT IS ACTIVE
if (iNWNX2active) {
sSQL = "SELECT year,month,day,hour,min,varunique FROM " + sCalendarTable + " WHERE val='CURRENT'";
SQLExecDirect(sSQL);
if (SQLFetch() == SQL_SUCCESS){
theYear = StringToInt(SQLGetData(1));
theMonth = StringToInt(SQLGetData(2));
theDay = StringToInt(SQLGetData(3));
theHour = StringToInt(SQLGetData(4));
theMin = StringToInt(SQLGetData(5));
theUnqe = StringToInt(SQLGetData(6));

// SET THE DATE AND TIME IN THE MODULE
SetCalendar(theYear, theMonth, theDay);
SetTime(theHour, theMin, 0, 0);
} else {
iErr = TRUE;
}
}

// SAVE THE DATE AND TIME IF ERROR WAS FOUND
if (iErr == FALSE)
SetLocalString(oDM, "INIT_DATE", "Y");
}

[Greyfort]

ok I have probbly havent given enough info...
what im tryn to do is ...

1) if no sMOD_PREFIX + "Calendar" table, then create add int to insure it works, then void SetDBdatetime() *this is a one time action for first time running module*

2) if sMOD_PREFIX + "Calendar" table, then on a void GetDBdatetime() get it updat module running

also should I call for the db table check before my set and get function? I was thinking not...since I want the mods calander to be data base driven.
I only want it to create the table once for the first time mod starts so when I give the source to others they can use it without any....

o Got request: SELECT month FROM AO_save_Calendar WHERE year='1' AND month='1'
! SQL Error: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'AO_save_Calendar'. Make sure it exists and that its name is spelled correctly.
o Empty set

errors like make sure it exists etc

in regaurds to the the already posted errors, I want it to update table over data that is there not make a new row, am I making my void CheckCalendrTable function wrong? I keep trying to get the current to work right with no success.

any help would be a refreshing mental break *wipes hair of the forums...A look of supprise on his face..."I think im looseing hair over the stress"...*

[Gryphyn]

SELECT COUNT(*) as table_Found FROM MsysObjects
WHERE [Type]=1 AND [Name] = 'MyTableName'

Should be the access syntax for a table. (google search)

Your create table is also missing the val column what should exists for the WHERE val='CURRENT' part of the query (or was this meant to be varunique?)

CREATE TABLE AO_save_Calendar (year text(4),month text(2),day text(2),hour text(2),min text(2),varunique text(10), val text(100))
INSERT INTO AO_save_Calendar (year,month,val) VALUES('1','1','CURRENT')

Cheers
Gryphyn

[Greyfort]

thanks Gryphyn I tried again fixing my check calender table here is what I got...

nwnx log error
o Got request: SELECT year FROM AO_save_Calendar WHERE val='CURRENT' and year='1'
o Empty set
o Got request: DROP TABLE AO_save_Calendar
o Got request: CREATE TABLE AO_save_Calendar (val text(10),year text(4),month text(2),day text(2),hour text(2),min text(2))
o Got request: INSERT INTO AO_save_Calendar (val,year,) VALUES('CURRENT','1')
! SQL Error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. relooking at post i cought a cumma after year

//=========================================================
// CalendarTable
// CHECK TO SEE IF TABLE EXISTS

void CheckCalendrTable(string sTable) {
object oDM = GetObjectByTag("SaveCountHolder");//GetModule();//GetObjectByTag("SaveCountHolder");
string sSQL;

if (GetLocalString(oDM, sTable) != "Y") {
// CHECK TABLE FOR POSITIVE ID
sSQL = "SELECT year FROM "+sTable+
" WHERE val='CURRENT' and year='1' ";
SQLExecDirect(sSQL);

if (SQLFetch() == SQL_ERROR) {
// CREATE A NEW TABLE
SQLExecDirect("DROP TABLE "+sTable);
SQLExecDirect("CREATE TABLE "+sTable+" (" +
"val text(10),"+
"year text(4)," +
"month text(2)," +
"day text(2)," +
"hour text(2)," +
"min text(2))");

// ADD POSITIVE CHECK RECORD
sSQL = "INSERT INTO " + sTable + " " +
"(val,year,) VALUES('CURRENT','1')";
SQLExecDirect(sSQL);
}
SetLocalString(oDM, sTable, "Y");
}
}
// greyfort
//=========================================================

In regaurds to your question...
INSERT INTO AO_save_Calendar (year,month,val) VALUES('1','1','CURRENT')
I would like...
table to read ( CURRENT,year ,month ,day ,hour ,min)
I used following insert as seen above yet I got a insert error?
INSERT INTO AO_save_Calendar (val,year) VALUES('CURRENT','1')

...thats not right hmm, i will right back with error or edit this post...
... yep pesk cumma time to stop lol i cant see thanks again Gryphyn

[Gryphyn]

For the INSERT;
The order (and number) of columns MUST match the order (and number) of values however this does not need to match the order of the columns in the table.

[Greyfort]

here is working function:

//
// CHECK IF CALENDER EXISTS. IF NOT CREATE IT

void CheckCalendrTable(string sTable);



//=========================================================
// CalendarTable
// CHECK TO SEE IF TABLE EXISTS

void CheckCalendrTable(string sTable) {
object oDM = GetObjectByTag("SaveCountHolder");//GetModule();//GetObjectByTag("SaveCountHolder");
string sSQL;

if (GetLocalString(oDM, sTable) != "Y") {
// CHECK TABLE FOR POSITIVE ID
sSQL = "SELECT year FROM "+sTable+
" WHERE val='CURRENT' and year='1' ";
SQLExecDirect(sSQL);

if (SQLFetch() == SQL_ERROR) {
// CREATE A NEW TABLE
SQLExecDirect("DROP TABLE "+sTable);
SQLExecDirect("CREATE TABLE "+sTable+" (" +
"val text(10),"+
"year text(4)," +
"month text(2)," +
"day text(2)," +
"hour text(2)," +
"min text(2))");

// ADD POSITIVE CHECK RECORD
sSQL = "INSERT INTO " + sTable + " " +
"(val,year) VALUES('CURRENT','1')";
SQLExecDirect(sSQL);
}
SetLocalString(oDM, sTable, "Y");
}
}
// greyfort
//=========================================================