How to get image (LONGVARBINARY) from resultset

[hadjiamit]

I’ve been trying to get the Image(LONGVARBINARY) file from a resultset so I could use it to update another row from another table to no avail. Please help.

	Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
	Db = Context.getByName("samplesystem")
	Conn = Db.getConnection("","") 
	
	strSQL = "SELECT * FROM [User] WHERE [ID] ='"+sampleid+"';"
    Stmt = Conn.createStatement()
    UserDataQuery = Stmt.executeQuery(strSQL)
    UserDataQuery.next()
    
    Dim CurrentUserID as integer
    Dim CurrentUserProfilePicture as Object
    Dim CurrentUserName as string
    Dim CurrentUserRole as String
    
    CurrentUserID = LoggedInUserID
    'CurrentUserProfilePicture =
    CurrentUserName =  UserDataQuery.GetString(4)
    CurrentUserRole =  UserDataQuery.GetString(12)
    

    msgBox  UserDataQuery.GetBytes(13)+"" 'BASIC runtime error. Object variable not set. 
'I get an error here. 
    
	streSQL = "Update [CurrentUser] SET [ProfilePicture] = '"+CurrentUserProfilePicture+"', [Name] =  '"+CurrentUserName+"', [UserID]= '"+CurrentUserID+"',[UserRole]= '"+CurrentUserRole+"' WHERE [ID] = 0;"
    Stmt = Conn.createStatement()
    UserDataQuery = Stmt.executeQuery(streSQL)

===========================================
Version: 7.3.5.2 (x86) / LibreOffice Community
Build ID: 184fe81b8c8c30d8b5082578aee2fed2ea847c01
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (en_PH); UI: en-US
Calc: CL

[RobertG]

Seems you are looking for getBinaryStream. Here a little part of the code I used for this:

…
 oStream = UserDataQuery.GetBinaryStream(13)
 oPath = createUnoService("com.sun.star.util.PathSettings")
 stPath = oPath.Temp & "/myimage.jpg"
 oSimpleFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess")
 oSimpleFileAccess.writeFile(stPath, oStream)
…

[hadjiamit]

Hello! Thank you for your attention to this. The suggestion you provided seems to still not solve my problem. The sample you’ve shown seems to have the objective to save the image from the database to a directory. Mine, however, aims to “pass” the image from one table to another table and applying the getter getBinaryStream still fires the same error I had.

[RobertG]

Haven’t tested your code. Here only an example, which will work: getBinaryStream from a field of the form, updateBinaryStream to a field of a form. Language is German …
Beispiel_Dokumente_einlesen_auslesen_Hsqldb.odb (392.0 KB)
Will have a look if I get this also directly to copy from one table to another.

[Ratslinger]

@hadjiamit
I had no issue with:

oStream = UserDataQuery.GetBinaryStream(MY FIELD NUMBER)

.
You have not provided much information as to what you are using. This code appears to be outside of Base but that is not clear where you are actually running it from. Also has signs of an external database but you have no mention of the database you are using, and if external the connection properties. Also what specific LO version are you using and on what OS? What type of images are you copying - .bmp .jpg etc.
.
This type of information can make the difference in answering a question.
.
I had no problem in copying (within Base HSQLDB embedded) both a bmp and jpg image from one table to another. I also find copying data from one table to another questionable.
.
I suspect you may have code you found on another post and may be better done if there were more information. Also suspect is that the image is actually field 13 in the result set.

[hadjiamit]

Apologies for not being too clear on my question details. It’s my first time using LO. I am using the Base database and for the LO version and OS details, I have entailed them in the question above right below my question. I thing the GetBinaryStream does work as a getter. The only think I am missing now is how to get the oStream in your oStream = UserDataQuery.GetBinaryStream(MY FIELD NUMBER) as a query parameter in my streSQL = "Update [CurrentUser] SET [ProfilePicture] = '"+CurrentUserProfilePicture+"' WHERE [ID] = 0;". Do you have any idea how? Thank you for your help.

[Ratslinger]

@hadjiamit
Yes, my oversight as to LO version & OS. However you did miss some other points.
Base is not a database. LO comes with the ability to use HSQLDB or Firebird ( with experimental turned on) embedded databases. I can guess you are using HSQLDB (note information at bottom line of main Base screen). Also no answer on where the code came from or where it is executed from.
.
Also storing images in an embedded database is not a good idea especially with HSQLDB. Crashes and loss of data have occurred. An external Firebird file may be of help here but in reality do not know what the purpose of all this is.
.
That said, I would use two connection objects - Conn to read & Conn1 to write. Here is an Update statement that works (albeit copying images can be slow):

streSQL = "Update [CurrentUser] SET [ProfilePicture] = '" & UserDataQuery.getString(13) & "', [Name] =  '" & CurrentUserName & "', [UserID]= '" & CurrentUserID & "',[UserRole]= '" & CurrentUserRole & "' WHERE [ID] = 0;"

.
This is of course provided you field is #13. Also, do not use the + but rather & as it may in some cases be confused with a mathematical function.
.
Edit:
Correction to SQL as the square brackets will cause an error:

streSQL = "Update ""CurrentUser"" SET ""ProfilePicture"" = '" & UserDataQuery.getString(13) & "', ""Name"" =  '" & CurrentUserName & "', ""UserID"" = '" & CurrentUserID & "',""UserRole""= '" & CurrentUserRole & "' WHERE ""ID"" = 0;"

[hadjiamit]

Yep. I now see what you meant by slow. My question is answered. Thank you for your quick solution. Also for unanswered questions:

• Yes, I see that I am actually using HSQLDB Embedded
• After some thinking, I now get why you said you don’t get the purpose of all this because I see now that it is an unnecessary feature for my form.

Thank you also for your tips on reading and writing SQL Queries. Cheers!