Opened 16 years ago

Closed 14 years ago

#2482 closed defect (fixed)

the SQL BETWEEN statement does not appear to be fully implented

Reported by: bclay Owned by: chaitanya
Priority: normal Milestone: 1.8.0
Component: OGR_SF Version: unspecified
Severity: major Keywords: OGR SQL, BETWEEN, oci
Cc: warmerdam

Description

When I try to use issue a SQL query with a date range it is not passed though to Oracle. Added the BETWEEN statement as a valid option to swg.c

Attachments (4)

swq.c (79.8 KB ) - added by bclay 16 years ago.
swq.h (5.6 KB ) - added by bclay 16 years ago.
between-sql-ticket-2482-against-trunk-r14954.patch (10.7 KB ) - added by Mateusz Łoskot 16 years ago.
Bruce's changes as regular patch generated against trunk (r14954)
between-sql-ticket-2482-related-changes-only.patch (597 bytes ) - added by Mateusz Łoskot 16 years ago.
This is new patch with only those changes I found as related to BETWEEN SQL. This patch needs to be reviewed. Bruce, I'd be very thankul for review.

Download all attachments as: .zip

Change History (14)

by bclay, 16 years ago

Attachment: swq.c added

by bclay, 16 years ago

Attachment: swq.h added

comment:1 by hobu, 16 years ago

Component: SWIG (all bindings)OGR_SF
Owner: changed from hobu to warmerdam

comment:2 by warmerdam, 16 years ago

Cc: warmerdam added
Keywords: OGR added
Milestone: 1.6.0
Owner: changed from warmerdam to Mateusz Łoskot
Priority: highnormal

Mateusz,

Please look into integrating BETWEEN support into OGR SQL trunk if it is convenient. If this is done, please ensure that the OGR SQL document (ogr/ogr_sql.dox) is updated, and an appropriate test suite entry is added. Please talk to me if you are concerned the implementation is complex or risky as I don't consider this a very high priority feature.

comment:3 by Mateusz Łoskot, 16 years ago

Keywords: oci added
Status: newassigned

comment:4 by warmerdam, 16 years ago

Mateusz,

I don't see how this has anything to do with the OCI driver. It appears Bruce has provided updates to the OGR SQL implementation - not the OCI driver.

If the problem is that WHERE clauses or whole ExecuteSQL() statements are not getting passed through to the OCI driver then something different needs to be done than the proposed patches.

comment:5 by Mateusz Łoskot, 16 years ago

Frank,

I understand. Simply, Bruce's message it is not passed though to Oracle suggested me it is related to OCI driver, so I decided to updated keywords.

Anyway, I will get on it tomorrow and see what's going on there.

by Mateusz Łoskot, 16 years ago

Bruce's changes as regular patch generated against trunk (r14954)

comment:6 by Mateusz Łoskot, 16 years ago

Bruce,

I'd be thankful if you could provide me with details of what GDAL version you used to apply your changes. If it was SVN trunk, perhaps you know what revision you used?

by Mateusz Łoskot, 16 years ago

This is new patch with only those changes I found as related to BETWEEN SQL. This patch needs to be reviewed. Bruce, I'd be very thankul for review.

comment:7 by Mateusz Łoskot, 16 years ago

Bruce,

Please, could you review the new patch I generated, file between-sql-ticket-2482-related-changes-only.patch and confirm if it consists of all changes you have made for the BETWEEN SQL feature?

-- mateusz

comment:8 by Mateusz Łoskot, 16 years ago

I've given another attempt to this issue and here is what I found:

  • editing swq.h/.c has nothing to do with SQL execution by OCI driver, so patchin OGR SQL engine to get Oracle working is irrelevant
  • Generally, SQL queries work with OCI driver (ie. ogrinfo ... -sql "SELECT ...")
  • Specifically, SQL queries with WHERE clause comparing Date and DateTime values do not work. No error is thrown, just empty/null layer is returned.

The 3rd point is quite misterious, because no OCI error occurs, resultset definition is correctly obtained (list of fields and types in query, etc.), OCIStmtExecute in OGROCIStatement::Execute does not fail with error, however subsequent call to OGROCIStatement::SimpleFetchRow fails returning NULL.

It definitely needs to be investigated, what I hope to get done.

comment:9 by warmerdam, 14 years ago

Milestone: 1.6.4
Owner: changed from Mateusz Łoskot to chaitanya
Status: assignednew

Pursue this issue as time permits. It is not considered release critical.

comment:10 by Even Rouault, 14 years ago

Milestone: 1.8.0
Resolution: fixed
Status: newclosed

Implement in r20563. Test added in r20564

Note: See TracTickets for help on using tickets.