Ado protocol = TZQuery problem rev 82.

Forum related to version 6.5.1 (alpha) and 6.6.x (beta) of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Ado protocol = TZQuery problem rev 82.

Post by aperger »

Hi All,

I have a product which uses ZeOS DBO engine. This product can use some database tpye: FireBird, PostgreSQL, MSSQL. The DB structure allways the same.
When I run it on my MSSQL database with the latest (rev. 82) source I have some problem with the queries. These queries are working on the oder type of datatabases (FireBird, PostgreSQL)!
If I call a quiery with TZQuery with Parameters, the query allways uses the design time value of the parameters.

Can anybody help on me?

I add "RetValue:=Value;" line into the SetInParam function. Now it looks me OK, but maybe this is not the right solution!

Code: Select all

procedure TZAdoPreparedStatement.SetInParam(ParameterIndex: Integer;
  SQLType: TZSQLType; const Value: TZVariant);
.
.
.
begin
   RetValue:=Value;

.
.
.

end;

Thanks

Attila
Terence
Zeos Dev Team
Zeos Dev Team
Posts: 141
Joined: 22.09.2005, 14:11
Location: Stuttgart
Contact:

Post by Terence »

Sry, i can't follow you. Try too make clear what is your problem, what is your actually solution and what don't work there.
fabian
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

Hi,

Please, try to run a query with parameters and use TZQuery class without my modification:
"select field from table where field = :fieldvalue;"
":filedvalue" never changes, in other words: never set.
So... I can not run quiries with parameters on MSSQL database with ado protocol.
Terence
Zeos Dev Team
Zeos Dev Team
Posts: 141
Joined: 22.09.2005, 14:11
Location: Stuttgart
Contact:

Post by Terence »

?? sure it never changes, your code doesn't change the param, you have to set it eg with Query.ParamByName(TableNameColName).Value:= 'xy';
And i don't know an example where the sql
"select field from table where 'xy';" will makes sense.
Still unclear.
fabian
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

Hi,

I set the value of the parameter, the code is working with other type of databases. (...and I fix the "speudo code".. :-) )
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

Code: Select all

try
  DMSzamla.qSzamlaFejAzon.Active:=false;
  DMSzamla.qSzamlaFejAzon.ParamByName('Azonosito').AsInteger:=Azon;
  DMSzamla.qSzamlaFejAzon.Active:=true;
except
  on E: Exception do begin
    ShowPSError(E.Message);
    SzamlaBetolt:=-1;
     Exit;
  end;
end;
if DMSzamla.qSzamlaFejAzon.Eof then begin
  ShowPSWarning(Lang.Read('MESSAGES','INVALID_INVOICE_ID'));
  SzamlaBetolt:=0;
  Exit;
  // this is happening always
end;
Terence
Zeos Dev Team
Zeos Dev Team
Posts: 141
Joined: 22.09.2005, 14:11
Location: Stuttgart
Contact:

Post by Terence »

What is the code line causing exception and what does the exception exactly says?
Furthermore let's see ur sql string.

What does this line do from your opinion?
"if DMSzamla.qSzamlaFejAzon.Eof then"
fabian
Terence
Zeos Dev Team
Zeos Dev Team
Posts: 141
Joined: 22.09.2005, 14:11
Location: Stuttgart
Contact:

Post by Terence »

Try to use it that way.. (untested)

Var
Const FIELDNAME='blaname';
begin
Query:= TZquery.create;
Query.Connection:= ZConnection;
Query.sql.Add('SELECT .. FROM .. WHERE '+FIELDNAME+'= :'+FIELDNAME);
Query.ParambyName(FIELDNAME).AsString:= 'xyz';
Query.Open;
Query.First;
while not Query.EOF do
< ...... >
Query.Next;
end;
Query.Close;
Query.Free;
fabian
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

The parameter value of "Azonosito" is zero, I set it at designe time. All of the invoice id are different from zero. If I call this query the result always an empty recodset, bacause the query never use the value which is coming from "Azon".
The code, and the query are working if I run it on other database (PostgreSQL, Firebird). The user can select a prefered database type at configuration time : FireBird, PostgreSQL, MSSQL.
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

please take a look into this ZeOS function:
procedure TZAdoPreparedStatement.SetInParam(ParameterIndex: Integer;
SQLType: TZSQLType; const Value: TZVariant);

the problem is Value and retValue are mixed!
Terence
Zeos Dev Team
Zeos Dev Team
Posts: 141
Joined: 22.09.2005, 14:11
Location: Stuttgart
Contact:

Post by Terence »

I will have a look tomorrow, its 01.38h in germany ;)
Last edited by Terence on 19.07.2006, 00:40, edited 1 time in total.
fabian
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

in Hungary too....Good Night
Terence
Zeos Dev Team
Zeos Dev Team
Posts: 141
Joined: 22.09.2005, 14:11
Location: Stuttgart
Contact:

Post by Terence »

ahh. i meant today in about 7 hours, gn8!
fabian
Terence
Zeos Dev Team
Zeos Dev Team
Posts: 141
Joined: 22.09.2005, 14:11
Location: Stuttgart
Contact:

Post by Terence »

I just took a look into that file of testing branch and it looks logic how its is used, please supply detailed info what you think is buggy - oldline of code
> new line of code.

Code: Select all

B := DefVarManager.GetAsInterface(Value) as IZBlob;
.........
DefVarManager.SetAsString(RetValue, B.GetString);
.........
DefVarManager.SetAsUnicodeString(RetValue, B.GetUnicodeString);
.........
DefVarManager.SetAsString(RetValue, BytesToStr(B.GetBytes));
.........
  case RetValue.VType of
    vtNull: V := Null;
    vtBoolean: V := SoftVarManager.GetAsBoolean(RetValue);
    vtInteger: V := Integer(SoftVarManager.GetAsInteger(RetValue));
    vtFloat: V := SoftVarManager.GetAsFloat(RetValue);
    vtString: V := SoftVarManager.GetAsString(RetValue);
    vtUnicodeString: V := SoftVarManager.GetAsUnicodeString(RetValue);
    vtDateTime: V := SoftVarManager.GetAsDateTime(RetValue);
  end;
fabian
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

Code: Select all

>RetValue:=Value;
.........
B := DefVarManager.GetAsInterface(Value) as IZBlob;
.........
DefVarManager.SetAsString(RetValue, B.GetString);
.........
DefVarManager.SetAsUnicodeString(RetValue, B.GetUnicodeString);
.........
DefVarManager.SetAsString(RetValue, BytesToStr(B.GetBytes));
.........
  case RetValue.VType of
    vtNull: V := Null;
    vtBoolean: V := SoftVarManager.GetAsBoolean(RetValue);
    vtInteger: V := Integer(SoftVarManager.GetAsInteger(RetValue));
    vtFloat: V := SoftVarManager.GetAsFloat(RetValue);
    vtString: V := SoftVarManager.GetAsString(RetValue);
    vtUnicodeString: V := SoftVarManager.GetAsUnicodeString(RetValue);
    vtDateTime: V := SoftVarManager.GetAsDateTime(RetValue);
  end;
"RetValue:=Value;" this is required for correct working because all of the "DefVarManager.Set*/Get*" function use RetValue.
Post Reply