• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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.
0
Vendi
Asked:
Vendi
1 Solution
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now