Re: MMBase 1.7.1 and Oracle 9

[Marcin Robak]

Hello Nico, Thank you very much. My instaltion of MMBase with Oracle is working now :). I've struglled with Java code of StorageFactory all last week :(. I use oracle.jdbc.driver.OracleDriver naturally. Although MMBase works there are a few porblems. For exaple if you try to install My News example application there is Oracle's error: ORA-01400: cannot insert NULL into ("MMUSER"."MM_OALIAS"."DESCRIPTION"). If you manually alter this field (it is not nulable but it should be) everythink work OK. Similar error is in My Yahoo application: ORA-01400: cannot insert NULL into ("MMUSER"."MM_URLS_TABLE"."M_NAME"). The better way then change this tables manually is change XML builder files. For first error in file config\builders\core\oaliases you sholud change atribut "notnull = false" in the tag which describes filed "Description". It makes an question: Why do another databases (e.g. MySQL) work fine without these changes? Once again thanks for help Marcin Robak Nico Klasens wrote: Hello Marcin Robak, The website I am working on uses oracle 9 and MMBase 1.7 too. My guess is that you are using the storagemanagerfactory in the mmbaseroot.xml. This one does not support oracle. And if it runs it does not use the strength of oracle (eg. Views). Look at the log files and you will probably see that the creation of tables failed. We use the old database layer which has a oracle-with-views implementation which works after fixing some bugs :-). I didn't take the time yet to commit it back to the mmbase sources, because there are some hardcoded schemas in the source. See the attached file Sql92WithViews.java. I don't know if storing of blobs is working in this implementation. We don't store attachments and images in the database. Clobs are working fine with large string fields. I made another change in the file org.mmbase.module.core.MMObjectBuilder. The constant MAX_QUERY_SIZE is set to 20000, but oracle 9 has a limit of 1000. The mmbaseroot.xml should have a commented out storagemanagerfactory and a line like <property name="database">oracle-with-views</property> Another thing you have to fix manually are the delete contraints on the tables otherwise you will get unused records in your tables. ALTER TABLE <prefix>_<type> add CONSTRAINT fk_<prefix>_<type> FOREIGN KEY (m_number) REFERENCES <prefix>_object(m_number) ON DELETE CASCADE; Add indexes to the important fields CREATE INDEX idx_object_number ON <prefix>_insrel(snumber); CREATE INDEX idx_object_number ON <prefix>_insrel(dnumber); And run cost-based statistics in production to boost performance. If you want more accurate help please post some parts of the log files then we can help you out much better. Nico PS The property 'driver' is not in your email. I assume you did change this to oracle.jdbc.driver.OracleDriver -----Oorspronkelijk bericht----- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Namens [EMAIL PROTECTED] Verzonden: woensdag 3 november 2004 14:42 Aan: [EMAIL PROTECTED] Onderwerp: MMBase 1.7.1 and Oracle 9 Hi all I am strongly interested in trying MMBase 1.7.1 with Oracle. Mr Pierre van Rooden has written that he would might explain how to set it up. I have followed all steps of database configuration. It means that I have done: 1. edition of jdbs.xml: <property name="url">jdbc:oracle:thin:@$HOST:$PORT:$DBM</property> <property name="user">mmuser</property> <property name="password">mmpass</property> <property name="supportclass"> org.mmbase.module.database.DatabaseSupportShim</property> <property name="database">mmbase01</property> <property name="connections">20</property> <property name="host">test04<!--localhost--></property> <property name="port">1521</property> <property name="queries">256</property> <property name="probetime">30</property> 2. creating database on Oracle on host test04, database mmbase01, user mmuser, passwors mmpass 3. copying Oracle JDBC driver for JDK 1.4.1 I use Tomcat 5.0 application server, MMBase 1.7.1 and Oracle 9. AN unviseral database viewer (DbVisualizer) connects to this database and everything seems to be OK but MMBase doesn't work. It shows error: "The server encountered an internal error () that prevented it from fulfilling this request." All help is more than welcome. Marcin Robak <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE database PUBLIC "-//MMBase/DTD database config 1.2//EN" "http://www.mmbase.org/dtd/database_1_2.dtd"> <database> <!-- <name> The Name of the Database we are running, only used for display --> <name>Oracle with usage of views</name> <!-- <mmbasedriver> Defines the database driver that handles the abstraction between mmbase and the given jdbs/database we are using. --> <mmbasedriver>org.mmbase.module.database.support.Sql92WithViews</mmbasedriver> <!-- <sqlhandler> Defines the SqlHandler that converts SearchQuery objects to sql statements. --> <sqlhandler>org.mmbase.storage.search.implementation.database.BasicSqlHandler</sqlhandler> <!-- <chainedsqlhandler> Defines the chained SqlHandler that extend the sqlhandler's functionality. This enables database extensions to be used. --> <chainedsqlhandler></chainedsqlhandler> <!-- <scheme> Defines the strings for the different scheme database statements. --> <scheme> <create>create table</create> <not-null>not null</not-null> <null>null</null> <primary-key>primary key</primary-key> </scheme> <!-- <mapping> defines how mmbase should map the different types mmbase to this database. mmbase-type, the internal mmbase datatypes allways in caps min-size, minimal size this value is valid for max-size, maximum size this value is valid for --> <mapping> <type-mapping mmbase-type="DOUBLE">double</type-mapping> <type-mapping mmbase-type="LONG">long</type-mapping> <type-mapping mmbase-type="FLOAT">float</type-mapping> <type-mapping mmbase-type="INTEGER">int</type-mapping> <type-mapping mmbase-type="NODE">int</type-mapping> <type-mapping mmbase-type="STRING" min-size="1" max-size="4000">varchar2(size)</type-mapping> <type-mapping mmbase-type="STRING" min-size="4001" max-size="16777216">clob</type-mapping> <type-mapping mmbase-type="XML" min-size="1" max-size="4000">varchar2(size)</type-mapping> <type-mapping mmbase-type="XML" min-size="4001" max-size="16777216">clob</type-mapping> <type-mapping mmbase-type="BYTE" min-size="1" max-size="16777216">blob</type-mapping> </mapping> <!-- <disallowed> defines disallowed fields, tables etc etc for this database if possible it also explains to mmbase what todo with it. illegal fieldnames for example will be automapped to a replacement if one is defined. This way apps made for mmbase don't need to be changes when running on a different database. --> <disallowed> <field name="body" replacement="m_body" /> <field name="comment" replacement="m_comment" /> <field name="column" replacement="m_column" /> <field name="columns" replacement="m_columns" /> <field name="constraints" replacement="m_constraints" /> <field name="date" replacement="m_date" /> <field name="data" replacement="m_data" /> <field name="day" replacement="m_day" /> <field name="from" replacement="m_from" /> <field name="global" replacement="m_global" /> <field name="group" replacement="m_group" /> <field name="hosts" replacement="m_hosts" /> <field name="hour" replacement="m_hour" /> <field name="key" replacement="m_key" /> <field name="keys" replacement="m_keys" /> <field name="level" replacement="m_level" /> <field name="local" replacement="m_local" /> <field name="month" replacement="m_month" /> <field name="name" replacement="m_name" /> <field name="number" replacement="m_number" /> <field name="orderby" replacement="m_orderby" /> <field name="open" replacement="m_open" /> <field name="parent" replacement="m_parent" /> <field name="role" replacement="m_role" /> <field name="row" replacement="m_row" /> <field name="rows" replacement="m_rows" /> <field name="second" replacement="m_second" /> <field name="session" replacement="m_session" /> <field name="size" replacement="m_size" /> <field name="start" replacement="m_start" /> <field name="status" replacement="m_status" /> <field name="stop" replacement="m_stop" /> <field name="time" replacement="m_time" /> <field name="table" replacement="m_table" /> <field name="to" replacement="m_to" /> <field name="type" replacement="m_type" /> <field name="use" replacement="m_use" /> <field name="view" replacement="m_view" /> <field name="year" replacement="m_year" /> </disallowed> </database>