× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Yeah, it's the update that comes back to haunt me. I really hadn't given stored procedures much thought but might revisit it. The issue is that there are green screen apps that access the same tables so I can't change the tables because it would force a recompile. I am trying to get my Ruby skills up to the point where I can understand the AR code and develop a plugin to handle the vaguerities of my particular legacy DB.

I *have* slapped the guy that put those # in the field names and he agreed, in retrospect, that it was a mistake, but who would have known a DB designed in 1979 would still have legs in 2009?

Pete

Aaron Bartell wrote:
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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.