PDA

View Full Version : Stored Procedure in Firebird databse (IBase)



Specialized1
14th September 2009, 22:07
I faced with problem executing a stored procedure from my Firebird database.
I got an error:
"no permission for execute access to PROCEDURE MyProcedure Could not prepare statement"

I worked with several in my database and everything was fine. I have created the database in IBExpert tool and I did not fix Roles and users in database. So, I used a SYSDBA name and standard password.

I got and error during prepare QSQLQuery:

QSqlQuery query;
bool result = query.prepare("EXECUTE PROCEDURE MyProcedure (:a)");
QString err = query.lastError().text(); // "no permission for execute access to PROCEDURE...
query.bindValue(":a", 1);
query.exec();
...

I have tried to call procedure directly in IBExpert and FlameRobin tools and evething works fine, but using QT 4.5 - no.

Does anybody had such problem?

I have used:
- QT 4.5
- Firebird 2.1 (embedded server "fbclient.dll")
- WIN XP SP2
- connection string : "user id=SYSDBA;password=masterkey;server type=Embedded;auto_commit=True;auto_commit_level=4 096;connection lifetime=1; DataBase=EMPTY.FDB"

lyuts
15th September 2009, 07:55
I'm not sure you are calling for last error at the right place. Here is what you do:
1) prepare the query
2) find out the last error
3) execute the query

Specialized1
15th September 2009, 10:47
I have found the problem! The problem is during connection to the database.

I wrote:

QSqlDatabase database;
database.setDatabaseName("D:\Test\d1.fdb");
database.setConnectOptions(user id=SYSDBA;password=masterkey;server type=Embedded;auto_commit=True;auto_commit_level=4 096;connection lifetime=1; DataBase="D:\Test\d1.fdb");

But should be:

QSqlDatabase database;
database.setDatabaseName("D:\Test\d1.fdb");
database.setUserName("SYSDBA");
database.setPassword("masterkey");
database.setConnectOptions(server type=Embedded;auto_commit=True;auto_commit_level=4 096;connection lifetime=1; DataBase="D:\Test\d1.fdb");

So, we should set User name and Password using methods "setUserName", "setPassword". That is why I did not have permissions to execute procedures.