Inserting auto increment records through ADO

[jensrodi]

I've run into a problem when I try to do the following on a Access table through ADO:

Code: Select all

Insert;
FieldByName('Name').AsString := 'A name';
Post;

At the 'Post' line I get the message: "You tried to assign the Null value to a variable that is not a Variant data type"

The table I want to insert into is very simple (got a index and two text fields), but it has an auto increment key field and that seems to be the problem. If I change to a SQLite database instead and do the exact same, it works without problems.

I'm using ZeosLib 6.6.4-stable but have also tried some of the 7.0.0 test builds with the same result. After doing some debugging I came up with the following fix by adding the line "and (not Metadata.IsAutoIncrement(I))" in DefineInsertColumns in the ZGenericCachedResolver class:

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 (not Metadata.IsAutoIncrement(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;

This works for me but I'm a bit reluctant to do such changes as I would have to maintain that when I eventually update to a newer ZeosLib version.

Have anybody similar experiences? If so, is there a more 'correct' way through? Is there some setups that I've missed?

[mdaems]

Hi,

Is it possible to set these autoincrement fields to a value manually using ADO? (I mean override the automatic incremented value)

Because your solution doesn't work right for databases that allow value insertion into an autoincrement field. (eg. mysql allows setting a value manually for such fields)

If ADO does not allow this, we can solve the problem by making the autoincrement columns return false for Metadata.IsWritable(I) for ADO connections.
However if ADO allows you to enter values for an autoincrement field, we should solve this in a more complex way.
- Add a property IsAutoIncrement to TZResolverParameter and fill it with the metadata information
- Add a boolean parameter tot the CopyResolverParameters with a name like 'suppressnullautoincs' and implement that action.
- Change the calls to CopyResolverParameters. I believe the new parameter should only be true when the insert statement is created.

Mark

[jensrodi]

I've been able to manually assign the autoincrement field with a number during my debugging of this problem and that works. But it kind of negates the hole idea of an autoinc, doesn't it?

I use the autoinc feature to get a unique key for each record (guess that's the most common use) leaving the work of making sure the value is unique to the underlying database. Though I'm not a hardcore database programmer, I cannot see any reason to manually assign a autoinc field.

I think I'll stick with my 'workaround' for now. It should work for all the supported databases, as long as you don't want to assign the autoinc value during an insert.

Will the more complex solution you suggested be a part of a future ZeosLib?

Jens

[mdaems]

Will the more complex solution you suggested be a part of a future ZeosLib?

It would if somebody would like to write and test it... That's what I hoped you would do
I suppose at the moment you're the one whose having the experience. So I wrote a proposal for change. If you can do and test it it will certainly be part of Zeoslib 7. Maybe even for version 6.6.X but that depends on how intrusive the final solution will be.

Mark

[jensrodi]

I surpose I could look into it when I get the time. The change it self would of course require relatively little time, but it'll take much more to get the level of understanding of how ZeosLib works to confidently do the change - at least that's my impression - ZeosLib is a rather big and complex thing.

Jens

[Michael]

Hi Mark,

just dropped in and want to answer your question concerning the possibility of writing to an identity (aka: "Auto-Inc")-column, manually on an MS SQL Server (via ADO): Yes, you can! ... You just have to ...

• [font=Courier New]SET IDENTITY_INSERT tblTable ON[/font]
• Do your insert via INSERT-Statement naming all the columns you want to insert data into. Don't forget the identity-column ... like ...
  
  [font=Courier New]INSERT INTO tblTable (Identity, Text)[/font]
  [font=Courier New]VALUES (4711, 'A TestText')[/font]
• [font=Courier New]SET IDENTITY_INSERT tblTable OFF[/font]

And you're done !

The Next Insert that is done regularly will then increase the identity to 4712.

@Jens: A better practice (imho) would be to achieve the Insert using a "simple" INSERT-Statement omitting the identity column so the increment is done automatically as it should be done. A simple TZQuery component and an "ExecuteSQL" would do the job ...

Bye for now.

Michael.

[mdaems]

Hi Michael,

This might be a little bit off topic.
The problem here is that autoinc fields are explicitly added to the generated insert statement, even when their value is null. So breaking the functionality of our TZGenericResolver in case the autoinc column was in the dataset, but not provided by the user.
On mysql this works fine as there a null value is accepted as 'please, server, autoincrement for me'. But even there it would be better to skip the field when there's no explicit value provided.
The solution by Jens actually does this, but doesn't allow for a value to be provided explicitly. My proposal allows both situations. So it would also be possible to do this manual insert for ADO as well, provided you fire a 'Set identity_insert...' statement before updating.

Mark

[guessme]

Hi Mark,
I'm facing the same problem with Jen. I have a table with one AutoIncrement-PrimaryKey field and two other text fields. I'm using Access/ADO connected using Zeos from my Delphi app.

I tried to use ZQuery combined with ZUpdateSQL to connect the table to a grid. I've written the corresponding SQL statements for Delete, Insert, Modify and RefreshSQL properties.
The problem is, I got the following exception message after posting a new record into the dataset:

EOleException
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.


ZQuery.SQL property:
SELECT RecNo, ProductCode, Description FROM Product

ZUpdateSQL.InsertSQL property:
INSERT INTO Product (ProductCode, Description)
VALUES (:ProductCode, :Description)

ZUpdateSQL.ModifySQL property:
UPDATE Product
SET
ProductCode=:ProductCode,
Description=:Description
WHERE
RecNo=:RecNo


ZUpdateSQL.RefershSQL property:
SELECT ProductCode, Description FROM Product
WHERE RecNo=:RecNo


I believe this is caused by the RefreshSQL. Because the AutoInc field new value after the record has been inserted into the table does not visible to Zeos component. Thus when RefreshSQL got executed the WHERE clause becomes:
WHERE RecNo=NULL
therefore it raised the exception above.

I still can't make this work now. Did I miss something here?


Eddy

[mdaems]

Sorry Eddy,

But the problem with refreshSQL for a newly inerted record is that the primary key isn't available indeed. I'm afraid there's no solution for this available, unless ADO provides a last_insert_id like function that can be used to fill lthe autoincremented value in the dataset (the way it works for mysql).

Mark

[davy]

"SELECT @@IDENTITY" returns the last auto-numbered value

[davy]

Hi Michael,

your explanation about the IDENTITY_INSERT is right,
but it doesn't working using the ado provider.
It does work using the older (dblib) mssql provider.

It used the SET IDENTITY_INSERT ... ON with the ado provider
sql server returned "Cannot insert explicit value for identity column in table .... when IDENTITY_INSERT is set to OFF"

I run a profiler on the sql server and noticed the "SET IDENTITY_INSERT..." is executed, but all sql statements are executed on the server using:
EXEC SP_PREPEXEC ....

example:
declare @p1 int
set @p1=573
exec sp_prepexec @p1 output,NULL,N'INSERT INTO STRINGS (ID, LANGUAGEID, STRINGID, STRVALUE, INFO, CREATEDON, CREATEDBY, UPDATEDON, UPDATEDBY, UPDATES, ACCESSEDON, ACCESSEDBY, ACCESSES, STATUS) VALUES (7345, 83, 2714, ''Welcome'', NULL, ''20061115120100'', 1, ''20061030120100'', 1, 1, ''20061030120100'', 1, 1, 1)
'
select @p1
_______________________________________
Why is exec sp_prepexec used?


Kind regards,
Davy