#16 Parameters

[Jacq]

ZUpdateSQL no recognize parameters. Example included.
Compile with Lazarus 1.0.4 and zeoslib 7.0.2-rc

[Jacq]

• Description has changed:

Diff:

--- old
+++ new
@@ -1 +1,2 @@
 **ZUpdateSQL** no recognize parameters. Example included.
+Compile with Lazarus 1.0.4 and zeoslib 7.0.2-rc

[EgonHugeist]

Thank's for submitting this issue report.

I had a quick look to your attached template. Well our documentation is weak, i know. Longtimes i hope somebody helps us with little wiki here.

To your issue:

You're simply using the Parameters from the false Component. If TZUpdateSQL is assigned to another TZDataSet component and the 3 statments are filled then you can assign the Parameteters by using TZQuery1.ParamByName('memo2').AsString:='BeforeInsertSQL'; f.e.

Do not assign the Parameters of the TZUpdateSQL-Component. They are only made to declare default falues, theire types etc.

Can you please follow my suggestions? IMHO this is not a ticket thread.

Cheers Michael

[Jacq]

Yes, i'am trying this but not working. This time error is:
ZQuery1: Parameter "memo2" not found
I try to set parameter in event ZQuery1BeforePost like this:
ZQuery1.ParamByName('memo2').AsString:='ZQuery1BeforePost';
When ZQuery create parameters and then i my assign value?

[EgonHugeist]

@Jacq

I think you're on the right way. Be sure the TZUpdateSQL statments are assigned before changing the UpdateState from dsBrowse to dsEdit or dsInsert. Than all Parameters are available and you can assign the Values like you can do it, if you would use a TZDataset-Statement like TZQuery.SQL.Text = insert into foo(id, text) values(:id, :text);

Again: Assign the Parameters and Statetements befor using Edit, Append, Insert etc.

Waiting for responce..

[Jacq]

As you can see in example, ZUpdateSQL statements are assigned just after open ZQuery. But it does not work as you describe. ZQuery has no params at all.
ZUpdate create params (i can assign values) but not use them to prepare SQL statement. They are useless. Try compile example. I browse sources of TZUpdate class but they are to complex to me. How to force ZUpdate to respect values assigned to params? What for they are?
PS: ZUpdate.ParamCheck is True.
PS2: Sory for my english.

[EgonHugeist]

Don't worry about your english. I'm also not the best in this domain.

Here is an example of our test-suites:

{**
Test the bug report #727373.

The TZQuery with TZUpdateObject can't update records
when left outer joins is used in sql query.
}
procedure TZTestCompMySQLBugReport.Test727373;
var
Query: TZQuery;
UpdateSql: TZUpdateSQL;
begin
if SkipTest then Exit;

if SkipClosed then Exit;

Query := CreateQuery;
try
UpdateSql := TZUpdateSQL.Create(nil);
try
// Query.RequestLive := True;

  Query.SQL.Text := 'delete from people where p_id >= ' + IntToStr(TEST_ROW_ID);
  Query.ExecSQL;

  Query.SQL.Text := 'select p.*, d.dep_name from people p ' +
    ' left outer join department d on p.p_id = d.dep_id ' +
    ' where p_id = ' + IntToStr(TEST_ROW_ID);
  Query.UpdateObject := UpdateSql;
  UpdateSql.InsertSQL.Text := 'insert into people (p_id, p_name, p_begin_work, ' +
    ' p_end_work) values (:p_id, :p_name, :p_begin_work, :p_end_work)';
  UpdateSql.ModifySQL.Text := 'update people set p_id = :p_id, p_name = :p_name, ' +
    ' p_begin_work = :p_begin_work, p_end_work = :p_end_work where p_id = :OLD_p_id';
  UpdateSql.DeleteSQL.Text := 'delete from people where p_id = :OLD_p_id';
  UpdateSql.Params.ParamByName('p_id').DataType := ftInteger;
  UpdateSql.Params.ParamByName('p_id').ParamType := ptInput;
  UpdateSql.Params.ParamByName('p_name').DataType := ftString;
  UpdateSql.Params.ParamByName('p_name').ParamType := ptInput;
  UpdateSql.Params.ParamByName('p_begin_work').DataType := ftTime;
  UpdateSql.Params.ParamByName('p_begin_work').ParamType := ptInput;
  UpdateSql.Params.ParamByName('p_end_work').DataType := ftTime;
  UpdateSql.Params.ParamByName('p_end_work').ParamType := ptInput;
  UpdateSql.Params.ParamByName('OLD_p_id').DataType := ftInteger;
  UpdateSql.Params.ParamByName('OLD_p_id').ParamType := ptInput;

  Query.Open;
  Query.Append;
  Query.FieldByName('p_id').AsInteger := TEST_ROW_ID;
  Query.FieldByName('p_name').AsString := 'Vasia';
  Query.FieldByName('p_begin_work').AsDateTime := EncodeTime(9, 30, 0, 0);
  Query.FieldByName('p_end_work').AsDateTime := EncodeTime(18, 30, 0, 0);
  Query.Post;
  Query.ApplyUpdates;
  Query.Close;

  Query.Open;
  CheckEquals(False, Query.IsEmpty);
  CheckEquals(TEST_ROW_ID, Query.FieldByName('p_id').AsInteger);
  CheckEquals('Vasia', Query.FieldByName('p_name').AsString);
  CheckEquals(EncodeTime(9, 30, 0, 0), Query.FieldByName('p_begin_work').AsDateTime);
  CheckEquals(EncodeTime(18, 30, 0, 0), Query.FieldByName('p_end_work').AsDateTime);
  Query.Edit;
  Query.FieldByName('p_id').AsInteger := TEST_ROW_ID;
  Query.FieldByName('p_name').AsString := 'Petia';
  Query.FieldByName('p_begin_work').AsDateTime := EncodeTime(10, 0, 0, 0);
  Query.FieldByName('p_end_work').AsDateTime := EncodeTime(19, 0, 0, 0);
  Query.Post;
  Query.ApplyUpdates;
  Query.Close;

  Query.Open;
  CheckEquals(False, Query.IsEmpty);
  CheckEquals(TEST_ROW_ID, Query.FieldByName('p_id').AsInteger);
  CheckEquals('Petia', Query.FieldByName('p_name').AsString);
  CheckEquals(EncodeTime(10, 0, 0, 0), Query.FieldByName('p_begin_work').AsDateTime, 0.001);
  CheckEquals(EncodeTime(19, 0, 0, 0), Query.FieldByName('p_end_work').AsDateTime, 0.001);
  Query.Delete;
  Query.ApplyUpdates;
  Query.Close;

  Query.Open;
  CheckEquals(True, Query.IsEmpty);
