FiveTech Software tech support forums

Code: Select all  Expand view

 cBuff := MemoRead('TestRTF.RTF')

 cSql := "MERGE entry_trn ON ( ticket_id = '123' ) "+;
         "WHEN MATCHED THEN UPDATE SET ( concerns = :bin_data1 ) " +;
         "WHEN NOT MATCHED THEN INSERT ( ticket_id = '123', concerns = :bin_data2 );"     //concern field is binary type


 dbSelectArea( 0 )

 IF AdsCreateSqlStatement( 'XX', ADS_ADT, hADS )

    IF AdsPrepareSql( cSql )

       IF AdsSetBinary( "bin_data1", cBuff )
       ELSE
          msginfo('oopss...','bin_data1')  //ok good never reached
       END

       IF AdsSetBinary( "bin_data2", cBuff )
       ELSE
          msginfo('oopss...','bin_data2')  //ok good never reached
       END

      /* I have no problem with bitmap/image

        cBmp := BmpToStr(oImg:hBitmap)
        AdsSetBinary( 'iPicture', cBmp )                     //ipicture field is image type.

      */

       IF AdsExecuteSQL()    
          AdsCloseSQLStatement()
       ELSE
          cerror := ''
          AdsGetLastError( @cerror)
          msginfo('oopss... ' + cerror ,'AdsExecuteSQL') //here..
       END

    ELSE
       msginfo('oopss...','AdsPrepareSql')  //ok good never reached
    END

 ELSE
    msginfo('oopss...','AdsCreateSqlStatement') //ok good never reached
 END

 

Code: Select all  Expand view

cSql := "MERGE entry_trn ON ( ticket_id = '123' ) "+;
         "WHEN MATCHED THEN UPDATE SET ( concerns = :bin_data1 ) " +;
         "WHEN NOT MATCHED THEN INSERT ( ticket_id = '123', concerns = :bin_data2 );"     //concern field is binary type
 

Code: Select all  Expand view

cSql := "MERGE entry_trn ON ( ticket_id = '123' ) "+;
         "WHEN MATCHED THEN UPDATE SET ( concerns = :bin_data1 ) " +;
         "WHEN NOT MATCHED THEN INSERT ( ticket_id, concerns ) VALUES ( '123', :bin_data2 );"     //concern field is binary type
 

Code: Select all  Expand view

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

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("charter"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )  // here is the key VTArrayWrapper
                                                                     // to make the AppendChunk() work  
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.)

Code: Select all  Expand view

MERGE entry_trn ON ( ticket_id = '123' )
WHEN MATCHED THEN
UPDATE SET concerns = :bin_data1  
WHEN NOT MATCHED THEN  
INSERT ( ticket_id, concerns )  
VALUES ( '123', :bin_data2 );
 

If I read your post correctly, you are trying to import a binary file to a Sql Table ? If so, the field type ( sql server ) must be VarBinary (max ) or equivalent.