DBGrid - ADO (MSSQL 2005, MySQL 5)

[aperger]

Hi,

I have a problem. I use a TDBGid component with a TZTable, TDBNavigator and a TDataSource in my project (in a utility program). I use this utility tool to correct some value directly in the database.
Key-Settings:
ZTable1.ReadOnly = False;
DataSource1.AutoEdit = True;
DataSource1.Enabled = True;
DBGrid1.Options contains "dbEditing";
all "ZTables1XXXFILED".ReadOnly = False;

but when I edit a record and save it with DBNavigator1 (nbPost button), it looks ok, but if I refresh the grid, or restart the program the edited record is not changed.

What I am doing wrong? Thanks for your help.

Attila


A previous release of ZeOS Lib was working (2007.02.11???): I forgot to save this revision and I forgot the version. Something was changed, but I do not know what!

[mdaems]

Hi,

Is this a db specific problem? ADO/MSSQL only or can I try it with mysql as well? (I don't have MSSQL)
If I can test it using mysql, please send a table/data creation script in plain sql.
If it's ADO/MSSQL specific, can you provide the necessary information for others to test it? I hope you'll find somebody...

Did you try to check if the update statement is fired by using a TZSQL monitor?

Mark

[aperger]

Hi,

Sorry... I need some time to test what you offer me... So... My SmartStorage application is running on PostgreSQL 8.0, FireBird 1.5-2.0, and MSSQL 2000-2005. I have no problem 'till yesterday, but now on MSSQL (with ADO protocol) I can not use TDBGrid. I have tested it today.
It looks me, it is an ADO/MSSQL specific problem. I attache to you my MySQL backup in a ZIP file, but on MySQL I can not find this problem, but I don not try (an idea at the moment :-)) with ADO protocol with MySQL (I will).

Attila

Code: Select all

CREATE TABLE TBIZONYLATFEJ
(
  FAZONOSITO	INTEGER NOT NULL  AUTO_INCREMENT,
  FBIZTIP       INTEGER NOT NULL,
  FSTATUS       INTEGER NOT NULL,
  
  FCEGNEV VARCHAR(100) NOT NULL,
  FCEGORSZAG VARCHAR(100),
  FCEGIRSZ VARCHAR(70),
  FCEGTELEP	VARCHAR(100) NOT NULL,
  FCEGUTCA VARCHAR(100) NOT NULL,
  FCEGTEL	VARCHAR(70),
  FCEGFAX	VARCHAR(70),
  FCEGEMAIL	VARCHAR(100),
  FCEGBANKSZLA	VARCHAR(100),
  FCEGADOSZAM	VARCHAR(100),

  FVEVONEV VARCHAR(100) NOT NULL,
  FVEVOORSZAG	VARCHAR(100),
  FVEVOIRSZ	VARCHAR(70),
  FVEVOTELEPULES	VARCHAR(100) NOT NULL,
  FVEVOUTCA	VARCHAR(100) NOT NULL,
  FVEVOTELEFON	VARCHAR(100),
  FVEVOFAX	VARCHAR(100),
  FVEVOEMAIL	VARCHAR(100),
  FVEVOBANKSZLA	VARCHAR(100),
  FVEVOADOSZAM	VARCHAR(100),

  FFORMATUM	INTEGER NOT NULL,

  FSZEV                    INTEGER NOT NULL,
  FSZSZAM	INTEGER NOT NULL,
  FKIALLDATUM	TIMESTAMP NOT NULL,
  FFIZDATUM	TIMESTAMP,
  FTELJDATUM	TIMESTAMP,
  FFIZMOD	VARCHAR(70),
  FTIPUS	INTEGER,
  FKEDVEZMENY	FLOAT,
  FIDO	TIMESTAMP,
  FUSER	VARCHAR(70),
  FMEGJEGYZES	VARCHAR(255),
  FRENDSZAM	VARCHAR(70),
  FMEGRENDELES	VARCHAR(70),
  FHIVATKOZAS	VARCHAR(70),
  FNYOMTATVA	INTEGER,
  FPELDANY	INTEGER,
  FVALUTA VARCHAR(10),
  FSTORNOSZLA	INTEGER,
  FSTORNOKIALLDATUM	TIMESTAMP,
CONSTRAINT BIZONYLATFEJ_AZONOSITO PRIMARY KEY (FAZONOSITO),
CONSTRAINT BIZONYLATFEJ_KOD UNIQUE (FBIZTIP,FSZEV, FSZSZAM)
)

