Export of binary field images

Hi,

Is it somehow possible to export images stored in binary fields from local Derby databases?

Thanks,

Evert

Hi Evert,

unfortunatelly, this can only be done by the hard way. You would need to access the database directly and do the export yourself by SQL.

Derby DB can be accessed using the database explorer: http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/tips_and_tricks/db_explorer.html

Another possibility would be to write a groovy script for that. We don't have any example for this, and I'm affraid that it's not a simple task.

Cheers,

Filip

You might be able to do this with Knime.

Download it from here: http://www.knime.org/node/81

And see here for database connections  http://tech.knime.org/database-documentation

But be warned: once you learn to do these kind of things in knime you will never want to go back to scripting. Trust me, i speak from experience, and feel like a drug pusher for suggesting it

Here is a prototype script, which may be used for obtaining images from binary field.
It has some restrictions:

• Filename field has to be present.


• If filename is empty the script ends up with NPE

I hope it may serve as an example how to export binary data.

import com.google.common.io.Files



def ety = dataTree.rootVertex.entity

def edp = ety.schema.dataProvider.getEntityDataProvider(ety)



// Modify the name of the directory.

// Images or other binary files will be exported into it.

def targetDir = "c:\\temp\\exported_images" // for UNIX systems “/tmp” fro example



// Name of image field

imageField = ety.fields.items.find { it.name == "New Binary Field" }



// Name of text field which has values used as file names

filenameField = ety.fields.items.find { it.name == "Filename" }



ids = edp.queryForIds(DFTermExpression.ALL_DATA, SortDirective.EMPTY, env)

ids.each { id ->

    values = edp.getData([id], env)[id]

    filename = values[filenameField.id]

    byte[] image = values[imageField.id]

    File target = new File(targetDir + File.separator + filename)

    println target

    println filename

    Files.write(image, target)

}

Ellert van Koperen wrote:

You might be able to do this with Knime. Download it from here: http://www.knime.org/node/81 And see here for database connections  http://tech.knime.org/database-documentation But be warned: once you learn to do these kind of things in knime you will never want to go back to scripting. Trust me, i speak from experience, and feel like a drug pusher for suggesting it

Hi Ellert,

Maybe you can help me out here: what would be the correct syntax for the database URL in a Knime Database Connector node for a local Derby-type database on Windows, e.g. jdbc:derby://<host>:<port>/<database_name>?

Let's say that my database is located at c:\ijc-project, what should the URL look like in order to be able to connect? Are there any other settings for the Database Connector node that are of importance?

Thnaks,

Evert

Hi Evert,

I personally do not use derby, i rather work with for example mysql
(would use oracle for the best performance if i had the money to spend
on that, but at this moment the priorities are different). For mysql
running on localhost the connectionstring is: 
jdbc:mysql://localhost:3306/databasename

With the documentation page here http://tech.knime.org/database-documentation  you should be able to get going.
If
you made a connection and it doesn't balk, then you can press the Fetch
Metadata button to get the layout of your database. That is proof that
the connection is working.

Note that i prefer the Database
Reader node, as in that one you can do everything in one node
(connecting and firing off a query), but others prefer the more
structured approach of the Database Connector + Database Query /
Database Connection Reader, etc.

I hope this gets you going (and welcome to the dark side ) further KNIME questions are better asked on the knime forum or by personal message.

Cheers, Ellert.

Thanks Ellert. I know this is fairly straightforward if you have a true mySQL server. My problem is that I have a large number of images in a local Derby database (i.e. without a true server), which I want to move to a mySQL server. In order to be able to export them using Knime I need to know the correct syntax of the database path (which in this case merely is a folder on my local PC).

I was hoping you had some experience with this as well. Thanks anyway.

Hi Evert, 

the ansver for your previous question is that the hole url for your local project you can find in schema settings in IJC by right click on this project in field "JDBC URL" : it looks like that:

jdbc:derby:C:/Users/jiri/ijc-project357/.server/localdb/db;upgrade=true

Hi,

This does not make a difference, but is more likely a Knime and Derby driver-related problem, not IJC.

Hello Evert,

you can use Squirrel sql and derby driver (both in prerequisite section on the linked page) and then export your select which should  be importable again.

URL should be starting same as in Baštíks' previous answer, username is APP and there is no password.

with this you should be able to login to the database where you can export the content as sql insert which should be import-able to MySQL.

Best regards,

Martin

Playing around with derby, something started to tickle: are you using the derby-server, or trying to read the database-data file directly ?

The latter aproach will not work.

If you download the derby installer you will find documentation with it, and i recomend you read up to here : Derby_10/docs/html/getstart/index.html

there is explained how you can connect to it using a syntax like so:  

jdbc:derby://localhost:1527/seconddb
 

 

OK, I can now connect to a DB with Squirrel and select data from a table with images, but how can I actually export them?

Hello Evert,

I forgot to mention that you will also need to create a new alias for your MySQL schema (driver for squirrel is here)

Now you just find your table, select it and r-click  > Copy Table, go to your MySQL and Paste Table to correct schema.

after that, login to your MySQL database again with Instant JChem and just promote your table.

Best regards,

Martin

Hi,

I tried your approach but get the following error when pasting the copied table into the mySQL schema:

'Source database table has a column define (CD_ID) that cannot be used as a column name in the destination database'

The table I copy is a structure table, hence the CD_ID column. I don't really need this column in the destination table though.

Any tips?

Thanks,

Evert

You can perhaps duplicate the table in derby and then remove the cd_id filed in the duplicated table prior to moving it to the MySQL.

you can also export it via ij tool by Apache Derby - documentation on how to do that is in Derby documentation.

Best regards,
Martin