Table.Post question

I need an explanation for the following.
I was using this code to insert into a table:

Table.Close;
Table.Open;
Table.Insert;
Table.FieldByName('One').AsInteger = X;
.
etc.
Table.Post;
.
same code repeated for second table
.

This is a simplified example but still gives the general idea.  The application using this code worked for several months.  Then started hanging on the Table.Post line of first table.  Sometimes, the data would post and sometimes in would not (usually not).  It always caused the application to stop responding and usually the machine had to be restarted.

The problem was solved using:
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add(SomeSQLStatement);
Query1.ExecSQL;

Of the two tables, the first had about 167 records and the second, 2000. What caused this?  Has anyone else ran into the same problem?  The points will go to the person who tells me what caused the problem (and provides enough details to support their answer).

Using Delphi 5 Enterprise, Windows 98 machines, SQL Server 6.5, NT server.
LVL 1
VendiAsked:
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.

AswCommented:
Hi Vendi,

I had some sort of problem like this  a while ago try.


Table.Close;
Table.Open;
Table.Insert;
TableOne.Value := X;
..
etc.
Table.Post;

Asw
0
Phoenix_sCommented:
what database are you using...

have you switched delphi versions at some point in the program's lifetime

have you upgraded the bde at all at some point

0
rwilson032697Commented:
I don't have a suggestion as to why the table.post line hangs, but shouldn't this line:

Query1.ExecSQL;

be

Query1.Open;

?

Cheers,

Raymond.
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

chengjianCommented:
I met such problems when using mssql.
I find that, if some triggers or identify fields is in the table, the problems may .
try to delete all triggers and do not use identify fields, or, use tquery instead.
0
VendiAuthor Commented:
rwilson, i believe Query1.Open is used when using a select or similiar statement (returning result sets).  However, ExecSQL works for update, insert, delete, etc. - no result set.
..
Phoenix_s, your comments are very interesting, in fact, we did switch from Delphi 4 standard to Delphi 5 enterprise during the development of this program.  (using MS SQL server 6.5)Do you have any more information on why or if it caused this problem?
0
VendiAuthor Commented:
chengjian, thanks for your proposed answer, but there are no triggers being used in this application.  Also, what exactly do you mean by identify field?  Unless you know WHY this problem is occurring, please don't answer it.  I specifically stated that the points would go to the person who could explain the cause of this problem.
0
TAZICommented:
Hi..

Initially when I started using Delphi V1.0 I use to make use of the Table Component.  I picked up very much the same problems and the system use to hang as the database grew. As you mentioned above, this problem was solved with the TQuery Component.  I now only make use of the TQuery Component and I am using Delphi 5 with SQL Server.  RWilson's statement above Query1.ExecSQL should be Query1.Open. If you insert or update Data in a table you make use of EXECSQL if you need to Select data from the table that you will use to Display on the Screen for eg) then you make use of Open.

According to the way I understand this is that Table Components take each record and individually modify or add it to the database (1 at a time) taking considerably longer however with the Query Component all the records that you wish to insert are done together not individually (like a bulk insert) thus saving time and work much quicker.

Hope this answers your question.

Regards,
Tazi
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
VendiAuthor Commented:
Adjusted points to 40
0
VendiAuthor Commented:
Phoenix s,  do you have anymore information on that?
0
Phoenix_sCommented:
I am currently developing with SQL server 6.5 and delphi 4 (w/ SP2)

I ran into problems posting data into a table that had an 'identity' column... then it'd lose track of which record the cursor was sitting on.

so... needless to say... I had to kill the identity column.. and insert a standard int field and create a stored proc to generate unique key fields (actually this is the way I generate all unique record identifiers)

this issue of mine also had something to do with master-detail issues as well.

are the 2 tables you're referring to part of a master detail set, or are they 2 distinct and seperate entities?  If so... you have to post to the master table first, then the detail, especially if you have any referential integrity constraints to deal with.

anyway... a little more background as to exactly what you are trying to accomplish here might be in order for me to be of further use to you...

cheers!
0
VendiAuthor Commented:
no, I'm not using master-detail.  These two tables are updated with new information from user input.  the unique key fields of these tables are not automatically generated.  an existing key exists for each individual and we use this same unique number.  there are no referential integrity issues to cause this problem.  
0
VendiAuthor Commented:
Thanks for your comments TAZI, Phoenix I am also putting out some points for you in another question.  I appreciate all your comments so far and look forward to reading any more.
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.