Re: [U2] Get External Sql Data Dynamically
I believe that Reality will allow you to interface to other databases. In fact, in Vegas they indicated that you could write your entire database to Oracle or SQL for storing your data and have your programs etc be in Pick. Eugene - Original Message - From: "Ken Wallis" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 06, 2004 10:26 PM Subject: RE: [U2] Get External Sql Data Dynamically > Kris Stevens wrote: > > > That was our first solution was updating a local file with > > the data from the sql table. > > I was hoping for something "a little more dynamic", but that > > is what you get with a "closed environment" that UniData is. > > What you want to do is what the OFS used to do, and still does do, (and is > documented to do) but is now (strongly) deprecated, as far as I can tell. > > OFS provides a mechanism through which you can identify an alternate set of > 'C' routines which will transparently handle I/O to your data source. Once > upon a time there was a third party that wrote and distributed a library set > called DAM (Data Access Modules) which provided OFS access to Oracle/Sybase > and Informix. I still support one site that runs this DAM software to > interface with Oracle (in fact they are just doing testing of the interface > with UniData 6 and Oracle 9i). > > There are many uglinesses about OFS. The worst of which is that it only > works for BASIC programs and not for ECL commands (though the DAM libraries > do fashion a kludge which allows a form of SELECT to work). Others include > the way in which it has been treated by Ardent/Informix and IBM with extra > bits being added to the API to support the aborted SQLator project, but > never documented properly so others could update their OFS drivers to allow > for things like transaction commit and rollback. The DAM libraries had > their own uglinesses in that they used STATIC SQL statements (ie no > parameter passing, just hard coded values in the SQL for each update or > select) which tended to make Oracle administrators in particular have a cow > whenever they analysed what was going in and out of the database. > > The best thing about OFS though is that once you have the code in place and > you set up an "FX" VOC pointer, an external RDBMS table, or set of tables, > can simply be OPENed by a BASIC program which can then do READs and WRITEs > without knowing that it is connected to anything unusual. > > I believe that at least one other contributor to this list has written his > own OFS driver for Oracle. I doubt he'd share with you, because it isn't a > trivial thing to do, and I'm not recommending you try and write you own - > again because it really isn't a trivial exercise. I suppose I'm just > pointing out that there used to be a way to do this quite well (OFS was > first introduced in UniData 3.1 about 10 years ago), but it got sat on and > squished until it died and now the best alternative is BCI which still only > works from BASIC and still requires you to purchase and install a third > party component (the ODBC manager). > > Last time I saw a U2 presentation there was talk of something new being > developed to sort of replace OFS but work everywhere, but I don't recall the > timeline on that being anywhere 'real soon now'. > > Meanwhile, jBASE remains the only MV product on the market which does allow > transparent integration with external databases. The jBASE jEDI interface > does the same sort of things as OFS, but extends throughout the product and > works transparently behind every facet of jBASE - BASIC, TCL, PROC, etc. As > someone who has written jEDI drivers and supports live sites that use them, > I know that the jEDI is not perfect, but it does work. Now if only I could > persuade the guys at Temenos not to do to the jEDI what UniData did to OFS, > I'd be a happy camper. Sadly though, they already seem to be heading for > the dark side - adding new bits to the API and refusing to document them, > changing things that used to work and improving them until they don't ... > > Cheers, > > Ken > > PS. In your specific example: > > >> List MYLocalFILE where MyRemoteKey = "X" MyRemoteData > > It looks like you have a UniData file whose KEYs are significant to your > external data source. That your external data is essentially providing > additional attributes that you want to access and query from within UniData. > That being the case, you may well be able to use BCI inside a SUBR called > from a Virtual Field (I type) to access the data in the remote database and > retrieve it. With a bit of judicious use of named common you co
Re: [U2] Get External Sql Data Dynamically
Certainly, that's a valid option -- just test to make sure the overhead is reasonable for the given process. I also like the web services suggestion, and am pursuing setting up a system for exactly this purpose. BTW, if anyone else is working with Apache Axis, please feel free to contact me offline with any hints / tips / questions. Best, David Beahm Tom Firl wrote: Could Kris use an I-descriptor to call a BASIC SUBRoutine that uses BCI to retrieve the value in the SQL database? Tom Firl -Original Message- From: David Beahm [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 2:52 PM To: [EMAIL PROTECTED] Subject: Re: [U2] Get External Sql Data Dynamically BCI doesn't offer quite as tight an integration as that. What it gives you is the ability to read and write to SQL data sources from within UniBasic. For instance, we have a table that exists in MySQL, but we need to access the data from UniData (and for the moment we don't have time to change the user interface to support interfacing with UniData -- besides it made a good test case). Anyway, we were able to execute SQL queries against the external database from within a program. In your case, you might want to write a program that updates a local file with data from the SQL table. Then you can use all your familiar Retrieve logic (I-types, etc.) HTH, David Beahm --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
Re: [U2] Get External Sql Data Dynamically
Ken, Meanwhile, jBASE remains the only MV product on the market which does allow transparent integration with external databases. The jBASE jEDI interface does the same sort of things as OFS, but extends throughout the product and works transparently behind every facet of jBASE - BASIC, TCL, PROC, etc. Actually, Reality has a better support than jBASE and ON Corp is refers to their system as a database independent MV environment. Kris, you could also build an automated update to your SQL database with any number of tools, including [AD] the upcoming Zeus SQL Edition (scheduled for release June/July of this year) [/AD]. Additionally, you could make the SQL system drive the process and have it output to flatfile which you can link to U2 by declaring it a type 19 file. Not so closed after all, is it? For that matter, you could make the U2 database work as a web service and make the SQL do the same and cross-update using CGI. -- - Charles Barouch [EMAIL PROTECTED] - Zeus Data Integration [EMAIL PROTECTED]- Consulting services (718) 762-3884 x 1 - Key Ally Voice mail Mount Olympus, Home of Zeus Data Integration --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
RE: [U2] Get External Sql Data Dynamically
Kris Stevens wrote: > That was our first solution was updating a local file with > the data from the sql table. > I was hoping for something "a little more dynamic", but that > is what you get with a "closed environment" that UniData is. What you want to do is what the OFS used to do, and still does do, (and is documented to do) but is now (strongly) deprecated, as far as I can tell. OFS provides a mechanism through which you can identify an alternate set of 'C' routines which will transparently handle I/O to your data source. Once upon a time there was a third party that wrote and distributed a library set called DAM (Data Access Modules) which provided OFS access to Oracle/Sybase and Informix. I still support one site that runs this DAM software to interface with Oracle (in fact they are just doing testing of the interface with UniData 6 and Oracle 9i). There are many uglinesses about OFS. The worst of which is that it only works for BASIC programs and not for ECL commands (though the DAM libraries do fashion a kludge which allows a form of SELECT to work). Others include the way in which it has been treated by Ardent/Informix and IBM with extra bits being added to the API to support the aborted SQLator project, but never documented properly so others could update their OFS drivers to allow for things like transaction commit and rollback. The DAM libraries had their own uglinesses in that they used STATIC SQL statements (ie no parameter passing, just hard coded values in the SQL for each update or select) which tended to make Oracle administrators in particular have a cow whenever they analysed what was going in and out of the database. The best thing about OFS though is that once you have the code in place and you set up an "FX" VOC pointer, an external RDBMS table, or set of tables, can simply be OPENed by a BASIC program which can then do READs and WRITEs without knowing that it is connected to anything unusual. I believe that at least one other contributor to this list has written his own OFS driver for Oracle. I doubt he'd share with you, because it isn't a trivial thing to do, and I'm not recommending you try and write you own - again because it really isn't a trivial exercise. I suppose I'm just pointing out that there used to be a way to do this quite well (OFS was first introduced in UniData 3.1 about 10 years ago), but it got sat on and squished until it died and now the best alternative is BCI which still only works from BASIC and still requires you to purchase and install a third party component (the ODBC manager). Last time I saw a U2 presentation there was talk of something new being developed to sort of replace OFS but work everywhere, but I don't recall the timeline on that being anywhere 'real soon now'. Meanwhile, jBASE remains the only MV product on the market which does allow transparent integration with external databases. The jBASE jEDI interface does the same sort of things as OFS, but extends throughout the product and works transparently behind every facet of jBASE - BASIC, TCL, PROC, etc. As someone who has written jEDI drivers and supports live sites that use them, I know that the jEDI is not perfect, but it does work. Now if only I could persuade the guys at Temenos not to do to the jEDI what UniData did to OFS, I'd be a happy camper. Sadly though, they already seem to be heading for the dark side - adding new bits to the API and refusing to document them, changing things that used to work and improving them until they don't ... Cheers, Ken PS. In your specific example: >> List MYLocalFILE where MyRemoteKey = "X" MyRemoteData It looks like you have a UniData file whose KEYs are significant to your external data source. That your external data is essentially providing additional attributes that you want to access and query from within UniData. That being the case, you may well be able to use BCI inside a SUBR called from a Virtual Field (I type) to access the data in the remote database and retrieve it. With a bit of judicious use of named common you could even make this reasonably efficient so that accessing 5 separate virtual fields based on the same UniData key value would only go to your external data source once. The tricky bit about transparent access comes when you have NO local data records from which you can drive your queries, simply the knowledge that an entity exists "somewhere else" and contains information that you need to see: LIST MyRemoteFile WITH ItsRemoteKey = "Y" There really is no way to do this except to run a specific BCI BASIC program to access the information and return it in a specific format. > David Beahm wrote: > BCI doesn't offer quite as tight an integration as that. > What it gives > you is the ability to read and write to SQL data sources from within > UniBasic. For instance, we have a table that exists in MySQL, but we > need to access the data from UniData (and for the moment we > don't have > time to change
Re: [U2] Get External Sql Data Dynamically
In a message dated 5/6/2004 3:53:03 PM Pacific Daylight Time, [EMAIL PROTECTED] writes: > I was hoping for something "a little more dynamic", but that is what you > get > with a "closed environment" that UniData is. Unidata is not closed. --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
RE: [U2] Get External Sql Data Dynamically
Could Kris use an I-descriptor to call a BASIC SUBRoutine that uses BCI to retrieve the value in the SQL database? Tom Firl > -Original Message- > From: David Beahm [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 06, 2004 2:52 PM > To: [EMAIL PROTECTED] > Subject: Re: [U2] Get External Sql Data Dynamically > > > BCI doesn't offer quite as tight an integration as that. > What it gives > you is the ability to read and write to SQL data sources from within > UniBasic. For instance, we have a table that exists in MySQL, but we > need to access the data from UniData (and for the moment we > don't have > time to change the user interface to support interfacing with > UniData -- > besides it made a good test case). Anyway, we were able to > execute SQL > queries against the external database from within a program. In your > case, you might want to write a program that updates a local > file with > data from the SQL table. Then you can use all your familiar Retrieve > logic (I-types, etc.) > > HTH, > David Beahm > > > > Kris Stevens wrote: > > > Thanks for the response. > > > > Here is a followup question, to verify I am asking my > question clearly. > > > > What I am trying to do is: > > > > List MYLocalFILE where MyRemoteKey = "X" MyRemoteData > > > > where the MyLocalfile is a local unidata mv file. > > MyRemoteKey is a dict item that references the remote SQL > table on the > > network. > > MyRemoteData is a dict item that returns the value from the > remote SQL > > table. > > > > The clients would not have any software installed, just the > server only. > > > > Would this need to be done in a datatel subroutine? > > > > Thanks! > > > > Kris Stevens > > > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Behalf Of David Beahm > > Sent: Thursday, May 06, 2004 1:44 PM > > To: [EMAIL PROTECTED] > > Subject: Re: [U2] Get External Sql Data Dynamically > > > > > > Yes, BCI is the tool. If you're on Unix, you also need to > have an ODBC > > manager. Being on 64-bit HP-UX (which makes it hard to > find compatible > > OTS software), we ended up using EasySoft's ODBC-ODBC > bridge (they made > > a custom port to HP-UX for us), which is working great. > > > > Best, > > David Beahm > > > > Tom Firl wrote: > > > >>Take a look at BCI... this is the U2 feature that provides > ODBC client > > > > functionality. I personally haven't used it, but several > readers of this > > group have... > > > >>Tom Firl > >>Columbia Ultimate > > > > --- > > u2-users mailing list > > [EMAIL PROTECTED] > > http://www.u2ug.org/listinfo/u2-users > > --- > > u2-users mailing list > > [EMAIL PROTECTED] > > http://www.u2ug.org/listinfo/u2-users > --- > u2-users mailing list > [EMAIL PROTECTED] > http://www.u2ug.org/listinfo/u2-users --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
RE: [U2] Get External Sql Data Dynamically
That was our first solution was updating a local file with the data from the sql table. I was hoping for something "a little more dynamic", but that is what you get with a "closed environment" that UniData is. Still , I like the environment warts and all. Kris Stevens -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of David Beahm Sent: Thursday, May 06, 2004 4:52 PM To: [EMAIL PROTECTED] Subject: Re: [U2] Get External Sql Data Dynamically BCI doesn't offer quite as tight an integration as that. What it gives you is the ability to read and write to SQL data sources from within UniBasic. For instance, we have a table that exists in MySQL, but we need to access the data from UniData (and for the moment we don't have time to change the user interface to support interfacing with UniData -- besides it made a good test case). Anyway, we were able to execute SQL queries against the external database from within a program. In your case, you might want to write a program that updates a local file with data from the SQL table. Then you can use all your familiar Retrieve logic (I-types, etc.) HTH, David Beahm Kris Stevens wrote: > Thanks for the response. > > Here is a followup question, to verify I am asking my question clearly. > > What I am trying to do is: > > List MYLocalFILE where MyRemoteKey = "X" MyRemoteData > > where the MyLocalfile is a local unidata mv file. > MyRemoteKey is a dict item that references the remote SQL table on the > network. > MyRemoteData is a dict item that returns the value from the remote SQL > table. > > The clients would not have any software installed, just the server only. > > Would this need to be done in a datatel subroutine? > > Thanks! > > Kris Stevens > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of David Beahm > Sent: Thursday, May 06, 2004 1:44 PM > To: [EMAIL PROTECTED] > Subject: Re: [U2] Get External Sql Data Dynamically > > > Yes, BCI is the tool. If you're on Unix, you also need to have an ODBC > manager. Being on 64-bit HP-UX (which makes it hard to find compatible > OTS software), we ended up using EasySoft's ODBC-ODBC bridge (they made > a custom port to HP-UX for us), which is working great. > > Best, > David Beahm > > Tom Firl wrote: > >>Take a look at BCI... this is the U2 feature that provides ODBC client > > functionality. I personally haven't used it, but several readers of this > group have... > >>Tom Firl >>Columbia Ultimate > > --- > u2-users mailing list > [EMAIL PROTECTED] > http://www.u2ug.org/listinfo/u2-users > --- > u2-users mailing list > [EMAIL PROTECTED] > http://www.u2ug.org/listinfo/u2-users --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
Re: [U2] Get External Sql Data Dynamically
BCI doesn't offer quite as tight an integration as that. What it gives you is the ability to read and write to SQL data sources from within UniBasic. For instance, we have a table that exists in MySQL, but we need to access the data from UniData (and for the moment we don't have time to change the user interface to support interfacing with UniData -- besides it made a good test case). Anyway, we were able to execute SQL queries against the external database from within a program. In your case, you might want to write a program that updates a local file with data from the SQL table. Then you can use all your familiar Retrieve logic (I-types, etc.) HTH, David Beahm Kris Stevens wrote: Thanks for the response. Here is a followup question, to verify I am asking my question clearly. What I am trying to do is: List MYLocalFILE where MyRemoteKey = "X" MyRemoteData where the MyLocalfile is a local unidata mv file. MyRemoteKey is a dict item that references the remote SQL table on the network. MyRemoteData is a dict item that returns the value from the remote SQL table. The clients would not have any software installed, just the server only. Would this need to be done in a datatel subroutine? Thanks! Kris Stevens -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of David Beahm Sent: Thursday, May 06, 2004 1:44 PM To: [EMAIL PROTECTED] Subject: Re: [U2] Get External Sql Data Dynamically Yes, BCI is the tool. If you're on Unix, you also need to have an ODBC manager. Being on 64-bit HP-UX (which makes it hard to find compatible OTS software), we ended up using EasySoft's ODBC-ODBC bridge (they made a custom port to HP-UX for us), which is working great. Best, David Beahm Tom Firl wrote: Take a look at BCI... this is the U2 feature that provides ODBC client functionality. I personally haven't used it, but several readers of this group have... Tom Firl Columbia Ultimate --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
RE: [U2] Get External Sql Data Dynamically
Thanks for the response. Here is a followup question, to verify I am asking my question clearly. What I am trying to do is: List MYLocalFILE where MyRemoteKey = "X" MyRemoteData where the MyLocalfile is a local unidata mv file. MyRemoteKey is a dict item that references the remote SQL table on the network. MyRemoteData is a dict item that returns the value from the remote SQL table. The clients would not have any software installed, just the server only. Would this need to be done in a datatel subroutine? Thanks! Kris Stevens -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of David Beahm Sent: Thursday, May 06, 2004 1:44 PM To: [EMAIL PROTECTED] Subject: Re: [U2] Get External Sql Data Dynamically Yes, BCI is the tool. If you're on Unix, you also need to have an ODBC manager. Being on 64-bit HP-UX (which makes it hard to find compatible OTS software), we ended up using EasySoft's ODBC-ODBC bridge (they made a custom port to HP-UX for us), which is working great. Best, David Beahm Tom Firl wrote: > Take a look at BCI... this is the U2 feature that provides ODBC client functionality. I personally haven't used it, but several readers of this group have... > > Tom Firl > Columbia Ultimate --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
Re: [U2] Get External Sql Data Dynamically
Yes, BCI is the tool. If you're on Unix, you also need to have an ODBC manager. Being on 64-bit HP-UX (which makes it hard to find compatible OTS software), we ended up using EasySoft's ODBC-ODBC bridge (they made a custom port to HP-UX for us), which is working great. Best, David Beahm Tom Firl wrote: Take a look at BCI... this is the U2 feature that provides ODBC client functionality. I personally haven't used it, but several readers of this group have... Tom Firl Columbia Ultimate --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
RE: [U2] Get External Sql Data Dynamically
Take a look at BCI... this is the U2 feature that provides ODBC client functionality. I personally haven't used it, but several readers of this group have... Tom Firl Columbia Ultimate > -Original Message- > From: Kris Stevens [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 06, 2004 10:04 AM > To: '[EMAIL PROTECTED]' > Subject: [U2] Get External Sql Data Dynamically > > > I am looking to setup a link from within unidata to a > external SQL Db table. > Has anybody done this? > > Background: > > This table needs to be maintained outside the unidata environment, > but the data available inside the unidata environment. > > Any pointers would be nice to have. > > Thanks in advance! > > Kris Stevens > Sr. Programmer/Analyst > [EMAIL PROTECTED] > St. Louis, MO > --- > u2-users mailing list > [EMAIL PROTECTED] > http://www.u2ug.org/listinfo/u2-users --- u2-users mailing list [EMAIL PROTECTED] http://www.u2ug.org/listinfo/u2-users