[aperger]

Hi All,

I did some test with my program (the previous test program is almost same). I tried to connect to a local MySQL5 server with different protocols.
1. mysql-5
2. ado ("Provider=MSDASQL.1;Persist Security Info=False;Data Source=SmartStorage14MySQL")

I got interesting result:
I find some problem with ADO protocol, too. Not just with MSSQL. (MySQL ODBC driver v. 3.51.22, MySQL 5.0.45).

The table looks empty with ADO and MySQL ODBC driver!


Please check the attached pictures.

Attila Perger

[aperger]

2. picture

[aperger]

3. picture

[aperger]

Hi again,

I tried to use the TZSQLMonitor components. I got the following result, when I want to modify a filed in a DB Grid (ADO protocol and MSSQL 2005):

Code: Select all

11:01:04 - SELECT * FROM TBIZONYLATFEJ
11:00:48 - SELECT * FROM TBIZONYLATFEJ
11:00:48 - SELECT * FROM TRIPORTOK
11:00:48 - SELECT COUNT(*) AS Mennyiseg FROM TBIZONYLATFEJ
11:00:48 - SELECT COUNT(*) AS MENNYISEG FROM TANYAGMOZGAS
11:00:47 - SELECT * FROM TUSERS WHERE FUSERID = ? AND FPASSWORD = ?
11:00:46 - SELECT * FROM TUSERS
11:00:45 - SELECT * FROM TRIPORTOK
11:00:45 - SELECT * FROM TBEALLIT
11:00:45 - CONNECT TO "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=SmartStorage14;Data Source=PERGER-DELL" AS USER "sa"

It looks me the UPDATE SET command was not called!
Can somebody help me to solve this?

THANKS for it.

[mdaems]

Can you debug?

Add a breakpoint in TZAdoCachedResolver.create to check if a CachedResolver Object is created during the execution of TZAdoStatement.GetCurrentResult.
If The cached Resolver is used, check if TZAbstractCachedResultSet.PostUpdates and TZAdoCachedResolver.PostUpdates are executed.

[aperger]

Hi

I did some debugging. It looks me the problem is comming from "TZGenericCachedResolver.DefineUpdateColumns" function, because the IsWritable is false at the fileds which were modified!

[aperger]

so... (continue)

If the IsWriteble is always false the number of collected UpdateColoums is zero.

Code: Select all

procedure TZGenericCachedResolver.PostUpdates(Sender: IZCachedResultSet;
  UpdateType: TZRowUpdateType; OldRowAccessor, NewRowAccessor: TZRowAccessor);
var
  Statement: IZPreparedStatement;
  SQL: string;
  SQLParams: TObjectList;
begin
  if (UpdateType = utDeleted)
    and (OldRowAccessor.RowBuffer.UpdateType = utInserted) then
    Exit;

  SQLParams := TObjectList.Create;
  try
    case UpdateType of
      utInserted:
        SQL := FormInsertStatement(SQLParams, NewRowAccessor);
      utDeleted:
        SQL := FormDeleteStatement(SQLParams, OldRowAccessor);
      utModified:
        SQL := FormUpdateStatement(SQLParams, OldRowAccessor, NewRowAccessor);
      else
        Exit;
    end;

    if SQL <> '' then // Here SQL is empty!!!!
    begin
      Statement := Connection.PrepareStatement(SQL);
      FillStatement(Statement, SQLParams, OldRowAccessor, NewRowAccessor);
      Statement.ExecuteUpdatePrepared;
    end;
  finally
    SQLParams.Free;
  end;
end;

[mdaems]

Hi Attila,

Did you check in 'TZAdoResultSet.Open' how ColumnInfo.Writable and ColumnInfo.ReadOnly are set?
My first impression from the code is that the BASECOLUMNNAME of the retrieved fields is empty.
Also have a look at 'TZAdoDatabaseMetadata.GetColumns'. There the code to set the writable flags from metadata is disabled, so the code in 'TZAbstractResultSetMetadata.ReadColumnByName' may not be able to find out if the column is updatable.

Mark

[aperger]

Thanks, I will try...