Mysql ODBC and XROW

[khoward]

Hello,

I have a MariaDB (MySQL compatible) 10 database with a table that has a BLOB column that holds an image.
I am using the Mysql 8 ODBC connector.
Connecting through a macro (created an UNO service) works fine and I get an XResultSet, which is stored in a variable named oResultSet.
The problem occurs when I attempt to get the blob data with oResultSet.getBlob:

An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: The driver does not support the function 'XRow::getBlob'..On Line 156  Aborting....

It seems that the Mysql ODBC connector does support blobs, based on this:
https://dev.mysql.com/doc/connectors/en/connector-odbc-reference-datatypes.html

But maybe the mapping is wrong?

What I want to accomplish is get the binary data from the database and set the image data to an image control.
Thanks for the input!

EDIT

I’ve changed my approach.
I edited the table to be a varchar instead of a blob and am using oResultSet.getString instead.
So, now I have path to an image, C:\images\myimage.jpg, for example.
How do I load the image data from that file into the Image Control?

Thanks
Keith

[Ratslinger]

Hello,

It is confusing as to the reason for the need of a database connection. Only based upon the tags, the image is to be in a Writer document. You can insert an image directly there from the menu. With an image control you simply select the image in the property field Graphics. Then there is also the ability to invert using a registered database.

Can you clear up the process so as to give a proper direction?

[khoward]

Ratslinger,

I think explaining why I am using a database only confuses the matter.
However, I have a permit system and writer forms that have database fields that merge data from the database upon opening the document. The image control is for a signature of the individual doing the plan review…see, I doubt you know or care what I’m talking about. Bottom line, I wanted to pull (at first) the image data from the database, but now see that it can be accomplished from a different angle by only storing the path to the file. See the answer.

[khoward]

Got it! Referencing code from [Solved] How to insert an image into footer of Calc? (View topic) • Apache OpenOffice Community Forum The property ImageURL doesn’t just take a path like C:\images\myImage.jpg You have to format your path as a URL or, easier still, call convertToURL() on your path and assign it to the ImageURL property…Done! For reference, the URL is formatted like: file:///C:/images/myImage.jpg Also, if there are any spaces, they are converted to the HTML entity, ‘%20’

Hope this helps someone! Keith

[Ratslinger]

Hello,

You stated:

…see, I doubt you know or care what I’m talking about.

That is not true at all. It presents a better idea on what processes to present; there were multiple. The link you provided in your answer is not valid. So (link now repaired) I will post what I compiled while waiting for information.

Macro to load image control from file location:

Option Explicit
Sub loadImageControl
    Dim oForm As Object
    Dim oControl As Object
    Dim sFolder As String
    Dim sImageURL As String
	oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_INTERNAL_FORM")
	oControl = oForm.getByName("YOUR_CONTROL")
    sFolder = "IMAGE_FOLDER_AND_NAME"
    sImageURL = convertToURL(sFolder)
	oControl.ImageURL = sImageURL
End Sub

You can also insert an image without an image control. See this post →
Re: Inserting Image, Positioning and Resizing via Macro in C

Then you also insert information without using any macro by registering the database and accessing the record through Data Sources (Ctrl+Shift+F4).

Not so certain macros are needed and, yes, the more information provided the better the possible fit to what is needed.

[khoward]

Hi Ratslinger,
Thanks for the reply and still coming back to clarify…sorry, didn’t mean to imply you don’t care just that it is complicated and I am having a hard time articulating my situation.
I like the idea of using less code and I understand what you mean associating the doc with a table, but I don’t know how I would get the query to work…and I didn’t know if you wanted to get much deeper into my issue.
Don’t know what happened to the link I posted…it got mangled somehow…I corrected it.
Also, thanks for the clear example, I think it will help others that visit.
If you are interested in assisting further, can I private message you or email you and discuss further?
Thanks!
Keith

[Ratslinger]

@khoward,

This site has no mechanism for private messaging. With that am not willing to post an email address for contacting. Have already had an issue with information available through TDF.

Best if you have further questions or concerns to simply ask as new question(s). Many good people here willing to help.

[khoward]

@Ratslinger
" Many good people here willing to help."
Yes, indeed…including yourself.
Sincere thanks.
Keith

[ms777]

Hi,

coming back to the original question: I have a MariaDB on a Synology. I can create tables with blobs and read the blobs. Below is some code example.
Maybe the key is to use the com.mysql.jdbc.Driver …

Good luck,

ms777

Sub Main
oDriverManager = CreateUnoService("com.sun.star.sdbc.DriverManager") 
Dim info(2) as new com.sun.star.beans.PropertyValue
info(0).Name = "User"
info(0).Value = ""
info(1).Name = "Password"
info(1).Value = ""
info(2).Name = "JavaDriverClass"
info(2).Value = "com.mysql.jdbc.Driver"

sUrl = "sdbc:mysql:jdbc:192.168.178.999:9999/test" 
oDriver = oDriverManager.getDriverByUrl(sUrl)

oConnection = oDriver.connect(sUrl, info)

oStatement = oConnection.createStatement()
oStatement.setPropertyValue("ResultSetConcurrency", com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE)

oStatement.execute("drop table if exists blobtable;")
oStatement.execute("CREATE TABLE blobtable (ID TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, blobcol BLOB, PRIMARY KEY (ID));")


oStatement.execute("INSERT INTO blobtable VALUES(1, 123);") 'insert a blob. Please note that the 123 is interpreted as a string


oResult = oStatement.executeQuery( "select * from blobtable") 'insert a blob. Here, a real byte array is inserted
oResult.moveToInsertRow()
oResult.updateBytes(2, CreateUnoValue("[]byte", Array(3, 2, 1)))
oResult.insertRow()


oResult = oStatement.executeQuery( "select * from blobtable") 'now read the blobs back ...

oResult.next()
oBlob1 = oResult.getBlob(2)
abReturn1 = oBlob1.getBytes(1, oBlob1.length)
xray abReturn1

oResult.next()
oBlob2 = oResult.getBlob(2)
abReturn2 = oBlob2.getBytes(1, oBlob2.length)
xray abReturn2

End Sub

[khoward]

Hey ms777,
Good solution!
Yeah, there must be some limitation reading blobs using ODBC driver.
I learned a little bit more from reading your code.
Thanks for sharing!