How to use db:_begin(), db:_commit(), db:_end() ?

Post Reply
visionok
Posts: 22
Joined: Thu Mar 04, 2021 8:50 am

How to use db:_begin(), db:_commit(), db:_end() ?

Post by visionok »

I want to do a bunch of inserts into a database, if I do them one by one, it takes quite a while (seconds or more,lots of disk thrashing) for relatively few entries (eg 10's of entries). So I surrounded the insertions/updates with db:_begin(), db:_commit(), db:_end()
Code: [show] | [select all] lua
      local dbh = db:get_database("database"), res
      if map_room_count > db_room_count
      then
        update_db = true
        display("Updating DB")
        db:_begin()
        local i, r
        for i, r in pairs(endresult)
        do
          res = db:add(dbh.area_rooms, {room = r, id = i,  area=area.current})
          if res == nil
          then
            display("error adding room")
          end
        end
        db:_commit()
        db:_end()
      end
but that throws up an error attempt to call method '_begin' (a nil value)>

Am I using db:_begin(), db:_commit(), db:_end() for their intended purpose and am I using them correctly?

User avatar
Vadi
Posts: 4943
Joined: Sat Mar 14, 2009 3:13 pm

Re: How to use db:_begin(), db:_commit(), db:_end() ?

Post by Vadi »

Hey - check out https://wiki.mudlet.org/w/Manual:Scripting#Transactions, you're supposed to use the actual db name in there.

I've updated the examples to be clearer :)

visionok
Posts: 22
Joined: Thu Mar 04, 2021 8:50 am

Re: How to use db:_begin(), db:_commit(), db:_end() ?

Post by visionok »

Yeah, the updates are much quicker now, thanks.
Code: [show] | [select all] lua
      if map_room_count > db_room_count
      then
        update_db = true
        display("Updating DB")
        dbh:_begin()
        local i, r
        for i, r in pairs(endresult)
        do
          res = db:add(dbh.area_rooms, {room = r, id = i,  area=area.current})
          if res == nil
          then
            display("error adding room")
          end
        end
        dbh:_commit()
        dbh:_end()
      end

User avatar
Vadi
Posts: 4943
Joined: Sat Mar 14, 2009 3:13 pm

Re: How to use db:_begin(), db:_commit(), db:_end() ?

Post by Vadi »

Glad you got it working!

Post Reply