finally
  UpdateSql.Free;
end;

finally
Query.Free;
end;
end;

It completely shows you how to work with the TZUpdateSQL-Component. I hope it helps you better (oftenly some code-lines saying wore than 1000 words).

Please check your code again. I personally do start thinking about a way to use the Parameters of the TZUpdateSQL-Component too, because you're really not the first one who is running into the same issue.

Cheers, Michael

[Jacq]

Is this bug (#727373) still present? (I try this code and values of Time fields are not written to table)
Is there any place where bug reports are? (Like bugs.freepascal.org)

[Mark Daems]

Hi Jacq,
No it shouldn't be present anymore. It's a very old one from the old bug tracker on sourceforge that we already dropped 5 years ago.
In the meantime we have been using a tracker on http://zeosbugs.firmos.at. But we just (a few months ago) decided to continue here, because here we don't have to worry about maintaining tracker software which was more difficult in the previous location.

The code Michaels shows is in the 'test' directory of zeos SVN. It isn't distributed in official releases, but we use that battery of tests to avoid re-introducing old bugs.
And I just ran this test again against my test databases and I can confirm it doesn't show errors. Which means : it behaves as expected, but of course, that doesn't mean it solves your questions.

[Jacq]

I must ommit something in this test, beacuse the time fields are always 0:00:00 :(
Is this test tested with mysql only? I use postgresql.

[Jacq]

Test #727373 from MySql tested with PostgreSql:
Shows: "TZTestCompPostgreSQLBugReport.Test727373: expected: <0,3958> but was: <0>
EAssertionFailedError"
I Add test to ZTestBugCompPostgreSql.pas
Patch included

Resolved!
I using fpc 2.6.0
When i undef WITH_FPC_FTTIME_BUG in ZeosLazarus.inc then time fields are right set.
WITH_FPC_FTTIME_BUG is undefined from fpc 2.7.0 but should be from fpc 2.6.0

[Mark Daems]

Jacq,

I'm not sure if it's that simple. As far as I know this fttime bug was only fixed at the time of fpc 2.7.1 (September 2012).
I checked with our fpc contacts (marcov and ludob) if they can find out what exact version this bugfix is in. Waiting for their reply.
You may also have hit a use case where the define has an unwanted side effect.
Testing your postgres version of the test now. Delphi compilers pass the test without trouble. No idea about fpc yet.

[EgonHugeist]

Hi Jacq,

where did you get informations about issue #727373 from? I'm not able to find something about it..

But back to your ticket: Can we close this issue or are there more questions/issues about it to expect?

[Mark Daems]

Michael,
We don't have information about this issue. It's the sample you posted above that's named so.
This ticket shouldn't be solved immediately. I'm currently mailing with Marco about the fpc versions that don't need the WITH_FPC_FTTIME_BUG define anymore. This may be fpc 2.6.2. (Not clear yet, still checking the details).
Then I hope Jacq can tell me exactly which fpc version he's using. Official 2.6.0 or a more recent build that already contains the fttime fields patch.

Mark

[EgonHugeist]

Mark,

sorry for my stupid quetion. I didn't see the second page and your responce with Jaqc.

It was me who pointed LudoB to the time-field issue in the past. See http://zeos.firmos.at/viewtopic.php?t=3534

Assign a time-Field with a string did currupt the TDateTime-values.

AFAIK is this issue only solved with FPC2.7.1 http://bugs.freepascal.org/view.php?id=22371
AFAICS MarcoVV didn't commit to patch to FPC2.6.2 too.

Michael

[Jacq]

Mark,
i'm using Lazarus 1.0.4, it includes fpc 2.6.0

Egon,
time-field issue is not fixed in mine fpc. But it probably does not affect time setting by method "asDateTime"

[EgonHugeist]

Jacq,

with FPC 2.6.0 this issue isn't solvable. As far as i understand Mark does he want to know if the issue is solved with FPC2.6.1 or FPC2.6.2 if released. We know this issue is solved with FPC2.7.1.
Imagine the FPC SQLDB Components using the TBufDataset and Zeos uses the T(Wide)DataSet for it's components.

Do i understand you right: Assign the TTimeField with a TDateTime value also corrupts the Data?

Anyway currently we do no longer talk about a Zeos issue IMHO. Is your ticket reason solved? Can we close this ticket?

[Mark Daems]

Michael,
I only partly agree. It's true we can't be held liable for FPC errors in 'old' releases. But this is the current FPC release, so a little effort can be expected from our team. When we have the time, ...
From Ludo and Marco I heard the define is needed for all fpc versions below 2.6.2. I already modified the code in testing7.1.
I have fpc 2.6.0 available here, so I can try with the #727373 test if it really makes a difference when we uncomment the DEFINE for fpc 2.6.0.

[EgonHugeist]

Mark,

yes i know what you want and which issue Jaqc currently has. The reason for my question to close this ticket is the purpose of this ticket:
"ZUpdateSQL no recognize parameters. Example included."

Also have i realized your FPC-verison Patch for that define but as long Jaqc is using the current stable FPC2.6.0 we can't handle the new issue we're talking about. Your patch is completely right for the coming stable FPC relases but how can we solve an non Zeos issue for an Compiler which currently is in production?

'#727373 is a code snipet if attached to show him the "how to" for our TZUpdateSQL-Component but it was a random thing that this test includes code-lines for TTime-Fields too.

IMHO this ticket closed. Now we've got a new thread which is a new ticked or am i wrong? Imagine the little http://zeos.firmos.at/viewtopic.php?t=3427 topic which was a thread to collect some testers accordingly the encoding stuff. And what was happen? ((:

[Jacq]

WITH_FPC_FTTIME_BUG define is not perfect beacuse it solves .asString and brokes .asDateTime this topic is for new ticket or to forget.

The main topic of this ticket: "Parameters" is not solved, but i do some tests and found something. When memo2 is not in select statement. (and more specifically memo2 is not in result dataset)
ZQuery1.SQL.Text:='SELECT x, memo1 FROM test';
Then assigning params values working well. Even without setting DataType and ParamType.
ZUpdateSQL1.Params.ParamByName('memo2').AsString:='BeforeInsertSQL';

I using zeoslib 7.0.3-stable

[EgonHugeist]

Hi Jaqc,

I don't understand why you're going on using the syntax of your attached example. Didn't my code-snippets show how to work with the TZUpdateSQL-Component?

Making the Parameter-Way possible by using this Component and the Parameters i think this is a feature request thought.

Cheers, Michael

[Jacq]

Michael,

I'm working with events. I'm just trying assign value of parameters that are in statement of TZUpdateSQL component. Initially I expect behavior analogically as TZQuery. I want assign values at last just before post (ie before execute insert statement). In which event of TZUpdateSQL should I do this?
Temporarily I'm use Datasource1 OnUpdateData event to assign some fields

:::pascal
if Datasource1.State = dsInsert then
Datasource1.DataSet.FieldbyName

PS: How to highlight pascal sources?

--
Cheers, Jacq

[EgonHugeist]

Jacq,

sorry for the delay. I don't know how to hightlight source-code snippets.

Ok you use Events, i understand. But tell me why don't you use the parameters of TZQuery? I can't see a relation between Events and the Parameters. Nothing prevents you to use the events and the Parameters of the ZDataSet component which uses the TZUpdateSQL. Get the Parameters of TZUpdateSQL running is a feature request for me. Yes ok some people running into the same issue like you, but after a small advice 'Use the parameters of the TZDataSet instead' everything was fine. Can you attach an example which clearly shows me that this is NOT possible?

Cheers, Michael

[EgonHugeist]

Still no reply for his feature request.

I'll close this ticked in a periode of two weeks if nothing happens.

Michael