save picture in sql data base

[darzanmihai]

is it possible to save a picture in a sql database?

If it is, could someone post an example script or guide me?

Thx

[PsaltyDS]

is it possible to save a picture in a sql database?

If it is, could someone post an example script or guide me?

Thx

Most DBs can save a binary BLOB type of some kind. You could save the binary jpeg, gif, tiff, etc. data that way.

P.S. Googled an example: MS SQL Server KB258038

Edited September 11, 2009 by PsaltyDS

[wolf9228]

is it possible to save a picture in a sql database?

If it is, could someone post an example script or guide me?

Thx

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <WindowsConstants.au3>
#include <StaticConstants.au3>
#include <GUIConstantsEx.au3>
#Include <WinAPI.au3>



Dim $SS_REALSIZEIMAGE = 0x00000800 ,$msg
GUICreate(" My GUI Bitmap", 260, 120)
ImageSaveToDataBase("Image.png","database.db",1)
Dim $Array = ImageLoadFromDataBase("database.db",1)

Dim $Pic = GUICtrlCreateLabel("", 10, 10, $Array[1][4], $Array[1][5],$WS_BORDER + $SS_CENTERIMAGE + $SS_REALSIZEIMAGE)
Dim $HandleImage = $Array[1][1] ;$ImageArray[0][1] = "HBITMAP"
STATIC_SETBITMAP($Pic,$HandleImage)
GUISetState()
    While 1
        $msg = GUIGetMsg()

        If $msg = $GUI_EVENT_CLOSE Then ExitLoop
    WEnd
    GUIDelete()
    Exit

;Fully qualified image file name
;$DB_Filename Database filename
;$key key ImageSave
;$IMAGETABLE TABLE ImageSave
;Return False Or True
Func ImageSaveToDataBase($Image_Filename,$DB_Filename,$key = 1 ,$IMAGETABLE = "IMAGETABLE")
Local $hFile = _WinAPI_CreateFile($Image_Filename, 2,2) , $nBytes
if Not $hFile Then Return -1
Local $tSize = FileGetSize($Image_Filename) + 1
Local $DATA = DllStructCreate("byte[" & $tSize & "]")
_WinAPI_ReadFile($hFile, DllStructGetPtr($DATA), $tSize, $nBytes)
if @error Then
_WinAPI_CloseHandle($hFile)
return -2
EndIf
_WinAPI_CloseHandle($hFile)
Local $Binary = String(DllStructGetData($DATA,1))

_SQLite_Startup ()
Local $handle = _SQLite_Open ($DB_Filename)
if @error Then Return -3
_SQLite_Exec($handle,"CREATE TABLE " &  $IMAGETABLE & " (key,value);")
if @error Then Return -4
_SQLite_Exec($handle,"INSERT INTO " & $IMAGETABLE & " (key,value) VALUES ('" & $key & "','" & $Binary & "');")
if @error Then Return -5
_SQLite_Close ($handle)
Return True ;Return False Or True
EndFunc


;$DB_Filename Database filename
;$key key ImageSave
;$IMAGETABLE TABLE ImageSave
;Return $ImageArray
Func ImageLoadFromDataBase($DB_Filename,$key = 1 ,$IMAGETABLE = "IMAGETABLE")
_SQLite_Startup ()
Local $handle = _SQLite_Open ($DB_Filename)
if @error Then Return -1
Local $aRow
_SQLite_QuerySingleRow(-1,"SELECT value FROM " & $IMAGETABLE & " WHERE key = '" & $key & "' LIMIT 1;",$aRow)
if @error Then Return -2
if $aRow[0] = "" Then Return -2
$BinaryData= $aRow[0]

