Page 1 of 2
Ado protocol = TZQuery problem rev 82.
Posted: 17.07.2006, 16:27
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
Posted: 18.07.2006, 23:47
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.
Posted: 19.07.2006, 00:00
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.
Posted: 19.07.2006, 00:07
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.
Posted: 19.07.2006, 00:10
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".. :-) )
Posted: 19.07.2006, 00:16
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;
Posted: 19.07.2006, 00:23
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"
Posted: 19.07.2006, 00:29
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;
Posted: 19.07.2006, 00:33
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.
Posted: 19.07.2006, 00:36
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!
Posted: 19.07.2006, 00:39
by Terence
I will have a look tomorrow, its 01.38h in germany
Posted: 19.07.2006, 00:39
by aperger
in Hungary too....Good Night
Posted: 19.07.2006, 00:44
by Terence
ahh. i meant today in about 7 hours, gn8!
Posted: 19.07.2006, 09:52
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;
Posted: 19.07.2006, 11:13
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.