PDA

View Full Version : QOpenOCCI Oracle SQL Plugin



a_white
20th February 2008, 20:53
Hello, I am looking for anyone how may be interested in testing/trying/breaking a Qt SQL plugin for Oracle. This project is new so any feedback I get is good feedback.

From the project webpage...

"QOpenOCCI is a Qt SQL Plugin for Oracle released under the LPGL that can be used by all editions of Qt (Free/Open/Commercial). QOpenOCCI will strive to be a free, production ready, alternative to any similar proprietary solution. QOpenOCCI is built using Oracle's C++ OCCI libraries (1.11.0) and Trolltech's Qt (4.3.3)."

The project is hosted at https://sourceforge.net/projects/qopenocci/.

Thanks, a_white

jjay
21st February 2008, 11:44
Yes I am really interested by this project :)

a_white
22nd February 2008, 17:36
Good to hear. Anyone wishing to contact me directly about this project, can use the contact info at https://sourceforge.net/users/a_white/ or the mailing lists listed on the Sourceforge project page.

Right now I just need people trying it out and finding any missing key features. Though developers are always welcomed ;)

GreyGeek
26th February 2008, 15:53
I am curious as to how you see your project fitting in.

Oracle offers the freely available InstantClient for both Linux and XP and it works against their commercial database and their free "Express Edition".

Are you adding features that InstantClient doesn't have?

a_white
29th February 2008, 14:43
Sorry for the delay in posting, work has been crazy.

This project is intended to act like any other SQL plug-in for Qt, that it should provide a Qt standard interface to Oracle's DB. I actually use the Instant Client libraries to build this interface.

The motivation for this project is the lack of a free plug-in for Qt. Trolltech offers similar functionality in their Enterprise Edition, but I see no reason why the Free/Open editions can't have the same tools.

const
29th September 2008, 11:26
Hello!

I have a problem with QOpenOCCI Oracle SQL Plugin.

I built plugin on my PowerBook G4 under Mac OS X Tiger 10.4.11 with Qt 4.4.1 Open Source and Oracle Instant Client 10.1. Then I put libqopenocci.dylib into Qt plugin directory.

Using the plugin I'm trying to connect to the Oracle database from the application:

QSqlDatabase db = QSqlDatabase::addDatabase("QOpenOCCI");
db.setHostName( "XXXXXXX" );
db.setDatabaseName( "XXXXXXX" );
db.setPort( 1521 );
db.setUserName( "XXXXXXX" );
db.setPassword( "XXXXXXX" );
db.open();

Debugger gives me an error:

terminate called after throwing an instance of '
QOpenOCCIException'
what(): Error QOpenOCCIDriver.cpp[35]: Error: failed to initialize OCI Environment

Help me please!

a_white
29th September 2008, 14:47
The offending code is the following...



QOpenOCCIDriver::QOpenOCCIDriver() :
QSqlDriver(),
mConnection(NULL)
{
if (QOpenOCCIDriver::driverCount == 0) {
if (!OCI_Initialize(QOpenOCCIDriver::ErrHandle, NULL, OCI_ENV_DEFAULT))
throw QOPEN_OCCI_EXCEPTION("Error: failed to initialize OCI Environment");
}
QOpenOCCIDriver::driverCount++;
}

Specifically, the OCI_Initialize function is dieing which is actually part of the OCILIB project http://orclib.sourceforge.net/. QOpenOCCI doesn't actually use OCCI anymore due to limitation in Oracle's implementation so it now uses a C OCI wrapper which is OCILIB.

If I had to guess, the C OCI libraries where not linked at compile time and OCILIB is looking for them and failing. In sort, make sure the Oracle OCI libs are accessible at runtime by copying them to a system wide directory or setting an environment var and see if that fixes your problem.

QOpenOCCI does have forums and mailing lists for just these types of question though at https://sourceforge.net/projects/qopenocci/

deivisj
15th October 2008, 15:28
hi,
a_white help me...
plugin version - COpenOCCI 1.3.1a
OS - WinWP
QT 4.4.3 opensource with MinGw 5.1.4
Oracle - 10g

connection to Oracle and query executing work fine. but I can't call a procedure.

example:

QSqlQuery procedure(DB);
procedure.prepare("call shemename.packagename.procedurename(?,?)");
procedure.bindValue("parameter1", "parameter2");
if (!procedure.exec())
QMessageBox::information(this,tr("error"),tr("%1").arg(procedure.lastError().databaseText()));

