iMatch 2017 Scripting and App -- Connect to Oracle Database

[helen]

Hi,

I am a new baby for iMatch2017. Now I have a task about retrieving data from external Oracle Database and import those data to the related attribute items in iMatch2017. It is a little bit difficult for me to find out where to start with. Is there anyone can support any ideas about connecting an external database in iMatch2017? For example, which programming language is more suitable and security? My first thought is to use JavaScript but the Google says it is horrible dangerous to connect database by using JavaScript. Because it will ship the connection string to the client, and the client gets the ability to do whatever they want.
Also, I might need to build a User Interface which enables customized operations. But this part is lower priority.

Thanks for your help in advance.

[Mario]

You cannot connect to external databases from JavaScript. Except your database system exposes a web service (similar to what IMatch does) which allows you to retrieve the data. And then, given that you connect to the web service via an encrypted connection (HTTPS) it is pretty secure.

But the beauty of the app concept in IMatch 2017 is that it is agnostic to the programming language you use.
You can use any programming language which can utilize a web service (to access the IMatch database): Java, .NET, Python, PHP, Go, Koitlin, ... even Windows PoweShell.(Most) of these languages have bindings to connect to external databases like ORACLE or MySQL. And they can access IMWS so this should do all you need to do. It depends on which programming languages you already know.

I show some simple examples for Windows Powershell and Python in the Developer Center.

Is this task a one-time operation? Or infrequently?
In that case maybe a simple "Export data from ORACLE to a CSV file", import into IMatch via the CSV Import module will do?
It depends on the kind and volume of data you need to process, and how often.

Another way would probably an JSON export from ORACLE and then a Windows PowerShell or Python script which loads that data into IMatch. Or, if the volume is not too big, you can do this with JavaScript as well. I have successfully used very large JSON files to import data into IMatch Attributes.

Also, I might need to build a User Interface which enables customized operations. But this part is lower priority.

That should not be to difficult. You can use JavaScript and HTML to write an app that runs in IMatch, in an App Panel or a separate (dialog) window.
Or you write a software in whatever programming language you are experienced with - as long as it can utilize a web service to access IMatch you#re fine.

[helen]

Hi Mario,

Thanks for your advice. Well, this process is not one-operation. When new records come into the Oracle Database, the manager will sync the data to the iMatch 2017. So it might not be a good idea to 'export the data from Oracle into a CSV file' quite often. My current idea is creating an simple UI which allows the clients to click a button and a script will be run in the back-end. Then the data can be transferred into Attributes panel in iMatch2017.

My current plan is : using Python to implement a server which can retrieve data from Oracle Database and import the data into iMatch2017. Then build the UI with JavaScript and HTML. The JavaScript sends XMLHttpRequest object to the page on the this server. I am not sure if it is an efficient way. My another question is can I use any web framework in iMatch2017,such as CherryPy or some PHP framework?

Thanks 

[Mario]

What programming language or framework you use on your server is irrelevant for iMatch. If your server exposes a web service or even just an URL that enables your IMatch App to access the data, it will work.

You can start external applications / scripts from IMatch Apps. See the "Process" sample app for more info about how to start and monitor an external script. This could be used e.g., to monitor run your Python script in the background. Or, if you implement something in PHP, you can just call an URL from your IMatch App to pull the data out of your PHP service and import it into IMatch.

[helen]

Hi Mario,

Sorry to bother you too much. I am trying to write a python script to update the attributes of the selected image. My partial code is following:

Code Select Expand

def update_attrs():
    task = []
    data = {'Title': 'LOGO','Description':'Add your description here','Date':'2017-07-31'}
    json_data = json.dumps(data)
    task_instance = {
        'op': 'update',
        'instanceId': 1,
        'data': json_data
        }
    task.append(task_instance)
    try:
        update_attrs_req = requests.post(HOST_URL + '/v1/attributes',params={
            'auth_token': auth_token,
            'idlist': '@imatch.filewindow.active.selection',
            'set': 'Image',
            'tasks': task
            },timeout=REQUEST_TIMEOUT)
        if update_attrs_req.status_code == requests.codes.ok:
            print ('SUCCESSFULLY')
        else:
            update_attrs_req.raise_for_status()
        return
   
    except Exception as ex:
        print (ex)

The output is `SUCCESSFULLY`but the content are not updated at all. Do you have any clue for this issue? This code is only used for test...
Thanks so much...

[thrinn]

Unfortunately, I am not accustomed to Python, but maybe some things to check:
'instanceid' must be lower case (not camel case 'instanceId')
'instanceid' must be an array of ids, not a single value

If you use 'update' it only works if there is an attribute record already.

In most cases the 'instanceId' of the first attribute record is indeed 1, but this is not guaranteed. InstanceIds may change when adding/deleting multiple attribute records for a file, or by compacting the database.

[Mario]

