SQL Server / ADO with attachments - Clipper

This is a discussion on SQL Server / ADO with attachments - Clipper ; 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 ...

+ Reply to Thread
Results 1 to 10 of 10

SQL Server / ADO with attachments

  1. Default 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

  2. Default Re: SQL Server / ADO with attachments

    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



  3. Default Re: SQL Server / ADO with attachments

    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",adLongVarBinary,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

  4. Default Re: SQL Server / ADO with attachments

    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

  5. Default Re: SQL Server / ADO with attachments

    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



  6. Default Re: SQL Server / ADO with attachments

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

    >



  7. Default Re: SQL Server / ADO with attachments

    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

    >
    > >



  8. Default Re: SQL Server / ADO with attachments

    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?

  9. Default Re: SQL Server / ADO with attachments

    Geoff

    A search on MSDN turned up

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

    CYA
    Steve



  10. Default Re: SQL Server / ADO with attachments

    > 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

    >>
    >> >

    >



+ Reply to Thread