[patch_done] Have problems with ZTable.Edit & Post

Daniel775 · Post by **Daniel775** » 27.03.2012, 20:20

I changed from 6.6.6 stable to 7.0.0 alpha with Delphi 7. Now I get the some errors like

SQL Error: Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 1, column1. Error Code: -104. Invalid token.

I use Firebird 2.5.1. In 6.6.6 works this code.

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var
  i: integer;
begin

  self.ZTable1.Locate('ID', self.txtField.Text, [loCaseInsensitive]);

  for i := 0 to self.ZTable1.RecordCount do
  begin

    if (self.ZTable1.State <> dsEdit) or
      (self.ZTable1.State <> dsInsert) then
        self.ZTable1.Edit;

    if self.ZTable1.State = dsEdit then
    begin

      self.ZTable1STATUS.AsInteger := 1;
      self.ZTable1ZEITSTEMPEL.AsString := '25.04.2012 17:45';

    end;

    self.ZTable1.Post;
    self.ZTable1.Next;

  end;


  self.ZTable1.CommitUpdates;


end;

Should I call such procedures like .Prepare? There are any Changes between 6.6.6 and 7.0.0?

Best Greetings

Post by **EgonHugeist** » 27.03.2012, 20:52

Daniel775,

ZEITSTEMPEL aha ein Deutscher!

Can you tell us more detailed your configurations?
Also you can use the TZSqlMonitor to check the internal generated and failded Statements..

Du könntes den SQLMonitor dazu verwenden herauszufinden, was beim posten an den Server gesendet wird. Könntest du das eventuell anhängen? Oder debug dich ab dem InternalPost durch... Wäre auch hilfreich zu wissen, welchen Server du verwendest...

Best regards,

EgonHugeist

Daniel775 · Post by **Daniel775** » 27.03.2012, 21:38

Ja, hier ist ein Deutscher

System: WinXp_32bit, 2GB RAM, DuoCore CPU
Dev. environment: Delphi 7, ZEOS 7.0.0 alpha (used Protocol: firebird-2.1)
Database Server: Firebird 2.5.1.26351
Database Editor: EMS - SQL Manager 2010 Lite for Interbase and Firebird

My Story:
I have developed two programs on my developing Machine in the Zeos version 6.6.6 and Delphi 7. One for local access to the database and one for remote access via TCP Protocol (IP:Databasealias). The programs worked fine on my developing Machine. As i test the programs on two different machines, there was no way to make a connection via TCP string to the Database. Constantly get errors such as Access Violation at address 0000000 read 0000000 with the User sysdba and a error Message like "not enough rights on Database ..." with a new created User. I gave the User full access rights. But nothing changed! So i tried the Version 7.0.0 and the connection worked. Now I'm here with you ...

It is not very helpful i think, but here is the wanted TZSqlMonitor log:

2012-03-27 22:00:57 cat: Connect, proto: firebirdd-2.1, msg: CONNECT TO "D:\PersonaDB_BetaTest\DB\PERSONACLOCK.GDB" AS USER "sysdba"
2012-03-27 22:00:57 cat: Transaction, proto: firebirdd-2.1, msg: TRANSACTION STARTED.
2012-03-27 22:00:57 cat: Prepare, proto: firebirdd-2.1, msg: Statement 1 : SELECT * FROM NACHRICHTEN

2012-03-27 22:00:57 cat: Execute prepared, proto: firebirdd-2.1, msg: Statement 1
2012-03-27 22:00:57 cat: Execute, proto: firebirdd-2.1, msg: SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION, a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE as RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE FROM RDB$RELATION_FIELDS a JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE) LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE and c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE') WHERE a.RDB$RELATION_NAME = 'NACHRICHTEN' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION
2012-03-27 22:00:59 cat: Execute, proto: firebirdd-2.1, msg: Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 1, column 1, errcode: -104, error: Invalid token

Daniel775 · Post by **Daniel775** » 27.03.2012, 22:27

I got no SQL statement in unit: ZDbcInterbase6Utils method: PrepareStatement!

After 'self.ZConnection1.Connect;' in Event FormCreate i debug this in PrepareStatement from unit ZDbcInterbase6Utils:

first call:
Var sql:
'SELECT * FROM NACHRICHTEN'#$D#$A

