Jim, These are the actual tables
And I did originally code USING ID and ran into trouble. One of the tables would be unusable if I just opened RBEDIT. The serendipity here is that I fell over this early and could correct my code before I spent too much time chasing ghosts. I did not catch this right away as I was looking at the built in help which was not as clear as the text you posted. You pointed me straight to the fix and for that I am very grateful. I'll have to standardize on RSYNTAX.COM for my help needs from now on. Dennis McGrath -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of James Bentley Sent: Wednesday, August 13, 2008 4:29 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: SATTACH performance question Dennis, My interpretation of the key would be the same as yours. In no way am I an expert in ODBC. I just know when I first started using SATTACH I used the ALL option with very unsatisfactory results. I don't remember whether it was discussion on the list or an upgrade to RBase documentation that cleared up confusion about the "USING" OPTION. As for the result returned by Microsoft, they have been known to use non-standard SQL syntax. It could be a quirk of how the Microsoft driver returns information to ODBC internal query from the interface between RBace and the ODBC driver manager. Perhaps another expert lurks on this list and can chime in. I would be interested in knowing if I used a "USING colname" syntax where colname is the actual primary key name would cause problems. An assid question Dennis. Are the two table the actual base table or are you seeing a VIEW? Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293 --- On Wed, 8/13/08, Dennis McGrath <[EMAIL PROTECTED]> wrote: > From: Dennis McGrath <[EMAIL PROTECTED]> > Subject: [RBASE-L] - RE: SATTACH performance question > To: "RBASE-L Mailing List" <[email protected]> > Date: Wednesday, August 13, 2008, 9:07 AM > Very interesting Jim, > > This puzzles me. > > If one uses the option on the utilities tab to attach a SQL > table, it automatically sets up the table as if you used the > USING ALL syntax. This apparently is not what I should do > in my case. > > Both tables have a primary key on the ID column. > > I did write code to do the SATTACH correctly but included > USING ID in the syntax. I did have issues with one of the > tables getting goofy as soon as I opened up RBEDIT. > > I just went in and removed the using clause from both > definitions. > > The RBEDIT problem seems to be resolved now, but.... > > I am still puzzled. I checked the definition of the tables > in SQL Server and the primary key on both tables is indeed > just the one column. > > Here is what shows when I list the SATTACHED tables in > RBASE > It looks like RBASE thinks the primary key is the first 2 > columns. > Am I mistaken? > > Table: CFG1Order No Lock(s) > Descr: Server table - STG_ORDER_OB Configurator1 > > No. Column Name Attributes > --- ------------------ > ---------------------------------------------------- > 1 ID Type : INTEGER NOT NULL QualKey > Comment: #ID > 2 Processed Type : INTEGER NOT NULL QualKey > Comment: #PROCESSED > 3 TYPE_CODE Type : TEXT 1 NOT NULL > Comment: #TYPE > 4 ORDER_NUM Type : TEXT 250 NOT NULL > Comment: #ORDER_NUM > 5 ORDER_REF_NUM Type : TEXT 250 > Comment: #ORDER_REF_NUM > 6 USER_ID Type : TEXT 12 > Comment: #USER_ID > 7 CUST_NAME Type : TEXT 100 > Comment: #CUST_NAME > > > R>list cfg1orderdetail > > Table: CFG1OrderDetail No Lock(s) > Descr: Server table - STG_ORDER_DETAIL_INPUT_OB > Configurator1 > > No. Column Name Attributes > --- ------------------ > --------------------------------------------------- > 1 ID Type : INTEGER NOT NULL QualKey > 2 PROCESSED Type : INTEGER NOT NULL QualKey > 3 ORDER_NUM Type : TEXT 250 NOT NULL > 4 ORDER_LINE_NUM Type : INTEGER NOT NULL > 5 INPUT_NAME Type : TEXT 250 NOT NULL > 6 INPUT_LABEL Type : TEXT 250 > 7 INPUT_VALUE Type : NOTE 2000 > 8 INPUT_VALUE_LABEL Type : NOTE 2000 > 9 TS Type : DATETIME NOT NULL > 10 TRANSACT_ID Type : INTEGER > Current number of rows: SERVER > > > Thanks for the info. > Dennis McGrath > > > > > > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On > Behalf Of James Bentley > Sent: Tuesday, August 12, 2008 8:07 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: SATTACH performance question > > Mike, > > The following is from www.rsyntax.com for SATTACH > "USING ALL > Specifies all columns uniquely identify the rows in an > attached table if no primary or unique keys are defined. > > USING collist > If the foreign table has no primary or unique key, > specifies the column(s) that uniquely identify the rows in > the table." > > It is important to understand that the "USING > ALL" or "USING collist" syntax should be used > ONLY if the table to be attached does not have a PRIMARY or > UNIQUE key. If you can cause problems or effect the speed > of data retrieval is you use this syntax when there id a > PRIMARY or UNIQUE key. > > Jim Bentley > American Celiac Society > [EMAIL PROTECTED] > tel: 1-504-737-3293 > > > --- On Tue, 8/12/08, Dan Goldberg > <[EMAIL PROTECTED]> wrote: > > > From: Dan Goldberg <[EMAIL PROTECTED]> > > Subject: [RBASE-L] - RE: SATTACH performance question > > To: "RBASE-L Mailing List" > <[email protected]> > > Date: Tuesday, August 12, 2008, 12:05 PM > > I have used both and they seem to run about the same > speed. > > I only use them > > for querying(for now) but will be updating the > sattached > > tables soon!! > > > > Dan Goldberg > > > > _____ > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On > > Behalf Of Ramsour Mike > > Sent: Tuesday, August 12, 2008 8:24 AM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - SATTACH performance question > > > > > > > > Good morning: > > > > > > > > Is there a difference in the speed of accessing data > in > > foreign tables from > > an SATTACHed table depending on whether the > "USING > > ALL" vs. "USING > > <collist>" is used? > > > > > > > > i.e. Is it better to use the "USING ALL" > syntax > > vs. specifying exact columns > > vs. not specifying any columns? > > > > > > > > Thanks for any insight. > > > > > > > > Mike Ramsour > > AK Steel Coshocton Works > > Quality Department > > > > Phone/VMS: 740-829-4340
