Inserting files

[Former Member]

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.

[MarkCulp]

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?

[Former Member]

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.)

[VolkerBarth]

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;

[Former Member]

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

[VolkerBarth]

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