QOpenOCCI Oracle SQL Plugin

[a_white]

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]

Yes I am really interested by this project

[a_white]

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]

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]

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]

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]

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++;
}

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++;
}

To copy to clipboard, switch view to plain text mode 

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]

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()));

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()));

To copy to clipboard, switch view to plain text mode 

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]

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()));

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()));

To copy to clipboard, switch view to plain text mode 

[deivisj]

thanks a_white.
My mistake .
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()));

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()));

To copy to clipboard, switch view to plain text mode 

[deivisj]

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();

query->finish();

To copy to clipboard, switch view to plain text mode 

but witch way is more correct.

thanks

[a_white]

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]

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]

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]

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]

Ye, you are right. there is function. Sorry for stupid question.

[foggy-mind]

Hy a_white,
One more question. Does QOpenOCCI plug'in support row count??? By QT documentation it returns with function query->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();

Model = new QModel(this);
	Model->setQuery(query);
	tmp = Model->rowCount();

To copy to clipboard, switch view to plain text mode 

)

tmp = 255 in fact rows is 419

Any suggestions...
thanks.

[spirit]

btw, Qt 4.5.0 opensource already contains OCI driver.

[foggy-mind]

btw, Qt 4.5.0 opensource already contains OCI driver.

very good news

[foggy-mind]

I find the answer why QSqlQueryModel function count() returns not the real count of rows. The answer is there QSqlQueryModel::fetchMore()

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