db_load_sql_data (public)

 db_load_sql_data [ -dbn dbn ] [ -callback callback ] file

Defined in packages/acs-tcl/tcl/01-database-procs.tcl

Loads a CSV formatted file into a table using PostgreSQL's COPY command or Oracle's SQL*Loader utility. The filename format consists of a sequence number used to control the order in which tables are loaded, and the table name with "-" replacing "_". This is a bit of a kludge but greatly speeds the loading of large amounts of data, such as is done when various "ref-*" packages are installed.

Switches:

-dbn (optional)

The database name to use. If empty_string, uses the default database.

-callback (optional, defaults to "apm_ns_write_callback")

Parameters:

file (required)

Filename in the format dd-table-name.ctl where 'dd' is a sequence number used to control the order in which data is loaded. This file is an RDBMS-specific data loader control file.

Partial Call Graph (max 5 caller/called nodes):

Testcases:

No testcase defined.

Source code:

    switch [db_driverkey $dbn] {

        oracle {
            set user_pass [db_get_sql_user -dbn $dbn]

            set fd [open $file r]
            set file_contents [read $fd]
            close $fd
            set file_contents [subst $file_contents]

            set fd1 [file tempfile tmpnam [ns_config ns/parameters tmpdir]/oacs-XXXXXX.ctl]
            puts $fd1 $file_contents
            close $fd1

            cd [ad_file dirname $file]

            set fd [open "|[ad_file join $::env(ORACLE_HOME) bin sqlldr] userid=$user_pass control=$tmpnam" "r"]

            while { [gets $fd line] >= 0 } {
                # Don't bother writing out lines which are purely whitespace.
                if { ![string is space $line] } {
                    apm_callback_and_log $callback "[ns_quotehtml $line]\n"
                }
            }
            close $fd
        }

        postgresql {
            set pguser [db_get_username]
            if { $pguser ne "" } {
                set pguser "-U $pguser"
            }

            set pgport [db_get_port]
            if { $pgport ne "" } {
                set pgport "-p $pgport"
            }

            set pgpass [db_get_password]
            if { $pgpass ne "" } {
                set pgpass "<<$pgpass"
            }

            if { [db_get_dbhost] eq "localhost" || [db_get_dbhost] eq "" } {
                set pghost ""
            } else {
                set pghost "-h [db_get_dbhost]"
            }

            set fd [open $file r]
            set copy_command [subst -nobackslashes [read $fd]]
            close $fd
            set fd [file tempfile copy_file [ad_tmpdir]/psql-copyfile-XXXXXX]
            puts $fd $copy_command
            close $fd

            if { $::tcl_platform(platform) eq "windows" } {
                set fp [open "|[file join [db_get_pgbin] psql] -f $copy_file $pghost $pgport $pguser [db_get_database]" "r"]
            } else {
                set fp [open "|[file join [db_get_pgbin] psql] -f $copy_file $pghost $pgport $pguser [db_get_database] $pgpass" "r"]
            }

            while { [gets $fp line] >= 0 } {
                # Don't bother writing out lines which are purely whitespace.
                if { ![string is space $line] } {
                    apm_callback_and_log $callback "[ns_quotehtml $line]\n"
                }
            }

            # PSQL dumps errors and notice information on stderr, and has no option to turn
            # this off.  So we have to chug through the "error" lines looking for those that
            # really signal an error.

            set errno [ catch {
                close $fp
            } error]

            # remove the copy file.
            file delete -force -- $copy_file

            if { $errno == 2 } {
                return $error
            }

            # Just filter out the "NOTICE" lines, so we get the stack dump along with real
            # ERRORs.  This could be done with a couple of opaque-looking regexps...

            set error_found 0
            foreach line [split $error "\n"] {
                if { [string first NOTICE $line] == -1 } {
                    append error_lines "$line\n"
                    set error_found [expr { $error_found
                                            || [string first ERROR $line] != -1
                                            || [string first FATAL $line] != -1 } ]
                }
            }

            if { $error_found } {
                return -code error -errorinfo $error_lines -errorcode $::errorCode $error_lines
            }

        }

        nsodbc {
            error "db_load_sql_data is not supported for this database."
        }
        default {
            error "db_load_sql_data is not supported for this database."
        }
    }

XQL Not present:

Generic, PostgreSQL, Oracle