Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Post Problem

Posted on 1998-05-20
10
Medium Priority
?
272 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
ID: 1346710
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
ID: 1346711
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 480 total points
ID: 1346712
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:Greedy
ID: 1346713
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
ID: 1346714
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
 
LVL 1

Expert Comment

by:Greedy
ID: 1346715
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
ID: 1346716
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
ID: 1346717
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
ID: 1346718
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
ID: 1346719
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

963 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