SQLite Multiple Blob Topic is solved

[Spark]

recently I learned about SQLite using the JustMe Class and read tutorial from Joe Glines, it's better than using "IniFile", slow but surel I can write and read in the database, but I am stuck with "blob" type, can anyone help me give an example to write (input) and read (view display) multiple "blob"?

sorry for my bad english

thanks in advance

[just me]

Basic usage:

• MyDB.StoreBLOB(SQL, BlobArray)
  should be called whenever BLOBs shall be stored in the database. For each BLOB in the row you have to specify a ? parameter within the statement. The parameters are numbered automatically from left to right starting with 1. For each parameter you have to pass an object within BlobArray containing the address and the size of the BLOB.

Spark: How would you store blobs in an INI file?

Klarion: Don't guess, just read.

[Klarion]

good..
I'll wait and read
Go ahead..

[Spark]

Dear Just me,

thanks for your reply,

I usually use "ini file" to save the image path and then call it with "iniread", but when I have many paths to load, it becomes very slow to load, so I want to try using SQLite as my database, I can read and write data on the SQLite base from your example, but I can't load / read a lot of blobs to display to my GUI (I can open the first image, but not the second and third)

and this is part of my code:

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
;~ #Warn  ; Recommended for catching common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance Force
#Include %A_ScriptDir%\Class_SQLiteDB.ahk

objDb := New SQLiteDb
strDbFile := A_ScriptDir . "\BLOBB.db"
blnDbIsNew := !FileExist(strDbFile)
;~ MsgBox, Ready to OpenDb() to create or open the database:`n`n %strDbFile%	;================================================================================
if !objDb.OpenDb(strDbFile) { ; create an empty database or open an existing one
	MsgBox, % "SQLite Error OpenDb`n`nMessage: " . objDb.ErrorMsg . "`nCode: " . objDb.ErrorCode . "`nFile: " . strDbFile
	return
}


strSQLHListSelect := "SELECT rowid, Title, Picture FROM Test ORDER BY rowid;"
	objDb.GetTable(strSQLHListSelect, objTable)
	If (objTable.HasNames) {
		If (objTable.HasRows) {
			If !objDb.Query(strSQLHListSelect, objRecordSet)
			MsgBox, % "SQLite QUERY Error`n`nMessage: " . objDb.ErrorMsg . "`nCode: " . objDb.ErrorCode . "`nFile: " . strDbFile . "`nQuery: " . strSQL
			Loop
			{
				intResult := objRecordSet.Next(objRow) 
				if (!intResult) {
					MsgBox, % "SQLite NEXT Error`n`nMessage: " . objDb.ErrorMsg . "`nCode: " . objDb.ErrorCode
					return
				}
				if (intResult = -1) ; end of list
					break
				jml := A_Index
				Loop, % objRecordSet.ColumnCount
				{
					strReport1 := objRow[1]	; RowID
					strReport2 := objRow[2]	 ;Title
					strReport3 := objRow[3]	;Picture BLOB
                    
				}
				L_Title%A_Index% := strReport2
			}
			objRecordSet.Free()
		}
	}


HFILE := FileOpen("Test.jpg", "w")
If !objDb.Query("SELECT * FROM Test;", RecordSet)
   MsgBox, 16, SQLite Error: Query, % "Msg:`t" . RecordSet.ErrorMsg . "`nCode:`t" . RecordSet.ErrorCode
If (RecordSet.HasRows) {
   If (RecordSet.Next(Row) < 1) {
      MsgBox, 16, %A_ThisFunc%, % "Msg:`t" . RecordSet.ErrorMsg . "`nCode:`t" . RecordSet.ErrorCode
      Return
   }
   Loop, % RecordSet.ColumnCount {
      If IsObject(Row[A_Index]) {
         Size := Row[A_Index].Size
         Addr := Row[A_Index].GetAddress("Blob")
         If !(Addr) || !(Size) {
            MsgBox, 0, Error, BlobAddr = %Addr% - BlobSize = %Size%
         } Else {
            VarSetCapacity(MyBLOBVar, Size) ; added
            DllCall("Kernel32.dll\RtlMoveMemory", "Ptr", &MyBLOBVar, "Ptr", Addr, "Ptr", Size) ; added
            HFILE.RawWrite(&MyBLOBVar, Size) ; changed
         }
      }
   }
}
RecordSet.Free()
HFILE.Close()
RecordSet.Free()




Gui Add, Text, x40 y20 w30 h21 +0x200, % L_Title1
Gui Add, Text, x+80 yp w30 h21 +0x200, % L_Title2
Gui Add, Text, x+80 yp w30 h21 +0x200, % L_Title3
Gui Add, Picture, x10 y50 w100 h100, Test.jpg
Gui Add, Picture, x+10 yp w100 h100, % L_Picture2
Gui Add, Picture, x+10 yp w100 h100, % L_Picture3
Gui Show,, Window
Return

