ZQuery Insert/Update problem with MSSQL

[nayamda]

Hi,

i'm connected to MSSQL_Server via ZEOS Version 6.6.2-RC.
When I send direct Statements to the Server everything is fine:

Code: Select all

zquery.sql.text := 'SELECT * FROM TEST';
zquery.open;

or even

Code: Select all

zquery.sql.text := 'DELETE FROM TEST WHERE bla';
zquery.execsql;

But when i try to insert or append like:

Code: Select all

zquery.insert;
FieldByName('test').AsString := 'Hallo';
zquery.Post;

Nothing happens!
There is no error or stuff. It is simply not inserted.
It even doesn't update any Record.

Is this a Bug? Or even a wrong setting?

----------
ZConnection:

AutoCommit: True;
ReadOnly: False;
SQLHopurGlass: False;
Tag: 0;
TransactIsolationLevel: TiReadCommitted;

ZQUery:

AutoCalcFields: True;
CachedUpdates: False;
Options: [doCalcDefaults, doAlwaysDetailResync];
ParamCheck: True;
Params: empty
ReadOnly: False;
ShowRecordTypes: [usUnmodified,usModiefied, usInserted];
Tag: 0;
UpdateMode: umUpdateChanged;
UpdateObject : ;
WhereMode: wmWhereKeyOnly

[btrewern]

Try using the TZSQLMonitor to see what queries are being sent to the server.

Ben

[Michael]

Hi nayamda,

I tested this against an MS SQL Server 2005 Express and got the same result ... To get it going I suggest to deploy a TZUpdateSQL for your query. This will work. It is a better practice, anyway, (and I recommend it) to use a TZQuery with a connected TZUpdateSQL in order to have a better control over INSERT/UPDATE and DELETE.

Best Regards!

[nayamda]

Hi Michael,

ich antworte mal auf deutsch, ich hoffe, dass ist kein Problem.

ein TZUpdateSQL noch mit einzubauen ist für uns leider keine geeignete Lösung.
Wir haben eine Software, die mit Firebird 2.0 und MSSQL arbeiten soll.
Mit Firebird funktioniert ja auch alles soweit und mit den "alten" Zeos hat das auch mit MSSQL funktioniert. Da wir momentan unsere neue Version verschicken wollen wollten wir jetzt nicht noch kurz vor knapp alles umstellen und erneut durchtesten.
Außerdem finde ich diese UpdateSQL-Kompo nicht so wirklich gut, da ich alle geänderten Datenbankfelder manuell einpflegen muss, damit alles gut funktioniert. Hier haben mir die Möglichkeiten einer normalen ZQuery völlig ausgereicht.

Wenn das noch ein Problem in den ZEOS-Kompos ist, wie stehen die Chancen, dass das behoben wird und wie schnell könnte das sein?

Soweit ich das mit dem SQLMonitor sehen konnte werden schon allein die Querys nicht an die DB übertragen.

[mdaems]

Michael,
nayamda,

This sounds like a problem that should be fixed indeed. Usually this kind of problems isn't that difficult to debug. I would start with a debug point at the top of TZGenericCachedResolver.PostUpdates. (or if there exists a protocol dependent version : that version. eg. for dblib there's TZDBLibCachedResolver.PostUpdates) Unfortunately,I don't use mssql. It may also be necessary to know
- what mssql server version is used
- which protocol is set in the connection (ADO,mssql,???)
- what connectivity dll version is used

Some clarification needed:

Soweit ich das mit dem SQLMonitor sehen konnte werden schon allein die Querys nicht an die DB übertragen.

Does this mean the updates are in the Monitor log or not?

Mark

[nayamda]

Hi Mark,

i've tried it with MS SQL Server 2005 and Michael has tested it against MS SQL Server 2005 Express.

I use ADO as protocol with SQLOLEDB.1 as provider.
I can't say what dll version is used.

Sorry for that german. The inserts and the updates are not in the Monitor log.
There are only the statements which i send direct to the database.

