Forum OpenACS Development: -blob_files parameter to db procs behaves differently in Postgresql than Oracle

Collapse
1: -blob_files parameter to db procs behaves differently in Postgresql than Oracle
Posted by Dave Bauer on 09/21/05 07:07 PM
The -blob_files aprameter to db procedures is documented to accept a list. And all the documentation shows something like this

db_dml query "" -blob_files [list $tmp_filename]

If $tmp_filename contains a space, the lob insert will fail.

This is demonstrated by

% set tmp_filename "This is a file.txt"
This is a file.txt
% list $tmp_filename
{This is a file.txt}
% set tmp_filename "this-is-a-file.txt"
this-is-a-file.txt
% list $tmp_filename
this-is-a-file.txt
%

You can see that [list $tmp_filename] will return the filename surroned by {}.

In the code that sets up the lob in postgresql it only supports and assumes one filename, and sends the entire blob_files parameter to the database driver as the filename. This results in error messages such as

tdxdave: [21/Sep/2005:08:27:21][10812.136204288][-conn:openacs5::1]
Error: Error opening file {/var/tmp/tmp.86.qrRKrm/Test Document.pdf}: 2(No such file or directory)

You can see the file error message has the filename with {} around it.

There are many places in the toolkit that use -blob_files [list $something]

This does not always happen, so I think somehwere there is
list/string represenation difference in the calling code.

Collapse
2: Re: -blob_files parameter to db procs behaves differently in Postgresql than Oracle (response to 1)
Posted by Dave Bauer on 09/21/05 08:02 PM
I have figured it out.

The intermittent failure is due to not using aolserver generated tmp filenames when unzipping a folder before adding it to the content repository.

So we had filenames like you see above. /var/tmp/tmp.86.qrRKrm/Test Document.pdf

When you upload a single file it uses ns_mktmp to generate the filename for the tmp file so it is guaranteed to contain no spaces.

I can work around this by using tmp filenames from within the zip file that don't have spaces, but I think the error is in db_dml where it passes the list to ns_pg.

ns_pg blob_dml_file $db [uplevel {set __lob_id}] $blob_files

Collapse
3: Re: -blob_files parameter to db procs behaves differently in Postgresql than Oracle (response to 1)
Posted by Malte Sussdorff on 09/22/05 07:53 AM
Did you try using foreach blob_file $blob_file {ns_pg ...}? I'm not sure why you would ever call ns_pg blob_dml_file with multiple files...
Collapse
4: Re: -blob_files parameter to db procs behaves differently in Postgresql than Oracle (response to 1)
Posted by Dave Bauer on 09/22/05 03:19 PM
Malte,

Since the Oracle version supports multiple files, I would think the PG version needs to be the same, but the hack to make it work makes it tricky.

For oracle you can just to empty_blob() for every blob column and it will "just work" as far as I can tell, but the PG support only allows for one __lob_id to be passed into the query.

Supporting just one blob at a time, seems reasonable, and I don't know of any existing code that actually sends more than one file at a time.

Perhaps we should change the oracle code to only support one blob also? This would make future code simpler to support on multiple databases, I think.

Collapse
5: Re: -blob_files parameter to db procs behaves differently in Postgresql than Oracle (response to 1)
Posted by Malte Sussdorff on 09/23/05 09:16 AM
Yes, please, if you have time, change the whole code to work with multiple files through a foreach command consistently both for Oracle and PostgreSQL. Makes it more consistent and I'd say obvious as well.