InfoPath Dev

Hi - Using Infopath 2007,

I have set up a form with 50ish fields and  potentially 20 or so file attachments.  This form is published in a sharepoint document library, but I want to be able to save some of the information to a SQL Server database.

I have set up the SQL Server db - the fields to store the file attachments are of type varbinary(8000) - (I hope that is correct ?). 

I have set up a webservice to process the 'ordinary' fields which is working correctly.

I have now turned to the file attachments.  I have read several articles on 'decoding the file attachments' to write out files - but am unsure about WHAT to write out to the SQL database.  I believe there is a header which is of length 24 bytes + length of the filename. Following this head is the file contents. These are base64 encoded. - so do I get rid of the header completely ? - what do I do about the filename ?

I have created a query form based on my SQL database and selected one of the fields containing the 'file attachment stuff',  when I run the query the binary content of the field is displayed in a text box.  I have been into the design and tried to convert the field from a text box - but file attachment is not an option. 

So my questions are......
1. Am I trying to store the file attachments in the correct data type ?
2. Which bit of the file attachment xml do I need to write to the SQL Database ? 
3. How do I get the file attachment back out of the SQL database to display in an infopath query form?

Thank  you
Catharine

Hi Catherine.

I have done the encoding and decoding of attachments but I do not know if you really need to do this. I did this to have my own attachment control and to convert files to text for search capabilities.

Are you attaching the files into the InfoPath Form or do you want to link to them? If you are using the attachment control then the files are embedded in your form and will round trip.

Have you looked at the database accelerator? Qdabra Database Accelerator Suite Version 2.1.2815 - Developer

It stores the form in SQL, gives you text search and the ability to report on all of your fields.  With it, I have saved multiple attachments into their own table as blobs however I do not remember what kind of field type I used. Image should be used if the file is larger than 8KB.

What kind of files are you saving?

Hi Clay

Thank you for the reply - I will have a look at the database accelerator - but I don't think I will get that through for this application.

I do not know what file types the users will attach to the form.  I am collecting 'any information they have' and I would guess that it will mainly be in excel or word - but I don't want to limit them.

I am using varbinary(max) cos I found a note from microsoft that image was going to be 'disappearing in the future' so varbinary should be used for new developments.

If I do not decode the xml how/what do I store in the DB ?  I am sending the data to the db using a stored procedure.  If I use the undecoded xml string I get a sql exception error {"Operand type clash: ntext is incompatible with varbinary"}.  So I must have to do something with the xml - but what ?

Thank you

Catharine