Vantage db structure alterations

Thanks for the update Alan. It's good toknow how things pan out.

Troy

----- Original Message -----
From: Alan Williams
To: vantage@Yahoogroups. com
Sent: Wednesday, January 16, 2002 9:53 AM
Subject: [Vantage] RE: Vantage db structure alterations


Sorry for the delay in reply. Just wanted to say thanx to Ted, Troy, and
William for their help. I wasn't able to make Crystal (8.0) accept the sql
statement; It kept breaking Crystal AND giving me sql errors on my server. I
truncated my Part.PartDescription to 250 and everything is still fine. (So
far)

Thanx,
Alan
----------------------------------------------------------------------------
-----------------------------------------------

Date: Tue, 8 Jan 2002 09:47:48 -0600
From: Ted Kitch <onelist@...>
Subject: RE: Vantage db structure alterations

This was originally submitted by Anton Wilson.

1 - I discovered this today, and it was right in front of my nose all along.
It's by far the simplest solution and I'd recommend it.

Go into the "Insert" menu in Crystal. Select "Create SQL Expression". From
here you can either do an RTrim, or more safely, do a "{fn
SUBSTRING((field.name),1,255)}" - this ensures that the field is no longer
than 255 characters.

>From there you can include the SQL expression in formulas.

The SUBSTRING will have to be entered by hand. You won't be able to click
on fields to select them. It has worked great for me.

Ted Kitch
Misha1, LTD.
866-464-7421

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Tuesday, January 08, 2002 12:38 PM
To: vantage@...
Subject: Re: [Vantage] Vantage db structure alterations

Alan,
We did the truncation of Part Description as you've described and it
hasn't bothered us a bit. As you mention, we don't use excessively long
Part Descriptions.
I personally couldn't get this to work, but there is supposedly a way to
create an SQL expression in Crystal which will allow you to work with the
longer BLOB fields instead of doing the truncation of the database. It was
in a recent thread, and was submitted by Ted Kitch.

Troy Funte
----------------------------------------------------------------------------
--------------------------------------------------
Date: Tue, 08 Jan 2002 22:22:01 -0500
From: william <william@...>
Subject: Re: Vantage db structure alterations

Short Course on Database changes.

Each table in a Progress database has a CRC value calculated for it. The
CRC value for all tables referenced by a procedure are stored in the .r
file. When Progress tries to run a .r, it compares the CRC's it has
against the CRC's for the tables in the dictionary, and if they match
exactly, the procedure runs. Otherwise you get a run time error message.

The CRC of a table is calculated using the values of a number elements
for the table itself, each field in the table, and each index of the
table. The exact elements used vary from release to release. If you are
interested in the elements check Annex A of the Progress Programming
Handbook for your release. Changing any of the elements that goes into
the CRC will invalidate your .r files. Note that indexes are used in
calculating the CRC, so adding an index will invalidate the .r file.

The Good News here is that format is not part of the CRC calculation.
Thus you can change the defined format safely. The Even Better News is
that changing the format will not change the way the field is viewed on
the screen, displayed in reports, or affect the values that you enter
from the screen. For the program to see the new format it must be
recompiled.

So for the particular case in point, you may safely change the format to
"x(200)" for large character fields, remembering of course that you will
only see the first 200 characters in the query, even though you may have
entered more than that from the application.

HTH.

