RE: Modify Data via ODBC


(system) #1
I have to second the reluctance to write from an uncontrolled program. We Sys Admins get in enough trouble. How would you like to face your users if you did an OOPS, and we all go OOPS from time to time.

Shirley Graver
Sys Admin
Rubber Associates Inc.

(system) #2
I have a dilemma and I am curious if anyone else is having the same issue.

In the Customer Tracker module you obviously have access to all customer
history. This includes the ability to view aging, payment history, g/l
distribution, etc...

In our industry (plastic injection mold design and build) we have several
employee's who must provide updates on projects and make frequent contact
with our customers. I cannot find a method of providing access to the
customer database without giving them it all. Is this a problem for others?
and if any of you have found a workaround short of a printed list what was
it?

Thanks for the feedback.

Daniel A. Nichols
General Manager
If any part of this message is unreadable, please contact the above at
dann@...

(system) #3
At 09:54 AM 7/27/2000 , you wrote:
>with our customers. I cannot find a method of providing access to the
>customer database without giving them it all. Is this a problem for others?
>and if any of you have found a workaround short of a printed list what was

You could setup ODBC and perhaps an Access screen to display the info. Of
course then the PC can easily access to the ENTIRE database if someone
knows what they're doing.

BTW... I'd like to use ODBC to modify data (yeah, I know the risks) but
with 4.0 / 9.0B / 3.6 the ODBC driver chokes anytime a write fires a
trigger. Anyway around this? Seems like the Progress process back on the
server should be able to kick in and run the trigger code?

-Wayne Cox

(system) #4
Unfortunately triggers execute on the Client side, not the server. What error message do you get when the trigger tries to execute?

Wayne Cox wrote:

> At 09:54 AM 7/27/2000 , you wrote:
> >with our customers. I cannot find a method of providing access to the
> >customer database without giving them it all. Is this a problem for others?
> >and if any of you have found a workaround short of a printed list what was
>
> You could setup ODBC and perhaps an Access screen to display the info. Of
> course then the PC can easily access to the ENTIRE database if someone
> knows what they're doing.
>
> BTW... I'd like to use ODBC to modify data (yeah, I know the risks) but
> with 4.0 / 9.0B / 3.6 the ODBC driver chokes anytime a write fires a
> trigger. Anyway around this? Seems like the Progress process back on the
> server should be able to kick in and run the trigger code?
>
> -Wayne Cox
>
>
>
> We no longer allow attachments to files. To access/share Report Files, please go to the following link: http://www.egroups.com/files/vantage/
> (Note: If this link does not work for you the first time you try it, go to www.egroups.com, login and be sure to save your password, choose My Groups, choose Vantage, then choose Files. If you save the password, the link above will work the next time you try it.)

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

Programmers, with their inbuilt sense of order and discipline, should really be running the world.

William E. Colls Tel 613 591 0079
PROComputer Systems Fax 613 591 3924
67 Willow Glen Dr www.procomsys.com
Kanata Ontario Canada K2M 1T1 PEG Member 1998051404

Specialists in Progress software development since 1985

(system) #5
Wayne,

I totally agree with you in saying that it would be nice to be able to
change the data via ODBC. But would I do it (even if I could)? No way.

You say that you know the risks, but I wonder if you really do. Do you know
all the tables that are linked directly or indirectly to the table that you
are working on? You certainly can't depend on Epicor to document all the
relationships. If you miss something there are two possibilities that
should give you pause: 1) it could crash your system, or 2) it is possible
that Vantage would continue to function but give you wrong data; this is the
really scary outcome because you would never know what data you could trust.

Part of the problem is that the triggers DO fire on the server when you
attempt to write from ODBC, and they are usually looking for system
variables (like company ID, user ID, etc.) that your ODBC connection cannot
supply. There are _very few_ tables in Vantage that don't have these
constraints.

ODBC has the capability to write data back to the Progress database, but I
don't know of a single database administrator that would recommend that
option for anything but the simplest of schemas. Vantage is a relatively
large and complex database. Those triggers that prevent you from writing
data via ODBC are actually saving you from yourself.

================
Steve Sanders
Delta Centrifugal Corp.

-----Original Message-----
From: Wayne Cox [mailto:wmc@...]
Sent: Thursday, July 27, 2000 9:09 AM
To: vantage@egroups.com
Subject: Re: [Vantage] Customer Tracking Module (& ODBC)


At 09:54 AM 7/27/2000 , you wrote:
>with our customers. I cannot find a method of providing access to the
>customer database without giving them it all. Is this a problem for
others?
>and if any of you have found a workaround short of a printed list what
was

You could setup ODBC and perhaps an Access screen to display the info. Of
course then the PC can easily access to the ENTIRE database if someone
knows what they're doing.

