RE: Witango-Talk: DBMS action
Not Really, all the search action does is make it simpler to create SQL Statements. The SQL statement is stored within the XML. So the server just processes the SQL statement. Ben Johansen - http://www.pcforge.com -Authorized WiTango Reseller http://www.pcforge.com/WitangoGoodies.htm -Authorized Alt-N Reseller http://www.pcforge.com/AltN.htm -Original Message- From: Ted Wolfley [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 1:50 PM To: [EMAIL PROTECTED] Subject: Witango-Talk: DBMS action Hi, Does any one know if there is a performance difference between a search action and dbms action? Ted TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
RE: Witango-Talk: DBMS action
Correction. I misread a Direct DBMS stored in XML the SQL for a search action has to be built upon the server reading the file. and a directDBMS is stored in the XML so all the server is has to do is submit the SQL So technically DirectDBMS is faster, but not by much. Ben Johansen - http://www.pcforge.com -Authorized WiTango Reseller http://www.pcforge.com/WitangoGoodies.htm -Authorized Alt-N Reseller http://www.pcforge.com/AltN.htm -Original Message- From: Ben Johansen [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 2:07 PM To: [EMAIL PROTECTED] Subject: RE: Witango-Talk: DBMS action Not Really, all the search action does is make it simpler to create SQL Statements. The SQL statement is stored within the XML. So the server just processes the SQL statement. Ben Johansen - http://www.pcforge.com -Authorized WiTango Reseller http://www.pcforge.com/WitangoGoodies.htm -Authorized Alt-N Reseller http://www.pcforge.com/AltN.htm -Original Message- From: Ted Wolfley [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 1:50 PM To: [EMAIL PROTECTED] Subject: Witango-Talk: DBMS action Hi, Does any one know if there is a performance difference between a search action and dbms action? Ted TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
Re: Witango-Talk: DBMS action
Ted, There is not a performance hit, unless you don't have an index on the column that generates the list. Your best bet, since you are using a direct DBMS action, is a sub-query: SELECT a, bunch, of, columnnames FROM maintable WHERE columnvalue IN (SELECT othertablecolumn FROM othertable WHERE somecolumn = '<@VAR request$varname>' ) But, with Oterro/R:Base, be sure NOT to have any line breaks between the parentheses that surround the subquery. On the other hand, if you do not use a subquery, and use something like this... SELECT a, bunch, of, columnnames FROM maintable WHERE columnvalue IN (<@ARRAY request$valuelist APREFIX='' ASUFFIX='' CPREFIX='' CSUFFIX='' RPREFIX='' RSUFFIX=','>0) (The "comma-zero" at the end helps deal with the fact that the RSUFFIX comma character leaves a comma at the right end of the valuelist array, and you need to put one more dummy value after that last comma.) ... then, eventually a user might generate such a long list that you will get a database error message something like "Expression size limit exceeded." You will have to have your code test for the size of the list, and, if necessary, take evasive action. Bill On Mon, 8 Sep 2003 14:46:10 -0400, Ted Wolfley wrote: >I am using a DBMS action to retrieve rows from a Rbase database. In the SQL >select code, I am using the IN syntax to retrieve each row's key field that >matches the list of key fields retrieved from another table. It works with >5 key fields in the list and I am wondering if there is a limit on the >number of values that can be in the list? Or if there is performance hit >the number goes up? TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
Re: Witango-Talk: DBMS action
we at one point used alot of this: > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (<@ARRAY request$valuelist APREFIX='' > ASUFFIX='' CPREFIX='' CSUFFIX='' RPREFIX='' RSUFFIX=','>0) but then as our clients collected more data we found out (atleast for R:Base not sure about others...could be an odbc limitation though) that there is a max of 4000 characters or 500 items in the DBMS query. we had alot of these things explode on us from getting too large and quickly changed to using the subquery format: > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (SELECT othertablecolumn FROM othertable > WHERE somecolumn = '<@VAR request$varname>' ) and now all is well! - Original Message - From: "Bill Downall" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 11:56 AM Subject: Re: Witango-Talk: DBMS action > Ted, > > There is not a performance hit, unless you don't have an index on the > column that generates the list. Your best bet, since you are using a direct > DBMS action, is a sub-query: > > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (SELECT othertablecolumn FROM othertable > WHERE somecolumn = '<@VAR request$varname>' ) > > But, with Oterro/R:Base, be sure NOT to have any line breaks between the > parentheses that surround the subquery. > > On the other hand, if you do not use a subquery, and use something like > this... > > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (<@ARRAY request$valuelist APREFIX='' > ASUFFIX='' CPREFIX='' CSUFFIX='' RPREFIX='' RSUFFIX=','>0) > > (The "comma-zero" at the end helps deal with the fact that the RSUFFIX > comma character leaves a comma at the right end of the valuelist array, > and you need to put one more dummy value after that last comma.) > > ... then, eventually a user might generate such a long list that you will get a > database error message something like "Expression size limit exceeded." > You will have to have your code test for the size of the list, and, if necessary, > take evasive action. > > Bill > > On Mon, 8 Sep 2003 14:46:10 -0400, Ted Wolfley wrote: > > >I am using a DBMS action to retrieve rows from a Rbase database. In the > SQL > >select code, I am using the IN syntax to retrieve each row's key field that > >matches the list of key fields retrieved from another table. It works with > >5 key fields in the list and I am wondering if there is a limit on the > >number of values that can be in the list? Or if there is performance hit > >the number goes up? > > > > > > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
RE: Witango-Talk: DBMS action
Thanks, I just tested the subquery format and got the same results so I will go with that since I do not know how large the list will become. Just a side note, I did not use the >0 with my array variable and I still retrieved all the data. Is this a glitch or just safe coding? -Original Message- From: Atrix Wolfe [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 3:15 PM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: DBMS action we at one point used alot of this: > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (<@ARRAY request$valuelist APREFIX='' > ASUFFIX='' CPREFIX='' CSUFFIX='' RPREFIX='' RSUFFIX=','>0) but then as our clients collected more data we found out (atleast for R:Base not sure about others...could be an odbc limitation though) that there is a max of 4000 characters or 500 items in the DBMS query. we had alot of these things explode on us from getting too large and quickly changed to using the subquery format: > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (SELECT othertablecolumn FROM othertable WHERE > somecolumn = '<@VAR request$varname>' ) and now all is well! - Original Message - From: "Bill Downall" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 11:56 AM Subject: Re: Witango-Talk: DBMS action > Ted, > > There is not a performance hit, unless you don't have an index on the > column that generates the list. Your best bet, since you are using a direct > DBMS action, is a sub-query: > > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (SELECT othertablecolumn FROM othertable WHERE > somecolumn = '<@VAR request$varname>' ) > > But, with Oterro/R:Base, be sure NOT to have any line breaks between > the parentheses that surround the subquery. > > On the other hand, if you do not use a subquery, and use something > like this... > > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (<@ARRAY request$valuelist APREFIX='' > ASUFFIX='' CPREFIX='' CSUFFIX='' RPREFIX='' RSUFFIX=','>0) > > (The "comma-zero" at the end helps deal with the fact that the RSUFFIX > comma character leaves a comma at the right end of the valuelist > array, and you need to put one more dummy value after that last > comma.) > > ... then, eventually a user might generate such a long list that you > will get a > database error message something like "Expression size limit > exceeded." You will have to have your code test for the size of the > list, and, if necessary, > take evasive action. > > Bill > > On Mon, 8 Sep 2003 14:46:10 -0400, Ted Wolfley wrote: > > >I am using a DBMS action to retrieve rows from a Rbase database. In > >the > SQL > >select code, I am using the IN syntax to retrieve each row's key > >field that > >matches the list of key fields retrieved from another table. It > >works with > >5 key fields in the list and I am wondering if there is a limit on > >the number of values that can be in the list? Or if there is > >performance hit the number goes up? > > > > > __ > __ > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
RE: Witango-Talk: DBMS action
On Mon, 8 Sep 2003 15:26:31 -0400, Ted Wolfley wrote: >Just a side note, I did not use the >0 with my array variable and I still >retrieved all the data. Is this a glitch or just safe coding? Ted, It may have been a recent clever enhancement to Oterro to let us get away with that. I know when I started using Tango 3 with R:Base 3 or 4 years ago, I needed that dummy value. Bill TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
Re: Witango-Talk: DBMS action
just last year when we were doin that, we also had to use the dummy value so it musta been a pretty recent upgrade (: - Original Message - From: "Bill Downall" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 12:25 PM Subject: RE: Witango-Talk: DBMS action > On Mon, 8 Sep 2003 15:26:31 -0400, Ted Wolfley wrote: > > >Just a side note, I did not use the >0 with my array variable and I still > >retrieved all the data. Is this a glitch or just safe coding? > > Ted, > > It may have been a recent clever enhancement to Oterro to let us get away > with that. I know when I started using Tango 3 with R:Base 3 or 4 years ago, I > needed that dummy value. > > Bill > > > > > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
RE: Witango-Talk: DBMS Action
Steve - why do you need to do this? Is the null field causing a problem, or is it something else? Would it help any to change the WHERE clause to " AND ((o5.OV_ID = b2.OV_ID3) OR b2.OV_ID3 IS NULL) ", so that the clause was true if either the two IDs match or the ID3 value is null? You could check the OV_ID3 field in advance and set up your query depending on the results of that search. What database are you using? > -Original Message- > From: Fogelson, Steve [mailto:[EMAIL PROTECTED]] > Sent: Monday, February 17, 2003 11:00 PM > To: Witango User Group (E-mail) > Subject: Witango-Talk: DBMS Action > > > In the following DBMS action, b2.OV_ID3 may be null. Is there > a way in the WHERE clause to not include "AND (o5.OV_ID = > b2.OV_ID3)" if "b2.OV_ID3 is null". > > If so, would it also be neccessary to not include "o5.OV_Name > as `Option3`" in the SELECT clause? > > If so, how would I eliminate this? > > Thanks > > Steve Fogelson > Internet Commerce Solutions > > SELECT p1.P_ID, > p1.P_SKU, > p1.P_Part_Number, > p1.P_Name, > p1.P_Price1 as `Price`, > b2.B_Quantity, > b2.B_ItemNumber, > p1.V_ID, > b2.OV_ID1, > o3.OV_Name as `Option1`, > b2.OV_ID2, > o4.OV_Name as `Option2`, > b2.OV_ID3, > o5.OV_Name as `Option3` > > FROM Products p1, Basket b2, OptionValue o3, OptionValue o4, > OptionValue o5 WHERE (b2.B_ShopperID = <@var user$U_ID>) > AND (p1.P_Disable Is Null) > AND (b2.P_ID = p1.P_ID) > AND (o3.OV_ID = b2.OV_ID1) > AND (o4.OV_ID = b2.OV_ID2) > AND (o5.OV_ID = b2.OV_ID3) > ORDER BY b2.B_ItemNumber ASC > __ > __ > TO UNSUBSCRIBE: send a plain text/US ASCII email to > [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
Re: Witango-Talk: DBMS Action
Steve, You can build your complete SQL in variables in a results action, modifying the SELECT list, the FROM list, and the WHERE conditions according to some IF conditions. Of course, you will have to SELECT the ov_id3 column from the appropriate BASKET row first, to set up your if conditions. Then, if you have the SQL you want in @@local $StevesQuery, you can, in your directDBMS action, just put <@VAR local$StevesQuery encoding="none"> Bill On Mon, 17 Feb 2003 23:00:10 -0600, Fogelson, Steve wrote: >In the following DBMS action, b2.OV_ID3 may be null. Is there a way in the >WHERE clause to not include "AND (o5.OV_ID = b2.OV_ID3)" if "b2.OV_ID3 is >null". > >If so, would it also be neccessary to not include "o5.OV_Name as `Option3`" >in the SELECT clause? > >If so, how would I eliminate this? > >Thanks > >Steve Fogelson >Internet Commerce Solutions > >SELECT p1.P_ID, > p1.P_SKU, > p1.P_Part_Number, > p1.P_Name, > p1.P_Price1 as `Price`, > b2.B_Quantity, > b2.B_ItemNumber, > p1.V_ID, > b2.OV_ID1, > o3.OV_Name as `Option1`, > b2.OV_ID2, > o4.OV_Name as `Option2`, > b2.OV_ID3, > o5.OV_Name as `Option3` > > FROM Products p1, Basket b2, OptionValue o3, OptionValue o4, OptionValue o5 > WHERE (b2.B_ShopperID = <@var user$U_ID>) > AND (p1.P_Disable Is Null) > AND (b2.P_ID = p1.P_ID) > AND (o3.OV_ID = b2.OV_ID1) > AND (o4.OV_ID = b2.OV_ID2) > AND (o5.OV_ID = b2.OV_ID3) > ORDER BY b2.B_ItemNumber ASC >_ ___ >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >with unsubscribe witango-talk in the message body TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
RE: Witango-Talk: DBMS Action
I think it is more complicated than I stated. All 3 or 2 or 1 of the following fields b2.OV_ID1, b2.OV_ID2, and b2.OV_ID3 can be null. There can also be many rows in the basket. The rows in the basket are purchased products and the 3 fields I refer to are possible options (size, color, etc.) for the products. If one or more of the 3 fields is null, the DBMS doesn't return a matched row. Maybe the best thing for me to do is have a default option that is titled "no option". When an item is added to the basket, I should check for "null" options and set them to the default "no option" before the DBMS search. That way all 3 fields would have a match. These are extra steps I would like to avoid if the search could somehow ignore the null fields. I didn't quit understand Bill's suggestion. If I understand it correctly, I would have to do a loop of some sort and build the conditional search for each row in the basket instead of "viewing" all the rows at one time. I guess I was wondering how the "Incl. Empty" option in the "criteria" of a "search" action could be related to a DBMS. I understand this option (if set to false) to not include a criteria if null. Maybe I don't understand it correctly. Steve Fogelson -Original Message- From: Bill Downall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 18, 2003 12:41 PM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: DBMS Action Steve, You can build your complete SQL in variables in a results action, modifying the SELECT list, the FROM list, and the WHERE conditions according to some IF conditions. Of course, you will have to SELECT the ov_id3 column from the appropriate BASKET row first, to set up your if conditions. Then, if you have the SQL you want in @@local $StevesQuery, you can, in your directDBMS action, just put <@VAR local$StevesQuery encoding="none"> Bill On Mon, 17 Feb 2003 23:00:10 -0600, Fogelson, Steve wrote: >In the following DBMS action, b2.OV_ID3 may be null. Is there a way in the >WHERE clause to not include "AND (o5.OV_ID = b2.OV_ID3)" if "b2.OV_ID3 is >null". > >If so, would it also be neccessary to not include "o5.OV_Name as `Option3`" >in the SELECT clause? > >If so, how would I eliminate this? > >Thanks > >Steve Fogelson >Internet Commerce Solutions > >SELECT p1.P_ID, > p1.P_SKU, > p1.P_Part_Number, > p1.P_Name, > p1.P_Price1 as `Price`, > b2.B_Quantity, > b2.B_ItemNumber, > p1.V_ID, > b2.OV_ID1, > o3.OV_Name as `Option1`, > b2.OV_ID2, > o4.OV_Name as `Option2`, > b2.OV_ID3, > o5.OV_Name as `Option3` > > FROM Products p1, Basket b2, OptionValue o3, OptionValue o4, OptionValue o5 > WHERE (b2.B_ShopperID = <@var user$U_ID>) > AND (p1.P_Disable Is Null) > AND (b2.P_ID = p1.P_ID) > AND (o3.OV_ID = b2.OV_ID1) > AND (o4.OV_ID = b2.OV_ID2) > AND (o5.OV_ID = b2.OV_ID3) > ORDER BY b2.B_ItemNumber ASC >_ ___ >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >with unsubscribe witango-talk in the message body TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
RE: Witango-Talk: DBMS Action
On Tue, 18 Feb 2003 13:24:22 -0600, Fogelson, Steve wrote: >I guess I was wondering how the "Incl. Empty" option in the "criteria" of a >"search" action could be related to a DBMS. I understand this option (if set >to false) to not include a criteria if null. No, those settings deal with data that comes back after a query against the database. In your unusual case, you need to know ahead of time if you even want to join a particular table or not, based on a null in a linking column. In usual database schemas, the columns used for linking are not columns that would sometimes contain nulls. I think your situation is complicated enough that you ought to do what I suggested earlier, build your final query a little bit at a time, as you figure out what tables you need, and what columns you will select from each of those, and what links you will need for each of those. There are also multiple ways to do the database trick called an "outer join," which builds null-filled rows in the result set based on non- matches in the query. (rather than excluding rows from both contributing tables if they don't match anything, which is the normal result, also called an "inner join" or a "natural join"). I think you use R:Base, do you not? If so, there is an outer join syntax to SELECT, and you can also build your own logical equilvalent of the outer join by stringing parallel SELECT statements together with the UNION ALL syntax in between them. With three or more contributing tables, though, outer joins can get very hairy. Bill TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
RE: Witango-Talk: DBMS Action
At 01:46 PM 2/18/2003, you wrote: On Tue, 18 Feb 2003 13:24:22 -0600, Fogelson, Steve wrote: >I guess I was wondering how the "Incl. Empty" option in the "criteria" of a >"search" action could be related to a DBMS. I understand this option (if set >to false) to not include a criteria if null. No, those settings deal with data that comes back after a query against the database. That is incorrect. The "Include If Empty" option in the Criteria tab of a Search action definitely has an impact on the SQL sent to the server. Yes, it may affect the results you see, but only because it changes the SQL SELECT statement sent to the DBMS. Take a simple example. Imagine you have a form with one input field called ID. You have a table in your database with two columns: "TheID", and "Name". You've set up a Search action to return the TheID and Name columns from your table. You have set the Criteria tab of the Search action to include the TheID = <@ARG ID>. Now, imagine that the user doesn't enter a value in the ID input field. That is, the ID argument IS EMPTY. If the Include If Empty option is set to False, the SQL sent to the database will be: SELECT t1.TheID, t1.Name FROM MyTable t1 Notice that there is no WHERE clause. That's because the criteria wasn't included because the argument was empty. If you set Include If Empty to True, the SQL will be: SELECT t1.TheID, t1.Name FROM MyTable t1 WHERE t1.TheID IS NULL Now, the criteria has been included, even though the value (<@ARG ID>) is empty. There is more information on this topic in the manuals. HTH, Eric TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
RE: Witango-Talk: DBMS Action
On Tue, 18 Feb 2003 15:25:44 -0600, Eric Weidl wrote: >That is incorrect. Eric, Maybe I didn't explain clearly enough, but it was definitely not incorrect . Steve's got a handle on what you are talking about. He would like to be able to "not include if empty" a JOIN condition between tables. (and the table in the FROM list, and a column in the SELECT list. To simplify his problem, he wants an automated way to switch between these two forms: SELECT t1.column1, t2.column2, t3.column3 FROM table1 t1, table2 t2, table3 t3 WHERE (other conditions that may or my not appear based on "include if empty" settings, as Eric described) AND (t1.linkcolumn = t2.linkcolumn) AND (t2.linkcolumn = t3.linkcolumn) and SELECT t1.column1, t2.column2 FROM table1 t1, table2 t2 WHERE (other conditions that may or my not appear based on "include if empty" settings, as Eric described) AND (t1.linkcolumn = t2.linkcolumn) Bill TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
Re: Witango-Talk: DBMS Action Select with Limit # or Rows
Steve, You are probably better doing this with a custom SQL action. What SQL server are you using? J. On 09/03/2004, at 3:12 PM, Fogelson, Steve wrote: I am trying to construct a DBMS Select action that will limit the number of rows retrieved. WITH IMAGINATION Planning, Implementation and Management of Web Applications 160 Pacific Highway North Sydney NSW Australia 2060 phone + 612 9929 9229 fax + 612 9460 4770 web - email - [EMAIL PROTECTED]
Re: Witango-Talk: DBMS Action Select with Limit # or Rows
Steve, Are you using the "builders"? These work fine for me with Oterro 2.6 and 3.0, with the "limit to xx rows" and "previous xx" and "next xx" links, but I have customized the builder considerably. Bill At 11:12 PM 3/8/2004, you wrote: I am trying to construct a DBMS Select action that will limit the number of rows retrieved. TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
Bill & Jason, Windows 2003 Web Edition, R:Tango version 5, Oterro 3.0 update 3 Yes this used to work correctly for me with R:Tango 2000 as well. When I upgraded to version 5 is when it changed. I just tried using a search builder for a test, used the default of 20 for "Limit to" and it listed 28 rows. Display below. There are 28 matching records. Displaying matches 1 through 28. With the "Next 8 Matches" submit button after the list of urls. Thanks Steve -Original Message- From: Bill [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 7:15 AM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: DBMS Action Select with Limit # or Rows Steve, Are you using the "builders"? These work fine for me with Oterro 2.6 and 3.0, with the "limit to xx rows" and "previous xx" and "next xx" links, but I have customized the builder considerably. Bill At 11:12 PM 3/8/2004, you wrote: >I am trying to construct a DBMS Select action that will limit the number of >rows retrieved. TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
Steve, The limit does the following. It processes the full select and then limits the rows returning. So if the select returns a 100 rows with the limit, then the limit = 20 prevents the last 80 from returning. Your are better servered by using the COUNT criteria Ben Johansen - http://www.pcforge.com Authorized Witango & MDaemon Reseller Available for Witango Developement -Original Message- From: Fogelson, Steve [mailto:[EMAIL PROTECTED] Sent: Monday, March 08, 2004 11:12 PM To: Witango User Group (E-mail) Subject: Witango-Talk: DBMS Action Select with Limit # or Rows I am trying to construct a DBMS Select action that will limit the number of rows retrieved. I do a first pass DBMS Select action the first time the taf is executed. The purpose of the first pass is to determine the number of rows that meet the search criteria. I do a second pass DBMS Select action each time the taf is executed again. The action has a "Start Retrieval at match number" and a "Limit To" for the "Number of rows to retrieve". It appears that the "Start Retrieval at match number" works fine, but the "number of Rows to retrieve" doesn't. Here are results: 1st taf execution Start Retrieval - 1 Limit to - 14 Rows Retrieved: 1 thru 26 2nd taf execution Start Retrieval - 15 Limit to - 14 Rows Retrieved: 15 thru 26 First execution retrieved all the records. I assumed 1st taf execution would retrieve only the first 14 rows. I tried not selecting a "Limit to" in the "Results" part of the action (kept the Start Retrieval) and replacing it with the following DBMS: SELECT P1.P_ID, P1.P_Name, FROM Products P1 WHERE (P1.Cat_ID=<@var request$CategoryID> AND P1.P_Disable IS NULL) and LIMIT = <@var request$MaxRows> 1st taf execution Start Retrieval - 1 Limit to - 14 Rows Retrieved: 1 thru 14 2nd taf execution Start Retrieval - 15 Limit to - 14 Rows Retrieved: no rows retrieved Not sure what happened here. Is my only option the first method I described or is something not working correctly? Or is this the way it is supposed to work? Thanks for comments and ideas. Steve Fogelson internet Commerce Solutions TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
Steve, This is quite weird! I went to look at my taf that does this just fine, and it doesn't do this just fine anymore! I have a taf that pages in 50s, and has been working great this way with next/previous buttons and your configuration as below, except that at the client site is is Oterro 2.6, on my development system it is Oterro 3.0. But now, it is not breaking where it should the buttons compute correctly, but it tries to assemble all 1032 on the first page. The only thing that has changed since this worked was my installing Witango server .65 over .58 that had been running there! Bill At 09:30 AM 3/9/2004, you wrote: Bill & Jason, Windows 2003 Web Edition, R:Tango version 5, Oterro 3.0 update 3 Yes this used to work correctly for me with R:Tango 2000 as well. When I upgraded to version 5 is when it changed. I just tried using a search builder for a test, used the default of 20 for "Limit to" and it listed 28 rows. Display below. There are 28 matching records. Displaying matches 1 through 28. With the "Next 8 Matches" submit button after the list of urls. Thanks TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
A followup: This definitely was broken in Witango server 5.0 after patch .58 and before patch .65. I have a site still running on .58. A taf that observes the limit and pages groups of 40 just fine in .58 puts the whole result set on the page on a server running .65. I'll send the example tafs to Fergal at Witango. Bill At 11:07 AM 3/9/2004, you wrote: Steve, This is quite weird! I went to look at my taf that does this just fine, and it doesn't do this just fine anymore! I have a taf that pages in 50s, and has been working great this way with next/previous buttons and your configuration as below, except that at the client site is is Oterro 2.6, on my development system it is Oterro 3.0. But now, it is not breaking where it should the buttons compute correctly, but it tries to assemble all 1032 on the first page. The only thing that has changed since this worked was my installing Witango server .65 over .58 that had been running there! Bill At 09:30 AM 3/9/2004, you wrote: Bill & Jason, Windows 2003 Web Edition, R:Tango version 5, Oterro 3.0 update 3 Yes this used to work correctly for me with R:Tango 2000 as well. When I upgraded to version 5 is when it changed. I just tried using a search builder for a test, used the default of 20 for "Limit to" and it listed 28 rows. Display below. There are 28 matching records. Displaying matches 1 through 28. With the "Next 8 Matches" submit button after the list of urls. Thanks TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
Thanks Bill. I thought something was up here. Steve -Original Message- From: Bill Downall [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 10:38 AM To: [EMAIL PROTECTED] Subject: RE: Witango-Talk: DBMS Action Select with Limit # or Rows A followup: This definitely was broken in Witango server 5.0 after patch .58 and before patch .65. I have a site still running on .58. A taf that observes the limit and pages groups of 40 just fine in .58 puts the whole result set on the page on a server running .65. I'll send the example tafs to Fergal at Witango. Bill At 11:07 AM 3/9/2004, you wrote: >Steve, > >This is quite weird! I went to look at my taf that does this just fine, >and it doesn't do this just fine anymore! > >I have a taf that pages in 50s, and has been working great this way with >next/previous buttons and your configuration as below, except that at the >client site is is Oterro 2.6, on my development system it is Oterro >3.0. But now, it is not breaking where it should the buttons compute >correctly, but it tries to assemble all 1032 on the first page. > >The only thing that has changed since this worked was my installing >Witango server .65 over .58 that had been running there! > >Bill > >At 09:30 AM 3/9/2004, you wrote: >>Bill & Jason, >> >>Windows 2003 Web Edition, R:Tango version 5, Oterro 3.0 update 3 >> >>Yes this used to work correctly for me with R:Tango 2000 as well. When I >>upgraded to version 5 is when it changed. >> >>I just tried using a search builder for a test, used the default of 20 for >>"Limit to" and it listed 28 rows. Display below. >> >>There are 28 matching records. >> >>Displaying matches 1 through 28. >> >>With the "Next 8 Matches" submit button after the list of urls. >> >>Thanks > > > >TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: DBMS Action Select with Limit # or Rows
As this is R:Base specific it may be worth contacting R:Base to see what is happening in the db/ODBC driver. The issue may lie in the Oterro 3 driver. 065 introduced a strict ODBC 3.0 interface. 058 used an ODBC 2 interface to make db calls. Oterro prior to version 3 only supported ODBC 2.5 calls. Try placing a trace on the db and the ODBC dm to see what sql it is executing. Are they different? If anyone is experiencing this with other dbs can they let us know. On 10/03/2004, at 7:22 AM, Fogelson, Steve wrote: Thanks Bill. I thought something was up here. Steve -Original Message- From: Bill Downall [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 10:38 AM To: [EMAIL PROTECTED] Subject: RE: Witango-Talk: DBMS Action Select with Limit # or Rows A followup: This definitely was broken in Witango server 5.0 after patch .58 and before patch .65. I have a site still running on .58. A taf that observes the limit and pages groups of 40 just fine in .58 puts the whole result set on the page on a server running .65. I'll send the example tafs to Fergal at Witango. Bill At 11:07 AM 3/9/2004, you wrote: Steve, This is quite weird! I went to look at my taf that does this just fine, and it doesn't do this just fine anymore! I have a taf that pages in 50s, and has been working great this way with next/previous buttons and your configuration as below, except that at the client site is is Oterro 2.6, on my development system it is Oterro 3.0. But now, it is not breaking where it should the buttons compute correctly, but it tries to assemble all 1032 on the first page. The only thing that has changed since this worked was my installing Witango server .65 over .58 that had been running there! Bill At 09:30 AM 3/9/2004, you wrote: Bill & Jason, Windows 2003 Web Edition, R:Tango version 5, Oterro 3.0 update 3 Yes this used to work correctly for me with R:Tango 2000 as well. When I upgraded to version 5 is when it changed. I just tried using a search builder for a test, used the default of 20 for "Limit to" and it listed 28 rows. Display below. There are 28 matching records. Displaying matches 1 through 28. With the "Next 8 Matches" submit button after the list of urls. Thanks __ __ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ___ _ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ___ _ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: DBMS Action Select with Limit # or Rows
What do I do to trace these at the level that would be useful? It's obviously not just the SQL in the Witango debugging output. When I go into ODBC Administrator and "Start Tracing now" using the default odbctrac.dll, and then hit the Oterro database driver through the Witango application, nothing happens. I don't find any results in the \SQL.log file. Thanks. Bill At 03:09 AM 3/10/2004, you wrote: As this is R:Base specific it may be worth contacting R:Base to see what is happening in the db/ODBC driver. The issue may lie in the Oterro 3 driver. 065 introduced a strict ODBC 3.0 interface. 058 used an ODBC 2 interface to make db calls. Oterro prior to version 3 only supported ODBC 2.5 calls. Try placing a trace on the db and the ODBC dm to see what sql it is executing. Are they different? TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
Bill Oterro doesn't log to the ODBC Log. You need to do this: Add the line: DEBUG ON to the Oterro.cfg file. This will create an Oterro.log file in the C:\ directory Ben Johansen - http://www.pcforge.com Authorized Witango & MDaemon Reseller Available for Witango Developement -Original Message- From: Bill Downall [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 9:24 AM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: DBMS Action Select with Limit # or Rows What do I do to trace these at the level that would be useful? It's obviously not just the SQL in the Witango debugging output. When I go into ODBC Administrator and "Start Tracing now" using the default odbctrac.dll, and then hit the Oterro database driver through the Witango application, nothing happens. I don't find any results in the \SQL.log file. Thanks. Bill At 03:09 AM 3/10/2004, you wrote: >As this is R:Base specific it may be worth contacting R:Base to see >what is happening in the db/ODBC driver. The issue may lie in the >Oterro 3 driver. 065 introduced a strict ODBC 3.0 interface. 058 >used an ODBC 2 interface to make db calls. Oterro prior to version 3 >only supported ODBC 2.5 calls. Try placing a trace on the db and the >ODBC dm to see what sql it is executing. Are they different? TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
At 10:16 AM 3/10/2004, you wrote: Bill Oterro doesn't log to the ODBC Log. You need to do this: Add the line: DEBUG ON to the Oterro.cfg file. This will create an Oterro.log file in the C:\ directory Ben Johansen - http://www.pcforge.com Authorized Witango & MDaemon Reseller Available for Witango Developement Wow. What a resource hog! Two queries (slowed down greatly!) and I have a 6+ Meg Log file. I don't think I'll try that on the production system running Witango xxx.58 during the business day. Bill TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
Ben, Actually, it turns out that Oterro DOES log to the odbc log, it just doesn't show up until you disconnect. I had two 6 Megabyte logs from 2 queries, one was the odbc administrator SQL.log, the other was the Oterro debug oterro.log. Similar details, but slightly different format. No wonder performance took a big hit! At 10:16 AM 3/10/2004, you wrote: Bill Oterro doesn't log to the ODBC Log. You need to do this: Add the line: DEBUG ON to the Oterro.cfg file. This will create an Oterro.log file in the C:\ directory TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
There has been a change, if I recall correctly. Oterro 3.0 is RDEBUG ON. Chuck Lockwood LockData Technologies, Inc. 309 Main Avenue, Hawley, Pa 18428 570-226-7340 ~ Fax: 570-226-7341 [EMAIL PROTECTED] ~ www.lockdata.com -Original Message- From: Bill Downall [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 2:21 PM To: [EMAIL PROTECTED] Subject: RE: Witango-Talk: DBMS Action Select with Limit # or Rows Ben, Actually, it turns out that Oterro DOES log to the odbc log, it just doesn't show up until you disconnect. I had two 6 Megabyte logs from 2 queries, one was the odbc administrator SQL.log, the other was the Oterro debug oterro.log. Similar details, but slightly different format. No wonder performance took a big hit! At 10:16 AM 3/10/2004, you wrote: >Bill Oterro doesn't log to the ODBC Log. > >You need to do this: > >Add the line: >DEBUG ON >to the Oterro.cfg file. >This will create an Oterro.log file in the C:\ directory TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: DBMS Action Select with Limit # or Rows
At 03:46 PM 3/10/2004, you wrote: There has been a change, if I recall correctly. Oterro 3.0 is RDEBUG ON. Chuck Lockwood Chuck DEBUG ON worked for me with 3.0. I think the change was the name and location of the log, from Microrim.log to c:\Oterro.log, as of Oterro version 2.6. Bill TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
