PDA

View Full Version : Database stored funtions calling



gbaguma
4th July 2013, 13:01
Hey guys,
Am writing a database application to interact with an Oracle database. In the Oracle database i have stored procedures and functions. Problem is i cannot retrieve data or even call functions. stored procedures work just fine. The QT documentation says that functions are not fully supported, and instead advises to see the driver documentation. the thing is,..i think functions are supported in the OCI driver. How does someone go about this?
Below is a code fragment from one of my functions

qint32 historyid;
QSqlQuery query;
query.setForwardOnly(true);
query.prepare(" call :histid := tracdba.proj_pkg.change_project(:rojid,:ver,:reaso n)");
query.bindValue(":rojid", versioninfo.ProjectID );
query.bindValue(":ver",versioninfo.projectVersion );
query.bindValue(":reason", versioninfo.Reason);

query.bindValue(":histid",0, QSql::Out);
if(!query.exec())
{
qDebug()<< "DATABASE ERROR!!"<< query.lastError().databaseText();
return "db_Exec_Failed";
}
historyid = query.boundValue(":histid").toInt();

Lesiok
4th July 2013, 14:04
I have not had to deal with Oracle but I think you can just :

qint32 historyid;
QSqlQuery query;
query.setForwardOnly(true);
query.prepare(" SELECT tracdba.proj_pkg.change_project(:rojid,:ver,:reaso n)");
query.bindValue(":rojid", versioninfo.ProjectID );
query.bindValue(":ver",versioninfo.projectVersion );
query.bindValue(":reason", versioninfo.Reason);

if(!query.exec())
{
qDebug()<< "DATABASE ERROR!!"<< query.lastError().databaseText();
return "db_Exec_Failed";
}
if( !query.next() )
return "some_error_code";

historyid = query.record().value(0).toInt();

gbaguma
4th July 2013, 14:46
Thanx so much,..but the thing is where would the returned value go. in this event the DBMS just returns missing expression from.

Added after 26 minutes:

hey guys,.am not even really sure that this feature is actually supported,...imean what the qt guys saythat the return feature for database functions is not fully supported and they refer the reader to the driver documentation. the thing is even in the driver documentation,...they just write a whole load of stuff on installing and thats it. oracle seems to document however that both support for stored procedures and functions is built into the OCI

ChrisW67
4th July 2013, 23:06
In Lesiok's approach the result should come back in the first column of the first (only) row of results. You may need to make the query:

SELECT ... FROM dual
In order to get a row rather than an error returned by Oracle.

gbaguma
5th July 2013, 09:43
Thanks very much there ChrisW67, but it instead gives me an uptodate comflict: This project is already up to date

Added after 19 minutes:

i think this is because the function am trying to execute is actually modifying data in the database,..is there anyother way of doing this?

Added after 1 3 minutes:

hey ChrisW67,..i think that its probably an error being returned by the function itself,..but just an inquiry,..does this particular type of call allow u to write, modify
as well as read from the database

gbaguma
5th July 2013, 10:26
i do think now that u cannot perform DML in select statements,..so this function call cannot work. Oracle complains. the sad thing however is that even for the most trivial calls ,
oracle says something like espression is of the wrong type.

gbaguma
10th July 2013, 14:06
Hey guys am still battling with calling oracle functions in QT,..is there a way of doing this at all in QT.

seneca
11th July 2013, 01:40
Please try this:


query.prepare("BEGIN tracdba.proj_pkg.change_project(:rojid,:ver,:reaso n) END;");

gbaguma
11th July 2013, 11:12
Thanx very much,..your last post went a long way to help me solve this. For a function call in QT using the oracle driver, this is what you have to do


query.prepare("BEGIN :aliase := schema_name.pkg_name.function_name(:aliase1); END;");


And then ofcourse go ahead and apply the bindings as would be.