HELP! I'm frustrated with Access

Posted by Katie Love on Sat 17 Jan 2009 04:19 AM — 4 posts, 17,713 views.

Katie Love Sat 17 Jan 2009 04:19 AM #0

For some reason my automapping script is going haywire. After digging around all the logic seems fine.

It's acting as if the code is executing in parallel rather than serial. In other words -- after I do a con:execute to save data in my access database (through a DSN) it's not waiting for the data to be entered before continuing on in the code. This is causing HUGE problems, as the next line does a looking for the ID number of the newly entered room.

Am I right? Is there a way to tell Mushclient to slow down, take it's time and finish one task before going on to another? :-p

Nick Gammon Australia Forum Administrator Sat 17 Jan 2009 05:14 AM #1

Can you give a code snippet that illustrates this? In my test post a while ago (http://www.gammon.com.au/forum/?id=6324) I didn't have any such problems. That post shows an example of creating a table, populating it, and reading it back in, all at full speed.

An example of the code in question would help. Also, are you using Lua, VBscript or what? Generally the script engines run as fast as they can (of course) however some things are naturally asynchronous (eg. pulling in web pages) because they can't complete instantly.

However, again I say that my example on that page used con:execute to add records to a table, and in the very next statement, used con:execute again to select the resulting records back in.

It isn't really a case of telling MUSHclient to slow down - it gets a piece of script code, hands it off to the scripting engine (Lua, VBscript, JScript or whatever) and that engine runs it as it sees fit. I don't have a "please run this more slowly" option.

Katie Love Sat 17 Jan 2009 09:52 PM #2

Nick,

Thanks for the quick reply :-). I am using LUA. That is as I suspected. Once it hands off the database call to the DSN it moves on, so if the next line completes before the SQL command, then you end up with bad data.

A "pseudocode" example of what I was trying to do:

$PreviousRoom = 1
$DirectionMoving = s
sqlquery->"Insert into rooms (RoomName, Detail, n, s, e, w) values (a big room", "lots of detail", ".$PreviousRoom.", 0, 0, 0)"
// id is an autoincrement field
$CurrentRoom = sqlquery->"select id from rooms where RoomName="a big room" and detail="lots of detail""

if $CurRoom != nil then
sqlquery->"Update rooms set s=".$CurrentRoom." WHERE ID=".$PreviousRoom
end if

I hope that makes sense...:-p

So I can't update the previous room with the information for where the exit takes you until I get the query back with the primary key of the new room.

The only solution I can come up with is to use a GUID instead of using an auto increment in Access. This way I can create a new record in the database, and know what the primary key value for the record will be ahead of time.

Sorry if I came across as grumpy in my post. I was a bit frustrated last night. :-p

Nick Gammon Australia Forum Administrator Sat 17 Jan 2009 11:13 PM #3

It would help if you showed actual code rather than pseudo-code. Your code looks like Perl to me, and it is possible the problems are in your coding, rather than the Access engine. I got a test to work with doing roughly what you had, as far as I can tell.


-- load the ODBC dll
assert (package.loadlib ("odbc.dll", "luaopen_luasqlodbc")) ()

-- create environment object
env = assert (luasql.odbc())

-- connect to data source
con = assert (env:connect ("luatest",     -- DSN name
                           "nick",        -- user name
                           "swordfish"))  -- password

-- empty our old table (comment this out the first time around)
assert (con:execute"DROP TABLE rooms")

-- create the rooms table
assert (con:execute[[
  CREATE TABLE rooms(
    ID counter,
    RoomName VARCHAR(50),
    Detail TEXT,
    n INT,
    s INT,
    e INT,
    w INT
  )
]])


-- add a few elements
list = {
  { RoomName="Market", Detail="a bustling market", },
  { RoomName="Shop", Detail="a food shop", },
  
}

for i, p in pairs (list) do
  assert (con:execute(string.format([[
    INSERT INTO rooms (RoomName, Detail)
    VALUES ('%s', '%s')]], p.RoomName, p.Detail)
  ))

 -- find the ID of the room we just added

  cur = assert (con:execute ("SELECT @@IDENTITY" ))
  row = cur:fetch ({}, "n")
  print ("New ID assigned is", row [1])
  cur:close ()

end  -- for loop

What you really want to do, to find the newly assigned record, is to select @@IDENTITY from the database. That returns the most recently-assigned ID, and you can then use that for future operations. In my case, running this in the Immediate window gave this:


New ID assigned is 1
New ID assigned is 2

After the code above you can add this extra stuff to prove it all got added:


-- retrieve a cursor
cur = assert (con:execute ("SELECT * from rooms" ))

-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")

while row do
  print ("\n------ new row ---------\n")
  table.foreach (row, print)
  
  -- reusing the table of results
  row = cur:fetch (row, "a")
end  -- while loop

-- close everything
cur:close()
con:close()
env:close()

This printed for me:


------ new row ---------

ID 1
RoomName Market
Detail a bustling market

------ new row ---------

ID 2
RoomName Shop
Detail a food shop

No problems with the database not being updated in time for the next line of code. :)