Ado protocol = TZQuery problem rev 82.

aperger · Post by **aperger** » 17.07.2006, 16:27

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 · Post by **Terence** » 18.07.2006, 23:47

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.

aperger · Post by **aperger** » 19.07.2006, 00:00

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 · Post by **Terence** » 19.07.2006, 00:07

?? 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.

aperger · Post by **aperger** » 19.07.2006, 00:10

Hi,

I set the value of the parameter, the code is working with other type of databases. (...and I fix the "speudo code".. :-) )

aperger · Post by **aperger** » 19.07.2006, 00:16

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 · Post by **Terence** » 19.07.2006, 00:23

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"

Terence · Post by **Terence** » 19.07.2006, 00:29

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;

aperger · Post by **aperger** » 19.07.2006, 00:33

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.

aperger · Post by **aperger** » 19.07.2006, 00:36

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 · Post by **Terence** » 19.07.2006, 00:39

I will have a look tomorrow, its 01.38h in germany

aperger · Post by **aperger** » 19.07.2006, 00:39

in Hungary too....Good Night

Terence · Post by **Terence** » 19.07.2006, 00:44

ahh. i meant today in about 7 hours, gn8!

Terence · Post by **Terence** » 19.07.2006, 09:52

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;

aperger · Post by **aperger** » 19.07.2006, 11:13

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.