Insert new records externally from Vantage

Yahoo Archive
1
Perhaps that's why when I was inserting records from outside the
database via mssql that I couldn't get a bam to fire from the
transaction...



Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

Mobile: (715)896-0590

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Friday, April 16, 2010 1:52 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Insert new records externally from Vantage.





The database metadata in Vantage MSSQL is pretty thin, basically just
some indexes, and no referential integrity is defined. All of the
database structure is defined in the Progress schema holder database
that sits in front of MSSQL.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix
bspolarich@...
<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~
www.advancedphotonix.com

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of k99ja04
Sent: Friday, April 16, 2010 2:44 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: Insert new records externally from Vantage.

Like others have said some tables are safer than others. I have done it
with orders and labor. Haven't tackled quotes yet. I have heard that SQL
will let you insert 'dirty' records whereas Progress actually enforces
referential integrity so won't let you do it. I can't confirm, just what
I've heard.





[Non-text portions of this message have been removed]
2
Hi everyone,

It seems I always find the strangest things to post here. This one is no exception.

What I'd like to find out is if anyone has inserted records externally from Vantage using a VB program. I do know that there is Service Connect, but I'm not a huge fan of it and we have a lot running through ours which at times makes it fairly slow. When we send through Service Connect, it could be a few seconds to minutes depending how much is being sent to it at the time. I'm trying to get a more immediate record insert.

If I needed to, I probably could deal with Service Connect. I've heard that it is a no-no to insert records from external programs to Vantage tables. The last thing I want to do is hose a table, but I'd just like to see if anyone has done this without any negative consequences, and if so, how?

Thank you in advance or your help!

Kurt
3
Kurt:
Â
 I've done it, and still do, using VB / VBA and ODBC.
However, 'major' tables (such as Parts, Orders, etc.) should be avoided.
I have uploaded some Parts, but only without any 'methods' or 'bom's '.
The problem is of course by-passing the 'triggers' ( or business objects) that are in place.
I've made great effort to guess at those and re-create them when up-loading, as in making sure all other records get modified/added due to the upload.
Â
Bill

--- On Fri, 4/16/10, Kurt <iusevantage@...> wrote:


From: Kurt <iusevantage@...>
Subject: [Vantage] Insert new records externally from Vantage.
To: vantage@yahoogroups.com
Date: Friday, April 16, 2010, 11:03 AM


Â



Hi everyone,

It seems I always find the strangest things to post here. This one is no exception.

What I'd like to find out is if anyone has inserted records externally from Vantage using a VB program. I do know that there is Service Connect, but I'm not a huge fan of it and we have a lot running through ours which at times makes it fairly slow. When we send through Service Connect, it could be a few seconds to minutes depending how much is being sent to it at the time. I'm trying to get a more immediate record insert.

If I needed to, I probably could deal with Service Connect. I've heard that it is a no-no to insert records from external programs to Vantage tables. The last thing I want to do is hose a table, but I'd just like to see if anyone has done this without any negative consequences, and if so, how?

Thank you in advance or your help!

Kurt











[Non-text portions of this message have been removed]
4
I have done it with the ShipTo and CustCnt records using a BPM. Epicor recently negotiated a special developers license from OpenEdege which allows you to do this inside of Vantage using 4GL code. I had to write my own validation logic to make sure what needs to be created gets created, unlike Service Connect which doesn't have to worry about validation because it uses the Business Objects.

It is possible to call Business Objects from Visual Basic, but the coding gets kind of hairy.

I don't think I am brave enough to attempt adding something as complex as a Sales Order. The ShipTo and CustCnt tables are simple enough that even if they get hosed, it easy enough to repair them.




________________________________
From: Kurt <iusevantage@...>
To: vantage@yahoogroups.com
Sent: Fri, April 16, 2010 11:03:04 AM
Subject: [Vantage] Insert new records externally from Vantage.

Â
Hi everyone,

It seems I always find the strangest things to post here. This one is no exception.

What I'd like to find out is if anyone has inserted records externally from Vantage using a VB program. I do know that there is Service Connect, but I'm not a huge fan of it and we have a lot running through ours which at times makes it fairly slow. When we send through Service Connect, it could be a few seconds to minutes depending how much is being sent to it at the time. I'm trying to get a more immediate record insert.

If I needed to, I probably could deal with Service Connect. I've heard that it is a no-no to insert records from external programs to Vantage tables. The last thing I want to do is hose a table, but I'd just like to see if anyone has done this without any negative consequences, and if so, how?

Thank you in advance or your help!

Kurt







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

Thanks for your reply!!

I understand about the major tables. This deals with the Quote table. From a quick look of the trace, adding a new quote through Vantage looks to just does an update to the Quote table, but I'll check a little further. I'm trying to have a record added and then get the new quote number right after. I'm looking to use a sqlcommand INSERT to do this from my VB.net app.

