Feature request #9163

Extend DBManager to Oracle

Added by Regis Haubourg almost 4 years ago. Updated about 2 years ago.

Status:Closed
Priority:Low
Assignee:-
Category:DB Manager
Pull Request or Patch supplied:No Resolution:
Easy fix?:No

Description

Hi all,
I miss the ability to create a datasource from a custom SQL query in Oracle.
Extending DBmanager (or have a dedicated plugin like FastSQLLayers) working with Oracle would be handy.
I do not use MSSQL, but I guess that is interesting someone..
No hurry, I'm filing the feature request for future uses.
Régis


Related issues

Related to QGIS Application - Feature request #14527: DB Manager - Add support for SQL-Server Open 03/18/2016
Duplicated by QGIS Application - Feature request #11652: Add support for Oracle and SQL Server to DB manager Closed 11/15/2014

Associated revisions

Revision 644bdf04
Added by Jürgen Fischer about 2 years ago

Merge pull request #2222 from medspx/dbmanager-oracle

DBManager Oracle Spatial support (fixes #9163)

History

#1 Updated by Regis Haubourg almost 4 years ago

In fact,
I tested typing a sql query directly in the qgs file, and it fails to load project, when doing the same with postgis works. Does the provider supports this? Maybe we encounter table / schema double quote problemes (Oracle table names are upper case)

#2 Updated by Médéric RIBREUX about 3 years ago

It seems that the Oracle data provider from QGis 2.4 is able to handle custom queries. I've done lots of tests and the performances are quite good (same level than ArcGIS 10.2).

In order to load a custom SQL query from Oracle you have to modify the qgs file and change the table definition (table= variable). Just add parenthesis and use escaping to deal with " characters. You also have to add a key definition in the layer definition (add key=field_name before table statement), otherwise QGis is not able to find a primary key for the layer and returns an error.

Here is an example of a layer definition in the qgs file:

<datasource>dbname='geobase' port=1521 user='USER' password='password' estimatedmetadata=true srid=-1 type=POLYGON key=GID table="(SELECT a.GID, a.FIELD1, a.FIELD2, a.FIELD3, a.FIELD4, a.FIELD5, a.GEOM FROM \\"SCHEMA\\".\\"LAYER1\\" a, \\"SCHEMA\\".\\"LAYER2\\" b WHERE SDO_ANYINTERACT(a.GEOM, b.GEOM) = 'TRUE' AND b.FIELD=1234561)" (GEOM) sql=</datasource>

Now that we have a quite reliable Oracle provider to handle dynamic queries, I agree that we need to have DBManager support for Oracle...

#3 Updated by Jürgen Fischer about 3 years ago

Médéric RIBREUX wrote:

In order to load a custom SQL query from Oracle you have to modify the qgs file and change the table definition (table= variable). Just add parenthesis and use escaping to deal with " characters. You also have to add a key definition in the layer definition (add key=field_name before table statement), otherwise QGis is not able to find a primary key for the layer and returns an error.

No need to modify the project file. You can also use QgsDataSourceURI:

uri = QgsDataSourceURI()
uri.setConnection('','1521',"geobase",'USER','password')
uri.setSrid("-1")  # Edit: has to be a string
uri.setUseEstimatedMetadata(True)
uri.setWkbType(QGis.WKBPolygon)
uri.setDataSource("", '(SELECT a.GID, a.FIELD1, a.FIELD2, a.FIELD3, a.FIELD4, a.FIELD5, a.GEOM FROM "SCHEMA"."LAYER1" a,"SCHEMA"."LAYER2" b WHERE SDO_ANYINTERACT(a.GEOM, b.GEOM) = \\'TRUE\\' AND b.FIELD=1234561)', 'GEOM', '', 'GID')

to produce the appropriate uri and use it with iface.addVectorLayer().

#4 Updated by Regis Haubourg about 3 years ago

Thanks for the hacking hints. My request is to have a GUI for that.. Anyway, I couldn't have python do the trick:
1- uri.setSrid(-1)returns an error:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
TypeError: QgsDataSourceURI.setSrid(QString): argument 1 has unexpected type 'int'

2-When I can load attribute table from provider, I can't load the same with python, vector layer is invalid.

here is what I tried:

uri = QgsDataSourceURI()
uri.setConnection('vslin189','1521',"EXP",'visu','visu')
uri.setUseEstimatedMetadata(True)
uri.setDataSource("", '(SELECT ROWNUM, comm_comm_insee, comm_dept_num, lac_code from "GOC"."GEN_COMMUNE_LAC"', '', 'ROWNUM')
vlayer = QgsVectorLayer(uri.uri(), "test_oracle_layer", "oracle")
vlayer.isValid()
>>False

Am I doing it wrong with not defining geometry type and letting geom column name empty?
I could'nt find in the docs how to define a uri for an attribute table.
Cheers
Régis

#5 Updated by Jürgen Fischer about 3 years ago

Regis Haubourg wrote:

Thanks for the hacking hints. My request is to have a GUI for that..

Might still be a starting point for volunteers.

Anyway, I couldn't have python do the trick:
1- uri.setSrid(-1)returns an error:
[...]

Right, actually it's uri.setSrid("-1")

2-When I can load attribute table from provider, I can't load the same with python, vector layer is invalid.

uri = QgsDataSourceURI()
uri.setConnection('somehost', '1521', "someservicename", 'someuser', 'somepassword')
uri.setSrid("-1")
uri.setUseEstimatedMetadata(True)
uri.setWkbType(QGis.WKBNoGeometry)
uri.setDataSource("", '(SELECT somecolumn,someothercolumn FROM sometable WHERE somecolumn<somevalue)', None, '', 'somecolumn')
v = iface.addVectorLayer( uri.uri(), "sometable", "oracle" )
v.isValid()

works for me.

#6 Updated by Regis Haubourg about 3 years ago

Got it !
I missed WKBNoGeometry, None value for geom field and parenthesis for SQL definition!

Thanks a lot Jürgen, If I find some time, I could give life back to FastSQLLayer and adapt it for Oracle.. or fund dbmanager extension..

Cheers,
Régis

#7 Updated by Regis Haubourg about 3 years ago

One last question, locator is not activated here (and will not), do you think it is possible to fake geometry by manually computing point WKT?
I tried this, with no success:

uri.setDataSource("", '(select id  , \\'SRID=2154;POINT(\\'||y||\\',\\'||x|| \\')\\' geomwkt from goc.gen_localis where rownum <10)', 'geomwkt', '', 'id')

That would be sufficient for some uncommon usecases here (we have ETL jobs to load data in postgis, but sometimes, having a direct read access for geometries is cool)

#8 Updated by Regis Haubourg about 3 years ago

Regis Haubourg wrote:

One last question, locator is not activated here (and will not), do you think it is possible to fake geometry by manually computing point WKT?
I tried this, with no success:
[...]

That would be sufficient for some uncommon usecases here (we have ETL jobs to load data in postgis, but sometimes, having a direct read access for geometries is cool)

I have my answer in error log of qgis, this is not possible since QGIS asks SDO functions to get srid of geom field..

#9 Updated by Médéric RIBREUX about 3 years ago

Hello,

I've written an implementation of DBManager for Oracle . It needs cx_Oracle to work which is quite easy to install under MS-Windows and seems to provide more work to install it under GNU/Linux.

Hope it will help and it will be implemented in the official QGis source code (it is the only objective of this work)...

best regards

#10 Updated by Jürgen Fischer about 3 years ago

Médéric RIBREUX wrote:

I've written an implementation of DBManager for Oracle . It needs cx_Oracle to work which is quite easy to install under MS-Windows and seems to provide more work to install it under GNU/Linux.

Could you also use QtSql and qocispatial that is shipped with QGIS?

#11 Updated by Paolo Cavallini almost 3 years ago

See also #11652 (extend also to SQL server, possibly other DBs).

#12 Updated by Jürgen Fischer over 2 years ago

Jürgen Fischer wrote:

Médéric RIBREUX wrote:

I've written an implementation of DBManager for Oracle . It needs cx_Oracle to work which is quite easy to install under MS-Windows and seems to provide more work to install it under GNU/Linux.

Could you also use QtSql and qocispatial that is shipped with QGIS?

See also https://github.com/medspx/dbmanager-oracle/pull/3

#13 Updated by Jürgen Fischer about 2 years ago

  • Status changed from Open to Closed

Also available in: Atom PDF