To Mr. Nages

[elvira]

Dear Mr. Nages,

Can you please indicate me how to store and retireve a Pdf file with Ado?.

Many thanks. !

[Rick Lipkin]

Elvira

Here is an entire programming code I developed to import any binary file into a Sql database ( varbinary(max) for Sql Server or OleEnbeded for Access ) and then to retrieve and re-assemble the file on demand.

Note .. this code works in xHarbour .. not sure if it will work in Harbour. The Import routine is in _Doit() .. notice this line to assign the data to the field name 'Charter"

Code: Select all | Expand

oRsCh:Fields("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )
 

The re-assemble the binary file to view in _ViewUm()

Code: Select all | Expand

cREAD := oRsCh:Fields("charter"):GetChunk( oRsCh:Fields("datalen"):Value)
 

Rick Lipkin

Code: Select all | Expand

// Chimport.prg

#INCLUDE "FIVEWIN.CH"
#INCLUDE "XBROWSE.CH"

STATIC oDLG

//---------------------
FUNC _Chimport( oRsProj, cPROJECTEID, cMODE, oProj )

LOCAL SAYING
LOCAL oRsCh, cSQL, oERR

cDEFA := SET(7)

SysReFresh()

IF EMPTY( cPROJECTEID ) .or. cPROJECTEID = "BOGUS"
   SAYING := "SORRY .. you can not Import a Charter until you"+CHR(10)
   SAYING += "Save the Project record First .. Aborting"+CHR(10)
   MsgInfo( SAYING )
   _CleanUP()
   RETURN(.F.)
ENDIF

IF EMPTY( cMODE )
   cMODE := 'V'
ENDIF


/*
IF xSUPER = 'Y' .or. xPROJ = 'Y'
ELSE
   SAYING := "SORRY ... You do not have Rights to run this Module"
   MsgAlert( SAYING )
   _cleanup()
   RETURN(.F.)
ENDIF
*/

oRsCh := TOleAuto():New( "ADODB.Recordset" )
oRsCh:CursorType     := 1        // opendkeyset
oRsCh:CursorLocation := 3        // local cache
oRsCh:LockType       := 3        // lockoportunistic

cSQL := "SELECT * from CHARTER where PROJECTEID = '"+cPROJECTEID+"' order by date_imported"

TRY
   oRsCh:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening CHARTER table" )
   _CleanUP()
   RETURN(.F.)
END TRY

IF oRsCh:eof

   IF cMODE = "V"
      SAYING := "SORRY .. No CHARTER has been Uploaded"
      MsgInfo( SAYING )
      oRsCh:CLose()
      _CleanUP()
      RETURN(.F.)
   ENDIF

   IF xPROJ = 'Y' .or. xSUPER = 'Y'
      SAYING := "There are no CHARTER documents Loaded associated"+chr(10)
      SAYING += "with this project named "+oRsProj:Fields("projname"):Value+chr(10)
      SAYING += "Would you like to Import your CHARTER Now ?"+CHR(10)

      IF MsgYesNo( SAYING )
         IF _Cimport( oRsProj, oRsCh, cPROJECTEID )
         ELSE
            oRsCh:Close()
            _CleanUP()
            RETURN(.F.)
         ENDIF

      ELSE
         oRsCh:Close()
         _CleanUP()
         RETURN(.F.)
      ENDIF

   ELSE
      SAYING := "SORRY .. No CHARTER has been Uploaded"
      MsgInfo( SAYING )
      oRsCh:CLose()
      _CleanUP()
      RETURN(.F.)
   ENDIF

ENDIF

// browse here for retrieval

StandardGrad()

_Charbrow( oRsCh, cPROJECTEID, oRsProj, cMODE, oProj )

oRsCh:CLose()
LightGreyGrad()

SysReFresh()

RETURN(.T.)

//------------------------
Static Func _Charbrow( oRsCh, cPROJECTEID, oRsProj, cMODE, oProj )

LOCAL SAYING,oBTN1,oBTN2,oBTN3,oBTN4
LOCAL oDLG,oLBX, cPROJNAME, cTITLE

IF EMPTY( cPROJECTEID ) .or. cPROJECTEID = "BOGUS"
   SAYING := "In Order to View a Charter Document.. you have to IMPORT one First"
   MsgInfo( SAYING )
   RETURN(.F.)
ENDIF

IF cMODE = "A"
   cMODE := "V"
ENDIF

cPROJNAME := oRsProj:Fields("projname"):Value
cPROJNAME := alltrim( cPROJNAME )

DO CASE
CASE cMODE = "E"
     cTITLE := "CHARTER and SCHEDULE History for Project    "+cPROJNAME+"     EDIT"
OTHERWISE
     cTITLE := "CHARTER and SCHEDULE History for Project    "+cPROJNAME+"     VIEW"
ENDCASE

DEFINE DIALOG oDlg RESOURCE "CHARBROW" ;
      TITLE cTITLE ;

      REDEFINE xBROWSE oLBX   ;
      RECORDSET oRsCh         ;
      ID 111 of oDlg          ;
      COLUMNS "date_imported",;
              "imported_by",  ;
              "importtype",   ;
              "filename",     ;
              "datalen"       ;
      COLSIZES 90,90,90,200,90   ;
      HEADERS "Date Import",  ;
                "Imported By",;
                "ImportType", ;
                "Filename",   ;
                "Bytes Loaded";
      AUTOSORT AUTOCOLS LINES CELL

    olBx:nRowHeight := 15
    oLbx:bLDblClick := { |nRow,nCol |_Viewum( oRsCh, oPROJ, oDLG ) }


   REDEFINE BUTTON oBTN1 ID 112  ;
       ACTION ( oDLG:END() )     ;
       DEFAULT

   REDEFINE BUTTON oBTN2 ID 114  ;   // view
       ACTION ( _ViewUm( oRsCh, oProj, oDLG ) )

   REDEFINE BUTTON oBTN3 ID 115  ;   // import
       ACTION (  _Cimport( oRsProj, oRsCh, cPROJECTEID ),;
             oLBX:ReFresh(), SysReFresh() )

   REDEFINE BUTTON oBTN4 ID 113  ;   // delete
       ACTION ( _DelUm( oRSCh, oLbx ),;
              oLBX:ReFresh(), SysReFresh() )

ACTIVATE DIALOG oDlg ;
         ON INIT ( _ChkButtons( cMODE, oBTN3, oBTN4 ) )

RETURN( .T. )

//-----------------------
Static Func _ChkButtons( cMODE, oBTN3, oBTN4 )

IF EMPTY(cMODE)
   cMODE := "V"
ENDIF

IF cMODE = "A"     // should have never got here in add mode
   cMODE := "V"
ENDIF

IF cMODE = "V"
   oBTN3:Hide()
   oBTN4:Hide()
   RETURN(NIL)
ENDIF

IF cMODE = "E"
   DO CASE
   CASE xSUPER = 'Y'
         // do nothing  show all buttons
   CASE xPROJ = 'Y' .and. xSUPER <> 'Y'
        oBtn4:Hide()
   ENDCASE
ENDIF

RETURN(NIL)

//-----------------------------
Static Func _DelUm( oRs1, oLbx )

LOCAL SAYING,oErr

IF xSUPER = 'Y' .or. xProj = 'Y'

   SAYING := "Are you SURE you want to DELETE this Imported Document ?"
   IF MsgYesNo( SAYING )
      Try
        oRs1:Delete()
      Catch
        MsgInfo( "Delete Failed")
        oLbx:SetFocus()
        RETURN(.F.)
      End try

      TRY
        oRs1:MoveNext()
      CATCH
      END TRY

      IF oRs1:eof .and. .not. oRs1:bof
         TRY
           oRs1:MoveFirst()
         CATCH
         END TRY
      ENDIF
   ENDIF

ENDIF

oLbx:ReFresh()
*SysReFresh()

RETURN(.T.)

//-------------------------------
Static Func _Viewum( oRsCh, oPROJ, oDLG )

LOCAL nHANDLE, cREAD, cFILENAME

cFILENAME := alltrim(oRsCh:Fields("filename"):Value )

cREAD := oRsCh:Fields("charter"):GetChunk( oRsCh:Fields("datalen"):Value)
FERASE( xVOL+"\DBTMP\"+cFILENAME )

nHANDLE := FCREATE(  xVOL+"\DBTMP\"+cFILENAME, 0 )
IF FERROR() <> 0
   SAYING := "Error Creating file "+(xVOL+"\DBTMP\"+cFILENAME)+CHR(10)
   SAYING += "Error "+STR(FERROR())+CHR(10)
   MsgInfo( SAYING )
   RETURN(.F.)
ENDIF

FWRITE( nHANDLE, cREAD )   // write out the file
FCLOSE( nHANDLE )


SysReFresh()

SHELLEXECUTE( "", "open", (xVOL+"\DBTMP\"+cFILENAME),"","",1)

SysReFresh()

RETURN(.T.)

//-------------------------
Static Func _Cimport( oRsProj, oRsCh, cPROJECTEID )

LOCAL lOK,oType,cType,oBtn1,oBtn2,cLocation,cPath,oPath
Local oFontB,aWinVer,oFont

Local oSay1,oSay2,oSay3,oSay4
Local cSay1,cSay2,cSay3,cSay4

cLOCATION := GetEnv( "USERPROFILE" )

If Empty( cLOCATION )
   cLOCATION := "C:\"
Else

   aWinVer := GetVersion()
   IF aWinVer[1] = 5  // xp
      cLOCATION := cLOCATION+"\MY DOCUMENTS"
   Else
      cLOCATION := cLOCATION+"\DOCUMENTS"
   Endif

   aDIR := DIRECTORY( cLOCATION+"\*.*","D" )
   If Len( aDir ) > 0
   Else
      cLOCATION := "C:\"
   Endif
Endif

// .t.
oFont   := TFont():New("Ms Sans Serif",,-6,.F.,.F.,,,,.F. )
oFontB  := TFont():New("Ms Sans Serif",,-6,.F.,.T.,,,,.F. )


lOK    := .T.
cPATH  := lower(cLocation) // "C:\*.PDF, *.DOC, *.DOCx"
cType  := "CHARTER"

cSay1  := "The Project CHARTER is the official 'Executive Sponsorship' document that authorizes "
cSay1  += "this project, its approved resources, deliverables, goals/objectives, and constraints "
cSay1  += "for the life of the project. The CHARTER template can be found on our Intranet. "
cSay1  += "Please click on the line below to drill to the file location of your CHARTER document "
cSay1  += "you wish to Import for this project. You may attach updated  versions, as needed."+chr(10)
cSay1  += "    "+chr(10)
cSay1  += "A work breakdown structure (WBS) is a deliverable oriented decomposition of a project "
cSay1  += "into smaller components. It defines and groups a project’s discrete work elements in a "
cSay1  += "way that helps organize and define the total work scope of the project.  A WBS also "
cSay1  += "provides the necessary framework for detailed cost estimating and control along with "
cSay1  += "providing guidance for schedule development and control."+chr(10)
cSay1  += "    "+chr(10)
cSay1  += "A Project Schedule identifies each task needed to complete the scope of a project. "
cSay1  += "It defines the 'what, when and who' and assigns deadlines for their completion. "
cSay1  += "Scheduling requires a comprehensive understanding of which action steps need to "
cSay1  += "get done and when."



StandardGrad()

DEFINE DIALOG oDlg RESOURCE "CHIMPORT"         ;
       TITLE "CHARTER and SCHEDULE Import Routine"          ;

   REDEFINE SAY oSay1  PROMPT cSay1  ID 134 OF oDlg UPDATE
   oSay1:SetFont( oFont )

   REDEFINE SAY oSay2 /* PROMPT cSay1 */ ID 113 OF oDlg UPDATE
   oSay2:SetFont( oFontB )

   REDEFINE SAY oSay3 /* PROMPT cSay1 */ ID 114 OF oDlg UPDATE
   oSay3:SetFont( oFontB )

   REDEFINE SAY oSay4 /* PROMPT cSay1 */ ID 115 OF oDlg UPDATE
   oSay4:SetFont( oFontB )
   oSay4:SetColor( nRgb( 7,7,224 ))

   REDEFINE COMBOBOX oType  var cType ID 158 of oDlg ;
         ITEMS { "CHARTER","PROJSCH" }

   REDEFINE GET oPath  var cPath   ID 119 of oDlg BITMAP "search"  ;
            action( FileGet( @lOK,@cPath,oPath ) )
   oPath:lAdjustBtn := .t.
   oPath:bKeyDown:= {|nK| if( nK==VK_RETURN,FileGet( @lOK,@cPath,oPath ) , ) }

   REDEFINE BTNBMP oBTN1 ID 111 of oDlg;
         RESOURCE "OK", "DOK", "DOK" ;
         PROMPT "  &Ok    " LEFT 2007;
         ACTION ( lOK := _doit( oRsProj, oRsCH, cPROJECTEID,cType, @cPath ) )

   REDEFINE BTNBMP oBTN2 ID 112 of oDlg ;
         RESOURCE "CLOSE", "DCLOSE", "DCLOSE" ;
         PROMPT "&Cancel" LEFT 2007;
         ACTION ( lOK := .F., oDlg:End() )

ACTIVATE DIALOG oDlg

LightGreyGrad()
RELEASE oFontB
RELEASE oFont

RETURN( lOK )

//------------------------------
Static Func Fileget( lOK, cPath, oPath  )

LOCAL SAYING,cLocation,aDir,nSize

SysReFresh()

IF lOK = .F.
   oDLG:END()
   RETURN(.F.)
ENDIF


/*
cPATH := cGETFILE( "PDF   (*.pdf)| *.pdf|" +   ;
                   "DOCx  (*.docx)| *.docx|" +   ;
                   "DOC   (*.doc)| *.doc|"   ;
                   ,"Please select a file to Import", 4 )
*/

*cPATH := cGETFILE( "ALL   | *.*|" , ;
*                   "Please select a file to Import( .Exe or .Dll not allowed)" )

cPATH := cGETFILE( "PDF   (*.pdf)|  *.pdf|" +   ;
                   "DOCx  (*.docx)| *.docx|" + ;
                   "DOC   (*.doc)|  *.doc|"   + ;
                   "XLS   (*.xls)|  *.xls|" + ;
                   "XLSx  (*.xlsx)| *.xlsx|"   + ;
                   "All   (*.*)| *.*|" ;
                   ,"Please select a file to Import (.exe,.dll or files > 1gb are not Allowed", 6 )


cPATH := UPPER( alltrim(cPATH) )

/*
IF (AT( ".PDF", cPATH ) > 0 .or.;
    AT( ".DOC", cPATH ) > 0 ).and. .not. EMPTY( cPATH )
ELSE
   SAYING := "Sorry ... no files were chosen to import .. Aborting"
   MsgInfo( SAYING )
   oDLG:End()
   lOk := .f.
   RETURN(lOk)
ENDIF
*/

Do Case
Case AT( ".EXE", cPATH ) > 0
     Saying := "Sorry ... .EXE files are not Allowed"
     MsgInfo( Saying )
     oDlg:End()
     lOk := .f.
     Return(lok)
Case AT( ".DLL", cPATH ) > 0
     Saying := "Sorry ... .DLL files are not Allowed"
     MsgInfo( Saying )
     oDlg:End()
     lOk := .f.
     Return(lok)
Case EMPTY( cPATH )
     Saying := "Sorry .. no files to Import"
     MsgInfo( Saying )
     oDlg:End()
     lOk := .f.
     Return(lok)
End Case

aDir := Directory( cPath )
nSize := aDir[1] [2]

If nSize > 1000000
   Saying := "Sorry .. Your file size is greater than 1gb  "+chr(10)
   Saying += "  "+chr(10)
   Saying += lower(cPath)+chr(10)
   Saying += "File Size "+ltrim(str(nSize/1000000))+"gb"+chr(10)
   Saying += "  "+chr(10)
   Saying += "and can not be Uploaded .. Aborting"
   MsgInfo( Saying )
   oDlg:End()
  lOk := .f.
  Return(lok)
Endif

cPath := Lower( cPath )
oPATH:ReFresh()
SysReFresh()

lOk := .t.

RETURN(lOk)

//------------------------------
Static Func _Doit( oRsProj, oRsCh, cPROJECTEID, cType, cPath )

LOCAL cFILENAME, nSTRING, nLEN, nSTART, SAYING, nDATETIME
LOCAL nHANDLE, nBYTES, cEID, cFILE, dDATE

LOCAL cBUFFER   //cBinaryData        // <------- This is the actual data to be stored
LOCAL nBytesRead

cFILE := ALLTRIM( cPATH )     // C:\DBTMP\CHARTER.DOC
nLEN  := LEN( cFILE )

nSTART := RAT( "\", cFILE )

IF nSTART > 0
ELSE
   SAYING := "INVALID File name or Location .. Aborting"
   MsgInfo( SAYING )
   oDLG:END()
   RETURN(.F.)
ENDIF

// get file name //
cFILENAME := SUBSTR( cPATH, nSTART+1, nLEN-nSTART )    // CHARTER.PDF

IF LEN(cFILENAME) > 35
   SAYING := "Sorry .. the maximum length of your file"+chr(10)
   SAYING += cFILENAME+CHR(10)
   SAYING += "is longer than 35 characters. Please re-name"+chr(10)
   SAYING += "your file to meet the 35 max length"+chr(10)
   MsgInfo( saying )
   oDlg:end()
   RETURN(.F.)
ENDIF


// open file //
nHANDLE := FOpen( cFILE )
IF FERROR() <> 0
   SAYING := "Error reading file "+cFILE+CHR(10)
   SAYING += " "+STR(FERROR())+CHR(10)
   MsgInfo( SAYING )
   oDLG:END()
   RETURN(.F.)
ENDIF

// get number of bytes in file
nBYTES := FSEEK( nHANDLE, 0,2 )

// pad the buffer nBytes+1
cBUFFER := SPACE(nBYTES+1)

FSeek( nHANDLE, 0, 0 )
nBytesRead   := FRead( nHANDLE, @cBuffer, nBytes )

FClose( nHANDLE )

if nBytesRead != nBytes
   SAYING := "nBytesRead = "+str(nBYTESREAD)+CHR(10)
   SAYING += "nBytes     = "+str(nBYTES)+CHR(10)
   SAYING += "Error Reading Data"+chr(10)
   MsgInfo( saying )
   oDLG:END()
   RETURN ( .F. )
endif

cEID := _GenEid()
IF cEID = "BOGUS"
   oDlg:End()
   RETURN(.F.)
ENDIF

nDateTime := dtoc(date())+" "+time()

oRsCh:AddNew()

oRsCh:Fields("chartereid"):Value    := cEID
oRsCH:Fields("projecteid"):Value    := cPROJECTEID
oRsCh:Fields("date_imported"):Value := nDateTime
oRsCh:Fields("imported_by"):Value   := xLOGIN
oRsCh:Fields("datalen"):Value       := nBYTES
oRsCh:Fields("filename"):Value      := cFILENAME
oRsCh:Fields("IMPORTTYPE"):Value    := cType
oRsCh:Fields("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )

oRsCh:Update()

SysReFresh()

SAYING := "Bytes Read   = "+str(nBYTESREAD)+CHR(10)
SAYING += "Bytes Stored = "+str(nBYTES)+CHR(10)
SAYING += "  "+CHR(10)
SAYING += "Upload Complete for file name "+cFILENAME+chr(10)
MsgInfo( saying )

oRsCh:Sort("date_imported")
oRsCh:MoveFirst()
oRsCh:Find( "chartereid = '"+cEID+"'" )

oDLG:END()
RETURN(.T.)

//-------------------
Static Func _GenEid()

LOCAL nRAND, cRAND
LOCAL oRs, cSQL, oERR

oRs:= TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSQL := "SELECT chartereid from CHARTER"

TRY
   oRs:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening CHARTER table to Create Unique EID" )
   RETURN("BOGUS")
END TRY

cRAND := 'BOGUS'

DO WHILE .T.

   nRAND := nRANDOM(10000000000000000)

   // 1 is reserved and 0 is a null key //

   IF nRAND = 1 .or. nRAND = 0 .or. nRAND = NIL
      LOOP
   ENDIF

   cRAND := STRZERO(nRAND,18)

   IF oRs:eof
   ELSE
      oRs:MoveFirst()
      oRs:Find("chartereid = '"+cRAND+"'" )
   ENDIF

   IF oRs:eof
      EXIT
   ELSE
      LOOP
   ENDIF

   EXIT

ENDDO

oRs:Close()

RETURN( cRAND )

//--------------------
Static Func _CleanUP()

oPATH  := NIL
cPATH  := NIL
oDLG   := NIL


RETURN(.T.)

[nageswaragunupudi]

Dear Mr. Nages,

Can you please indicate me how to store and retireve a Pdf file with Ado?.

Many thanks. !

1. Create the field as Binary field
2. oRs:Fields( "fieldname" ):Value := STRTOHEX( MemoRead( <pdffilename> ) )
oRs:Update()
3. For MySql ensure configuring adequate size of "packet_size"
4. For reading you need not convert:
cMyVar := oRs:Fields( "fieldname" ):Value
is just enough.

1. Creating the field as binary field.
If you prefer to create table with your own sql like "CREATE TABLE ..", then you should set the field type of this field to binary field type appropriate to the database, eg. BLOB, LONGBINARY, etc.

Using the function FWAdoCreateTable( cTable, aStruct, oCn ) is not only easier to use, but also is portable across different databases. aStruct is an array like DbStruct(). If you want to create a binary field, specify the field type in aStruct array as "m" ( small m ). The function creates a long variable length binary field appropriate to the database being used.

2. Writing Data
(a) If you are using ADO recordset opened via FW_OpenRecordSet(...) or your own way, the syntax I mentioned above will work in case of small and medium sized data. In case of MySql, it is possible that the maximum configured packet_size is less than what you need to write the data in single statement. You can dynamically increase this size by
oCn:Execute( "SET SESSION max_allowed_packet=16777216;" ) // 16MB.
I am sure you won't be needing larger sizes than this.

Note: oField:AppendChunk() is required in case of (a) very huge data that can not be handled in one single update/insret and/or (2) where the network or server's configuration is the limiation. In such cases AppendChunk() is to be called repeatedly. Otherwise simple assignment ofield:Value := STRTOHEX( binarydata ) is better and simpler.

(b) XBrowse, TDataRow and TRecSet classes take care of all such conversions automatically.

(i) XBrowse: oCol:Value := MEMOREAD( pdf/image file ) is enough
(ii) TDataRow:
oRec := TDataRow():New( oRs )
oRec:MyBinaryField := MemoRead( <binarydatafile> )
oRec:Save()
(iii) TRecSet class
oRs := TRecSet():New():Open( oCn, cSql )
oRs:BinaryField := MemoRead( <binarydatafile> )

I encourage you to use these classes which reduce the size of your code. Less your code, easy maintenance and fewer bugs

(c) Reading Data:

As simple as :
cMyVar := oRs:Fields( "fieldname" ):Value


Note for Mr Rick:
It appears you are using an additional column to indicate the length of the data written to the binary field. It is not necessary. oField:ActualSize indicates the actual size of data written in the VarChar or VarBinary field.
GetChunk() and AppendChunk() are meant for those dialects which can not handle large amounts of data like we do in (x)Harbour. We can straightaway use access and assignment .

[Rick Lipkin]

Rao

The only reason I store the size of the data length of the binary file is to verify the size of the buffer going into the table and the verification of the file being re-created as it is being read out of the table.

I have found that it is best to limit the file size to 1 gb or less if you are storing it in a Sql table. There are pros and cons to storing binary data vs just referencing a datafile on a share... I have done it both ways.

Rick Lipkin