BTW... I'd like to use ODBC to modify data (yeah, I know the risks) but
with 4.0 / 9.0B / 3.6 the ODBC driver chokes anytime a write fires a
trigger. Anyway around this? Seems like the Progress process back on the
server should be able to kick in and run the trigger code?

-Wayne Cox


----------------------------------------------------------------------------
--



----------------------------------------------------------------------------
--
We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
(Note: If this link does not work for you the first time you try it, go
to www.egroups.com, login and be sure to save your password, choose My
Groups, choose Vantage, then choose Files. If you save the password, the
link above will work the next time you try it.)



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

(system) #6
At 10:26 AM 7/27/2000 , you wrote:
>Unfortunately triggers execute on the Client side, not the server. What
>error message do you get when the trigger tries to execute?

From MS Access:
* ODBC--update on a linked table 'CheckHed' failed.
* [MERANT][ODBC PROGRESS driver][PROGRESS]** "DB/TRG/CHECKHED/Write.p" was
not found. (293)(#293)
* Reserved error (-7713); there is no message for this error. [thanks Bill]

This is Vantage 4 / ODBC 3.6 (on a test server.) Dunno if that makes much
difference... from what I could figure out from all the various EraNet
documents, this new ODBC driver is quite a bit different from the previous
ones? Is ODBC *supposed* to be compatible with the triggers?

-Thanks,
Wayne

(system) #7
At 10:45 AM 7/27/2000 , you wrote:
>You say that you know the risks, but I wonder if you really do. Do you know
>all the tables that are linked directly or indirectly to the table that you

Well, I do have about 10 years of database programming on our old MRP /
Financial package before we cut to Vantage last Dec. And the entire
Vantage DB structure is remarkably similar to that 20+ year old monstrosity ;)

So maybe I'm being overly confident... I'm just trying to blank out
garbage data in CheckHed.Country, so the new 4.0 Country code table cab be
cleared of the garbage that found its way in there. From what I can tell,
it's just a snapshot of the Vendor data at the time the check was written,
that is left to sit there forever; and so should be a harmless update. If
we were talking Job... or Part... tables, there's NO WAY I'd be poking in
there given my present knowledge.

>ODBC has the capability to write data back to the Progress database, but I
>don't know of a single database administrator that would recommend that

Is there any way to make it read only? There's some info on EraNet, but it
appears to be obsolete now. I've only been playing with the ODBC driver
for a couple weeks now. As Is, it's the WORST of both situations. I can't
use it to modify stuff I was hoping to. But there are still nearly 200
tables with no triggers that it will happily write to, that could be abused
if I put it on an end-user's PC.

-Wayne Cox

(system) #8
You might be able to get past the not found error by setting the -trig startup parameter for your client session.(See the progress Admin Ref for details). However,as was pointed out else where that may only get you to other errors with undefined global variables etc.

HTH

Wayne Cox wrote:

> At 10:26 AM 7/27/2000 , you wrote:
> >Unfortunately triggers execute on the Client side, not the server. What
> >error message do you get when the trigger tries to execute?
>
> From MS Access:
> * ODBC--update on a linked table 'CheckHed' failed.
> * [MERANT][ODBC PROGRESS driver][PROGRESS]** "DB/TRG/CHECKHED/Write.p" was
> not found. (293)(#293)
> * Reserved error (-7713); there is no message for this error. [thanks Bill]
>
> This is Vantage 4 / ODBC 3.6 (on a test server.) Dunno if that makes much
> difference... from what I could figure out from all the various EraNet
> documents, this new ODBC driver is quite a bit different from the previous
> ones? Is ODBC *supposed* to be compatible with the triggers?
>
> -Thanks,
> Wayne
>
>
>
> We no longer allow attachments to files. To access/share Report Files, please go to the following link: http://www.egroups.com/files/vantage/
> (Note: If this link does not work for you the first time you try it, go to www.egroups.com, login and be sure to save your password, choose My Groups, choose Vantage, then choose Files. If you save the password, the link above will work the next time you try it.)

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

Programmers, with their inbuilt sense of order and discipline, should really be running the world.

William E. Colls Tel 613 591 0079
PROComputer Systems Fax 613 591 3924
67 Willow Glen Dr www.procomsys.com
Kanata Ontario Canada K2M 1T1 PEG Member 1998051404

Specialists in Progress software development since 1985

(system) #9
On second thoughts, this probably won't work either, as the parameter would only be known to the client session, not the ODBC session. Sorry.

/* ================================================================= */

Programmers, with their inbuilt sense of order and discipline, should really be running the world.

William E. Colls Tel 613 591 0079
PROComputer Systems Fax 613 591 3924
67 Willow Glen Dr www.procomsys.com
Kanata Ontario Canada K2M 1T1 PEG Member 1998051404

Specialists in Progress software development since 1985