second call:
Var sql:
'SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION, a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE as RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE FROM RDB$RELATION_FIELDS a JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE) LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE and c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE') WHERE a.RDB$RELATION_NAME = 'NACHRICHTEN' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION'

Why is no termination on this statement like the first calling? (#$D#$A)

and after my 'self.ZTable1.Locate('ID', self.txtField.Text, [loCaseInsensitive]);' statement in Button2Click Event i catch this in PrepareStatement method!

PlainDriver: D1 = 0, D2= 0, D3 = 0, D4 = (0, 0, 0, 0, 0, 0, 0, 0)
Dialect: 3
SQL: ''

Post by **EgonHugeist** » 27.03.2012, 23:31

Which branch do you use here?

you can get some informations concerning revisions here:
http://zeos.firmos.at/viewtopic.php?t=3431
use the 'how to' from MDeams
http://zeos.firmos.at/viewtopic.php?t=841

What i can see is that Zeos collects the field-informations for your statement to prepare the internal field informations. It seems something is wrong with the automatic generated one.

You can check the statement you've posted with FlameRobin if you doubt.

I'm using FB too and i've no problems. So can you please update your revision... Hopefully it helps you.?!

Don't think the termnation is on need..

Best regards

Daniel775 · Post by **Daniel775** » 28.03.2012, 12:59

Download and install TortoiseSVN Is that correct? What is TortoiseSVN? I read something about Apache server!

I´ve download my Version from here
https://sourceforge.net/projects/zeosli ... t/download

Should i download from https://zeoslib.svn.sourceforge.net/svn ... .6-patches?

And what is FlameRobin?

Best regards

marcov · Post by **marcov** » 28.03.2012, 14:55

Subversion is a version system, and it is hosted on Apache servers.

The most used windows client is "tortoisesvn", which is standalone (except for putty which it can use for encryption, but that isn't needed in this case)

Post by **EgonHugeist** » 28.03.2012, 21:18

Daniel775,

yes this is correct. Download&install. All yo've to know is in the threads i posted before..
Your downloaded version isn't up to date.

Did you've success?

Best regards

Daniel775 · Post by **Daniel775** » 30.03.2012, 13:18

I could not build and compile the new ZEOS package as a component. So I had to remove and reinstall Delphi 7.

It seems that the reason for this problems were the old dcu Files from ZEOS 6.6.6 in my Project folder. At the beginning I had problems with the new ZEOS lib exactly the same as with 6.6.6 version. The problems were solved after i had deleted the files in my Project folder and after i had added the search path to the new ZEOS library in Delphi.
Also, a problem apparently was that I have the protocol version used 2.1 instead of 2.5.
Is actually logical.

But I do have just another problem. ZTable.eof will never come true. The record is in edit mode, and I call in a while loop ZTable.next. ZTable.post before ZTable.next does not change anything. This worked in version 6.6.6 stable yet.

Example:

Code: Select all


    if self.ZTable1.Active = true then
    begin

       self.ZTable1.Filter := 'Personal_ID = ' +    IntToStr(self.ZTable2['ID']) +
            ' And Datum >= ' + QuotedStr(FormatDateTime('yyyy.mm.dd hh:mm',
              EncodeDateTime(nYear, nMonth, firstDayMonth, 0, 0, 0, 0))) +
            ' And Datum <= ' + QuotedStr(FormatDateTime('yyyy.mm.dd hh:mm',
              EncodeDateTime(nYear, nMonth, lastDayMonth, 23, 59, 0, 0))) + '';

          self.ZTable1.SortedFields := 'DATUM, KOMMEN';
          self.ZTable1.IndexFieldNames := 'DATUM ASC, KOMMEN ASC';
          self.ZTable1.SortType := stAscending;
          self.ZTable1.Filtered := true;

      while not self.ZTable1.Eof do
      begin

        if self.ZTable1.State <> dsEdit then self.ZTable1.Edit;
        if self.ZTable1.State = dsEdit then
        begin
        
          .....

        end;

        self.ZTable1.Next;

      end;

    end;

Daniel775 · Post by **Daniel775** » 31.03.2012, 08:05

I have a table for example with 15 records from ID 60 to 77. After filtering and sorting of records shows the DB Grid these records sorted ascending also from ID 60 to ID 77. But i have no idea why the record pointer in a for-loop jumps from ID 60 to 71, from 71 to 77, from 77 to 65 and from 65 back to 71!?

That is the reason why in my while loop ztable.eof never becomes true.

ZTable1.Filter: Personal_ID = 1 And Date >= '2012.03.01 00:00' And Date <= '2012.03.31 23:59

Code: Select all

      for i := 0 to self.ZTable1.RecordCount do
      begin

        if self.ZTable1.State <> dsEdit then self.ZTable1.Edit;
        if self.ZTable1.State = dsEdit then
        begin
       
          .....

        end;

        self.ZTable1.Next;

        // ZTable1ID 60 -> 71 -> 77 -> 65 -> 71

      end;

DB-Grid and ZTable1 has the same DataSource.

Post by **EgonHugeist** » 31.03.2012, 11:21

Daniel775,

possible issues to find out and locate:

is the issue present, if no filter is set? If you use a Zquery with select * from ZEITEN where ID >= 60 and Id <= 77 order by ID; for example?

is the issue present if you don't sort the table?

Saw you use a lot of time values. Is that thread http://zeos.firmos.at/viewtopic.php?t=3449 eventually the same problem?

Which Fields where chaged in your loop?

best regars

Daniel775 · Post by **Daniel775** » 31.03.2012, 12:40

Don´t know why but this problem is gone after i´ve recreated the table.

One problem fixed, but the next ones already in queue

Is there an more elegant option to keep the record pointer on the last edited record after post?

Example 1:
4 Records
ZConn.AutoCommit is set to True and
ZConn.TransactIsolationLevel is set to tiReadCommitted

loop1: self.ZTable1.RecNo = 1
loop2: self.ZTable1.RecNo = 2
loop3: self.ZTable1.RecNo = 3
loop4: self.ZTable1.RecNo = 4

Code: Select all


...
     frmMain.ZConn.StartTransaction;
     for i := 1 to self.ZTable1.RecordCount do
     begin

        if self.ZTable1.State <> dsEdit then self.ZTable1.Edit;
        ...

        if not self.ZTable1ID.IsNull then
          lastID := self.ZTableID.AsLargeInt;
        self.ZTable1.post;
        self.ZTable1.Locate('ID', lastShownID, [loCaseInsensitive]);

        self.ZTable1.Next;

     end;

     self.ZTable1.ApplyUpdates;
     self.ZTable1.CommitUpdates;
     frmMain.ZConn.Commit;

...

Because only Ztable.next without Ztable.post sets the record pointer to max 2. Although more than two records in Table available!? I´ve testet with and without setting a Filter!

Example 2:
4 Records
ZConn.AutoCommit is set to True and
ZConn.TransactIsolationLevel is set to tiReadCommitted

loop1: self.ZTable1.RecNo = 1
loop2: self.ZTable1.RecNo = 2
loop3: self.ZTable1.RecNo = 2
loop4: self.ZTable1.RecNo = 2

Code: Select all


...
     frmMain.ZConn.StartTransaction;
     for i := 1 to self.ZTable1.RecordCount do
     begin

        if self.ZTable1.State <> dsEdit then self.ZTable1.Edit;
        ...

        self.ZTable1.Next;

     end;

     self.ZTable1.ApplyUpdates;
     self.ZTable1.CommitUpdates;
     frmMain.ZConn.Commit;

...

ism · Post by **ism** » 31.03.2012, 13:24

Without the example project difficult to check

Daniel775 · Post by **Daniel775** » 31.03.2012, 13:57

ok, something is wrong if I change the data fields. if I comment out does it work.

Post by **EgonHugeist** » 31.03.2012, 14:21

concering your table recreating:

Zeos is allways looking for the best row identiefier like primary keys or unique indices... Is it possible that you primary key wasn't set?

Is that really true that the RecNo changed after post? Normaly that shouldn't happen. In my mind you don't need to locate the record again.

Zeos API:

We use two differnet IZResultSets internal. One with the Database-results (read-only) and one cached for you to post your data. Both are sync but using different RecNo. But the cached one points allway to the internal one. After successful posting Zeos resyncs them. So you've one editalbe with cached sorting/filtering options which is faster then DB-requests. The reason for that handling is a row/field-compare to check if realy somthing changed.

concerning your record-pointer request:

try this one:
http://zeos.firmos.at/viewtopic.php?t=3158
That solution actually isn't in our sources. Still waiting for reply of Shagrat3 ( http://zeos.firmos.at/viewtopic.php?t=3 ... c&start=15 ).

Please report your findings.

Best regards