TD 4.2 - saving files in SQLServer and retrieving them

General discussion forum about all databases other than SqlBase.
ColdBeer16

TD 4.2 - saving files in SQLServer and retrieving them

Post by ColdBeer16 » 12 Jan 2012, 22:56

I have a small program that allows the user to save files (pdf, doc, txt, jpg, docx), etc, in an SQLServer 2005 database. I have used both image and varbinary(max) in my testing. Everything works fine - except the office formats introduced with office 2007, i.e. docx, xlsx, etc. When I 'reconstitute' one of those files to disk and open it, word, excel, etc. provides an error message, and offers to fix the file. If I answer yes, the file proceeds to open fine.

I have attached a small sample that shows how it works. The table that it saves the files to can be created using the create statement in the top level comment section of the code.

Any suggestions welcome. Thanks!
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by Jeff Luther » 12 Jan 2012, 23:20

When I 'reconstitute' one of those files to disk..
I wouldn't think you'd need to reconstitute anything, unless you must mean DB --> FETCH to Long String --> save to disk using SalFileWrite(). I'll look at your test case.

Well... missing APLs so I can't compile. Plus, I don't see where you are saving the file. I need a SMALL test case -- sample DOCX file, say, that you read in, then write out. If write out [to disk] is what you mean by reconstitute . Otherwise, I need you to clarify what you are trying to do.
word, excel, etc. provides an error message
And what is the error msg? Maybe a snapshot of it and attach here?

Again, I need to be able to repro. what you see so I'll need a complete test case that shows this issue.

Jeff Luther

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by Jeff Luther » 12 Jan 2012, 23:35

P.S. T-A apparently just sent something to support, but I cannot find it.

Help me Keep It Simple guys, if you would. This forum thread is the best place for anything related to this topic. How about just attaching whatever T-A had sent to this forum thread?

MUCH easier this way. Thanks.

ColdBeer16

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by ColdBeer16 » 13 Jan 2012, 02:20

Hi Jeff,

Sorry about that. Please check the attached file with the apls merged. The code where I fetch the file from the database into a long string and write to the file is in the function ViewFile under the tblDocMan table window inside the form. Typical scenario is a user attaches a file, another user wants to view it. I've attached a docx sample file as well, and screen shots of the error. These are in the other zip file.

I did not send anything to support, but did use your profile to send an email to you (assumed it went direct to you) referring to this case as I was hoping you could help. Sorry if that created confusion.

Appreciate you help.

T-A
You do not have the required permissions to view the files attached to this post.

ColdBeer16

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by ColdBeer16 » 13 Jan 2012, 17:32

Hi Jeff,

Another tidbit obout the app. To add files, drag from Windows Explorer and drop into the table window, or use browse to locate a file and save it to the database. To extract the file, double click on a row or select a row and click View. No need to use the save button for anything.

The extracted files are written to c:\temp (not picking up the user's temp folder since this is just a quick demo app).

I have compared the extracted file with the original, and they are different. This can easily be examined by changing the file extension to zip and using a zip tool (I like zipgenius which is a great free zip/unzip tool) to look at the contents. The four letter extensions ending in X is essentially a zip file with a set of XML files inside.

Thanks for looking at it.

Jeff Luther

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by Jeff Luther » 19 Jan 2012, 00:04

Earlier you wrote:
except the office formats introduced with office 2007, i.e. docx, xlsx, etc. When I 'reconstitute' one of those files to disk and open it, word, excel, etc. provides an error message, and offers to fix the file.
Have you considered zipping these files before inserting/updating, then unzipping after a fetch? I don't know what have happened to the file that caused Winword to think it's not correct, but zipping might be the solution for you.

There are a number of 3rd-party DLLs for zip/unzip around, I'm sure. Do a search for VBZIP. I'm sure there are others as well. I also see I had old info. for this site: http://www.polarsoftware.com/products/s ... component/

ColdBeer16

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by ColdBeer16 » 06 Feb 2012, 17:18

Ok, I found the issue. When writing a long string to a file, TD adds one byte to the string if you specify a large generic buffer. If you specify the exact size of the long string as originally read, it does not add the extra byte. The extra byte is ignored by all other programs/file formats I've attached (pdf, bmp, jpg, txt, etc.), but causes a problem with the docx, xlsx, etc. files. The database has nothing to do with this.

I have attached a sample with the update in case it is useful for anyone else.

Short description of how it works: Create the table in your MSS database (CREATE TABLE statement included inside the file, at the file comments section). Run program, drag one or more files and drop onto table window. Files are inserted into database table. Double click on any row in the table to extract the file to C:\temp and have it opened using the shell command and the associated program.
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by Jeff Luther » 07 Feb 2012, 00:41

One thought: MS SS definition for 'image' column type from this page:
http://msdn.microsoft.com/en-us/library/ms187993.aspx
(bold is mine): Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

If you are inserting TEXT file in the image column, there may be unexpected behavior like that extra "one byte" you refer to.

ColdBeer16

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by ColdBeer16 » 07 Feb 2012, 15:07

Thanks for the suggestion Jeff. Turns out that the database has nothing to do with it, it is related to the buffer size in the 3rd parameter of SalFileWrite.

If I run this code to read file into a long string and write it straight back out, TD adds one character to the new file:

Call SalFileOpen( fhFile, sFileName, OF_Read )
Set nCharsRead = SalFileRead( fhFile, lsFile, MAX_FILE_BUFFER )
Call SalFileClose( fhFile )

Call SalFileOpen( fhFile, 'c:\\temp\\' || sFileName, OF_Write | OF_Create )
Call SalFileWrite( fhFile, lsFile, MAX_FILE_BUFFER ) ! This line adds one character
Call SalFileWrite( fhFile, lsFile, nCharsRead ) ! This line works fine as the buffer is set to the actual file size.
Call SalFileClose( fhFile )

Jeff Luther

Re: TD 4.2 - saving files in SQLServer and retrieving them

Post by Jeff Luther » 07 Feb 2012, 18:29

Using MAX_FILE_BUFFER and it adding 1 bytes doesn't surprise me: Since TD doesn't know how many bytes to write (yet), it's reading to the end of string to find the end-of-string (EOS) char and including that in the file write. That's my guess.

It also seems that passing MAX_FILE_BUFFER is not the best way to code this; there are better ways to specify the length of the string to write. See below.

When you specify nCharsRead that length does not include the EOS char. since it's the actual chars in the file, so TD's excluding that extra EOS char. in the file write. The file doesn't need or use the EOS char; the OS has its own way of managing a file's length. That extra char. is TD's internal '\0' for the string so it knows where the end of the string is in memory.

For TD v4.2 you could likely use SalStrGetBufferLength() or SalStrLength() to find out the 'true' length and use that for the write as well. Be sure to test this, but back prior to the TD v5/v6 days and Unicode/double-byte chars, in the v.42 and earlier ANSI world buffer length = str length = 1 byte/char and I don't believe SalStrGetBufferLength() returned the 'actual' buf len + that 1 byte EOS char.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests