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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.CustCode = "HBERT" BOTH DOCKETS MISSING
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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.CustCode = "HBERT" BOTH DOCKETS MISSING
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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.CustCode = "HBERT" BOTH DOCKETS MISSING
RE: [DUG]: SQL Poser
Title: Message 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:41To: Multiple recipients of list delphiSubject: 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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.Dock
Re: [DUG]: SQL Poser
Mark Dump Paradox, This a perenial problem with ISAMs Neven - Original Message - From: Mark Howard To: Multiple recipients of list delphi Sent: Thursday, September 06, 2001 10:40 AM 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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.CustCode = "HBERT" BOTH DOCKETS MISSING
Re: [DUG]: SQL Poser
Title: Message 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:41To: Multiple recipients of list delphiSubject: 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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "
RE: [DUG]: SQL Poser
A very important point that can never be repeated too much: It is absolutely imperative that when a table is posted a call is made to DbiSaveTable to flush the cache. On Windows 95 anyway, and there have been documented other issues on networks, and with Windows disk caching. OTOH I have experienced few problems at a customer site, only the occasional server crash or in one instance a hard disk crash. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Maurice ButlerSent: Thursday, 6 September 2001 09:40To: Multiple recipients of list delphiSubject: 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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.CustCode = "HBERT" BOTH DOCKETS MISSING
RE: [DUG]: SQL Poser
To rebuild the indexes you can use the TTUtility component set from O2A www.o2a.com -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mark HowardSent: Thursday, 6 September 2001 10:41To: Multiple recipients of list delphiSubject: 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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.CustCode = "HBERT" BOTH DOCKETS MISSING
RE: [DUG]: SQL Poser
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:20To: Multiple recipients of list delphiSubject: 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:41To: Multiple recipients of list delphiSubject: 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
Re: [DUG]: SQL Poser
Title: Message
Hey thanks Stephen!
That'll save me a bit of time. I guess I just cycle
through my tables replacing your 'archive.db' with each in turn?
Regarding the conversion to C/S, the IBO components have a set
that directly replace existing TQuery's and TTables as a halfway house before
going native - it's just that they appear to be pretty slow compared to the
existing Paradox app - so I don't think I can deploy that option before doing
some C/S style tuning.
It's what I really SHOULD do anyway :-{ but thanks for
the suggestion.
- Original Message -
From:
Stephen
Barker
To: Multiple recipients of list delphi
Sent: Thursday, September 06, 2001 5:12
PM
Subject: RE: [DUG]: SQL Poser
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:20To: Multiple recipients of list delphiSubject:
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:41To: Multiple recipients of list
delphiSubject: 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
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 getForest 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 selectD.DocketNo,D.ForCode,D.CustCodefrom Dockets D where D.DocketDate '07/01/2001'and D.CustCode = "HBERT"and D.ForCode = "MOCO" Returns dockets 5589555899 Now lookup Customer nameselectD.DocketNo,D.CustCode,C.CustNamefrom Dockets D, Customer C where D.DocketDate '07/01/2001'and D.CustCode = C.CustCodeand D.CustCode = "HBERT" Returns dockets 5589555899 Now lookup Forest name insteadselectD.DocketNo,D.ForCode,F.ForNamefrom Dockets D, Forest F where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.ForCode = "MOCO" Returns dockets ..5589555899... Now try to lookup both Customer AND Forest namesselectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.ForCode = "MOCO" BOTH DOCKETS MISSING selectD.DocketNo,D.ForCode,F.ForName,D.CustCode,C.CustNamefrom Dockets D, Forest F,Customer C where D.DocketDate '07/01/2001'and D.ForCode = F.ForCodeand D.CustCode = C.CustCodeand D.CustCode = "HBERT" BOTH DOCKETS MISSING
