Binary file to field via SQL - SOLVED!

[fraxzi]

Dear All,

if sending image via SQL, there's BmpToStr() function. What if sending binary (.pdf or .rtf)? BinToStr() or similar?

Anyone?

[nageswaragunupudi]

If you use latest FW libraries with ADO,
it is as simple as
oRecSet:Photo := MemoRead( "john.jpg" )
or
oRecSet:Reply := MemoRead( "reply.rtf" )

[fraxzi]

RAO,

I tried this:

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

 

Using Bitmap/Image working fine.. Any idea?

[reinaldocrespo]

Hi Frances;

Your code should work just fine regardless of the file type. Are you getting some error?

You may store any file type on a blob field by simply reading the file using MemoRead() into a local variable that's then used in AdsSetBinary() to set the parameter for the sql statement. It really does not matter what the file type is.

Reinaldo.

[reinaldocrespo]

I just re-read your post, I think there might be a problem with the actual sql statement. I'm not sure the insert portion is the correct syntax.

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
 

should be:

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
 

Reinaldo.

[Rick Lipkin]

Francis

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.

Here is the code that has worked for me ( may be xHarbor specific using VTArrayWrapper() ) .. it uses the AppendChunk() method to import any binary file, .pdf, .docx, xls, etc.. I am sure Rao could simplify this code into a wrapper.

Rick Lipkin

ps .. I did not see you were using Ads .. the code below is generic ansi Sql and works for Access or Ms Sql Server, It may or may not work for Ads

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

[fraxzi]

Reinaldo,

Thanks for your insights.

I've been using that MERGE syntax with no Issue. But anyhow, I followed your suggestion.


Then later I realized my stupidity.. You are correct. something went very wrong with my syntax.

This is the correct way:

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

Issue SOLVED.

[fraxzi]

Rick,

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.

Yes exactly. the field type in ADS is binary.