sql server guardar y mostrar imagen

[jpcavagnaro]

Hola gente, necesito ayuda como mostrar un campo "IMAGE" de una tabla sql server.

Lo guardo sin problema, pero no encuentro la forma de mostrar la imagen en jpg.

Saludos.
Jorge

[nageswaragunupudi]

Saving:

Code: Select all

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

Display:

Code: Select all

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

//------------------------------
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

//-------------------------------
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

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

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

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

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

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

WITHOUT OPENING RECORDSET:

Code: Select all

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

[jpcavagnaro]

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

Saludos
Jorge

[Cgallegoa]

Jorge,

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

O puedes hacer:

Code: Select all

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

[jpcavagnaro]

Buen día, funciono perfecto.

Muchas gracias

Saludos
Jorge.

[jpcavagnaro]

Una pregunta que me surgió, como manejan la calidad y/o tamaño de las imágenes.

Por el espacio que ocupan.

SAludos.
Jorge