xBrowse jpg in SQL-database

[Marc Vanzegbroeck]

Hi,

I want to browse data and bitmaps stored in an SQL-database with ADO.
Browsing the data comming from the database is no problem, also browsing a bitmap depending of the database is no problem.
Is the an example I can use?

Thanks,

[Rick Lipkin]

Marc

I presume you are trying to view jpg thumbnails imported into a Sql table. From my experience, the only way to store any file in a Sql table is to import the picture as a binary file (AppendChunk) into a ( sql server ) VarBinary(Max) or equivalent.

The data is stored as bytes and have to be re-assembled back in it original form ( GetChunk )to be viewed... so I don't know if it is feasable to do all of that within an xBrowse table.

Like you mentioned .. you can test to see if the BarBinary(Max) field isnil() or not then based on that you can have xBrowse show a generic picture icon.

Rick Lipkin

[Marc Vanzegbroeck]

Rick,

Indeed,I want to show jpg-files imported in a SQL-table in a browse.
I want to store it with "'0x"+STRTOHEX() into a MEDIUMBLOB field, and extracting it before showing it, but I don't know how to show it...

[nageswaragunupudi]

We advise 4 simple ways of inserting images into Ado table. Following program works for any database.

Code: Select all

#include "fivewin.ch"
#include "adodef.ch"  // important
//----------------------------------------------------------------------------//

function Main()

   local oCn, cTableName, aJpg

   oCn := <......... Ado connection object. Connect in your own way ..>
   cTableName := "MyImages"  // any other name you like
   aJpg            := { array of jpg files wih path }

   AdoImportImages( oCn, "MyImages", aJpg )

   oCn:Close()

return nil

//----------------------------------------------------------------------------//

function AdoImportImages( oCn, cTable, aJpg )

   CreateTable( oCn, cTable )
   UsingSQL(               oCn, cTable, cFileNoPath( aJpg[ 1 ] ), MemoRead( aJpg[ 1 ] ) )
   UsingAdoRecordSet( oCn, cTable, cFileNoPath( aJpg[ 2 ] ), MemoRead( aJpg[ 2 ] ) )
   UsingTDataRow(      oCn, cTable, cFileNoPath( aJpg[ 3 ] ), MemoRead( aJpg[ 3 ] ) )

   BrowseImagesTable( oCn, cTable )

return nil

//----------------------------------------------------------------------------//

function CreateTable( oCn, cTable )

   TRY
      oCn:Execute( "DROP TABLE " + cTable )
   CATCH
   END

   FWAdoCreateTable( cTable, { ;
      { "IMAGENAME",    'C',  20,   0 }, ;
      { "IMAGEDATA",    'm',  10,   0 } }, oCn )

/*
Notes: Use this function to create tables. Thie same syntax and notiation are compatible with different databases.
Data Type small 'm' indicates binary data in contast to "M" meaning text memo.
*/

return nil

//----------------------------------------------------------------------------//

function UsingSQL( oCn, cTable, cImageName, cImageData )

   local cSql

   PRIVATE table_name := cTable

   cSql  := SQL INSERT INTO &table_name ( IMAGENAME,IMAGEDATA ) VALUES ( cImageName, cImageData )
   oCn:Execute( cSql )
   // This works for ADO and non ado connections
return nil

//----------------------------------------------------------------------------//

function UsingAdoRecordSet( oCn, cTable, cImageName, cImageData )

   local oRs

   oRs   := FW_OpenRecordSet( oCn, cTable )
   oRs:AddNew( { "ImageName", "ImageData" }, { cImageName, STRTOHEX( cImagedata ) } )
   oRs:Close()

return nil

//----------------------------------------------------------------------------//

function UsingTDataRow( oCn, cTable, cImageName, cImageData )

   local oRs, oRec

   oRs   := FW_OpenRecordSet( oCn, cTable )
   oRec  := TDataRow():New( oRs, .t. )
   oRec:ImageName    := cImageName
   oRec:ImageData    := cImageData
   oRec:Save()
   oRs:Close()

return nil

//----------------------------------------------------------------------------//

function BrowseImagesTable( oCn, cTable )

   local oRs   := FW_OpenRecordSet( oCn, cTable )

   XBROWSER oRs FASTEDIT SETUP ( oBrw:ImageData:nDataBmpAlign := AL_CENTER, oBrw:lCanPaste := .t. )

   oRs:Close()

return nil
 

3 methods are shown in the 3 Useby functions in the above program. Now the 4th method.

Copy any image from Web or any other imaging program or document into clipboard. Move to any Image cell in the xbrowse. Press Ctrl-V. The image is pasted into the cell and also is written in the database table.


This program is tested with MSSQL (SQLEXPRESS) and with MYSQL before posting.

Specific note for MySql: By default the maximum size of packet size for communication between client and server is only 1 MB and this is not adequate for images. You need to increase this size sutabluy once. Please see
// https://dev.mysql.com/doc/refman/5.5/en ... large.html

[Marc Vanzegbroeck]

Thank you for the 4 examples.
I will try them and let you know the results.

[Marc Vanzegbroeck]

Hi,