GuiEscape:
GuiClose:
    ExitApp

thanks in advance

[just me]

Hi,

you only provided some code trying to read BLOBs, but no information about the structure of your database. Seemingly, there are two fields of interest in each row of your database:

• Title
  I think it's a text field containing the (file) name of the picture.
• Picture
  I think that's the BLOB field containing the raw picure data.

So my first question is: How do you store the images in the database?

Back to your code:

First you call objDb.GetTable() just to check whether the table HasNames and also HasRows. It makes no sense since GetTable() doesn't retrieve BLOBs. Remove it.

- Just call objDB.Query() to select the fields rowid, Title, and Picture from the table Test. That returns an AHK objRecordSet object for all rows in table Test.
- Read each record of objRecordSet by calling objRecordSet.Next().
- Every returned objRow contains a BLOB as an AHK object in the third column (field Picture). objRow[3].Size contains the size of the BLOB in bytes, objRow[3].Blob contains the raw binary date. To access the data, you need to get the memory address by calling Addr := objRow[3].GetAddress("Blob").
- Now you have all you need to process the BLOB data as you want.

[Spark]

Dear Just Me,

Hi,

you only provided some code trying to read BLOBs, but no information about the structure of your database

sorry about that, I'm not good at explaining

• Title
  I think it's a text field containing the (file) name of the picture.
• Picture
  I think that's the BLOB field containing the raw picure data.

yes, correct

Back to your code:

First you call objDb.GetTable() just to check whether the table HasNames and also HasRows. It makes no sense since GetTable() doesn't retrieve BLOBs. Remove it.

- Just call objDB.Query() to select the fields rowid, Title, and Picture from the table Test. That returns an AHK objRecordSet object for all rows in table Test.
- Read each record of objRecordSet by calling objRecordSet.Next().
- Every returned objRow contains a BLOB as an AHK object in the third column (field Picture). objRow[3].Size contains the size of the BLOB in bytes, objRow[3].Blob contains the raw binary date. To access the data, you need to get the memory address by calling Addr := objRow[3].GetAddress("Blob").
- Now you have all you need to process the BLOB data as you want.

Thanks for your guide, now i can move forward (slow but sure)

So my first question is: How do you store the images in the database?

