RE: [DUG]: SQL Poser

[Stephen Barker]

Title: Message

Mark,   If you are using Paradox you only need to do like:        tbarchive := nil;      try         Screen.Cursor := crHourglass;         tbArchive := TTable.create( nil );         with tbArchive do begin              databasename := datadir;              tablename    := 'archive.db';              exclusive    := true;         end;         try            tbarchive.open;            dbiRegenIndexes( tbArchive.Handle );            application.processmessages;         except            (* warn user couldn't open exlusive *)            Application.MessageBox( 'Please ensure that no other users are using '+                                    'the system and try again', strTitle, MB_ICONSTOP );         end;      finally         tbarchive.close;         tbarchive.free;         Screen.Cursor := crDefault;      end;   Also if you are converting from Paradox to Client Server, and don't relish the thought of recoding everything in a C/S fashion, take a look at the upcoming C/S version of DBISAM at http://www.elevatesoft.com. It can easily convert existing data from BDE, and the C/S engine is optimised to work well with TTable-style apps (as well as sql). I believe they have a demo which you can run as client and connect to their server across the internet - on second thoughts I think this is only available to registered users and beta testers.     Steve   -----Original Message----- From: Mark Howard [mailto:[EMAIL PROTECTED]] Sent: Thursday, 6 September 2001 11:20 To: Multiple recipients of list delphi Subject: Re: [DUG]: SQL Poser Scheduling a rebuild at night sounds better. I have TUtil32 but don't know if it runs with comand line parameters, which it would need to do if it were to run unattended.  If it does accept parameters would need to know the syntax. The alternative is to just write a console app to do it.   Re Neven's suggestion to dump Paradox - that's the easy part: the more difficult part of that is replacing it with something else :-). I am part way through a port to Firebird with IBO but as always there is a problem getting time to complete this while supporting the current system. Having said that, Paradox has served me well for over 3 years now without (too many) problems.   Thanks for the help ----- Original Message ----- From: James Sugrue To: Multiple recipients of list delphi Sent: Thursday, September 06, 2001 10:58 AM Subject: RE: [DUG]: SQL Poser Depends the size of the tables and the spec of the machine running on. A reasonably sizeable table on a <500MHz machine will take minutes - tens of minutes to rebuild, which probably isn't good on start up.   A better way would be to run a scheduler and have it rebuild in the middle of the night.   There is a table rebuilding util called TUtil which rebuilds Paradox tables. Cant remember the URL but a google search should find it. -----Original Message----- From: Mark Howard [mailto:[EMAIL PROTECTED]] Sent: Thursday, 6 September 2001 10:41 To: Multiple recipients of list delphi Subject: Re: [DUG]: SQL Poser That's an interesting suggestion. But some questions: Is this recommended practice? Does it slow down startup significantly (26 tables) What code do you use to see if you have exclusive use of table? What do you use to rebuild the indexes - will DBIRestructure do it? Mark   ----- Original Message ----- From: Maurice Butler To: Multiple recipients of list delphi Sent: Thursday, September 06, 2001 9:39 AM Subject: Re: [DUG]: SQL Poser It pays to rebuild the indexes automatically on startup of the first instance. ie. if you can get exclusive use of the table when you start rebuild the indexes. This way any crashes, power offs etc are automatically recovered from. This seems to required more often with paradox then dbase. dbase has a slightly more complicated problem with indexes being detached but this does not happen very often.   Maurice ----- Original Message ----- From: Mark Howard To: Multiple recipients of list delphi Sent: Thursday, September 06, 2001 8:06 AM Subject: Re: [DUG]: SQL Poser Neven   Thanks!  Rebuilt the Customer table and all was well. Wonder how long it would have taken me to get to that solution.  I thought screwed indexes always resulted in error messages.  This sort of symptom is much more worrisome (resulting in people not getting invoiced).   Appreciate your help.   Mark ----- Original Message ----- From: Neven MacEwan To: Multiple recipients of list delphi Sent: Wednesday, September 05, 2001 5:20 PM Subject: Re: [DUG]: SQL Poser Mark   SQL looks fine I'd say your indexes are screwed   Neven ----- Original Message ----- From: Mark Howard To: Multiple recipients of list delphi Sent: Wednesday, September 05, 2001 5:15 PM Subject: [DUG]: SQL Poser Hi   This question relates to a Paradox database and a three table query: the main table is Dockets the other two (Forest and Customer) merely provide a look up to get Forest and Customer names.   Trouble is as follows: I can select from Dockets and return the correct number of dockets when I'm looking up either the Forest or the Customer but not both.  But this ONLY happens for one specific customer.   The tests I have done, with the results, follow.   Can any one suggest what is happening here?  I hope I have given enough information.   ANY suggestions will be appreciated.   TIA   Mark   select D.DocketNo,  D.ForCode,  D.CustCode from  Dockets D  where  D.DocketDate > '07/01/2001'  and  D.CustCode = "HBERT"  and  D.ForCode = "MOCO"   Returns dockets      55895    55899   Now lookup Customer name select D.DocketNo,  D.CustCode,  C.CustName from  Dockets D,  Customer C  where  D.DocketDate > '07/01/2001'  and  D.CustCode = C.CustCode  and  D.CustCode = "HBERT"   Returns dockets      55895    55899   Now lookup Forest name instead select D.DocketNo,  D.ForCode,  F.ForName from  Dockets D,  Forest F  where  D.DocketDate > '07/01/2001'  and  D.ForCode = F.ForCode  and  D.ForCode = "MOCO"   Returns dockets  .    .    55895    55899    .    .    .   Now try to lookup both Customer AND Forest names select D.DocketNo,  D.ForCode,  F.ForName,  D.CustCode,  C.CustName from  Dockets D,  Forest F,  Customer C  where  D.DocketDate > '07/01/2001'  and  D.ForCode = F.ForCode  and  D.CustCode = C.CustCode  and  D.ForCode = "MOCO"   BOTH DOCKETS MISSING   select D.DocketNo,  D.ForCode,  F.ForName,  D.CustCode,  C.CustName from  Dockets D,  Forest F,  Customer C  where  D.DocketDate > '07/01/2001'  and  D.ForCode = F.ForCode  and  D.CustCode = C.CustCode  and  D.CustCode = "HBERT"   BOTH DOCKETS MISSING