Forum OpenACS Development: Fundemental problem with oracle bind variables, lobs and upvar

I'm having problems with cr_write_content which I'm think are really fundemental problems with the way the Oracle db rountins handle upvar and bind variables. I was having problems with clobs yesterday and I fixed them. Now I'm having similar problems with blobs and I'm not sure I fixed clobs the correct way.

First I've always assumed that the bind variables and the results should happen in the same stack frame. For example

cr_write_content -revision_id $revision_id
  db_write_blob
     db_exec_lob passes -ulevel 3
        db_exec_lob_oracle passes 
            ns_ora 

The bind variable for :revision_id should come from the cr_write_content stack frame, but db_exec_lob_orace does

set start_time [clock clicks -milliseconds]

    set sql [db_qd_replace_sql $statement_name $pre_sql]

    # insert tcl variable values (Openacs - Dan)
    if {![string equal $sql $pre_sql]} {
        set sql [uplevel $ulevel [list subst -nobackslashes $sql]]
    }
 
    set file_storage_p 0
    upvar $ulevel storage_type storage_type

    if {[info exists storage_type] && [string equal $storage_type file]} {
        set file_storage_p 1
        set original_type $type
        set qtype 1row
        ns_log Debug "db_exec_lob: file storage in use"
    } else {
        set qtype $type
        ns_log Debug "db_exec_lob: blob storage in use"
    }

    set errno [catch {
	upvar bind bind

# Below, note that 'ns_ora blob_get_file' takes 3 parameters,
        # while 'ns_ora write_blob' takes only 2.  So if file is empty
        # string (which it always will/should be for $qtype
        # write_blob), we must not pass any 3rd parameter to the
        # ns_ora command: --atp@piskorski.com, 2003/04/09 15:10 EDT

	if { [info exists bind] && [llength $bind] != 0 } {
	    if { [llength $bind] == 1 } {
                if { [empty_string_p $file] } {
                    set selection [eval [list ns_ora $qtype $db -bind $bind $sql]]
                } else {
                    set selection [eval [list ns_ora $qtype $db -bind $bind $sql $file]]
                }

	    } else {
		set bind_vars [ns_set create]
		foreach { name value } $bind {
		    ns_set put $bind_vars $name $value
		}
                if { [empty_string_p $file] } {
                    set selection [eval [list ns_ora $qtype $db -bind $bind_vars $sql]]
                } else {
                    set selection [eval [list ns_ora $qtype $db -bind $bind_vars $sql $file]]
                }
	    }
} else {
            if { [empty_string_p $file] } {
                set selection [uplevel $ulevel [list ns_ora $qtype $db $sql]]
            } else {
                set selection [uplevel $ulevel [list ns_ora $qtype $db $sql $file]]
            }
	}

Here is a stack trace

nsoracle.c:3206:ora_tcl_command: error in `OCIStmtExecute ()': ORA-01008: not al
l variables bound

SQL:
          select content
          from cr_revisions
          where revision_id = :revision_id

    while executing
"ns_ora write_blob nsdb0 {
          select content
          from cr_revisions
          where revision_id = :revision_id
      }"
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_ora $qtype $db $sql]"
    invoked from within
"$which_proc -ulevel [expr {$ulevel +1}] $type $db $statement_name $pre_sql $fil
e"
    (procedure "db_exec_lob" line 35)
    invoked from within
"db_exec_lob write_blob $db $full_statement_name $sql  "
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle -dbn $dbn db {
        db_exec_lob write_blob $db $full_statement_name $sql
    }"
    (procedure "db_write_blob" line 5)
    invoked from within
"db_write_blob write_lob_content "
select content
          from cr_revisions
          where revision_id = :revision_id

""
    ("lob" arm line 12)
    invoked from within

Now I think the code with the insert tcl var comments should substitue for $revision_id in a query. but upvar bind bind will only go up 1 level. It seems like this should be upvar $ulevel bind bind (maybe, the wrapper rountines do not pass bind variables) but I also seems like the ns_ora command should be always upleveled as well.

The other thing that happens is db_with_handle does an uplevel also. I'm not sure how that interacts with the uplevel that happens latter

So I guess the questions are should the wrapper routines pass down bind variables? and more fundemental what frame should ns_ora run in. And I guess what needs to be done to fix it.