Dear All,
if sending image via SQL, there's BmpToStr() function. What if sending binary (.pdf or .rtf)? BinToStr() or similar?
Anyone?
If you use latest FW libraries with ADO,
it is as simple as
oRecSet:Photo := MemoRead( "john.jpg" )
or
oRecSet:Reply := MemoRead( "reply.rtf" )
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
ENDHi 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.
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 typecSql := "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
/------------------------------
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.)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.