This is my code (including saving the image into database, please correct it if I'm wrong):

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
;~ #Warn  ; Recommended for catching common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance Force
#Include %A_ScriptDir%\Class_SQLiteDB.ahk

objDb := New SQLiteDb
strDbFile := A_ScriptDir . "\BLOBB.db"
blnDbIsNew := !FileExist(strDbFile)
;~ MsgBox, Ready to OpenDb() to create or open the database:`n`n %strDbFile%	;================================================================================
if !objDb.OpenDb(strDbFile) { ; create an empty database or open an existing one
	MsgBox, % "SQLite Error OpenDb`n`nMessage: " . objDb.ErrorMsg . "`nCode: " . objDb.ErrorCode . "`nFile: " . strDbFile
	return
}

strSQLHListSelect := "SELECT rowid, Title, Picture FROM Test;"
If !objDb.Query(strSQLHListSelect, objRecordSet)
MsgBox, % "SQLite QUERY Error`n`nMessage: " . objDb.ErrorMsg . "`nCode: " . objDb.ErrorCode . "`nFile: " . strDbFile . "`nQuery: " . strSQL
Loop
{
	intResult := objRecordSet.Next(objRow) 
	if (!intResult) {
		MsgBox, % "SQLite NEXT Error`n`nMessage: " . objDb.ErrorMsg . "`nCode: " . objDb.ErrorCode
		return
	}
	if (intResult = -1) ; end of list
		break
	Loop, % objRecordSet.ColumnCount
	{
		strReport1 := objRow[1]	; RowID
		strReport2 := objRow[2]	 ;Title
		Size := objRow[3].Size ; Blob Size
		Addr := objRow[3].GetAddress("Blob")
	}
	HFILE%A_Index% := FileOpen("Test" . A_Index ".jpg", "w")
	VarSetCapacity(MyBLOBVar, Size) ; added
	DllCall("Kernel32.dll\RtlMoveMemory", "Ptr", &MyBLOBVar, "Ptr", Addr, "Ptr", Size) ; added
	HFILE%A_Index%.RawWrite(&MyBLOBVar, Size) ; changed
	HFILE%A_Index%.Close()
	L_Title%A_Index% := strReport2
	L_Pic%A_Index% := "Test" . A_Index . ".jpg"
}
objRecordSet.Free()

Gui Add, Text, x40 y20 w30 h21 +0x200, % L_Title1
Gui Add, Text, x+80 yp w30 h21 +0x200, % L_Title2
Gui Add, Text, x+80 yp w30 h21 +0x200, % L_Title3
Gui Add, Picture, x10 y50 w100 h100, % L_Pic1
Gui Add, Picture, x+10 yp w100 h100, % L_Pic2
Gui Add, Picture, x+10 yp w100 h100, % L_Pic3
Gui Add, Button, x+10 yp w100 h100 gAddNew,  Add New
Gui Show,, Window
Return

AddNew:
	Gui,2:Add,Text, x1 y5 w80 h20, Name
	Gui,2:Add,Edit,  x40 y5 w500 h20 vE_Title
	Gui,2:Add,Text,  x1  y30 w80  h20, Link
	Gui,2:Add,Edit,   x40 y30 w450 h20 vE_PicPath
	Gui,2:Add, Button, x480 y55 w60 h25, OK
	Gui,2:Add, Button, x500 y30 w40 h25 gAddPath, ...
	Gui,2:Show,  w545 h80, ADD
	Gui,1: +Disabled ; disable main window
return

AddPath:
	FileSelectFile, SelectedFile, 3,, Find a File, Picture (*.Jpg; *.Png; *.Gif; *.Ico)
	if SelectedFile =
		return
	else
		GuiControl,2:, E_PicPath, %SelectedFile%
return

2ButtonOK:
	Gui,2:submit
	Gui,2:Destroy
	Gui,1: -Disabled ; enable main window
	NewPic := FileOpen(E_PicPath, "r")
	Size := NewPic.RawRead(BLOB, NewPic.Length)
	NewPic.Close()
	SQL := "INSERT INTO Test VALUES('"E_Title "', ?);"
	objDb.Exec("BEGIN TRANSACTION;")
	BlobArray := []
	BlobArray.Insert({Addr: &BLOB, Size: Size}) ; will be inserted as element 1
	If !objDb.StoreBLOB(SQL, BlobArray)
	   MsgBox, 16, SQLite Error, % "Msg:`t" . objDb.ErrorMsg . "`nCode:`t" . objDb.ErrorCode
	objDb.Exec("COMMIT TRANSACTION;")
return

2GuiClose:
2GuiEscape:
	Gui, 1: -Disabled ; enable main window
	Gui, 2:Destroy
return

GuiEscape:
GuiClose:
    ExitApp

One more question if you don't mind, is the "update method" same as "insert method"?

Thanks in Advance

[just me]

Hello Spark,

our code looks ok, just some remarks:

Code: Select all

	Loop, % objRecordSet.ColumnCount
	{
		strReport1 := objRow[1]	; RowID
		strReport2 := objRow[2]	 ;Title
		Size := objRow[3].Size ; Blob Size
		Addr := objRow[3].GetAddress("Blob")
	}

repeats the same actions on the same 3 columns ColumnCount times. You can safely remove the Loop.

Code: Select all

	HFILE%A_Index% := FileOpen("Test" . A_Index ".jpg", "w")
	VarSetCapacity(MyBLOBVar, Size) ; added
	DllCall("Kernel32.dll\RtlMoveMemory", "Ptr", &MyBLOBVar, "Ptr", Addr, "Ptr", Size) ; added
	HFILE%A_Index%.RawWrite(&MyBLOBVar, Size) ; changed
	HFILE%A_Index%.Close()

you use the handle HFILE%A_Index% within only one iteration of the loop and close it immediately after writing the file. So you can safely use HFILE without %A_Index% in this case.

Finally, INSERT inserts new rows into a table, UPDATE updates the contents of existing rows.

Regards,
just me

[Spark]

Hello Spark,

our code looks ok, just some remarks:

Code: Select all

	Loop, % objRecordSet.ColumnCount
	{
		strReport1 := objRow[1]	; RowID
		strReport2 := objRow[2]	 ;Title
		Size := objRow[3].Size ; Blob Size
		Addr := objRow[3].GetAddress("Blob")
	}

repeats the same actions on the same 3 columns ColumnCount times. You can safely remove the Loop.

Code: Select all

	HFILE%A_Index% := FileOpen("Test" . A_Index ".jpg", "w")
	VarSetCapacity(MyBLOBVar, Size) ; added
	DllCall("Kernel32.dll\RtlMoveMemory", "Ptr", &MyBLOBVar, "Ptr", Addr, "Ptr", Size) ; added
	HFILE%A_Index%.RawWrite(&MyBLOBVar, Size) ; changed
	HFILE%A_Index%.Close()

you use the handle HFILE%A_Index% within only one iteration of the loop and close it immediately after writing the file. So you can safely use HFILE without %A_Index% in this case.

Finally, INSERT inserts new rows into a table, UPDATE updates the contents of existing rows.

Regards,
just me

Dear Just me,

works like a charm,
I couldn’t have done it without you
Thank you for your guidance

Have a great day