SQL Server Post Problem

I have a SQL Server problem.

I am using a TTable connected to a TDatabase all on a data module.
When connected to a paradox database table with the following structure,
the code works.

TABLE STRUCTURE
IncrementalValue is an auto incrementing field.
MyField1 is an integer
MyField2 is a string length 100

var
  nKey : Integer;

begin
  MyTable.Append;
  MyTable.FieldByName('MyField1').AsInteger := 10202;
  MyTable.FieldByName('MyField2').AsString := 'This is a good question';
  MyTable.Post;
  nKey := MyTable.FieldByName('IncrementalValue').AsInteger;
end;

With a paradox database nKey will contain the value that the database has assigned to the auto incrementing field.

With a SQL Server 6.5 database using the ODBC driver (please don't ask why - I just need an answer to the question) nKey will always contain zero.
The question is:
Using the BDE API or the table object or whatever, how do I get the auto increment value from the SQL server table just after adding the record?

Regards
Craig.
LVL 1
cmainAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimBob091197Commented:
Does "MyTable.Resync([])" or "MyTable.Resync([rmExact])" make any diff?  Do the rest of the fields (MyField1 & MyField2) contain the correct values?

JB
0
GreedyCommented:
I am assuming that you know how to set up the auto increment field in the MSSQL table.  (Identity is checked)

Instead of doing the Append do this

Table1.InsertRecord(['', 10202, 'This is a good question']);
Table1.Refresh;

The Append inserts the record but then clears all the fields.  I get a "Field 'IncrementalVaule' need a value" error when I try it.  

Damn this doesn't exactly answer your question...it might get you by but I see a problem.  The value for the newly inserted record doesn't really become visible until you do the refresh on the table and that sets it back to the first record.  You could use bookmarks but that's not what you (or I) want. This kinda sucks because the autoincrement is probably your primary index for this so you can't do a FindKey to get back...Need to find a way to get that SQL server to tell us what the next value of the AutoIncrement is going to be.  I bet there is a way because in the help it has this for Identity:
Enables columns to contain system-generated values that uniquely identify each row within a table. When inserting values into a table that has an identity column, SQL Server automatically generates the next identifier based on the last used identity value and the increment value specified when the column was created.  

So I'd bet there is a way to ask the server for this info...maby try Mirco$haft's web page.
0
Pegasus100397Commented:
Cmain,

   Unfortunately this is happening to EVERYONE that uses an AutoInc field in SQL Server. Here is the reason:

When the record is posted SQL Server assigned a unique ID, which is cool, but what it DOESN'T do is return that ID (remember, it was a POST, not a select, hence no value returned).

The only solutions I have found are:

  Post the record, then immediately do a Refresh, then LAST statement to get to the last record posted. The TTable dataset will then have retrieved the newly assigned value.

If you can't afford the overhead of doing this, then have a "generic" TQuery that has a Select Max(IdentColumn) from tablename statement in it. When you post, you can then immediately run this query and get the last value assigned (some concurrency issues here, but...).

It's not pretty, but it works. Hopefully the next version of the BDE will address this problem.

Good luck with your project!
Pegasus
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

GreedyCommented:
I'm only getting around 12 inserts a second with this code.  Does anyone know how to tell the MSSQL server to reset it's counter...like if you delete all the recodrs out...see it's the fact that it knows what that number is even when you delete out all the records that makes me think this value should have a access method...I'll try this insert with the query and see if it'll speed up.

procedure TForm1.Button1Click(Sender: TObject);
var
  i : integer;
begin
  Table1.DisableControls;
  for i := 1 to 100 do
  begin
    Table1.InsertRecord(['', 102002, 'Question is good']);
    Table1.Refresh;
    Table1.Last;
    Label1.Caption := IntToStr(Table1.FieldByName('IncrementalValue').AsInteger);
    Application.ProcessMessages;
  end;
  Table1.EnableControls;
end;

0
GreedyCommented:
check out the IDENTITY Property help page in the Transact-SQL Reference Help...it has all kinds of stuff on how to read this value.  I think you'll have to use a query component...I'm still readding it myself.
0
GreedyCommented:
It makes it much better to use the query
the following code never took more than 2 seconds

procedure TForm1.Button1Click(Sender: TObject);
var
  i : integer;
begin
  Table1.DisableControls;
  for i := 1 to 100 do
  begin
    Table1.InsertRecord(['', 102002, 'Question is good']);
    Query1.Active := False;
    Query1.Active := True;
    Label1.Caption := IntToStr(Query1.Fields[0].AsInteger);
    Application.ProcessMessages;
  end;
  Table1.EnableControls;
end;

I used SELECT MAX(IDENTITYCOL) FROM MyTestDB for my SQL property

I wonder if it's possible to figure out the value of @@IDENTITY with a query component?  Because I can do this

INSERT MyTestDB DEFAULT VALUES
select @@IDENTITY

with ISQL but I can't figure out how to read that value in DELPHI.

0
cmainAuthor Commented:
This is actually most annoying.
I just want to ask one small thing before I grade this question. I am not really happy but here is the question.

Are you guys sure that you cannot refresh the contents of the current table object using the BDE API. I have a suspicion that a solution like that would be better than the query option. I say this because then the database cursor would remain unmoved (on the current record). The TQuery solution could go wrong in severe multi user conditions. It's inherently dangerous.

If there is no other way, then I guess I'll have to settle for it.
0
Pegasus100397Commented:
Cmain,

   I've been working with Delphi since version 1.0 and have yet to see Borland come out with a fix for this bug. Believe me, I use Delphi for C/S app development in a Fortune 500 company and this bug annoys me a great deal. I have yet to be able to refresh only the "current" record because the "image" that exists in memory of the posted record (without the ID) does not match that of the record stored in SQL Server (with the new ID).

  The "cleanest" multiuser workaround I've found is a stored procedure on the server that gives ME the next available number, I slap in on the record, then post it. This assures me that the cached (client side) record and the posted SQL Server record are indeed the same. The mini-table containing the "next available number" is locked during the get/increment phase to insure no two people get the number.

Micro$oft has the stored procedure on thier website to address this issue.

It's not pretty, I'm not happy about it, but I play the hand that Borland & Micro$oft gave me. Good luck with your project and let me know if I can be of further assistance.

Pegasus
0
cmainAuthor Commented:
Pegasus,

Thanks for the response.
I have graded your answer. Perhaps you would be so kind as to mail me the URL for the work around stored procedure at the microsoft site, if you still have it.
email : craig-m@iafrica.com

I appreciate your response. Lets hold fingers for the Delphi 4.00 release.

Thanks
Kind Regards
Craig.
0
Pegasus100397Commented:
Craig,

   I'll mail you the URL for the stored procedure BUT...

I recently asked a "Record/Key Deleted" related question and was given a "fix" for it that seems to also have solved the autoincrement problem. It may be worthwhile for you to give it a try:

Set the BDE Sybase (or SQL Server) Language driver to Sybase SQL Dic850 in the BDE

Let me know if it improves your application's relationship with SQL Server, it certainly did on my project!

Regards,
Pegasus
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.