mysqltcl: cannot access additional result sets

2012-07-02T05:41:27-04:00

mysqltcl works very well for most of what I need done, but I tend to write procs so that the error trapping can be managed more centrally. So here's the problem. I have procedures that return multiple result sets. I can access the first result set, and I know the additional sets are there because the ::mysql::state says "RESULT_PENDING"

However, I cannot feed it an appropriate query handle to pull the records from the pending results, because I can't find a procedure that returns a new handle to me. In PHP this would be done using something like store_result, but they also have a special method for working with multi-set queries; mysqltcl has no documented implementation of multiquery, and they have no listed procs that return a new query handle; furthermore, the original query handle is, as expected, no longer useful after the last record's been fetched.

I should add here that I have already enabled Multiple Statements and Multiple Results in the connection, and I have even called ::mysql::setserveroption -multi_statment_on as demonstrated in the final procedure post.

The procedure follows. Line 24 (Base 1) is where I'm having the issue. If there's someone here that can help me, or refer me to a reference other than the ones that resemble http://makelinux.org/man/3/M/mysqltcl and http://manpages.ubuntu.com/manpages/pre ... tcl.3.html, I'd be much obliged. It's not a time critical thing, no business functions are going to crash, no chat rooms will be inaccessible; It's just something I'd like to be able to implement in multiple applications.

Thanks in advance!

Code:

proc loadbstriggers {} {  global botscenetriggers  set mysqliCN [getMysqliCN]  set list {}  set sql "call GetBotScene_Triggers();"  putlog "loadbstriggers 1 - sql:$sql"  if {[catch {::mysql::query $mysqliCN $sql} rs] == 1} {    putlog "loadbstriggers 1a - sql:$sql \nError:$rs"  } else {    putlog "loadbstriggers 1b - rs:$rs"    while {[set row [::mysql::fetch $rs]]!=""} {      lassign $row triggerID triggerName      lappend list [list $triggerID $triggerName {}]    }    lset botscenetriggers {} $list    if {[catch {::mysql::moreresult $mysqliCN} more] == 1} {      putlog "loadbstriggers 1c - \nError:$more"    } else {      putlog "loadbstriggers 1d - ::mysql::moreresult: $more"      if {$more != 0} {        set rows [::mysql::nextresult $mysqliCN]        putlog "loadbstriggers 1e - rows:$rows"        while {[set row [::mysql::fetch $rs]]!=""} {          lassign $row triggerID triggerName          putlog "loadbstriggers 1f - row:$row"          lappend list [list $triggerID $triggerName {}]        }        lset botscenetriggers {} $list      }    }    ::mysql::endquery $mysqliCN  }}

For those that would like to be certain I'm envoking the connection properly, I've included my mysqltcl connection routine

Code:

proc getMysqliCN {args} {  global mysqliCN  set state [::mysql::state $mysqliCN]  #putlog "getMysqliCN -state:$state"  if {$state == 0 || $state == 1 || $state == "NOT_A_HANDLE" || $state == "UNCONNECTED" } {    set mysqliCN [::mysql::connect -multistatement 1 -multiresult 1 -user dbuser -host 127.0.0.1 -db dbname -password password]    ::mysql::setserveroption $mysqliCN -multi_statment_on    putlog "getMysqliCN - connecting to mysql: $mysqliCN"  } elseif { $state == 4 ||$state == "RESULT_PENDING" } {    set trash [::mysql::sel $mysqliCN "select 0" -flatlist]  }  return $mysqliCN}

Statistics: Posted by MasterOfX — Mon Jul 02, 2012 5:41 am

egghelp/eggheads community

mysqltcl: cannot access additional result sets