Oracle OCI8 issues

Chris
Joined: 18 Jul 2008

Oracle OCI8 issues

Posted:18 Jul 2008 (06:39 UTC)
Hi,

How complete is Oracle support? I found that out of the box Please provide an icon name as iname parameter. You can select icons here. <-- (hmmm... {biticon} is broken) does not work with 10g. Maybe I configured it wrong but unless I comment out the SelectLimit function in util/adodb/drivers/adodb-oci8.inc.php I get ORA-00918 (ambiguous column name) errors.

This is because the function results in SQL such as: SELECT * FROM (SELECT alias1.*, alias2.* etc.

Am I missing something fundamental?

Thanks,
Chris
johnny Walker
Joined: 26 Jul 2008

Re: Oracle OCI8 issues

Posted:26 Jul 2008 (17:57 UTC)
from what I can tell Oracle 10g support is not very well documented.

I'm having difficulty installing against Oracle 10g where I get a blank page after hitting 'install packages'. If I run that install in debug mode where I can see the SQL queries, I can see that the problem is 'invalid identifier' due to quotation marks in the create table scripts.

I found a post mentioning that someone is aware of this and they were working to correct the schema-inc files, but that post was from 2006 and the issue persists. The post mentions that HEAD ( a reference to the latest version in CVS ) should install fine on Oracle.

This has not been my experience.

If I copy the queries that fail into SQL Developer and remove the double quotes entirely and then re-run them they succeed. It doesn't however create a working copy of BitWeaver when I'm done.

I notice there is a replaceQuotes variable defined in one of the adodb / oci8 files. It seems to make no difference when I change it. I've tried backticks, single quotes and double quotes.

To make it more frustrating, when I search the forums for 'Oracle 10g' I find 3 comments, but if I click on their titles I get taken to the General Discussion main page and not into the article itself as one would expect. I have to re-search for the article title and click on it's title again from there.

I'm going to be working on getting this running on Oracle 10g for some time and when I find a solution it will be posted, possibly with a walk through and some suggested documentation to add to the installer pages.
Chris
Joined: 18 Jul 2008

Re: Oracle OCI8 issues

Posted:27 Jul 2008 (07:39 UTC)
Hi Johnny,

Can you describe your setup a little more? I'm running 10.2.0.4 on Linux RH ES4 with Apache 2+PHP 5.26 and oci8 compiled in. I installed without issue apart from a few indexes which were previously created.

I get the odd runtime issue where bw is trying to functions like 'SUBSTRING' and Oracle only has 'SUBSTR'.

As you saw, I also had to comment out that function for any of the SQL that use SELECT yada.* to work.

Yes, I also have issues with the search results in these forums - you click the link in the search results but are just presented with a list of posts

Anyway, I'm really not sure why your Oracle install isn't working. Which BW version did you try to install? Worked with 2.0.2 and 2.1 beta for me.

Cheers,
Chris.
Lester Caine
Joined: 24 Apr 2004

Re: Oracle OCI8 issues

Posted:28 Jul 2008 (06:23 UTC)
I run and develop bitweaver on Firebird, which has a good match to Oracle in terms of SQL, especially since we dropped the double quoting of names from version 2.
I don't use all of the packages even in the core distributions, so some of the parts do not get checked, but occasional 'tidies' of the SQL by Posgres and MySQL developers do result in problems which are normally picked up quite quickly.

SO specific problems that we can clear up please. Which is complicated by the vast range of options available when configuring bitweaver :(
johnny Walker
Joined: 26 Jul 2008

Re: Oracle OCI8 issues

Posted:28 Jul 2008 (13:09 UTC)
When I get to the packages page, I hit the de-select all so that I'm trying to install as little as possible thinking it would be easier to get it running and then add other packages later.

I run in debug mode so that I can see the SQL statements. This is the first one that fails:

(oci8po): CREATE TABLE liberty_content ( content_id DECIMAL(10) NOT NULL, user_id DECIMAL(10) NOT NULL, modifier_user_id DECIMAL(10) NOT NULL, created DECIMAL(20) NOT NULL, last_modified DECIMAL(20) NOT NULL, content_type_guid VARCHAR(16) NOT NULL, format_guid VARCHAR(16) NOT NULL, content_status_id DECIMAL(10) NOT NULL, event_time DECIMAL(20) DEFAULT 0 NOT NULL, version DECIMAL(10), lang_code VARCHAR(32), title VARCHAR(160), ip VARCHAR(39), data CLOB , CONSTRAINT "liberty_content_status_ref" FOREIGN KEY ("content_status_id") REFERENCES "liberty_content_status"( "content_status_id" ) , CONSTRAINT "liberty_content_type_ref" FOREIGN KEY ("content_type_guid") REFERENCES "liberty_content_types"( "content_type_guid" ), PRIMARY KEY (content_id) )


Warning: ociexecute() function.ociexecute: ORA-00904: "content_status_id": invalid identifier in /usr/local/bitweaver/util/adodb/drivers/adodb-oci8.inc.php on line 1026
904: ORA-00904: "content_status_id": invalid identifier

ADODB_DataDict.ExecuteSQLArray(Array1) % line 148, file: install_packages.php
include_once(/usr/local/bitweaver/install/install_packages.php) % line 149, file: install.php

ADOConnection._Execute(CREATE TABLE liberty_content (
content_id DECIMAL(10) NOT NULL,
user_id DECIMAL(10) NOT NULL,
mod..., false) % line 769, file: adodb-oci8.inc.php
ADODB_oci8.Execute(CREATE TABLE liberty_content (
content_id DECIMAL(10) NOT NULL,
user_id DECIMAL(10) NOT NULL,
mod...) % line 269, file: adodb-datadict.inc.php
ADODB_DataDict.ExecuteSQLArray(Array1) % line 148, file: install_packages.php
include_once(/usr/local/bitweaver/install/install_packages.php) % line 149, file: install.php

If I copy the CREATE TABLE statement and re-run it w/o all the double-quotes (which are quite clearly still present, I don't know where you say you removed them from) the statement succeeds.

I'm running the following:

PHP is 5.2.4.
All recommended PHP.ini settings are met.
All Pear extensions installed.
All recommended executables are installed.
I'm only skipping imagick, magickwand and eAccelerator.
I am actually using Zend Core.

The Database connection settings say they are working and are as follows:

Db type: Oracle8.i
host: (tnsname of host)
User: (user)
pass: (pass)
db name: BLANK
db prefix: BLANK

Bitweaver R2_2.0.2
Apache 2.2
RHEL 4
Oracle 10g
Lester Caine
Joined: 24 Apr 2004

Re: Oracle OCI8 issues

Posted:28 Jul 2008 (20:40 UTC)
Second try - power went out when I had nearly finished the first one.

I think I have spotted the problem, and it is all to do with the backtick replacement. In oci8 these are simply stripped, in Firebird the option to replace them with double quote has been left since pre-R2 databases will have them on, but any new build should also just strip them.

In Datadict, the striping is handled a little differently, and they are not present in the schema -BUT for various reasons they exist in the constraint lines. Firebird is correctly stripping these extra backticks, but oci8 is leaving them in place. I think that we need to modify the CONSTRAINT handler in adodb-datadict.inc.php lines 583 onwards, and add a section for oci8 and oci8po which strips the backticks. Since I don't run oracle I can't test that, but the error message is caused by the double quotes being added to replace backtick.

Alternatively as a quicker fix, just replace the $this->connection->nameQuote by '' ( empty string ) and things should be OK for you. ONCE the installer has finished this is forced to an empty string, but it's not set for oracle until after the database has loaded :(
Lester Caine
Joined: 24 Apr 2004

Re: Oracle OCI8 issues

Posted:29 Jul 2008 (05:22 UTC)
This has been fixed in HEAD.
A tidy up of the database selection code had lost the oracle default to CaseSensitive=FALSE, this has been corrected.
In addition there is ONE use of SUBSTRING in the boards code, which has been extended to include the SUBSTR option for Oracle.
ADOdb does provide a variable - $substr, which we should be using in place of the hard coding, but Firebird already uses the SQL2003 standard which modifies the contents of the SUBSTRING function, so ADOdb really needs extending to provide a function returning the correct format package as well.
Since I don't actually HAVE Oracle - these tweaks have not been tested, but they should at least allow bitweaver to install.
Having reviewed the thread I not that there is also a difficulty with xxx.* - Firebird complains if the 'xxx.' is MISSING when several tables are combined, but this is related to unmatched fields between the tables so if you have a.data and b.data you have an ambiguous column using * or a*, b* so personally I prefer to fully identify the fields - which may be required in these cases.
Craig Sprout
Joined: 22 Oct 2007

Re: Oracle OCI8 issues

Posted:29 Jul 2008 (12:54 UTC)
I can verify that the problem with Oracle and ambiguous columns persists. I just downloaded and installed the HEAD revision.

The install will give you a bunch of errors about indices not being created, but those are because they already exist.

Also, any SQL statement that has something like "SELECT lc.*, br.text .. " will cause Oracle to throw the Ambiguous column error.

I haven't had the time to go through and enumerate exactly what needs to be selected, so I expanded the "SELECT lc.*" to lc.xxx, lc.xxy, lc.xxz for all the columns in the table.

If you look back through the archives, I submitted some fixes for various modules that will cure this. (http://www.bitweaver.org/forums/index.php?t=8849). I doubt that a straight "patch" will work, but this might get you going in the right direction.

I offered last year to help with the Oracle stuff, but got no response. I'll make the offer again, since we would love to start using this internally with our IT staff, but this broken Oracle support is making it a hard sell.

I have 9i and 10g databases to test on. (Also, if you didn't know, Oracle does have a free version of 10g that you can download for yourself.)

Let me know if I can help.
Lester Caine
Joined: 24 Apr 2004

Re: Oracle OCI8 issues

Posted:29 Jul 2008 (18:19 UTC)
http://www.bitweaver.org/wiki/ConnectingToIrc
Just drop in although things can be quiet - we tend to stay on line and pick up messages later so just post and hang around.
I thought I was being difficult with the Firebird differences, but it does look as if Oracle is even more obstructive :)
I Know Christian HAD Oracle running but that was a little while ago, and these problems were not present then, so is there some mode setting that my relax things on Oracle?
Craig Sprout
Joined: 22 Oct 2007

Re: Oracle OCI8 issues

Posted:29 Jul 2008 (20:27 UTC)
As much as I would like to get on IRC, it is blocked from work, and (no offense) I just don't want to do it at home. :)

Maybe one of these nights, I'll give it a shot.
  Page 1 of 2 »1 2