sql server guardar y mostrar imagen

[nageswaragunupudi]

Saving:

Code: Select all | Expand

oRs:Fields( <image_fieldname> ):Value := HB_STRTOHEX( MEMOREAD( cJpgFile ) )
oRs:Update()
 

Display:

Code: Select all | Expand

cImage := oRs:Fields( <image_fieldname> ):Value

// 1
XImage( cImage )

// 2
@ r,c XIMAGE cImage SIZE w,h OF oWnd
 

[Rick Lipkin]

George

Are you trying to save an image or file to a Sql Table ?? and trying to reconstruct it back to its original file ?? I may have interpreted your question incorrectly .. however, This is how I store and retrieve a file from Sql Server ..

1) Use VarBinary(Max) field type
2 here is the code to save your file ( any file ) to a VarBinary(max) field in your table ... this is my code .. note this line is the key to the code

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

Code: Select all | Expand

//------------------------------
Static Func _Doit( oRsTrav, oRsCh, cPROJECTEID, cPath,oDLg )

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

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

cFILE := upper(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("doceid"):Value        := cEID
oRsCH:Fields("TFormEid"):Value      := cPROJECTEID
oRsCh:Fields("DImport"):Value       := nDateTime
oRsCh:Fields("importby"):Value      := xLOGIN
oRsCh:Fields("datalen"):Value       := nBYTES
oRsCh:Fields("filename"):Value      := cFILENAME
oRsCh:Fields("notes"):AppendChunk( VTArrayWrapper():New( 17, cBUFFER ) )   // <--- here is the key to store the file in binary

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("DImport")
oRsCh:MoveFirst()
oRsCh:Find( "doceid = '"+cEID+"'" )

oDLG:END()
RETURN(.T.)

3) To retrieve the image from a VarBinary(max) field ..

Please note this line of code
cREAD := oRsCh:Fields("NOTES"):GetChunk( oRsCh:Fields("datalen"):Value)

Code: Select all | Expand

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

LOCAL nHANDLE, cREAD, cFILENAME, saying

If oRsCH:Eof
   Saying := "Sorry .. there are no records to view"
   Msginfo( saying )
   Return(.f.)
Endif


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

cREAD := oRsCh:Fields("NOTES"):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.)

Don't know if this is what you have in mind .. Hope this helps.

Rick Lipkin

[nageswaragunupudi]

Anyone can build and run this sample:
Here we connect to a free MSSQL server in the cloud provided by FWH for testing purposes.

Code: Select all | Expand

function TestImageMSSQL

   local oCn, cSql, oRs, oDlg, oImage
   local cTable   := "TEST_JPG"

   oCn := FW_OpenAdoConnection( "MSSQL,208.91.198.196,gnraore3_,fwhmsdemo,fwh@2000#", .t. )

   oCn:Execute( "DROP TABLE IF EXISTS " + cTable )
   FWAdoCreateTable( cTable, { { "NAME", "C", 20, 0 }, { "PHOTO", "m", 10, 0 } }, oCn )

   oRs   := FW_OpenRecordSet( oCn, cTable )
   oRs:AddNew()
   oRs:Fields( "NAME"  ):Value := "OLGA1"
   oRs:Fields( "PHOTO" ):Value := HB_STRTOHEX( MEMOREAD( "c:\fwh\bitmaps\olga1.jpg" ) )
   oRs:Update()

   XBROWSER oRs

   oRs:MoveFirst()
   DEFINE DIALOG oDlg SIZE 400,500 PIXEL
   @ 10,10 XIMAGE oImage SOURCE oRs:Fields( "PHOTO" ):Value SIZE -10,-10 OF oDlg
   ACTIVATE DIALOG oDlg CENTERED

   oRs:Close()
   oCn:Close()

return nil

Browse:


XImage:

[nageswaragunupudi]

Two ways of creating the above table
METHOD-1

Code: Select all | Expand

TEXT INTO cSql
CREATE TABLE TEST_JPG (
   ID    INT IDENTITY(1,1) PRIMARY KEY,
   NAME  VARCHAR( 20 ),
   PHOTO IMAGE
   )
ENDTEXT
   oCn:Execute( cSql )
 

METHOD-2: Simpler and recommended by FWH

Code: Select all | Expand

FWAdoCreateTable( cTable, { { "NAME", "C", 20, 0 }, { "PHOTO", "m", 10, 0 } }, oCn )
 

Note: Use small "m" for binary data

[nageswaragunupudi]

Alternative methods for inserting image (or any binary) data:

USING RECORDSET OBJECT:

Method-1: Longer code if you have patience to write

Code: Select all | Expand

oRs   := FW_OpenRecordSet( oCn, cTable )
oRs:AddNew()
oRs:Fields( "NAME"  ):Value := "OLGA1"
oRs:Fields( "PHOTO" ):Value := HB_STRTOHEX( MEMOREAD( "c:\fwh\bitmaps\olga1.jpg" ) )
oRs:Update()
 

Method-2: Short and simpler code, for lazy people like me.

Code: Select all | Expand

oRs   := FW_OpenRecordSet( oCn, cTable )
oRs:AddNew( { "NAME", "PHOTO" }, { "OLGA1", HB_STRTOHEX( MEMOREAD( "c:\fwh\bitmaps\olga1.jpg" ) ) } )
 

WITHOUT OPENING RECORDSET:

Code: Select all | Expand

#include "adodef.ch"
...
...
cSql  := SQL INSERT INTO TEST_JPG ( NAME, PHOTO ) VALUES ( "OLGA1", MEMOREAD( "c:\fwh\bitmaps\olga1.jpg" ) )
oCn:Execute( cSql )
 

[Cgallegoa]

Jorge,

HB_STRTOHEX() es de 'Harbour'. Si estás usando 'xHarbour' la function es STRTOHEX()

O puedes hacer:

Code: Select all | Expand

#xtranslate HB_STRTOHEX([<xx,...>]) => STRTOHEX([<xx>])

[nageswaragunupudi]

Hola, me da error HB_STRTOHEX no definida, que librería me falta?

Saludos
Jorge

Use STRTOHEX(...) instead of HB_STRTOHEX(...) if you are using xHarbour.