Question Re: Inserting files in Technology Q&A

Inserting files

Former Member — Tue, 11 Sep 2012 19:01:36 GMT

When I run a an insert statement with a large "contents" value I get an error "Could not execute statement. Server 'DirectoryServer': No such file or directory SQLCODE=-660, ODBC 3 State="HY000"

Anybody know why I'm erroring out? This happens when "contents" value is over 3000.

Re: Inserting files

MarkCulp — Tue, 11 Sep 2012 20:39:14 GMT

Please provide more information: it sounds like you are using a directory access proxy table - what is the exact statement that you are trying to execute when you get the error?

Re: Inserting files

Former Member — Wed, 12 Sep 2012 01:44:59 GMT

Yes I am using a directory access proxy table.

Here's a shortened version of the statement since it won't let me post everything.

Insert into DBA.Attachments(Contents, File_Name)
    values (0X5768656E20492072756E206120616E20696E736<skipped>206572, 'test.txt')

(Update: data has been truncated to make the page readable.)

Re: Inserting files

VolkerBarth — Wed, 12 Sep 2012 10:43:48 GMT

Does it work when you assign the file contents to a LONG VARBINARY variable and then use that variable within the insert, such as:

begin
  declare myBlob long varbinary;
  set myBlob = 0x5768656E20492072756E206120616E20696E736<skipped>206572;
  Insert into DBA.Attachments(Contents, File_Name)
    values (myBlob, 'test.txt');
end;

Re: Inserting files

Former Member — Wed, 12 Sep 2012 11:12:54 GMT

Yes that works. Thank you! Do you know why it has to be assigned to a variable for it to work?

Re: Inserting files

VolkerBarth — Wed, 12 Sep 2012 11:20:26 GMT

No, that was just wild guessing - similar to that proxy table question... but Mark will know:)