Local $tSize = StringLen($BinaryData) + 1
Local $DATA = DllStructCreate("byte[" & $tSize & "]")
DllStructSetData($DATA,1,$BinaryData)
Local $uFlags = 0x0002 , $dwBytes = DllStructGetSize($DATA)
Local $hMemory = DllCall("Kernel32.dll","hwnd","GlobalAlloc","long",$uFlags,"long",$dwBytes)
if @error Then
Return -1
Else
$hMemory = $hMemory[0]
if $hMemory = 0 Then Return -3
EndIf
Local $hLock = DllCall("Kernel32.dll","ptr","GlobalLock","hwnd",$hMemory)
if @error Then
Return -2
Else
$hLock = $hLock[0]
if $hLock = 0 Then Return -4
EndIf
Local $Destination = $hLock , $Source = DllStructGetPtr($DATA), $Length = $dwBytes
DllCall("Kernel32.dll","none","RtlMoveMemory","ptr",$Destination,"ptr",$Source,"long",$Length)
if @error Then
Return -3
EndIf
Local $fDeleteOnRelease = True , $Status , $ppstm , $Error
Local $Error = DllCall("Ole32.dll","int","CreateStreamOnHGlobal","ptr",$hLock ,"int",$fDeleteOnRelease,"ptr*",0)
if @error Then
Return -4
Else
if Not ($Error[0] = 0) Then Return -5
$ppstm = $Error[3]
EndIf
Local $gdiplusOpen = DllOpen("gdiplus.dll")
Local $ImageType = 0 , $ImageWidth = 0 ,$ImageHeight = 0 , $struct_GdiplusStartupInput , $Input , $LPInput , $VOID _
, $LPVOID_token , $Status , $HBITMAP , $HENHMETAFILE , $GdiPlus_Dll_Hwnd , $GdiPlus_Token
$struct_GdiplusStartupInput = _
"int GdiplusVersion;" & _
"int DebugEventCallback;" & _
"int SuppressBackgroundThread;" & _
"int SuppressExternalCodecs;"
$Input = DllStructCreate($struct_GdiplusStartupInput)
DllStructSetData($Input,"GdiplusVersion",1)
DllStructSetData($Input,"DebugEventCallback",0)
DllStructSetData($Input,"SuppressBackgroundThread",0)
DllStructSetData($Input,"SuppressExternalCodecs",0)
$LPInput = DllStructGetPtr($Input)
$VOID = DllStructCreate("int")
$LPVOID_token = DllStructGetPtr($VOID)
$Status = DllCall($gdiplusOpen,"int","GdiplusStartup","ptr",$LPVOID_token,"ptr",$LPInput,"ptr",0)
if @error Then
Return -5
Else
if Not ($Status[0] = 0) Then Return -6
$LPVOID_token = $Status[1]
EndIf
Local $stream = $ppstm , $image = 0
$Status = DllCall($gdiplusOpen,"int","GdipLoadImageFromStream","ptr",$stream,"int*",$image)
if @error Then
Return -6
Else
if Not ($Status[0] = 0) Then Return -7
$image = $Status[2]
EndIf
$Status = DllCall($gdiplusOpen,"int","GdipGetImageWidth","hwnd",$image,"int*",0)
if Not @error Then
if $Status[0] = 0 Then $ImageWidth = $Status[2]
EndIf
$Status = DllCall($gdiplusOpen,"int","GdipGetImageHeight","hwnd",$image,"int*",0)
if Not @error Then
if $Status[0] = 0 Then $ImageHeight = $Status[2]
EndIf
$Status = DllCall($gdiplusOpen,"int","GdipGetImageType","hwnd",$image,"int*",0)
if Not @error Then
Local $Case = $Status[2]
Select
Case $Case = 0
$ImageType = "ImageTypeUnknown"
Case $Case = 1
$ImageType = "ImageTypeBitmap"
$Status = DllCall($gdiplusOpen, "int", "GdipCreateHBITMAPFromBitmap", "hwnd", $image, "int*", 0, "int", 0)
if Not @error Then
if ($Status[0] = 0) Then $HBITMAP = $Status[2]
EndIf
Case $Case = 2
$ImageType = "ImageTypeMetafile"
Local $metafile = $image
$Status = DllCall($gdiplusOpen,"int","GdipGetHemfFromMetafile","hwnd",$metafile,"int*",0)
if Not @error Then
if ($Status[0] = 0) Then $HENHMETAFILE = $Status[2]
EndIf
EndSelect
EndIf

DllCall($gdiplusOpen,"int","GdiplusShutdown","ptr",$LPVOID_token)
DllClose($gdiplusOpen)
_SQLite_Close ($handle)

Dim $ImageArray[2][8]
$ImageArray[0][1] = "HBITMAP"
$ImageArray[0][2] = "HENHMETAFILE"
$ImageArray[0][3] = "ImageType"
$ImageArray[0][4] = "ImageWidth"
$ImageArray[0][5] = "ImageHeight"
$ImageArray[1][1] = $HBITMAP
$ImageArray[1][2] = $HENHMETAFILE
$ImageArray[1][3] = $ImageType
$ImageArray[1][4] = $ImageWidth
$ImageArray[1][5] = $ImageHeight

return $ImageArray
EndFunc


Func STATIC_SETBITMAP($hWnd,$HandleImage)
Local $GWL_STYLE = (-16) , $SS_BITMAP = 0x0000000E , $SS_ICON   = 0x00000003 ,$STM_SETIMAGE = 0x0172
If Not IsHWnd($hWnd) Then $hWnd = GUICtrlGetHandle($hWnd)
if Not BitAND(_WinAPI_GetWindowLong($hWnd, $GWL_STYLE), $SS_BITMAP) = $SS_BITMAP Then
_WinAPI_SetWindowLong($hWnd, $GWL_STYLE, _WinAPI_GetWindowLong($hWnd, $GWL_STYLE) + $SS_BITMAP )
ElseIf  BitAND(_WinAPI_GetWindowLong($hWnd, $GWL_STYLE), $SS_ICON) = $SS_ICON Then
_WinAPI_SetWindowLong($hWnd, $GWL_STYLE, _WinAPI_GetWindowLong($hWnd, $GWL_STYLE) - $SS_ICON)
EndIf
$ImageH =  _SendMessage($hWnd, $STM_SETIMAGE, 0, $HandleImage)
Return _WinAPI_DeleteObject($ImageH)
EndFunc

Edited September 12, 2009 by wolf9228

[darzanmihai]

Most DBs can save a binary BLOB type of some kind. You could save the binary jpeg, gif, tiff, etc. data that way.

P.S. Googled an example: MS SQL Server KB258038

Thank you wolf9228

And how could I save it back from the data base to a file?

[Juvigy]

Another thing you could do is store all the pics in a directory on the server and in the database store only the path to the file.

[jvanegmond]

Another thing you could do is store all the pics in a directory on the server and in the database store only the path to the file.

That's right. And in fact, that's what you should do.

@OP, Please avoid storing pictures in the SQL data base. Store the pictures on the server, accessible via HTTP/FTP and keep a URL to the file in the SQL database.

[PsaltyDS]

Thank you wolf9228

And how could I save it back from the data base to a file?

The example shown in the MS KB article I linked is actually for storing the blob as a file using ADODB.

As Manadar pointed out, I make no claim that this is an efficient way to do things, just that it is certainly possible.

[acidman]

is there any simpler way for doing this lets say you have a picture control... is there a way to use something other than windows api to set the picture?

[Valik]

Don't fuck corpses. At least not in public.

[acidman]

huh?

[jchd]

Valik means "create your own fresh thread instead of bringing very old ones to artificial life".

[acidman]

Ah ok thanks man...

[Juvigy]

Don't fuck corpses. At least not in public.

Valik means "create your own fresh thread instead of bringing very old ones to artificial life".

We need FUN FORUM LINES subforum section or something. This is priceless ...

[Valik]

We need FUN FORUM LINES subforum section or something. This is priceless ...

It already