Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Extend DBManager to Oracle #17796

Closed
qgib opened this issue Dec 4, 2013 · 13 comments

Comments

@qgib
Copy link
Contributor

commented Dec 4, 2013

Author Name: Regis Haubourg (@haubourg)
Original Redmine Issue: 9163

Redmine category:db_manager


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 issue(s): #19904 (duplicates), #22500 (relates)
Redmine related issue(s): 11652, 14527


@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Dec 4, 2013

Author Name: Regis Haubourg (@haubourg)


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)

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 8, 2014

Author Name: Médéric RIBREUX (Médéric RIBREUX)


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:

@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=@

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

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 8, 2014

Author Name: Jürgen Fischer (@jef-n)


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()@.

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2014

Author Name: Regis Haubourg (@haubourg)


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

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2014

Author Name: Jürgen Fischer (@jef-n)


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.

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2014

Author Name: Regis Haubourg (@haubourg)


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

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2014

Author Name: Regis Haubourg (@haubourg)


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)

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2014

Author Name: Regis Haubourg (@haubourg)


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..

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 28, 2014

Author Name: Médéric RIBREUX (Médéric RIBREUX)


Hello,

I've written "an implementation of DBManager for Oracle":https://github.com/medspx/dbmanager-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

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Aug 28, 2014

Author Name: Jürgen Fischer (@jef-n)


Médéric RIBREUX wrote:

I've written "an implementation of DBManager for Oracle":https://github.com/medspx/dbmanager-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?

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Nov 16, 2014

Author Name: Paolo Cavallini (@pcav)


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

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Jun 6, 2015

Author Name: Jürgen Fischer (@jef-n)


Jürgen Fischer wrote:

Médéric RIBREUX wrote:

I've written "an implementation of DBManager for Oracle":https://github.com/medspx/dbmanager-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

@qgib

This comment has been minimized.

Copy link
Contributor Author

commented Jul 25, 2015

Author Name: Jürgen Fischer (@jef-n)


Fixed in changeset "644bdf046fc305ddd068b7d31b08080baaba859a".


  • status_id was changed from Open to Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
1 participant
You can’t perform that action at this time.