I do get concerned because of Vantages quirks (which many are unknown to us until something breaks). Like even creating a view of Vantage tables in sql would at times lock all the users until we found to use 'with (nolock)' in the view. I was worried I'd find out something like that (maybe worse) doing an INSERT. We also have the PROGRESS_RECID and PROGRESS_RECID_IDENT_ columns that I'm not sure about. Someone told me that for some reason those don't get created correctly. Maybe it was Vantage wanting us to use Service Connect.

If people are doing inserts with no problems, I will look in to it more. I don't think Quotes would have the same affect on other areas like orders or parts tables. Thanks again for your response!! If you have any other input, please let me know.


Regards,

Kurt

--- In vantage@yahoogroups.com, Bill Jackson <fujijapman@...> wrote:
>
> Kurt:
> ÂÂ
>  I've done it, and still do, using VB / VBA and ODBC.
> However, 'major' tables (such as Parts, Orders, etc.) should be avoided.
> I have uploaded some Parts, but only without any 'methods' or 'bom's '.
> The problem is of course by-passing the 'triggers' ( or business objects) that are in place.
> I've made great effort to guess at those and re-create them when up-loading, as in making sure all other records get modified/added due to the upload.
> ÂÂ
> Bill
>
> --- On Fri, 4/16/10, Kurt <iusevantage@...> wrote:
>
>
> From: Kurt <iusevantage@...>
> Subject: [Vantage] Insert new records externally from Vantage.
> To: vantage@yahoogroups.com
> Date: Friday, April 16, 2010, 11:03 AM
>
>
> ÂÂ
>
>
>
> Hi everyone,
>
> It seems I always find the strangest things to post here. This one is no exception.
>
> What I'd like to find out is if anyone has inserted records externally from Vantage using a VB program. I do know that there is Service Connect, but I'm not a huge fan of it and we have a lot running through ours which at times makes it fairly slow. When we send through Service Connect, it could be a few seconds to minutes depending how much is being sent to it at the time. I'm trying to get a more immediate record insert.
>
> If I needed to, I probably could deal with Service Connect. I've heard that it is a no-no to insert records from external programs to Vantage tables. The last thing I want to do is hose a table, but I'd just like to see if anyone has done this without any negative consequences, and if so, how?
>
> Thank you in advance or your help!
>
> Kurt
>
>
>
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
6
Steven,

Thanks for your input!

Yeh, I'm still mulling over what I want to do since this will be from outside of Vantage. The application I'm doing is just too big to be able to do in Vantage, so it's going to be an external VB app. If I knew SC would be quicker, it wouldn't be a problem. But depending on how it looks, I might have to go that route anyway.

Thanks again Steven!

Regards,

Kurt

--- In vantage@yahoogroups.com, Steven Gotschall <sgotschall@...> wrote:
>
> I have done it with the ShipTo and CustCnt records using a BPM. Epicor recently negotiated a special developers license from OpenEdege which allows you to do this inside of Vantage using 4GL code. I had to write my own validation logic to make sure what needs to be created gets created, unlike Service Connect which doesn't have to worry about validation because it uses the Business Objects.
>
> It is possible to call Business Objects from Visual Basic, but the coding gets kind of hairy.
>
> I don't think I am brave enough to attempt adding something as complex as a Sales Order. The ShipTo and CustCnt tables are simple enough that even if they get hosed, it easy enough to repair them.
>
>
>
>
> ________________________________
> From: Kurt <iusevantage@...>
> To: vantage@yahoogroups.com
> Sent: Fri, April 16, 2010 11:03:04 AM
> Subject: [Vantage] Insert new records externally from Vantage.
>
> ÂÂ
> Hi everyone,
>
> It seems I always find the strangest things to post here. This one is no exception.
>
> What I'd like to find out is if anyone has inserted records externally from Vantage using a VB program. I do know that there is Service Connect, but I'm not a huge fan of it and we have a lot running through ours which at times makes it fairly slow. When we send through Service Connect, it could be a few seconds to minutes depending how much is being sent to it at the time. I'm trying to get a more immediate record insert.
>
> If I needed to, I probably could deal with Service Connect. I've heard that it is a no-no to insert records from external programs to Vantage tables. The last thing I want to do is hose a table, but I'd just like to see if anyone has done this without any negative consequences, and if so, how?
>
> Thank you in advance or your help!
>
> Kurt
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
7
 Sure, glad to help.
Â
 My app's were almost all in VB 6.0, of course the SQL statements would be very similar.
