Dennis I would read the documentation that way! I would not thrust a foreign db-ODBC-driver to correctly inform R:Base what is the primary key. So I agree with Javier always name the primarykey/uniqkey in the using clause
Gunnar Ekblad Kontema IT AB Hästholmsvägen 32 131 30 Nacka Sweden -----Ursprungligt meddelande----- Från: [email protected] [mailto:[email protected]] För Dennis McGrath Skickat: den 10 februari 2009 15:17 Till: RBASE-L Mailing List Ämne: [RBASE-L] - Re: Sconnect revisited I may be wrong, but my understanding of the documentation indicates that if the source table actually has a primary key, you should avoid the USING clause. Here is the applicable documentation: 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. Dennis McGrath ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of James Bentley Sent: Monday, February 09, 2009 7:22 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Sconnect revisited Tom and Javier, I agree that you need to do USING your_primary_key. However you must exercise caution in doing "USING *" I no primary key exists the the goal is to define only those columns in the "USING ..." necessary to uniquely identify each row in the table. Using extra columns could muddy up the issues. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 --- On Mon, 2/9/09, Javier Valencia <[email protected]> wrote: From: Javier Valencia <[email protected]> Subject: [RBASE-L] - Re: Sconnect revisited To: "RBASE-L Mailing List" <[email protected]> Date: Monday, February 9, 2009, 5:20 PM Tom: I am working on similar application and this is how I do it SCONNECT. SATTACH remote table AS. Project Remote Table into temporary local table UPDATE local table with data from temporary table SDETACH ALL nochk SDISCONNECT. SCONNECT SATTACH remote table AS. Project Local Table into temporary local table UPDATE Remote table with data from temporary table SDETACH ALL nochk SDISCONNECT. It might be an overkill to disconnect and reconnect but it makes sure tables and buffers are updated. Also, don't forget that when you SATTACH a table you must use "USING your_primary_key" or "USING *" if you don't have a primary key; I was unable to update the remote database when I did not follow this rule. Javier Valencia 913-915-3137 ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of TOM HART Sent: Sunday, February 08, 2009 8:41 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Sconnect revisited Here is what I'm doing: project temptableA from tableB project temptableB from tableA inserting temptableA into tableA inserting temptableB into tableB ----it hangs up right here, I get disk error and pointer error, have to kill with task manager if I do it this way project temptableA from tableB inserting temptableA into tableA drop temptableA project temptableB from tableA inserting temptableB into tableB drop temptableB this works, I know it is not a syntax problem because I am just copying and pasting ________________________________________ From: Alastair Burr <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Sunday, February 8, 2009 2:23:05 AM Subject: [RBASE-L] - Re: Sconnect revisited Tom, Is your error on the second project or the next command? I projected two temp tables at the R:> with no problem using a single line command with a ; separator. Just another thought: are you using the "USING *" syntax or specifying some columns? Regards, Alastair. ----- Original Message ----- From: TOM HART To: RBASE-L Mailing List Sent: Sunday, February 08, 2009 1:20 AM Subject: [RBASE-L] - Re: Sconnect revisited The table names are not the problem, I just did that for example. The situation is that if I try to project the 2 temptables(with different names) at the same time the the process fails. Tom ________________________________________ From: Alastair Burr <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Saturday, February 7, 2009 6:57:59 PM Subject: [RBASE-L] - Re: Sconnect revisited Table names not distinct in the first 8 characters? Regards, Alastair. ----- Original Message ----- From: TOM HART To: RBASE-L Mailing List Sent: Saturday, February 07, 2009 7:45 PM Subject: [RBASE-L] - Sconnect revisited First of all I corrected the scratch settings as per Razz. The problem I seem to run into, but is easy to work around is this I am trying to update(or sync) 2 identical databases with data from a table, and this scenario does not work: Connected: DatabaseA, Table: TableA sConnected: DatabaseB, sAttached Table: TableA as alias TableB To update the data I am projecting a temp tableA from TableB where data does not exist, and inserting it into TableA Then projecting a tempB table from TableA and inserting it into TableB if I try to create both temp tables in consecutive order, the process fails - I get error disk problem however if I create tempA, do the process, drop tempA, create tempB and do process everything works drop TempTableA drop TempTableB project temp TempTableA from TableB where...... project temp TempTableB from TableA where...... do my updating this will fail however drop TempTableA project temp TempTableA from TableB where...... do my updating drop TempTableA drop TempTableB project temp TempTableB from TableA where...... do my updating drop TempTableB this will work The work-around is simple but why will the first scenario not work. Tom ________________________________________ No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.233 / Virus Database: 270.10.19/1938 - Release Date: 02/04/09 16:35:00 ________________________________________ No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.233 / Virus Database: 270.10.19/1939 - Release Date: 02/06/09 17:28:00
