SQL Server / ADO with attachments

Hello,

With much please spending time on working with VO2ADO with connection
to a Microsoft SQL 2005 server database and have one question.

How are you all working with attachments (like Word, Excel or other)
in your VO application with a connection to an SQL Windows 2005
Database? I assume that you're not able to put attachments in your SQL
database (and perhaps you even don't want?) but how do you, in
general, set up a relation between a record in a SQL database and its
relevant attachments? I have a kind of customer relation database and
would like to be able to add for example some Word visit reports to my
SQL database.

Thanks for any input that you can give

Mvg,
Rob

Rob,

What is the problem? SQL Server handles this quite well.

Geoff



"Rob van Erk" <erk.v@hotmail.com> wrote in message
news:d548fdbf-2c89-4bd4-b4f1-41e9b6381cc9@56g2000hsm.googlegroups.com:

> Hello,
>
> With much please spending time on working with VO2ADO with connection
> to a Microsoft SQL 2005 server database and have one question.
>
> How are you all working with attachments (like Word, Excel or other)
> in your VO application with a connection to an SQL Windows 2005
> Database? I assume that you're not able to put attachments in your SQL
> database (and perhaps you even don't want?) but how do you, in
> general, set up a relation between a record in a SQL database and its
> relevant attachments? I have a kind of customer relation database and
> would like to be able to add for example some Word visit reports to my
> SQL database.
>
> Thanks for any input that you can give
>
> Mvg,
> Rob

Rob: this work for my.

FUNCTION GrabarArchivo( cSDoc )

LOCAL uFileSize AS DWORD
LOCAL uFileSize AS DWORD
LOCAL PTRHandle1 AS PTR
LOCAL cBuffer AS STRING
LOCAL oBuffer AS AdoLongBinary
LOCAL cNombre, cMD5 AS STRING
LOCAL oCmd AS adoCommand
LOCAL oPar AS AdoParameter
LOCAL llohizo AS LOGIC
LOCAL dwReadText , nID AS DWORD
LOCAL oRS AS AdoRecordSet

//Name of source file: cSDoc
llohizo := FFirst( csDoc, FC_NORMAL )
uFileSize:=FSize()
IF uFileSize > 10048576
FabMessageStop(,"El archivo a adjuntar debe tener un tamaño inferior
a 10 MB", "Error" )
RETURN 0
ENDIF
cMD5:=RKF_MD5File (cSDoc)
PTRHandle1 := FOpen2(cSDoc, FO_READ)
cNombre:= Upper(SubStr(cSDoc, RAt('\', cSDoc)+1 ))

//Writing the file to a varchar(max) field on the MS SQLServer
cBuffer:=Space(uFileSize)
oCmd := adoCommand{}
oCmd:ActiveConnection := oConexion
oCmd:CommandType:=adCmdText
oCmd:CommandText:="INSERT INTO Archivo (ARCHIVO, NOMBRE, TAMAÑO,MD5)
VALUES (?, '"+cNombre+"', "+;
NTrim(uFileSize) +",'" + cMD5+"')"
oPar:=oCmd:CreateParameter("ARCHIVO",adLongVarBina ry,adParamInput,uFileSize,
NIL)
dwReadText := FRead(ptrhandle1, @cBuffer, uFileSize )
oBuffer := AdoLongBinary{cBuffer}
oPar:AppendChunk(oBuffer)
oCmd:Parameters_:Append(oPar)

oCmd:Execute(NIL, NIL, NIL)
FClose(ptrHandle1)

oCmd:CommandText:="SELECT MAX(ID_ARCHIVO) FROM Archivo"
oRS:= oCmd:Execute(NIL, NIL, NIL)
nID:= oRS:FIELDGET(1)

RETURN nID
************************************************** *********************
TABEL ARCHIVO

Column_name Type
------------ --------
ID_ARCHIVO int
NOMBRE varchar
ARCHIVO image
TAMAÑO int
MD5 char



Sergio Labiano
Argentina

Geoff,

Basically the question was how I should consider to add Excel/Word
documents in a SQL database.
I was looking at an example on this. Not sure what kind of type field
I should define, etc. I will try to use Sergio's program.

Thanks,
Rob

Rob,

Each DBMS has a binary field type of one kind or other. Simply use that.
In MS SQL there is a data type called varbinary(max) which is the
recommended type.

Geoff


"Rob van Erk" <erk.v@hotmail.com> wrote in message
news:12baf128-9df0-4eb9-a58b-cd144d2c7ce6@i76g2000hsf.googlegroups.com:

> Geoff,
>
> Basically the question was how I should consider to add Excel/Word
> documents in a SQL database.
> I was looking at an example on this. Not sure what kind of type field
> I should define, etc. I will try to use Sergio's program.
>
> Thanks,
> Rob

>
> Each DBMS has a binary field type of one kind or other. Simply use that.
> In MS SQL there is a data type called varbinary(max) which is the
> recommended type.

Never done this, but I am pretty sure that if you are using Office 2007 you
could store the files in a XML column.
That makes it possible to query for the content of the document as well...

Erik





>
> Geoff
>
>
> "Rob van Erk" <erk.v@hotmail.com> wrote in message
> news:12baf128-9df0-4eb9-a58b-cd144d2c7ce6@i76g2000hsf.googlegroups.com:
>
>> Geoff,
>>
>> Basically the question was how I should consider to add Excel/Word
>> documents in a SQL database.
>> I was looking at an example on this. Not sure what kind of type field
>> I should define, etc. I will try to use Sergio's program.
>>
>> Thanks,
>> Rob
>

No, not really because it only applies to xml structured data and not
office 2007 docs natively because they are zipped. I'd need to consult
with Ed on this but I think you are better off storing docx etc in a
varbinary column and using full text search on it. When you declare the
document type (jpeg, pdf, doc, docx etc) it is capable of searching it.

If what you wanted was xml based search then you have to save it into
the xml column as xml. A raw 2007 document wouldn't qualify.

Geoff



"ER!k \/!sser" <nospam@nospam.com> wrote in message
news:6hstleFnsc1jU1@mid.individual.net:

> >
> > Each DBMS has a binary field type of one kind or other. Simply use that.
> > In MS SQL there is a data type called varbinary(max) which is the
> > recommended type.
>
>
> Never done this, but I am pretty sure that if you are using Office 2007 you
> could store the files in a XML column.
> That makes it possible to query for the content of the document as well...
>
> Erik
>
>
>
>
>
>
> >
> > Geoff
> >
> >
> > "Rob van Erk" <erk.v@hotmail.com> wrote in message
> > news:12baf128-9df0-4eb9-a58b-cd144d2c7ce6@i76g2000hsf.googlegroups.com:
> >
>
> >> Geoff,
> >>
> >> Basically the question was how I should consider to add Excel/Word
> >> documents in a SQL database.
> >> I was looking at an example on this. Not sure what kind of type field
> >> I should define, etc. I will try to use Sergio's program.
> >>
> >> Thanks,
> >> Rob
>
> >

On Aug 30, 8:29*pm, "Geoff Schaller"
<geo...@softxwareobjectives.com.au> wrote:
> No, not really because it only applies to xml structured data and not
> office 2007 docs natively because they are zipped. I'd need to consult
> with Ed on this but I think you are better off storing docx etc in a
> varbinary column and using full text search on it. When you declare the
> document type (jpeg, pdf, doc, docx etc) it is capable of searching it.
>
> If what you wanted was xml based search then you have to save it into
> the xml column as xml. A raw 2007 document wouldn't qualify.
>
> Geoff
>
> "ER!k \/!sser" <nos...@nospam.com> wrote in message
>
> news:6hstleFnsc1jU1@mid.individual.net:
>
>
>
>
>
> > > Each DBMS has a binary field type of one kind or other. Simply use that.
> > > In MS SQL there is a data type called varbinary(max) which is the
> > > recommended type.
>
> > Never done this, but I am pretty sure that if you are using Office 2007you
> > could store the files in a XML column.
> > That makes it possible to query for the content of the document as well....
>
> > Erik
>
> > > Geoff
>
> > > "Rob van Erk" <er...@hotmail.com> wrote in message
> > >news:12baf128-9df0-4eb9-a58b-cd144d2c7ce6@i76g2000hsf.googlegroups.com:
>
> > >> Geoff,
>
> > >> Basically the question was how I should consider to add Excel/Word
> > >> documents in a SQL database.
> > >> I was looking at an example on this. Not sure what kind of type field
> > >> I should define, etc. I will try to use Sergio's program.
>
> > >> Thanks,
> > >> Rob- Hide quoted text -
>
> - Show quoted text -

I'd like to see the actual code to accomplish this. If I had a PDF
file, how is this actually saved to the server?

Geoff

A search on MSDN turned up

http://msdn.microsoft.com/en-us/libr...70(VS.85).aspx

CYA
Steve

> A raw 2007 document wouldn't qualify.

I think you are right here. After I posted it, I remembered it is a zipped
package of several xml files.
But indeed, saving it as a XML file could be an option.

Erik

"Geoff Schaller" <geoffx@softxwareobjectives.com.au> wrote in message
news:48b9e5e0$1@dnews.tpgi.com.au...
> No, not really because it only applies to xml structured data and not
> office 2007 docs natively because they are zipped. I'd need to consult
> with Ed on this but I think you are better off storing docx etc in a
> varbinary column and using full text search on it. When you declare the
> document type (jpeg, pdf, doc, docx etc) it is capable of searching it.
>
> If what you wanted was xml based search then you have to save it into the
> xml column as xml. > Geoff
>
>
>
> "ER!k \/!sser" <nospam@nospam.com> wrote in message
> news:6hstleFnsc1jU1@mid.individual.net:
>
>> >
>> > Each DBMS has a binary field type of one kind or other. Simply use
>> > that.
>> > In MS SQL there is a data type called varbinary(max) which is the
>> > recommended type.
>>
>>
>> Never done this, but I am pretty sure that if you are using Office 2007
>> you
>> could store the files in a XML column.
>> That makes it possible to query for the content of the document as
>> well...
>>
>> Erik
>>
>>
>>
>>
>>
>>
>> >
>> > Geoff
>> >
>> >
>> > "Rob van Erk" <erk.v@hotmail.com> wrote in message
>> > news:12baf128-9df0-4eb9-a58b-cd144d2c7ce6@i76g2000hsf.googlegroups.com:
>> >
>>
>> >> Geoff,
>> >>
>> >> Basically the question was how I should consider to add Excel/Word
>> >> documents in a SQL database.
>> >> I was looking at an example on this. Not sure what kind of type field
>> >> I should define, etc. I will try to use Sergio's program.
>> >>
>> >> Thanks,
>> >> Rob
>>
>> >
>