Solved

SQL Server Post Problem

Posted on 1998-05-20
10
253 Views
Last Modified: 2013-11-23
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.
0
Comment
Question by:cmain
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 5

Expert Comment

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

JB
0
 
LVL 1

Expert Comment

by:Greedy
Comment Utility
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
 
LVL 3

Accepted Solution

by:
Pegasus100397 earned 120 total points
Comment Utility
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
 
LVL 1

Expert Comment

by:Greedy
Comment Utility
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
 
LVL 1

Expert Comment

by:Greedy
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:Greedy
Comment Utility
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
 
LVL 1

Author Comment

by:cmain
Comment Utility
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
 
LVL 3

Expert Comment

by:Pegasus100397
Comment Utility
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
 
LVL 1

Author Comment

by:cmain
Comment Utility
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
 
LVL 3

Expert Comment

by:Pegasus100397
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now