I got error:
Error QOpenOCCIDriver.cpp[48]: (00911) ORA-00911: invalid character

What I done wrong?
Does QOpenOCCI plugin support procedure's executing???

a_white
15th October 2008, 19:20
Look in src/tests for exactly how this is tested. Basically, your code should look like this...



QSqlQuery procedure(db);
procedure.prepare( "BEGIN proc_name(:0, :1); END;");
procedure.bindValue(0, x);
procedure.bindValue(1, y);
if (procedure.exec() == FALSE)
QMessageBox::information(this,tr("error"),tr("%1").arg(procedure.lastError().databaseText()));

deivisj
16th October 2008, 07:13
thanks a_white.
My mistake :rolleyes:.
The main error was with bindValue. Your example and QT example how to call procedure works fine. This example correct to...


QSqlQuery procedure(DB);
procedure.prepare("call shemename.packagename.procedurename(:0,:1)");
procedure.bindValue(0, "parameter1");
procedure.bindValue(1, "parameter2");
if (!procedure.exec())
QMessageBox::information(this,tr("error"),tr("%1").arg(procedure.lastError().databaseText()));

deivisj
29th October 2008, 07:56
hi, I have question. how to commit DB correctly?
do I have to do a query not active for commit? commit work without
query->finish();but witch way is more correct. :)

thanks

a_white
14th November 2008, 22:16
Don't know how I missed your reply, but qDB->commit() is the best way to commit a transaction.

As an added advertisement, QOpenOCCI 2.0 will be released soon and will have many improvements :)

foggy-mind
22nd December 2008, 10:40
Hi,
good thing this plug-in and I'm waiting for QOpenOCCI 2.0 release.

And want to ask one question.
In "QSqlQuery Class Reference" says that "Stored procedures that uses the return statement to return values, or return multiple result sets, are not fully supported."

Does QOpenOCCI 1.3.1.a support values return???


thanks.

a_white
22nd December 2008, 17:17
First, QOpenOCCI is limited to what the OCI and Qt lib support. Saying that, I think you can do what you ask. Second, in the Oracle world, stored procedures never return values and functions always returns values. Small correction but people get them easily confused and we can end up talking about the wrong thing. Now to do what you want you have two cases

1) See the stored procedure example I posted earlier in this thread. This deals with non-returning calls (unless you do out binds which is allowed too)

2) Formulate your sql to like "select function_to_call(value) from dual" and then treat that as a normal sql call.

QOpenOCCI 2.0 should be out soon (yeah I know I said that a while ago) but I am waiting for OCILIB to lock in it's release. I need to make sure that two libs get along before making 2.0 official. If anyone is interested in a pre-release that can contact via souceforge and I will work something out.

a_white
22nd December 2008, 20:48
Let me clarify something. QOpenOCCI does not support the Oracle "returning into" clause since the Qt lib does not have a good mechanism for that exchange. May be something for a future release though.

Next, array like structures are not currently supported in 1.x but will have basic support in 2.0. This will be limited to input bound variables or native non-complex types such as numbers and strings. I am currently investigating a way to tie in non-primitive Oracle types (varrays/nested-tables/objects) back though Qt.

foggy-mind
23rd December 2008, 07:24
Ye, you are right. there is function. Sorry for stupid question. ;)

foggy-mind
12th March 2009, 08:49
Hy a_white,
One more question. Does QOpenOCCI plug'in support row count??? By QT documentation it returns with function query->size() (http://doc.trolltech.com/4.4/qsqlquery.html#size), I get -1.

the size is not supported on driver.
qDB.driver()->hasFeature(QSqlDriver::QuerySize); returns 0.

I don't want to use while(query->next()) tmp++; to get row count.
QSQlQueryModel returns not the right count, and I don't know why (
Model = new QModel(this);
Model->setQuery(query);
tmp = Model->rowCount();)

tmp = 255 in fact rows is 419

Any suggestions...
thanks.

spirit
12th March 2009, 09:00
btw, Qt 4.5.0 opensource already contains OCI driver. ;)

foggy-mind
12th March 2009, 09:17
btw, Qt 4.5.0 opensource already contains OCI driver. ;)

very good news :)

foggy-mind
13th March 2009, 09:39
I find the answer why QSqlQueryModel function count() returns not the real count of rows. The answer is there QSqlQueryModel::fetchMore() (http://doc.trolltech.com/4.4/qsqlquerymodel.html#fetchMore)

And QOCI plugin don't report back the size of a query too :(.