db_dml

db_dml { -key "" -type "" -where "" -clobs "" -blobs "" -clob_files "" -blob_files "" } sql_or_table args

Performs a DML statement.

Source code:

arg_parser_for_db_dml $args

    # Only one of clobs, blobs, clob_files, and blob_files is allowed. Remember which one
    # (if any) is provided.
    set lob_argc 0
    set lob_argv [list]
    set command "ns_db dml"
    if { ![empty_string_p $clobs] } {
	set command "ns_ora clob_dml"
	set lob_argv $clobs
	incr lob_argc
    }
    if { ![empty_string_p $blobs] } {
	set command "ns_ora blob_dml"
	set lob_argv $blobs
	incr lob_argc
    }
    if { ![empty_string_p $clob_files] } {
	set command "ns_ora clob_dml_file"
	set lob_argv $clob_files
	incr lob_argc
    }
    if { ![empty_string_p $blob_files] } {
	set command "ns_ora blob_dml_file"
	set lob_argv $blob_files
	incr lob_argc
    }
    if { $lob_argc > 1 } {
	error "Only one of -clobs, -blobs, -clob_files, or -blob_files may be specified as an argument to db_dml"
    }

    if { ![string compare "" $type] } {
	# Plain ol' DML/DDL statement.
	if { [llength $args] != 0 } {
	    error "Too many arguments to db_dml: expects only one argument if -type is unspecified"
	}
	set sql $sql_or_table
    } else {
	if { [llength $args] != 1 } {
	    error "Expected two arguments to db_dml"
	}
	set columns [lindex $args 0]
	set names [list]
	set values [list]
	foreach column $columns {
	    # Build lists of column names/values.
	    set name [lindex $column 0]
	    if { ![string compare $type "update"] && ![string compare $key $name] } {
		# If it's an update, don't insert the primary key column! Instead,
		# save the value in key_value.
		set key_value [lindex $column 1]
	    } else {
		if { ![string compare $type "update"] } {
		    lappend values "$name = [lindex $column 1]"
		} else {
		    lappend names $name
		    lappend values [lindex $column 1]
		}
	    }
	}
	
	if { ![string compare $type "insert"] } {
	    # Perform the insert.
	    if { [string compare "" $where] } {
		# A where clause was specified... do an INSERT ... SELECT to handle this
		# case.
		set sql "insert into ${sql_or_table}([join $names ","])
		select [join $values ","] from dual
		where $where"
	    } else {
		set sql "insert into ${sql_or_table}([join $names ","]) values([join $values ","])"
	    }
	} elseif { ![string compare $type "update"] } {
	    # It's an update. Build the where clause.
	    set where_items [list]
	    if { [string compare "" $key] } {
		if { ![info exists key_value] } {
		    error "Value for key column ($key_value) is unspecified."
		}
		lappend where_items "$key = $key_value"
	    }
	    if { [string compare "" $where] } {
		lappend where_items "($where)"
	    }
	    if { [llength $where_items] != 0 } {
		set where_clause "where [join $where_items "and"]"
	    } else {
		set where_clause ""
	    }
	    
	    set sql "update $sql_or_table set [join $values ", "] $where_clause"
	} else {
	    error "type, if specified, must be either \"insert\" or \"update\""
	}
    }
    
    db_with_handle db {
	eval [concat $command [list $db $sql] $lob_argv]
    }