Re: ODBC

This WebDNA talk-list message is from

2000

It keeps the original formatting. numero = 35162
interpreted = N
texte = It's important to run all of the permutations. Since I know now that you are running Oracle, I can give more specific suggestions.When the WebCat queries fail, use sqlplus from the webserver as well asanother machine to connect to the database. If the database is on thesame machine as the webserver, check your TNSNAMES entry and see whetheryou are connecting via TCPIP or BEQUEST, and try the other one. For aremote Oracle database, you only have the choice of TCPIP. You can alsotry TNSPING80 from the webserver and another machine and make sure thatthe listener is still, umm, listening. BEQUEST connections do not needto have the listener running, by the way.The Oracle for NT installation should include an executable which theycall Oracle ODBC Test. It is a 330k executable that you can copy toany machine you want to use to test ODBC connectivity. It is prettylame, but it does work acceptably. You can use this to test that the ODBC driver is still functioning (after a fashion). I would try and run the same queries that WebCat is running.Lastly, install all of the Oracle management tools. They are dauntingfor a new DBA, but they can tell you exactly what is going on insidethe database itself.Oh, one more thing, have you tuned your indexes intelligently? Oracleis very picky about indexes; you think you have created a good index andyet Oracle will not use it until you create the appropriate query. Forexample: select field1, field2, field3 from table1 where field1 > 0 and field2 > '' and field3 > 0 order by field1, field2, field3If you have created an index on field1, field2, field3, the ORDER BYclause will not use the index unless all three fields are used in theWHERE clause. This really makes you crazy the first dozen times you forget it, but it makes for very efficient queries when you understandit. My example contains trivial WHERE criteria on purpose; as long asthe field is being tested, the query optimizer will evaluate the indexesfor use during the query. If you leave out any of the fields from theWHERE clause, that index will not be used and you are going to do a fulltable search.Here's an even more neat trick: say you have a lookup table and you onlywant to return records from the main table that are in the lookup table. select field1, field2 from table1 where exists ( select 1 from table2 where field1=table1.field1) and field2 = 'something'If you have an index on table2 for field1, this is an extremely fastquery, similar to the difference between [search] and [lookup].HTHJohn PeacockDaventab - Dave Jones wrote:> > I am connecting to Oracle 8i.> I haven't tried connecting with the oracle client tools yet while it's down.> But I will be sure to the next time it happens. To answer number 2, no I can> not connect to the database with any other WEBCATALOG app.> Thanks,> > Dave.> > ----- Original Message -----> From: John Peacock > To: WebCatalog Talk > Sent: Tuesday, July 25, 2000 1:01 PM> Subject: Re: ODBC> > > What backend database are you connecting to? Have you tried to use the> > native client to connect to the database from the webserver (to make> > sure that it is still up)? Have you tried to use an ODBC client (like> > Access or Oracle's ODBC Test)?> >> > There a lots of reasons that ODBC calls would stop working. You need> > to work backwards from the server to the client app to confirm where it> > is having a problem:> >> > 1) can you connect to the database from another client?> > 2) can you connect to the database from another app on webserver?> > 3) can you connect using native tools (e.g. sqlplus for Oracle)?> > 4) can you connect using other ODBC clients?> > .> > .> > .> >> > HTH> >> > John Peacock> >> >> > Daventab - Dave Jones wrote:> > >> > > Webcat 3.0, NT 4.0 &> > > Webcat 3.0, Win 2000.> > >> > > About twice a day my SQL commands in webcatalog stop working. In order> to> > > get them to start again I have to restart the server. I can't be sure> which> > > website is causing this because SQL errors are not recorded in the error> > > log. It seems like the ODBC adapter just stops working all together. Any> > > page I try to access with SQL connect strings will not work. I am fairly> > > certain that the problem is not in the code. Have any of you ever> > > experienced this type of problem before? Any help would be great.> > >> > > Thanks,> > >> > > Dave.> > >> > > -------------------------------------------------------------> > > This message is sent to you because you are subscribed to> > > the mailing list .> > > To unsubscribe, E-mail to: > > > To switch to the DIGEST mode, E-mail to> > > > Web Archive of this list is at: http://search.smithmicro.com/> >> > -------------------------------------------------------------> > This message is sent to you because you are subscribed to> > the mailing list .> > To unsubscribe, E-mail to: > > To switch to the DIGEST mode, E-mail to> > > Web Archive of this list is at: http://search.smithmicro.com/> >> > -------------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list .> To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > Web Archive of this list is at: http://search.smithmicro.com/-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/ Associated Messages, from the most recent to the oldest:

It's important to run all of the permutations. Since I know now that you are running Oracle, I can give more specific suggestions.When the WebCat queries fail, use sqlplus from the webserver as well asanother machine to connect to the database. If the database is on thesame machine as the webserver, check your TNSNAMES entry and see whetheryou are connecting via TCPIP or BEQUEST, and try the other one. For aremote Oracle database, you only have the choice of TCPIP. You can alsotry TNSPING80 from the webserver and another machine and make sure thatthe listener is still, umm, listening. BEQUEST connections do not needto have the listener running, by the way.The Oracle for NT installation should include an executable which theycall Oracle ODBC Test. It is a 330k executable that you can copy toany machine you want to use to test ODBC connectivity. It is prettylame, but it does work acceptably. You can use this to test that the ODBC driver is still functioning (after a fashion). I would try and run the same queries that WebCat is running.Lastly, install all of the Oracle management tools. They are dauntingfor a new DBA, but they can tell you exactly what is going on insidethe database itself.Oh, one more thing, have you tuned your indexes intelligently? Oracleis very picky about indexes; you think you have created a good index andyet Oracle will not use it until you create the appropriate query. Forexample: select field1, field2, field3 from table1 where field1 > 0 and field2 > '' and field3 > 0 order by field1, field2, field3If you have created an index on field1, field2, field3, the ORDER BYclause will not use the index unless all three fields are used in theWHERE clause. This really makes you crazy the first dozen times you forget it, but it makes for very efficient queries when you understandit. My example contains trivial WHERE criteria on purpose; as long asthe field is being tested, the query optimizer will evaluate the indexesfor use during the query. If you leave out any of the fields from theWHERE clause, that index will not be used and you are going to do a fulltable search.Here's an even more neat trick: say you have a lookup table and you onlywant to return records from the main table that are in the lookup table. select field1, field2 from table1 where exists ( select 1 from table2 where field1=table1.field1) and field2 = 'something'If you have an index on table2 for field1, this is an extremely fastquery, similar to the difference between [search] and [lookup].HTHJohn PeacockDaventab - Dave Jones wrote:> > I am connecting to Oracle 8i.> I haven't tried connecting with the oracle client tools yet while it's down.> But I will be sure to the next time it happens. To answer number 2, no I can> not connect to the database with any other WEBCATALOG app.> Thanks,> > Dave.> > ----- Original Message -----> From: John Peacock > To: WebCatalog Talk > Sent: Tuesday, July 25, 2000 1:01 PM> Subject: Re: ODBC> > > What backend database are you connecting to? Have you tried to use the> > native client to connect to the database from the webserver (to make> > sure that it is still up)? Have you tried to use an ODBC client (like> > Access or Oracle's ODBC Test)?> >> > There a lots of reasons that ODBC calls would stop working. You need> > to work backwards from the server to the client app to confirm where it> > is having a problem:> >> > 1) can you connect to the database from another client?> > 2) can you connect to the database from another app on webserver?> > 3) can you connect using native tools (e.g. sqlplus for Oracle)?> > 4) can you connect using other ODBC clients?> > .> > .> > .> >> > HTH> >> > John Peacock> >> >> > Daventab - Dave Jones wrote:> > >> > > Webcat 3.0, NT 4.0 &> > > Webcat 3.0, Win 2000.> > >> > > About twice a day my SQL commands in webcatalog stop working. In order> to> > > get them to start again I have to restart the server. I can't be sure> which> > > website is causing this because SQL errors are not recorded in the error> > > log. It seems like the ODBC adapter just stops working all together. Any> > > page I try to access with SQL connect strings will not work. I am fairly> > > certain that the problem is not in the code. Have any of you ever> > > experienced this type of problem before? Any help would be great.> > >> > > Thanks,> > >> > > Dave.> > >> > > -------------------------------------------------------------> > > This message is sent to you because you are subscribed to> > > the mailing list .> > > To unsubscribe, E-mail to: > > > To switch to the DIGEST mode, E-mail to> > > > Web Archive of this list is at: http://search.smithmicro.com/> >> > -------------------------------------------------------------> > This message is sent to you because you are subscribed to> > the mailing list .> > To unsubscribe, E-mail to: > > To switch to the DIGEST mode, E-mail to> > > Web Archive of this list is at: http://search.smithmicro.com/> >> > -------------------------------------------------------------> This message is sent to you because you are subscribed to> the mailing list .> To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > Web Archive of this list is at: http://search.smithmicro.com/-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/ John Peacock

DOWNLOAD WEBDNA NOW!

Re: ODBC

2000

Top Articles:

Related Readings: