Search the Community

Topic Closed, please go here SQLite demonstration of native recognition of BLOB object in Listview Following an idea that was raised in a discussion with water and jchd, I have been working during the past week on a ListView SQLite demo that integrates BLOB (Binary large Object) fields. Download link at the bottom of this post This is what I have until now, version sero sero sero … one – to use the words of a well know Spanish biker . It is a fully workable example and I would be happy if you could test it and give me your comments. Pre-requisites: 1. You have to download the SQLite Database here: http://users.telenet.be/GreenCan/AutoIt/GreenCan_SQLite_Database_demo.zip 2. You have to download trancexx’ great GIFAnimation udf here: GIFAnimation or http://code.google.com/p/gif-animation/downloads/list For your comfort, I included the latest version in the above zip file. I decided to use GIFAnimation.au3 for the Image display because it works with several kinds of graphic types (except multipage tiff) including animated GIFs J . GIFAnimation? What’s in a name… 3. SQLite3.dll (also included in the zip package) In this first version, you will see two different icons in the Listview (as opposed to the screenshot where you see all images as icons) 1. Eye Icon: this is an image object, clicking once will open a preview box 2. Database with lightning arrow: any kind of other (non-image) object A BLOB field doesn’t need to be an image, it can store literally everything you can imagine, the database contains several objects that you can try-out. I didn’t include an exe file, but why not? It is possible. Up to you to find out if it is useful. These non-graph objects are not preview-able and will be executed. By the way, all these BLOB examples are inoffensive, for example the autoit code just displays an ‘Hello World’ MsgBox. If you don’t trust it, just comment out the ShellExecute in line 219 and verify the content in the Temp folder… or pass your favorite virus scanner over these files You will notice that the first execute take a bit more time to start, afterwards, everything is blinking fast. Not sure why this slow behavior, maybe it’s only on my PC. Although the example looks good, I am not completely satisfied with the result. The BLOB object is not natively recognized and I think it should, but I don’t know how to do this. Any suggestion is welcome here… I can use a isBinary() to identify the field as a BLOB, but I still don’t know how to recognize the BLOB as an image, or an Excel sheet, or anything else. So this is the reason why I have the TypeOfObject column, that links the object to the physical file. Again, any suggestion is welcome here… Another issue is that I have to export the file to a physical file to be able to display (or use) it. I tried UEZ’s _GDIPlus_BMPFromMemory() function for the bitmaps but it is too slow (not even sure it can handle anything else than BMP. And slowness will increase with very large binary objects. I am wondering what the performance would be with a database containing several hundreds of objects to export. I will come with version 0.0.0.2 within a few days. I just let you test the current version first. Version 2 has, as you can see in the screenshot above, a visible icon representation of the image object. This is how it should be but there are significant disadvantages in the way that I programmed it. But I will explain that when I publish the new example script. SQLite GreenCan_demo BLOB in Listview 0.0.0.1.au3 (Please jump immediately to version 0.0.0.2 if you do not intend to test both examples) Edit: 06 May 2013 New version! Version 0.0.0.2 This enhanced version displays a visible thumbnail of each Database image object in the ListView. The example works with the database file that you have to download or already downloaded with the first version (see Prerequisite 1 higher in this post) SQLite GreenCan_demo BLOB in Listview 0.0.0.2.au3 Edit: 10 May 2013 With this script, you can import your own objects into the GreenCan_demo.db Database (you have to download the database again from the link in Pre-requisite 1, because I modified the structure, the key column is now autoincremental) The script will work for both demo's Import Object in SQLite Database.zip Anyhow, enjoy and please give me feedback. GreenCan

As the title says, I'm trying to show a thumbnail in a gui, selected by the user, which must be resized to fit the Gui Control and store the image itself in SQLite as a blob. Thanks to some useful examples found on the forum, I managed to load, show and store an image but I'm experiencing some problems in resizing the picture. This is what i made until now: #include <SQLite.au3> #include <SQLite.dll.au3> #include <GUIConstantsEx.au3> #include <GDIPlus.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Local $hGUI, $hBMP, $hBitmap, $hGraphic Local $hQuery, $item Local $filename Local $x = 100, $y = 100 _SQLite_Startup() _SQLite_Open() ; open Database in Memory If @error Then MsgBox(16, "SQLite Error", "Can't Load Database!") Exit -1 EndIf _SQLite_Exec(-1,"CREATE TABLE IF NOT EXISTS DBTest (IMAGE BLOB);") $hGUI = GUICreate("GUI", 400, 300) $GUIImage = GUICtrlCreatePic("", 10, 10, $x, $y, BitOR($GUI_SS_DEFAULT_PIC,$SS_CENTERIMAGE,$SS_SUNKEN,$WS_BORDER), BitOR($WS_EX_CLIENTEDGE,$WS_EX_STATICEDGE)) GUISetState() GUISetState(@SW_SHOW) Local $filename = FileOpenDialog("Select image",@ScriptDir,"Image (*.jpg;*.bmp)",3) ; I could show the image in the gui here: ; GUICtrlSetImage($GUIImage,$ImageFileName) ; But I want to resize it, store it in a DB, then show the resized image in the GUI ; This is what I am trying to do for resize (taken from an example made by UEZ): _GDIPlus_Startup() Local $hImageFromFile = _GDIPlus_ImageLoadFromFile($filename) Local $Thumbnail = DllCall($ghGDIPDll, "uint", "GdipGetImageThumbnail", "handle", $hImageFromFile, "uint", $x, "uint", $y, "int*", 0, "ptr", 0, "ptr", 0) $Thumbnail = $Thumbnail[4] $object_bitmap = _GDIPlus_BitmapCreateFromHBITMAP($Thumbnail); Create a Bitmap object from a bitmap handle (?) _GDIPlus_Shutdown() ;********** How could I show the resized pic in the GUI? Dunno :( ***** ;preparing resized image for storing in SQLite Local $binary_bitmap = Binary($object_bitmap) Local $encoded_bitmap = _SQLite_FastEncode($binary_bitmap) ;insert the blob If Not _SQLite_Exec(-1, "SELECT IMAGE FROM DBTest") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec(-1, "INSERT INTO DBTest (IMAGE) VALUES (" & $encoded_bitmap & ");") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) ;Retrieve from database If Not _SQLite_Query(-1, "SELECT * FROM DBTest;", $hQuery) = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_FetchData($hQuery, $item, False, False) = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) Local $retrieve_pic = $item[0] ;NOW *I THINK* I SHOULD HAVE THE RESIZED PICTURE IN $retrieve_pic BUT I CAN'T SHOW IT IN THE GUI..... ; Run the GUI until the dialog is closed While 1 $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop WEnd _SQLite_Close() _SQLite_Shutdown() Exit I'm not very good at GDI coding and quite a newbie with SQLite, too... Any help would be very appreciated!

Hi to all, I am writing a feature to an existing internal AutoIt program, based on a client's request. The requested feature would be to grab the file via a button, which reads the file's path. Below the file browsing button would be a button to upload the file into a MySQL server. Initially, my method would be to read the contents of the file provided in the filepath, then convert the contents of the file into binary data, and then store it into a column of a table as a BLOB. I am not sure whether if this is the best approach to storing text/Word/PDF files into the MySQL Server in this way, if the program used is an AutoIt program. So far, I am only able to generate Excel files, but only because there was already an existing BLOB column in an existing table. Sample code: $sTemplateName = "SearchTemplate" $sFilePath = @ScriptDir & "\temp\" & $sTemplateName & ".xls" $iFileExists = FileExists($sFilePath) ; If template file not found, create it. If (Not $iFileExists) Then generateExcelFile($sTemplateName, $sFilePath) EndIf $iFileExists = FileExists($sFilePath) If $iFileExists = 1 Then $oExcel = _ExcelBookOpen($sFilePath, 0) _ExcelWriteCell($oExcel, "Search conducted on " & _NowCalc() & " by " & $username & "", 2, 1) $counter = 0 While Not $recordSet.EOF _ExcelWriteCell($oExcel, 1 + $counter, 6 + $counter, 1) _ExcelWriteCell($oExcel, getName("User", $recordSet.Fields("UserID").value), 6 + $counter, 2) _ExcelWriteCell($oExcel, $recordSet.Fields("InternalName").value, 6 + $counter, 3) _ExcelWriteCell($oExcel, getName("Product", $recordSet.Fields("ProductID").value), 6 + $counter, 4) _ExcelWriteCell($oExcel, $recordSet.Fields("Serial").value, 6 + $counter, 5) _ExcelWriteCell($oExcel, getName("Location", $recordSet.Fields("LocationID").value), 6 + $counter, 6) _ExcelWriteCell($oExcel, $recordSet.Fields("Remarks").value, 6 + $counter, 7) _ExcelWriteCell($oExcel, getFriendlyDate($recordSet.Fields("LastModified").value, 1), 6 + $counter, 8) _ExcelWriteCell($oExcel, getFriendlyDate($recordSet.Fields("CreationDate").value, 1), 6 + $counter, 9) $recordSet.MoveNext $counter = $counter + 1 WEnd _ExcelBookSaveAs($oExcel, $exportDir & "\" & $username & "_Search_Results_" & @YEAR & @MON & @MDAY & "_" & @HOUR & @MIN & @SEC, "xls") _ExcelBookClose($oExcel, 1, 0) EndIf

New release. 19 June 2013 (1.0.0.4) There is a relation with this topic SQLite ListView and BLOB demo but I decided to start a completely new Topic because the approach is considerably different from the two previous examples which were only kick-offs to this demo. This example shows how binary objects can be recognized natively in a database BLOB field without having to link to other fields that may contain information of the data object. In the demo I used 2 approaches for native recognition 1. For multi-type binary objects, the file name is added in the header of the BLOB Multi-type object can be images or any other kind of file. Because of the object header data, there is no need to identify the object in the binary code 2. Objects without header data, this works only for images, an algorithm will identify the type of image. The demo shows what happens whit objects which are not identifiable, see example 5. Credits to: 1. trancexx: GIFAnimation.au3 '?do=embed' frameborder='0' data-embedContent>> 2. smashly: _ImageResize() Resizes and converts different graphicformats 3. rover: Customize Draw of Listview rows Optimizations of WS_NOTIFY I also thank rover for giving a second method to resolve the image space issue. I implemented the one proposed by KaFu, because very simple to implement 4. KaFu: Solved the Listview issue with image space in Columns one. 5. jchd: For some hints and background info on SQLite 6. Yashied: WinAPIEx.au3 '?do=embed' frameborder='0' data-embedContent>> new release. Version 1.0.0.4 What's new: - added fully generic Add, Edit, Add/Copy, Delete and Find buttons. With fully generic I mean, you don't have to bother about the table content, GUI field inputs will populate accoring to the table definition. - Added Field validation, also according to how the columns were defined in the table. (see GUI dynamic input validation for more information) tested a thousand times... on W7 and WXP 32/64 For a working example you have to download 2 files (see links in between the horizontal lines: SQLite GreenCan_demo BLOB in Listview 1.0.0.4.zip GreenCan_demo2.zip (if you already did, don't mind downloading it again) Note: For the Edit GUI, you will notice that sometimes one field is not editable, marked as (*PK) in the description. The field is a 'Primary Key autoincrement'. When appending the row, the PK will automatically increment, therefore it is not allowed to edit the field. Other fields mared (*) cannot be empty, you can only save the row if these fields contain data. In the case (*PK) is editable, you have to put unique data for the Primary key. If you do a copy/add without changing the field content, you will get a not unique Error. Special case: I don't allow empty primary key (NULL), while SQLite does, but it's pretty useless anyhow because you can only have 1 NULL in a Primary key. I have also included a very small non BLOB database, for example 0, to show that the generic edit/add works also here. SQLite GreenCan_demo BLOB in Listview 1.0.0.4.zip and don't forget to download this zip file to complete the required files for the demo http://users.telenet.be/GreenCan/AutoIt/GreenCan_demo2.zip I let you explore the demo and please give me feedback. GreenCan