I have use

Code: Select all

oSQL:execute( "INSERT INTO 'boeken' ( nummer,cover) VALUES ( 1,"+STRTOHEX(memoread("C:\COVERS\1.jpg"))+" )")

to inport the jpg-files into the database.

In my xbrowse I use

Code: Select all

oBrw:bline = {|| { str(oRs:Fields( 'nummer' ):Value),oRs:Fields( 'cover' ):Value  }}

but the column that should show the bitmap is empty.

Should I use something special to show bitmap?

[nageswaragunupudi]

1) Please use the syntax as I provided. That is safe.
2) bLine is not for xbrowse. It is for wbrowse.

For xbrose just use:

@ r,c XBROWSE oBrw DATASOURCE oRs COLUMNS "nummer", "naam", "cover" ..............

[Marc Vanzegbroeck]

What should I use for bLine for wbrowse?
I also have some wbrowse in an existing program I want to modify.

[nageswaragunupudi]

It is xbrowse which is provided with display of images.
I advise you to use xbrowse for this purpose

[Marc Vanzegbroeck]

Thank you, I will use xbrowse

It is xbrowse which is provided with display of images.
I advise you to use xbrowse for this purpose

[nageswaragunupudi]

I have use
Code:
oSQL:execute( "INSERT INTO 'boeken' ( nummer,cover) VALUES ( 1,"+STRTOHEX(memoread("C:\COVERS\1.jpg"))+" )")

to inport the jpg-files into the database.

This may not work. Also you need to write this statement differently for different databases.
As we recommend, please use this syntax:

Code: Select all

cSql  := SQL INSERT INTO boeken ( nummer, cover ) VALUES ( 1, memoread( "c:\covers\1.jpg" ) )
oCn:Execute( cSql )
 

This works with all databases

[cdmmaui]

Hi Rao,

I am getting the following error while trying to save images to MS SQL table. What am I doing wrong?

ERROR: Incorrect syntax near '+'. (0x80040E14)

Code:
cSqlIns := [INSERT INTO asset_chart(]
cSqlIns += [itemnumber,]
cSqlIns += [inspcft]
cSqlIns += [)]

cSqlIns += [ VALUES (]
cSqlIns += ['] + _SqlClean( cItem ) + [',]
cSqlIns += MemoRead( cCft + aImages[nX1,1] )
cSqlIns += [)]
TRY
oSql:Open( cSqlIns, xCONNECT )
nAdded++
CATCH oError
IF MsgNoYes( "Unable to Perform Table Update, Process Aborted." + cEol + oError:Description + cEol + cSqlIns + cEol + xCONNECT, Ptitle )
MemoWrit( 'sql-err.txt', oError:Description )
EXIT
ENDIF
END

[cdmmaui]

Rao,

Here is the complete code in case you need it:

TRY
oSql:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "Unable to Load SQL Driver" )
RETURN (.F.)
END

// Set...
oSql:CursorType := 1 // opendkeyset
oSql:CursorLocation := 3 // local cache
oSql:LockType := 3 // lock opportunistic

// Init
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xDATABASE+';User Id='+xUSERID+';Password='+xPASSWORD
aImages := Directory( cCft + "*.*" )

FOR nX1=1 TO LEN(aImages)
cTemp := aImages[nX1,1]
cItem := ""

oSay:SetText( LTRIM( STR( ROUND( (nX1/LEN(aImages))*100, 2), 9, 3) ) + "% Complete . . ." )
Sysrefresh()


FOR nX2:=1 TO LEN(cTemp)
cChar := SUBSTR(cTemp,nX2,1)
IF (cChar=="-") .OR. (cChar=='.')
EXIT
ENDIF
cItem += cChar
NEXT nX2
IF ! EMPTY(cItem)
//cSqlIns := [INSERT asset SET inspcft=(SELECT * FROM OPENROWSET(BULK N'] + cCft + cTemp + [',SINGLE_BLOB) AS CertificateImage),inspcfttype='JPG' WHERE itemnumber='] + ALLTRIM(cItem) + [']

cSqlIns := [INSERT INTO asset_chart(]
cSqlIns += [itemnumber,]
cSqlIns += [inspcft]
cSqlIns += [)]

cSqlIns += [ VALUES (]
cSqlIns += ['] + _SqlClean( cItem ) + [',]
cSqlIns += MemoRead( cCft + aImages[nX1,1] )
cSqlIns += [)]
TRY
oSql:Open( cSqlIns, xCONNECT )
nAdded++
CATCH oError
IF MsgNoYes( "Unable to Perform Table Update, Process Aborted." + cEol + oError:Description + cEol + cSqlIns + cEol + xCONNECT, Ptitle )
MemoWrit( 'sql-err.txt', oError:Description )
EXIT
ENDIF
END
ENDIF
NEXT nX1
oSql:=nil

[cdmmaui]

Rao,

I changed the following code and it seems to be working.

cSqlIns += STRTOHEX( MemoRead( cCft + aImages[nX1,1] ) )

I will let you know once process is complete

[cdmmaui]

Hi Rao,

It did not work. Any ideas what needs to be done?