Results 1 to 9 of 9

Thread: Database stored funtions calling

  1. #1
    Join Date
    Jul 2013
    Posts
    8
    Thanks
    3
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Database stored funtions calling

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

  2. #2
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Database stored funtions calling

    I have not had to deal with Oracle but I think you can just :
    Qt Code:
    1. qint32 historyid;
    2. QSqlQuery query;
    3. query.setForwardOnly(true);
    4. query.prepare(" SELECT tracdba.proj_pkg.change_project(:rojid,:ver,:reaso n)");
    5. query.bindValue(":rojid", versioninfo.ProjectID );
    6. query.bindValue(":ver",versioninfo.projectVersion );
    7. query.bindValue(":reason", versioninfo.Reason);
    8.  
    9. if(!query.exec())
    10. {
    11. qDebug()<< "DATABASE ERROR!!"<< query.lastError().databaseText();
    12. return "db_Exec_Failed";
    13. }
    14. if( !query.next() )
    15. return "some_error_code";
    16.  
    17. historyid = query.record().value(0).toInt();
    To copy to clipboard, switch view to plain text mode 

  3. The following user says thank you to Lesiok for this useful post:

    gbaguma (11th July 2013)

  4. #3
    Join Date
    Jul 2013
    Posts
    8
    Thanks
    3
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Database stored funtions calling

    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
    Last edited by gbaguma; 4th July 2013 at 14:46.

  5. #4
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Database stored funtions calling

    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:
    Qt Code:
    1. SELECT ... FROM dual
    To copy to clipboard, switch view to plain text mode 
    In order to get a row rather than an error returned by Oracle.

  6. The following user says thank you to ChrisW67 for this useful post:

    gbaguma (11th July 2013)

  7. #5
    Join Date
    Jul 2013
    Posts
    8
    Thanks
    3
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Database stored funtions calling

    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
    Last edited by gbaguma; 5th July 2013 at 09:43.

  8. #6
    Join Date
    Jul 2013
    Posts
    8
    Thanks
    3
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Database stored funtions calling

    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.

  9. #7
    Join Date
    Jul 2013
    Posts
    8
    Thanks
    3
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Database stored funtions calling

    Hey guys am still battling with calling oracle functions in QT,..is there a way of doing this at all in QT.

  10. #8
    Join Date
    Jan 2006
    Posts
    132
    Thanked 16 Times in 16 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Database stored funtions calling

    Please try this:

    Qt Code:
    1. query.prepare("BEGIN tracdba.proj_pkg.change_project(:rojid,:ver,:reason) END;");
    To copy to clipboard, switch view to plain text mode 

  11. The following user says thank you to seneca for this useful post:

    gbaguma (11th July 2013)

  12. #9
    Join Date
    Jul 2013
    Posts
    8
    Thanks
    3
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Database stored funtions calling

    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
    Qt Code:
    1. query.prepare("BEGIN :aliase := schema_name.pkg_name.function_name(:aliase1); END;");
    To copy to clipboard, switch view to plain text mode 

    And then ofcourse go ahead and apply the bindings as would be.
    Last edited by gbaguma; 11th July 2013 at 14:03.

Similar Threads

  1. Calling Stored Procedure using ODBC
    By luiz.carlos.do.lago in forum Newbie
    Replies: 0
    Last Post: 21st November 2010, 00:35
  2. Problem calling stored procedure.. Help!
    By triperzonak in forum Qt Programming
    Replies: 4
    Last Post: 15th March 2010, 09:34
  3. Problem with calling stored procedures in QT
    By sudheer168 in forum Qt Programming
    Replies: 3
    Last Post: 9th November 2009, 10:07
  4. Replies: 14
    Last Post: 16th January 2009, 09:11
  5. Frozen? ... no ... calling 01800 Database
    By chaosgeorge in forum Qt Programming
    Replies: 2
    Last Post: 26th November 2006, 14:06

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.