I will report that I have also had a challenge with GATEWAY and an autonumber column. I ended up having to number it after the import.
-----Original Message----- From: "Bruce A. Chitiea" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Fri, 24 May 2013 08:11:06 -0700 Subject: [RBASE-L] - Re: Gateway Autonum Failure > Razzak: > > Thank you very much for the exercises. > > My SHOW ZERO shows "ON" > > I've performed a set of controlled experiments with a set of source > files: > > Opcashbalance.xlsx > Opcashbalance.xls > Opcashbalance.csv > > ... generated from Excel 2013 > ... containing only one worksheet > ... with blank column 1 to accommodate the CASHBALID autonumber column > ... with data starting on row 1 > ... sourced sequentially from each two folders: > > D:\00\gway > <database folder> > > ... with your exercise code appropriately modified > > both exercises 01.RMD and 02.RMD fail to import data. > > The import dialog appears and is immediately overlain by a constraint > error > message: > > "Value for Column CashBalID Cannot be NULL" [OK] > "Value for Column CashBalID Cannot be NULL" [OK] > "No rows in table, INSERT a NULL row? [NO] > > There may be more clues, but through the blue, hard to view. > > Thoughts? And thanks. > > Bruce > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of A. > Razzak > Memon > Sent: Thursday, May 23, 2013 8:00 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Gateway Autonum Failure > > At 08:07 PM 5/23/2013, Bruce A. Chitiea wrote: > > >Summary: > > > >a) The GATEWAY command at the R>Prompt fails completely to draw data > > into the target table; > > > >b) The GATEWAY tool, with or without a specification, loads everything > > correctly with the exception of the AUTONUM column, which > populates > > with NULLs. > > > > I have a simple table with an autonum column: > > > > List table opcashbalance > > > > Cashbalid integer not null autonumber > > Cashdate date > > Cashbal currency > > Cashavg currency > > Cashdelta currency > > Cashmin currency > > > > With an XLSX (single worksheet) source file, the following command > > fails to draw data in: > > > > GATEWAY IMPORT XLS d:\00\gway\gway_opcashbalance.xlsx + > > APPEND opcashbalance + > > OPTION AUTONUM ON + > > |FIRST_ROW 2 > > > > When the XLSX file is saved as CSV, the following command fails to > > draw data in: > > > > GATEWAY IMPORT CSV d:\00\gway\gway_opcashbalance.csv + > > APPEND opcashbalance + > > OPTION AUTONUM ON + > > |FIRST_ROW 2 > > > > When the process is worked through the GATEWAY tool, everything > > imports except the AUTONUM column, which populates with NULLs. > > > > Am I missing something here, or should I generate the autonum > value > > in the source file and learn to love the LOAD command? > > Bruce, > > A few questions and suggestions ... > > What is the database SETting for ZERO? > > CONNECT dbname > SHOW ZERO > > Make sure the ZERO setting is SET to ON. > > SET ZERO ON > > What is the definition of AUTONUM column? > > Without knowing all details, try the following exercises and see what > you > get. > > -- Exercise_01.RMD > -- Load Data with AutoNumbered Column > -- Define Temporary Table to Load Data > SET ERROR MESSAGE 2038 OFF > DROP TABLE tOpCashBalance > SET ERROR MESSAGE 2038 ON > CREATE TEMPORARY TABLE tOpCashBalance + > CashBalID INTEGER NOT NULL + > ('Value for Column CashBalID Cannot be NULL'), + > CashDate DATE, + > CashBal CURRENCY, + > CashAvg CURRENCY, + > CashDelta CURRENCY, + > CashMin CURRENCY > AUTONUM CashBalID IN tOpCashBalance USING 1,1 NONUM > COMMENT ON TABLE tOpCashBalance IS 'Temporary Table to Load Data' > -- Import Data > GATEWAY IMPORT XLS + > tOpCashBalance.xls APPEND tOpCashBalance + > OPTION FIRST_ROW 2 > -- Browse Imported Data > CLS > BROWSE * FROM tOpCashBalance > RETURN > > -- Exercise_02.RMD > -- AutoNumber Column After Loading Data > -- Define Temporary Table to Load Data > SET ERROR MESSAGE 2038 OFF > DROP TABLE tOpCashBalance > SET ERROR MESSAGE 2038 ON > CREATE TEMPORARY TABLE tOpCashBalance + > CashBalID INTEGER NOT NULL + > ('Value for Column CashBalID Cannot be NULL'), + > CashDate DATE, + > CashBal CURRENCY, + > CashAvg CURRENCY, + > CashDelta CURRENCY, + > CashMin CURRENCY > AUTONUM CashBalID IN tOpCashBalance USING 1,1 NONUM > COMMENT ON TABLE tOpCashBalance IS 'Temporary Table to Load Data' > -- Import Data > GATEWAY IMPORT XLS + > tOpCashBalance.xls APPEND tOpCashBalance + > OPTION FIRST_ROW 2 > -- Autonum CashBalID Column > AUTONUM CashBalID IN tOpCashBalance USING 1,1 NUM > -- Browse Imported Data > CLS > BROWSE * FROM tOpCashBalance > RETURN > > Hope that provides you with some blue's clues ... > > Very Best R:egards, > > Razzak. > > www.rbase.com > www.facebook.com/rbase > >
