CODEWITHSUNDEEP
qt-creatorfirebirdfirebird2.1
Ozplc
Ozplc

Reputation: 1131

using Qt Stored procedure in Firebird

I'm starting in the world of Qt and a Firebird database. I finished the driver installation process and perform operations on the database like insert, update and select.

When I started to make a stored procedures and run them from Qt, it did not work. It does not fail and always shows that everything was made ​​perfect, but the database does not run.

I am programming in Linux using Qt 2.0.1 and Firebird 2.1. I created a simple stored procedure test which makes an insert into a table. It works by running the console, but when trying to run from Qt, it does not work and gives me no errors. The SQL code is:

SET TERM ^ ;
CREATE PROCEDURE AGREEGAR_UNO AS 
BEGIN 
  insert into JUEGO(CODIGO,ESCRUTINIO,ESTADO,FECHA,HORAINICIO) 
      values (next value for GNECODIGOJUEGO,'111,123,154,169,178','Hi', current_date, current_time);
END^
SET TERM ; ^
GRANT EXECUTE ON PROCEDURE AGREEGAR_UNO TO SYSDBA;

The following code will use to connect to Firebird from Qt

bool VentanaPrueba::conectar()
{
    this->db= QSqlDatabase::addDatabase("QIBASE","Data");
    this->db.setDatabaseName("./BD/Data.fdb");
    this->db.setPassword("password");
    this->db.setUserName("SYSDBA");
    if(!db.open())
    {
        return false;
    } else {
        return true;
    }

And this is the code that is responsible for calling the procedure

void VentanaPrueba::procedimiento()
{
    if (!this->db.isOpen()) this->conectar();
    if(this->db.isOpen())
    {
        QSqlQuery procedimiento = QSqlQuery::QSqlQuery(this->db);
        bool bandera = procedimiento.prepare("EXECUTE PROCEDURE AGREEGAR_UNO");
        QString err = procedimiento.lastError().text();
        bool respuesta= procedimiento.exec();
        //this->db.commit();
        if(!respuesta)
        {
            this->db.close();
        } else {
            procedimiento.finish();
            this->db.commit();
            this->db.close();
        }
    } else {
       //error
    }
}

Upvotes: 3

Views: 1287

Answers (2)

mukthar ali
mukthar ali

Reputation: 41

There is a simpler way, even though little bit weird.

Create a stored procedure in Firebird with some output variable to suspend, and a VARCHAR(1024) input variable to pass the procedure call.

And call it in Qt with procedure call as a string parameter.

SET TERM ^ ;
create PROCEDURE SP_EXECUTE (STMNT varchar(1024) )
    RETURNS (
        INRETURN integer )
AS
BEGIN
    execute statement stmnt;
    inReturn=1;
    suspend;
END^
SET TERM ; ^

Then in Qt:

procedimiento.prepare("SELECT INRETURN FROM SP_EXECUTE('EXECUTE PROCEDURE AGREEGAR_UNO')");

Upvotes: 0

Tim Meyer
Tim Meyer

Reputation: 12600

I can't tell if this works for firebird, but you could try the following:

procedimiento.prepare("BEGIN EXECUTE PROCEDURE AGREEGAR_UNO; END;");

This is similar to the PL/SQL passage

BEGIN
  EXECUTE PROCEDURE AGREEGAR_UNO;
END;

I am accessing Oracle databases with Qt and had to include the "BEGIN" and "END;" commands there. You don't have the "EXECUTE PROCEDURE" in oracle, but it might be required for Firebird.
Also, I am using Qt 4 so there might be a difference as well.

Upvotes: 0

Related Questions