ZQuery.Open causes Access Violation (Delphi7 + ZEOSLIB 6.6.5 + Firebird 2 Embedded)
Ok, simple Desktop App using Delphi 7, Firebird 2.0 Embedded, and ZEOS 6.6.5 Stable components.
I can connect to the fdb, but when I try to open a query I get Access Violation. I don't know what I've overlooked.
If I connect using user = ' ' (space) and password = ' ' (space) I can connect but if I try to ZQuery1.Open (or Zquery1.ExecSQL) I am told that this user does not have the privilege to perform this operation on this object. So I use the user and password that were used to create the fdb and then I can connect but I get an AV if I try ZQuery1.Open (however, no exception if I use ZQuery1.ExecSQL)
these are set at design time
DBGrid1.DataSource = DataSource1
DataSource1.DataSet = ZQuery1
ZQuery1.Connection = ZConnection1
this code runs when I type some sql into a memo and click a button
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.AddStrings(Mem
try
//ZQuery1.ExecSQL;
ZQuery1.Open;
except
On E: Exception do ShowMessage(E.Message);
end;
I get AV for ZQuery1.Open but not ZQuery1.ExecSQL
What am I missing? Any ideas? much appreciated.
I can connect to the fdb, but when I try to open a query I get Access Violation. I don't know what I've overlooked.
If I connect using user = ' ' (space) and password = ' ' (space) I can connect but if I try to ZQuery1.Open (or Zquery1.ExecSQL) I am told that this user does not have the privilege to perform this operation on this object. So I use the user and password that were used to create the fdb and then I can connect but I get an AV if I try ZQuery1.Open (however, no exception if I use ZQuery1.ExecSQL)
these are set at design time
DBGrid1.DataSource = DataSource1
DataSource1.DataSet = ZQuery1
ZQuery1.Connection = ZConnection1
this code runs when I type some sql into a memo and click a button
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.AddStrings(Mem
try
//ZQuery1.ExecSQL;
ZQuery1.Open;
except
On E: Exception do ShowMessage(E.Message);
end;
I get AV for ZQuery1.Open but not ZQuery1.ExecSQL
What am I missing? Any ideas? much appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do not use multiple statements in ZQuery.SQL, this will not work. Then
i := 0;
while i<Memo1.Lines.Count do
begin
ZQuery1.Text := Memo1.Lines.Strings[i];
ZQuery1.ExecSQL;
ZQuery1.Open;
// process recordset here
ZQuery1.Close;
i := i+1;
end;
i := 0;
while i<Memo1.Lines.Count do
begin
ZQuery1.Text := Memo1.Lines.Strings[i];
ZQuery1.ExecSQL;
ZQuery1.Open;
// process recordset here
ZQuery1.Close;
i := i+1;
end;
Generally when working with ZEOS:
1. ExecSQL the query, doesn't matter what it do
2. Open the query and process results
3. Close the query if it was opened in 2.
4. you do not need 2. and 3. for INSERT,UPDATE,DELETE,...
1. ExecSQL the query, doesn't matter what it do
2. Open the query and process results
3. Close the query if it was opened in 2.
4. you do not need 2. and 3. for INSERT,UPDATE,DELETE,...
ASKER
I appreciate all your comments, but I'm still getting an AV...
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add(Memo1.Text
// I'm positive the table exists
try
ZQuery1.ExecSQL;
ZQuery1.Open; // *Access Violation happens here*
except
On E: Exception do ShowMessage(E.Message);
end;
Q) Is it the Query's DataSource property that has to be populated with the DataSource object
or the DataSource's DataSet property that has to be populated with the Query object? I think I'm confused...
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add(Memo1.Text
// I'm positive the table exists
try
ZQuery1.ExecSQL;
ZQuery1.Open; // *Access Violation happens here*
except
On E: Exception do ShowMessage(E.Message);
end;
Q) Is it the Query's DataSource property that has to be populated with the DataSource object
or the DataSource's DataSet property that has to be populated with the Query object? I think I'm confused...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it is of no use at this point in protecting your code
there is nothing new
it will remain guess work otherwise
can you connect to the firebird using the firebird tools ?
or using a ADOQuery and ADOConnection or IBQuery and IBConnection ?
did you specify the database ?
in your connection object , check the connected property = true
you should be able to do all this at design time
there is nothing new
it will remain guess work otherwise
can you connect to the firebird using the firebird tools ?
or using a ADOQuery and ADOConnection or IBQuery and IBConnection ?
did you specify the database ?
in your connection object , check the connected property = true
you should be able to do all this at design time
oh, and for what it's worth
i don't believe in or recommend embedded databases
i don't believe in or recommend embedded databases
ASKER
jfromanski, thank you for confirming that. It's what I had.
Geert Gruwez, believe me there's nothing to protect (I'll try to post full code tonight - it might be more embarassing than helpful), I just started a new project with a single form, copied the dll's for firebird2 embedded into the project's bin folder, along with an existing fdb created on another machine. I dragged and dropped a ZConnection and ZQuery component from the ZEOS Access tab and created a few textboxes to input db filename, hostname, username, password, and protocol in order for the Connection object to be able to connect, and it does connect (I test the ZConnection.Connected property).
I can connect to the db at design time, but I get same AV when I try to make ZQuery.Active = True
(with simple select * from table_name in ZQuery.SQL)
I can connect to that db from IBOConsole
I will try to connect using the IBX objects in the Interbase tab. I will do this tonight, because I'm at work right now.
I've used firebird embedded with IBX components at work but this is the first time I'm trying with ZEOS components. The problem with embedded is that you can't create a database, you have to use an existing db, and you need to connect with username = ' ' (space) and password = ' ' (space) so security is "different" in firebird embedded.
Consider this angle...
If I connect using username = 'SYSDBA' and password = 'masterkey' (don't laugh!!!), I get an AV when I try to run the query 'select * from table_name'.
If I connect using username = ' ' and password = ' ', I don't get an AV, I get 'this user doesn't have permission to perform this operation on this object' when I try to run the query 'select * from table_name'.
Could it be possible that I'm getting an AV because the fbclient.dll wants me to log in with those spaces, and maybe the key to getting access to the existing tables is to change something in RDB$USER_PRIVILEGES. I've had a look at that table and it obviously doesn't contain any mention of the username ' ' (space). Or maybe I should delete all rows from that table... sounds like more trouble shooting for me tonight.
I appreciate all your comments and ideas, there's a lack of solutions for this area on the net so we may be pioneers if we solve it. Hurrray!!! :-)
Geert Gruwez, believe me there's nothing to protect (I'll try to post full code tonight - it might be more embarassing than helpful), I just started a new project with a single form, copied the dll's for firebird2 embedded into the project's bin folder, along with an existing fdb created on another machine. I dragged and dropped a ZConnection and ZQuery component from the ZEOS Access tab and created a few textboxes to input db filename, hostname, username, password, and protocol in order for the Connection object to be able to connect, and it does connect (I test the ZConnection.Connected property).
I can connect to the db at design time, but I get same AV when I try to make ZQuery.Active = True
(with simple select * from table_name in ZQuery.SQL)
I can connect to that db from IBOConsole
I will try to connect using the IBX objects in the Interbase tab. I will do this tonight, because I'm at work right now.
I've used firebird embedded with IBX components at work but this is the first time I'm trying with ZEOS components. The problem with embedded is that you can't create a database, you have to use an existing db, and you need to connect with username = ' ' (space) and password = ' ' (space) so security is "different" in firebird embedded.
Consider this angle...
If I connect using username = 'SYSDBA' and password = 'masterkey' (don't laugh!!!), I get an AV when I try to run the query 'select * from table_name'.
If I connect using username = ' ' and password = ' ', I don't get an AV, I get 'this user doesn't have permission to perform this operation on this object' when I try to run the query 'select * from table_name'.
Could it be possible that I'm getting an AV because the fbclient.dll wants me to log in with those spaces, and maybe the key to getting access to the existing tables is to change something in RDB$USER_PRIVILEGES. I've had a look at that table and it obviously doesn't contain any mention of the username ' ' (space). Or maybe I should delete all rows from that table... sounds like more trouble shooting for me tonight.
I appreciate all your comments and ideas, there's a lack of solutions for this area on the net so we may be pioneers if we solve it. Hurrray!!! :-)
i'll have to do some reading and investigating of my own for this user space
my idea would be it uses the current logged on user and it's settings ...
maybe that's why you don't have priviliges ... because it was created with a other user
my idea would be it uses the current logged on user and it's settings ...
maybe that's why you don't have priviliges ... because it was created with a other user
ASKER
yes, I think that's the issue, but although I can connect with the other user's credentials (ie. SYSDBA), firebird embedded requires username=' ' (space) and password=' ' (space). Maybe it only seemed to work at my workplace because I also had firebird classic server installed, but it wasn't really using the embedded drivers at that time.
I really think this issue is worth more than 500 points.
I really think this issue is worth more than 500 points.
ASKER
Hello everyone, I've found the ZEOS forum dealing with Firebird
http://zeos.firmos.at/viewforum.php?f=17&topicdays=0&start=0
There are many posts there around similar issues. I've read a few and already I have plenty of ideas to try out tonight when I get home. I will not forget to post the answer to my particular issue when I finally solve it, but I am confident it will be solved soon.
Thanks to jfromanski and Geert Gruwez for your contributions. I intend to split the points, but not before I post details of the final solution.
http://zeos.firmos.at/viewforum.php?f=17&topicdays=0&start=0
There are many posts there around similar issues. I've read a few and already I have plenty of ideas to try out tonight when I get home. I will not forget to post the answer to my particular issue when I finally solve it, but I am confident it will be solved soon.
Thanks to jfromanski and Geert Gruwez for your contributions. I intend to split the points, but not before I post details of the final solution.
ASKER
OK, I'm done.
First of all, there were errors in my code.
(1) The Access Violation was happening because I tried to ZQuery.Open before I had connected successfully. The way I was checking the connection status was wrong.
(2) Read/Access Permission to the tables in the existing database could not be acheived unless I connected using the same credentials that created that FDB. Those users are found in the RDB$USER_PRIVILEGES system table of that database. With Firebird Embedded you can connect with any user and password but whether you can read/write to the database objects (eg. tables) through that connection is still subject to the SQL privileges assigned to the logged on user.
(3) There were no problems accessing a Firebird1.5 database using Firebird 2.1 Embedded.
(4) To connect at design-time using the "firebirdd-2.1" protocol, I copied fbembed.dll to \Program Files\Borland\Delphi7\Bin\
But if I changed the protocol I would get an error and could reconnect even if I changed it back unless I restarted the Delphi IDE.
(5) To connect at run-time using the "firebirdd-2.1" protocol, I needed to copy the fbembed.dll to \WINDOWS\SYSTEM32\fbclient
Well, luckily I didn't post my code, it would have been embarrassing :-)
First of all, there were errors in my code.
(1) The Access Violation was happening because I tried to ZQuery.Open before I had connected successfully. The way I was checking the connection status was wrong.
(2) Read/Access Permission to the tables in the existing database could not be acheived unless I connected using the same credentials that created that FDB. Those users are found in the RDB$USER_PRIVILEGES system table of that database. With Firebird Embedded you can connect with any user and password but whether you can read/write to the database objects (eg. tables) through that connection is still subject to the SQL privileges assigned to the logged on user.
(3) There were no problems accessing a Firebird1.5 database using Firebird 2.1 Embedded.
(4) To connect at design-time using the "firebirdd-2.1" protocol, I copied fbembed.dll to \Program Files\Borland\Delphi7\Bin\
But if I changed the protocol I would get an error and could reconnect even if I changed it back unless I restarted the Delphi IDE.
(5) To connect at run-time using the "firebirdd-2.1" protocol, I needed to copy the fbembed.dll to \WINDOWS\SYSTEM32\fbclient
Well, luckily I didn't post my code, it would have been embarrassing :-)
ASKER
Thanks for your contribution guys. The situation was too complicated for there to be one resolution. I've briefly described the changes I had to make to resolve the issue completely. Merry Christmas.
wow man,
looks like your on the good path of a real troubleshooter
i hope for you you don't this kind of problem at a customer
this looks like a real hard one to find
great work
looks like your on the good path of a real troubleshooter
i hope for you you don't this kind of problem at a customer
this looks like a real hard one to find
great work
ASKER
I'm doing a Select and I've connected using SYSDBA... any other thoughts?
I hope I don't have to create persistent fields in the query, I want to pass the sql string in so I'm not sure how to go about creating persistent fields for every possible query I might pass in...