SQL Insert versus Table.Append
J Ottery
currently using the standard TTable component:
Table.Append
(20 fields).................
Table.Post
to add records but I am starting to lose records (it is writing across
a network).
Simple question.
1.Will using an SQL Component and INSERT INTO statement improve the
performance and ,especially, stop losing records?
(I have set up a Database ALIS using the STANDARD driver in the BDE
Config.)
2. Will this method allow me to run multiple (write only) users on the
same table.
OR Do I need a separate SQL Server (MySQL or Firebird or ????)
Advice is appreciated.
Arian Silwan
news:01d0f35a-748c-404f...@s12g2000prg.googlegroups.com...
> to add records but I am starting to lose records (it is writing across
> a network).
What and in which situaton is that ecxactly happening? More accurate fault
description would give better idea what could actually be the reason.
> 1.Will using an SQL Component and INSERT INTO statement improve the
> performance and ,especially, stop losing records?
No both TTable and TQuery are just similar layers that use Windows file
handling capabilities. TTable and TQuery components also share about 80% of
common pascal code in the background
Windows is able to lock a part (File Control Block, FCB) of a database file
against other network users and in this locked state write your changes to
the database filem ad then release the lock again.
If Windows fails with this file handling operation in networked environment
and is not able to write the new data to the database file then the thin
TTable and TQuery layers on that are not able to correct the situation.
> 2. Will this method allow me to run multiple (write only) users on the
> same table.
I know users that have given read only rights only, but what exactly are
write only users?
> OR Do I need a separate SQL Server (MySQL or Firebird or ????)
These databases use totally different approach in database file access.
There is a separate Server process on database server machine and it does
all the reads and writes to the database files. All the client machines only
send SQL requests to that Server process to read and write something from/to
the database.
This type of multi user databases are multitudes of more reliable against
data loss and data file corruption.
On the other hand, you should have no difficulties in having a reliable BDE
+ dBase database with 5..10 simultaneous users on a 100 MBit/s network. I
have run tens of this kind of small network databases for over a decade.
Depending on your database's load, you probably do not have immediate need
to move to MySQL or something else.
-Arian
J Ottery
> "J Ottery" <jott...@becsystems.com.au> wrote in message
Thanks so much for your insightful response.
My current problem is:
Using a dbf file with 10 indexes and 500000 records and writing
across a 100Mbs network.
I am beginning to lose records intermittently
Simple Table.Append; ...... Table.Post; It is erroring on the Post
method. (I have the Table open always)
Turned on CachedUpdates and tried to handle the error etc but to no
avail.
I also have sucessfully used this method to write to tables, for
years, but the datafiles have never been as big as this one.
If you have any suggestions on fixing this situation I am all ears.
Your advice convinces me that it is probably time to migrate to a more
robust DBMS anyway.
One more question: Perhaps you know, can I use mySQL to manage my
current dbf type files?
Arian Silwan
> Simple Table.Append; ...... Table.Post; It is erroring on the Post
A professional developer way, when reporting erros situations, is to tell
the exact error messages you are getting.
> Using a dbf file with 10 indexes and 500000 records and writing
My educated, brute force quess is that the table size could be something
like 50 MB. And index files something like 20 MB. In general, you should
still have reasonable 2..5 sec response times over 100 Mbit betwork. And no
evident reason why this size dBase database would not work reliably with a
limited number of users.
It could still be a bug somewhere in your own code. Having as much as 10
different index on one physical table could mean also your code design could
be a bit hairy.
> One more question: Perhaps you know, can I use mySQL to manage my
> current dbf type files?
Manage?? Well, you can use Delphi, VB, Perl, PHP etc. to write application
code that 'manages', that means reads and writes data to your dBase or MySQL
database.
But I'm not able to imagine how MySQL could manage your DBF dBase files. Of
course there are ways to copy, transfer your dBase table data to MySQL
database, if that was the question.
-Arian
J Ottery
> "J Ottery" <jott...@becsystems.com.au> wrote in message
Thanks for the reply(s) again.
Your advice has been taken into consideration.
If you need help with Serial Port Comms and Thermal Label printers (my
specialties) I am offering my services.
Regards, Jeff jot...@becsystems.com.au
Arian Silwan
>
> If you need help with Serial Port Comms and Thermal Label printers (my
All right then, now we have got a right man to answer:)
I myself don't have Serial Port problems, but my colleague works with
Service Station software and systems, he often needs to have even 6 COM
ports on a PC system. All the customer displays, banknote readers, gas
pump links etc. need a COM port.
There are only 2 physical COM-ports available, and no PCI slots on the
miniatyre model industry PC. The PC could be changed to some other
model, if that solves anything. Currently the 4 extra COM ports are made
with USB -> COM converter cables.
The problem is that every now and then some of those converted USB/COM
port hang, no response nor traffic. And the only way to get the single
port up again is to boot the whole PC.
So the question is if there is some other way how to get reliably 6 COM
ports than to build them on USB/COM converters?
Those USB/COM converters like ATEN need, and rely on Windows-XP drivers
written by ATEN. And my quess is the weakest part and the reason is
there, why these ports hang every now and then.
I don't think anyone has a simple and proven answer to this. So I threw
this question out only semi-seriously, for any Serial Port specialist to
give her/his opinion:)
-Arian
J Ottery
I have only ever used either PCI cards or USB to Serial Coverters .
I use Prolific Technologies USB / Serial converters and have had
little or no problems with them although if you are dependent on
hardware control lines CTS/RTS/DTR/DSR then you may encounter
problems. I normally use these ports for simple read or write only.
If he is having lock up problems, at risk of stating the bleeding
obvious, perhaps he can try a SerialPort,Disconnect ....
SerialPort.Connect from within his code rather than have to reboot the
whole box. I wonder what Serial Port driver he is using?
Not much help I know but it's all I got.
Jeff
Jamie
We have one at work that supports up to 64 ports, the first 16 can
be mapped in as windows comports if you install the driver for that or,
simply talk to it via a TCP text based connection that uses a ASC HEX
base link etc..
Arian Silwan
> obvious, perhaps he can try a SerialPort,Disconnect ....
> SerialPort.Connect from within his code rather than have to reboot the
Some interfaces to the peripheral devices there are DLL or ActiveX
components only, so direct Serial Port commanding is not always possible.
> whole box. I wonder what Serial Port driver he is using?
I have no idea about this. ATEN USB/COM converters bring their drivers and
Windows (XP) automatically installs the rest.
> Not much help I know but it's all I got.
Yes, I thought than no final solution maybe will be found, I was only
hopefully fishing to maybe get something. Also I am glad this Serial Port
problem is not mine personally.
My idea was that some hardware based solution that directly brings 6 or
more 'true' Serial Ports to the computer maybe would not hang so easily and
maybe could work best. -Arian
Arian Silwan
news:q5SAj.12$Vt2...@newsfe07.lga...
> In his case, He should be using a multiport Serial to ether net link..
>
> We have one at work that supports up to 64 ports, the first 16 can
>
> http://webpages.charter.net/jamie_5"
Hmm, should that link lead to some page with more info about Multiport
devides?
If the fat lady there is the Main Technical Support or Sales Contact Person,
I'm not sure if I'll pick my phone right away.<g>