running raw SQL call issues

[Manz]

I am trying to run a raw query of the postgresql tables and forward the results onto the html pages set up in webtest, however I am not sure I am using the right function to do this and if I am, how to shape the input to what I would like.

Currently I have this :-

Code: Select all

@isUserConnected   # wrapper handles login (or redirects to webclient login). Connection passed in **kwargs
def manz(request, annId, **kwargs):
   import string, os
#    from omero.gateway import DatasetWrapper, ImageWrapper
   conn = kwargs['conn']
   eid = conn.getEventContext().userId
   query = "select id from Image as id"
   queryService = conn.getQueryService()
   params = omero.sys.ParametersI()
#    params.addString("id", id)
   imageLinks = queryService.findAllByQuery(query, params)
   response =HttpResponse("<p>b %s </p>" % (imageLinks[1]))
   return response

And I expected to get only the id's (1,51,52,53) but instead got the following

Code: Select all

object #0 (::omero::model::Image) { _id =
object #1 (::omero::RLong) { _val = 51 } _details =
object #2 (::omero::model::Details) { _owner =
object #3 (::omero::model::Experimenter) { _id =
object #4 (::omero::RLong) { _val = 2 } _details = _loaded = False _version = _groupExperimenterMapSeq = { } _groupExperimenterMapLoaded = False _omeName = _firstName = _middleName = _lastName = _institution = _email = _annotationLinksSeq = { } _annotationLinksLoaded = False _annotationLinksCountPerOwner = { } } _group =
object #5 (::omero::model::ExperimenterGroup) { _id =
object #6 (::omero::RLong) { _val = 3 } _details = _loaded = False _version = _name = _groupExperimenterMapSeq = { } _groupExperimenterMapLoaded = False _annotationLinksSeq = { } _annotationLinksLoaded = False _annotationLinksCountPerOwner = { } _description = } _creationEvent =
object #7 (::omero::model::Event) { _id =
object #8 (::omero::RLong) { _val = 1124 } _details = _loaded = False _status = _time = _experimenter = _experimenterGroup = _type = _containingEvent = _logsSeq = { } _logsLoaded = False _session = } _updateEvent = _permissions =
object #9 (::omero::model::Permissions) { _perm1 = -39 } _externalInfo = } _loaded = True _version = _acquisitionDate =
object #10 (::omero::RTime) { _val = 1281923769000 } _archived =
object #11 (::omero::RBool) { _val = False } _partial = _format =
object #12 (::omero::model::Format) { _id =
object #13 (::omero::RLong) { _val = 171 } _details = _loaded = False _value = } _imagingEnvironment = _
objectiveSettings = _instrument = _stageLabel = _experiment = _pixelsSeq = { } _pixelsLoaded = False _wellSamplesSeq = { } _wellSamplesLoaded = False _roisSeq = { } _roisLoaded = False _datasetLinksSeq = { } _datasetLinksLoaded = False _datasetLinksCountPerOwner = { } _annotationLinksSeq = { } _annotationLinksLoaded = False _annotationLinksCountPerOwner = { } _name =
object #14 (::omero::RString) { _val = /OMERO/DropBox/Manz/GFPmef_C002.tif } _description = }

which I assume is because of the way param tells findAllByQuery what to bring back. How do I alter this? My eventual plan is to query a secondary schema that I have set up on the same database in postgresql . Or if it possible to run a straight sql call and get the answers back? If it is not, I'll just use a secondary connection using python to run the query, but I would rather run the call using the connection to postgresql already open. I'm sorry if this is a noob question, I am new to Django.

Thanks for your help!

[jmoore]

Hi,

You'll need to change:

Code: Select all

   query = "select id from Image as id"
   queryService = conn.getQueryService()
   params = omero.sys.ParametersI()
#    params.addString("id", id)
   imageLinks = queryService.findAllByQuery(query, params)


to

Code: Select all

   query = "select i.id from Image as i"
   queryService = conn.getQueryService()
   params = omero.sys.ParametersI()
   imageLinks = queryService.projection(query, params)
   ids = [ x[0].val for x in imageLinks ]


...
which I assume is because of the way param tells findAllByQuery what to bring back. How do I alter this? My eventual plan is to query a secondary schema that I have set up on the same database in postgresql . Or if it possible to run a straight sql call and get the answers back? If it is not, I'll just use a secondary connection using python to run the query, but I would rather run the call using the connection to postgresql already open. I'm sorry if this is a noob question, I am new to Django.

It's important to remember that though very similar, these methods take HQL (Hibernate Query Language) and not SQL.

As for creating a secondary schema, the OMERO API currently disallows access to anything other than the mapped Hibernate tables for security reasons. Sorry for the hassle.

Cheers,
~Josh.

[Manz]

As for creating a secondary schema, the OMERO API currently disallows access to anything other than the mapped Hibernate tables for security reasons. Sorry for the hassle.

How do I map Hibernate tables?

[jmoore]

The mapping is done for you in the server. Each table is mapped to an object; each column to a field (more or less). These are the same objects that get returned via most IQuery and other service methods. In the case of OMERO, the mapping is fairly one-to-one in terms of naming.

If you'd like to read more about the ins-and-outs of HQL you can see the Hibernate language reference. For examples of such HQL queries, you might start with WorkingWithOmero, the examples directory, or other threads in the forums, like "HQL queries and other short examples".

Cheers,
~Josh.

P.S. after writing that, I realized you may be talking about how can you add new mappings to the already existing OMERO ones. The short answer is: you can't. You would need to also create the remoting objects, etc. etc. You may want to take a look at ExtendingOmero for the longer answer to your question.