[mdaems]

Kein Problem. German is the third language in Belgium (and quite similar to dutch). So I understand it quite well...

So, can you try to debug TZAdoCachedResolver.PostUpdates (Even the fact the code is never executed is useful information)? And eventually TZAdoStatement.GetCurrentResult to check if the CachedResolver is actually created in your case.

Mark

[nayamda]

I poorly understand your procedures so it's hard for me to debug *g*,
but i'll give it a try.... tomorrow.

thx so far.

[nayamda]

Ok, let's see what we get.

I'll get into the procedure TZAdoCachedResolver.PostUpdates and the UpdateType is utInserted, what seems to be ok. The FAutoColumnIndex is not bigger than 0 and OldRowAccessor.IsNull(FAutoColumnIndex) is false. Whatever it means.

So the condition:

Code: Select all

if (UpdateType = utInserted) and (FAutoColumnIndex > 0)
    and OldRowAccessor.IsNull(FAutoColumnIndex) then
  begin
    Recordset := FHandle.Execute(RA, null, 0);
    if Recordset.RecordCount > 0 then
    {$IFNDEF VER130BELOW}
      NewRowAccessor.SetLong(FAutoColumnIndex, Recordset.Fields.Item[0].Value);
    {$ELSE}
      NewRowAccessor.SetLong(FAutoColumnIndex,
        Integer(Recordset.Fields.Item[0].Value));
    {$ENDIF}
  end;

won't be executed.


The procedure TZAdoStatement.GetCurrentResult is also executed and the AdoRecordset is assigned. AdoRecordSet.State and adStateOpen is both adStateOpen. The ResultSetConcurrency is rcUpdateable, so the LastResultSet should be a TZCachedResultSet.

Code: Select all

LastResultSet := TZCachedResultSet.Create(NativeResultSet, SQL, TZAdoCachedResolver.Create((Connection as IZAdoConnection).GetAdoConnection, Self, NativeResultSet.GetMetaData))

I hope it helps.

[mdaems]

I'll get into the procedure TZAdoCachedResolver.PostUpdates and the UpdateType is utInserted, what seems to be ok. The FAutoColumnIndex is not bigger than 0 and OldRowAccessor.IsNull(FAutoColumnIndex) is false. Whatever it means.

It means at least the resolver tries to post the insert/update/delete statements.
This happens in the first line of the procedure

Code: Select all

inherited PostUpdates(Sender, UpdateType, OldRowAccessor, NewRowAccessor);

The conditions that follow just handle the case where 'autonumber' columns must be fetched from the server again to fill these values in the cached dataset after the server assigned a value.

So next thing we should study is TZGenericCachedResolver.PostUpdates.

Code: Select all

{**
  Posts updates to database.
  @param Sender a cached result set object.
  @param UpdateType a type of updates.
  @param OldRowAccessor an accessor object to old column values.
  @param NewRowAccessor an accessor object to new column values.
}
procedure TZGenericCachedResolver.PostUpdates(Sender: IZCachedResultSet;
  UpdateType: TZRowUpdateType; OldRowAccessor, NewRowAccessor: TZRowAccessor);
var
  Statement            : IZPreparedStatement;
  SQL                  : string;
  SQLParams            : TObjectList;
  lUpdateCount         : Integer;
  lValidateUpdateCount : Boolean;
