world.DatabaseLastInsertRowid("dbName") can't get rowid

Posted by Spark on Fri 28 Sep 2012 01:22 AM — 8 posts, 33,801 views.

Spark Fri 28 Sep 2012 01:22 AM #0


CREATE TABLE IF NOT EXISTS  rooms(roomid INTEGER NOT NULL PRIMARY KEY autoincrement,
roomname  TEXT NOT NULL,roomexits TEXT,
roomuniquelevel INTEGER DEFAULT 1, 
roomtype INTEGER DEFAULT 1,
roomdesc TEXT,roomext TEXT)
.......

insert into rooms(roomname,roomexits,roomtype,roomuniquelevel,roomdesc,roomext) 
values('testroom','east|north',1,1,'hello room','test');
......
var ret = world.DatabaseExec("dbName",sql);
		var _retNum = world.DatabaseLastInsertRowid("dbName");


world.note("ret is "+ ret);

window shows : "ret is"
that means after insert is done,and i can view the record in database,but i can't get the roomid's value by call world.DatabaseLastInsertRowid("dbName");
【question】 Am i worry use the function ? or the function has bug in javascript? thanks for help.
【remark】 language:js mushclient version: 4.82

Kyrock Fri 28 Sep 2012 03:41 AM #1

-Just- read the help files on this stuff,so yeah... if you're not getting 0, then DatabaseExec failed in some way and you should use DataBaseError to get what's going on? Also, you did open the database some where before this code right? And, out of curiosity, is it legal to mix up the order of parameters like you are doing, as well as only give 5 of them in an insert?

Nick Gammon Australia Forum Administrator Fri 28 Sep 2012 04:31 AM #2

Testing your code in Lua (with which I am more comfortable) I get this code:



DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)

DatabaseExec ("db", [[
    CREATE TABLE IF NOT EXISTS rooms 
   (roomid INTEGER NOT NULL PRIMARY KEY autoincrement,
   roomname TEXT NOT NULL,
   roomexits TEXT,
   roomuniquelevel INTEGER DEFAULT 1, 
   roomtype INTEGER DEFAULT 1,
   roomdesc TEXT,
   roomext TEXT)

]])

print (DatabaseError("db"))
      
sql = [[ insert into rooms(roomname,roomexits,roomtype,roomuniquelevel,roomdesc,roomext) 
          values('testroom','east|north',1,1,'hello room'); ]]


DatabaseExec("db",sql);

print (DatabaseError("db"))

DatabaseClose ("db")  -- close it

Results:


not an error
5 values for 6 columns

So the create table worked OK, but not the insert. You should be checking for a zero return result from DatabaseExec.

As Kyrock said, you only have 5 values for 6 rows.

Spark Fri 28 Sep 2012 12:57 PM #3


   __sql="insert into rooms(roomname,roomexits,roomtype,roomuniquelevel,roomdesc,roomext) 
values('dd','west|east|north|south',1,2,'hh','')"


doInsert	:	function(__sql){
		var _sql = __sql;
		var _ret = world.DatabaseExec(this.dbName,_sql);
		var _retNum = world.DatabaseLastInsertRowid(this.dbName);
		world.note ("_ret="+_ret+",_retNum="+_retNum+";DatabaseLastInsertRowid = "+ world.DatabaseError(this.dbName));
		
		if(world.DatabaseError(this.dbName)=="not an error"){
			util.todebug ("exec insert SQL:"+__sql);
		}else{
			util.toerror("exec error:"+world.DatabaseError(this.dbName)+";result:"+_ret+";sql="+__sql);
		}
		_ret=null;
		_sql=null;
		return _retNum;
	}


var _db = new DB("mydb");
			_db.openDB();
			_ret=_db.doInsert(_sql);
			_db.closeDB();

and the log is：


_ret=0,_retNum=;DatabaseLastInsertRowid = not an error

so we can see insert sql is well done ,and the return value _ret is 0,and i also can see the record in sqlite,but the rowid _retNum is empty.

Nick Gammon Australia Forum Administrator Fri 28 Sep 2012 10:22 PM #4

You have found a bug. Fixed in version 4.83.

Spark Sat 29 Sep 2012 12:40 AM #5

so waiting ver 4.83.
thanks all.

Nick Gammon Australia Forum Administrator Sat 29 Sep 2012 03:35 AM #6

Version 4.83 now available.

Spark Sat 29 Sep 2012 07:39 AM #7

thanks a lot .