Alan Williams wrote:
>
> Here's a question for all you Progress heavies: I need to use Crystal
> Reports to manipulate Part.PartDescription, but can't 'cause it's a blob
> field. We're on 5.10.107, and I've already applied the odbcfix.r, but it
> only addresses index descriptions, if I'm understanding it right. I ran
> across instructions for altering the field length for vb form fields, and
it
> works just fine for the Part.PartDescription too. I trimmed it to 250
chars,
> (including the sql length), restarted the db (I'm using our test db, by
the
> way- I'm not that crazy, yet) and it works just fine.
> My question is, What would I break doing this? It's not an
indexed field,
> and none of our data exceeds 200 chars. I know I'm being paranoid, but am
I
> being paranoid enough?
>
> Thanx,
> Alan


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
Here's a question for all you Progress heavies: I need to use Crystal
Reports to manipulate Part.PartDescription, but can't 'cause it's a blob
field. We're on 5.10.107, and I've already applied the odbcfix.r, but it
only addresses index descriptions, if I'm understanding it right. I ran
across instructions for altering the field length for vb form fields, and it
works just fine for the Part.PartDescription too. I trimmed it to 250 chars,
(including the sql length), restarted the db (I'm using our test db, by the
way- I'm not that crazy, yet) and it works just fine.
My question is, What would I break doing this? It's not an indexed field,
and none of our data exceeds 200 chars. I know I'm being paranoid, but am I
being paranoid enough?

Thanx,
Alan
Alan,
We did the truncation of Part Description as you've described and it hasn't bothered us a bit. As you mention, we don't use excessively long Part Descriptions.
I personally couldn't get this to work, but there is supposedly a way to create an SQL expression in Crystal which will allow you to work with the longer BLOB fields instead of doing the truncation of the database. It was in a recent thread, and was submitted by Ted Kitch.

Troy Funte
----- Original Message -----
From: Alan Williams
To: vantage@Yahoogroups. com
Sent: Tuesday, January 08, 2002 7:12 AM
Subject: [Vantage] Vantage db structure alterations


Here's a question for all you Progress heavies: I need to use Crystal
Reports to manipulate Part.PartDescription, but can't 'cause it's a blob
field. We're on 5.10.107, and I've already applied the odbcfix.r, but it
only addresses index descriptions, if I'm understanding it right. I ran
across instructions for altering the field length for vb form fields, and it
works just fine for the Part.PartDescription too. I trimmed it to 250 chars,
(including the sql length), restarted the db (I'm using our test db, by the
way- I'm not that crazy, yet) and it works just fine.
My question is, What would I break doing this? It's not an indexed field,
and none of our data exceeds 200 chars. I know I'm being paranoid, but am I
being paranoid enough?

Thanx,
Alan


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
Short Course on Database changes.

Each table in a Progress database has a CRC value calculated for it. The
CRC value for all tables referenced by a procedure are stored in the .r
file. When Progress tries to run a .r, it compares the CRC's it has
against the CRC's for the tables in the dictionary, and if they match
exactly, the procedure runs. Otherwise you get a run time error message.

The CRC of a table is calculated using the values of a number elements
for the table itself, each field in the table, and each index of the
table. The exact elements used vary from release to release. If you are
interested in the elements check Annex A of the Progress Programming
Handbook for your release. Changing any of the elements that goes into
the CRC will invalidate your .r files. Note that indexes are used in
calculating the CRC, so adding an index will invalidate the .r file.

The Good News here is that format is not part of the CRC calculation.
Thus you can change the defined format safely. The Even Better News is
that changing the format will not change the way the field is viewed on
the screen, displayed in reports, or affect the values that you enter
from the screen. For the program to see the new format it must be
recompiled.

So for the particular case in point, you may safely change the format to
"x(200)" for large character fields, remembering of course that you will
only see the first 200 characters in the query, even though you may have
entered more than that from the application.

HTH.

Alan Williams wrote:
>
> Here's a question for all you Progress heavies: I need to use Crystal
> Reports to manipulate Part.PartDescription, but can't 'cause it's a blob
> field. We're on 5.10.107, and I've already applied the odbcfix.r, but it
> only addresses index descriptions, if I'm understanding it right. I ran
> across instructions for altering the field length for vb form fields, and it
> works just fine for the Part.PartDescription too. I trimmed it to 250 chars,
> (including the sql length), restarted the db (I'm using our test db, by the
> way- I'm not that crazy, yet) and it works just fine.
> My question is, What would I break doing this? It's not an indexed field,
> and none of our data exceeds 200 chars. I know I'm being paranoid, but am I
> being paranoid enough?
>
> Thanx,
> Alan
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

--
/* ================================================================*/

Specialists in Progress Databases and Software since 1986

William E. Colls william@...
PROComputer Systems Tel 613 591 0079
67 Willow Glen Dr. Fax 613 591 3924
Kanata Ontario Canada www.procomsys.com
K2M 1T1
This was originally submitted by Anton Wilson.

1 - I discovered this today, and it was right in front of my nose all along.
It's by far the simplest solution and I'd recommend it.

Go into the "Insert" menu in Crystal. Select "Create SQL Expression". From
here you can either do an RTrim, or more safely, do a "{fn
SUBSTRING((field.name),1,255)}" - this ensures that the field is no longer
than 255 characters.

From there you can include the SQL expression in formulas.

The SUBSTRING will have to be entered by hand. You won't be able to click
on fields to select them. It has worked great for me.

Ted Kitch
Misha1, LTD.
866-464-7421

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Tuesday, January 08, 2002 12:38 PM
To: vantage@...
Subject: Re: [Vantage] Vantage db structure alterations

Alan,
We did the truncation of Part Description as you've described and it
hasn't bothered us a bit. As you mention, we don't use excessively long
Part Descriptions.
I personally couldn't get this to work, but there is supposedly a way to
create an SQL expression in Crystal which will allow you to work with the
longer BLOB fields instead of doing the truncation of the database. It was
in a recent thread, and was submitted by Ted Kitch.

Troy Funte
----- Original Message -----
From: Alan Williams
To: vantage@Yahoogroups. com
Sent: Tuesday, January 08, 2002 7:12 AM
Subject: [Vantage] Vantage db structure alterations


Here's a question for all you Progress heavies: I need to use Crystal
Reports to manipulate Part.PartDescription, but can't 'cause it's a blob
field. We're on 5.10.107, and I've already applied the odbcfix.r, but it
only addresses index descriptions, if I'm understanding it right. I ran
across instructions for altering the field length for vb form fields, and
it
works just fine for the Part.PartDescription too. I trimmed it to 250
chars,
(including the sql length), restarted the db (I'm using our test db, by
the
way- I'm not that crazy, yet) and it works just fine.
My question is, What would I break doing this? It's not an indexed
field,
and none of our data exceeds 200 chars. I know I'm being paranoid, but am
I
being paranoid enough?

Thanx,
Alan


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]





Yahoo! Groups Sponsor


ADVERTISEMENT

<http://rd.yahoo.com/M=216105.1806326.3318126.1261774/D=egroupweb/S=17050071
83:HM/A=915128/R=0/O=1/I=brandr-lrec/postholidaygroup/*http://shopping.yahoo
.com/domain?d=holiday&cf=posthol01>


<http://us.adserver.yahoo.com/l?M=216105.1806326.3318126.1261774/D=egroupmai
l/S=1705007183:HM/A=915128/rand=180400557>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.


[Non-text portions of this message have been removed]
Sorry for the delay in reply. Just wanted to say thanx to Ted, Troy, and
William for their help. I wasn't able to make Crystal (8.0) accept the sql
statement; It kept breaking Crystal AND giving me sql errors on my server. I
truncated my Part.PartDescription to 250 and everything is still fine. (So
far)

Thanx,
Alan
----------------------------------------------------------------------------
-----------------------------------------------

Date: Tue, 8 Jan 2002 09:47:48 -0600
From: Ted Kitch <onelist@...>
Subject: RE: Vantage db structure alterations

This was originally submitted by Anton Wilson.

1 - I discovered this today, and it was right in front of my nose all along.
It's by far the simplest solution and I'd recommend it.

Go into the "Insert" menu in Crystal. Select "Create SQL Expression". From
here you can either do an RTrim, or more safely, do a "{fn
SUBSTRING((field.name),1,255)}" - this ensures that the field is no longer
than 255 characters.

>From there you can include the SQL expression in formulas.

The SUBSTRING will have to be entered by hand. You won't be able to click
on fields to select them. It has worked great for me.

Ted Kitch
Misha1, LTD.
866-464-7421

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Tuesday, January 08, 2002 12:38 PM
To: vantage@...
Subject: Re: [Vantage] Vantage db structure alterations

Alan,
We did the truncation of Part Description as you've described and it
hasn't bothered us a bit. As you mention, we don't use excessively long
Part Descriptions.
I personally couldn't get this to work, but there is supposedly a way to
create an SQL expression in Crystal which will allow you to work with the
longer BLOB fields instead of doing the truncation of the database. It was
in a recent thread, and was submitted by Ted Kitch.

Troy Funte
----------------------------------------------------------------------------
--------------------------------------------------
Date: Tue, 08 Jan 2002 22:22:01 -0500
From: william <william@...>
Subject: Re: Vantage db structure alterations

Short Course on Database changes.

Each table in a Progress database has a CRC value calculated for it. The
CRC value for all tables referenced by a procedure are stored in the .r
file. When Progress tries to run a .r, it compares the CRC's it has
against the CRC's for the tables in the dictionary, and if they match
exactly, the procedure runs. Otherwise you get a run time error message.

The CRC of a table is calculated using the values of a number elements
for the table itself, each field in the table, and each index of the
table. The exact elements used vary from release to release. If you are
interested in the elements check Annex A of the Progress Programming
Handbook for your release. Changing any of the elements that goes into
the CRC will invalidate your .r files. Note that indexes are used in
calculating the CRC, so adding an index will invalidate the .r file.

The Good News here is that format is not part of the CRC calculation.
Thus you can change the defined format safely. The Even Better News is
that changing the format will not change the way the field is viewed on
the screen, displayed in reports, or affect the values that you enter
from the screen. For the program to see the new format it must be
recompiled.

So for the particular case in point, you may safely change the format to
"x(200)" for large character fields, remembering of course that you will
only see the first 200 characters in the query, even though you may have
entered more than that from the application.

HTH.

Alan Williams wrote:
>
> Here's a question for all you Progress heavies: I need to use Crystal
> Reports to manipulate Part.PartDescription, but can't 'cause it's a blob
> field. We're on 5.10.107, and I've already applied the odbcfix.r, but it
> only addresses index descriptions, if I'm understanding it right. I ran
> across instructions for altering the field length for vb form fields, and
it
> works just fine for the Part.PartDescription too. I trimmed it to 250
chars,
> (including the sql length), restarted the db (I'm using our test db, by
the
> way- I'm not that crazy, yet) and it works just fine.
> My question is, What would I break doing this? It's not an
indexed field,
> and none of our data exceeds 200 chars. I know I'm being paranoid, but am
I
> being paranoid enough?
>
> Thanx,
> Alan