db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Torsten Schlabach <TSchlab...@gmx.net>
Subject Re: AW: Oracle 9i BLOB malfunction / 4k mystery (issue OJB170)
Date Fri, 06 Jun 2003 06:23:25 GMT
Looks like the attachments were thrown out somehow.
Let's try inline:

*** OracleClobHelper.java ***

/*
 * Created on 28.05.2003
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package de.detemobil.eam.util;

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

//needed for new CLOB classes
import oracle.sql.*;

import org.apache.ojb.broker.PersistenceBroker;
import org.apache.ojb.broker.metadata.ClassDescriptor;
import org.apache.ojb.broker.metadata.MetadataManager;
import org.apache.ojb.broker.metadata.DescriptorRepository;
import org.apache.ojb.broker.metadata.FieldDescriptor;

/**
 * @author schlabac
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */

public class OracleClobHelper {


	public static void store(Object objToStore,PersistenceBroker broker) {

		String fullTableName;
		
		try {
			
			Connection jdbcConnection =
broker.serviceConnectionManager().getConnection();

			/*
			 * This routine needs auto-commuit set to false. Therefore we save the
state of
			 * it here and set it back after we're done. Not sure if this is a good
thing
			 * to do, but it works for now.
			 */			
			boolean savedAutoCommitState = jdbcConnection.getAutoCommit();
			jdbcConnection.setAutoCommit(false);
			

			/*
			 * Retrieve Metadata
			 */

			DescriptorRepository repository =
MetadataManager.getInstance().getRepository();
			ClassDescriptor classDescriptor =
repository.getDescriptorFor(objToStore.getClass());

			fullTableName = classDescriptor.getFullTableName();

			FieldDescriptor[] fields = classDescriptor.getFieldDescriptions();
			for(int i=0;i<fields.length;++i) {
				if(fields[i].getColumnJdbcType() ==
Types.CLOB)
{
					storeClobField(objToStore,broker,fullTableName,fields[i],classDescriptor.getPrimaryKey(),jdbcConnection);
				
				}
			}

			jdbcConnection.setAutoCommit(savedAutoCommitState);

		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	
	public static void storeClobField(Object objToStore,PersistenceBroker
broker,
		String fullTableName,FieldDescriptor field, FieldDescriptor pkField,
Connection jdbcConnection) {
			
			
			try {
				String key = (String)pkField.getPersistentField().get(objToStore);
				String xlobFieldvalue =
(String)field.getPersistentField().get(objToStore);
				
				Statement stmt = jdbcConnection.createStatement ();
				CLOB clob = null;
	
				/*
				 * A xLOB field can be "null". If this is the case we cannot write to
it. So
				 * we update the record with an empty xLOb to make sure we can write to
it later.
				 */
				String cmd = "update "+fullTableName+
					" set "+field.getColumnName()+"=empty_clob() where
"+pkField.getColumnName()+"='"
					+key+"'";
				stmt.execute(cmd);
				stmt.execute("commit");
	
				/*
				 * Next we select the row for update and leave it in this state until
we've written
				 * the xLOB. (Hint: This is why we cannot live on auto-commit!)
				 */
				cmd = "select * from "+fullTableName+" where
"+pkField.getColumnName()+"='"+key+"' for update";
				ResultSet rset = stmt.executeQuery(cmd);
	
				while (rset.next()) {
					
					clob = ((OracleResultSet)rset).getCLOB(5);
	
					Writer out = clob.getCharacterOutputStream();
					out.write(xlobFieldvalue);
					out.close();
				}
				
				stmt.execute("commit");
		} catch (Exception e) {
			// FIXME: Handle Exception
		}
	}
	
	public static void populate(Object objToPopulate,PersistenceBroker broker)
{
		try {
			System.out.println("Dummy");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

*** String2ClobFieldConversion.java ***

/*
 * String2ClobFieldConversion.java
 *
 * Created on 26. März 2003, 14:02
 */

package de.detemobil.eam.util;

import org.apache.ojb.broker.accesslayer.conversions.FieldConversion;

import de.detemobil.eam.StrutsDebug;

/**
 *
 * @author  schlabac
 */
public class String2ClobFieldConversion implements FieldConversion {
    
    /** Creates a new instance of String2ClobFieldConversion */
    public String2ClobFieldConversion() {
    }
    
        /**
     * @see FieldConversion#javaToSql(Object)
     */
    public Object javaToSql(Object source)
    {
        if (source instanceof String)
        {
            //StrutsDebug.print(this,"->javaToSql: Convertiere: "+source);
            //return ((String)source).toCharArray();
            return null;
        }
        else
        {
            StrutsDebug.print(this,"->javaToSql: Keine Konvertierung
erforderlich für: "+source);
            return source;
        }
    }

    /**
     * @see FieldConversion#sqlToJava(Object)
     */
    public Object sqlToJava(Object source)
    {
		//StrutsDebug.print(this,source.getClass().toString());
		
        if (source instanceof java.sql.Clob)
        {
            StrutsDebug.print(this,"->sqlToJava: Convertiere: "+source);
            return "";
        }
        else
        {
            //StrutsDebug.print(this,"->sqlToJava: Keine Konvertierung
erforderlich für: "+source);
            return source;
        }
    }
    
}

****

Regards,
Torsten

> Hi Torsten,
> 
> sounds great, where is the code?
> 
> cheers,
> Thomas
> 
> Torsten Schlabach wrote:
> > Dear List,
> > 
> > I managed to implement the OracleClobHelper workaround that we discussed
> > on the list. The code might have a lot of flaws. But it works for me and
> > therefore I thought it might be worth sharing. I am not sure if this
> > could become part of the CVS after some beautifying.
> > 
> > Some remarks:
> > 
> > - It appears that opposed to writing CLOB objects to the database there
> > is no special handling needed when reading them.
> > - When using this helper, first store the object to the database like
> > that:
> > 
> > broker.beginTransation();
> > broker.store(object);
> > broker.commitTransaction();
> > 
> > Then (after the commit!) call
> > 
> > OracleClobHelper.storeClobs(object,broker);
> > 
> > This is because the helper will use a SELECT FOR UPDATE statement to
> > write the CLOB, therefore the row needs to be in the database and
> > committed in order for the SELECT FOR UPDATE to find it.
> > 
> > - You need to declare the fields as JDBC type CLOB. If your object's
> > attribute is a String, you will also need the
> > String2ClobFieldConversion.
> > 
> > Is anyone working on the *real* solution?
> > 
> > I will find the time to complete the "Howto use CLOBs" with the latest
> > findings soon hopefully.
> > 
> > Regards,
> > Torsten
> > 
> > -----Ursprüngliche Nachricht-----
> > Von: Lance Eason [mailto:lance.eason@whisperwire.com] 
> > Gesendet: Mittwoch, 21. Mai 2003 14:50
> > An: OJB Developers List; tschlabach@gmx.net
> > Betreff: RE: Oracle 9i BLOB malfunction / 4k mystery (issue OJB170)
> > 
> > PersistenceBroker.serviceConnectionManager().getConnection() gives you
> > back the underlying JDBC connection from which you can do anything.
> > 
> > -----Original Message-----
> > From: 1075244@imap.gmx.net [mailto:1075244@imap.gmx.net]
> > Sent: Tuesday, May 20, 2003 3:54 AM
> > To: ojb-dev@db.apache.org
> > Subject: RE: Oracle 9i BLOB malfunction / 4k mystery (issue OJB170)
> > 
> > 
> > 
> >>>I realized after I wrote the note that OJB let's you access the JDBC
> > 
> > layer so you can work around it yourself.
> > 
> > How would that work? I did not find a way to navigate from the
> > PersistenceBroker to any JDBC connection. There is a serviceJDBC but
> > this
> > one still is an encapulation of the JDBC level which does not let you
> > fire
> > arbitrary statements to the database.
> > 
> > Torsten
> > 
> > 
> > Original Message:
> > -----------------
> > From: Lance Eason lance.eason@whisperwire.com
> > Date: Thu, 15 May 2003 16:17:35 -0500
> > To: ojb-dev@db.apache.org, thma@apache.org
> > Subject: RE: Oracle 9i BLOB malfunction / 4k mystery (issue OJB170)
> > 
> > 
> > Yeah, you're right.  I realized after I wrote the note that OJB let's
> > you
> > access the JDBC layer so you can work around it yourself.  As one of the
> > people burned by this problem I'd actually be perfectly happy if core
> > OJB
> > wasn't modified but the problem was clearly documented and sample
> > workaround code was provided and ideally some patch utility class was
> > provided to do the repetitive work of doing the SELECT FOR UPDATE and
> > writing out the contents.
> > 
> > -----Original Message-----
> > From: Thomas Mahler [mailto:thma32@web.de]
> > Sent: Thursday, May 15, 2003 2:15 PM
> > To: OJB Developers List
> > Subject: Re: Oracle 9i BLOB malfunction / 4k mystery (issue OJB170)
> > 
> > 
> > Hi angain
> > 
> > Lance Eason wrote:
> > 
> >>I'll answer the first question.  It is most definitely an Oracle bug.
> >>Regardless it is important that OJB address it in my opinion.  
> > 
> > 
> > +1
> > 
> > 
> >>Users
> >>using JDBC directly can work around this bug, users using OJB
> >>currently cannot.  
> > 
> > 
> > As it's pretty OK to use OJB to obtain JDBC connections you can use OJB 
> > *and* use direct JDBC calls to work around this problem.
> > 
> > 
> >>That creates a decision point when BLOB/CLOB data
> >>is required of use Oracle or use OJB and to most people the DBMS is
> >>going to be the higher priority.
> > 
> > 
> > don't agree, see above.
> > 
> > 
> >>And yes the OCI driver does not exhibit this bug but it is not always
> >>possible to use the OCI driver.  First it requires an Oracle client
> >>installation on each machine and second it is native code and at
> >>least for Oracle 8.1.7 is flaky (many, many SEGFAULTs in our recent
> >>load testing).
> > 
> > 
> > As I mentioned in my other mail, Oracle seems to have fixed the 
> > CLOB/BLOB problems with the thin driver in their latest release!
> > 
> > I think we should further investigate this before launching bug fix 
> > rampage. ;-)
> > 
> > cheers,
> > thomas
> > 
> > 
> >>-----Original Message----- From: Torsten Schlabach
> >>[mailto:TSchlabach@gmx.net] Sent: Thursday, May 15, 2003 6:29 AM To:
> >>ojb-dev@db.apache.org Subject: Re: Oracle 9i BLOB malfunction / 4k
> >>mystery (issue OJB170)
> >>
> >>
> >>Folks,
> >>
> >>if I get this right, we still don't have a *real* solution to this,
> >>do we? I found that Per-Olof's CLOB patch for using the thin driver
> >>made it to the CVS, but I understand it only fixes this for text < 4
> >>KB, right?
> >>
> >>So first of all I thought it was a good idea to enter an issue in the
> >>bug database at http://scarab.werken.com/scarab/issues/id/OJB170
> >>(which became OJB170).
> >>
> >>So to me there are at two questions right now:
> >>
> >>1. Is this an OJB bug, an Oracle bug or both? 2. How do we *want* do
> >>handle this at all?
> >>
> >>What I mean is: To what column type would I map a String object that
> >>I except to grow very large (i.e. some dozends KB of text)?
> >>
> >>I might map it to JDBC type CLOB which would be closest to reality
> >>but this will break with a class cast exception (you cannot cast a
> >>string to a java.sql.Clob).
> >>
> >>If you map it to anything else such as LONGVARCHAR Oracle will not
> >>care but this will probably break other things.
> >>
> >>In fact it might depend on your application what you want back in
> >>your bean when using a CLOB column: You either might want to get a
> >>stream you can read from in some other place or you might want to
> >>just get the stuff into a String and not care about it any more
> >>(which would make your application code much less Oracle specific by
> >>the way).
> >>
> >>This is essentially two different JDBC types needed for the same type
> >>of DB column. Does the framework support this at all? I am wrong in
> >>any assumption?
> >>
> >>Torsten
> >>
> >>P.S.: I would like to post this as a comment in Scarab, but I did not
> >>yet find out how to edit the issue. I was able to submit it though.
> >>Any help appreciated.
> >>
> >>Original Message: ----------------- From: Thomas Poeschmann
> >>t.poeschmann@exxcellent.de Date: Mon, 12 May 2003 16:05:08 +0200 To:
> >>ojb-dev@db.apache.org Subject: Re: Oracle 9i BLOB malfunction / 4k
> >>mystery
> >>
> >>
> >>Hi there,
> >>
> >>
> >>
> >>>Michael Mogley wrote: Thomas Poeschmann says on the list that he
> >>>almost has a solution, using the above method I presume.
> >>
> >>
> >>Yes, of course using the SELECT FOR UPDATE. Sorry for promising
> >>posting code but not doing it, but I will try to find it this
> >>evening. It is probably just for reference for you, since you already
> >>have it.
> >>
> >>
> >>
> >>>Are there other dbmses and drivers that exhibit the same irregular
> >>> behavior regarding LOB´s.
> >>
> >>
> >>Not that I know. Sometimes it is different to call one of the methods
> >>on a statement to bring certain Java objects in. For example passing
> >>an array in as a String. But I have never seen anything hard as
> >>Oracle XLOBs ;)
> >>
> >>
> >>
> >>>Unfortunatly, our solution (apart from the the fix submitted)
> >>>currently consists of changing to the oci driver. Sad but true.
> >>
> >>
> >>Which has other drawbacks, but well... Other ORM can not handle it
> >>either with thin, by the way ;)
> >>
> >>Kind regards,
> >>
> >>Thomas
> >>
> >>
> >>---------------------------------------------------------------------
> >> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org For
> >>additional commands, e-mail: ojb-dev-help@db.apache.org
> >>
> >>
> >>
> >>---------------------------------------------------------------------
> >> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org For
> >>additional commands, e-mail: ojb-dev-help@db.apache.org
> >>
> >>
> >>---------------------------------------------------------------------
> >> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org For
> >>additional commands, e-mail: ojb-dev-help@db.apache.org
> >>
> >>
> > 
> > 
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-dev-help@db.apache.org
> > 
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-dev-help@db.apache.org
> > 
> > 
> > 
> > --------------------------------------------------------------------
> > mail2web - Check your email from the web at
> > http://mail2web.com/ .
> > 
> > 
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-dev-help@db.apache.org
> > 
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-dev-help@db.apache.org
> > 
> > 
> > 
> > ------------------------------------------------------------------------
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 




Mime
View raw message