Yep. All IMatch/IMWS endpoints parameters are in lower-case and case-sensitive.
Does IMWS not return additional info in the response?

[helen]

@Mario @thrinn

Hey,

I made some changes to my code:

Code Select Expand

def update_attrs():
    task = []
    data = {'Title': 'LOGO','Description':'Updated value'}
    task_instance = {
        'op': 'update',
        'instanceid': [1],
        'data': json.dumps(data)
        }
    task.append(task_instance)
    print(type(json.dumps(data)))
    print(type(task))
    try:
        update_attrs_req = requests.post(HOST_URL + '/v1/attributes',params={
            'auth_token': auth_token,
            'idlist': '@imatch.filewindow.active.selection',
            'set': 'Image',
            'tasks': task
            },timeout=REQUEST_TIMEOUT)
        if update_attrs_req.status_code == requests.codes.ok:
            print (update_attrs_req.text)
        else:
            update_attrs_req.raise_for_status()
        return
   
    except Exception as ex:
        print (ex)

the error message is : {"result":"failed","resultMessage":"parse error - unexpected 'o'"}
I guess the reason is I did not parse the `task` or `data` in the correct format. My question is do you have any data type requirement for the variable, such as `data` and `task`.
(in my code, `task` is a list; `data` is string)

Thanks 

[thrinn]

Hi Helen,
maybe it is more a question of URL encoding than of data type?
I used the Attributes Sample App to check what is sent to IMatch.
To update attribute "Beschreibung" with "Test" the app creates something like:

Code Select Expand


setid:1
idlist:@imatch.filewindow.active.selection
tasks:[{"op":"update","instanceid":["1"],"data":{"Beschreibung":"Test"}}]


But what is sent to the Web Service is:

Code Select Expand


setid=1&idlist=%40imatch.filewindow.active.selection&tasks=%5B%7B%22op%22%3A%22update%22%2C%22instanceid%22%3A%5B%221%22%5D%2C%22data%22%3A%7B%22Beschreibung%22%3A%22Test%22%7D%7D%5D


I do not "speak" Python, but Python should be able somehow to properly URL-encode the data.

[Mario]

This is a POST request and the (task) data is sent as part of the request body.
If you look at the Attributes example, it does a

params.tasks = JSON.stringify(tasks);

to properly stringify (convert into a JSON string) the task object.
The server does the reverse operation, converting from a JSON string into a JSON object.

I don't speak Python but I'm sure you need to tell it to convert your task object into the proper string representation before sending it.

[helen]

Hey Mario,

Yes, You are right. My problem is that I did not convert the `task` into a proper string format. Now I changed my code as following and it worked out:

Code Select Expand


task = []
    data = {'Title': row[1],'Description':row[2],'Copyright':row[4]}
    task_instance = {
        'op': 'add',
        'data': data
        }
    task.append(task_instance)
    json_task = json.dumps(task)
    try:
        update_attrs_req = requests.post(HOST_URL + '/v1/attributes',params={
            'auth_token': auth_token,
            'idlist': '@imatch.filewindow.active.selection',
            'set': 'Image',
            'tasks': json_task
            },timeout=REQUEST_TIMEOUT)
        if update_attrs_req.status_code == requests.codes.ok:
            print (update_attrs_req.text)
        else:
            update_attrs_req.raise_for_status()
        return

`json_task = json.dumps(task)` ----- this step is quite important in Python !!

Currently, I can import the attribute data to iMatch2017 from oracle database in Python script, but only for one selected image files. Another question has come out : does iMatch2017 support updating the attribute records of multiple images at one time? I mean adding attribute records to all images which are located in a selected folder at one time?
Let me use an simple example to explain my questions more clearly:

folder A  contains image files : a1 a2 a3 a4...My task is selecting this folder and run the script. Then data can be retrieved from oracle database and import into the attribute section of related images.

Any suggestions are welcome~~

[Mario]

Currently, I can import the attribute data to iMatch2017 from oracle database in Python script, but only for one selected image files.

You specify the idlist '@imatch.filewindow.active.selection' in your code so you will only change these files.
To update data from arbitrary files you need to determine their ids. I don't know how you know which files to update, which kind of key you use in your Oracle DB. After figuring out the IMatch file ids from that, you can update all files in one batch.

You can lookup files by their fully-qualified file name using the /files endpoint, for example. Or the special /files/lookup endpoint, which has been designed for import purposes. It takes any number of file names and returns the matching id. You can then either use these ids directly, or create an idlist using one of the /idlist endpoints and then use that. Depends on your requirements.

I mean adding attribute records to all images which are located in a selected folder at one time?

Sure. Always the same. Get the ids of the files you want to update, then use them. You can update individual files, use the /folder/files endpoint to get all files in a folder, or the /categories... endpoint to get all files in a category. You can search for files using one of the search functions and then use the resulting ids etc. Always the same.