begin
  if (UpdateType = utDeleted)
    and (OldRowAccessor.RowBuffer.UpdateType = utInserted) then
    Exit;

  SQLParams := TObjectList.Create;
  try
    case UpdateType of
      utInserted:  //---> create an update statement based on the old and new row values
        SQL := FormInsertStatement(SQLParams, NewRowAccessor);
      utDeleted:
        SQL := FormDeleteStatement(SQLParams, OldRowAccessor);
      utModified:
        SQL := FormUpdateStatement(SQLParams, OldRowAccessor, NewRowAccessor);
      else
        Exit;
    end;

    if SQL <> '' then  //---> what's in SQL??
    begin
      Statement := Connection.PrepareStatement(SQL);
      FillStatement(Statement, SQLParams, OldRowAccessor, NewRowAccessor);
      // if Property ValidateUpdateCount isn't set : assume it's true
      lValidateUpdateCount := (Sender.GetStatement.GetParameters.IndexOfName('ValidateUpdateCount') = -1)
                            or StrToBoolEx(Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);

      lUpdateCount := Statement.ExecuteUpdatePrepared;  //---> here the work should be done....
      if  (lValidateUpdateCount)
      and (lUpdateCount <> 1   ) then
        raise EZSQLException.Create(Format(SInvalidUpdateCount, [lUpdateCount]));
    end;
  finally
    SQLParams.Free;
  end;
end;

See my comments with '//--->'.

Mark

[nayamda]

Code: Select all

// if Property ValidateUpdateCount isn't set : assume it's true
      lValidateUpdateCount := (Sender.GetStatement.GetParameters.IndexOfName('ValidateUpdateCount') = -1)
                            or StrToBoolEx(Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);

      lUpdateCount := Statement.ExecuteUpdatePrepared;  //---> here the work should be done....
      if  (lValidateUpdateCount)
      and (lUpdateCount <> 1   ) then
        raise EZSQLException.Create(Format(SInvalidUpdateCount, [lUpdateCount]));

These lines were not in my procedure?!
But also the SQL-String is empty, so

Code: Select all

SQL := FormInsertStatement(SQLParams, ewRowAccessor);

provides an empty string.

Within the FormInsertStatement there are the lines

Code: Select all

  DefineInsertColumns(Columns);
  if Columns.Count = 0 then
  begin
    Result := '';
    Exit;
  end;

and Columns.Count is in this case 0.

In the procedure DefineInsertColumns Metadata.GetTableName(I) provides the right tablename and Metadata.GetColumnName(I) provides also the right column, so the metadata seems to be correct. But Metadata.IsWritable(I) is never true, so the InsertColumns objectlist keeps empty.

Code: Select all

procedure TZGenericCachedResolver.DefineInsertColumns(Columns: TObjectList);
var
  I: Integer;
begin
  { Precache insert parameters. }
  if InsertColumns.Count = 0 then
  begin
    for I := 1 to Metadata.GetColumnCount do
    begin
      if (Metadata.GetTableName(I) <> '') and (Metadata.GetColumnName(I) <> '')
        and Metadata.IsWritable(I) then
      begin
        InsertColumns.Add(TZResolverParameter.Create(I,
          Metadata.GetColumnName(I), Metadata.GetColumnType(I), True, ''));
      end;
    end;
  end;
  { Use cached insert parameters }
  CopyResolveParameters(InsertColumns, Columns);
end;

[mdaems]

No problem about the missing lines. That's a new addition in SVN versions.

But this brings me to the point : what zeoslib version are you using? Because there was an ado fix because of post http://zeos.firmos.at/viewtopic.php?t=1769.

To see the changes of this fix : http://fisheye2.atlassian.com/changelog/zeos?cs=352

Please verify if your code contains this fix. It's not in 6.6.2 and SVN revisions below 352 (Testing branch) or 361 (Trunk).
If the fix is included already we can continue with the text below.

Seems like the current code doesn't interpret the Metadata from the server right.

If I'm correct this should be retrieved using the TZAdoDatabaseMetadata.GetColumns function.
What happens at this line ?

Code: Select all

          Result.UpdateBooleanByName('WRITABLE',
            (Flags and (DBCOLUMNFLAGS_WRITE or DBCOLUMNFLAGS_WRITEUNKNOWN) <> 0)); 

?

[nayamda]

I'm using the official version 6.6.2 RC.

This code doesn't contain this fix.
I've inserted this fix into my code and it works!

Thx alot for all that help.

Can you close the Bugtracker issue, please?!

http://zeosbugs.firmos.at/view.php?id=121