Forum OpenACS Q&A: Inserting a image file in ORACLE

Hi...I was going thru the documentation of OpenACS 4.5 and found a piece of code in

https://openacs.org/doc/openacs-4/db-api.html

for uploading blob images in oracle. And I tried to do the following

  <form name=myform action='mypic-2.tcl' method=post
enctype=multipart/form-data>
   Name <input type=text name=name>
   File <input type=file name=picture>
   <input type=submit value=upload>
  </form>

in mypic.tcl

then in mypic-2.tcl I did

set_the_usual_form_variables


set tmp_filename [ns_queryget picture.tmpfile]
set file_extension [string tolower [file extension $picture]]


set insert_stmt "insert into pic_table
                   (name, pic)
               values
                   ($name, empty_blob())
               returning pic into :1"

db_dml insert_stm $insert_stmt -blob_files [list $tmp_filename]

set page_content "One file uploaded $tmp_filename"

my table structure is

SQL> desc pic_table;
 Name					   Null?    Type
 ----------------------------------------- --------
 NAME						    VARCHAR2(100)
 PIC						    BLOB

I get this error while loading a .gif file

       ora8.c:4715:lob_dml_bind_cmd: error in `OCIStmtExecute ()':
ORA-00984: column not allowed here

       SQL: insert into pic_table 
                          (name, pic) 
                      values 
                          (my, empty_blob()) 
                      returning pic into :1
           while executing
       "ns_ora blob_dml_file_bind nsdb0 {insert into pic_table 
                          (name, pic) 
                      values 
                          (my, empty_blob()) 
       ..."
           ("uplevel" body line 1)
           invoked from within
       "uplevel 2 [list ns_ora $type $db $sql] $args"
           invoked from within
       "db_exec blob_dml_file_bind nsdb0 insert_stm {insert into
pic_table 
                          (name, pic) 
                      values 
                          (my, emp..."
           ("eval" body line 1)
           invoked from within
       "eval [list db_exec "${command}_bind" $db $statement_name $sql
$bind_vars] $lob_argv"
           invoked from within
       "if { $lob_argc == 1 } {
                   # Bind :1, :2, ..., :n as LOBs (where n = [llength
$lob_argv])
                   set bind_vars [list]
                   for { set i 1 } { $i <= [ll..."
           ("uplevel" body line 2)
           invoked from within
       "uplevel 1 $code_block "
           invoked from within
       "db_with_handle db {
               if { $lob_argc == 1 } {
                   # Bind :1, :2, ..., :n as LOBs (where n = [llength
$lob_argv])
                   set bind_vars [list]
                   for { ..."
           (procedure "db_dml" line 32)
           invoked from within
       "db_dml insert_stm $insert_stmt -blob_files [list
$tmp_filename]"
           (file "/web/aces/www/mypic-2.tcl" line 22)
           invoked from within

.....
....
....

Please help

Thanks

  insert into pic_table 
    (name, pic) 
  values 
    (my, empty_blob()) 
  returning pic into :1

Hi JSvete,

I have emailed you with files and a short crude explanation. For the benefit of others here is the snippet of the short crude explanation on how to use CR.

The summary below is for uploading binary data to CR and putting the binary data to the file system.
1. Make a form with a enctype=multipart/form-data.
2. Use a input type=file form element
3. Submit the form to a tcl and get the file using ad_page_contact

upload_file:trim,notnull #the file name
upload_file.tmpfile:tmpfile #the tempfile that aol creates

4. Guess the mime_type and set it via ns_guesstype
5. Normally I create a plpgsql to do the following

5.a. Insert a new mime type to cr_mime_types if needed
5.b. create a new acs_object
5.c. create a new cr_items
5.b. create a new cr_revisions

6. Use cr_create_content_file to place the file on the file system
7. Updated cr_revisions with the correct file size and the file name that is created on the file system that is under content-respositorty-content-files

I hope this helps others in getting comfortable with CR.