(we're on Vantage 6.10) - Not sure about those columns (PROGRESS_RECID and PROGRESS_RECID_ IDENT_) either, I believe they came after our version.
Â
 The Quote table I think is relatively safe to 'write' to, at least we haven't seen any problematic results yet, once the app is debugged.Â
Â
 We hav an app where I'm creating 'quote lines' within an exist' Quote.
Â
 I've also used the 4GL for some record 'creating'.
Â
Â
 We do as much testing as we can in our 'pilot' DB before imposing upon the Live DB.

--- On Fri, 4/16/10, Kurt <iusevantage@...> wrote:


From: Kurt <iusevantage@...>
Subject: [Vantage] Re: Insert new records externally from Vantage.
To: vantage@yahoogroups.com
Date: Friday, April 16, 2010, 12:40 PM


Â





Bill,

Thanks for your reply!!

I understand about the major tables. This deals with the Quote table. From a quick look of the trace, adding a new quote through Vantage looks to just does an update to the Quote table, but I'll check a little further. I'm trying to have a record added and then get the new quote number right after. I'm looking to use a sqlcommand INSERT to do this from my VB.net app.

I do get concerned because of Vantages quirks (which many are unknown to us until something breaks). Like even creating a view of Vantage tables in sql would at times lock all the users until we found to use 'with (nolock)' in the view. I was worried I'd find out something like that (maybe worse) doing an INSERT. We also have the PROGRESS_RECID and PROGRESS_RECID_ IDENT_ columns that I'm not sure about. Someone told me that for some reason those don't get created correctly. Maybe it was Vantage wanting us to use Service Connect.

If people are doing inserts with no problems, I will look in to it more. I don't think Quotes would have the same affect on other areas like orders or parts tables. Thanks again for your response!! If you have any other input, please let me know.

Regards,

Kurt

--- In vantage@yahoogroups .com, Bill Jackson <fujijapman@ ...> wrote:
>
> Kurt:
> ÂÂ
>  I've done it, and still do, using VB / VBA and ODBC.
> However, 'major' tables (such as Parts, Orders, etc.) should be avoided.
> I have uploaded some Parts, but only without any 'methods' or 'bom's '.
> The problem is of course by-passing the 'triggers' ( or business objects) that are in place.
> I've made great effort to guess at those and re-create them when up-loading, as in making sure all other records get modified/added due to the upload.
> ÂÂ
> Bill
>
> --- On Fri, 4/16/10, Kurt <iusevantage@ ...> wrote:
>
>
> From: Kurt <iusevantage@ ...>
> Subject: [Vantage] Insert new records externally from Vantage.
> To: vantage@yahoogroups .com
> Date: Friday, April 16, 2010, 11:03 AM
>
>
> ÂÂ
>
>
>
> Hi everyone,
>
> It seems I always find the strangest things to post here. This one is no exception.
>
> What I'd like to find out is if anyone has inserted records externally from Vantage using a VB program. I do know that there is Service Connect, but I'm not a huge fan of it and we have a lot running through ours which at times makes it fairly slow. When we send through Service Connect, it could be a few seconds to minutes depending how much is being sent to it at the time. I'm trying to get a more immediate record insert.
>
> If I needed to, I probably could deal with Service Connect. I've heard that it is a no-no to insert records from external programs to Vantage tables. The last thing I want to do is hose a table, but I'd just like to see if anyone has done this without any negative consequences, and if so, how?
>
> Thank you in advance or your help!
>
> Kurt
>
>
>
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>











[Non-text portions of this message have been removed]
8
Like others have said some tables are safer than others. I have done it with orders and labor. Haven't tackled quotes yet. I have heard that SQL will let you insert 'dirty' records whereas Progress actually enforces referential integrity so won't let you do it. I can't confirm, just what I've heard.

Can you be more specific on what you want to do? There is a great deal of flexibility by using .NET code behind a customization.

Just a caution on Service Connect, aside from performance. Depending on your version, you may have to rewrite your workflows when upgrading to the next version.

--- In vantage@yahoogroups.com, "Kurt" <iusevantage@...> wrote:
>
> Hi everyone,
>
> It seems I always find the strangest things to post here. This one is no exception.
>
> What I'd like to find out is if anyone has inserted records externally from Vantage using a VB program. I do know that there is Service Connect, but I'm not a huge fan of it and we have a lot running through ours which at times makes it fairly slow. When we send through Service Connect, it could be a few seconds to minutes depending how much is being sent to it at the time. I'm trying to get a more immediate record insert.
>
> If I needed to, I probably could deal with Service Connect. I've heard that it is a no-no to insert records from external programs to Vantage tables. The last thing I want to do is hose a table, but I'd just like to see if anyone has done this without any negative consequences, and if so, how?
>
> Thank you in advance or your help!
>
> Kurt
>
9
The database metadata in Vantage MSSQL is pretty thin, basically just some indexes, and no referential integrity is defined. All of the database structure is defined in the Progress schema holder database that sits in front of MSSQL.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of k99ja04
Sent: Friday, April 16, 2010 2:44 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Insert new records externally from Vantage.

Like others have said some tables are safer than others. I have done it with orders and labor. Haven't tackled quotes yet. I have heard that SQL will let you insert 'dirty' records whereas Progress actually enforces referential integrity so won't let you do it. I can't confirm, just what I've heard.