View Issue Details

IDProjectCategoryView StatusLast Update
0010996mantisbtdb oraclepublic2015-03-16 19:20
ReporterwatergadAssigned Todregad 
PrioritynormalSeverityblockReproducibilityalways
Status closedResolutionfixed 
Platformx86OSWindowsOS Version
Product Version 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0010996: Cant use Mantis with oracle9 - var binding fails
Description

Doesn't work with oracle9.
Used:

  • mantis 1.1.8 or 1.2.0rc1
  • adodb within mantis or latest adodb
  • instant client (10)
  • php ext php_oci8.dll or php_oci8_11.dll or php_pdo_oci.dll
    I even tried to use ado/odbc through Oracle odbc driver.
Additional Information

I debug the process watching the echoing state of queries and vars (and oracle trace). Whatever I do - seems that variables doesn't bind.
Ado calls OCIBindByName with query and array (I can't see anything wrong with them) but the oracle client traces unbind query.
When I use coarse string replacements instead of OCIBindByName I can go a little further but of course its still unusable.

Is there something I don't understand or doesn't Mantis support oracle now?

TagsNo tags attached.

Relationships

related to 0012150 closeddregad Mantis 1.2.1 Install Error using Oracle db 
related to 0010488 closeddregad Inserting strings > 4000 Bytes not working -> direct file upload and email 
child of 0013227 closeddregad Oracle DB support multiple issues 

Activities

damien_b

damien_b

2009-10-29 18:28

reporter   ~0023476

We've used patched Mantis 1.1.2 -> 1.1.7 with Oracle 10g, and the binding part (by name, we don't use binding by position) always worked. By default, the 1.1.x series doesn't use binding at all to my knowledge.

watergad

watergad

2009-10-30 03:30

reporter   ~0023483

Maybe 10g client with 9i db causes the problem, I'm not sure...

watergad

watergad

2009-11-25 04:53

reporter   ~0023807

Tried 10g db with 10g client - same thing.

Last queries from trace:
|........|
|........|
|........|
|..4SELEC|
|T.*.....|
|..FROM.m|
|_user_t.|
|......WH|
|ERE.user|
|name=:1.|
|........|
|........|

...

|........|
|........|
|....'ORA|
|-01008:.|
|........|

ORA-01008 means "not all variables bound".

bob

bob

2009-12-06 22:10

reporter   ~0023866

Problem is in the _query code within adodb-oci8 code
Where the bindbyname is done, it is using the array indice as the binding reference, ie: OCIBindByName($stmt,":$k",$inputarr[$k],$len);
However within the array k starts at 0.
Within the sql statement the bind variable starts with :1, so they are always off by 1.

bob

bob

2009-12-06 22:25

reporter   ~0023867

The code within _query is correct. The problem appears to be that the
$g_db_param_count was not always reset for a new statement, so the db_param() call was not starting at :0 for the first parameter.
ie: query for login screen was being generated as
SELECT FROM m_user_tbl WHERE username=:1
instead of
SELECT
FROM m_user_tbl WHERE username=:0
In particular examine user_api.php
$g_db_param_count should always be reset to zero at the start of a new statement.

bob

bob

2009-12-07 09:51

reporter   ~0023873

Partial fix is to reset g_db_param_count within db_query_bound procedure, ie:

function db_query_bound( $p_query, $arr_parms = null, $p_limit = -1, $p_offset = -1 ) {
global $g_queries_array, $g_db, $g_db_log_queries, $g_db_param_count;

# reset global db_param count for next query R. Armstrong Dec 7 2009
$g_db_param_count = 0;

Related Changesets

MantisBT: master 2ce60e47

2011-08-10 12:34:07

dregad

Details Diff
Fix 0013227: Oracle database support (oci8)

Mantis 1.2.6 currently does not work with Oracle DB:
1. Installation:
1.1. Oracle DB autocreates PK, so index creation for same field forbidden
1.2. Oracle DB uses datetime literal format timestamp'YYYY-MM-DD HH-MI:SS'
1.3. Oracle DB don't allows altering field property NOT NULL into NOT NULL
1.4. Oracle DB max object length is 30 chars, so some index names must be reduced
1.5. Oracle DB means empty string as NULLs, so NOT NULL restriction must be disabled for some field
1.6. Oracle DB can resolve database server name through TNS, so database name cannot be required
2. General:
2.2. Direct DB query execution result accessing instead of db_fetch_array() 1.3. usage didn't works with Oracle DB
2.4. Oracle DB binds variable by name, so bind names in statement must be sorted to address them.
2.5. Oracle DB handles NULL/DEFAULT values with specific way.
2.6. Oracle DB returns NULL value as true PHP null
2.7. Oracle DB handles sequence access with specific syntax
2.8. Nothing returned by db_prepare_string() in case of oci8
2.9. Oracle DB max object length is 30 chars, so table names must be reduced
2.10. Oracle DB uses LOB literal format similar to mssql
2.11. GetRowAssoc returns empty field values with oci8, it's need to enable returning both associative and indexed arrays.

The original patch was provided by DKuranov. He reckons that this also resolves
issues 0006853, 0007644, 0010437, 0010996, 0011265, 0011270, 0011276, 0012152, 0012478

Porting to 1.3 - Conflicts:
admin/install.php
admin/schema.php
core/database_api.php
manage_tags_page.php
mod - admin/install.php Diff File
mod - admin/schema.php Diff File
mod - core/database_api.php Diff File

Issue History

Date Modified Username Field Change
2009-10-03 15:19 watergad New Issue
2009-10-29 18:28 damien_b Note Added: 0023476
2009-10-30 03:30 watergad Note Added: 0023483
2009-11-25 04:53 watergad Note Added: 0023807
2009-12-06 22:10 bob Note Added: 0023866
2009-12-06 22:25 bob Note Added: 0023867
2009-12-07 09:51 bob Note Added: 0023873
2010-08-04 16:24 atrol Relationship added related to 0012150
2011-08-09 11:55 dregad Relationship added parent of 0013227
2012-12-20 03:56 dregad Relationship added related to 0010488
2013-10-17 12:08 dregad Changeset attached => MantisBT master 2ce60e47
2013-10-17 12:51 dregad Target Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Status new => resolved
2013-10-17 12:52 dregad Fixed in Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Resolution open => fixed
2013-10-17 12:52 dregad Assigned To => dregad
2014-04-12 19:39 grangeway Issue cloned: 0017201
2014-12-08 00:34 vboctor Status resolved => closed
2015-03-16 19:20 dregad Relationship replaced child of 0013227