Re: Ruby on Rails on i site back up

[Aaron Bartell <aaronbartell@xxxxxxxxx>]

What I have had to do is create SQL Views to get around some of that stuff
and I am wondering if the same would work for you.  Here is an example DDL
of a workaround I needed for RPG to simplify the programming and not deal
with generated column names that didn't make sense.  I believe Views might
be read-only, so you might be better off going with store procedures for
troublesome columns.  Makes you want to slap the guy that thought #'s in
column names was a good idea :-)  (well, I suppose it was when there was
only 6 characters to work with :-)

DROP TABLE CHRTACCT;
CREATE TABLE CHRTACCT
(
  BA_RECCODE         FOR COLUMN RECCOD   CHAR (1),
  SUB_NO             FOR COLUMN SUBNO    INT,
  ACCT_NO            FOR COLUMN ACTNO    INT,
  ACCT_TYPE          FOR COLUMN ACTTYPE  INT,
  FROM_ACCT_RANGE_NO FOR COLUMN FRMACTR  INT,
  FROM_ACCT_TYPE     FOR COLUMN FRMACTT  INT,
  FIRM_NO            FOR COLUMN FIRMNO   INT,
  BRANCH                                 CHAR (4),
  TO_ACCT_RANGE_NO   FOR COLUMN TOACTR   INT,
  TO_ACCT_TYPE       FOR COLUMN TOACTT   INT,
  DATA_SRCE_CODE     FOR COLUMN DTACODE  CHAR (4),
  NYSE_ACCT_CAT_CODE FOR COLUMN CATCODE  INT,
  ASOF_CYMD          FOR COLUMN ASOFDAT  CHAR (10)
);

CREATE VIEW CHRTACCT1V (
BA_RECCODE         FOR COLUMN RECCOD,
SUB_NO             FOR COLUMN SUBNO ,
ACCT_NO            FOR COLUMN ACTNO ,
ACCT_TYPE          FOR COLUMN ACTTYPE ,
FROM_ACCT_RANGE_NO FOR COLUMN FRMACTR ,
FROM_ACCT_TYPE     FOR COLUMN FRMACTT ,
FIRM_NO            FOR COLUMN FIRMNO ,
BRANCH ,
TO_ACCT_RANGE_NO   FOR COLUMN TOACTR ,
TO_ACCT_TYPE       FOR COLUMN TOACTT ,
DATA_SRCE_CODE     FOR COLUMN DTACODE ,
NYSE_ACCT_CAT_CODE FOR COLUMN CATCODE ,
ASOF_CYMD          FOR COLUMN ASOFDAT )
AS
SELECT
         COALESCE(BA_RECCODE, 0)         AS BA_RECCODE,
         COALESCE(SUB_NO, 0)             AS SUB_NO,
         COALESCE(ACCT_NO, 0)            AS ACCT_NO,
         COALESCE(ACCT_TYPE, 0)          AS ACCT_TYPE,
         COALESCE(FROM_ACCT_RANGE_NO, 0) AS FROM_ACCT_RANGE_NO,
         COALESCE(FROM_ACCT_TYPE, 0)     AS FROM_ACCT_TYPE,
         COALESCE(FIRM_NO, 0)            AS FIRM_NO,
         COALESCE(BRANCH, ' ')           AS BRANCH,
         COALESCE(TO_ACCT_RANGE_NO, 0)   AS TO_ACCT_RANGE_NO,
         COALESCE(TO_ACCT_TYPE, 0)       AS TO_ACCT_TYPE,
         COALESCE(DATA_SRCE_CODE, ' ')   AS DATA_SRCE_CODE,
         COALESCE(NYSE_ACCT_CAT_CODE, 0) AS NYSE_ACCT_CAT_CODE,
         COALESCE(ASOF_CYMD, ' ')        AS ASOF_CYMD
        FROM CHRTACCT;


Aaron Bartell
www.SoftwareSavesLives.com


On Mon, Dec 28, 2009 at 7:10 AM, Pete Helgren <Pete@xxxxxxxxxx> wrote:

> Embedded '#' in column names is a show stopper in AR and there isn't an
> easy workaround.  The embedded '#' has been my nemesis with this
> particular legacy DB for years so it isn't unique to Rails but I haven't
> yet found a way to deal with it in Rails.  Other DB's all I had to do
> was to use double quotes or brackets and all was well.  The primary
> key/identity column issues have workarounds but they aren't easy nor do
> they work well with other plugin's.
>
> However, the Rails framework for the reasons you state.  I just wish
> there were easier ways to implement it in a legacy DB.
>
> Pete