[SOLVED] ZEOS TZQuery usage
lazarus / 16-Databases

----------------------------------------------------------------------
kribjo · 2010-12-16 12:33

Hi,

I'm using CodeTyphoon from 7th of December. A pre-packaged version of
fpc 2.4.2 and the latest Lazarus plus a lot of components on the
components palette.

I have a form populated with Datasource, ZConnection, ZQuery and
DBGrid. I have also got a Button and a Edit.

ZQuery have one parameter with the following properties:
Code: [Select]DataType ftString
Name Userid
NumericScale 0
ParamType ptInput
Precision 0
Size 0
Button click have the following code:
Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
begin
  ZQuery1.SQL.Text:='select * from User_tab where :UserID like
identity';
  ZQuery1.ParamByName('UserID').AsString:=Edit1.Text;
  ZQuery1.ExecSQL;
  ZQuery1.Open;
//  ZQuery1.Active:=TRUE;
end;
When running this simple application I type in some text in the Edit
component and press the button which fire the code above. I then get
the exception below:

Code: [Select]Project abc.exe raised exception class 'EZSQLException'
with message: SQL Error: OCI_INVALID_HANDLE
If I delete the Edit and Button and put a simple SQL like select *
from User_tab where identity like 'A%' in ZQuery and run it, the
DBGRID shows all usernames beginning with A, so I believe I'm doing
something wrong with the button click code.

I have tried to google it, and it looks like the ZEOS web pages are
down for unknown reason. If anyone can put me on the right track I
will greatly appreciate it. Thanks.

Regards,

Bjørn

----------------------------------------------------------------------
JD · 2010-12-16 13:17

Try putting the 'ParamByName' statement before the 'SQL.Text'
statement as below & then try again.

Code: [Select]  ZQuery1.ParamByName('UserID').AsString:=Edit1.Text;
  ZQuery1.SQL.Text:='select * from User_tab where :UserID like
identity';

----------------------------------------------------------------------
kribjo · 2010-12-16 13:26

Thanks for the tip.

I tried to do that, but I still get the same exception. Clicking break
it opens the ZEOS file ZDbOracleUtils on line 665: which have code:
Format(SSQLError1, [ErrorMessage]));
Stepping into this and the same happens as if I clicked Continue
(instead of break on the excetion dialog box). I get a new dialog-box
saying: SQL Error: OCI_INVALID_HANDLE Press OK to ignore and risk data
corruption. Press Cancel to kill the program.

Regards,

Bjørn

----------------------------------------------------------------------
joseme · 2010-12-16 14:18

I think you should remove the line ZQuery1.ExecSQL, leaving alone the
Open

----------------------------------------------------------------------
Arbee · 2010-12-16 14:22

You are using both ExecSQL and an Open.

open is for queries that return a result (like SELECT) and execSQL is
for queries that don't.  You can't use them both.
You should remove the execSQL.

----------------------------------------------------------------------
kribjo · 2010-12-16 15:04

Hi,

Thanks, that did the trick.

RJBosch excellent and simple explanation of why. Excellent, because I
learned something in addition. 

Regards,

Bjørn