Oracle support for DatabaseLayer

[jb_coder]

I'm trying to get back to adding Oracle support in DatabaseLayer and am coming across 1 big issue. I was able to get all the unit tests passing except the ones with Blobs. It turns out that the OCCI (C++ API) interface uses a 2-step method for adding Blob parameters to the database. Does anyone know if the OCI (C API) is more friendly towards 1-step Blob creation? Before redoing the entire Oracle backend code, I'd like to get a 2nd opinion.

Thanks!

P.S. - After Oracle, I'll try to start on FreeTDS for Sybase/MS SQL Server support.

[medienluemmel]

If you are still interested in getting an answer how to work with BLOBs in Oracle with OCCI, please leave a short messae here. Working with BLOBS cost me hours, but now I seem to have found a way in managing it - so maybe I can help you with your problems.

[jb_coder]

Thanks for the offer, but someone rewrote the Oracle code to use the OTL interface. I'm not sure at this point if Blobs work with the OTL code, but apparently OCCI is very Oracle version dependent so might have been limiting users ability to connect to a variety of different Oracle servers by going with the OCCI route in the first place.

[jb_coder]

Looking more at the Oracle code lately, I'm curious... will your solution work with the OCI interface as well as the OCCI interface?

Thanks!

[medienluemmel]

Looking more at the Oracle code lately, I'm curious... will your solution work with the OCI interface as well as the OCCI interface?

I am awfully sorry, but I don't know the OCI interface at all.

[jb_coder]

Could you explain your solution with the OCCI interface? The interfaces are similar enough that it may still be helpful.

Thanks!

[medienluemmel]

Could you explain your solution with the OCCI interface? The interfaces are similar enough that it may still be helpful.

Let's start:

We have have got

Code: Select all

	Environment *oenv;
	Connection *ocon;
	Statement *ostm;

And somewhere in the application we have set:

Code: Select all

		ostm->setAutoCommit (true);

Now we will read a Clob-value

Code: Select all

	wxString s_sql = "SELECT value FROM table WHERE ...";

So we write a new function

Code: Select all

wxString GetClobValue (wxString s_sql)
{
	wxString s_retval = "";
	ResultSet *rs = ostm->executeQuery (s_sql.c_str ());
	if (rs->next ())
	{
		Clob cl = rs->getClob (1);
		if (cl.isNull ())
		{
			ostm->closeResultSet (rs);
			return "";
		}
		cl.open (OCCI_LOB_READONLY);
		unsigned int cloblen = cl.length ();
		if (cloblen > 0)
		{
			char *buffer = new char[cloblen * 4]; // * 4 is for Unicode-characters, else you'll get runtime errors
			unsigned int chars = cl.read (cloblen, (unsigned char*) buffer, cloblen * 4); // * 4 is for Unicode-characters, else you'll get runtime errors
			s_retval = buffer;
			s_retval = s_retval.Left (chars) + "\0";
			#ifndef __WIN32__
			buffer = NULL; // this is for Linux environment, else you may get errors, don't know why
			#endif
			delete (buffer);
		}
		cl.close ();
	}
	ostm->closeResultSet (rs);
	return s_retval;
	// here I normally use a self-written GetSqlText-Function to handle apostrophes
}

Now we will write a Clob-value

Code: Select all

bool SetClobValue (wxString s_table, wxString s_where, wxString s_field, wxString s_contval)
{
	wxString s_content = s_contval;
	// here I normally use a self-written SetSqlText-Function to handle apostrophes
	wxString s_sql = "";
	s_sql = "UPDATE " + s_table + " SET " + s_field + " = ' ' WHERE " + s_where;
	ostm->executeUpdate (s_sql.c_str ());
	ostm->setAutoCommit (false); // you mustn't forget this
	s_sql = "SELECT " + s_field + " FROM " + s_table + " WHERE " + s_where + " FOR UPDATE";
	ResultSet *rs = ostm->executeQuery (s_sql.c_str ());
	if (rs->next ())
	{
		Clob cl = rs->getClob (1);
		cl.open (OCCI_LOB_READWRITE);
		unsigned int cloblen = s_content.Length ();
		unsigned char *buffer = new unsigned char[cloblen];
		buffer = (unsigned char*) (s_content.c_str ());
		cl.writeChunk (cloblen, buffer, cloblen, 1);
		cl.close ();
		ocon->commit ();
		#ifndef __WIN32__
		buffer = NULL; // this is for Linux environment, else you may get errors, don't know why
		#endif
		delete (buffer);
	}
	else
	{
		wxString s_mess = "Data could not be updated";
		ErrorMess (s_mess);
		ostm->closeResultSet (rs);
		ostm->setAutoCommit (true);
		return false;
	}
	ostm->closeResultSet (rs);
	ostm->setAutoCommit (true);
	return true;
}

With this code you can only add Clob-values to existing records/rows.

May it be helpful!